SQL Server surprises: Holland - Belgium

10 October 2019 Nico-Jacobs SQL Server

Holland-Belgium, the SeQueL

After 15 years of SQL Server training I got some idea of which T-SQL features confuse people. Great starting point for a quiz which I ran at multiple conferences. In this blog post I want to share with you the results og running this quiz last week and this week at two major IT conferences in The Netherlands (Techorama NL) and Belgium (Dataminds Connect).

Belgium clearly won when it comes to the number of participants: 42 versus 23. But from the 13 questions the Belgians got on average 3.6 questions correct, whereas in The Netherlands this was 4.1!

The questions ranged from data types over joins, error handling to indexing. Each question had to be compact enough to fit in about 12 lines of T-SQL code (to fit on screen), and most where more compact than that.

The most difficult question: TRY-CATCH

In both countries by far the most difficult question (only 4% correct answers) involves the slightly weird behavior of try-catch blocks when it comes to incorrect meta-data:

-- Which rows are returned by this query?
BEGIN TRY
  SELECT 1
  SELECT * FROM ThisTableDoesNotExists
  SELECT 3
END TRY
BEGIN CATCH
  SELECT 4
END CATCH
SELECT 5

Most people expect that a 1 4 5 is returned, but this is not the case: after the SELECT 1 is executed the select of the non-existing table throws an error which cannot be caught by the TRY-CATCH. Checkout the documentation on TRY-CATCH blocks, since there are more 'weird' exception as the one shown above. In fact the third most challenging question involved TRY-CATCH as well.

Data types can kill performance

The second most difficult question involved data types and indexes:

-- Given table T with a varchar(100) column FullName which has a nonclustered index on the FullName column, which query is the better one and why?

-- Query A
SELECT * FROM dbo.T
WHERE FullName = 'Tom Bas';

-- Query B
SELECT * FROM dbo.T
WHERE FullName = N'Tom Bas'

Most people figured out that query A is the better (because the N turns the string into a unicode string). But they thought it would only have a minor impact. The big problem is that the non-clustered index is created on a varchar which cannot be used in a seek operation if you query with an nvarchar. This leads to a significant slower query!

The verdict

In this good old Holland-Belgium it was clear: Holland won the game. But study the details of T-SQL, there are surprising things which can make you lose the fight with your SQL Server: data types, join types, transactions, try-catch blocks... they don't always do what you think at first!

Detailed results

Checkout this Power BI report to compare the results for the Belgian conference (dmc) with the one in The Netherlands (tornl). The bar shows the fraction of correct answers. Click on a bar to see the question below.

Tags :