Row and Column wise sum in a table using Sqlserver

CREATE TABLE [dbo].[tbl_Sample](
[ColumnA] [int] NOT NULL,
[ColumnB] [int] NOT NULL,
[ColumnC] [int] NULL
) ON [PRIMARY]

GO

SELECT  [ColumnA]
      ,[ColumnB]
      ,[ColumnC]
  FROM [SampleTest].[dbo].[tbl_Sample]

UPDATE tbl_Sample
SET ColumnC = (ColumnA+ColumnB)
FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY [COLUMNA]) AS tbl_Sample
      FROM tbl_Sample
      ) x

INSERT INTO tbl_Sample(ColumnA,ColumnB,ColumnC)
SELECT SUM(ColumnA),SUM(ColumnB),SUM(ColumnC)
FROM tbl_Sample

SELECT  [ColumnA]
      ,[ColumnB]
      ,[ColumnC]
  FROM [SampleTest].[dbo].[tbl_Sample]

SQL Query Order of Operations

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

This order holds some very interesting pros/cons:

FROM Clause

Since this clause executes first, it is our first opportunity to narrow down possible record set sizes. This is why I put as many of my ON rules (for joins) as possible in this area as opposed to in the WHERE clause:

	FROM
	contact c
	INNER JOIN
	display_status d
	ON
	(
	c.display_status_id = d.id
	AND
	d.is_active = 1
	AND
	d.is_viewable = 1
	)

This way, by the time we get to the WHERE clause, we will have already excluded rows where is_active and is_viewable do not equal 1.

WHERE Clause

With the WHERE clause coming second, it becomes obvious why so many people get confused as to why their SELECT columns are not referencable in the WHERE clause. If you create a column in the SELECT directive:

SELECT
	( 'foo' ) AS bar

It will not be available in the WHERE clause because the SELECT clause has no

t even been executed at the time the WHERE clause is being run.

ORDER BY Clause

It might confuse people that their calculated SELECT columns (see above) are not available in the WHERE clause, but they ARE available in the ORDER BY clause, but this makes perfect sense. Because the SELECT clause executed right before hand, everything from the SELECT should be available at the time of ORDER BY execution.

I am sure there are other implications based on the SQL clause order of operations, but these are the most obvious to me and can help people really figure out where to tweak their code.

order-of-execution-of-the-query

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

FROM [MyTable]
    ON [MyCondition]
  JOIN [MyJoinedTable]
 WHERE [...]
 GROUP BY [...]
HAVING [...]
SELECT [...]
 ORDER BY [...]

abstract, sealed, static, and partial in c#

1. Why do we want to have class as Abstract?

   Abstract – Abstract classes are incomplete classes, i.e., they will have combination of implemented and unimplemented methods along with data members, properties, events, delegates and indexers.
   The main idea to have class as abstract is to have only Base class that can be derivable and that will have basic functionality. As I mentioned above, we can have unimplemented method in abstract class that gives flexibility to derived classes to implement as required along with basic functionality. And also, we can’t instantiate object of abstract class. Abstract class can be with/without abstract methods. But if methods are declared as abstract in a class, that class also should be declared as abstract.
   Sealed – Sealed classes are classes that are not inheritable. Methods also can be sealed, that is, those methods declared as sealed can’t be overridable, i.e., derived classed can’t override those methods. And normal classes can’t have sealed method. Sealed keyword should be declared with overridekeyword in the derived class' method for which base class will have virtual method.
   

   Why do we want to have class and method as Sealed?

   The reason to have class and method as sealed is to stop the inheritance at one level.
   

   Where to use sealed?

   If you think that class is not to be inherited in your design, you can use class as sealed. But sealed class can inherit from interface and class. If you think virtual method cannot to be inherited in derived class at one stage, we can declare a method with sealed+override combination.
   By default structures are sealed, that is the reason structures are not supporting inheritance.
   

   Why do we want to have class and methods as static?

   In your design, if you feel that a class should have a set of methods which operate only on arguments that we pass without initiating object of that class, then we can make that class as static class (Example:System.Math). Merely call those instance methods by class name.
   

   How this class will be loaded without creating an object?

   If your code accesses any of static class’s methods, then this is the responsibility of CLR (Common Language Runtime) to load this class into memory once which is the lifetime of your application.
   Object of static class can’t be instantiated. Why? Since static class can’t have instance constructor.Static class supports inheritance but other classes can’t inherit from static class, i.e., static classes are sealed. Classes can have static methods but static classes can’t have instance member and instance methods .if, should be declared with static keyword.
   Static class is useful when you implement Singleton Design pattern.
   

   What does partial modifier indicate?

   Partial key word is to declare the class as partial meant to say it splits the classes into multiple parts under the same class name in a single namespace.
   Why? So that developers can implement the functionally for the same class parallely.
   But all combined will give one class. Each split class can have instance variable, instance methods, properties, indexers, events and delegates.
   Structures can’t have modifiers like abstract, sealed, and static whereas classes can have.
   Both structure and class can have partial modifiers.
   As I mentioned earlier, structures can have method declaration but not virtual and sealed methods. Why? Since those two are essential for inheritance. Anyhow, the structure won’t support inheritance and declaring methods using those two keywords will throw compile time errors.
2. Classes can have explicitly parameterless constructors whereas structures can’t.
3. Member variable initialization is possible in class whereas in Structures, it is not.
4. It is not possible to declare destructor in structure but in class it is possible.
5. Process of converting structure type into object type is called boxing and process of converting object type into structure type is called unboxing.Example: int a=10;
   
   Hide   Copy Code

   Object ob = (object) a;  //Boxing
    a= (int) obj;   //Unboxing

6. The “this” keyword gives different meaning in structure and class. How?
   1. In class, “this” keyword is classified as value type of class type within which it is used like inside instance constructor or instance method.
   2. In structure, “this” keyword is classified as variable type of structure type within which it is used.

Difference Between Structure and class in c#

This blog defines the difference between  Structure and class

Structure

1. Structure is a value type that is why its object is created on the stack memory.
    
2. Structure does not support the inheritance.
    
3. Structure can only have the parametrized constructor. it means a structure can not have the non-parametrized constructor,default constructor and destructor also.
    
4. The member variable of structure can not be initialized directly.
    
5. Structure object can be created without using the new keyword.(optional)
   
   Demo obj;

Class

1. Class is a reference type and its object is created on the heap memory.
    
2. Class can inherit the another class.
    
3. Class can have the all types of constructor and destructor.
    
4. The member variable of class can be initialized directly.
    
5. class object can not be created without using the new keyword, it means we have to use it.
   
   Demo obj=new Demo();

A short summary of each:

Classes Only:

• Can support inheritance
• Are reference (pointer) types
• The reference can be null
• Have memory overhead per new instance

Structs Only:

• Cannot support inheritance
• Are value types
• Are passed by value (like integers)
• Cannot have a null reference (unless Nullable is used)
• Do not have a memory overhead per new instance - unless 'boxed'

Both Classes and Structs:

• Are compound data types typically used to contain a few variables that have some logical relationship
• Can contain methods and events
• Can support interfaces

When to Use Structure and Class?

In general, classes can be used when you have more complex behavior or data. And if you think that these behaviour or data to be modified after creating an instance of class, then classes are absolute methods.

Structures can be used for small data structures. If developer feels that data members of structure cannot to be modified after creating structure, then having structure will suit.

1. Classes are Reference types and Structures are Values types.When I say Classes are reference types, basically they will contain the address of an instance variables. For example:
   
   Hide   Copy Code

   Class MyClass
   {
   Public Int DataMember;  //By default, accessibility of class data members 
      //will be private. So I am making it as Public which 
      //can be accessed out side of the class.
   }

   In main method, I can create an instance of this class using new operator that allocates memory for this class and stores the base address of that into MyClass type variable.
   
   Hide   Copy Code

   Static Public void Main (string [] arg)
   {
    MyClass _myClassObject1 =new MyClass ();
    _ myClassObject1.DataMember=10;
    MyClass _myClassObject2 =_myClassObject1;
    _ myClassObject2.DataMember=20;
   }

   In the above program, “MyClass _myClassObject2 =_myClassObject1” instruction indicates that both variables of type MyClass myClassObject1 and myClassObject2 will point to the same memory location. It basically assigns the same memory location into another variable of same type.
   So if any changes that we make in any one of the objects type MyClass will have an effect on another since both are pointing to the same memory location.
   “_ myClassObject1.DataMember=10” at this line both the object’s data members will contain the value of 10. _ myClassObject2.DataMember=20 at this line both the object’s data member will contains the value of 20. Eventually, we are accessing datamembers of an object through pointers.
   Unlike classes, structures are value types. For example:
   
   Hide   Copy Code

   Structure MyStructure
   {
   Public Int DataMember;  //By default, accessibility of Structure data 
      //members will be private. So I am making it as 
      //Public which can be accessed out side of the structure.
   }
   
   Static Public void Main (string [] arg)
    {
    MyStructure _myStructObject1 =new MyStructure ();
    _ myStructObject1.DataMember=10;
    MyStructure _ myStructObject2 =_ myStructObject1;
    _ myStructObject2.DataMember=20;
   }

   In the above program, instantiating the object of MyStructure type using new operator and storing address into _myStructObject variable of type MyStructure and assigning value 10 to data member of the structure using “_ myStructObject1.DataMember=10”. In the next line, I am declaring anothervariable_myStructObject2 of type MyStructure and assigning _myStructObject1 into that. Here .NET C# compiler creates another copy of _myStructureObject1 object and assigns that memory location into MyStructure variable _myStructObject2.
   So whatever change we make on _myStructObject1 will never have an effect on another variable_myStructObject2 of type MyStructrue. So that’s why we are saying Structures are value types.
   So the immediate Base class for class is Object and immediate Base class for Structure is ValueTypewhich inherits from Object.
2. Classes will support an Inheritance whereas Structures won’t.

Some important questions that people ask in forums is "what's the difference between a structure and a class?" and "what could be the value of a structure since we already have classes that can do the job perfectly?". Those questions could particularly be posed by a Java programmer that wants to migrate from Java to .Net. For this reason I will list in this article some of those differences to make the issue clear.

1. Structures are value types and the classes are reference types. Before proceeding to the next point, let explain the difference between the two types. Imagine this is the memory within the machine:
   
   
   
   
   Figure 1
   
   The value types are stored in the stack but the reference types are not. In fact, what could be really stored in the stack for the reference types is a pointer that targets an address at the heap level.
   
   Then the type of structure objects are stored in the stack exactly like any value type, say an integer, a double or a float. Meanwhile, memory locations could be reserved for reference types in the heap. Defining the heap and stack and the difference between them is beyond the scope of this article but nevertheless I propose this excellent Matthew article to understand the memory mechanisms.
   
   http://www.c-sharpcorner.com/UploadFile/rmcochran/csharp_memory01122006130034PM/csharp_memory.aspx?ArticleID=9adb0e3c-b3f6-40b5-98b5-413b6d348b91
    
2. Classes are usually used for large amounts of data, whereas structs are usually used for smaller amounts of data.
3. Classes can be inherited whereas structures not.
4. A structure couldn't be null like a class.
5. A structure couldn't have a destructor such as a class.
6. A structure can't be abstract, a class can.
7. You cannot override any methods within a structure except the following belonging to the type object:
    
   • Equals()
   • GetHashCode()
   • GetType()
   • ToString()
   
   And the other polymorphism technique used for structures is implementing interfaces.
    
8. Declared events within a class are automatically locked and then they are thread safe, in contrast to the structure type where events can't be locked.
9. A structure must always have the default parameter less constructor defined as public but a class might have one, so you can't define a private parameter-less constructor as in the following:
   
   struct Me    {
           private Me()// compile-time error        {
           }
       }     
   class Me         {
               private Me()// runs Ok{
            }
    
10. A static constructor is triggered in the case of a class but not in the case of a structure as in the following:
    
    struct myStructure    {
            static myStructure() 
            {
                Console.WriteLine("This is me a structure");
            }
        }
        class myClass
        {
            static myClass()
            {
                Console.WriteLine("This is me a class");
            }
        }
        class Program    {
            static void Main(string[] args)
            {
               myStructure s =new myStructure();//Nothing happen           myClass c =new myClass();//Will out put This is me a class           Console.Read();
            }
        }
     
11. The strucutre can't conatain a volatile field whereas the class can
12. You can't use sizeof with classes but you can with structures
13. Fields are automatically initialized with classes to 0/false/null wheatheras strucutres are not
14. Fields can't be directley instantiated within structures but classes allow such operations as in the following:
    
    struct myStructure    {
            publicstring x = 2;//Not allowed     }
        class myClass    {
            publicstring x = 2;//Allowed    }
     
15. Structures and classes don't adopt the same aproach for the System.Object.Equals() method.
    
    Assume the following strucutre and class:
    
    struct StructurePerson    {
            publicstring FirstName;
            publicstring LastName;
        }
        class ClassPerson    {
            publicstring FirstName;
            publicstring LastName;
        }
    Now, try this code:
    
    class Program    {
            static void Main(string[] args)
            {
                StructurePerson strX =new StructurePerson();
                strX.LastName = "Bejaoui";
                strX.FirstName = "Bechir";
                StructurePerson strY =new StructurePerson();
                strY.LastName = "Bejaoui";
                strY.FirstName = "Bechir";
                if (strX.Equals(strY))
                {
                    Console.WriteLine("strX = strY");
                }
                else            {
                    Console.WriteLine("strX != strY");
                }//This code displays strX = strY
                ClassPerson clsX =new ClassPerson();
                clsX.LastName = "Bejaoui";
                clsX.FirstName = "Bechir";
                ClassPerson clsY =new ClassPerson();
                clsY.LastName = "Bejaoui";
                clsY.FirstName = "Bechir";
                if (clsX.Equals(clsY))
                {
                    Console.WriteLine("clsX = clsY");
                }
                else            {
                    Console.WriteLine("clsX != clsY");
                }//This code displays clsX != clsY            Console.Read();
            }    }
    
    In the first strucutre the two objects are value types, they are compared depending on their values like int I = 5 and int J = 5 so I=J because they have the same value. In the contrast, in the class case of two different and distinct references, to make clsX = clsY you should use the following code:
    
                ClassPerson clsX =new ClassPerson();
                clsX.LastName = "Bejaoui";
                clsX.FirstName = "Bechir";
                ClassPerson clsY = clsX;
                if (clsX.Equals(clsY))
                {
                    Console.WriteLine("clsX = clsY");
                }
                else            {
                    Console.WriteLine("clsX != clsY");
                }//This code displays clsX = clsY

That's it.

Different Types of SQL Server Functions

Types of Function

1. System Defined Function

   These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server

   1. Scalar Function

      Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.
      System Scalar Function

      Scalar Function

      Description

      abs(-10.67)

      This returns absolute number of the given number means 10.67.

      rand(10)

      This will generate random number of 10 characters.

      round(17.56719,3)

      This will round off the given number to 3 places of decimal means 17.567

      upper('dotnet')

      This will returns upper case of given string means 'DOTNET'

      lower('DOTNET')

      This will returns lower case of given string means 'dotnet'

      ltrim(' dotnet')

      This will remove the spaces from left hand side of 'dotnet' string.

      convert(int, 15.56)

      This will convert the given float value to integer means 15.

   2. Aggregate Function

      Aggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.
      System Aggregate Function

      Aggregate Function

      Description

      max()

      This returns maximum value from a collection of values.

      min()

      This returns minimum value from a collection of values.

      avg()

      This returns average of all values in a collection.

      count()

      This returns no of counts from a collection of values.

2. User Defined Function

   These functions are created by user in system database or in user defined database. We three types of user defined functions.

   1. Scalar Function

      User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

      
       --Create a table 
      CREATE TABLE Employee
      (
       EmpID int PRIMARY KEY,
       FirstName varchar(50) NULL,
       LastName varchar(50) NULL,
       Salary int NULL,
       Address varchar(100) NULL,
      )
      --Insert Data
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
      --See created table
      Select * from Employee 
      
      

      

      
       --Create function to get emp full name 
      Create function fnGetEmpFullName
      (
       @FirstName varchar(50),
       @LastName varchar(50)
      )
      returns varchar(101)
      As
      Begin return (Select @FirstName + ' '+ @LastName);
      end 
      
      

      

      
       --Calling the above created function
      Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 
      
      

      

   2. Inline Table-Valued Function

      User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

      
       --Create function to get employees
      Create function fnGetEmployee()
      returns Table
      As
       return (Select * from Employee) 
      
      

      

      
       --Now call the above created function
      Select * from fnGetEmployee() 
      
      

      

   3. Multi-Statement Table-Valued Function

      User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

      
       --Create function for EmpID,FirstName and Salary of Employee
      Create function fnGetMulEmployee()
      returns @Emp Table
      (
      EmpID int, 
      FirstName varchar(50),
      Salary int
      )
      As
      begin
       Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
      --Now update salary of first employee
       update @Emp set Salary=25000 where EmpID=1;
      --It will update only in @Emp table not in Original Employee table
      return
      end 
      
      

      

      
       --Now call the above created function
      Select * from fnGetMulEmployee() 
      
      

      

      
       --Now see the original table. This is not affected by above function update command
      Select * from Employee 
      
      

      

Note

1. Unlike Stored Procedure, Function returns only single value.
2. Unlike Stored Procedure, Function accepts only input parameters.
3. Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
4. Like Stored Procedure, Function can be nested up to 32 level.
5. User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.
6. User Defined Function can't returns XML Data Type.
7. User Defined Function doesn't support Exception handling.
8. User Defined Function can call only Extended Stored Procedure.
9. User Defined Function doesn't support set options like set ROWCOUNT etc.