T-SQL best practice SQL server

SQL server T-SQL best practice

This month’s TSQL Tuesday party is being hosted by Amit Banerjee (Blog | Twitter).

He is working with Microsoft and see here his online activities MSDN. The topic this month is T-SQL best practice. It’s the both DBA and developers Topic/Job.

T-SQL best practice

DOs and Don’Ts

• Check/Validate your conditions out of the loop (While/If) statement.

• Specify exact data type size

Ask the customer and specify the correct data type size what they really need.  Otherwise you will end up with a problem when your data grow larger.

E.X For inserting a 4 digit character don’t create CHAR (400) just create CHAR (4)

• Always write a query using seek-able operator in your WHERE clause.

http://msdn.microsoft.com/en-us/library/ms172984.aspx

• Try to avoid Functions and date time functions when you write Quires i.e. Sproc .

• Avoid an index hint

Use the index hint if you really know it’ll improve the performance otherwise avoid it.

SELECT * FROM emp  WITH (INDEX (ix_n)) WHERE n ='server'

• Don’t create too many indexes.

Create the indexes it’s really useful otherwise don’t. I have seen a table has 5 non clustered indexes with mostly same column definitions for a particular table/SP and the table size is 4 GB and the index size are nearly 9 GB.

• Don’t use wildcard characters at the beginning of the word

• Always write seekable codes.

SELECT ID FROM <table_name> WHERE NAME LIKE '%kumar'

-- use below

SELECT ID FROM <table_name> WHERE NAME LIKE 'muthu%'

• Use “order by” and “distinct” – sorting, really when there is a need.

If you have a doubt on that, you can test run the code with/without sorting, you come to know, how it perform well.

• Normalize your tables

The best design will give the best performance. Normalize your tables before going to your project. I.e. my second point (Specify correct data type size etc…)

• Try to write set based queries minimize iteration/Cursors as much as you can.

• Use the batch statement if you’re going to delete a huge number of records from the table.

Write a batch statement if you’re going to delete huge records it’ll minimize the log file size. Most importantly inform to the DBA team before going to delete otherwise we will get a call from a customer the DB was down 🙂

• Use ANSI-Standard Join clauses instead of the old style joins.

SELECT e.no,ed.name
FROM employee e, employee_details ed,
WHERE ed.name ='muthu'

-- use below

SELECT e.no,ed.name
FROM employee e INNER JOIN employee_details ed
ON emp.no=ed.no
WHERE ed.name ='muthu'

• Test your data and the indexes after migrating one server to another server especially 2000  to 2005/2008.

• Create a filtered index if you know the query often fetch the unchanged data from a big table.

A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance too.

CREATE NONCLUSTERED INDEX I_fi_test
ON dbo.test(ID,joinDate,Relievingdate)
WHERE joinDate > '2001/01/01';

• Use NO LOCK hint if you have no problem with dirty reads or use RCS isolation.

See my friend’s article http://sqlvibes.blogspot.in/2011/07/beware-of-nolock.html

• Try Create a clustered index (Primary key) for all mostly used tables

Try to create a clustered index for all the tables. I had seen most of my big tables are not defragmented because of the tables are created primary key with NON clustered index. I asked the reason they said we don’t know who has created.

Use SELECT 1 instead of SELECT *

Use SELECT 1 when you’re checking the records are available at your table.

IF EXISTS (SELECT 1 FROM <table_name> WHERE id=100)

• In general rule retrieve the columns as you want don’t use “SELECT *”

• Write a required column in the SELECT statement as you want don’t SELECT all the columns.

E.X

SELECT * FROM <table_name>

-- use below

SELECT c1,c2 FROM <table_name>

• Don’t write your procedure name starting with “SP_”

SQL server searches all the producers one by one starting with “SP_” in the database including system procedure then finally found yours. It causes minor overhead, not really make much improvement.

• Use WHERE condition as much as possible

SELECT * FROM emp WHERE Name ='SERVER'

• Use TRY-Catch for error handling

See my procedure has written using Try-Catch.

• Try to avoid dynamic SQL

It’ll generate SQL injections if you’re not writing it effectively.

http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

• Always write your codes/SProcs with descriptions and comments.

I hope this best practice will help for the developers/coders. Thanks to Amit has given this great topic.

Edit: Drop me a comment or email if you have / find any t-SQL best practice I will add that too.