Erland Sommarskog's home page

I'm an independent consultant who provides expert consulting in the SQL Server area. I was awarded Most Valuable Professional on SQL Server by Microsoft in April 2001. This page gathers articles and utilities that I have written about and for SQL Server. Details on my consulting business are available here in Swedish and here in English. The English page includes some information about me as a person. I also have a photo gallery.

To see what has changed on this site recently, please see my web log.

Texts on SQL

This is a small collection of texts about some features in SQL Server that people frequently ask about in the newsgroups about MS SQL Server. Rather than being a FAQ with many questions with short answers, these are in-depth articles on some of my favourite topics

• The curse and blessings of dynamic SQL. How you use dynamic SQL, when you should – and when you should not. German translation of an old version available. Latest revision: 2011-06-23.
• Arrays and Lists in SQL Server. Several methods on how to pass an array of values from a client to SQL Server, and performance data about the methods. There are three articles: one for SQL 2008 which is devoted entirely to table-valued parameters, one for SQL 2005 and later (for situations when you cannot use TVPs), and one for SQL 2000 and earlier. Latest revisions: 2012-07-01 (SQL 2008) and 2010-10-10 (SQL 2005).
• Dynamic Search Conditions. How to write a stored procedure that permits users to select among many search conditions, using both dynamic and static SQL. There are two articles: one for SQL 2008, and one for SQL 2005 and earlier. French and German translations of the SQL 2005 version available. Latest revision: 2011-08-26.
• How to share data between stored procedures. Different ways of passing sets of data between stored procedures. Latest revision: 2013-03-24.
• Giving Permissions through Stored Procedures. SQL 2005 adds two new methods – signing with certificates and impersonation with EXECUTE AS – that can manage cases where the classic method of ownership chaining fails. I explain the two new methods, as well as the old one, and warn you about the pitfalls. Latest revision: 2011-12-31.
• Slow in the Application, Fast in SSMS? Ever experienced the situation that your query runs slow in your application, but when you try it in SQL Server Management Studio, the response is immediate? This article explains why this may happen, what parameter sniffing is and suggests methods to analyse and address your performance problem. Latest revision: 2013-04-12. Russian translation available.
• Build Your Own Index. This is a teaser for.. Well, follow the link and read on!
• Error Handling in SQL 2005 and Later. This is an unfinished article, which consists only of a jumpstart section that shows how to use BEGIN-TRY. The full article will appear ...sometime. The article also gives you links to my old articles for error handling in SQL 2000.
• Connecting to MS SQL Server from Unix. A brief summary of what's available. Since I don't work with Unix myself, it's not extremely well-informed, but people have kept asking me about this. Latest revision: 2011-12-15.

My SQL Server Wishlist

I've compiled a list of Connect items related to development that I wish to see implemented in SQL Server, the sooner the better. Read it and vote for the suggestions you like! Latest revision: 2013-05-18.

Utilities and Code

All of this is code in the public domain.

• AbaPerls. A collection of tools for developing and deploying stored procedures, tables and other SQL Server objects. You get a preprocessor, the ability to load stored procedures and get full object checking directly. You will be able to build to update scripts from Team Foundation Server or SourceSafe and a lot more. AbaPerls is a tool that I use in my daily work, and it's very much a living tool. Current label is 1.0.0262, released February 2012.
• beta_lockinfo and aba_lockinfo. This is two stored procedures for lock-monitoring, giving you a good snapshot so you can see who is blocking whom and what their last command was. beta_lockinfo is for SQL 2005 SP2 and later, while aba_lockinfo exists in five different versions, supporting SQL 2005 RTM/SP1 (which it does so-so), SQL 2000, SQL 7 and SQL 6.5. Latest version: Version 15, 2012-11-18.
• Win32::SqlServer. Access SQL Server from Perl through OLE DB. Current version is 2.008, released in September 2012.
• sp_sqltrace. A stored procedure that takes an SQL batch as a parameter, sets up a trace, runs the batch, and then summarises the trace. On SQL 2008 and later, you also get wait-stats information per statement. Optionally, you can get the execution plans for the statements. Rather than tracing your own process, you can snoop another spid. It is also possible to reanalyse a previously collected trace. A very useful tool for performance tuning developed by Lee Tudor that I am proud to host on my site. Latest version: Version 4, 2012-10-27.

Contact

You can reach me on esquel@sommarskog.se.