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