This time around, I've got five wish list items for T-SQL / DDL, and then one bonus - a parallelism bug. My thinking on the former is that, since we are either about to start (or are just finishing) locking down the code for SQL Server 2012, this is the first moment where some of these suggestions can be seriously considered without the "but we're about to ship" excuse. :-) The last one is just an incorrect results bug that needs to be fixed. I'll start with the T-SQL / DDL wishes:

Ordered Set Functions

For a while I've been pretty vocal about getting grouped concatenation into SQL Server - functionality that exists in MySQL (GROUP_CONCAT) and more recently added to Oracle 11gR2 (LISTAGG). (And here I thought we were the only pencil-necks using an R2 moniker.) I've often promoted this Connect item when the question comes up on forums or #sqlhelp. This can be solved by a grander implementation - ordered set functions. This comes directly from the standard and supports grouped concatenation (with predictable ordering, unlike many of the workarounds that exist today, such as FOR XML PATH), and a slew of other functionality. The item you should vote for is one that Itzik Ben-Gan just raised this week (and please see this document for more in-depth background):

Connect #728969

Create or Replace

I'm pretty sure I've pimped this one before, but in the interest of driving up items that might make the T-SQL / DDL feature list for the *next* version of SQL Server... we should have the ability to specify in the DDL that we want to create this thing if it doesn't exist, and alter if it does. This avoids the need to re-script permissions, worry about breaking existing dependencies, or build complicated batch logic with a stub create, dynamic SQL, etc. I would gladly take this for simple modules only (procedures, triggers, functions, non-indexed views) as the logic around completely re-designing, say, a table can quickly become more complex.

Connect #127219

Native Regular Expression Support

I would love to see RegEx as a first class citizen, and at least in V1 don't expect this to be fully optimized the way LIKE can be. I just get so sick of writing complex and wordy expressions using PATINDEX / SUBSTRING / STUFF / REVERSE while switching between 0-based and 1-based in my head. And I . Since other CLR functionality is slowly making its way into the top T-SQL tier (spatial, FORMAT(), TRY_PARSE(), etc), I think it's high time we can perform RegEx as well without each of us having to write our own complicated assemblies, deploying and maintaining them, all that assuming we can assure the boss or the IT guys that it's ok to use CLR.

Connect #261342

Create Table As

It would be really nice to have DDL that just says "create a table, but make it a copy of that other table." Today it is easy enough to do this using the tools but to do it in an automated way of any kind is foolhardy - the complexity of the code to generate a CREATE TABLE statement when anything complex is involved can be astounding - consider dependencies, constraints, keys, permissions, triggers, computed columns, indexes, etc. Some folks recommend PowerShell for this but I just think you are trading a different syntax for the same complex code.

Connect #124506

Dynamic Pivot

The PIVOT and UNPIVOT commands as they exist today expect that you know all of the potential values up front. What if you are feeding a report that is more than happy to accept as many columns as you're willing to provide? What ends up happening is we use really ugly workarounds with dynamic SQL after scanning the table and seeing all the potential pivot values (year-month combinations, let's say) that may be represented. I'd like to see this be a little more reactive to the data returned by the query, though it does step into the land of unstructured data at least a little bit...

Connect #127071

...and one of these things is not like the others...

Another parallelism bug

While at least one of the related bugs has recently been fixed, I've previously talked about a few potential perils of parallelism (say that three times fast!). Last week at the MVP Summit I encountered an unfortunate scenario in SQL Server 2012, where incorrect and in fact unpredictable results can come out of using SUM() OVER() with the new windowing functions when combined with parallelism. There are some decent workarounds but they're not very intuitive (and it may be difficult to notice that you're affected).

Connect #728932
 

A long, long time ago in a galaxy you can reach out and touch right now, ORDER BY in a VIEW meant something. If you had a view like the following:

CREATE VIEW dbo.Rubbish AS     SELECT TOP 100 PERCENT Trash         FROM dbo.Garbage         ORDER BY Trash;

And then you ran a query such as:

SELECT Trash FROM dbo.Rubbish;

You could reliably expect the rows to come back ordered by Trash in ascending order, even though it is relatively clear that the ORDER BY here serves a completely different and overloaded purpose - to dictate which rows should be filtered by TOP.

When SQL Server 2005 came around, changes to the optimizer meant that this behavior - which was never documented, by the way, hence never guaranteed - ceased to work as reliably as it had in previous versions. In fact in this case the optimizer sees TOP 100 PERCENT and simply throws out both TOP and ORDER BY. Compare the differences in the plan from the view with and without the ORDER BY:'

 

Notice how there is no sort operator in the query without ORDER BY? Because we didn't tell SQL Server what order we want, it's free to make its own choice. In this case it is choosing a clustered index scan which is not based on the Trash column. You can the end result of the difference in these plans by comparing the results from the select:

 

Obviously only the latter result honors the outer ORDER BY, and in fact both cases have discarded the inner TOP/ORDER BY combination.

People are surprised by this change, and in fact variations on the symptom come up in forum discussions all the time. Further to the confusion, that still exists today, early on there were some very vocal customers who screamed bloody murder and were very stubborn about changing their code to put the ORDER BY on the outermost query where it belongs. After upgrading to SQL Server 2005 they cried foul until Microsoft finally submitted - creating a trace flag that forced the optimizer to behave the old way. You can read about this in . Note that this trace flag is only valid in SQL Server 2005 and SQL Server 2008 - in SQL Server 2008 R2 and SQL Server 2012, the trace flag seems to be a no-op (at least in every case that I've tried). Even if you are still on 2005/2008, I highly recommend using this option as a last resort only, because it changes the way the optimizer works and this can have other undesirable impacts on your entire workload.

Hopefully none of this is news, as it has been discussed at length both online in general, and more specifically on Connect. Where it gets more interesting is that there have been several bugs reported that tools within Management Studio, such as the view designer, actually encourage this behavior and help perpetuate the myth. The issue I noticed this weekend is that, while most bugs have been closed as won't fix, one (#249248) has been closed as fixed. So I immediately fired up the latest build of Management Studio I have, right-clicked the views node, and selected New View. Once I added a table, what I saw was not very promising, as I was able to choose Sort Order and Sort Type:

 

So Management Studio for SQL Server 2012 still allows me to specify a sort order and sort direction for any column in the view! And what does it do to the SQL? It injects TOP 100 PERCENT / ORDER BY! Bad, bad, bad!

On the plus side, if I try to *save* this view, I at least get some kind of warning:

 

For Google/Bing-fu:

Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself. Click CANCEL to discard your modifications. Click OK to save the view.

It's great that they're finally trying to stop people from believing that ORDER BY in a view means anything about ordering the result. But the problem that's being overlooked here is that not everyone uses the view designer to create and save a view in a single session. They may be generating code for a view that they're going to cut & paste into a CREATE/ALTER view script elsewhere, to use in a CTE or derived query in a more complicated script, or to send to a colleague. In a lot of cases they are never going to see this warning, because this won't magically pop up when they try to use the syntax in other scenarios.

So I left a comment on the Connect item with a little detail about that, because I'm unfortunately going to have to disagree that this issue has been "fixed." I'm not sure why the Sort options in the view designer can't just be hidden without changing the behavior of the tool in general - can't it just always behave as if I hadn't changed those options at all?

I do acknowledge that there are some edge cases here, where someone really does want to create a view that selects the top (n) rows ordered by some criteria. So perhaps my suggestion to remove the columns altogether is a bit harsh. But when I choose a Sort Order for one or more columns, it should not just throw TOP 100 PERCENT into my query; the query that's produced at this point shouldn't be valid. Instead it should prompt me with a similar warning to what I get now when I click Save, with additional wording that implies, "If you really do mean to take some subset of the rows in the table, please add a TOP (n) clause to indicate the number or percentage of rows you want to return."

Agree with me? Please go comment and vote!
 

Last night Microsoft released two new cumulative updates for SQL Server 2008 R2:

Cumulative Update 5 for SQL Server 2008 R2 SP1

• KB #2659694
• Build 10.50.2806
• 16 fixes, including a nasty SCOPE_IDENTITY() bug and several performance fixes.
  

My recommendation: You should test and plan for this update if SELECT @@VERSION is between 10.50.2500 and 10.50.2805.

Cumulative Update 12 for SQL Server 2008 R2 (RTM)

• KB #2659692
• Build 10.50.1810
• 3 fixes but none are directly related to the relational engine.

My recommendation: Consider applying if SELECT @@VERSION is between 10.50.1600 and 10.50.1809, you are affected by any of the issues highlighted in the KB article, and you are avoiding Service Pack 1 intentionally (otherwise you should install SP1 and CU5 for SP1).

My usual disclaimer: these updates are NOT for SQL Server 2008. Only apply to systems where @@VERSION returns 10.50.xxxx.

I see a lot of people suggest while loops instead of cursors in situations where row-based processing is required (or, at least, where folks think that row-based processing is required). Sometimes the justification is that constructing a while loop is simpler and more straightforward than constructing a cursor. Others suggest that a while loop is faster than a cursor because, well, it isn't a cursor. Of course the underlying mechanics still represent a cursor, it's just not explicitly stated that way using DECLARE CURSOR.

The difficulty of writing a piece of code should not be the primary factor in avoiding that type of code. I use MERGE - while I will likely never memorize that syntax, I know it is safer and less likely to cause deadlocks than typical "UPSERT" methodologies. Looking up the syntax diagram in Books Online, or using templates or snippets, are good workarounds to having to avoid coding constructs because they're "too hard." The same is true for cursors - sure the syntax is cumbersome, but templates or snippets can do most of the work for you.

Besides, is it that much simpler to set up and use a WHILE loop? And is it really that much more efficient?

Simplicity

I asked a few colleagues to take this code sample and change it from an explicit cursor to a WHILE loop:

DECLARE      @schema_name SYSNAME,      @object_name SYSNAME,      @index_name  SYSNAME      @s NVARCHAR(MAX) = N''; DECLARE indexes CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY      FOR          SELECT s = OBJECT_SCHEMA_NAME(o.[object_id]), o = o.name, i = i.name             FROM sys.objects AS o            INNER JOIN sys.indexes AS i             ON o.[object_id] = i.[object_id]             INNER JOIN             (              SELECT [object_id], index_id, row_count = SUM(row_count)                   FROM sys.dm_db_partition_stats                   GROUP BY [object_id], index_id              ) AS s              ON o.[object_id] = s.[object_id]              AND i.index_id = s.index_id              WHERE o.is_ms_shipped = 0              AND i.index_id >= 1              ORDER BY s.row_count DESC, s, o, i; OPEN indexes; FETCH NEXT FROM indexes INTO @schema_name, @object_name, @index_name; WHILE @@FETCH_STATUS = 0 BEGIN -- we're just concatenating here, but pretend we needed to, -- say, call a stored procedure for each row in the cursor:      SET @s += CHAR(13) + CHAR(10) + N'ALTER INDEX '              + QUOTENAME(@index_name)  + ' ON '              + QUOTENAME(@schema_name) + '.'              + QUOTENAME(@object_name) + ' REORGANIZE;';      FETCH NEXT FROM indexes INTO @schema_name, @object_name, @index_name; END CLOSE indexes; DEALLOCATE indexes;

Before you crucify me about the code sample, or tell me that I could do this without a cursor or a while loop, please understand that I wasn't really trying to solve this problem, I was only trying to come up with a simple string-building exercise that can be accomplished with a cursor. I know that a cursor isn't required to just return a concatenated string, for example you can use FOR XML PATH:

DECLARE @s NVARCHAR(MAX); SELECT @s = (     SELECT CHAR(13) + CHAR(10) + 'ALTER INDEX '        + QUOTENAME(i) + ' ON '        + QUOTENAME(s) + '.'        + QUOTENAME(o) + ' REORGANIZE;'        FROM        (            SELECT TOP (1000000)                s = OBJECT_SCHEMA_NAME(o.[object_id]),                o = o.name,                i = i.name            FROM sys.objects AS o            INNER JOIN sys.indexes AS i            ON o.[object_id] = i.[object_id]            INNER JOIN            (                SELECT [object_id], index_id, row_count = SUM(row_count)                    FROM sys.dm_db_partition_stats                    GROUP BY [object_id], index_id            ) AS s            ON o.[object_id] = s.[object_id]            AND i.index_id = s.index_id            WHERE o.is_ms_shipped = 0            AND i.index_id >= 1            ORDER BY s.row_count DESC, s, o, i        ) AS x         FOR XML PATH(''), TYPE ).value('.[1]', 'NVARCHAR(MAX)');

Or even simpler concatenation:

DECLARE @s NVARCHAR(MAX) = N''; SELECT @s += CHAR(13) + CHAR(10) + 'ALTER INDEX '        + QUOTENAME(i) + ' ON '        + QUOTENAME(s) + '.'        + QUOTENAME(o) + ' REORGANIZE;'         FROM        (            SELECT TOP (1000000)                s = OBJECT_SCHEMA_NAME(o.[object_id]),                o = o.name,                i = i.name            FROM sys.objects AS o            INNER JOIN sys.indexes AS i            ON o.[object_id] = i.[object_id]            INNER JOIN            (                SELECT [object_id], index_id, row_count = SUM(row_count)                    FROM sys.dm_db_partition_stats                    GROUP BY [object_id], index_id            ) AS s            ON o.[object_id] = s.[object_id]            AND i.index_id = s.index_id            WHERE o.is_ms_shipped = 0            AND i.index_id >= 1            ORDER BY s.row_count DESC, s, o, i        ) AS x;

(While it's true that these alternatives don't use an explicit cursor or while loop, so may appear to be "set-based," it is important to note that in these approaches the actual output order is not guaranteed. And if you think this is accomplished under the hood without a cursor-like operation, you're only fooling yourself. :-))

Again, the point of the exercise was not to eliminate the cursor, but to see if a simpler, more straightforward while loop could achieve the same result. One of the complications of the desired result is that I want the resulting output to order the indexes by row_count descending. So there is no unique, incrementing column or index to take advantage of, like you might be able to do if you have an IDENTITY column and you don't care about order otherwise. So this was a potential wrench I threw in intentionally because, in my experience, you will often care about order and you won't always have the luxury of a column inherent in the data set that supports the desired order of processing.

I'll show the four while loops that were submitted. They're all quite similar, using either #temp tables or @table variables to hold the intermediate data set before looping. I'm not going to reveal who submitted these approaches, but rather simply label them Colleague #1, #2, #3 and #4 (both to keep them straight and to protect the innocent). I hope they aren't offended that I took a few liberties with their syntax (mostly indenting and statement termination). These are all fairly similar constructs, and in fact I received a 5th contribution just prior to publishing - I didn't include it because it didn't have any elements not present below.

Colleague #1

DECLARE @indexes TABLE (    s SYSNAME,    o SYSNAME,    i SYSNAME,    c BIGINT,    rn INT ); INSERT @indexes(s, o, i, c, rn)    SELECT s, o, i, c, rn = ROW_NUMBER() OVER        (ORDER BY c DESC, s, o, i)    FROM    (        SELECT s = OBJECT_SCHEMA_NAME(o.[object_id]),            o = o.name,            i = i.name,            c = s.row_count        FROM sys.objects AS o        INNER JOIN sys.indexes AS i        ON o.[object_id] = i.[object_id]        INNER JOIN        (            SELECT [object_id], index_id, row_count = SUM(row_count)                FROM sys.dm_db_partition_stats                GROUP BY [object_id], index_id        ) AS s        ON o.[object_id] = s.[object_id]        AND i.index_id = s.index_id        WHERE o.is_ms_shipped = 0        AND i.index_id >= 1    ) AS x; DECLARE    @min INT,    @max INT,    @cur INT = 0,    @s NVARCHAR(MAX) = N''; SELECT @min = MIN(rn), @max = MAX(rn) FROM @indexes; WHILE (@cur < @max) BEGIN    SET @cur += 1;    SELECT @s += CHAR(13) + CHAR(10)        + 'ALTER INDEX ' + QUOTENAME(i)        + ' ON ' + QUOTENAME(s) + '.'        + QUOTENAME(o) + ' REORGANIZE;'        FROM @indexes        WHERE rn = @cur; END

Colleague #2

SELECT RowID = ROW_NUMBER() OVER (ORDER BY s.row_count DESC, OBJECT_SCHEMA_NAME(o.[object_id]), o.name, i.name), s = OBJECT_SCHEMA_NAME(o.[object_id]), o = o.name, i = i.name INTO #Temp FROM sys.objects AS o INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] INNER JOIN (     SELECT [object_id], index_id, row_count = SUM(row_count)     FROM sys.dm_db_partition_stats     GROUP BY [object_id], index_id ) AS s ON o.[object_id] = s.[object_id] AND i.index_id = s.index_id WHERE o.is_ms_shipped = 0   AND i.index_id >= 1 ORDER BY s.row_count DESC, s, o, i; DECLARE @CurrentRowID INT,        @s NVARCHAR(MAX) = N''; SELECT @CurrentRowID = MIN(RowID) FROM #Temp; WHILE @CurrentRowID IS NOT NULL BEGIN SELECT @s += CHAR(13) + CHAR(10) + N'ALTER INDEX ' + QUOTENAME(i)  + ' ON ' + QUOTENAME(s) + '.' + QUOTENAME(o) + ' REORGANIZE;' FROM #Temp WHERE RowID = @CurrentRowID; SELECT @CurrentRowID = MIN(RowID) FROM #Temp WHERE RowID > @CurrentRowID; END DROP TABLE #Temp;

Colleague #3

DECLARE @Temp TABLE ( RowID INT IDENTITY PRIMARY KEY, SchemaName SYSNAME, ObjectName SYSNAME, IndexName SYSNAME ); INSERT INTO @Temp (SchemaName, ObjectName, IndexName) SELECT s = OBJECT_SCHEMA_NAME(o.[object_id]), o = o.name, i = i.name FROM sys.objects AS o INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] INNER JOIN (     SELECT [object_id], index_id, row_count = SUM(row_count)     FROM sys.dm_db_partition_stats     GROUP BY [object_id], index_id ) AS s ON o.[object_id] = s.[object_id] AND i.index_id = s.index_id WHERE o.is_ms_shipped = 0   AND i.index_id >= 1 ORDER BY s.row_count DESC, s, o, i; DECLARE @CurrentRowID INT,     @s NVARCHAR(MAX) = N''; SELECT @CurrentRowID = MIN(RowID) FROM @Temp; WHILE @CurrentRowID IS NOT NULL BEGIN SELECT @s += CHAR(13) + CHAR(10) + N'ALTER INDEX ' + QUOTENAME(IndexName)  + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' REORGANIZE;' FROM @Temp WHERE RowID = @CurrentRowID; SELECT @CurrentRowID = MIN(RowID) FROM @Temp WHERE RowID > @CurrentRowID; END

Colleague #4

DECLARE     @Row INT = 1,     @s NVARCHAR(MAX) = N''; CREATE TABLE #IndexList (    RowID INT IDENTITY(1,1)    ,SchemaName SYSNAME    ,TableName SYSNAME    ,IndexName SYSNAME ); INSERT INTO #IndexList ( SchemaName ,    TableName ,    IndexName ) SELECT s = OBJECT_SCHEMA_NAME(o.[object_id]), o = o.name, i = i.name          FROM sys.objects AS o          INNER JOIN sys.indexes AS i          ON o.[object_id] = i.[object_id]          INNER JOIN          (              SELECT [object_id], index_id, row_count = SUM(row_count)                 FROM sys.dm_db_partition_stats                  GROUP BY [object_id], index_id          ) AS s          ON o.[object_id] = s.[object_id]          AND i.index_id = s.index_id          WHERE o.is_ms_shipped = 0          AND i.index_id >= 1          ORDER BY s.row_count DESC, s, o, i; WHILE @Row <= (SELECT MAX(RowID) FROM #IndexList) BEGIN     SELECT @s += CHAR(13) + CHAR(10)         + N'ALTER INDEX ' + QUOTENAME(IndexName)         + ' ON ' + QUOTENAME(SchemaName) + '.'         + QUOTENAME(TableName) + ' REORGANIZE;'     FROM #IndexList     WHERE RowID = @Row;     SET @Row = @Row + 1; END DROP TABLE #IndexList;

I validated that the resulting value of @s matched the cursor result in all four cases. I don't know about you, and maybe I'm just being dense, but I had to think a lot harder about that than I would have with a cursor, cumbersome syntax and all. As far as I'm concerned, based on these four submissions from esteemed colleagues, constructing a while loop to solve this problem isn't any easier or more straightforward than a cursor. Especially since a DECLARE CURSOR block can easily be constructed for you using Management Studio's templates (or the new snippets feature in SQL Server 2012).

But what about performance? Surely a cursor is slower than a while loop, because it's a cursor, right?

Performance

As I mentioned in a previous article, the cursor usually gets a bad rap for two reasons: (1) folks think a cursor is bad simply because it says DECLARE CURSOR, and (2) people use a cursor (or any row-by-row processing methodology) when it isn't necessary. In cases where it is the best option (e.g. running totals) or it is necessary (e.g. maintenance tasks, or cases where a stored procedure call must be made for every row or conditionally depend on processing that occurred for the previous row), people just say DECLARE c CURSOR FOR ... when they should usually be using the most efficient cursor declaration possible (LOCAL STATIC FORWARD_ONLY READ_ONLY). And yes, some will argue that FORWARD_ONLY READ_ONLY could be replaced by FAST_FORWARD, but I prefer the former, because the latter can't be combined with STATIC:

Msg 1048, Level 15, State 1, Line 1 Conflicting cursor options STATIC and FAST_FORWARD.

Now, you are more than welcome to do your own tests with the various cursor options, to see which perform best in your scenario, but I've always come back to the set of options described above. I put my cursor code above into a stored procedure, as well as the two "set-based" concatenations, and the four colleague-provided options. I then ran some performance tests where I measured just duration of each method, executing 1000 times (I ran the middle section three times, then took the average):

SET NOCOUNT ON; GO CREATE TABLE #stats (     rownum INT IDENTITY(1,1),     procname SYSNAME,     dt DATETIME2 ); GO INSERT #stats(procname,dt) SELECT '-', SYSDATETIME(); GO EXEC dbo.while_cursor; GO 1000 INSERT #stats(procname,dt) SELECT 'cursor', SYSDATETIME(); GO EXEC dbo.while_xmlconcat; GO 1000 INSERT #stats(procname,dt) SELECT 'xml concat', SYSDATETIME(); GO EXEC dbo.while_simpleconcat; GO 1000 INSERT #stats(procname,dt) SELECT 'simple concat', SYSDATETIME(); GO EXEC dbo.while_colleague1; GO 1000 INSERT #stats(procname,dt) SELECT 'colleague 1 while', SYSDATETIME(); GO EXEC dbo.while_colleague2; GO 1000 INSERT #stats(procname,dt) SELECT 'colleague 2 while', SYSDATETIME(); GO EXEC dbo.while_colleague3; GO 1000 INSERT #stats(procname,dt) SELECT 'colleague 3 while', SYSDATETIME(); GO EXEC dbo.while_colleague4; GO 1000 INSERT #stats(procname,dt) SELECT 'colleague 4 while', SYSDATETIME(); GO -- using the new LAG functionality in SQL Server 2012: SELECT     procname,     duration = DATEDIFF(MILLISECOND, LAG(dt, 1, NULL) OVER (ORDER BY rownum), dt) FROM #stats ORDER BY rownum; GO DROP TABLE #stats; GO

The results (the "winner" highlighted in green):

 

 

The XML and simple concatenation approaches aside, the cursor clearly outperformed all of the while loops. This may not necessarily be true in simpler scenarios, so I'm not suggesting that a cursor will always be as good as or better than a while loop. But by the same token, I think I've demonstrated that a while loop isn't always faster or easier to write and understand than a cursor.

I would love to hear your experience, especially any examples where a while loop clearly outperforms a cursor or where it is much easier to write or comprehend.
 

This one is quite subjective, and I'm sure I will face plenty of opposition - not only because it's a preference thing and many people are married to their preferences, but also because it violates the strict interpretation of the standard. Personally, I'm more worried about the former than the latter - I have no concerns whatsoever that SQL Server will eradicate the = notation for column aliases, nor do I worry that the code I write needs to work when ported to Oracle, DB2, MySQL, etc. (I highly doubt this will be the biggest problem in such an event). If these concerns are important to you, you may want to stop reading.

To be honest, there are four six different ways you can define the alias for a column in a SELECT statement - the alias being the column name the application or API sees when the result is returned from SQL Server. The methods I've seen used are:

SELECT 1 AS x; -- #1 SELECT x = 1; -- #2 SELECT 'x' = 1 ; -- #3 SELECT 1 x; -- #4 SELECT 1 AS 'x'; -- #5 SELECT 1 'x'; -- #6

(I've ignored [alias] and "alias" variations for brevity.)

But I really want to discuss only two of these methods.

First, I want to discard item #3 entirely (which I've edited thanks to techvslife's comments). Why? Because using string literals as column aliases has been deprecated for some time now. You can see how prevalent this is in your environment with the following query:

SELECT [object_name], instance_name, cntr_value     FROM sys.dm_os_performance_counters     WHERE [object_name] LIKE '%:Deprecated Features%'     AND instance_name LIKE 'String literals as column aliases%';

I'd also like to discard #4, simply because I find it completely unreadable. Without using either = or AS, it makes it very hard to interpret if that is intended to be an alias, or if x is actually a column name and the author simply forgot a comma. I feel the same way about leaving out AS to denote a table alias, or WITH to denote a table hint (maybe I'll treat those in another post).

And #5 and #6 I'm going to ignore because they're the same as #1 and #4 but with single quotes around the alias. Personally I find the single quotes around aliases to be distracting, making the select list tougher to read no matter which aliasing convention you use. All alias names are strings; why would I want to make it look like it is intended to be data? Also note that #5 and #6 are not defined in any standard, they just seem to be allowed into the syntax (I haven't studied the standards lately but I don't believe these forms are documented).

So that leaves #1 and #2.

I prefer #2 simply for readability reasons. Remember that even in cases where you're "the database guy," the T-SQL code you write is not consumed solely by you - it is also consumed by application developers who want to understand your query (and in a lot of cases, the priority is understanding the shape of the result set); it will also be consumed by your successors, should you move on to a different project, a different company, or worse. Let's take a look at a very simple example of a query against the Sales.SalesOrderHeader table in AdventureWorks2008R2. I've added some new columns using calculations, and you can see how different it can be for someone trying to read the code and determine what the column names are. On the left pay attention to how your eyes have to move all over the script to locate the alias names that have been provided; on the right, it is a much simpler scan down the left hand side. (Click for larger.)

 

Now, this all depends on whether you are already following somewhat logical coding conventions in the first place. If your SELECT list looks like stream of consciousness from James Joyce, which I see more often than I'd like, then whether you use AS or = isn't going to matter at all. This is with word wrap turned on in the T-SQL editor, and is much worse with word wrap turned off.

Raise your hand if you've seen this kind of code (or have written such a mess yourself). I suggest always writing code with word wrap turned off - in T-SQL at least, there aren't many cases where a line *needs* to go off-screen, and having word wrap turned off will protect you from doing it inadvertently.

In SQL Server 2012, there are easier ways of determining the shape of a resultset from a stored procedure, as long as it is the *first* resultset; in this case, it doesn't matter how ugly your queries are, you never have to look at them to determine the names of columns, their data types, or the order they are returned:

SELECT name, system_type_name     FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(N'dbo.UsingAS'), NULL)     ORDER BY column_ordinal;

Results:

That doesn't mean you should stop paying attention to formatting once you've upgraded to SQL Server 2012; your code should still be tidy and readable, this just gives you a quick way to examine result sets from code written by less motivated people, without having to refactor the code first (which is what I often end up having to do when I am trying to troubleshoot unreadable code).

If I haven't convinced you to use = for column aliases, I hope I've at least convinced you to use AS if you're currently using method #3 or #4 above.
  

On Monday, I completely missed that the SQL Server Release Services team published two cumulative updates for SQL Server 2008. I'd blame jury duty, but that didn't start until Tuesday, and only lasted two days. Well, better late than never.

Cumulative Update #8 for SQL Server 2008 Service Pack 2

• KB : http://support.microsoft.com/kb/2648096
• Build number : 10.00.4326
• Number of fixes : 6
• Relevant for : Builds of SQL Server where SELECT @@VERSION yields between 10.00.4000 and 10.00.4325

Cumulative Update #3 for SQL Server 2008 Service Pack 3

• KB : http://support.microsoft.com/kb/2648098
• Build number : 10.00.5770
• Number of fixes : 14
• Relevant for : Builds of SQL Server where SELECT @@VERSION yields between 10.00.5000 and 10.00.5769

As usual, I'll post my standard disclaimer here: these updates are NOT for SQL Server 2008 R2 (where @@VERSION will report 10.50.xxxx).

P.S. this is blog post #499. You're probably not counting, but I am. :-)
 

This month's T-SQL Tuesday is being hosted by Dave Howard (@DaveH0ward), and the topic is "Second Chances." What he's letting us do is look back at all the previous T-SQL Tuesdays and pick a topic that we missed or maybe thought we needed another crack at.

I chose a recent topic that I missed due to travel, "Data Presentation," hosted by Robert Pearl in September 2011 (T-SQL Tuesday #22).

At my previous job, one of the most complicated pieces of presentation work we had to do was to provide users with data in their preferred time zone. We separated data by customer in separate databases, which would have made things relatively easy, if only all the users of each customer were in the same time zone. In that case, we could have just stored all the customer's data in that time zone.

But of course, customers had offices spread throughout the country and in fact the world, so we had to implement a way to show the same data, translated to local time, for users in different time zones. A complication to the requirement was that, based on the user's login to the web application, we needed to show the data in their preferred time zone even if they were using a computer in a different office - so we couldn't rely on the web browser's preferences or local machine's regional settings to determine the time zone. And in addition to these requirements, we also had to know the actual UTC offset for given dates, both in the past and in the future, since several of the supported time zones would observe daylight saving time.

In order to fulfill these requirements, we decided to implement the logic within the database. This way the application wouldn't have to know anything except who the user was, and the logic would work the same way regardless of which application was requesting the data (we also had several applications that weren't tied to the web application, that wouldn't always know the user, so each customer had a default to fall back on). The servers in the data center were all set to GMT and to *not* observe daylight savings time. This is actually on my informal best practices list because it prevents ever having to worry about data collisions or gaps twice a year.

So here is a skeleton of the schema and minimal data to demonstrate the functionality. First I'll let you create a dummy database to target:

USE [master]; GO IF DB_ID('TSQLTuesdayAaron') IS NOT NULL BEGIN     DROP DATABASE TSQLTuesdayAaron; END GO CREATE DATABASE TSQLTuesdayAaron; GO USE TSQLTuesdayAaron; GO

Now we need tables for TimeZones and UTCOffsets:

CREATE TABLE dbo.TimeZones (     TimeZoneID TINYINT PRIMARY KEY,     Name VARCHAR(32) NOT NULL UNIQUE ); GO INSERT dbo.TimeZones(TimeZoneID, Name) VALUES     (1,'Eastern US Time'),     (2,'Greenwich Mean Time'); CREATE TABLE dbo.UTCOffsets (     TimeZoneID TINYINT NOT NULL,     [Date] SMALLDATETIME NOT NULL,     Offset SMALLINT NOT NULL,     PRIMARY KEY CLUSTERED(TimeZoneID, [Date]) ); GO ALTER TABLE [dbo].[UTCOffSets] ADD CONSTRAINT [FK_utc_TZ]     FOREIGN KEY([TimeZoneID]) REFERENCES [dbo].[TimeZones] ([TimeZoneID]);

For brevity, I'm just going to populate the transition days, e.g. when daylight savings time changes. In actuality, the table is truly a calendar table; each date from 2005-01-01 through 2030-12-31 is represented, once for each time zone:

INSERT dbo.UTCOffsets(TimeZoneID, [Date], Offset) VALUES     (1,'2011-03-12',-5),     (1,'2011-03-13',-4),     (1,'2011-11-05',-4),     (1,'2011-11-06',-5),     (1,'2012-03-10',-5),     (1,'2012-03-11',-4),     (1,'2012-11-03',-4),     (1,'2012-11-04',-5),     (2,'2011-03-26', 0),     (2,'2011-03-27', 1),     (2,'2011-10-29', 1),     (2,'2011-10-30', 0),     (2,'2012-03-24', 0),     (2,'2012-03-25', 1),     (2,'2012-10-27', 1),     (2,'2012-10-28', 0);

Now of course we need some customers and users:

CREATE TABLE dbo.Customers (     CustomerID INT PRIMARY KEY,     Name NVARCHAR(32) NOT NULL UNIQUE,     TimeZoneID TINYINT NOT NULL ); GO ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [FK_c_TZ]     FOREIGN KEY([TimeZoneID]) REFERENCES [dbo].[TimeZones] ([TimeZoneID]); GO INSERT dbo.Customers(CustomerID, Name, TimeZoneID) VALUES     (1, N'Teks', 1),     (2, N'MSDN', 2); GO CREATE TABLE dbo.Users (     UserID INT PRIMARY KEY,     Email VARCHAR(320) NOT NULL UNIQUE,     CustomerID INT NOT NULL,     TimeZoneID TINYINT NOT NULL ); GO ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_u_TZ]     FOREIGN KEY([TimeZoneID]) REFERENCES [dbo].[TimeZones] ([TimeZoneID]); ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_u_Cust]     FOREIGN KEY([CustomerID]) REFERENCES [dbo].[Customers] ([CustomerID]); GO INSERT dbo.Users(UserID, Email, CustomerID, TimeZoneID) VALUES     (1, 'foo@bar.com', 1, 1),     (2, 'bar@foo.com', 1, 2),     (3, 'foo@bar.net', 2, 1); GO

And then functions to determine the offset for a given day and user or customer:

CREATE FUNCTION [dbo].[GetUTCOffSet_ByUserID] (     @UserID INT,     @Date   SMALLDATETIME ) RETURNS SMALLINT AS BEGIN     RETURN     (         SELECT utc.Offset             FROM dbo.UTCOffsets AS utc             INNER JOIN dbo.Users AS u             ON utc.TimeZoneID = u.TimeZoneID             WHERE utc.[Date] = DATEDIFF(DAY, 0, @Date)             AND u.UserID = @UserID     ); END GO CREATE FUNCTION [dbo].[GetUTCOffSet_ByCustomerID] (     @CustomerID  INT,     @Date        SMALLDATETIME ) RETURNS SMALLINT AS BEGIN     RETURN     (         SELECT utc.Offset             FROM dbo.UTCOffsets AS utc             INNER JOIN dbo.Customers AS c             ON utc.TimeZoneID = c.TimeZoneID             WHERE utc.[Date] = DATEDIFF(DAY, 0, @Date)             AND c.CustomerID = @CustomerID     ); END GO

So now, given a date/time value and a user or company, we could determine the offset that should be used, depending on the known CustomerID or UserID:

SELECT dbo.GetUTCOffset_ByCustomerID(1, '20111106'); SELECT dbo.GetUTCOffset_ByCustomerID(2, '20111029'); SELECT dbo.GetUTCOffset_ByUserID(1, '20111106'); SELECT dbo.GetUTCOffset_ByUserID(2, '20111029'); SELECT dbo.GetUTCOffset_ByUserID(3, '20111106')

Once a user is logged in, we could simply store their timezone in a session variable, so we wouldn't have to retrieve that value more than once. For any date we needed to display, we could just pass in the date and the timezone to the following function:

CREATE FUNCTION [dbo].[GetPresentationDateTime] (     @TimeZoneID TINYINT,     @DateTime   SMALLDATETIME ) RETURNS SMALLDATETIME AS BEGIN     RETURN     (         SELECT DATEADD(HOUR, @Offset, @DateTime);     ); END GO SELECT dbo.GetPresentationDateTime(1, '20111106 04:32'); SELECT dbo.GetPresentationDateTime(2, '20111029 04:32');

We could have done all of the above in a single, overloaded function or procedure, but decided it would be better to make the access methods much more defined. You also might suggest DATETIMEOFFSET, but this solution was initially implemented in SQL Server 2005, long before the new date/time data types were introduced. Besides, I'm not sure how it would have helped, since most values were input without any TZ awareness (e.g. GETUTCDATE()), relying on the system clock to store UTC date/time correctly. Most data did not need to be timezone aware until presented to the user, and since it could be different for every user, the value of storing one user's TZ information with the data was pretty weak.

The above didn't really help if we wanted to pull a report with lots of datetime values - we would have to call the scalar function for every row (and sometimes for multiple values) - in those cases we just wrote out a join the long way (if we didn't already know the offset) or used DATEADD inline when we did.

Now of course, you're probably thinking this isn't isn't quite complete. And you'd be right. There's that funky time we haven't accounted for - the first couple of hours in each DST-observing time zone on the forward/back day, before the change is actually supposed to take place. We opted to defer that since most events happen during business hours, and the issue never came up with any of our customers. Certainly a concern you will want to take into account if you're thinking about this type of solution.

And in the spirit of keeping with the topic, if I were to be presented with this same challenge today, I would probably use it as a second chance to get the job done a little better, rather than take the easy "I-solved-this-before" route.
 

I’ve seen the question a few times now: “How do I compare two plans side-by-side in Plan Explorer?” Management Studio allows you to view multiple graphical plans that are visible simultaneously, either by generating plans for every statement in a batch, or by generating plans in separate query windows and then splitting the panes. However, at least in my estimation, a lot of the other metrics and properties are more valuable when performing comparisons. In SSMS this is tough, because you can only have one tooltip visible at a time (regardless of how many plans are visible). Same for the Properties panel – only a single plan or operator can populate this panel at one time. So even though you can compare high-level graphical details quickly, you will likely end up with multiple instances of SSMS to perform true side-by-side comparisons of the lower-level details.

In Plan Explorer (free download) it is very easy to compare a lot of the metrics side-by-side for two (or more) plans. Since Plan Explorer can generate estimated or actual plans for you, it’s as simple as entering multiple queries in the Command Text pane, and then generating an estimated or actual plan:

I recommend trying the Vertical Layout, so that the various plan-related tabs and the Statements Tree are a lot closer together (reducing mouse and eye movement):

Here is a quick comparison of the two default options (vertical on the left, horizontal on the right – click to enlarge):

(There is also a Custom Layout option if you wish to change from the default options.)

And you can still compare a lot of the other details about two different statements by looking at the other columns in the Statements Tree tab. For example, comparing costs, row counts and operation counts between estimated or actual plans can give you a much more accurate picture of the performance impact of different statements:

And you can switch between graphical plans quickly by using arrow keys or mouse clicks to switch between statements:

Since the other tabs are context sensitive, you can do this for any tab: simply highlight a tab (e.g. Top Operations) and, as you switch through the rows in the Statements Tree, the tab below will update accordingly.

For more complex plans, and depending on how much screen real estate you have, you may prefer to open two instances of Plan Explorer so that you can interact with the plans a little more independently. In this case you won’t be able to see percentages or other relative comparisons directly against one another, but for most eyeballing, it’s still better than what you get in Management Studio IMHO.

That all said, comparing two graphical plans side by side is definitely on the list for a future update; in the meantime, I hope the above is helpful.