Stored procedure debugging is a feature that has existed in SQL Server Management Studio (SSMS) for some time (since before SQL Server 2005 I believe) yet in my experience not many folks make use of it, indeed many folks aren’t even aware it exists. In this post I shall try and shine a light on what I believe is an underused feature in the SQL ...

There’s a very nifty feature of T-SQL that, in my experience, not everyone knows about but is handy for generating surrogate key values as part of an ETL routine. I myself had to ask on Twitter what the syntax was because I couldn’t remember it (thanks to Matija Lah for reminding me) so I think its worth blogging in case I forget it again. To ...

My next guest is Doug Lane, who has been blogging for the past few years on his website: http://www.douglane.net/. Doug is also a speaker who has spoken at SQL Saturdays as well as the SQL PASS Summit, and as I write these interview questions was on his way to speak at the SQL PASS Business Analytics conference in 2013. I also liked quite a few of ...

In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called Composable DML and also outlined one of its limitations; namely that data from the OUTPUT cannot be aggregated prior to insertion. Composable DML does have some useful scenarios however and one of those is in capturing and storing values that ...

Even the simplest data integrity rules are not easy to implement in the database if instead of individual rows we need to deal with groups or subsets. For example, making sure that a column value is unique in a table is as trivial as creating a unique constraint or index. However, what are we supposed to do if we do not want to store a ...

One of the things that separates a good programmer from a great one is a firm understanding about what is going on inside the computer. For some programming languages, it is very obvious what is going on inside the computer because you are working at a very low level. For example, if you are a C/C++ programmer writing an OS, you will know a lot ...

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests. Implementation The following class extends SqlCommand and rolls back active transactions after timeouts:     public static class ...

After the timeout, we may sometimes, but not always, end up with an outstanding transaction. If this is the case, we need to rollback the transaction ourselves. Otherwise the consequent commands which use the same connection might not work as expected. Let us debug through some client code and see what is happening in more ...

I was checking my Twitter feed on my way in to work this morning and was alerted to an interesting blog post by Valentino Vranken that highlights a problem regarding the OLE DB Source in SSIS. In short, using double-dash comments in SQL statements within the OLE DB Source can cause unexpected results. It really is quite an important read if you’re ...