Thursday, October 1, 2015

SELECT INTO & INSERT INTO in SQL Server

In SQL Server, it is very common situation to transfer data from one table to another. This could be either between the temporary tables or existing tables. To achieve this, we have 2 options, use SELECT INTO & INSERT INTO. Both provide the same functionality, but there are two important differences between these two, which may affect your decision about which one to use. Let's discuss these differences one by one.
  1. The first difference is that SELECT INTO does not requires a target table to exist in your database for transferring the data from source table. A new table will be created automatically, when this command is used.

    On the other hand, INSERT INTO requires a target table to exist, before data can be transferred from source to target table.



    As seen above, INSERT INTO will require us to create a target table and then insert the data. Also, if we try to execute the SELECT INTO for an existing table, it will not work as it will try to create another table with same name, which will result in error.
     
  2. The second difference is related to the structure of the target tables. As we discussed above, when we use SELECT INTO statement, it creates the structure of the table automatically. The problem is that the new table is created with the same structure, which the source table has. This can become a problem when we try to insert the data. Let's discuss with an example

    We have a source table, with a VARCHAR column of size 50. When we use SELECT INTO, the target table generated will also have the VARCHAR column with size 50. So if you try to insert data in the VARCHAR column, which is more than the size 50, it will result in error String or binary data would be truncated. Of-course this would have been the error in normal scenarios also, but this error is something which could affect your choice to select type of command you should use. See the example below:

Difference between select into and insert into

Select Into ->Creates the table on the fly upon select execution
while
Insert Into ->Presumes that the Table DB already exist
lastly
Create, simply creates the table from the return result of the query

here are some very important differences between SELECT INTO and INSERT.
First, for the INSERT you need to pre-define the destination table. SELECT INTO creates the table as part of the statement.
Second, as a result of the first condition, you can get type conversion errors on the load into the table using INSERT. This cannot happen with a SELECT INTO (although the underlying query could produce an error).
Third, with a SELECT INTO you need to give all your columns names. With an INSERT, you do not need to give them names.
Fourth, SELECT INTO locks some of the metadata during the processing. This means that other queries on the database may be locked out of accessing tables. For instance, you cannot run two SELECT INTO statements at the same time on the same database, because of this locking.
Fifth, on a very large insert, you can sometimes see progress with INSERT but not with SELECT INTO. At least, this is my experience.
When I have a complicated query and I want to put the data into a table, I often use:
SELECT top 0 *
INTO <table>
FROM <query>

INSERT INTO <table>
    SELECT * FROM <query>

Modify User-defined Functions

You can modify user-defined functions in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. Modifying user-defined functions as described below will not change the functions’ permissions, nor will it affect any dependent functions, stored procedures, or triggers.

Limitations and Restrictions

ALTER FUNCTION cannot be used to perform any of the following actions:
  • Change a scalar-valued function to a table-valued function, or vice versa.
  • Change an inline function to a multistatement function, or vice versa.
  • Change a Transact-SQL function to a CLR function, or vice-versa.
Alters an existing Transact-SQL or CLR function that was previously created by executing the CREATE FUNCTION statement, without changing permissions and without affecting any dependent functions, stored procedures, or triggers.

Practical Difference between Const & ReadOnly

Introduction

While programming on C#, it is always suggested that we understand the concepts before we go ahead and implement the code. In this article, let us understand the basic differences between the const and the readonly keywords and also understand how to use them in our code.
This is my first article on code project and would like to contibute and learn more from here.

Background

At a very high level, as per MSDN
Constants are immutable values which are known at compile time and do not change their values for the life of the program. 
Readonly variables are also immutable values which are known at run time and do not change their values for the life of the program.
Hmm..definitions apart, let us understand what this really means to us.

Using the code

Constants:
Constants are declared using a "const" keyword.
Constants should be assigned a value at the time of the variable declaration and hence are known at compile time. Now whenever you declare a constant variable, the C# compiler substitutes its value directly into the Intermediate Language (MSIL).
    class ConstantEx
    {
        public const int number =3;
    }

    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(ConstantEx.number);
            Console.ReadLine();
        }
    }
Now that we understand the value is directly replaced in the MSIL, any modifications you do to the const variable would result in something similar to below
class Program
    {
        static void Main(string[] args)
        {
            // ConstantEx.number = 15   
            // The above line would throw an error as it internally becomes a statement saying 3=15 
            // which is not valid 
            Console.WriteLine(ConstantEx.number);
            Console.ReadLine();
        }
    }
Hence, constants are immutable values which are known at compile time and do not change their values for the life of the program.

Readonly :
Readonly variables are a little different from their colleague, const.
Readonly variables are known at runtime, they can be assigned a value either at runtime or at the time of the instance initialization. Again, lets understand through code here.
    class ReadOnlyEx
    {
        public readonly int number = 10;
    }  

    class Program
    {
        static void Main(string[] args)
        {
            ReadOnlyEx readOnlyInstance = new ReadOnlyEx();
            Console.WriteLine(readOnlyInstance.number);
        }
    }
In the above code snippet, the readonly variable is assigned a value at the time of the declaration and is accessed using the instance of the class rather than using the class itself. Now you may have another instance of the class, which might have the readonly number variable assigned to a different value based on some conditions. Can I do it? Yes, because the readonly variables are known at run time.
Let us try doing this.
class ReadOnlyEx
{
    public readonly int number = 10;
    public ReadOnlyEx()
    {
        number =20;
    }
    public ReadOnlyEx(bool IsDifferentInstance)
    {
        number = 100;
    }
}

class Program
{
    static void Main(string[] args)
    {

        ReadOnlyEx readOnlyInstance = new ReadOnlyEx();
        Console.WriteLine(readOnlyInstance.number);

        ReadOnlyEx differentInstance = new ReadOnlyEx(true);
        Console.WriteLine(differentInstance.number);

        Console.ReadLine();
    }
}
You would see different values coming out of the program's output for the two different instance of the class.
Hence,Readonly variables are also immutable values which are known at run time and do not change their values for the life of the program.

Now for those "Let me read this interview question" kind of guys:

Constants:
1. Constants can be assigned values only at the time of declaration
2. Constant variables have to be accessed using "Classname.VariableName"
3. Constants are known at compile time
Read Only:
1. Read only variables can be assigned values either at runtime or at the time of instance initialization via constructor
2. Read only variables have to be accessed using the "InstanceName.VariableName"
3. Read only variables are known at run time.

Now that we understand the differences between them, one can easily determine the appropriate keyword as per the requirement.

JQUERY, JSON , Angular Interview questions

What is Jquery ?

Jquery is a reusable javascript library which simplifies javascript coding. So rather than writing length javascript code as below.
document.getElementById("txt1").value = "hello";
By jquery the above javascript code is now simplified as below.
$("#txt1").val("Hello");

So will jquery replace javascript ?

No, Jquery is not meant to replace javascript. Jquery is a library while javascript is a language. Jquery sits on the top of javascript to make your development easy.

So how do we use these reusable jquery libraries?

You need to download Jquery.js file from jquery.com and include the same in your web pages. The jquery files are named with version number like “jquery-1.4.1.js” where 1.4.1 is the version of the JS file. So at the top of your web page you need to include the javascript as shown in the below code.
<script src="file:///C:/Documents%20and%20Settings/admin/Documents/My%20Web%20Sites/Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>

What is CDN (Content delivery network)?

In CDN multiple copies of the website is copied on different geographical servers. When users request website content which have CDN enabled depending on their geographical location , content is served from the nearest geographical location server of the user.
So if a user is from India, the Indian CDN server will serve request for Indian users. This leads to faster delivery of data.

For Jquery files which are the popular CDN’s?

There are two popular CDN’s Microsoft and google.
If you want to reference google CDN Jquery files you can use the below script.
<script type="text/javascript"
    src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
If you want to use Microsoft CDN you can use the below javascript.
<script type="text/javascript"
    src="http://ajax.microsoft.com/ajax/jquery/jquery-1.9.1.min.js">
</script>

How can we reference local Jquery files if CDN fails?

Many times it’s possible that Microsoft and google servers can go down for some time. So in those situations you would like your page to reference jquery files from local server.
So to implement a CDN fallback is a two-step process:-
First reference the CDN jquery. In the below code you can see we have reference Microsoft CDN jquery file.
http://ajax.microsoft.com/ajax/jquery/jquery-1.9.1.min.js "></script>
Now if Microsoft CDN is down then the Jquery value will be “undefined”. So you can see in the below code we are checking if the Jquery is having “undefined” value then do a document write and reference your local Jquery files.
if (typeof jQuery == 'undefined')
{
  document.write(unescape("%3Cscript src='Scripts/jquery.1.9.1.min.js' type='text/javascript'%3E%3C/script%3E"));
}
Below is the full code for the same.
<script type="text/javascript" src="file:///C:/Documents%20and%20Settings/admin/Documents/My%20Web%20Sites/%20http:/ajax.microsoft.com/ajax/jquery/jquery-1.9.1.min.js%2520"></script>
<script type="text/javascript">
if (typeof jQuery == 'undefined')
{
  document.write(unescape("%3Cscript src='Scripts/jquery.1.9.1.min.js' type='text/javascript'%3E%3C/script%3E"));
}
</script>

What is the difference between Jquery.js and Jquery.min.js file?

First thing both the files provide the same jquery functionalities. One is a long version and the other is compressed / minified version. The minified version is compressed to save bandwidth and space by compressing and removing all the white spaces.
Below is the view of Jquery.js.
Below this is view of Jquery.min.js file (compressed and minified).

When should we use jquery.js over jquery.min.js ?

When you are doing development use “jquery.js” file because you would like to debug, see the javascript code etc. Use “Jquery.min.js” for production environment. In production / live environment we would like to consume less bandwidth, we would like to our pages to load faster.

What is the use jquery.vsdoc.js ?

This file you can include if you want to enable intellisense in visual studio for Jquery.

How does the basic syntax of Jquery looks like?

Jquery syntax structure can be broken down in to four parts:-
  • All Jquery commands start with a “$” sign.
  • Followed by the selection of the HTML element. For example below is a simple image where we are selecting a HTML textbox by id “txt1”.
  • Then followed by the DOT (.) separator. This operator will separate the element and the action on the element.
  • Finally what action you want to perform on the HTML element. For instance in the below Jquery code we are setting the text value to “Hello JQuery’.

What is the “$” sign in Jquery ?

The “$” sign is an alias for jquery.

When should we use Jquery.noConflict()?

There are many javascript frameworks like MooTools, Backbone, Sammy, Cappuccino, Knockout etc. Some of these frameworks also use “$” sign so this can lead to conflict with Jquery framework.
So you can use the “noConflict” method and release the jquery “$” sign as shown in the below code.
$.noConflict();
jQuery("p").text("I am jquery and I am working…");
You can also create your own jquery shortcut as shown below.
var jq = $.noConflict();
jq("p").text("I am invoked using jquery shortcut…");

What are the different ways by which you can select a HTML element in JQuery ?

You can select Jquery elements in the following ways:-
Select all
Below is a simple code snippet which selects all paragraph tags and hides them.
$("p").hide();
Select by ID
$("#Text1").val("Shiv");
Select using Equal method
Select using Find method
Select using Filter method

What is the use of Document.ready in Jquery ?

“Document.Ready” event occurs once the complete HTML DOM is loaded. So the next question is when do we actually need this event?. Consider the below simple code where we are trying to set a text box “text1” with value “Sometext”.
Now at the point when Jquery code tries set the textbox value , at that moment that text box is not available in the HTML DOM. So it throws an exception for the same.
<script>
      $("#text1").val("Sometext"); // Throws exception as the textbox is not //accessible at this moment
</script>
</head>
<body>
<input type="text" id="text1" />
</body>
So we would like to execute the Jquery code which sets the textbox value only when all the HTML objects are loaded in DOM. So you can replace the code of setting text box value to something as shown below.
<script>
       $(document).ready(function(){
           $("#text1").val("Sometext");
       });
</script>
Here is a nice detail article with a video which explains Jquery Ready event in a more detail mannerhttp://www.dotnetinterviewquestions.in/article_jquery-interview-questions:-when-do-we-need-documentreadyevent-_230.html

Can we have two document.ready in a webpage?

Yes.

How can we attach a method to a HTML element event using Jquery ?

Below is a simple code which attaches a function to click event of a button.
$("button").click(function(){
$("p").toggle();
});
Below is one more example where we have attached the a function to a mouse enter event of a paragraph.
$("#p1").mouseenter(function(){
  alert("You entered p1!");
});

How can we add a style using Jquery?

$("li").filter(".middle").addClass("selected");
<style>
      .selected { color:red; }
</style>

What is JSON?

JSON (JavaScript object notation) helps us to present and exchange data in a self-descriptive, independent and light way. This data can then be easily consumed and transformed in to javascript objects.
Below is a simple example of JSON format looks. You can understand from the format how lightweight and easy the format looks.
Figure :- JSON
The biggest advantage of JSON format is it can be evaluated to a javascript object. For instance you can see in the below code snippet we have a JSON format data which has “name”,”street”,”age” and “phone”. Now this data can be consumed as shown in the code snippet below, evaluated to a javascript object and invoked as anobject property.
You can see how we have called the “name” property using an object “JSONObject.name”.
<script type="text/javascript">

var JSONObject= {
"name":"John Johnson",
"street":"Oslo West 555", 
"age":33,
"phone":"555 1234567"};

alert(JSONObject.name); 
</script>

Was not SOAP meant to do the same thing which JSON does?

SOAP is heavy due to XML tags. For example a SOAP message "Shiv" will become short , sweet and light in JSON like "Name" : "Shiv". Second most important it evaluates as javascript object. To convert the complicated SOAP XML in to javascript JSON object would be a tough and tedious task.
Figure 11.11:- SOAP meant to do the same thing

Do all technologies support JSON?

Yes , Almost all technologies who deal with exchange of data support JSON. For instance if you want to that your WCF service should send JSON message rather than SOAP you can set the “ResponseFormat” as “WebMessageFormat.Json” on your operation contract.
[OperationContract]
[WebInvoke(Method="GET", UriTemplate="/GetData", RequestFormat=WebMessageFormat.Json,
           ResponseFormat=WebMessageFormat.Json)]
string GetData();
If you want your MVC to emit out JSON data you can return “JsonResult” as shown below. If you call the below action it will emit out Customer objects in Json format.
public JsonResult  CustomerJson()
{
     List<Customer> obj1 = new List<Customer>();
     Thread.Sleep(5000);
            Customer obj = new Customer();
            obj.CustomerCode = "1001";
            obj1.Add(obj);
            return Json(obj1,JsonRequestBehavior.AllowGet);
}
If you want to emit JSON using ASP.NET we need to use the “DataContractJsonSerializer” class as shown in the below code.”myPerson” is the class.
DataContractJsonSerializer serializer = new DataContractJsonSerializer(myPerson.GetType());
MemoryStream ms = new MemoryStream();
serializer.WriteObject(ms, myPerson);
string json = System.Text.Encoding.UTF8.GetString(ms.ToArray());
Response.Clear();
Response.ContentType = "application/json;charset=utf-8";
Response.Write(json);
Response.End();

How can you make a JSON call using Jquery ?

Let’s assume you have a MVC controller action “getEmployee” which emits out employee JSON object as shown in the below code. Please note you can always emit JSON from any server technology like WCF , ASP.NET , MVC etc as discussed in the previous questions.
public JsonResult  getEmployee()
{
Emp obj = new Emp();
obj.empcode = "1001";
return Json(obj,JsonRequestBehavior.AllowGet);
}
To make a call to the above MVC action using Jquery we need to use “getJSON” method. Below is the simple code for the same. It has three parameters:-
  1. The first parameter is the URL which emits out JSON. For instance in the below code the URL is “/Employee/getEmployee”.
  2. The next parameter helps us to pass data to the resource which emits out JSON currently it’s the MVC action. Currently we are only doing a get so the second parameter is NULL for now.
  3. The last parameter is the call back function which will be invoked once the MVC action returns data. You can see how the “getData” function just displays the “empcode” property. Because the output is in JSON it automatically converts the JSON data to javascript object.
$.getJSON("/Employee/getEmployee", null, getData);
function getData(data)
{
alert(data.empcode);
}

How can we post JSON to Server?

We can use the “post” method of jquery to send data to the server. Below is how the post method call looks like. First parameter is the URL which will accept JSON data, second is the data which we want to send and the final parameter is the call back function where we receive the response.
var mydata ={name:"Shiv",city:"Mumbai"};

$.post("/Send/Request", // URL
mydata , // Data to be sent
function(data,status){alert(data + “ “ + status);}); // Call back function

How can we post a complete HTML form in JSON format?

To post a complete HTML form we need to call “serialize” function as shown in the below code. “form1” is a HTML form. The data given by the function can then be passed to the “post” method of Jquery.”DisplayData” is a callback function to handle the output given by the server.
var Mydata = $("#form1").serialize();
$.post("/Customer/getCustomer",JSON. stringify (MyData), DisplayData);
The above posted JSON string is received at the server side “request.inputstream” , below is a simple sample code for the same.
System.IO.Stream body = Request.InputStream;
System.IO.StreamReader reader = new System.IO.StreamReader(body);
string s =   reader.ReadToEnd() ;

How can we convert JSON string in to c# object?

To convert a JSON string to a c# object we need to use the “JavascriptSerializer” class as shown in the below code.
“JsonString” is the string which has the JSON value and by using “Deserialize” we are converting the string to a c# object. Now this object which we receive is a collection of “key” and “value” pair which can be browsed and accessed in c#.
var jsonser = new JavaScriptSerializer()
var obj = jsonser.Deserialize<dynamic>(JsonString);
foreach (var x in obj)
{
    String strvalue = x[“value”];
}

What are single page applications (SPA)?

SPA means you web page has the following :-
  • Utilize the browser client power to the maximum by executing the maximum code on the client side by using javascript , HTML and CSS.
  • Rather than loading the complete page necessary HTML fragments or JSON data is loaded as the user demands.
  • Javascript which handles DOM manipulation, binding, Ajax calls are separated in to controllers thus separating views and models.
  • DOM manipulations are replaced by declarative programming.

What is Angular JS ?

Angular JS is JavaScript framework to create SPA applications. It simplifies complex javascript DOM manipulation code by providing declarative tags. This provides a clean separation between DOM manipulation logic and the HTML view.
For example below is a simple Angular code which helps us to display textbox data in the DIV tag when the user types in the textbox.
<input type=text ng-model="name">
<div>
Current user's name: {{name}}
Below is a simple video which explain Angular in 5 minutes with an example: -