T-SQL best practice SQL server

by Muthukkumaran kaliyamoorthy Published on: July 12, 2011
Comments: 13 Comments
Tags: ,
Categories:Performance, SQL party

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.

Transact SQL is the best tool to improve the SQL server overall performance. You write your code efficiently then you no need to spend lots of money to buy extra hardware or RAM etc… So the coders/developers have to know the best practice.

He or She does a good job (writing correct codes) then, There is no work for the DBAs. There is lots of T-SQL best practice available on the Internet. Here I’m going to share mine. The first 15 bullet points are personally I have faced and tuned in my environment. I will have planned to write each post for all 15 points with examples.

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 VARCHAR (400) just create VARCHAR (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 avoids 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 Instead of below

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

 

  • 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 joins instead of sub query

Use the joins instead of sub query it’ll give better performance.

 

SELECT * FROM TBL WHERE N IN (SELECT N FROM TBL1)

-- use Instead of below

SELECT A. * FROM TBL A JOIN TBL1 B
ON (A. N=B. N)

 

  • 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 Instead of 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.

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

  • 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 Instead of 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.

  • 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.

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Share this
13 Comments - Leave a comment
  1. vinoth says:

    Great best practice muthu.I liked the most of the points. Filter index available on SQL Server 2008 onwards.

  2. Muthukkumaran says:

    Thanks dude.

  3. Muthukkumaran says:

    Wow great 40 Responses from the DBA and developers Click here and read it T-SQL best practice

  4. Muthukkumaran says:

    T-SQL Tuesday #20 Wrap-up and a few smiles

    T-SQL Tuesday #20 Wrap-up

    23 posts from the worldwide. Read and enjoy it.

  5. Anonymous says:

    Excellent post.Great points about T-SQL best practice.

    Developers need to follow it.I'm going to share this with my team.

  6. Yunus says:

    Can we change the default schema ?
    Such that whenever we create a table in that database we get table with that schema only not with dbo schema

  7. Muthukkumaran says:

    Thanks for your comments.

    Reader please add the T-SQL best practice if i missed anything.
    OR)
    Whatever you feel it's best practice.It'll be useful for others.

  8. Woodrow Doino says:

    Once I initially commented I clicked the -Notify me when new feedback are added- checkbox and now each time a remark is added I get four emails with the identical comment. Is there any means you may remove me from that service? Thanks!

  9. ชุดนอนเซ็กซี่ says:

    I am glad to be a visitor of this utter site ! , regards for this rare info ! .

  10. Hazel Servino says:

    I’m not sure where you are getting your info, but great topic. I needs to spend some time learning much more or understanding more. Thanks for magnificent info I was looking for this info for my mission.

  11. sai krishna says:

    hi muthu, can you help me out in building concepts on indexes and some important topics in sql server.
    Thanks in advance.

Leave a comment

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

Current ye@r *


Welcome , today is Saturday, August 30, 2014