Requirement Some time back I presented a PowerShell script which extracts T-SQL code from RDL files. Remember that I created this script to assist me in doing code reviews of ad-hoc SQL snippets which were embedded in the report definition files. Another common usage of ad-hoc T-SQL code is within SSIS...

One of the things I do in my day job at Microsoft Services is to review database design and T-SQL code. Now, when it comes to code, there is a challenge just to find ALL the possible code which hits our database engine. This is because a non-trivial amount of T-SQL code is not in the form of Stored Procedures...

Scenario Today my customer told me that he is facing an error message ‘Cannot resolve the collation conflict’ when executing an JOIN between two tables: a base table and a temporary table. They had recently created the database afresh from scripts and were facing this issue since. On the existing ‘good...

Today there was a question on the #sqlhelp Twitter hashtag: “Is it possible to get UPDATE/SELECT/INSERT/DELETE statement counts from SQL Server?” Implementation This is a perfect use case for the SQLDOM parser a.k.a. Microsoft.SqlServer.TransactSql.ScriptDom. I put together a sample C# application to...

TechEd 2013 was a grand success! Thank you – those of you who stayed till 6:15PM at the Pune session – and even more to those who engaged me in Q&A till 7PM that evening I was very impressed and happy to see people interested in my talk, even though it was at the end of a very long day for most....

Some of you might be aware of the above namespace , which holds an implementation of a first-class T-SQL parser. In this post I would like to explain some of the complexity you will face when dealing with the ScriptDOM yourselves, typically using Visitor pattern. Case Study Our objective in this case...

Background Constraints in SQL Server are of the following types: CHECK constraints DEFAULT constraints Foreign key constraints NULLable constraint UNIQUE constraint PRIMARY KEY constraint BTW, if you are wondering what a NULLable constraint is, it is the formal representation of a NULL / NOT NULL definition...

Today during a discussion a point came up around the role of PRINT statements in production code. While most data access today is routed through a data access layer (typically .NET or JDBC) and is focussed on consuming result sets (or executing U/I/D nonquery stuff) we were wondering on what you use...

This is really not a T-SQL anti-pattern as much as it is a database design issue, but we see it so often that it’s worthwhile bringing it up and clarifying things. For illustrating the scenario, let’s examine the table Person.Contact in the AdventureWorks database. It has 2 columns called FirstName and...

Scenario A common requirement for enquiry queries on an OLTP database is to have search criteria which are very specific ('get me details for for OrderID = NNNN') and also the occasional reports which ask for all the orders ('get me all the orders, no questions asked'.) Here is a sample from AdventureWorks...

Scenario It is a pretty common situation to have transaction date-time stored in a DATETIME field. The problems start with the fact most applications used GETDATE() or some such equivalent at the client side to record the order date-time stamp. So a typical entry for an OrderDate would actually end up...

This one is even more common and therefore has a high impact: the usage of the above functions in WHERE clause predicates. I've examined each in some detail below. Here's a typical example I see, based off the AdventureWorks database. Scenario To illustrate the effect of this example, I have added an...

In the course of my work as a SQL Server Premier Field Engineer, we tend to see a lot of patterns in code. Some of these patterns do not lend themselves to optimal performance. This post is the first in a series of such 'how not to code' tips which would hopefully help some T-SQL developers out there...