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.
(Added June 2001, Completely revised: April 2006. Most recent update: December 2008.)
German
translations of an old version available.
- 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. (Added March 2003.
Version for SQL 2005 added March
2007, major revision and addition
of article for SQL 2008: January 2010)
- 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. (Added May 2003. Most recent update:
November 2009.
French and
German translations
of the SQL 2005 version available.)
- How to share data between stored procedures.
Different ways of passing sets of data between stored procedures.
(Added July 2001, Most
recent update: January 2010)
- 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.
(Added February 2006)
- Build Your Own Index. This is a
teaser for.. Well, follow the link and read on! (Added
November 2009.)
- 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. (Added July 2003. Most recent update:
November 2009 )
- 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.
(Added Jan 1999, Latest update: June 2008)
My SQL Server Wishlist
I've compiled a list of development-oriented Connect items that I wish to see implemented in SQL 11, the next version
of SQL Server. Read it and vote for the suggestions you like! (Added: 2008-08-16,
Most recent change 2008-09-28.)
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 SourceSafe and a lot more. AbaPerls is a tool that I
use in my daily work, and it's very much a living tool. (Added in January 2009, current label is 1.0.0091.)
- 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 12, 2009-06-26.
- Win32::SqlServer. Access SQL Server from Perl through OLE DB. I also have two older Perl
modules, MSSQL::Sqllib and MSSQL::DBlib which uses DB-Library to access.
Current version of Win32::SqlServer is 2.005, released
in June 2009.
- sqltrace. A stored
procedure that takes an SQL batch as a parameter, sets up a trace, runs the batch, and then summarises the trace.
Optionally, you can get the execution plans for the statements. A very useful tool for performance tuning. sqltrace
is developed by Lee Tudor, and I have the honour to host it on my site. (Added November 2008.)
- Query Plan Tree. A simple
tool to display an execution plan as a tree, developed by
Ivan Arjetinski. (Added in February 2008.)
Other Resources
Some other good resources on SQL Server not to be missed:
- Books Online. Ships with SQL Server, and you find it right under your
Start button. Microsoft regularly publishes updates, and you can get the most recent version for
SQL 2008 here, for
SQL 2005 here and
SQL 2000 here. Initially you may find
it too bewildering to get through, but in the long run use of Books Online pays off tremendously. (You may
see some people talking about BOL; that's short for Books Online:)
Many of my fellow SQL Server MVP's also have very useful sites. A selection:
Contact
You can reach me on esquel@sommarskog.se.