From long time being I was planning to share my experiences on SQL Server technology. Thanks to WordPress. In this blog I am sharing my own thoughts, findings, views, & some discoveries and contributes some knowledge to SQL community and getting least comments ;). I hope you all find my articles, scripts useful and help you to explore more on SQL technology.
This is a personal weblog. The opinions expressed here represent my own and not those of my employer.
Welcome to my blog on WordPress!
“What are Do’s and Don’ts while writing the T-SQL?” I heard this many time from developers or new learners or even some of the interviewers. You may get a lot many articles on Google if you search for T-SQL Best practices. In this article I have summarized Do’s & Don’t for your quick reference. I hope this will help you next time you write the T-SQL.
- Finalize or decide upon the database naming convention across the environment.
- SELECT <required fields>. This will result in less I/O and network traffic and thus better performance.
- Frequently consider advance SQL or views or table variables. But excessive use of table variable can cause memory overhead.
- Writing explanatory comments in Code. This helps others to understand the code plainly. Writing or maintain lengthy comments won’t impact the code performance
- Opt for set based approach instead of procedural approach. In exceptional cases, CURSOR can be avoided using WHILE loops.
- If your database contains heavy text based search opt for FULL text search instead of LIKE operator.
- Using SET NOCOUNT hint at the beginning of the code this improves the performance by reducing the network traffic.
- Avoid Dynamic SQL. Dynamic SQL likely to be slower than the static SQL, as it generates the execution plan every time at runtime.
- Using Error handling in stored procedures. @@ERROR or TRY CATCH.
- Use the Column list in INSERT statement.
- Using Transaction Handling (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION) if transaction involves more than one data manipulation (INSERT/UPDATE/DELETE) operation.
- Use single (not double) blank lines to separate logical pieces of T-SQL code, and do so liberally
- Use SELECT *.
- Use temporary tables in processing data. It creates a more disk IO which could hit the performance.
- Use the server side cursor
- Use Sub queries in JOIN. In Sub-query Inner query is executed for each and every row in Outer query. This can be avoided using JOINS.
- Use prefix for object “sp_”. The prefix “sp_” is reserved for System procedures.
- Use the Index Name in Query.
- Use wildcard character at the beginning of the word while searching using LIKE keyword.i causes index scan instead of index seek
- Use locking Hints in SQL statement as SQL server automatically handles the locking in a way to minimize the level of locking.
For more Do’s and Don’t please navigate through the MSDN.
Finally! MICROSOFT has delivered the most recent version of SQL Server…SQL Server 2008 R2. In this release MICROSOFT has expanded its reporting and analytics through business intelligence. SQL Server 2008 R2 delivers several breakthrough capabilities that will enable your organization to scale database operations with confidence, improve IT and developer efficiency, and enable highly scalable and well managed Business Intelligence on a self-service basis for your users.
Read more on : http://www.microsoft.com/sqlserver/2008/en/us/R2.aspxDownload
Download SQL Server 2008 R2: click here to download SQL Server 2008 R2 for free.