Procedure Optimization Techniques

1. Set nocount on: Sql counts and returns number of records effected sql statement. Sql skip this if we set set nocount on.
2. Use Schema name with objects: Use schema name with objects. Ex. Before execution sql will first try to find sp with this name in all schemas even a cached plan available then it pics the cached plan so it degared performance.
3. Don't use sp_ prefix: sp_ format is used in master database for procedures. For sp_ pattern sql first searches sp in master database then in current session db, this degrade performance.
4. Use 1 instead of column or * : When we need to check whether a record is exist or not.
   IF EXISTS (SELECT * FROM Emp where name like '%Ram%') --Avoid
   IF EXISTS (SELECT 1 FROM Emp where name like '%Ram%') --Recmmonded
5. Use sp_executesql procedure for dynamic queries: This procedure supports parameter. A cached plan can be reused for sql queries also but if there is no change in query (single character) even of spaces. with sp_executesql you can pass parameters so query remain unchaged if you change parameter value
   Ex.
   
   --Avoid
   SET @Salary = 25000
   SET @Query = 'SELECT * FROM dbo.tblEmp WHERE salary > ' + @salary
   EXEC (@Query)
   --Recommended
   SET @Query = 'SELECT * FROM dbo.tblEmp WHERE salary = @salary'
   EXECUTE sp_executesql @Query, N'@salary int', @salary = 25
6. Avoid Cursors: Use while loop wherever possible in place of cursor as cursor consumes more sql resources.
7. Keep Transaction short as short as possible: Transaction uses locks that blocks other executions it degrades performance, so keep transaction shorter.

SQL - Concurrent operations | Problems | Solutions

Concurrent Operations: When more than one user(s) / application(s) attempt operation (select / insert / update) on same data row(s), this is called Concurrent operation.

Concurrent operations may cause below given issues -

1. Dirty Reads: If session 2 executed during the 15 second delay. Session 2 will get updated data by session 1, but then session 1 get rolled back, so session 2 got wrong data its called Dirty Read.

Session 1

Begin tran

update tb_city set regionid = 2 where id between 11 and 20

waitfor delay '00:00:15'

--Commit

Rollback

Session 2

select * from tb_city where id = 15

2. Unrepeatable Reads:  If all these session executing parallel then 4 select statements of session 1 will get different values every time.

Session 1

select * from tb_city where id between 100 and 200

select * from tb_city where id between 100 and 200

select * from tb_city where id between 100 and 200

select * from tb_city where id between 100 and 200

Session 2 update tb_city set regionid = 2 where id between 100 and 200

Session 3 update tb_city set regionid = 4 where id between 100 and 200

Session 4 update tb_city set regionid = 9 where id between 100 and 200

Session 5 update tb_city set regionid = 3 where id between 100 and 200

3. Phantom Rows: User looking to set 20 for all active records. Session 2 is running parallel added new active values. These new rows are Phantom Rows.

Session 1

update tb_city set regionid = 20 where IsActive = 1

Session 2

insert into tb_city values (201, 1)

insert into tb_city values (202, 1)

insert into tb_city values (203, 1)

4. Lost Update: user is looking to set region id to 200, but parallel session 2 set it to 100. so user 1 lost his updates.

Session 1

update tb_city set regionid = 200 where IsActive = 1

Session 2

update tb_city set regionid = 100 where IsActive = 1

--------------------------------------------------------------------------

Solutions for these problems

1. Optimistic Lock
2. Pessimistic Lock

SQL - Pivot & Unpivot

Pivot & Unpivot are relational operators.
Pivot is used to rotate table valued expression. In a table valued expression, it turns unique values of one column into multiple columns and perform aggregation on any of other columns that is required in output.
Unpivot is used to perform opposite operation of Pivot, In a table valued expression, it turns multiple columns into one column values with multiple rows.

Syntax
SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [nth pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the main source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [nth pivoted column])
) AS <alias for the pivot table>

<optional ORDER BY clause>

Example

 Table valued sql expression

Pivoted table 

Unpivot Example

SQL - Change Data Capture (CDC)

CDC - Change data capture helps us to track insert/update/delete on any table
CDC is not available in express edition, it is available in enterprise, developer & enterprise evaluation editions

Steps to enable CDC on one or more tables those you want to track

1. First enable CDC at database level
   
   sp_cdc_enable_db
2. Enable CDC at table level
   
   sp_cdc_enable_table
   @source_schema = 'dbo',
   @source_name = 'table1'
   
   
   @role_name = null
   
   
   sp_cdc_enable_table
   @source_schema = 'dbo',
   @source_name = 'table2'
   
   
   @role_name = null

Sql creates some 6 new tables and lots of procedures to track and store data of change.

For every table on which you on CDC it creates a table with name pattern for above example

cdc.dbo_table1_CT

cdc.dbo_table2_CT

Sql stores all changed data in their respective table with a column "_$operation" that have values as given below

1 - deleted record

2 - Inserted record

3 - Record before update

4 - Record after update

SQL - Useful system functions

1. Coalesce

--COALESCE ( expression [ ,...n ] )

 --Returns value from first not null column from the provided columns

  select Coalesce(FirstName,MiddleName,LastName) from tb_userdesc

2. Row_Number

 --Row_number()

 --Sorted on FirstName and given serial number

 select Row_number() over (order by Firstname) as SNo, userid, FirstName, Address_Type from tb_userdesc

 -- Records grouped on values of Address_Type then these group has sorted on UserId with row number restarted

 select Row_number() over (PARTITION by Address_Type order by UserId) as SNo, userid, FirstName, Address_Type from tb_userdesc

3. Rank

 --Rank()

 -- Generates sequence like Row_number, gives same number for same values in order by column. But for next value uses the number that actually returned by Row_number

 select rank() over (order by FirstName) as SNo, userid, FirstName, Address_Type from tb_userdesc

4. Dense_Rank

 --dense_rank()

 --Generates sequence like Row_number, gives same number for same values in order by column. For next value uses the next number in sequence

 select dense_rank() over (order by FirstName) as SNo, userid, FirstName, Address_Type from tb_userdesc

SQL - Stored Procedures

Stored procedures are precompiled sql statements.

Sql caches execution plan for stored procedures and on every execution of procedure it fetches execution plan from cache by procedure id and executes it.
So, Sql no need go through steps of syntax checking(step1) and plan selection(step2), this increases performance of application that uses the procedure.

While Sql also caches execution plan for sql statements we execute. But if we change any value sql treated it as new sql statement and cache it again with different name.
Example
Select * from employees where Name like '%Ram%'
Select * from employees where Name like '%Raj%'
Plans for above both statements will be cached but with different Ids, so can't take benefit of it.

Procedure Syntax :

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

       -- Add the parameters for the stored procedure here

       <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

       <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

    -- Insert statements for procedure here

       SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

SQL - Inserted & Deleted Tables in Trigger

'Inserted' & 'Deleted' tables are temporary table created by Sql
These tables have same structure as Main table
Their scope is in triggers
These can be used for validation or other calculation or as required in triggers

Example :
ALTER TRIGGER [dbo].[Reminder]

ON [dbo].[Filings]

AFTER UPDATE

AS

declare @FilingId as int

declare @FilingSId as int

declare @IsOnTime as bit

SELECT @FilingId = i.FilingId FROM INSERTED i

SELECT @FilingSId = i.filingstatus FROM INSERTED i

SELECT @IsOnTime = case when getdate() > A.DueDate then 0 else 1  end from vw_Companies A  inner join  INSERTED i on A.crno = i.crno

IF ( UPDATE (filingstatus) and @FilingSId =12)

BEGIN

update   Filings set isontime = @IsOnTime where filingid = (select i.FilingId FROM INSERTED i)

END;

SQL - 1NF, 2NF & 3NF

Normal Forms are fall under database normalization techniques

1NF (First Normal Form) : Each table cell should contain a single value, there should not be repetitive data in rows.

2NF (Second Normal Form) : Table should be in follow 1NF & Each column of table should depend on full primary key.
In this example We have composite key ProductId + SuplierId, here Price is depend on ProductId only not on SupplierId, so it should be moved in table where it depends on primary key that is Products table. In Product table Store is not depend on ProductId so it should be moved in new Store table

3NF (Third normal Form) : Each column should only depend on primary key.
While 2NF say column depend on full primary key.
In this example Total is depend on quantity and Price not on ProductId, Total can be calculated using Price and Quantity so it should removed.

SQL - Query Optimization Techniquess - I

Below given are some facts those can be used to optimize our query performance

• Unique Key: If there is no primary key defined on a table sql uses "Table Scan" operator.table scan operator reads records one by one until it gets required record, so this operator is costly if table having large number of rows.
  When we create primary key on table it improve performance drastically. As displayed in picture.

• Scan Vs Seek: If we've clustered / non clustered index on table then sql uses 'Index Scan' or 'Index Seek' operators.Index Scan : Touches every row whether it is qualified or not, start to end. So cost associated with this search is proportional total number of rows.
  This scan is right choice if table is small or most of the rows are qualified for condition of query.
  Index Seek : Index seek touches only rows / pages those are qualified for condition of query.
  So, if we provide some condition sql prefer to choose 'Index Seek', Even if condition provided result record number is large then optimizer change the plan uses Scan instead of Seek.
  This is right choice if table having large number of rows or qualified rows count is very much smaller(few) compare to all rows. 

SQL - Basics Sql execution plan

Below given is high level diagram shows how Sql queries get executed

Sql query processing steps : There are some steps for sql statements execution

1. Parsing : Parser checks syntax of sql statement including spellings, reserved keywords etc, and prepare sequence tree.
2. Normalization : Normalization process mainly perform binding for objects of sequence tree, checks tables and columns available or not, loading metadata, adding required information for conversations to sequence tree. Replacing views with their definitions.
3. Compilation : Compilation process include extraction of true transact statements(select, insert, update, delete etc) to pass to optimizer, because whole code may include variable declarations, assignments, loops, if conditions etc those not required optimization.