Query optimization tips

1. Create necessary indexes as suggested by Database Tuning Adsvisor.
2. You can also look at the dynamic views like sys.dm_db_missing_index_details

3. If you have proper indexes, go for the query execution plan to find out heavy operations. Look for Table Scans and index scans.

4. Normally, these are, unnecessary use of user defined functions in SELECT, unnecessary use of user defined functions in WHERE, unnecessary joins, and joining on NON indexed columns (should be joined with PK-FK relationship)

5. If using derived tables in the query, check for the query which is slowing down the whole query.

6. Avoid unnecessary DISTINCT and Order BY

7. Apply your WHERE clause with some mandatory conditions. For example, From date, To date, location, etc to slice down the records and output

8. Create covering Index if required.

9. Learn how to read execution plan if you dont know.
 
10. Get youself familiar with the performance moniotor and its SQL Server, Disk I/O, etc related counters

Insert File in SQL Server table

INSERT INTO dbo.Files (FileName, [File])

SELECT 'MyDoc.doc' AS FileName, *

FROM OPENROWSET(BULK N'C:\MyDoc.doc', SINGLE_BLOB) AS [File]

A simple XML parsing example

DECLARE @t TABLE ( Id INT PRIMARY KEY, booksXML XML )

INSERT INTO @t VALUES

( 1, '<books
category="novel"><book>Gone with the
wind</book><book>The lord of the rings</book></books>'
),

( 2, '<books
category="textbook"><book>linear
algebra</book><book>advanced
mathematics</book></books>' )

SELECT t.Id, x.y.value('.', 'VARCHAR(100)') book

FROM @t t

CROSS APPLY t.booksXML.nodes('books/book') x(y)

Find data difference between two schema identical tables

Declare @vSQL varchar(max)

Declare @vCols varchar(max)

Create Table vTable1 (id int, StudentID int, Dept varchar(10),BookID int)

Create Table vTable2 (id int, StudentID int, Dept varchar(10),BookID int)

Insert into vTable1

Select 1,123,'CS',465 Union All

Select 2,123,'CS',345 Union All

Select 3,223,'TE',190

Insert into vTable2

Select 1,123,'CS',465 Union All

Select 2,223,'TE',345 Union All

Select 3,223,'TE',190

-- Get the column names from schema with case statements to get 0 or 1 as
result

-- Now, this will depend upon the columns of your actual tables

Select @vCols = Stuff((Select ',case when a.' +
[name] + ' = b.'
+ [name] + ' then 1 else 0 end as ' +
[name] from sys.columns
where Object_id
= Object_id('vTable1') for XML Path('')),1,1,'')

print @vCols

-- Concatenate the @vCols with main sql

Set @vSQL = ' Select a.id,' + @vCols + ' From vTable1 a

Inner Join vTable2 b on b.ID = a.ID '

Print @vSQL

Exec (@vSQL)

 

Drop table vTable1

Drop table vTable2

Check / evaluate Multiple LIKES without Dynamic SQL

CREATE TABLE vTable (id INT, NAME VARCHAR(100))

INSERT INTO vTable

SELECT 1,'Shamas Qamar' UNION ALL

SELECT 2,'Atif' UNION ALL

SELECT 3,'Kashif' UNION ALL

SELECT 4,'Imran'

DECLARE @vParam VARCHAR(100)

SET @vParam = 'Sha,hif' 

SELECT * FROM vTable

CROSS APPLY (SELECT [value] FROM
dbo.fnSplit(@vParam,',')) b

WHERE NAME LIKE '%' + b.[VALUE] + '%'

DROP TABLE vTable

 

Query to get dependent objects

-- Get SP names which references the tables with specified schema

SELECT DISTINCT sd.object_id "object_id",

OBJECT_NAME(sd.object_id)
"OBJECT_NAME",

OBJECT_NAME(referenced_major_id)
"Ref_OBJECT_NAME", referenced_major_id "Ref_OBJECT_Id"

into #table1

FROM sys.sql_dependencies sd

where referenced_major_id in

(

select object_id

from sys.tables

where schema_name(schema_id) = 'dbo'

)order by Ref_OBJECT_Id

-- Now, get sps with all dependent tables.

SELECT distinct sd.object_id "object_id",

OBJECT_NAME(sd.object_id)
"OBJECT_NAME",

OBJECT_NAME(referenced_major_id)
"Ref_OBJECT_NAME", referenced_major_id "Ref_OBJECT_Id"

FROM sys.sql_dependencies sd

where OBJECT_NAME(sd.object_id)

in

(

select [object_name] from #table1

)

drop table #table1

Data Difference of two identical Tables

This script was written to answer a question on
sqlservercentral.com. The original poster wanted to have a dynamic stored
procedure which can tell you the columns with different values in two tables.

Here is the code;

ALTER Procedure uspTableDiff

   @pTableName1 varchar(100), -- Name of First Table

   @pTableName2 varchar(100), -- Name of 2nd Table

   @pJoinCondition varchar(Max), -- Join Condition. I am assuming that

                                 -- you will use alias a for table in

                                 -- @pTableName1 and b for

                                 -- table in @pTableName2

   @pIDColumns varchar(Max) -- Valid ID columns to diplay

As

Begin

   Declare @vSQL varchar(max)

   Declare @vCols varchar(max)

   Set @vCols = ''

    -- Check if the specified tables areidentical or

   -- not as you will match schema wise
   -- identical tables.

   if Exists(

      Select *
      from

      (

      Select [name] from sys.columns

      where Object_id = Object_id(@pTableName1)

      ) c1

      Full Outer Join

      (

      Select [name]

      from sys.columns

      where Object_id = Object_id(@pTableName2)

     ) c2 on c2.[name] = c1.[name]

     Where c2.[name] is Null or c1.[Name] is Null

   )

   begin

      Select 'Tables Not Identical'

      Return

   end 

   Select @vCols =

      Stuff((Select ',case when IsNull(a.' + [name] +

                               ','''') = IsNull(b.' + [name] + ','''')

                       then 1 else 0 end

                       as [SIM_' +   @pTableName1 + '.' + [name] + ']'

   from sys.columns
   where Object_id = Object_id(@pTableName1) for XML Path('')),1,1,'')

   -- Concatenate the @vCols with main sql

   Set @vSQL = ' Select ' + @pIDColumns + ',' + @vCols

   + ' From '+ @pTableName1 +  ' a Inner Join '

   + @pTableName2 +

   ' b on ' + @pJoinCondition

   Print @vSQL

   Exec (@vSQL)

end

Trim Non-Alpha characters from string

Here is the code to trim non-alpha characters
from the string. This script uses tally table approach.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go 

--=============================================

-- Author: Atif Sheikh

-- Create date: 28-05-2010

-- Description: Trim Non Alpha Characters

--=============================================

--Select dbo.fnTrimNonAlphaCharacters('2131231Atif123123 234234Sheikh6546')

CREATE FUNCTION [dbo].[fnTrimNonAlphaCharacters]

(

   @pString varchar(max)

)

RETURNS varchar(max)

AS

BEGIN

   Declare @vRetString varchar(max)

   Set @vRetString = ''

   ;with
   wcte as (

      Select Top(len(@pString)) *

      from (Select row_number() over (order by a.object_id) N

            from sys.columns a, sys.columns b

           ) Main

    )SELECT @vRetString = @vRetString + SUBSTRING(@pString,N,1)

     FROM wcte a

     WHERE N <= LEN(@pString)

     And (Ascii(SUBSTRING(@pString,N,1)) between 97 and 122

     Or Ascii(SUBSTRING(@pString,N,1)) between 65 and 90

     Or Ascii(SUBSTRING(@pString,N,1)) = 32)

     ORDER BY N

  Return @vRetString

END

Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness

If Someone is performing Mirroring in SQL Server 2005, he should look at the following articles...


Database Mirroring Automating Failover for Multiple SQL Server Databases


Also


Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness


Enjoy...

Parsing / breaking character separated values in a table in one query

Consider a table having following data,
Declare @vTable Table(id int,val varchar(100))

Insert into @vTable Values (1,'Atif Sheikh2 Sheikh123')

Insert into @vTable Values (2,'Asif Sheikh1 Sheikh2 Sheikh3')

Now if you want to break
the space separated values in the abovetable as;

ID     Val
1       Atif
1       Sheikh2
1       Sheikh123
2       Asif
2       Sheikh1
2       Sheikh2
2       Sheikh3

Here is the query;
;with wcte (id,val,newpos) as

(
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 newpos from
@vTable

Union all

Select b.id,substring(b.val,newpos,case when charindex(' ',b.val,newpos) = 0 then Len(b.val) else charindex(' ',b.val,newpos) -newpos end) as val

,charindex(' ',b.val,newpos) + 1 newpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.newpos <> 1

)Select ID,Val from wcte order by id

Let me explain the above mentioned query. As you can see that i have used a
recursive function CTE.

The first query in the CTE query is;
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 newpos from
@vTable
This creates the main record for parsing the string. This
loads the first value in the required data set and to use in the second part.
Here I used the newpos column to get the position to
parse the next value in the character separated string. It is just the length
of the previous parsed value. The second part is;

Select b.id,substring(b.val,newpos,case when charindex(' ',b.val,newpos) = 0 then Len(b.val) else charindex(' ',b.val,newpos) -newpos end) as val

,charindex(' ',b.val,newpos) + 1 newpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.newpos > 1

This query uses the posintion in newpos column and parses the next value. And as is is recursive, it goes on until the newpos
> 1. newpos, as you can
see in the first part of the query, is the charindex of the character which is
used to separate the values in the string.

Thats it...

Concatenating row values into one grouped column

Sometimes, small things create alot of problems. Like, a core issue discussed in here.
Consider we have a table as;

Declare @vTable Table(id int,val varchar(100))

Insert into @vTable Values (1,'Atif')
Insert into @vTable Values (1,'Sheikh')
Insert into @vTable Values (2,'Asif')
Insert into @vTable Values (2,'Sheikh')
Insert into @vTable Values (2,'Sheikh2')
Insert into @vTable Values (2,'Sheikh3')

 
Now we need to concatenate values as;
 
ID      Vals
1       Atif Sheikh
2       Asif Sheikh Sheikh2 Sheikh3
 
 
There may be alot of ways to perform this but using XML Path here is a savior. The query is;
 
Select Distinct id,
Stuff((Select ' ' + [val] from @vTable t2 where id = Main.ID for XML Path('')),1,1,'') as Vals
from @vTable Main

Blogger Buzz: Blogger integrates with Amazon Associates

Blogger Buzz: Blogger integrates with Amazon Associates

Basic Rules for coding in User Defined Functions

1.Ownership

a)The developer writing the Function must provide his / her Full Name, Create
Date, and brief Description of the Function. This commented information should
be the first part of the Stored Procedure.

-- Author : Atif Sheikh
-- Create date : 25-Aug-08
-- Description : Brief Description


2.SET Options

a)The Following options should be set in Each Stored Procedure,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON

3.Naming Conventions

a)Function name should start with ‘fn. The next alphabet after ‘fn’ should be
CAPITAL and each recognizable word in the name of Stored Procedure should start
with CAPITAL letter. For example ‘fnMyFunction’

b)Parameters of Function should start with ‘@p’. For Example, @pFunctionVariable.
The next alphabet after ‘@p’ should be CAPITAL and each recognizable word in the
name of variable should start with CAPITAL letter.

c)Return variable of Function should start with ‘@pRet’. For Example,
@pRetVariable_OUT. The next alphabet after ‘@p’ should be CAPITAL and each
recognizable word in the name of variable should start with CAPITAL letter.

d)Scope variables should start with ‘@’ sign, following with the initials of the
data type of the respective variable. The next alphabet after ‘@’ + initials of
the data type should be CAPITAL and each recognizable word in the name of
variable should start with CAPITAL letter. For example, @intIntegerVariable,
@varcVarcharVariable, @varbVarbinaryVariable, etc.


4.Data Types

a)The data Types used in the Function should be the SQL Server Native data Types.
If any User Defined data type is used in the Function, the comment must be added
in the code.

b)The length and precision should be provided in accordance with the usage of the
variable.


5.Code Indenting

a)The Code of the Function should be properly Indented and readable. The spacing
can be controlled by using TAB key (equal to 4 spaces).

b)Each Block of code should begin with BEGIN keyword and end with END keyword.


6.SELECT Statements

a)SELECT Statements should contain the column names.


7.Table Or Scalar

a)Select right type of function depending upon the return values. If it is a
single value, go for Scalar. If it is a table, then go for table valued function.


7.Performance

a)Always use function in the outer most query if your query is nested.. In this
way you will be applying
function on the final result set. Using it in the inner queries will definitely
effect performance badly. For Example;

-- Wrong approach
-- This query is applying function on all records in employee.
Select * from
(
Select EmployeeID,Name,
fnGetMinimumSalary(tblDesignation.SalaryID) as MinimumSalary
from tblEmployee
Left Outer Join tblDesignation on tblDesignation.ID = tblEmployee.DesignationID
) Main
Inner Join tblEmployeeDetail on tblEmployeeDetail.EmployeeID = Main.EmployeeID
Where tblEmployeeDetail.CountryCode = 'PK'

-- Right approach
-- This query is applying Function only on the Employee with Country Code 'PK'
Select *,fnGetMinimumSalary(tblDesignation.SalaryID) as MinimumSalary
from
(
Select EmployeeID,Name,
SalaryID
from tblEmployee
Left Outer Join tblDesignation on tblDesignation.ID = tblEmployee.DesignationID
) Main
Inner Join tblEmployeeDetail on tblEmployeeDetail.EmployeeID = Main.EmployeeID
Where tblEmployeeDetail.CountryCode = 'PK'

Basic rules for coding in Stored Procedures in SQL Server

Following are few impoortant tips for coding Stored Procedure in SQL Server;

1.Ownership

a)The developer writing the Stored Procedure must provide his / her Full Name,
Create Date, and brief Description of the Stored Procedure. This commented
information should be the first part of the Stored Procedure.

-- Author: Atif Sheikh
-- Create date : 25-Aug-08
-- Description : Brief Description


2.SET Options

a)The Following options should be set in Each Stored Procedure,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON


3.Naming Conventions

a)Stored Procedure name should start with ‘usp’. The next alphabet after ‘usp’
should be CAPITAL and each recognizable word in the name of Stored Procedure
should start with CAPITAL letter. For example ‘uspInsertProcedure’

b)INPUT parameters of Stored Procedure should start with ‘@p’ and end with ‘_IN’.
For Example, @pInputVariable_IN. The next alphabet after ‘@p’ should be CAPITAL
and each recognizable word in the name of variable should start with CAPITAL
letter.

c)OUTPUT parameters of Stored Procedure should start with ‘@p’ and end
with ‘_OUT’. For Example, @pOutputVariable_OUT. The next alphabet after ‘@p’
should be CAPITAL and each recognizable word in the name of variable should
start with CAPITAL letter.

d)Scope variables should start with ‘@’ sign, following with the initials of the
data type of the respective variable. The next alphabet after ‘@’ + initials of
the data type should be CAPITAL and each recognizable word in the name of
variable should start with CAPITAL letter. For example, @intIntegerVariable,
@varcVarcharVariable, @varbVarbinaryVariable, etc.



4.Data Types

a)The data Types used in the Stored Procedure should be the SQL Server Native data
Types. If any User Defined data type is used in the Stored Procedure, the
comment must be added in the code.

b)The length and precision should be provided in accordance with the usage of the
variable.


5.Return IDENTITY

a)In order to return the IDENTITY value from INSERT Stored Procedures,
SCOPE_IDENTITY should be used.

b)Proper OUTPUT parameter should be defined in the Stored Procedure to return the
value of IDENTITY.

6.Dynamic SQL

a)Dynamic SQL should be avoided as much as possible. But, if there is the need of
the code, execute the SQL string using ‘sp_Executesql’ with proper parameters.

b)In case of DDL Statements, EXECUTE can be used.


7.Code Indenting

a)The Code of the Stored Procedure should be properly Indented and readable. The
spacing can be controlled by using TAB key (equal to 4 spaces).

b)Each Block of code should begin with BEGIN keyword and end with END keyword.


8.TRANSACTION

a)The DML operations should be controlled by TRANSACTIONS. These Transactions
should be synchronized with the Front-End transactions to avoid any conflict.

b)Do not Set TRANSACTION ISOLATION LEVEL without consulting the Database Department


9.TRY / CATCH

a)TRY / CATCH Block should be the part of every Stored Procedure to control and
record errors.


10.Return ERROR_NUMBER

a)Proper Error Number should be returned from the CATCH Block to the application.


11.Nested SP calls

a)Nested Stored Procedure calls should be avoided. In case of necessity, the
TRANSACTION should be applied to the master Stored Procedure, calling the rest
of the Sps.


12.INSERT Statements

a)INSERT Statements should contain the column names.


13.SELECT Statements

a)SELECT Statements should contain the column names.

Check Invalid XML Message in Service Broker Queue

As we know after five failed concurrent
transactions you will get what is known as Message Poisoning after which your queue
will get disabled . Sometimes, it is required to check the invalid messages in
a service broker queue.

Begin try

Declare @vBin varbinary(max)

Declare @pBQuery nvarchar(max)

Declare @MessageXML XML

Declare vCur Cursor for Select Message_Body from dbo.BLOB_AdtQueue_Remote1_IOW

Open vCur

Fetch Next from vCur into @vBin

while @@Fetch_status = 0

begin

   Select @pBQuery = convert(nvarchar(max),@vBin)

   Select @MessageXML = cast(@pBQuery as XML)

   Fetch Next from vCur into @vBin

end

End Try

Begin Catch

   SELECT

   ERROR_NUMBER() AS ErrorNumber,

   ERROR_SEVERITY() AS ErrorSeverity,

   ERROR_STATE() AS ErrorState,

   ERROR_LINE() AS ErrorLine,

   ERROR_PROCEDURE() AS ErrorProcedure,

   ERROR_MESSAGE() AS ErrorMessage,GETDATE(),

   CONVERT(sysname ,USER_NAME())AS DBUSERNAME,

   CONVERT(SYSNAME,SUSER_SNAME()) AS SYSUSERNAME

   Select @vBin

End Catch

Deallocate vCur