Use Central Management Servers to Alter a Column in All Servers

The "Central Management Servers" functionality of SQL Server Management Studio is an excellent way to apply the same code to every server. By "every server", I mean groups of servers that you define: you might have different servers in folders named "Production", "Development", "QA", "SQL Server 2000", etc. It just depends on your needs.

To get to the "Central Management Servers" pane in SSMS, simply select the "View, Registered Servers" menu item. After you've set up your servers, you can execute queries against every database in every server. The example below changes the width of a column in a table. Note that in this example, we look for only databases whose name begins with "DB100", we exclude read-only databases, and we specify the schema, table, and column name we want to modify. The code also checks that the length isn't already set to the value we want, to avoid unnecessary noise in the output. This code is compatible with SQL Server 2000 and later (hence the use of "syscolumns", etc.)

-- Comment. 
set nocount on
declare @Sql nvarchar(4000)

set @Sql = 'use [?]

            if exists (select *
                         from syscolumns   sc
                         join sysobjects   so
                           on sc.id = so.id
                        where ''?''                                   like ''DB100%''
                          and databaseproperty(''?'', ''IsReadOnly'')    = 0
                          and user_name(so.uid)                          = ''dbo''
                          and object_name(sc.id)                         = ''Customer''
                          and sc.name                                    = ''FirstName''
                          and sc.length                                 != 120)
            begin
                select ''?'', sc.length
                  from syscolumns   sc
                  join sysobjects   so
                    on sc.id = so.id
                 where ''?''                                   like ''DB100%''
                   and databaseproperty(''?'', ''IsReadOnly'')    = 0
                   and user_name(so.uid)                          = ''dbo''
                   and object_name(sc.id)                         = ''Customer''
                   and sc.name                                    = ''FirstName''
                   and sc.length                                 != 120

                ALTER TABLE dbo.Customer ALTER COLUMN FirstName VARCHAR(120) NULL
            end '

exec sp_msforeachdb @Sql
go

References

For more information on Central Management Servers:

     Create a Central Management Server and Server Group

     Registered Servers and Central Management Server Stores

     Execute SQL Server query on multiple servers at the same time

SSMS 2012 - Lines with Background Color Have Ugly Gaps

I've been using SQL Server Management Studio 2012 for about 10 minutes now, so I don't have much to say about it yet. I'm sure it's a big improvement over previous versions, and I'm looking forward to exploring.

So what's the problem? It's trivial, really, but at the same time, just so glaringly ugly. I set my T-SQL comments to have a background color. For me, it's easier to "filter out" the non-executing part of the code that way. It also makes it possible to "section off" my code for clarity; splitting a long script into chunks makes it much more digestable. Here's an example using SSMS 2008:

See how the comments form one cohesive block of color? Smarter people than me could tell you why this is psycho-visually important. I just know that it is important. So imagine my dismay when I opened my first SQL file in SSMS 2012 and saw this (click on the image to get the full-size version):

Each comment line is now separated by a thin stripe of background color, in my case black. (And yes, it looks the same with a white background.) Who at Microsoft thought this monstrosity was acceptable? The only Connect I could find on it is at http://connect.microsoft.com/VisualStudio/feedback/details/645507/syntax-highligher-issues and it's been marked as "Closed Won't Fix," which is the kind of response that reminds me why I'm not a full-time a C/C++/C# developer anymore.

I know, it's a little thing, but a closer look at these lines reveals that someone put some work into creating this gap: it wasn't just accidental. Look closely at the line above the black gap in the zoomed-in image below:

You can see that the color of the line above the gap is blended with the background color, which, I suppose, gives it a more appealing look (assuming that you like the background lines in the first place). This touch is obviously someone's handiwork, which probably explains why they're not interested in fixing it. I guess now I just need to either live it with, stop coloring comments differently, or find a workaround somewhere.

Find All NOT NULL Columns Without a Default Constraint

Rarely, I'll run into a situation where it would be useful to know which columns that are specified as NOT NULL don't have a default constraint as well. For example, I've seen a table with two dozen columns, all but a few set as NOT NULL. This in itself isn't an issue, but on this occasion, for debugging purposes, we were constantly INSERTing a test row of data into the table. Having to specify a default value for each column got to be annoying after a while.

There are, of course, other ways to skin this cat, We could have created a SSMS template and used the old "Ctrl-Shift-M" key combo to type in just the values we needed. Or we could have created a script, or even a stored procedure to handle the default values for us. For various reasons, those solutions wouldn't work in our environment, so I decided to attack the problem at its root.

The code below looks at all non-empty tables and prints out T-SQL code to add the default constraint to each column it finds. Note that I've made an attempt to select a reasonable default value for all the common data types, but (a) the list is not complete, and (b) your idea of a "reasonable" default value may be very different from mine. So don't just copy and paste the output from this script and hit F5 - take a moment to read each line and be sure it does what you want; if not, you can easily change it!

-------------------------------------------------------------------------------
-- For all tables in the current database that have rows, finds all "NOT NULL" 
-- columns that don't have a DEFAULT constraint, and emits the ALTER TABLE     
-- statements needed to add the DEFAULT constraints for them.                  
-------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @SchemaName      sysname         SET @SchemaName     = ''
DECLARE @TableName       sysname         SET @TableName      = ''
DECLARE @ColumnName      sysname         SET @ColumnName     = ''
DECLARE @ColumnType      sysname         SET @ColumnType     = ''
DECLARE @ConstraintName  sysname         SET @ConstraintName = ''
DECLARE @Sql             NVARCHAR(MAX)   SET @Sql            = ''

DECLARE cur CURSOR FOR
    SELECT SCHEMA_NAME(t.schema_id)    AS 'Schema Name'
         , OBJECT_NAME(c.object_id)    AS 'Table Name'
         , c.name                      AS 'Column Name'
         , ty.name                     AS 'Column Type'
      FROM sys.columns   c
      JOIN sys.tables    t
        ON c.object_id = t.object_id
      JOIN sys.types     ty
        ON c.user_type_id = ty.user_type_id
     WHERE c.is_nullable                  = 0
       AND c.is_identity                  = 0
       AND c.is_computed                  = 0
       AND t.type_desc                    = 'USER_TABLE'
       AND t.is_ms_shipped                = 0
       AND ISNULL(c.default_object_id, 0) = 0
       AND 0 < (SELECT SUM(row_count)
                  FROM sys.dm_db_partition_stats
                 WHERE object_id = OBJECT_ID(t.name)   
                   AND (index_id = 0 OR index_id = 1))
  ORDER BY 'Schema Name'
         , 'Table Name'
         , 'Column Name'

OPEN cur

FETCH NEXT FROM cur
 INTO @SchemaName, @TableName, @ColumnName, @ColumnType
        
IF @@FETCH_STATUS = 0
BEGIN        
    PRINT 'USE ' + DB_NAME(0)
    PRINT ' ' 
END

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ConstraintName = QUOTENAME('DF_'+ @TableName + '_' + @ColumnName)
    
    SET @Sql = ''
             + 'PRINT ''Processing: ' + QUOTENAME(@SchemaName) + '.' + @ConstraintName + ''''
             + ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) 
             + ' ADD CONSTRAINT ' + @ConstraintName 
             + ' DEFAULT ('
             +
                CASE @ColumnType
                    WHEN 'char'             THEN ''''''
                    WHEN 'nchar'            THEN ''''''
                    WHEN 'varchar'          THEN ''''''
                    WHEN 'nvarchar'         THEN ''''''
                    WHEN 'sysname'          THEN ''''''
                    WHEN 'xml'              THEN ''''''
                    WHEN 'udtUserID'        THEN ''''''
                    WHEN 'text'             THEN ''
                    WHEN 'ntext'            THEN ''

                    WHEN 'bigint'           THEN '0'
                    WHEN 'int'              THEN '0'
                    WHEN 'smallint'         THEN '0'
                    WHEN 'tinyint'          THEN '0'
                    WHEN 'bit'              THEN '0'

                    WHEN 'real'             THEN '0.0'
                    WHEN 'money'            THEN '0.0'
                    WHEN 'smallmoney'       THEN '0.0'
                    WHEN 'float'            THEN '0.0'
                    WHEN 'decimal'          THEN '0.0'
                    WHEN 'numeric'          THEN '0.0'

                    WHEN 'image'            THEN '0x0'
                    WHEN 'binary'           THEN '0x0'
                    WHEN 'varbinary'        THEN '0x0'
                    
                    WHEN 'uniqueidentifier' THEN '0x0'
                    WHEN 'sql_variant'      THEN '0'
                    WHEN 'hierarchyid'      THEN '''/'''
                    WHEN 'geometry'         THEN '0'
                    WHEN 'geography'        THEN '0'

                    WHEN 'datetime'         THEN 'GETDATE()'
                    WHEN 'date'             THEN 'GETDATE()'
                    WHEN 'time'             THEN 'GETDATE()'
                    WHEN 'datetime2'        THEN 'GETDATE()'
                    WHEN 'datetimeoffset'   THEN 'GETDATE()'
                    WHEN 'smalldatetime'    THEN 'GETDATE()'
                    WHEN 'timestamp'        THEN 'GETDATE()'
                    ELSE                         '-1'
                END
                    
             + ')' 
             + ' FOR ' + QUOTENAME(@ColumnName)

    PRINT @Sql    
    
    FETCH NEXT FROM cur
     INTO @SchemaName, @TableName, @ColumnName, @ColumnType
END

CLOSE cur
DEALLOCATE cur

Script to Calculate Columns' Cardinality and Selectivity

The concepts of column cardinality and selectivity come up a lot when you're designing indexes; this script displays both values for a given table in the current database.

There are many excellent articles on the web explaining these two concepts (see the References section below for my favorites), but briefly:

Cardinality is the number of distinct values in a column. The classic example is the "Employees" table: the "Sex" column has two possible values (M, or F) and so has a cardinality of either 1 (for an all-woman company, for example), or 2 (for a co-ed company). The "ZIP Code" column could have a cardinality anywhere from 1 (for a very small company) to about 43,000 (for a huge, nation-wide enterprise).

Selectivity for a column is the ratio of the number of distinct values (the cardinality) to the total number of values. For example, in our "Employees" table, imagine there are 5000 employees, but all of them live in only 10 states. The selectivity of this column would be: 10 / 5000 = 0.002, or 0.2%. One potentially confusing point is that although this is a very low percentage, it is referred to as high selectivity: this would potentially be a good column for an index. The lower the selectivity percentage, the higher the selectivity this represents, and the more useful the column might be as the first column in an index.

I find this script useful when I encounter a table containing data I am totally unfamiliar with: I could probably guess the columns' selectivity for a table named Customer fairly well, but a table named Thx1138 containing some kind of exotic (to me) data would be a bigger challenge without this script.

-------------------------------------------------------------------------------
-- Calculates the row count, cardinality, and selectivity of each column in a  
-- user-specified table.                                                       
-------------------------------------------------------------------------------

SET NOCOUNT ON

-- Specify the table here - this is the only place you need to modify. 
DECLARE @SchemaName sysname         SET @SchemaName = 'dbo'
DECLARE @TableName  sysname         SET @TableName  = 'MyTable'

-- Declare variables. 
DECLARE @CrLf       CHAR(2)         SET @CrLf       = CHAR(13) + CHAR(10)
DECLARE @Sql        NVARCHAR(MAX)   SET @Sql        = ''
DECLARE @ColumnName sysname         SET @ColumnName = ''

-- Show total number of rows in table. 
SET @Sql = 'SELECT COUNT(*) AS "Row Count for ''' + @TableName + '''" FROM ' + @SchemaName + '.' + @TableName
EXEC sp_executesql @Sql

-- Calculate selectivity as "cardinality / row count" for each column. 
DECLARE cur CURSOR FOR
    SELECT name
      FROM sys.columns
     WHERE object_id   = OBJECT_ID(@SchemaName + '.' + @TableName)
       AND is_identity = 0
       
OPEN cur
FETCH NEXT FROM cur INTO @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR('Processing column: %s', 10, 1, @ColumnName) WITH NOWAIT

    SET @Sql = 'SELECT ''' + QUOTENAME(@ColumnName) + '''   AS ''Column'' '                        + @CrLf
             + '     ,       COUNT(DISTINCT ' + QUOTENAME(@ColumnName) + ')   AS ''Cardinality'' ' + @CrLf
             + '     , LEFT((COUNT(DISTINCT ' + QUOTENAME(@ColumnName) + ') * 1.0) / '             + @CrLf
             + '             NULLIF(COUNT(*), 0), 6)   AS ''Selectivity'' '                        + @CrLf
             + '  FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' '              + @CrLf
             + ' WHERE ' + QUOTENAME(@ColumnName) + ' IS NOT NULL '                                + @CrLf
             + ''                                                                                  + @CrLf

    EXEC sp_executesql @Sql
    FETCH NEXT FROM cur INTO @ColumnName
END

CLOSE cur
DEALLOCATE cur

References

Index columns, selectivity and equality predicates

Query Tuning Fundamentals: Density, Predicates, Selectivity, and Cardinality

Column Statistics Give the Optimizer an Edge

A quick script to display the total size each database, taking into account multiple MDF and LDF files.

-------------------------------------------------------------------------------
-- Returns the total size of all databases' mdf and ldf files, and the grand   
-- total of the two.                                                           
-------------------------------------------------------------------------------

-- Create the temp table. 
IF OBJECT_ID('tempdb..##t') IS NOT NULL
    DROP TABLE ##t

CREATE TABLE ##t 
(
    [Database]   sysname, 
    [File Type]  NVARCHAR(50),
    [Size (MB)]  BIGINT
)

-- Populate the temp table with value for all the mdf and ldf files for all databases.  
DECLARE @CrLf CHAR(2)
SET @CrLf = CHAR(13) + CHAR(10)

DECLARE @Sql NVARCHAR(MAX)

SET @Sql = 'USE ? '                                                           + @CrLf
         + ''                                                                 + @CrLf
         + 'INSERT ##t '                                                      + @CrLf
         + '    SELECT ''?'' '                                                + @CrLf
         + '         , type_desc '                                            + @CrLf
         + '         , CAST(Size AS BIGINT) * (8 * 1024) / 1024 / 1024 '      + @CrLf
         + '      FROM sys.database_files '

EXEC sp_msforeachdb @Sql

-- Calculate the grand total (mdf files' sizes + ldf files' sizes; add to temp table. 
SET @Sql = 'USE ? '                                                           + @CrLf
         + ''                                                                 + @CrLf
         + 'INSERT ##t '                                                      + @CrLf
         + '    SELECT ''?'' '                                                + @CrLf
         + '         , '' - TOTAL - '' '                                      + @CrLf
         + '         , CAST(SUM(Size) AS BIGINT) * (8 * 1024) / 1024 / 1024 ' + @CrLf
         + '      FROM sys.database_files '
 
EXEC sp_msforeachdb @Sql

-- Display the totals for each database and file type. 
  SELECT [Database], [File Type], SUM([Size (MB)])   AS 'Size (MB)'
    FROM ##t
GROUP BY [Database], [File Type]
ORDER BY [File Type] DESC, [Size (MB)] DESC

IF OBJECT_ID('tempdb..##t') IS NOT NULL
    DROP TABLE ##t

Setting the Number of ERRORLOG files

For some reason, the default number of ERRORLOG files maintained by SQL Server defaults to six. This has always seemed like a poor choice to me, as it makes it impossible to investigate events that may have occurred many months ago. Fortunately, this is a simple Registry setting, and we can use the undocumented xp_instance_regwrite system stored procedure to set it to the value we want.

-------------------------------------------------------------------------------
-- Set the number of ERRORLOG files to the maximum value of 99 instead of the  
-- default of 6. This is especially useful if you also set up a Job to cycle   
-- the ERRORLOG files every week or so, to keep them small enough to load      
-- comfortably.                                                                
-------------------------------------------------------------------------------

EXEC xp_instance_regwrite 
 N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', 
 N'NumErrorLogs', 
 REG_DWORD, 
 99

GO

See this SQLAdmin post at xp_instance_regwrite syntax for details on how to use that procedure.

Down the Rabbit-Hole with CASE and Order of Evaluation

An Aluminum Falcon?

Sometimes you have what looks like a simple problem, but after a few hours of immersion in some dark corner of SQL Server, you end up more confused than when you started. And not just about the original problem: you're now also confused about stuff you were pretty sure about. That's when, as a seasoned software professional, you stop, get a good night's sleep, and come back fresh the next day with a clear mind.

Or, you can consume a few hundred fluid ounces of Diet Mountain Dew, read absolutely everything you can find on the subject until the wee hours of the morning, write endless little test programs, and mutter to yourself maniacally for hours on end, determined to beat this beastie into submission, and meanwhile your wife slips into "Puttering Around the House Much More Loudly Than Necessary While Sighing Heavily" mode.

You can guess which option I chose.

Punch it, Chewie

The simple problem I thought I was having was that the query optimizer was not making use of a CHECK constraint to avoid having to read data from a table. The real code is too large to fit in the margins, so I've shown below the smallest script that could possibly fail. It creates two tables, each with one column. Each column has a CHECK constraint. Then, 100,000 rows are INSERT'd into each table. (I chose to use a lot of data just to make the differences in the number of "logical reads" stand out. And I've done the INSERTs in kind of a weird way, because it executes more quickly and also displays decently on a web page. If you really want to feel my pain on my production machine, change the "GO 10000" and "GO 10000000".)

Next, the SELECT statement uses a CASE to compare a variable to the values in each table. If a match is found, it performs an aggregation (specifically, MAX) on that table. This is made simpler by having table t1 contain only "1"s, and table t2 contain only "2"s: this is enforced by the CHECK constraints.

-- Step 1 of 3 - Set up. ------------------------------------------ 
CREATE TABLE t1 (c INT)
CREATE TABLE t2 (c INT)

ALTER TABLE t1 WITH CHECK ADD CONSTRAINT ck1 CHECK (c = 1)
ALTER TABLE t2 WITH CHECK ADD CONSTRAINT ck2 CHECK (c = 2)
GO

INSERT t1 (c) VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
GO 10000

INSERT t2 (c) VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2)
GO 10000

-- Step 2 of 3 - Execution. --------------------------------------- 
SET STATISTICS IO ON
DECLARE @LtOrEq INT = 1

SELECT CASE 
           WHEN EXISTS (SELECT *      FROM t2 WHERE c <= @LtOrEq)
           THEN        (SELECT MAX(c) FROM t2 WHERE c <= @LtOrEq)

           WHEN EXISTS (SELECT *      FROM t1 WHERE c <= @LtOrEq)
           THEN        (SELECT MAX(c) FROM t1 WHERE c <= @LtOrEq)
       END

SET STATISTICS IO OFF

-- Step 3 of 3 - Cleanup. ----------------------------------------- 
DROP TABLE t1 
DROP TABLE t2 

Since @LtOrEq is set to "1", I would expect the optimizer to use t2.ck2 to avoid scanning the t2 table; in other words, skip the evaluation of the first WHEN/THEN pair entirely. Since the values cannot be anything but "2", I would have thought that a search for a value of "1" would be eliminated without even looking at the data. At least that how I thought it worked.

Put another way, as Remus Rusanu wrote, "SQL is a declarative language. You express in a query the desired result and the server is free to choose whatever means to deliver those results. As such the order of evaluation of SQL expressions is not determined and OR and AND evaluation short circuit does not occur. However for CASE the documentation actually states that the order of evaluation occurs in the order of declaration and the evaluation stops after the first condition is met."

But that's not what I was seeing. When you run the code above, as I did on several different servers, the output is:

-- Truncated the zero values for readability. 
Table 't1'.        Scan count 2, logical reads   2
Table 'Worktable'. Scan count 0, logical reads   0
Table 't2'.        Scan count 1, logical reads 345

Now, there's three things about this output I don't understand:

1. The tables are listed in reverse order to what I would expect: t1 appears before t2. Books OnLine is very clear that WHEN statements are evaluated from first to last (my emphasis added in blue):    
   

   Searched CASE expression:
       
   • Evaluates, in the order specified, Boolean_expression for each WHEN clause.
       
   • Returns result_expression of the first Boolean_expression that evaluates to TRUE.
       
   • If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

   So either the WHEN statements aren't evaluated in the correct order, or SET STATISTICS IO is outputting them in an incorrect order. Which is it?

2. Table t1 is scanned twice, which makes sense: once to see if any matching rows exist, and once to find the MAX. And it only needs to do a single logical read for each of them because all 100,000 32-byte integer values in t1.c1 will fit inside a single 8 KB page and... uh-oh. Why doesn't it take, um, 100000 * 32 / 8060 = 397.02 logical reads, since the table has to occupy at least that many pages?
   

3. Table t2 is scanned once, which also makes sense: since its WHEN clause evaluates to FALSE, it shouldn't evaluate its THEN clause. So then why does it do 345 logical reads? How can the table with the check constraint in place to keep it from being read have more reads than the table that being scanned for MAX?
   

As an aside, the BOL entry for CASE is very clear about order of execution, but the language is muddled regarding short-circuiting. Evaluates, in the order specified, Boolean_expression for each WHEN clause. Returns result_expression of the first Boolean_expression that evaluates to TRUE. This could be read - logically correctly, but not what the authors meant (or so I thought) - as: Evaluates, in the order specified, Boolean_expression for each WHEN clause. Then, when all the WHEN clauses have been evaluated, returns result_expression of the first Boolean_expression that evaluated to TRUE. There's at least one posting on the web which makes this (incorrect) argument. The BOL page should be edited to make it clear that short-circuiting does indeed occur... but read on...

I used a third-party tool to try to understand what's going on, did some amateur art-work to highlight the two tables, and rearranged the tree to help me visualize it all.

Clearly the t2 table is getting scanned twice.

There is Another

What finally pointed me in the right direction was a blog post by Mladen Prajdić about short-circuiting.  After setting up a test table t1:

Run this statement:

SELECT *
       FROM t1
      WHERE id / 0 = 1
        AND id = 2
        AND CONVERT (DATETIME, val) > GETDATE()

You'll get this error:

Msg 241, Level 16, State 1, Line 10
Conversion failed when converting datetime from character string.

The execution plan shows us how SQL Server parameterizes our select statement:

SELECT *
       FROM [t1]
      WHERE [id] / @1 = @2
        AND [id] = @3
        AND CONVERT([DATETIME], [val], 0) > GETDATE()

Conversion failed when converting datetime from character string

The most obvious condition to fail is of course the divide by zero. You'd think it would be the first to evaluate since it's obviously an illegal call and everything else would be discarded. However because of the statement parameterization this doesn't happen because at the condition evaluation time the values 0 and 1 aren't known. For SQL Server they are still valid parameters whose condition cost must be evaluated.

Aha! No wonder the t2 table's WHEN was evaluated: the values of the @LtOrEq variable couldn't be assumed at run-time. So if I change the variable to a literal "1":

SELECT CASE 
           WHEN EXISTS (SELECT *      FROM t2 WHERE c <= 1  /* @LtOrEq */  )
           THEN        (SELECT MAX(c) FROM t2 WHERE c <= 1  /* @LtOrEq */  )

           WHEN EXISTS (SELECT *      FROM t1 WHERE c <= 1  /* @LtOrEq */  )
           THEN        (SELECT MAX(c) FROM t1 WHERE c <= 1  /* @LtOrEq */  )
       END

... the results are:

-- Truncated the zero values for readability. 
Table 't1'. Scan count 2, logical reads 2

Much better. Table t2 isn't read from at all (and this is backed up by the query plan). The problem now is, how do I get this behavior and still use variables?

These aren't The Droids You're Looking For

But first I have to explain that this has nothing to do with "short-circuiting" of evaluation. What's that, you say? Imagine code like "IF a OR b". During execution, if "b" is not evaluated because "a" was evaluated as TRUE, that's short-circuiting. Some languages, like C++ and C#, guarantee that short-circuiting will occur. The T-SQL language - and this is where it gets confusing - can short-circuit if the engine feels like it. It's totally up to the query optimizer, which means you and I better not write code that depends on short-circuiting: it may work as we expect for years, and then one day some threshold is crossed, and it stops working as we - incorrectly - expected. There isn't even a way to force short-circuiting, and no, parentheses won't help.

What this is (partly) about, however, is the closely related concept of "order of evaluation". T-SQL doesn't make any promises about this, either, with one exception. Order of evaluation is guaranteed in the CASE statement: the WHEN clauses are guaranteed to be evaluated from top to bottom. This means that CASE guarantees short-circuiting.

Except when it doesn't.

I've Got a Bad Feeling About This...

As it turns out, there was a bug in the CASE statement that was causing the order of evaluation to not be done correctly (great discussion at Bart Duncan's SQL Blog ). This has been fixed.

But as it turns out, what I was seeing was not that bug: it was expected behavior. Microsoft states here, referring to BOL for SQL Server 2012 (my emphasis added in blue):

The following has been added to the Remarks section of the topic CASE (Transact-SQL) in Books Online.

The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.

WITH Data (value) AS
     (
     SELECT 0 UNION ALL
     SELECT 1
     )
     SELECT
     CASE
          WHEN MIN(value) <= 0 THEN 0
          WHEN MAX(1/value) >= 100 THEN 1
     END
     FROM Data

You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.

But there's even more. It's not only in the WHEN clause that aggregate expressions cause divide-by-zero errors. If you change the code above to this:

WITH Data (value) AS
     (
     SELECT 0 UNION ALL
     SELECT 1
     )
     SELECT
     CASE
          WHEN MIN(value) <= 0 THEN 0
          WHEN 1          >= 1 THEN MAX(1/value)    -- Aggregate in THEN.
     END
     FROM Data

... you'll still get a divide by zero error, this time caused by the evaluation of the second THEN clause!

Conclusion

And there you have it. The CASE statement guarantees order-of-evaluation, unless there are aggregates in the WHEN (or THEN) clauses. Is this a bug, or at least something that could be corrected by Microsoft? If you read the references below, some good arguments are made for not "fixing" this. Either way, at least now I know what's going on. I know this is a long and twisting post, so if you hung in here this far, thanks for reading!

References

Books OnLine: CASE (Transact-SQL) Read this first.

How SQL Server short-circuits WHERE condition evaluation by Mladen Prajdić is in my opinion the best post on the web about short-circuiting.

Understanding T-SQL Expression Short-Circuiting by Gianluca Sartori is brilliant and covers a lot of ground. A must-read.

http://stackoverflow.com/questions/5063995/behavior-of-sql-or-and-and-operator The interesting idea in this thread is that T-SQL always short-circuits, but because the boolean operators are commutative, the order (left-to-right, or right-to-left?) is indeterminate. Some very smart people here.

Don’t depend on expression short circuiting in T-SQL (not even with CASE) is very interesting. This is the article that is referenced in the Connect that ultimately led to the bug in CASE getting fixed.

Connect: Aggregates Don't Follow the Semantics Of CASE includes the explanation by Microsoft about how CASE doesn't short-circuit for aggregates, only scalar values.

xp_fileexist Fails Even if Mapped to a Local Folder

Apparently the undocumented xp_fileexist procedure really doesn't like mapped drives. I knew about the headaches involved with using this proc to read network drives (permissions issues are usually the problem), but I expected it to return correct results for a locally mapped drive.

To see this, map your C:\Windows folder to, for example the W: drive, and then run this script:

-- Demonstration that xp_fileexist really, 
-- really doesn't like mapped drives - 
-- even those mapped to a local path. 
DECLARE @FileName VARCHAR(128)

-- Local disk folder succeeds: 
--    File_Exists = 1 
--    File_is_a_Directory = 0 
--    Parent_Directory_Exists = 1 
SET @FileName = 'C:\Windows\Notepad.exe'
EXEC xp_fileexist @FileName

-- Map a drive to  \\MYMACHINE\c$\Windows  as drive  W: 

-- Local mapped drive FAILS: 
--    File_Exists = 0 
--    File_is_a_Directory = 0 
--    Parent_Directory_Exists = 0 
SET @FileName = 'W:\Notepad.exe'
EXEC xp_fileexist @FileName

If there's a workaround for this, I'd be interested. (Yes, I could be the 1,000,000th person to write my own CLR to do this, but I'm hoping for a better way.)

Display All System-Named Constraints in All User Databases

In my previous post, I mentioned that system-named objects get arbitrary names (like, "PK__Executio__05F5D74515DA3E5D"), and that this makes comparing database schemas difficult. To fix this problem, you have to rename the offending constraints, and to do that, you have to find them. So, this script displays all the system-named constraints in all the databases.

-- Display all the system-named constraints in all user databases. 
DECLARE @Sql NVARCHAR(MAX) 

SET @Sql = 'USE [?] '                                                                     + CHAR(10)
         +                                                                                  CHAR(10)
         + 'IF DB_ID() > 4 '                                                              + CHAR(10)     
         + '   AND EXISTS (SELECT * FROM sys.key_constraints WHERE is_system_named = 1) ' + CHAR(10)    
         +                                                                                  CHAR(10)
         + '    SELECT ''?''                           AS ''Database'' '                  + CHAR(10)
         + '         , OBJECT_NAME(parent_object_id)   AS ''Table'' '                     + CHAR(10)
         + '         , name                            AS ''Constraint'' '                + CHAR(10)
         + '      FROM sys.key_constraints '                                              + CHAR(10)
         + '     WHERE is_system_named = 1'
         
PRINT @Sql
EXEC sp_MSforeachdb @sql

The results will look something like this:

Database        Table                  Constraint 
ReportServer    ExecutionLogStorage    PK__Executio__05F5D74515DA3E5D 

Script to Drop and Re-Create Column Statistics

If you're like me, you use a third-party tool to compare SQL Server database schemas. No matter how hard we try to keep schemas in synch, some little change always creeps in, and being able to confirm that two database schemas are exactly alike helps us sleep at night.

Schemas can differ in important ways (who added that stored procedure?) or in seemingly trivial ways (okay, there's an extra space at the end of the column, I can live with that). One difference that may seem trivial, but isn't, is statistics, specifically column statistics. I learned that the hard way years ago, when I published an upgrade script that tried to drop a column from a table: the script failed for some users, because that column happened to have a statistics object on it. Turns out you can't drop a column with statistics: you have to drop the statistics first. Didn't know that.

Another annoying thing about statistics is that if the system creates them, they'll end up with random (sorry, arbitrary) names, and of course the names will be different between databases. This becomes an issue when you're trying to prove that two schemas are the same, and the screen is cluttered with these bogus differences. Yeah, I could disable checking statistics names entirely in my third-party tool, but that's just... No.

So, here's a script that will drop all the column statistics on all the tables in the current database, and then manually re-creates them. Not something you'd want to do on a production machine during peak hours, but very handy in a development environment.

-- Drop all column statistics on all tables.  We join sys.tables to ensure 
-- these are user (not system) tables.  Note that statistics for indexes 
-- cannot be dropped; the index itself has to be dropped to get rid of its 
-- statistics. 
DECLARE @Sql        NVARCHAR(MAX)       SET @Sql       = ''
DECLARE @TableName  sysname             SET @TableName = ''
DECLARE @StatsName  sysname             SET @StatsName = ''

DECLARE cur CURSOR LOCAL FOR
SELECT OBJECT_NAME(s.object_id)   AS 'TableName'
     , s.name                     AS 'StatsName'
  FROM sys.stats     s 
  JOIN sys.tables    t
    ON s.object_id = t.object_id
 WHERE s.object_id > 100
   AND s.name NOT IN 
         (SELECT name FROM sys.indexes WHERE object_id = s.object_id)

OPEN cur
FETCH NEXT FROM cur INTO @TableName, @StatsName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = 'DROP STATISTICS ' + QUOTENAME(@TableName) + '.' + QUOTENAME(@StatsName)
    PRINT @Sql
    EXEC sp_executesql @Sql
    FETCH NEXT FROM cur INTO @TableName, @StatsName
END

CLOSE cur 
DEALLOCATE cur 

-- Create the statistics for all tables and columns in the database that don't 
-- already exist.  This is much easier than using CREATE STATISTICS on each 
-- table, as you can only do 16 columns at a time, and have to name them each. 
RAISERROR('Creating statistics on all tables and columns that are missing them', 10, 1) WITH NOWAIT, LOG
EXEC sp_createstats @indexonly = 'NO', @fullscan = 'FULLSCAN', @norecompute ='NO'

-- Set the automatic UPDATE STATISTICS setting to 'ON' for all indexes and 
-- statistics for all tables and indexed views in the database. 
RAISERROR('Running sp_autostats for all tables...', 10, 1) WITH NOWAIT, LOG
EXEC sp_MSforeachtable '  PRINT ''?''   EXEC sp_autostats ''?'', @flagc = ''ON'' '

-- Display the new names of the column indexes. 
SELECT OBJECT_NAME(s.object_id)   AS 'TableName'
     , s.name                     AS 'StatsName'
  FROM sys.stats     s 
  JOIN sys.tables    t
    ON s.object_id = t.object_id
 WHERE s.object_id > 100
   AND s.name NOT IN 
         (SELECT name FROM sys.indexes WHERE object_id = s.object_id)

What can you expect from this script? Well, I ran this against my local ReportServer database (I'm not recommending that you do that, mind you), and got the following results:

DROP STATISTICS [ConfigurationInfo].[_WA_Sys_00000003_0425A276]
DROP STATISTICS [Catalog].[_WA_Sys_00000014_060DEAE8]
DROP STATISTICS [Users].[_WA_Sys_00000004_1273C1CD]
DROP STATISTICS [Users].[_WA_Sys_00000003_1273C1CD]
DROP STATISTICS [Users].[_WA_Sys_00000005_1273C1CD]
DROP STATISTICS [Policies].[_WA_Sys_00000002_173876EA]
DROP STATISTICS [ModelItemPolicy].[_WA_Sys_00000004_1920BF5C]
DROP STATISTICS [SecData].[_WA_Sys_00000003_1B0907CE]
DROP STATISTICS [Event].[_WA_Sys_00000007_2A4B4B5E]
DROP STATISTICS [Event].[_WA_Sys_00000006_2A4B4B5E]
DROP STATISTICS [Subscriptions].[_WA_Sys_00000010_2D27B809]
DROP STATISTICS [SnapshotData].[_WA_Sys_00000009_34C8D9D1]
DROP STATISTICS [SnapshotData].[_WA_Sys_0000000A_34C8D9D1]
DROP STATISTICS [ChunkData].[_WA_Sys_00000005_36B12243]
DROP STATISTICS [ChunkData].[_WA_Sys_00000004_36B12243]
DROP STATISTICS [Notifications].[_WA_Sys_00000012_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_0000000C_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_00000011_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_0000000F_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_00000004_3A81B327]
DROP STATISTICS [RunningJobs].[_WA_Sys_0000000A_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000006_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000008_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000002_48CFD27E]
DROP STATISTICS [DBUpgradeHistory].[_WA_Sys_00000002_731B1205]
DROP STATISTICS [Keys].[_WA_Sys_00000004_7E6CC920]
DROP STATISTICS [Keys].[_WA_Sys_00000006_7E6CC920]
Creating statistics on all tables and columns that are missing them
Table 'ReportServer.dbo.History': Creating statistics for the following columns:
     SnapshotDate
Table 'ReportServer.dbo.ConfigurationInfo': Creating statistics for the following columns:
     Value
Table 'ReportServer.dbo.Catalog': Creating statistics for the following columns:
     Name
     Content
     Intermediate
     Property
     Description
     Hidden
     CreatedByID
     CreationDate
     ModifiedByID
     ModifiedDate
     MimeType
     SnapshotLimit
     Parameter
     PolicyID
     PolicyRoot
     ExecutionFlag
     ExecutionTime
     SubType
     ComponentID
Table 'ReportServer.dbo.UpgradeInfo': Creating statistics for the following columns:
     Status
Table 'ReportServer.dbo.SubscriptionsBeingDeleted': Creating statistics for the following columns:
     CreationDate
Table 'ReportServer.dbo.ModelDrill': Creating statistics for the following columns:
     ReportID
     ModelItemID
     Type
Table 'ReportServer.dbo.Segment': Creating statistics for the following columns:
     Content
Table 'ReportServer.dbo.ChunkSegmentMapping': Creating statistics for the following columns:
     StartByte
     LogicalByteCount
     ActualByteCount
Table 'ReportServer.dbo.ModelPerspective': Creating statistics for the following columns:
     ID
     PerspectiveID
     PerspectiveName
     PerspectiveDescription
Table 'ReportServer.dbo.CachePolicy': Creating statistics for the following columns:
     ExpirationFlags
     CacheExpiration
Table 'ReportServer.dbo.SegmentedChunk': Creating statistics for the following columns:
     ChunkFlags
     ChunkName
     ChunkType
     Version
     MimeType
Table 'ReportServer.dbo.Users': Creating statistics for the following columns:
     UserType
     AuthType
     UserName
Table 'ReportServer.dbo.ExecutionLogStorage': Creating statistics for the following columns:
     InstanceName
     ReportID
     UserName
     ExecutionId
     RequestType
     Format
     Parameters
     ReportAction
     TimeEnd
     TimeDataRetrieval
     TimeProcessing
     TimeRendering
     Source
     Status
     ByteCount
     RowCount
Table 'ReportServer.dbo.DataSource': Creating statistics for the following columns:
     Name
     Extension
     Link
     CredentialRetrieval
     Prompt
     ConnectionString
     OriginalConnectionString
     OriginalConnectStringExpressionBased
     UserName
     Password
     Flags
     Version
Table 'ReportServer.dbo.Policies': Creating statistics for the following columns:
     PolicyFlag
Table 'ReportServer.dbo.ModelItemPolicy': Creating statistics for the following columns:
     ModelItemID
     PolicyID
Table 'ReportServer.dbo.SecData': Creating statistics for the following columns:
     AuthType
     XmlDescription
     NtSecDescPrimary
     NtSecDescSecondary
Table 'ReportServer.dbo.Roles': Creating statistics for the following columns:
     Description
     TaskMask
     RoleFlags
Table 'ReportServer.dbo.PolicyUserRole': Creating statistics for the following columns:
     UserID
     PolicyID
Table 'ReportServer.dbo.Event': Creating statistics for the following columns:
     EventType
     EventData
     ProcessHeartbeat
     BatchID
Table 'ReportServer.dbo.Subscriptions': Creating statistics for the following columns:
     OwnerID
     Report_OID
     Locale
     InactiveFlags
     ExtensionSettings
     ModifiedByID
     ModifiedDate
     Description
     LastStatus
     EventType
     MatchData
     LastRunTime
     Parameters
     DataSettings
     DeliveryExtension
     Version
     ReportZone
Table 'ReportServer.dbo.ActiveSubscriptions': Creating statistics for the following columns:
     SubscriptionID
     TotalNotifications
     TotalSuccesses
     TotalFailures
Table 'ReportServer.dbo.SnapshotData': Creating statistics for the following columns:
     CreatedDate
     ParamsHash
     QueryParams
     EffectiveParams
     Description
     DependsOnUser
     TransientRefcount
     ExpirationDate
     PageCount
     HasDocMap
     PaginationMode
     ProcessingFlags
Table 'ReportServer.dbo.ChunkData': Creating statistics for the following columns:
     ChunkFlags
     ChunkName
     ChunkType
     Version
     MimeType
     Content
Table 'ReportServer.dbo.Notifications': Creating statistics for the following columns:
     SubscriptionID
     ActivationID
     ReportID
     SnapShotDate
     ExtensionSettings
     Locale
     Parameters
     Attempt
     SubscriptionLastRunTime
     DeliveryExtension
     SubscriptionOwnerID
     IsDataDriven
     BatchID
     ProcessHeartbeat
     Version
     ReportZone
Table 'ReportServer.dbo.Batch': Creating statistics for the following columns:
     Action
     Item
     Parent
     Param
     BoolParam
     Content
     Properties
Table 'ReportServer.dbo.Schedule': Creating statistics for the following columns:
     StartDate
     Flags
     NextRunTime
     LastRunTime
     EndDate
     RecurrenceType
     MinutesInterval
     DaysInterval
     WeeksInterval
     DaysOfWeek
     DaysOfMonth
     Month
     MonthlyWeek
     State
     LastRunStatus
     ScheduledRunTimeout
     CreatedById
     EventType
     EventData
     Type
     ConsistancyCheck
     Path
Table 'ReportServer.dbo.ReportSchedule': Creating statistics for the following columns:
     ReportAction
Table 'ReportServer.dbo.RunningJobs': Creating statistics for the following columns:
     StartDate
     RequestName
     RequestPath
     UserId
     Description
     Timeout
     JobAction
     JobType
     JobStatus
Table 'ReportServer.dbo.ServerParametersInstance': Creating statistics for the following columns:
     ParentID
     Path
     CreateDate
     ModifiedDate
     Timeout
     ParametersValues
Table 'ReportServer.dbo.DBUpgradeHistory': Creating statistics for the following columns:
     DbVersion
     User
     DateTime
Table 'ReportServer.sys.queue_messages_1977058079': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.sys.queue_messages_2009058193': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.dbo.DataSets': Creating statistics for the following columns:
     Name
Table 'ReportServer.sys.queue_messages_2041058307': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.sys.filestream_tombstone_2073058421': Creating statistics for the following columns:
     oplsn_bOffset
     oplsn_slotid
     rowset_guid
     column_guid
     filestream_value_name
     transaction_sequence_num
     status
Table 'ReportServer.sys.syscommittab': Creating statistics for the following columns:
     commit_lbn
     commit_csn
     commit_time
     dbfragid
Table 'ReportServer.dbo.Keys': Creating statistics for the following columns:
     MachineName
     InstanceName
     Client
     PublicKey
     SymmetricKey
Table 'ReportServer.dbo.ServerUpgradeHistory': Creating statistics for the following columns:
     ServerVersion
     User
     DateTime
 
Statistics have been created for the 253 listed columns of the above tables.
Running sp_autostats for all tables...
[dbo].[History]
[dbo].[ConfigurationInfo]
[dbo].[Catalog]
[dbo].[UpgradeInfo]
[dbo].[SubscriptionsBeingDeleted]
[dbo].[ModelDrill]
[dbo].[Segment]
[dbo].[ChunkSegmentMapping]
[dbo].[ModelPerspective]
[dbo].[CachePolicy]
[dbo].[SegmentedChunk]
[dbo].[Users]
[dbo].[ExecutionLogStorage]
[dbo].[DataSource]
[dbo].[Policies]
[dbo].[ModelItemPolicy]
[dbo].[SecData]
[dbo].[Roles]
[dbo].[PolicyUserRole]
[dbo].[Event]
[dbo].[Subscriptions]
[dbo].[ActiveSubscriptions]
[dbo].[SnapshotData]
[dbo].[ChunkData]
[dbo].[Notifications]
[dbo].[Batch]
[dbo].[Schedule]
[dbo].[ReportSchedule]
[dbo].[RunningJobs]
[dbo].[ServerParametersInstance]
[dbo].[DBUpgradeHistory]
[dbo].[DataSets]
[dbo].[Keys]
[dbo].[ServerUpgradeHistory]

TableName          StatsName
History                  SnapshotDate
ConfigurationInfo        Value
Catalog        Name
Catalog        Content
Catalog        Intermediate
Catalog        Property
Catalog        Description
Catalog        Hidden
Catalog        CreatedByID
Catalog        CreationDate
Catalog        ModifiedByID
Catalog        ModifiedDate
Catalog        MimeType
Catalog        SnapshotLimit
Catalog        Parameter
Catalog        PolicyID
Catalog        PolicyRoot
Catalog        ExecutionFlag
Catalog        ExecutionTime
Catalog        SubType
Catalog        ComponentID
UpgradeInfo        Status
SubscriptionsBeingDeleted        CreationDate
ModelDrill        ReportID
ModelDrill        ModelItemID
ModelDrill        Type
Segment        Content
ChunkSegmentMapping        StartByte
ChunkSegmentMapping        LogicalByteCount
ChunkSegmentMapping        ActualByteCount
ModelPerspective        ID
ModelPerspective        PerspectiveID
ModelPerspective        PerspectiveName
ModelPerspective        PerspectiveDescription
CachePolicy        ExpirationFlags
CachePolicy        CacheExpiration
SegmentedChunk        ChunkFlags
SegmentedChunk        ChunkName
SegmentedChunk        ChunkType
SegmentedChunk        Version
SegmentedChunk        MimeType
Users        UserType
Users        AuthType
Users        UserName
ExecutionLogStorage        InstanceName
ExecutionLogStorage        ReportID
ExecutionLogStorage        UserName
ExecutionLogStorage        ExecutionId
ExecutionLogStorage        RequestType
ExecutionLogStorage        Format
ExecutionLogStorage        Parameters
ExecutionLogStorage        ReportAction
ExecutionLogStorage        TimeEnd
ExecutionLogStorage        TimeDataRetrieval
ExecutionLogStorage        TimeProcessing
ExecutionLogStorage        TimeRendering
ExecutionLogStorage        Source
ExecutionLogStorage        Status
ExecutionLogStorage        ByteCount
ExecutionLogStorage        RowCount
DataSource        Name
DataSource        Extension
DataSource        Link
DataSource        CredentialRetrieval
DataSource        Prompt
DataSource        ConnectionString
DataSource        OriginalConnectionString
DataSource        OriginalConnectStringExpressionBased
DataSource        UserName
DataSource        Password
DataSource        Flags
DataSource        Version
Policies        PolicyFlag
ModelItemPolicy        ModelItemID
ModelItemPolicy        PolicyID
SecData        AuthType
SecData        XmlDescription
SecData        NtSecDescPrimary
SecData        NtSecDescSecondary
Roles        Description
Roles        TaskMask
Roles        RoleFlags
PolicyUserRole        UserID
PolicyUserRole        PolicyID
Event        EventType
Event        EventData
Event        ProcessHeartbeat
Event        BatchID
Subscriptions        OwnerID
Subscriptions        Report_OID
Subscriptions        Locale
Subscriptions        InactiveFlags
Subscriptions        ExtensionSettings
Subscriptions        ModifiedByID
Subscriptions        ModifiedDate
Subscriptions        Description
Subscriptions        LastStatus
Subscriptions        EventType
Subscriptions        MatchData
Subscriptions        LastRunTime
Subscriptions        Parameters
Subscriptions        DataSettings
Subscriptions        DeliveryExtension
Subscriptions        Version
Subscriptions        ReportZone
ActiveSubscriptions        SubscriptionID
ActiveSubscriptions        TotalNotifications
ActiveSubscriptions        TotalSuccesses
ActiveSubscriptions        TotalFailures
SnapshotData        CreatedDate
SnapshotData        ParamsHash
SnapshotData        QueryParams
SnapshotData        EffectiveParams
SnapshotData        Description
SnapshotData        DependsOnUser
SnapshotData        TransientRefcount
SnapshotData        ExpirationDate
SnapshotData        PageCount
SnapshotData        HasDocMap
SnapshotData        PaginationMode
SnapshotData        ProcessingFlags
ChunkData        ChunkFlags
ChunkData        ChunkName
ChunkData        ChunkType
ChunkData        Version
ChunkData        MimeType
ChunkData        Content
Notifications        SubscriptionID
Notifications        ActivationID
Notifications        ReportID
Notifications        SnapShotDate
Notifications        ExtensionSettings
Notifications        Locale
Notifications        Parameters
Notifications        Attempt
Notifications        SubscriptionLastRunTime
Notifications        DeliveryExtension
Notifications        SubscriptionOwnerID
Notifications        IsDataDriven
Notifications        BatchID
Notifications        ProcessHeartbeat
Notifications        Version
Notifications        ReportZone
Batch        Action
Batch        Item
Batch        Parent
Batch        Param
Batch        BoolParam
Batch        Content
Batch        Properties
Schedule        StartDate
Schedule        Flags
Schedule        NextRunTime
Schedule        LastRunTime
Schedule        EndDate
Schedule        RecurrenceType
Schedule        MinutesInterval
Schedule        DaysInterval
Schedule        WeeksInterval
Schedule        DaysOfWeek
Schedule        DaysOfMonth
Schedule        Month
Schedule        MonthlyWeek
Schedule        State
Schedule        LastRunStatus
Schedule        ScheduledRunTimeout
Schedule        CreatedById
Schedule        EventType
Schedule        EventData
Schedule        Type
Schedule        ConsistancyCheck
Schedule        Path
RunningJobs        StartDate
RunningJobs        RequestName
RunningJobs        RequestPath
RunningJobs        UserId
RunningJobs        Description
RunningJobs        Timeout
RunningJobs        JobAction
RunningJobs        JobType
RunningJobs        JobStatus
ServerParametersInstance        ParentID
ServerParametersInstance        Path
ServerParametersInstance        CreateDate
ServerParametersInstance        ModifiedDate
ServerParametersInstance        Timeout
ServerParametersInstance        ParametersValues
DBUpgradeHistory        DbVersion
DBUpgradeHistory        User
DBUpgradeHistory        DateTime
DataSets        Name
Keys        MachineName
Keys        InstanceName
Keys        Client
Keys        PublicKey
Keys        SymmetricKey
ServerUpgradeHistory        ServerVersion
ServerUpgradeHistory        User
ServerUpgradeHistory        DateTime

Note that the arbitrarily named column statistics such as [ConfigurationInfo].[_WA_Sys_00000003_0425A276] got renamed to the more standard (and thus, more easily-compared) name [ConfigurationInfo].[Value].

Proc to Fix the Too Many Virtual Log Files Problem

There could be a beast lurking in your log files, robbing you of performance, and you might not even know its name. But fear not - I'll give you a weapon to kill the beast.

If you've never heard of "virtual log files" (VLFs), read these articles: they'll well-written, short, and extremely informative. You owe it to yourself to read the originals, but I'll sum them up briefly here.

VLFs: A Quick Overview

What we think of as a log file is actually a bunch of interconnected of disk space chunks called VLFs. A log file can consist of a few, or hundreds, or even thousands of VLFs, and a new one is created every time the log file needs to expand. In a perfect world, you could create your log file to be the biggest it would ever need to be; since the log file would never need to expand, there would be no new VLFs created. Problem solved.

Unfortunately, if you're reading this, you don't live in a perfect world. Very likely, you're responsible for databases that have too many VLFs, and having too many VLFs is bad, especially for performance. So, your first step is to check your databases' log files to see how many VLFs they have. Then, if they have too many VLFs, you can fix the problem with a few simple commands. Fortunately for you, I've created a stored procedure that does both steps, so you can spend your time on more interesting things.

(How many VLFs is "too many"? It depends. In this blog, our bias is toward providing tools to get things done; I leave theoretical questions to people far more qualified. I picked "50" because Kimberly said so, and that's good enough for me.)

The Weapon

The script below will create a stored procedure that will examine and, if necessary, fix, the VLF problem in all the databases in your instance. Usually, you'll only need to run this once in a very long while, but again, it depends. Note the "@ExecuteFix" argument - setting this flag to zero allows you to see what T-SQL code would be executed; setting it to one actually executes that T-SQL.

-- Drop if it already exists. 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_FixExcessiveLogFileVlfs]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------------------------------
-- Display, logs, and fixes all databases with more than 50 Virtual Log Files. 
--                                                                             
-- See: http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/
-- See: http://sqlskills.com/blogs/kimberly/post/8-steps-to-better-transaction-log-throughput.aspx
-- See: http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx
--                                                                             
-- Created 2012-04-28 - Larry Leonard - http://SqlSoundings.blogspot.com/      
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs]  @ExecuteFix INT = 0
AS
BEGIN
    SET NOCOUNT ON

    -- Set up temp tables. 
    IF OBJECT_ID('tempdb..#stage') IS NOT NULL
        DROP TABLE #stage
        
    CREATE TABLE #stage
    (
        FileID          INT
      , FileSizeBytes   BIGINT
      , StartOffset     BIGINT
      , FSeqNo          BIGINT
      , [Status]        BIGINT
      , Parity          BIGINT
      , CreateLSN       NUMERIC(38)
    )
     
    IF OBJECT_ID('tempdb..#results') IS NOT NULL
        DROP TABLE #results

    CREATE TABLE #results
    (
        DatabaseName    sysname
      , LogFileName     sysname
      , VlfCount        INT
      , LogFileSizeMB   INT 
    )
     
    -- Gather the log file information into the temp tables. 
    DECLARE @Sql NVARCHAR(MAX)

    SET @Sql = 'USE [?] '
             + ''
             + 'INSERT INTO #stage '
             + '  EXEC sp_executesql N''DBCC LOGINFO ([?])'' '
             + ''
             + 'INSERT INTO #results '
             + '    SELECT DB_NAME(), MIN(FILE_NAME(FileID)), COUNT(*), SUM(FileSizeBytes) / 1024 / 1024 '
             + '      FROM #stage '
             + ' '
             + 'TRUNCATE TABLE #stage '
             
    EXEC sp_msforeachdb @Sql

    -- Log the results. 
    DECLARE @DatabaseName   sysname
    DECLARE @LogFileName    sysname
    DECLARE @VlfCount       INT 
    DECLARE @LogFileSizeMB  INT

    DECLARE cur CURSOR LOCAL FOR
        SELECT DatabaseName 
             , VlfCount
             , LogFileSizeMB
          FROM #results
      ORDER BY VlfCount DESC

    OPEN cur
    FETCH NEXT FROM cur INTO @DatabaseName, @VlfCount, @LogFileSizeMB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        RAISERROR('Database: %25s  -  Virtual Log Files: %4d  -  Size: %5d MB', 10, 1, @DatabaseName, @VlfCount, @LogFileSizeMB) WITH NOWAIT, LOG
        FETCH NEXT FROM cur INTO @DatabaseName, @VlfCount, @LogFileSizeMB
    END
          
    CLOSE cur
    DEALLOCATE cur

    -- Display the results. 
    RAISERROR(' ', 10, 1) WITH NOWAIT
    
    SELECT *
      FROM #results
     ORDER BY VlfCount DESC

    -- Fix the log files with too many VLFs.  We add two MB to the size because 
    -- ALTER DATABASE requires that we make the log larger.  Adding one doesn't 
    -- work, because of rounding issues when dividing, I think. 
    DECLARE cur CURSOR LOCAL FOR
        SELECT DatabaseName 
             , LogFileName
             , VlfCount
             , LogFileSizeMB
          FROM #results
         WHERE VlfCount > 50
      ORDER BY VlfCount DESC

    OPEN cur
    FETCH NEXT FROM cur INTO @DatabaseName, @LogFileName, @VlfCount, @LogFileSizeMB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Sql = 'USE ' + @DatabaseName                                          + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'CHECKPOINT '                                                   + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'DBCC SHRINKFILE (' + @LogFileName + ', 0, TRUNCATEONLY) '      + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'ALTER DATABASE ' + @DatabaseName                               + CHAR(10)
                 + '   MODIFY FILE '                                               + CHAR(10)
                 + '( '                                                            + CHAR(10)
                 + '      NAME = ' + @LogFileName                                  + CHAR(10)
                 + '    , SIZE = ' + CAST(@LogFileSizeMB + 2 AS NVARCHAR) + ' MB ' + CHAR(10)
                 + ') '                                                            + CHAR(10)

        IF @ExecuteFix = 0
        BEGIN
            RAISERROR('-- Proposed T-SQL code for database %20s: Log File: %10s', 10, 1, @DatabaseName, @LogFilename) WITH NOWAIT, LOG
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT, LOG
        END
        ELSE
        BEGIN
            RAISERROR('Processing database %20s: Log File: %10s', 10, 1, @DatabaseName, @LogFilename) WITH NOWAIT, LOG
            EXEC sp_executesql @Sql
        END
        
        FETCH NEXT FROM cur INTO @DatabaseName, @LogFileName, @VlfCount, @LogFileSizeMB
    END
          
    CLOSE cur
    DEALLOCATE cur

    -- Done. 
    IF OBJECT_ID('tempdb..#stage') IS NOT NULL
        DROP TABLE #stage

    IF OBJECT_ID('tempdb..#results') IS NOT NULL
        DROP TABLE #results
END

/* Testbed. 
 
 EXEC dbo.dba_FixExcessiveLogFileVlfs @ExecuteFix = 0 
 
 */

GO

SSIS: "And If You've Made it Wrong..."

When you start working with SSIS, there's one thing nobody mentions: you have to learn a new syntax for expressions. It's not T-SQL, it's not VB.NET, it's not JavaScript, it's not like anything you've made before. It's "SSIS Expression Syntax", and you can learn all about it at Integration Services Expression Reference. (And yes, I'm pretty sure "it won't keep you comin' back for more"!)

I bring this up because until you understand this, you're going to think SSIS was written in FORTRAN, because anytime you have to enter an expression, nothing works. Ever. For example, let's write an expression in T-SQL to strip out all occurrences of the hex 12 (0x0C, "FF") character from a string. Simple, right?

-- T-SQL code.  Don't try this in SSIS. 
DECLARE @FF CHAR
SET @FF = 0X0C
DECLARE @Msg VARCHAR(50)

SET @Msg = 'Hello ' + @FF + 'SSIS'
PRINT @Msg

SET @Msg = REPLACE(@Msg, 0x0C, '')
PRINT @Msg

The output, depending on your font, collation, codepage, which version of Windows you're using, and hat size, will look something like this:

Hello §SSIS
Hello SSIS

So, we've proven we know how to use the REPLACE function in T-SQL. Admirable, but it doesn't help us in SSIS. That is, this doesn't work:

REPLACE(SampleBeg, 0x0C, '')

Try that expression there, and you'll get the usual non-helpful error SSIS message stack. For some reason, SSIS likes double-quotes, not single-quotes, and of course has its own unique way of expressing character constants in hex:

REPLACE(SampleBeg, "\x000C", "")

I'm sure there are excellent reasons why SSIS is such a Frankenstein's monster. When you're building something that might not be such a good idea to begin with, you're going to find some funky design choices forced on you, and my guess is that's what happened with SSIS. All I know is, the way it's been designed, it will be a long time before I can get it to do what I want.

How Much Data Is In That Column?

This is a special-purpose script, but it still may be useful to someone, someday.

Imagine you have many tables with similar filenames and the same column names. This might happen if the tables are created automatically by a Job on a daily basis, for example. For each column you specify, this script displays the declared width, the average number of characters stored, and the minimum and maximum number of characters stored, and it does this across all tables matching a LIKE pattern than you also specify.

This script might be useful if you wanted to see the average "fullness" of each column, or how big the widest value is for a given column. This might be good to know if you're planning to copy data from this column to another table, for example.

-------------------------------------------------------------------------------
/* Sample setup code:                                                          
                                                                               
 USE AdventureWorks                                                            
                                                                               
 IF OBJECT_ID('dbo.Invoice_2011_10') IS NOT NULL DROP TABLE dbo.Invoice_2011_10
 IF OBJECT_ID('dbo.Invoice_2011_11') IS NOT NULL DROP TABLE dbo.Invoice_2011_11
 IF OBJECT_ID('dbo.Invoice_2011_12') IS NOT NULL DROP TABLE dbo.Invoice_2011_12
                                                                               
 CREATE TABLE dbo.Invoice_2011_10 (AccountNumber INT, Comment NVARCHAR(50))    
 CREATE TABLE dbo.Invoice_2011_11 (AccountNumber INT, Comment NVARCHAR(50))    
 CREATE TABLE dbo.Invoice_2011_12 (AccountNumber INT, Comment NVARCHAR(50))    
                                                                               
 INSERT dbo.Invoice_2011_10 (AccountNumber, Comment) VALUES (  2, 'ABC')       
 INSERT dbo.Invoice_2011_11 (AccountNumber, Comment) VALUES (234, 'ABCDEF')    
 INSERT dbo.Invoice_2011_12 (AccountNumber, Comment) VALUES (234, 'ABCDEFGHI') 
                                                                               
 -- Now set the values as follows, and run this script.                        
 --     @SchemaName  is 'dbo'                                                  
 --     @TableFormat is 'Invoice_2011%'                                        
 --     @tColumns    should contain 'AccountNumber' and 'Comment' *only*       
                                                                               
                                                                             */
-------------------------------------------------------------------------------

SET NOCOUNT ON


-------------------------------------------------------------------------------
-- These are the variables you need to set.  There are no changes necessary    
-- after this section.                                                         
-------------------------------------------------------------------------------

-- The schema for the tables. 
DECLARE @SchemaName sysname = 'dbo'

-- The LIKE format for all the tables you want to examine. 
DECLARE @TableFormat sysname = 'Invoice_2011%'

-- The set of column names in the set of tables you want to report on. 
DECLARE @tColumns TABLE (ColumnName sysname)

INSERT @tColumns
       (ColumnName)
VALUES
       ('AccountNumber')
     , ('Comment')
        

-------------------------------------------------------------------------------
-- Variables (you don't need to change these).                                 
-------------------------------------------------------------------------------

DECLARE @TableName    sysname       = ''
DECLARE @ColumnName   sysname       = ''

DECLARE @Sql          NVARCHAR(MAX) = ' '                                 + CHAR(10)
                                    + 'SET NOCOUNT ON'                    + CHAR(10)
                                    + 'SET STATISTICS IO OFF'             + CHAR(10)
                                    + ' '                                 + CHAR(10)
                                    + 'DECLARE @tTotals TABLE '           + CHAR(10)
                                    + '    (ColumnName   sysname,'        + CHAR(10)
                                    + '     Example      NVARCHAR(MAX), ' + CHAR(10)
                                    + '     AverageLen   INT, '           + CHAR(10)
                                    + '     MaximumLen   INT, '           + CHAR(10)
                                    + '     DeclaredSize INT) '           + CHAR(10)

-- The *COLUMNAME* is just a placeholder; each column will call REPLACE with 
-- its name from the set you specified above.  Done this way to keep all the 
-- variables you need to change in one place, and at the top of the file. 
DECLARE @ColumnsSQL   NVARCHAR(MAX) = 'QUOTENAME(''*COLUMNNAME*''), '            + CHAR(10)
                                    + '       MIN([*COLUMNNAME*]), '             + CHAR(10)
                                    + '       AVG(DATALENGTH([*COLUMNNAME*])), ' + CHAR(10)
                                    + '       MAX(DATALENGTH([*COLUMNNAME*])), ' + CHAR(10)

DECLARE @ColLengthSQL NVARCHAR(MAX) = ''


-------------------------------------------------------------------------------
-- Process each table that matches the LIKE expression.                        
-------------------------------------------------------------------------------

RAISERROR('All column sizes are in bytes, not characters.', 10, 1) WITH NOWAIT

DECLARE curTables CURSOR FOR
 SELECT name
   FROM sys.tables
  WHERE name LIKE @TableFormat ESCAPE '$'
    AND type_desc = 'USER_TABLE'
       AND SCHEMA_ID(@SchemaName) = schema_id
  ORDER BY name

OPEN curTables
FETCH NEXT FROM curTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql += ' '                                                                              + CHAR(10)
             +  'RAISERROR('' '', 10, 1) WITH NOWAIT'                                            + CHAR(10)
             +  'RAISERROR(''Table ' + @SchemaName + '.' + @TableName + ''', 10, 1) WITH NOWAIT' + CHAR(10)

    SET @ColLengthSQL = '       MIN(COL_LENGTH(''' 
                      + @SchemaName + '.' + @TableName 
                      + ''', ''*COLUMNNAME*'')) ' + CHAR(10)
    
    -- Process each column in the set. 
    DECLARE curColumns CURSOR FOR
        SELECT ColumnName
          FROM @tColumns
   
    OPEN curColumns
    FETCH NEXT FROM curColumns INTO @ColumnName
              
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        -- Some types don't support MIN, etc. 
        IF EXISTS (SELECT *
                     FROM sys.columns   c
                     JOIN sys.types     t
                       ON c.system_type_id = t.system_type_id
                    WHERE t.name IN ('uniqueidentifier', 'bit', 'xml')
                      AND c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                      AND c.name = @ColumnName)
        BEGIN
            RAISERROR('Skipping %s.%s.%s because type is unsupported', 10, 1, @SchemaName, @TableName, @ColumnName) WITH NOWAIT
            FETCH NEXT FROM curColumns INTO @ColumnName
            CONTINUE
        END

        -- Build the INSERT statement. 
        SET @Sql += ' '                               + CHAR(10)
                 +  'INSERT @tTotals (ColumnName, '   + CHAR(10)
                 +  '                 Example, '      + CHAR(10)
                 +  '                 AverageLen, '   + CHAR(10)
                 +  '                 MaximumLen, '   + CHAR(10)
                 +  '                 DeclaredSize) ' + CHAR(10)
                 +  'SELECT '
                 
        SET @Sql += REPLACE(@ColumnsSQL + @ColLengthSQL, '*COLUMNNAME*', @ColumnName)
                 +  '  FROM ' + @SchemaName + '.' + @TableName + CHAR(10)
                 +  ' WHERE ' + QUOTENAME(@ColumnName) + ' IS NOT NULL '  + CHAR(10)
                 
        SET @Sql += ' '                                                                + CHAR(10)
                 +  'RAISERROR(''    Column ' + @ColumnName + ''', 10, 1) WITH NOWAIT' + CHAR(10)
        
        FETCH NEXT FROM curColumns INTO @ColumnName
    END
    
    CLOSE curColumns
    DEALLOCATE curColumns

    FETCH NEXT FROM curTables INTO @TableName
END
    
CLOSE curTables
DEALLOCATE curTables


-------------------------------------------------------------------------------
-- Done with the tables.  Display the results.                                 
-------------------------------------------------------------------------------

SET @Sql += ' '                                                     + CHAR(10)
         +  'RAISERROR('' '', 10, 1) WITH NOWAIT'                   + CHAR(10)
         +  ' '                                                     + CHAR(10)
         +  '  SELECT ColumnName          AS [Column Name], '       + CHAR(10)
         +  '         MAX(Example)        AS [Example], '           + CHAR(10)
         +  '         AVG(AverageLen)     AS [Average Length], '    + CHAR(10)
         +  '         MAX(MaximumLen)     AS [Maximum Length], '    + CHAR(10)
         +  '         MIN(DeclaredSize)   AS [Declared Size] '      + CHAR(10)
         +  '    FROM @tTotals '                                    + CHAR(10)
         +  'GROUP BY ColumnName '                                  + CHAR(10)
         +  'ORDER BY ColumnName '                                  
     
EXEC (@Sql)

-- PRINT is limited to 8000 bytes.  There are better solutions out there. 
DECLARE @Idx INT = 1

WHILE @Idx < LEN(@Sql)
BEGIN
    PRINT SUBSTRING(@Sql, @Idx, 4000)
    SET @Idx += 4000
END

Adding Up the Logical Reads in the Output Window

Probably the first thing I learned about query tuning is that "clock-on-the-wall" time is definitely not the thing you want to measure. The elapsed time a query takes can be influenced by countless factors (other processes running, disk speed, number of processors, amount of RAM, phase of the moon, a few hundred database settings) that it's too rough a metric for serious performance tuning.

What I've been taught to measure, instead, are logical reads, which are the number of disk I/O reads needed to execute the query. If the query doesn't change, and the underlying data doesn't change, it's been my experience (and it makes sense theoretically), that you can run a given query all day long, under any load conditions you can create, and the number of logical reads will always be the same. This immunity to outside "noise" makes it ideal for tuning: you can be pretty sure (given the same schema, of course - indexes leap to mind - and at least similar data) that the logical read counts on your machine will be the same on another machine (such as the Production server, or your boss's laptop).

Ok, so how do we capture logical reads? Easy - simply enable the STATISTICS IO option, like so:

SET STATISTICS IO ON

Once turned on for a given session, this emits the number logical reads made for every table (including temp tables) for every statement executed, until you turn it off (by saying SET STATISTICS IO OFF). Let's look at a single line that would be emitted:

Table 'Invoices'. Scan count 1, logical reads 332, physical reads 10, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You'll notice a lot of other values displayed on this line, but for now, ignore them. One that you should especially ignore (for now) is physical reads, because its count is included in the logical reads value. That is, in our one-line example, we can see there are going to be 332 pages read from somewhere. That "somewhere" will be either in RAM (the buffer pool), or from disk (your "C:" drive, for example), but both of these "somewheres" are counted by logical reads. Physical reads only counts what was read from disk this time, which is the kind of unpredictable "clock-on-the-wall" information we know to be unreliable for query tuning (for now). Why are they unpredictable? Because who knows why the page wasn't in buffer pool in memory, and so had to read from disk, at the precise moment we needed it? It's unknowable, and even if it weren't - if you had perfect knowledge of the innards of the computer at that moment - it's still unpredictable.

Looking at our one-line example above, everything seems pretty rosy. SET STATISTICS IO ON spits out the logical reads we need, so all we have to do is total them up. But this is where it gets ugly, because the information we're interested in is interspersed with our own PRINT messages, RAISERROR output, warning and informational messages from the system, and data output from SELECT statements (if we're using "Output to Text" mode, which I often do, as it's the only way to get the dang output window to scroll). This can all be quite a mess to read:

Starting at 12:25:30 PM...
Table 'Customers'. Scan count 6, logical reads 10213968, physical reads 125391, read-ahead reads 254294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CustomerNum   InvoiceNum   Amt
----------- ------------ --------------------------------------
2344   1331111   333.23
4492   4837227 44222.84
3434   222444   91811.33
98474   54422  22.09
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Updating the total prices...
(1 row(s) affected)
Table 'Invoices'. Scan count 1, logical reads 332, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
12:30:38: Added row to the Invoices table for 2011/10/12
Warning: Aggregate calculation excluded NULL values.
Table 'Worktable'. Scan count 3, logical reads 120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now, try to total up the logical reads in that output. If there's only a few lines of output, it's manageable, but imagine trying to decipher hundreds of lines of stuff like this. Over time, I developed a rigamarole system:

• copy the entire contents of the Output window to the clipboard
• fire up Excel, and paste the output into it
• from Excel's "Data" tab, click the "Sort A to Z" button
• select only the rows that start with "Table..." and copy them to the clipboard
• paste the rows into a new window in Management Studio
• use a regular expression search-and-replace to strip out all the text on each line before the logical read numbers
• use a regular expression search-and-replace to strip out all the text on each line after the logical read numbers
• copy the column of logical read numbers back into Excel
• from Excel's "Formulas" tab, use the AutoSum button to, finally, calculate the total number of logical reads

"Crude but effective" doesn't quite do this process justice. There must be a better way, I thought.

My first idea was an AddOn for Management Studio, but the more I looked into it, I realized I just couldn't face the tedium of application programming (yes, I'm now permanently spoiled by a platform - SQL Server - that actually works without swearing at it). And while SQL Server isn't exactly famous for its text-manipulation ability, what I was was doing didn't have to be fast or elegant. And I'd rather hack something together in T-SQL in a few hours than beat my head against the C# wall for a few days. (Don't believe me? Ask someone who's written a truly great Visual Studio add-on.) As a bonus, the people using the code would be able to understand it, and maybe even build upon and improve it.

So, after an embarrassingly long delay caused by forgetting, again, that REPLACE replaces all occurrences, not just the first one, here it is. It works correctly as far as I can tell, but don't use it as an example of how to write good T-SQL code. (Please tell me about any bugs you find. Use at your own risk. Some settling of contents may occur during shipping.)

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

DECLARE @Text NVARCHAR(MAX) =
"-- INSERT YOUR TEXT BETWEEN THESE TWO LINES -----------------------------------
Starting at 12:25:30 PM...
Table 'Customers'. Scan count 6, logical reads 10213968, physical reads 125391, read-ahead reads 254294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CustomerNum   InvoiceNum   Amt
----------- ------------ --------------------------------------
2344   1331111   333.23
4492   4837227 44222.84
3434   222444   91811.33
98474   54422  22.09
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Updating the total prices...
(1 row(s) affected)
Table 'Invoices'. Scan count 1, logical reads 332, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
12:30:38: Added row to the Invoices table for 2011/10/12
Warning: Aggregate calculation excluded NULL values.
Table 'Worktable'. Scan count 3, logical reads 120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- INSERT YOUR TEXT BETWEEN THESE TWO LINES -----------------------------------"

DECLARE @Lines TABLE (Txt NVARCHAR(MAX), Num INT)
DECLARE @nLF   INT

WHILE 1=1
BEGIN
    SET @nLF = CHARINDEX(NCHAR(10), @Text)
    IF @nLF = 0  BREAK
    
    IF @nLF < 3
    BEGIN
        SET @Text = STUFF(@Text, 1, 1, '')  
        CONTINUE
    END
    
    INSERT @Lines (Txt)
    VALUES (LEFT(@Text, @nLF - LEN(NCHAR(13) + NCHAR(10))))

    SET @nLF = PATINDEX('%' + NCHAR(13) + NCHAR(10) + '%', @Text)
    SET @Text = STUFF(@Text, 1, @nLF, '')
END

DELETE @Lines WHERE Txt NOT LIKE '%, logical reads %'
UPDATE @Lines SET Num = PATINDEX('%, logical reads %', Txt)
UPDATE @Lines SET Txt = STUFF(Txt, 1, Num + LEN(', logical reads '), '')
UPDATE @Lines SET Num = CHARINDEX(',', Txt)
UPDATE @Lines SET Txt = LEFT(Txt, Num - 1)
UPDATE @Lines SET Num = CAST(Txt AS INT)
DELETE @Lines WHERE Num = 0

SELECT Num      AS 'Logical Reads'       FROM @Lines
SELECT SUM(Num) AS 'Total Logical Reads' FROM @Lines

SET QUOTED_IDENTIFIER ON

To use this script, just copy-and-paste the entire contents of the Output window between the two lines in red in the @Text variable's definition, and hit F5 (Ctrl-E, whatever) to execute the script. You should get output like this:

The first set of results represent the SET STATISTICS IO rows that have non-zero logical reads, useful mostly for debugging the script. You can suppress this output by commenting out the first SELECT statement in the script (third line of code from the bottom). The second set of results is what we're looking for: the total number of logical reads that appear in the text of the Output window.

SSIS: Word-wrapping Annotations (Using Only Notepad)

Remember that Twilight Zone episode, where a man sells his soul to the Devil for immortality, only to be sentenced to life in prison the next day?

I'm learning SSIS (2005, for now), and have discovered, like all those before me, that Microsoft implemented Annotations, but... they don't word-wrap! Your Annotation must fit on one really long one line! Bwa-ha-ha-ha-ha-ha!

Of course, I'm no longer surprised by anything SSIS: I even have a bookmark for "SSIS" Google searches now. And that's how I found Paul Blackwell's excellent Hidden SSIS Features: Word Wrapping Your Annotations And More which covers the subject extremely well. I highly recommend the entire article, especially the section titled Transparency & Word Wrapping.

Another very interesting read is [SSIS] Modifier l'apparence des packages via l'API [FAIL] by François Jehl. (We non-Francophones can use Google's translate-to-English, or the page itself has a Microsoft translation button.) After some research, his conclusion is that Annotations are a feature from a product created before SSIS ("DaVinci Design Surface", hence "MSDDS"), which might explain why Annotations are hacked implemented in SSIS package files as XML nested inside the document's XML. (Hmmm... that would explain all the <s and >s floating around!) For what François was attempting, that was a fatal roadblock. Fortunately, we're not trying anything nearly so ambitious or useful as he was - certainly nothing Notepad can't handle!

Anyway, using what they've uncovered, here's how to make your Annotations word-wrap. (This is how I do it - use at your own risk, and make a backup of your package file first). Close Visual Studio, and open the package file in Notepad. Search for controlprogid="MSDDS.Text", and you'll be treated to nested XML that looks something like this:

<ddscontrol controlprogid="MSDDS.Text" left="4118" top="-655" logicalid="18" controlid="9" masterid="0" hint1="0" hint2="0" width="6153" height="994" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
    <control>
      <ddsxmlobjectstreaminitwrapper binary="0002000009180000e20300000300640000000500008008000000000000002a000054006800650001000000900144420100065461686f0074006f002000506d615800540068006500200063006f006e006e0065006300740069006f006e0020006d0061006e00610067006500054006800650007200200066006f0074006f0020005000720020007400680069007300200d61582005400610073006b0020006600610069006c0073002000730070006f007200610064000540068006500074006f00200050006900630061006c006c007900200075006e006c00650073007300200069007400270073002000730065007400200d6158005400680065002006100730073006900760065002000540068006500004d006f00640065002e0000000000" />
    </control>
    <layoutobject>
      <ddsxmlobj />
    </layoutobject>
    <shape groupshapeid="0" groupnode="0" />
  </ddscontrol>

See the red "2" at position 61 of the ddsxmlobjectstreaminitwrapper item? To enable word-wrap, simply OR in a value of 1: in this case, we change the "10" (decimal 2) to "11" (decimal 3). Once that's done, save the package file (you made a backup first, right?), exit Notepad, open the package with Visual Studio, and - viola! - your Annotation is word-wrapped! Now do the same thing for all your other Annotations. (Hey, it's a hack, not magic.)

So, if it's just a bit-flip, why didn't Microsoft simply put a checkbox on the Properties page for Annotations? Because... Annotations don't have Property pages! Bwa-ha-ha-ha-ha-ha!