OVER() function(clause) in T-SQL

In T-SQL as everyone knows about OVER() function that is used with ranking functions. It has two options Partition By and Order By. Partition By is optional while Order By Mandatory for ranking functions.

If we think outside of ranking function than we can also use OVER() function with Aggregate functions, Next Value for (used with sequence) and Analytic functions.

OVER() function generates output according to conditions. We can reuse its values using CTEs or we can also insert this values inside the tables.

Some examples:

SELECT SalesOrderID, ProductID, OrderQty
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS [Average]    
FROM Sales.SalesOrderDetail 

SELECT SalesOrderID, ProductID, OrderQty
    ,ROW_NUMER() OVER(PARTITION BY SalesOrderID Order By SalesOrderID) AS [Average]    
FROM Sales.SalesOrderDetail  

User defined functions in T-SQL

User defined functions are set of codes which returns some value may be in terms of scalar value or table value after performing operations. These codes are reusable and made to perform any operation. It can have multiple input parameters but only one return.

Types of UDF in T-SQL

1. Scalar function:
1. deterministic
2. non-deterministic
2. Inline function:
3. Table valued function:

Scalar Functions: In this type of function it returns only one value of any data type except table. For integer data type we have two types:

1. deterministic: It means output is totally dependent on input
2. non-deterministic: That means output is no dependent on input

           CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS <data-type>
               AS
               BEGIN
                      <statements>
                      Return <variable>
               END

Inline Function: This type of function only returns table type with only single statement. This function does not have BEGIN...END.

       CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS TABLE
               AS
               RETURN (
                      <statements>
               )

Table Valued Function:  This type of function also returns table type but by creating table variable in definition with data type for each column.
              
       CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS <table-variable> TABLE (<columns with data-type>)
               AS
               BEGIN
                      <statements>
                      RETURN
              END

Pros: Precompiled which makes execution faster and reusable. A function can call another function and stored procedure also can call function. Can return table type.

Cons: We can not execute stored procedure inside function. Can not have output parameter, So can return only one value of any data type.

Stored Procedure in T-SQL

Stored Procedures are block of codes in T-SQL. It is a permanent database object in sql server. These codes are parsed and precompiled which saves execution time.In stored procedure we can multiple input parameters, multiple output parameters but can have only one return integer value.

In types of stored procedures we have main 4 types:

1. System Stored Procedures
2. User defined Stored Procedures
3. Extended Stored Procedures
4. CLR (Common Language Runtime)

In user defined stored procedures, we can categories according to parameters:

1. Stored Procedure with INPUT parameter
2. Stored Procedure without INPUT parameter
3. Stored Procedure with OUTPUT parameter
4. Stored Procedure without OUTPUT parameter
5. Stored Procedure with return 

We can not call store procedures but we have to execute stored procedures using EXEC or EXECUTE statement followed by stored procedure name with schema and parameters.

Syntax:
     CREATE PROC <procedure-name> <parameters>
      AS
      BEGIN
             <statements>
      END

    EXEC <procedure-name><parameters>

Stored procedure either can have BEGIN....END or GO at the end. We can execute stored procedure inside a stored procedure but we can not execute inside the function. We can also make function call inside stored procedure. Stored procedures are reusable and faster in execution compare to individual statements.

While creating stored procedures if we specify default values than missing parameter values will be passed from default value otherwise it will through an error in execution.

As per case of executing dynamic sql inside stored procedure requires executing stored procedure with writing permission.

Triggers in T-SQL

Triggers are set of codes which executes automatically when certain event occurs. Unlike stored procedure triggers do not have parameters to pass. We can have BEGIN and END to specify set of codes which are included in trigger definition. These events are may be DDL statements or DML statements or Login events. We have three types of triggers based on events:

1. DDL triggers
2. DML triggers
3. Logon triggers

DDL triggers: In DDL triggers we have After/For triggers. After/For triggers itself specifies after occurring of DDL statement this trigger will be fired automatically.
    CREATE TRIGGER <trigger-name> ON <Object-name>
      FOR/AFTER CREATE/ALTER/DROP
      AS
      BEGIN
            <statements>
      END

DML triggers: In DML triggers we have AFTER/FOR triggers and INSTEAD OF triggers. AFTER/FOR trigger behave same way as it does in DDL triggers. While in INSTEAD OF trigger it fired instead of particular DML statement. In DML triggers to catch data for particular DML operations we have two table access from SQL Server. These tables are called MAGIC TABLES.

MAGIC TABLES are INSERTED and DELETED. In INSERTED data will pop-up when you run INSERT and UPDATE statement. While in DELETED data will pop-up when you run UPDATE and DELETE statement. 

   CREATE TRIGGER <trigger-name> ON <Object-name>
      FOR/AFTER/INSTEAD OF INSERT/UPDATE/DELETE
      AS
      BEGIN
            <statements>
      END

LOGON triggers: LOGON trigger fires when user login into the Database system.
 
   CREATE TRIGGER <trigger-name> ON ALL SERVER
      FOR/AFTER LOGON
      AS
      BEGIN
            <statements>
      END



Triggers are database objects too that is the reason we can CREATE, ALTER and DROP. We can also Enable or Disable trigger for certain purposes.

Covering index in sql server

Covering indexes are one of the best feature in nonclustered indexes. Before covering indexes were introduced if you want multiple column search in some queries then you have to create nonclustered index on multiple column. And now specify you first column for nonclustered key value and rest column list goes with include clause.

Covering indexes are nonclustered index with include clause. Because creating wide or composite nonclustered index is lack of performance compare to covering index because it requires more storage space at top level and intermediate level of index b-tree.

CREATE NONCLUSTERED INDEX <index-name> ON <table-name> (<column-name>)
INCLUDE (<Columns>)

Above covering index is much faster in terms of execution compare to

CREATE NONCLUSTERED INDEX <index-name> ON <table-name> (<columns>)

We can take example:

Create table AB
(
    id int,
    name nvarchar(255)
)


create nonclustered index IX_AB on AB(id,name)

select * from AB where id = 1 and name = 'AA'



create nonclustered index CIX_AB on AB(id) include(name)

select * from AB where id = 1 and name = 'AA'

Indexes in sql server

When performance matters indexes comes first in terms of query performance. Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. Designing efficient indexes is paramount to achieving good database and application performance.

 Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because changes on table also make changes on indexes. If we have more select statements like we have in Data Warehouse than heavily indexes are preferable.

Indexes are one kind of pattern of storing data for faster retrieval. When you create an index on table it basically creates B-Tree (Balanced Tree) structure. It follows format for easier search of data. 

When you do not have any index on table than data get stored in random fashion which is also called table on Heap. Data retrieval are much slower when you compare table on a heap and table on an index.



In B-tree structure top level and intermediate level have nodes which stored key values based on what you are creating indexes. On the bottom level which is called leaf nodes may have data or pointers to other indexes key values.

When you search data in B-tree it starts from top to search for values and find the according values. 

In indexes we have two major types of indexes:

Clustered index and nonclustered index.

Clustered Index:

When you create a clustered index on a table than original storage structure will be thrown and it creates b-tree structure. First it sorts data on clustering key value. On leaf node of b-tree it actually stores all rows of data.

So, when you have clustered index on table and you are disabling index you are not able to retrieve any data and you cannot perform any DML operations on that table.

Having multiple clustering index doesn't make any sense. For that reason we have only 1 clustered index per table.

Creating clustered index in sql server
CREATE CLUSTERED INDEX <index-name> on <table-name> (<column-name>)

Nonclustered index:

Non clustered index is much different than clustered index. It sorts data based on your clustering key value and logically stores.

If you already have clustered index on table and you are creating nonclustered index on that table than on the leaf node you have nonclusered key value and pointer to clustered key value.

If you are creating nonclustered index on heap table than its called nonclustered index on heap. In leaf node we have nonclustered key value and pointer to heap row which is also called RID (Row Identifier).

Creating nonclustered index in sql server
CREATE NONCLUSTERED INDEX <index-name> ON <table-name>(<column-name>)

We can have clustered and nonclustered indexes on single or multiple column. Creating indexes on multiple column also known as WIDE index or composite key index.

Transactions in T-SQL

Transaction is a logical unit which contains set of statements. Transactions are made in terms of following ACID properties in database.

ACID properties is one of major requirement in database to avoid deadlocks. Atomicity, Consistency, Isolation and Durability.

• Atomicity: All T-SQL statements must be executed all together or nothing should be executed inside a transaction.
• Consistency: All transactions must leave the data in a consistent state after execution.
• Isolation: Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. ie one transaction should not modify the same data while the other transaction is still running on it.
• Durability: If a transaction is committed it must be committed on permanent basis. Once committed it persists. 

Types of Transactions in T-SQL

• Autocommit transactions: Each individual statement is a transaction.
• Explicit transactions: Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
• Implicit transactions:A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
• Batch-scoped transactions:Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server. 

Actions performed on Explicit Transactions

• BEGIN TRANSACTION: Marks the starting point of an explicit transaction for a connection.
• COMMIT TRANSACTION: Used to end a transaction successfully if no errors where encountered. All DML changes made in the transaction become permanent.
• ROLLBACK TRANSACTION: Used to erase a transaction in which errors are encountered.All DML changes made in the transaction are undone.