Invitation for T-SQL Tuesday #20 – T-SQL Best Practices
Posted by Amit Banerjee on July 5, 2011
What is T-SQL Tuesday?
T-SQL Tuesday is the brain-child of MVP Adam Machanic (blog|twitter). It happens once a month on the 2nd Tuesday and is hosted by a different person in the SQL community each time. The idea is to get the SQL Community blogging on a common topic on a single day of the month after which a round-up of all the participating posts are done by the hosting blog owner to give the SQL community a one-stop shop of some great articles on a single topic.
What is the topic for July?
The topic for July is “T-SQL Best Practices”. If you work with SQL Server, then undoubtedly you would have had to write T-SQL queries atleast once or would have had to debug the seemingly useful piece of T-SQL code written by your developers to find out where the performance bottleneck or problem was. Your post for this month’s revolving blog party could be along one of the areas:
a. A set of T-SQL best practices that you follow in your shop that or you believe that should be followed always. It could be as specific as for writing only linked server queries or writing queries for SSIS packages etc.
b. An issue that you resolved because certain T-SQL best practices were not followed.
c. A workaround that you used (like query hints) to resolve an issue where T-SQL best practices couldn’t be implemented due to involvement of a third party solution.
Why did I choose this topic?
Over the years of troubleshooting SQL performance related issues, I have found on multiple occasions that the T-SQL query in question was performing badly because certain best practices for writing that piece of code were not followed and the one responsible for the development had not foreseen that such an oversight could become a bottleneck when the data or the number of users increased. So, I thought it would be a good idea to get the SQL Community’s thoughts around best practices in this area. Sometimes, the most obvious things are the easiest to overlook!
What are the Rules?
T-SQL Tuesday has some basic rules which are pretty easy to follow.
1. Your post must be published between 00:00 GMT Tuesday July 12, 2011, and 00:00 GMT Wednesday July 13, 2011.
2. Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
3. Trackbacks should work, but if you don’t see one please link to your post in the comments section below so everyone can view your blog post.
Good-to-have
- Include a reference to T-SQL Tuesday in the title of your post
- Tweet about your post using the hash tag #TSQL2sDay
- Being creative always fetches brownie points and gets a smile from the reader!
So now it’s time for:
SELECT [content] FROM [dbo].[tblbrain] WHERE [content_type] = ‘T-SQL’
Before I wrap-up this post, a big thanks to Adam for letting me host T-SQL Tuesday for this month!
T-SQL Tuesday Topics – Febuary 2012 « Voice of the DBA said
[...] Tuesday #020 - T-SQL Best Practices [...]
TSQL Tuesday #026 – Second Chances | Dave's SQL Blog said
[...] Amit Banerjee – T-SQL Best Practices [...]
T-SQL best practice SQL server « SqlserverBlogForum said
[...] SQL server T-SQL best practice This month’s TSQL Tuesday party is being hosted by Amit Banerjee (Blog | Twitter). He is working in Microsoft and see here his online activities MSDN. The topic this [...]
dpriver said
Here are some SQL programming guidelines and best practices we collected:
* Do not use SELECT * in your queries.
* Always use table aliases when your SQL statement involves more than one source.
* Use the more readable ANSI-Standard Join clauses instead of the old style joins.
* Do not use column numbers in the ORDER BY clause.
* Always use a column list in your INSERT statements.
* Don’t ever use double quotes in your T-SQL code.
* Do not prefix your stored procedure names with “sp_”.
* Always use a SQL formatter to format your sql like Instant SQL Formatter(Free and Online)
You can check detailed explanation of those best practices in this blog:
http://www.dpriver.com/blog/2011/09/27/a-list-of-sql-best-practices/
TSQL2sday #20 – T-SQL Best Practices « Nancy Hidy Wilson's Blog said
[...] What is TSQL2sday? Back in late 2009, Adam Machanic (blog | twitter) had this brilliant idea for a monthly SQL Server blogger event (the origin of TSQL2sday) on a unified topic. This month’s event is hosted by Amit Banerjee (blog | twitter) and the selected topic is “T-SQL Best Practices”. [...]
T-SQL Tuesday #20 Wrap-up and a few smiles « TroubleshootingSQL said
[...] Invitation for T-SQL Tuesday #20 – T-SQL Best Practices [...]
Jack Vamvas said
Great subject , find post on http://www.sqlserver-dba.com/2011/07/filtered-statistics-t-sql-best-practises-for-t-sql-tuesday.html
T-SQL Tuesday and Best Practices – SQL is a Set-Based Language « Carlos Bossy's Blog said
[...] month’s T-SQL Tuesday#20 – T-SQL best Practices is a tall order to write about. Best practices can cover coding style, documentation, [...]
TSQL Tuesday #20: T-SQL Best Practices « Cleveland DBA said
[...] This month, I decided to crash the party with a submission of my own. The topic-du-jour is T-SQL Best Practices, hosted by Amit Banerjee [...]
Leka said
My Contribution:
http://leka.com.br/2011/07/12/t-sql-tuesday-20-melhores-prticas/
Nancy Hidy Wilson said
My humble contribution to this month’s event is here:
http://nancyhidywilson.wordpress.com/2011/07/12/tsql2sday-20-t-sql-best-practices/
extofer said
Gladly participated, and here are my few pointers regarding T-SQL Best Practices
http://www.extofer.com/2011/07/tsql-tuesday-20-t-sql-best-practices.html
Gabe aka extofer
Jason E Bacani, MBA said
My contribution. Many thanks!
http://key2consulting.com/Blogs/jbacani/2011/07/11/t-sql-tuesday-20-%E2%80%93-t-sql-best-practices-%E2%80%93-formatting-versus-%E2%80%9Cthe-process-worked-and-that%E2%80%99s-what-matters-most-right%E2%80%9D/
SQL Server DBA said
Filtered statistics, T-SQL best practises for T-SQL Tuesday…
I’m a DBA in a data centre with 400+ database servers , supported by 3 DBAs. Administering T-SQL best practises is one of the challenges. I’d like to review and sign off all T-SQL code , prior to production release…….
Aaron Bertrand said
Here is mine:
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/07/12/t-sql-tuesday-20-t-sql-best-practices.aspx
Amit Banerjee said
Thanks for the post Aaron. Was looking forward to your contribution.
Aaron Bertrand said
Sorry I have two entries. I didn’t see my trackback (and it seems to fall through just about every other T-SQL Tuesday).
Amit Banerjee said
No issues.
Aaron Bertrand : T-SQL Tuesday #20 : T-SQL Best Practices said
[...] T-SQL Tuesday #20 is hosted by Microsoft's Amit Banerjee (@banerjeeamit), and the topic he chose is T-SQL Best Practices. I like this topic choice because I have long been a proponent of using positive, consistent [...]
Josh Feierman said
Here’s my contribution, in case the pingback doesn’t work. Thanks for hosting!
http://awanderingmind.com/2011/07/12/t-sql-tuesday-20-best-practices-and-those-who-dont-follow-them/
TSQL Tuesday #20: T-SQL Best Practices | Erin Stellato | Erin Stellato said
[...] month’s TSQL Tuesday is hosted by Amit Banerjee ( blog | @banerjeeamit ) and topic is TSQL Best Practices. Now, I have never taken a T-SQL class, and I am not a database developer. I have spent minimal [...]
Amit Banerjee said
Thanks for the post Erin. Your posts are always a good read!
Erin Stellato said
Thanks Amit, that is high praise!
And thank you for hosting!
robert matthew cook said
Amit, thank you for being the #tsql2day host, hopefully you enjoy the experience. You can find my post at http://www.sqlmashup.com/t-sql-tuesday-20-t-sql-best-practices
Amit Banerjee said
Thanks for the post. It’s a different experience and I am enjoying being the host.
Matt Velic said
Thanks, Amit! You can find my post here: http://mattvelic.com/tsql-tuesday-20-best-practices/
Marcos Freccia said
T-SQL Tuesday #20 T-SQL Best Practices http://wp.me/pO66B-3g
Nick said
Here’s mine.
http://blog.nhaslam.com/2011/07/12/t-sql-tuesday-20-its-all-about-the-files-tsql2sday/
Thanks for hosting!
Muthukkumaran Kaliyamoorthy said
Here is mine’s. Amit thanks for gave such a great topic.
http://sqlserverblogforum.blogspot.com/2011/07/t-sql-best-practice-sql-server.html
Amit Banerjee said
Glad you liked the topic. Thanks for the post.
T-SQL Tuesday #20 – T-SQL Best Practices | sqlity.net said
[...] This post is my contribution to T-SQL Tuesday #20, hosted by Amit Banerjee (blog | twitter). This month’s topic is “T-SQL Best Practices”. [...]
Rich Brown said
Remember to use your ORDER BY clause correctly.
http://richbrownesq-sqlserver.blogspot.com/2011/07/t-sql-order-by-on-select-not-insert.html
Amit Banerjee said
Very good advice
thomasrushton said
My few thoughts… http://thelonedba.wordpress.com/2011/07/12/t-sql-tuesday-20-t-sql-best-practices/
Amit Banerjee said
Interesting thoughts and thank you for participating.
thomasrushton said
It’s the axe-wielding psychopath that makes it interesting, I know…
pinaldave said
Great great subject.
You continuously send me in my past with this subject.
My congtribution:
http://blog.sqlauthority.com/2011/07/12/sql-server-database-worst-practices-new-town-and-new-job-and-new-disasters/
Once again many thanks Amit!
Amit Banerjee said
Thank you for your post.
SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters « Journey to SQLAuthority said
[...] blog post is written in response to the T-SQL Tuesday hosted by Amit Banerjee. I know Amit personally and very big fan of his community activities. I read his blog, read his [...]
Noel McKinney said
Here’s my entry, thanks for hosting!
http://noelmckinney.com/2011/07/t-sql-tuesday-020-best-practices/
Amit Banerjee said
Thanks for posting.
DataWarehouse Explorer said
T-SQL Tuesday #20 – T-SQL Best Practices…
This first contribution to T-SQL Tuesday will be about my rules of thumb while coding T-SQL. The focus will not be on design aspects but merely on how you can write code so your co-workers and clients are able to understand what the code should be doin…
Amit Banerjee said
Sometimes these points are not followed even though people know them. Good to have them on paper.
robfarleyy said
Mine’s up, at http://sqlblog.com/blogs/rob_farley/archive/2011/07/12/my-biggest-recommendation-for-people-learning-t-sql.aspx
Amit Banerjee said
Thank you for posting.
T-SQL Tuesday #20 – T-SQL Best Practices – Formatting versus “The process worked and that’s what matters most, right?” | The Blog for Jason E Bacani MBA (@JEBacaniSQLDude) said
[...] this time around, hosted by Amit Banerjee (Blog|Twitter). The topic for the twentieth edition: T-SQL Tuesday #20 – T-SQL Best Practices. Follow the link to see all of the other contributions made by the wonderful Online SQL Server [...]