Erland Sommarskog's home page
I'm an independent consultant who provides expert consulting in the SQL
Server area. I'm passionate about sharing my expertise with the community and the main purpose of this site is exactly that: Here you find articles, information about my past and upcoming presentations, links to videos of my talks and a few more things, all free for the community. I was first awarded Most Valuable
Professional on SQL Server by
Microsoft in April 2001, and I am very grateful to have been in the MVP programme since then.
"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 very much 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: 2022-08-30.
- 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: 2023-05-01.
- 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: 2023-01-09. Russian translation available.
- Packaging Permissions in Stored Procedures. How you can package a permission inside a stored procedure, so that users can perform 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 in three parts 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: 2023-04-30. 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-09-03.
- 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 to load a comma-separated list 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 section of articles that are shorter than the ones above and which are more focused on a specific problem. As with the articles above, I try to keep them updated.
- Arrays and Lists in SQL Server. This short story discusses how to handle comma-separated lists in SQL Server. There is also a longer article that discusses many methods for splitting 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.
- The Power of Generate_series and Using a Table of Numbers. These two accompanying articles show how a one-column table sourc with consecutive numbers can be useful with many problems, not the least related to date and time. The first article is for readers who work SQL 2022 and compatibility level 160 where this is a built-in function for this purpose. This article also covers the function date_bucket. The second article is for users on older versions of SQL Server where you need to use different solutions. Latest revision: 2023-04-14.
- 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: 2022-09-18.
Tips from SQLServerGeeks Magazine
These are shorter articles that I originally wrote for the SQLServerGeeks Magazine, and which the very nice people at SQLServerGeeks later republished on their blog. Thus, these articles have a sleeker design, but on the other hand, I don't make updates to these articles. Some of these tips are extracts from my other articles and presentations.
The Video Section
I don't have my own YouTube channel, but several of my presentations have been recorded and uploaded to YouTube by others. Here you find links to a selection of these videos. For some sessions there is more than one link, because I have given the session in different versions with regards to the length. For all videos, there is also a link for downloading slides and scripts for the presentation.
- All You Wanted to Know About Collations. Learn lots about collations and titbits about human languages, for entertainment and enlightenment. but also for your practical work with SQL Server. For instance: when does the collation have a big impact on performance?
- Deadlocks – Analysing, Preventing and Mitigating. In order to deal with a deadlock, you need to find the deadlock XML and know what to look for in it. This is the first half of this session. The second part discusses various approaches you can take to prevent deadlocks or at least mitigate the consequences of them. Be warned that this is a packed sesssion, which assumes that you have some basic understanding of locking alread. Recorded for the Data Platform Summit and presented on 2022-09-20. 60 minutes. Slides and Scripts.
- Don't Bite Off More Than You Can Chew - Take it in Chunks. Techniques to split up very large operations in chunks or batches to make them more efficient and to avoid explosions of the transaction log or tempdb. Recorded for the Data Platform Summit and presented on 2020-12-04. 60 minutes. Slides and scripts.
- Don't Use Cursors or Why You Maybe Should Use a Cursor After All. A session for relatively inexperienced SQL programmers to learn why you should avoid loops in SQL, why set-based statements generally are faster. But also how you should write loops in the few cases where loops is the best choice. Originally presented at SQL Friday #79 on 2022-04-01. 60 minutes + some chat before and after. Slides and scripts.
- Dynamic Search Conditions. How to write a stored procedure that permits users to select among many search conditions, using both dynamic and static SQL.Drawn from my article with the same name.
- Looping Abreast. How to combine loops and set-based processing for problems that are difficult to express in T-SQL. Presented for DataMinutes #1 on 2021-06-11. Lightning talk, 10 minutes. Slides and scripts.
- Packaging Permissions in Stored Procedures. How you can package a permission inside a stored procedure, so that users can perform privileged actions in a way you have control over. Drawn from my article with the same name. Presented for the DBA Fundamentals Virtual User Group on 2021-04-13. 75 minutes. Slides and scripts.
- SQL Titbits for the Inexperienced. Some tips for people who have been using T-SQL left-handedly a year or two. From the DataPlatformGeeks Virtual Symposium, 2020-05-08. 65 minutes + Q&A at the end. Slides and scripts.
- Using Python to Find and Replace with Regular Expressions from T-SQL. A lightning talk from DataMinutes #2, 2022-01-22. 10 minutes. Slides and scripts.
- When Things go Wrong - Error and Transaction Handling in SQL Server. Condenses the most important points in my long series Error and Transaction Handling in SQL Server. Presented at SQL Friday #49 on 2021-05-21. 60 minutes + some chat afterwards. Slides and scripts.
Utilities and Code
All of this code is in the public domain.
- 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 28, 2022-12-30.
- Win32::SqlServer. Access SQL Server from Perl through OLE DB. Current version is 2.014, released
in June 2022.
- 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.
- 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. This is something I originally wrote for the system I worked with at my previous employer and later client. Sadly, I have not been able to keep it up to date with all new features in SQL Server, and today it is more a showcase of things you can do. Current label is 1.0.0402, released April 2022.
Other Written Material
A mixed bag of items that don't fit in elsewhere.
- My SQL Server Wishlist. I've compiled a list of feedback 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: 2023-02-26.
- Build Your Own Index. How you can make your LIKE searches run a lot faster - but with quite a bit of work. This page is a plug for the only book project I've been involved in. (And where our royalties go to War Child International.)
- Code sample for how to load an image or other binary file from VB .NET. A very simple example I put up many years ago, but which still may be useful.