Sunday 25 August 2013

SQL SERVER - SET VS SELECT


SET - It is designed to assign the values.
SELECT - It is designed to Select the result set.

Example 


DECLARE @S INT ,@D INT

SET @S = 2
SET @D = 4


SELECT @S = 1,@d = 3

Error on declare like follow.

SET @S = 2,@D = 5


SET 
  1. It is ANSI Standard.
  2. More than one variable can't be assign using one set statement.
  3. It will assign only scalar value, if multiple row are return from query than raise error.
  4. If the return query set doesn't have values for the column then SET will default the value as NULL.
  5. It will execute on each and every statement of assign.so time taken for execution.
  6. Performance wise slow when assign a value to variable when compare to SELECT.
SELECT
  1. More than one variable can be assigned at a time.
  2. If more than one value is returned from query then , it will assign one value from that result.
  3. It will not default assign the value NULL. when the query returns no records.
  4. Performance wise faster than SET, Because it will execute to assign any variables at a time.


From this article I hope you can understand the difference between the SET and SELECT.

Linq - Parallel Query and Cross Apply


In this article we are going to see how to create a parallel query execution and how to do the cross apply in Linq.

Parallel Query Execution :

/* Generate Even Numbers using parallel Extension. Execution of query in parallel to generate the Even number */
You can see the output of this program that the even numbers are print in UnOrder manner due to the parallel execution of query.

IEnumerable<int> values = ParallelEnumerable.Range(1, 40).Where(x => x % 2 == 0);
foreach (int i in values)
   Console.WriteLine(i);

Output:
2
4
22
32
6
12
24
34
8
14
26
36
10
16
28
38
18
30
40
20

Cross Apply:
Some times we have a requirement that cross apply the collection and get the value in another collection.
this can be done by SelectMany of one collection for each value of select in another collection.


  /* Cross Apply in Linq */
  var list1 = new List<string>() { "PC", "LAP", "TAB" };
  var list2 = new List<string>() { "HomeTheatre", "Graphics Card", "Moderm" };

  var cross = list1.SelectMany(x => list2.Select(y => x + "+" + y));
   foreach (string crs in cross)
        Console.WriteLine(crs);


Output:

PC+HomeTheatre
PC+Graphics Card
PC+Moderm
LAP+HomeTheatre
LAP+Graphics Card
LAP+Moderm
TAB+HomeTheatre
TAB+Graphics Card

TAB+Moderm


From this article I hope that you can learn the some basics parallel query execution and how to do the cross apply of Linq.

LINQ - usage of SequenceEqual method



In this article we are going to the usage of SequenceEqual method. Let we see the example how it is works.


            /*Find the equal of two sequence */
            var seq1 = new List<int>() {1,6,3,21};
            var seq2 = new List<int>() {4,6,21,3,5};
            var seq3 = new List<int>() { 3, 6, 21, 1 };
            var seq4 = new List<int>() { 1, 6, 3, 21 };


            Console.WriteLine(seq1.SequenceEqual(seq2));
Output:
     False

The output for the above code is false, because the values present in seq1 and seq2 is different.



            Console.WriteLine(seq1.SequenceEqual(seq3));  
Output:
      False

The output for the above code is false, because the values present in seq1 and seq3 is same but are in different sequence.

         

            Console.WriteLine(seq1.SequenceEqual(seq4));
Output:
       True

The output for the above code is True, because the values present in seq1 and seq4 is same and also in sequence.

From this article you can learn the usage of SequenceEqual in Linq.

Linq - Find the size of a Directory


In this article we are going to see the usage of EnumerateFiles and EnumerateDirectories in Linq and how it is working.

EnumerateFiles() : Will enumerate the files present in the folder 
EnumerateDirectories() : Will enumerate the Directory present in the folder.

/* Directory Size*/
   long _size = Size(@"D:\flot", true);
   Console.WriteLine("Directory Size in MB : {0:N2} MB", ((double)_size) / (1024 * 1024));
          

   static long Size(string path, bool includesubdir)
   {
            DirectoryInfo info = new DirectoryInfo(path);

            long _size = info.EnumerateFiles().Sum(x => x.Length);

            if(includesubdir)
            _size+=info.EnumerateDirectories().Sum(x => Size(x.FullName,includesubdir));
           
            return _size;
    }



Output :

Directory Size in MB : 2.14 MB

LINQ - Usage of Convert All Method



In this article we are going to see a usage of convert all method. If we want to convert the data type of values present in the collection then we can do it through ConvertAll.



/* Convert All method */
   var results = new List<object>() { 1, 0, 1, 1, 0, "true" };
   List<bool> boolresults = results.ConvertAll(x => Convert.ToBoolean(x));
   foreach (bool res in boolresults)
             Console.WriteLine(res);



In this example we have a list of values which are in object type , so we are converting all to the boolean type. Now the value 1 and "true" are cast as Bool True. and 0 as False.

Output:

True
False
True
True
False
True

I hope from this you can understand the usage of ConvertAll() method.

Linq - Usage of Union method


In this article we are going to see the usage of union method in Linq, It is same as Union the two collection sets.

For Example 
consider a collection seq1 which have numbers 1,6,3,21
consider a collection seq2 which have numbers 4,6,21,3,5

By union this two collection output is 1,6,3,21,4,5. How we can achieve this using Linq.

var seq1 = new List<int>() {1,6,3,21};

var seq2 = new List<int>() {4,6,21,3,5};

/*union of two sequence */
var diff=seq1.Union(seq2);
foreach (int d in diff)
     Console.WriteLine(d);


Output :
1
6
3
21
4
5

From this article you can learn the usage of Union in Linq.

LINQ - usage of Except method in Linq

In this post let we see the difference between the collections , using Linq. Let we take a two examples first is compare with collection then with collection two.

var seq1 = new List<int>() {1,6,3,21};
var seq2 = new List<int>() {4,6,21,3};


Now let take a example of two collections, following example is used to find the values of collection seq1 which is not present in collection seq2.

/*Difference between two sequence */
   var diff=seq1.Except(seq2);

    foreach (int d in diff)
       Console.WriteLine(d);

Output : 

1

Now let take a example of two collections, following example is used to find the values of collection seq2 which is not present in collection seq1.

/*Difference between two sequence */
   var diff=seq2.Except(seq1);

    foreach (int d in diff)
       Console.WriteLine(d);

Output : 

4



From this article i hope you can understand how to get the difference between the collections.