Performance improvement in SQL Server

Performance improvement 

• Use shortest data type possible
• Make sure the data types of the columns participating in a JOIN are of same type
• Use Calculated columns instead of on the fly complex calculations 
• Consider using RCSI (read committed snapshot isolation) to preclude readers from blocking writers (writers will still block other writes but this is much graceful than using NOLOCK hints)
• Partition Alignment (64K or 1024k) and block size (or cluster size) of 64KB
• Keep the transactions as short as possible to preclude blocking/deadlocking/TLog  bloating issues
• If they need to trace, use server side trace
• If it is Ad hoc workload, consider turning on the "Optimize the Adhoc Workload" option
• Teach them how to figure out missing indexes from DMVs but add them only necessary - tweak the existing indexes first (make them composite and include columns)
• Minimize queries that use text searches, rather use full-text indexes on those columns
• Familiarize themselves with In-Memory OLTP feature
• Avoid float data types if you need accuracy
• Avoid nested views
• Familiarize with different granular levels of compilation
• Pitfalls of using HEAPS
• Revisiting the index configuration every few months
• Familiarize themselves with Blocked Process Report
• Using SET NOCOUNT ON, avoiding sp prefix, etc
• Parameter Sniffing 
• Using triggers sparingly
• Use of sp_executesql instead of EXEC
• Pros and cons of using natural keys vs surrogate keys
• Data growth and its adverse effects on query time and ways to mitigate them
• Avoiding all hints unless absolutely necessary
• No user prompts within explicit transactions
• Peer review and unit test the code before submission
• Caching rarely changing lookup table data on the client side to mitigate network chatter

Refer from

Use Row_number() in SQL Server

SQL Scenario -I

Table A Table B
Id Id
1 1
2 -
- 3
4 4
Expected Output:
Id Description
1 1 Has both in tables
2 2 Has in A table Only
3 3 Has in B table Only

4 4 Has both in tables.

create view UV_Result
as
select a.id,a.id + '  ' + 'Has both in tables' as [DESCRIPTION] from table1 a,table2 b where a.id=b.id and a.id<>'-'

Create view UV_TestA
as
select Distinct id as [TableAID],
      RowNum = row_number() OVER (ORDER BY (SELECT 0))
from  table1 a
   

Create view UV_TestB
as
 select   id,
     RowNum = row_number() OVER (ORDER BY (SELECT 0))
from     table2

create view uv_Final
as
select id,DESCRIPTION from UV_Result
union ALL
select rownum,CONVERT(varchar(50),CONVERT(bigint,rownum)) +' ' + 'HAS TABLE B'  from UV_TestA where TableAId='-'
UNION ALL
select rownum,CONVERT(varchar(50),CONVERT(bigint,rownum)) +' ' + 'HAS TABLE A'  from UV_TestB where Id='-'

select * from uv_Final order by id

add Minutes to DateTime in Sql Server

We can use DATEADD() function like below to add minutes to DateTime in Sql Server. DATEADD() functions first parameter value can be minute or mi or n all will return the same result. Below example shows how we can add two minutes to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(minute,2,GETDATE()) 'Now + 2 Minutes'
SELECT GETDATE() 'Now',
           DATEADD(mi,2,GETDATE()) 'Now + 2 Minutes'
SELECT GETDATE() 'Now',
           DATEADD(n,2,GETDATE()) 'Now + 2 Minutes'

add Hours to DateTime in Sql Server

                We can use DATEADD() function like below to add hours to DateTime in Sql Server.                
                DATEADD() functions first parameter value can be hour or hh all will return the same result. Below example shows how we can add two hours to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(hour,2,GETDATE()) 'Now + 2 Hours'
SELECT GETDATE() 'Now',
           DATEADD(hh,2,GETDATE()) 'Now + 2 Hours'

add Seconds to DateTime in Sql Server

               We can use DATEADD() function like below to add seconds to DateTime in Sql Server. DATEADD() functions first parameter value can be second or ss or s all will return the same result. Below example shows how we can add two seconds to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(second,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
           DATEADD(ss,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
           DATEADD(s,2,GETDATE()) 'Now + 2 Seconds'

IIF funciton in SQL Server 2012 and give an Example

If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.

Create a Table

CREATE TABLE SimpleTable (ID INT, NAME VARCHAR(10))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, NAME)
SELECT 1, 'LAKSHMI'
UNION ALL
SELECT 2, 'NARAYANAN'
UNION ALL
SELECT 3, 'NARAYANAN'
GO

UPDATE SimpleTable
SET Gender = IIF(NAME = 'NARAYANAN', 'LAKSHMI', 'NARAYANAN')
GO
SELECT *
FROM SimpleTable
GO

Example for CASE Function in SQL

Create a Table

CREATE TABLE SimpleTable (ID INT, NAME VARCHAR(MAX))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, NAME)
SELECT 1, 'LAKSHMI'
UNION ALL
SELECT 2, 'NARAYANAN'
UNION ALL
SELECT 3, 'NARAYANAN'
GO

CASE Funciton

UPDATE SimpleTable
SET Gender = CASE NAME WHEN 'NARAYANAN' THEN 'LAKSHMI' ELSE 'NARAYANAN' END
GO
SELECT *
FROM SimpleTable
GO

Example for REPLACE Function in SQL

Create a Table

CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, 'female'
UNION ALL
SELECT 2, 'male'
UNION ALL
SELECT 3, 'male'
GO

Replace male into female using UPDATE Query

UPDATE SimpleTable
SET Gender = REPLACE(('fe'+Gender),'fefe','')
GO
SELECT *
FROM SimpleTable
GO

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = '<Sp_name>'

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%<search_word>%'

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

SQL Constraints

SQL Constraints

SQL constraints are used to specify rules for the data in a table.  If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Syntax for SQL Constraints

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
);

In SQL, we have the following constraints

NOT NULL - Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value when specified none for this column

Column Exists or Not in SQL Server

Column Exists or Not in SQL Server

CREATE FUNCTION Axfn_ColumnExists
(
 @TableName VARCHAR(100) ,
@ColumnName VARCHAR(100) )
 RETURNS VARCHAR(100)
AS
BEGIN
 DECLARE @Result VARCHAR(100);
 IF EXISTS
(
 SELECT 1 FROM INFORMATION_SCHEMA.Columns
             WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName ) BEGIN
 SET @Result = 'Already Exsits'
 END
 ELSE
 BEGIN SET @Result = 'Not Available, You can create now!'
 END
 RETURN (@Result)
 END
GO

SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

-- Disable all the constraint in database

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Refer this link

SQL SERVER – Create Unique Constraint on Table Column on Existing Table

by using UNIQUE for Unique constraint.

Syntax:

   UNIQUE([col name])

Example:

USE tempdb
GO
-- Create Table
CREATE TABLE Table1 (ID INT, Col1 VARCHAR(100))
GO
-- Alter Table Create Constraint
ALTER TABLE Table1
ADD CONSTRAINT UX_Constraint UNIQUE (Col1)
GO
-- Clean up
DROP TABLE Table1
GO

Parts in CTE

Common Table Expression contains three core parts

The CTE name (this is what follows the WITH keyword) The column list (optional) The query (appears within parentheses after the AS keyword) The query using the CTE must be the first query appearing after the CTE.

Use CTE in SQL Server

Syntax of CTE:With Parameter

With T(<col Name>, <col name1>, <Col name2>)  --Column names for Temporary table
AS
(
SELECT A.<Col name>, E.<col name1>, E.<col name2> from <table_name> A
INNER JOIN <table_name> E ON E.<col name> = A.<col name>
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.[col name]  > 50
ORDER BY T.<col name>

Use CTE in SQL Server

Syntax of CTE:Without Parameter

WITH MyCTE AS (SELECT c.[Col name] FROM [table_name] pc INNER JOIN [table_name] c ON c.[Col name] = pc.[Col name]) SELECT cte.[Col name], p.[Col name] FROM [table_name] p INNER JOIN [table_name].[Col name] ea ON ea.[Col name] = p.[Col name] INNER JOIN MyCTE cte ON cte.[Col name] = p.[Col name] INNER JOIN [table_name].PersonPhone ph ON ph.[Col name] = p.[Col name];

CTE in SQL Server

CTE in SQL Server

The common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.  You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL DB Formatting

SQL DB Formatting

Use upper case for all SQL keywords  SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.  Indent code to improve readability  Comment code blocks that are not easily understandable  Use single-line comment markers(–)  Reserve multi-line comments (/*.. ..*/) for blocking out sections of code  Use single quote characters to delimit strings.  Nest single quotes to express a single quote or apostrophe within a string  For example, SET @sExample = ‘SQL”s Authority’  Use parentheses to increase readability WHERE (color=’red’ AND (size = 1 OR size = 2))  Use BEGIN..END blocks only when multiple statements are present within a conditional code segment. Use one blank line to separate code sections.  Use spaces so that expressions read like sentences.  Format JOIN operations using indents  Also, use ANSI Joins instead of old style joins   Place SET statements before any executing code in the procedure.

SQL DB Structure

SQL DB Structure

Each table must have a primary key.  In most cases it should be an IDENTITY column named ID.  Normalize data to third normal form.  Do not compromise on performance to reach third normal form. Sometimes, a little de-normalization results in better performance.  Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead.  In VARCHAR data columns, do not default to NULL; use an empty string instead.  Columns with default values should not allow NULLs.  As much as possible, create stored procedures on the same database as the main tables they will be accessing.