Our presentation booth at the Data Platform conference included an SQL Server challenge. Many people participated in trying to solve the question that was presented to them:
What would be the result of running the following batch on SQL Server 2008?
Transact-SQL
CREATE TABLE #T (C INT); INSERT INTO #T (C) SELECT TOP (10) ROW_NUMBER () OVER (ORDER BY object_id ASC) FROM sys.objects; DECLARE @Command AS NVARCHAR(MAX) = N'SELECT C FROM #T WHERE C <= 5; TRUNCATE TABLE #T;'; INSERT INTO #T (C) EXECUTE (@Command); SELECT COUNT (*) FROM #T; DROP TABLE #T;
1
2
3
4
5
6
7
8
9
10
CREATE TABLE #T (C INT);
INSERT INTO #T (C)
SELECT TOP (10) ROW_NUMBER () OVER (ORDER BY object_id ASC)
FROM sys.objects;
DECLARE @Command AS NVARCHAR(MAX) =
INSERT INTO #T (C)
EXECUTE (@Command);
SELECT COUNT (*) FROM #T;
DROP TABLE #T;
The question is a bit tricky (what else?). Let’s go over the batch step by step.
First we create a temporary table. Then we insert 10 rows into that table.
I heard some people talk about the ROW_NUMBER function. This function was introduced in SQL Server 2005 along with a few other ranking functions. It simply numbers the rows according to the specified ORDER BY clause. In our case, the column C will hold the numbers 1 through 10.
Then comes the tricky part. We construct a command dynamically, which first retrieves 5 rows from the table (C <= 5), and then truncates the table.
I also heard a few people say that since #T is a temporary table, it is not accessible from the dynamic batch. This is true for table variables, cause their scope is the batch, but it is not true for temporary tables. The scope of a temporary table is the session, so it is accessible from any batch that runs under the same session.
Now we insert into #t the result of dynamic execution. The question here is actually what is the order of execution. Are the 5 rows returned from the dynamic batch and inserted back into the table, and only then the table is truncated, or is it the other way around.
This is how it goes:
First, the dynamic batch is executed.
Inside the batch, the 5 rows are retrieved and returned as a recordset to the calling module.
Then, still inside the dynamic batch, the table is truncated.
The execution of the batch ends, the batch returns the recordset with the 5 rows, and the table is now truncated.
The INSERT statement inserts the recordset returned from the dynamic batch into the #T table (5 rows).
So the last SELECT statement, which counts the number of rows in the #T table, returns 5.
I would like to thank Adi Cohen, who sent me this question a few months ago, so the credit for the question goes to him. Thanks, Adi. Great question!
So.. What were your answers? 42% of the answers were 0. Among the other answers were 1, 6, Error and many more. But there is only one winner. Out of only 14 correct answers, Roey Rosenberg from Bank Igud, who will enjoy a vacation in Eliezer Basade. Congratulations!
Comments