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 (which has very little relation to SQL Server...)
To see what has changed on this site recently, please see my web log.
Have you attended any of my talks? Go here to download slides and scripts. On this page you also can view my upcoming speaking engagements.
"A Small Collection of Large Articles"
Through the years I have written a number of in-depth articles about specific topics related to SQL Server, mainly from a development perspective. I try to keep the articles up-to-date, and I more than welcome feedback, both about contents and spelling/grammar errors!
- The Curse and Blessings of Dynamic SQL.
How you use dynamic SQL, when you should – and when you should not. Latest revision: 2021-01-01.
- Dynamic Search Conditions. How to
write a stored procedure that permits users to select among many search
conditions, using both dynamic and static SQL. Latest revision: 2021-01-02.
- 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: 2021-04-12. Russian translation available.
- Packaging Permissions in Stored Procedures. How you can package a permission inside a stored procedure, so that users can performed privileged actions in a way you have control over. The main article covers the three techniques available: ownership chaining, certificate signing and impersonation with EXECUTE AS in three contexts: database-local, server-level and cross-database access. The article has an appendix where I show how these techniques can be used to solve specific problems. Latest
- Don't Let Your Permissions Be Hijacked! How a user with permissions to deploy triggers and stored procedures could get you to unknowingly execute malicious code that performs actions to the benefit of the attacker – and how you can defend yourself against these attacks. Latest revision: 2021-01-16.
- Error and Transaction Handling in SQL Server. A series of three articles and three appendixes that discuss the bewildering topic of what may happen when an error occurs in SQL Server and how you should implement your error handling. The first part is a short jumpstart for everyone, while Parts Two and Three are in-depth articles. The appendixes cover special topics: linked servers, the CLR and Service Broker. There are also two old articles for SQL 2000. Latest revision: 2021-03-21. Spanish and Russian translations of Part One available.
- How to Share Data between Stored Procedures.
Different ways of passing sets of data between stored procedures. Latest revision: 2021-03-11.
- Using Table-Valued Parameters in SQL Server and .NET. A primer on how to pass data through table-valued parameters from a .NET program to SQL Server with practical examples of how load a comma-separated lists and a file with master-detail data. Latest
- Using the Bulk-Load Tools in SQL Server. An article that covers BCP, BULK INSERT and OPENROWSET (BULK) from a functional point of view. You will learn about format files and how you can use them to tackle various file formats, but you will also learn what formats they are not able to handle. Latest revision: 2021-01-26.
SQL Short Stories
This is a new section that I eventually will have a separate page for. The intention is that this will be a collection of shorter texts for tips in a specific area.
- Arrays and Lists in SQL Server. This short story gives an introduction how to handle comma-separated lists in SQL Server. There is also a longer version that discusses many methods how to split lists into tables, which is a little more on the geeky side. Latest
- Monitor Exceptions in Your Database. Describes how you can monitor and view exceptions in your database with help of an extended-events session that collects them, and a view and a stored procedure that permits you to easily view the data. Latest revision: 2020-05-28.
- Using a Table of Numbers. This short story shows how of a one-column table of consecutive numbers (or dates or hours etc) can be a powerful asset in your database to help you to write certain type of queries. Latest revision: 2020-09-16.
- Where Is that Table Used? This short story discusses various ways to find references to tables, columns etc in a database, but one of these ways is a simple utility to store your SQL code into a full-text indexed table, so that you can search the code efficiently. Latest revision: 2020-05-24.
My SQL Server Wishlist
I've compiled a list of Uservoice 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: 2019-04-27.
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.0360, released June 2017.
- beta_lockinfo. This is a stored procedure for lock-monitoring, giving
you a good snapshot so you can see who is blocking whom and what their current command is. Latest version: Version 26, 2020-09-04.
- Win32::SqlServer. Access SQL Server from Perl through OLE DB. Current version is 2.012, released
in July 2019.
- 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 9, 2018-08-09.
You can reach me on firstname.lastname@example.org.