sys.dm_db_stats_properties() - Initial Results on SQL 2008 R2 SP2

My first sys.dm_db_stats_properties() post showed results for SQL Server 2012 SP1. Here, I will show how the results for filtered indexes on 2008 R2 SP2 are different.

The test script is exactly the same as before, just running on a different version.

MSDN Docs

The 2008 R2 version of the docs is here. Because the results were different, I pulled the text for both the 2012 document and the 2008 R2 document and compared them. There are no substantive differences.

Here are the only differences:

2008 R2 SP2"Requires that the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."

2012 SP1 adds the highlighted section"Requires that the user has select permissions on statistics columns or the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."

Results

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
   Jun 28 2012 08:36:30 
   Copyright (c) Microsoft Corporation
   Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Beginning execution loop
Batch execution completed 10000 times.
name              filter_definition last_updated        rows  rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------

name              filter_definition last_updated        rows  rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid  NULL              2012-12-20 09:10:09 10000 10000        132   10000           0
IX_Test1_Deleted  NULL              2012-12-20 09:10:09 10000 10000        1     10000           0
IX_Test1_StatusId NULL              2012-12-20 09:10:09 10000 10000        1     10000           0
PK_Test1_ColId    NULL              2012-12-20 09:10:09 10000 10000        3     10000           0

Update Count: 5000
name              filter_definition last_updated        rows  rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid  NULL              2012-12-20 09:10:09 10000 10000        132   10000           0
IX_Test1_Deleted  NULL              2012-12-20 09:10:09 10000 10000        1     10000           5000
IX_Test1_StatusId NULL              2012-12-20 09:10:09 10000 10000        1     10000           5000
PK_Test1_ColId    NULL              2012-12-20 09:10:09 10000 10000        3     10000           0

Observations

Filtered Index Results
Filtered indexes are excluded from these results where they return NULLs in 2012.

The difference appears to be in the fact that the function returns a row in 2012 for a filtered index and does not return a row in 2008 R2 for a filtered index.

sys.dm_db_stats_properties() - Initial Results on SQL 2012 SP1

The SQL Server team just released a new feature, the DMF sys.dm_db_stats_properties(). This new feature is available in 2008 R2 SP2 and 2012 SP1.

These tests were run on SQL Server 2012 SP1 and will illustrate what the function returns in the normal case.

First, some references:

• sys.dm_db_stats_properties at MSDN
• What Happened to ROWMODCTR from SYSINDEXES? by Jason Strate
•  New Statistics DMF in SQL Server 2008R2 SP2 by Erin Stellato of SQLskills

The test code below will

1. create a new table with multiple indexes.
2. insert 10,000 rows, then UPDATE STATISTICS.
3. call sys.dm_db_stats_properties().
4. update the StatusId and Deleted columns of 50% of the rows.
5. call sys.dm_db_stats_properties().

Test Code

USE tempdb;GOPRINT @@VERSION;GOIF EXISTS (SELECT * FROM sys.tables WHERE OBJECT_ID('dbo.Test1') IS NOT NULL)
   DROP TABLE dbo.Test1;CREATE TABLE dbo.Test1 (
   ColId       INT                 IDENTITY,
   ColGuid     UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
   StatusId    tinyint             NOT NULL DEFAULT 1,
   Deleted     bit                 NOT NULL DEFAULT 0,
   CONSTRAINT PK_Test1_ColId PRIMARY KEY CLUSTERED (ColId),
   CONSTRAINT AK_Test1_ColGuid UNIQUE (ColGuid)
);GOCREATE NONCLUSTERED INDEX IX_Test1_ColId_WhereDeletedEq1 ON dbo.Test1 (
   ColId) WHERE Deleted = 1;GOCREATE NONCLUSTERED INDEX IX_Test1_StatusId_WhereDeletedEq1 ON dbo.Test1 (
   StatusId) WHERE Deleted = 1;GOCREATE NONCLUSTERED INDEX IX_Test1_Deleted ON dbo.Test1 (
   Deleted);GOCREATE NONCLUSTERED INDEX IX_Test1_StatusId ON dbo.Test1 (
   StatusId);GO-- ==================== Initial insertINSERT INTO dbo.Test1 DEFAULT VALUES;GO 10000-- ==================== Call sys.dm_db_stats_properties()SELECT
   s.name,
   s.filter_definition,
   CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated,
   p.[rows],
   p.rows_sampled,
   p.steps,
   p.unfiltered_rows,
   p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO-- ==================== UPDATE STATISTICSUPDATE STATISTICS dbo.Test1;GO-- ==================== Call sys.dm_db_stats_properties()SELECT
   s.name,
   s.filter_definition,
   CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated,
   p.[rows],
   p.rows_sampled,
   p.steps,
   p.unfiltered_rows,
   p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO-- ==================== Update 50% of records (StatusId, Deleted)UPDATE dbo.Test1SET
   StatusId = 2,
   Deleted  = 1WHERE ColId % 2 = 0;PRINT 'Update Count: ' + CONVERT(VARCHAR(11), @@ROWCOUNT);GO-- ==================== Call sys.dm_db_stats_properties()SELECT
   s.name,
   s.filter_definition,
   CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated,
   p.[rows],
   p.rows_sampled,
   p.steps,
   p.unfiltered_rows,
   p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO

Results

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
   Oct 19 2012 13:38:57 
   Copyright (c) Microsoft Corporation
   Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Beginning execution loop
Batch execution completed 10000 times.

name                              filter_definition last_updated        rows  rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------- ----  ------------ ----- --------------- --------------------
AK_Test1_ColGuid                  NULL              NULL                NULL  NULL         NULL  NULL            NULL
IX_Test1_Deleted                  NULL              NULL                NULL  NULL         NULL  NULL            NULL
IX_Test1_StatusId                 NULL              NULL                NULL  NULL         NULL  NULL            NULL
PK_Test1_ColId                    NULL              NULL                NULL  NULL         NULL  NULL            NULL
IX_Test1_ColId_WhereDeletedEq1    ([Deleted]=(1))   NULL                NULL  NULL         NULL  NULL            NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1))   NULL                NULL  NULL         NULL  NULL            NULL

name                              filter_definition last_updated        rows  rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid                  NULL              2012-12-19 14:59:41 10000 10000        136   10000           0
IX_Test1_Deleted                  NULL              2012-12-19 14:59:41 10000 10000        1     10000           0
IX_Test1_StatusId                 NULL              2012-12-19 14:59:41 10000 10000        1     10000           0
PK_Test1_ColId                    NULL              2012-12-19 14:59:41 10000 10000        3     10000           0
IX_Test1_ColId_WhereDeletedEq1    ([Deleted]=(1))   NULL                NULL  NULL         NULL  NULL            NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1))   NULL                NULL  NULL         NULL  NULL            NULL

Update Count: 5000

name                              filter_definition last_updated        rows  rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------------- ------------ ----- --------------- --------------------
AK_Test1_ColGuid                  NULL              2012-12-19 14:59:41 10000 10000        136   10000           0
IX_Test1_Deleted                  NULL              2012-12-19 14:59:41 10000 10000        1     10000           5000
IX_Test1_StatusId                 NULL              2012-12-19 14:59:41 10000 10000        1     10000           5000
PK_Test1_ColId                    NULL              2012-12-19 14:59:41 10000 10000        3     10000           0
IX_Test1_ColId_WhereDeletedEq1    ([Deleted]=(1))   NULL                NULL  NULL         NULL  NULL            NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1))   NULL                NULL  NULL         NULL  NULL            NULL

Observations

NULLs
Not surprisingly, the result columns are all NULL before calling UPDATE STATISTICS.

Steps
Steps is an interesting statistic. Steps indicates the "number of steps in the histogram." Note that "The maximum number of steps is 200." (ref) You won't get 200 by simply adding more rows. Inserting 100,000 rows instead of 10,000 gives a steps value of 56 for the GUID index.

Filtered Indexes
I was never able to get filtered rows to return any statistics even though the docs say for the rows column, "If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table."

Is this a bug in SQL Server or in my query?

modification_counter
As expected, modification_counter is 0 before there are any modifications and after modifying 5,000 rows, the counts are 5,000 (except for the filtered indexes).

SQL Server Data Compression and TDE

I had been looking for a definitive statement on how TDE and compression work together. I found it here:

Data Compression: Strategy, Capacity Planning and Best Practices

TDE encrypts the pages when they are written to disk and decrypts them when they are read from disk into memory. Because data compression (as well as decompression) is performed on in-memory pages, data compression always sees unencrypted data, and hence the effectiveness and efficiency of data compression is not impacted by TDE.

Most other resources I had found only address how TDE messes up backup compression.

SELECT INTO is faster than INSERT SELECT

I had heard that SELECT INTO is faster than INSERT SELECT, so I wanted to prove it.

Here are the results of testing in both FULL and SIMPLE recover models. Each test was run twice, once with INSERT-SELECT first and once with SELECT-INTO first.

The net result is that, at least for this test code on my hardware, using SELECT INTO is an order of magnitude faster in SIMPLE recover mode than INSERT SELECT. Using SELECT INTO is faster in FULL recover mode than INSERT SELECT, but the difference is less significant.

Test Results

Recovery Model: Simple
Order: INSERT-SELECT first
Elapsed Mcs - INSERT-SELECT: 8,391,839
Elapsed Mcs - SELECT-INTO: 962,096

Order: SELECT-INTO first
Elapsed Mcs - INSERT-SELECT:7,205,720
Elapsed Mcs - SELECT-INTO: 918,092

Recovery Model: Full
Order: INSERT-SELECT first
Elapsed Mcs - INSERT-SELECT:7,095,709
Elapsed Mcs - SELECT-INTO:1,289,129

Order: SELECT-INTO first
Elapsed Mcs - INSERT-SELECT: 6,274,628
Elapsed Mcs - SELECT-INTO:1,902,190

Test Script

USE MASTER;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SelectInto') ALTER DATABASE SelectInto SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SelectInto') DROP DATABASE  SelectInto;
GO
CREATE DATABASE SelectInto;
GO
ALTER DATABASE SelectInto SET RECOVERY FULL;
--ALTER DATABASE SelectInto SET RECOVERY SIMPLE;
GO
USE SelectInto;
GO

-- Source table: t1
CREATE TABLE dbo.t1 (
   ColID       INT         NOT NULL,
   Col1        CHAR(30)    NOT NULL DEFAULT 'asdf',
   CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (ColID)
);

-- Populate t1
DECLARE @MaxRows    INT     = 1000000;
DECLARE @i          INT     = 0;
WHILE @i < @MaxRows
BEGIN
   SET @i += 1;
   INSERT INTO dbo.t1 (ColID) VALUES (@i);
END;

-- Destination heap: t2
CREATE TABLE dbo.t2 (
   ColID       INT         NOT NULL,
   Col1        CHAR(30)    NOT NULL DEFAULT 'asdf'
);

DECLARE @t1     time(7);
DECLARE @t2     time(7);

-- INSERT/SELECT
SET @t1 = SYSUTCDATETIME();

INSERT INTO dbo.t2 (ColID, Col1)
SELECT ColID, Col1 FROM dbo.t1;

SET @t2 = SYSUTCDATETIME();
SELECT DATEDIFF(mcs, @t1, @t2) AS ElapsedMcs;

-- SELECT INTO
SET @t1 = SYSUTCDATETIME();

SELECT ColID, Col1
INTO dbo.t3
FROM dbo.t1;

SET @t2 = SYSUTCDATETIME();
SELECT DATEDIFF(mcs, @t1, @t2) AS ElapsedMcs;


References

• INTO Clause at MSDN

SQL Server CDC: Remember It's Not Free

Someone recently recommended that a developer use SQL Server's CDC (Change Data Capture) to create a history record for a transactional table because doing that work in a stored procedure "makes this stored procedure slow". Remember that there is no magic in CDC; it's not free.

Based on the research I did for this article:

Performance Testing SQL Server 2008's Change Data Capture functionality

"The average penalty for CDC with full recovery is 10.51%."

SQLskills' "IE1: Immersion Event on Internals and Performance"

I just attended the most intense and valuable week of training I have ever experienced: SQLskills' "IE1: Immersion Event on Internals and Performance". This is a full week of training given by Kimberly Tripp and Paul S. Randal.

The whole week was great. These are two of the best trainers in the business, period. In this class, they teach everything, so you're getting great content every minute of every day.

I highly recommend the class.

Declare DataType When Creating Computed Column in SQL Server

When creating a computed column in SQL Server, be sure to use CONVERT (or CAST) to specify the datatype of the computed column, otherwise SQL Server will make the choice for you. It may not make the choice that you expected.

Here is a very simple example that shows a computed column with two possible values: 0 and 1. With no covert, SQL Server defaults to int. By using convert, we can use bit. This simple example shows that using CONVERT drops the storage requirement from 4 bytes to a single bit.

Code

SET NOCOUNT ON;USE tempdb;GOIF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.TestTable'))
   DROP TABLE dbo.TestTable;GOCREATE TABLE dbo.TestTable (
   TestId      INT         IDENTITY,
   TestName    VARCHAR(25) NOT NULL,
   LamboNoConvert AS (
       CASE
       WHEN TestName LIKE 'Lamborghini%'
       THEN 1
       ELSE 0
       END
   ),
   LamboConvert AS (
       CASE
       WHEN TestName LIKE 'Lamborghini%'
       THEN CONVERT(bit, 1)
       ELSE CONVERT(bit, 0)
       END
   )
);GO
INSERT INTO dbo.TestTable (TestName)VALUES
   ('Lamborghini Aventador'),
   ('McLaren F1');
SELECT * FROM dbo.TestTable;
SELECT
   name                      AS ColumnName,
   TYPE_NAME(system_type_id) AS TypeNameFROM sys.columnsWHERE [object_id] = OBJECT_ID('dbo.TestTable');


Results

TestId      TestName                  LamboNoConvert LamboConvert
----------- ------------------------- -------------- ------------
1           Lamborghini Aventador     1              1
2           McLaren F1                0              0

ColumnName      TypeName
--------------- --------
TestId          INT
TestName        VARCHAR
LamboNoConvert  INT
LamboConvert    BIT

Computed Column References:

• Exploring Indexed Persisted Computed Columns
• More on Indexed Persisted Computed Columns

List All Columns with MAX Length

To get a list of all columns in a database with "MAX" length, use this query:

SELECT
   SCHEMA_NAME(t.[schema_id]) + '.' + t.name + '.' + c.name    AS [Sch.Tbl.Col],
   TYPE_NAME(c.user_type_id)                                   AS [Type]FROM sys.columns    AS cJOIN sys.tables     AS t ON c.[object_id] = t.[object_id]WHERE c.max_length = -1
  AND t.name NOT IN ('sysdiagrams')ORDER BY SCHEMA_NAME(t.[schema_id]) + '.' + t.name + '.' + c.name


SQL Server Date/Time Byte Counts

Here is a table showing how many bytes each date/time datatype requires. The script to generate the table is after the table.

Type	ByteCt	Example Precision
date	3	2012-06-08
smalldatetime	4	2012-06-08 16:27:00.000
datetime	8	2012-06-08 16:27:12.187
time(0)	3	16:27:12
time(1)	3	16:27:12.2
time(2)	3	16:27:12.19
time(3)	4	16:27:12.188
time(4)	4	16:27:12.1875
time(5)	5	16:27:12.18751
time(6)	5	16:27:12.187506
time(7)	5	16:27:12.1875059
datetime2(0)	6	2012-06-08 16:27:12
datetime2(1)	6	2012-06-08 16:27:12.2
datetime2(2)	6	2012-06-08 16:27:12.19
datetime2(3)	7	2012-06-08 16:27:12.188
datetime2(4)	7	2012-06-08 16:27:12.1875
datetime2(5)	8	2012-06-08 16:27:12.18751
datetime2(6)	8	2012-06-08 16:27:12.187506
datetime2(7)	8	2012-06-08 16:27:12.1875059
datetimeoffset(0)	8	2012-06-08 16:27:12 -07:00
datetimeoffset(1)	8	2012-06-08 16:27:12.2 -07:00
datetimeoffset(2)	8	2012-06-08 16:27:12.19 -07:00
datetimeoffset(3)	9	2012-06-08 16:27:12.188 -07:00
datetimeoffset(4)	9	2012-06-08 16:27:12.1875 -07:00
datetimeoffset(5)	10	2012-06-08 16:27:12.18751 -07:00
datetimeoffset(6)	10	2012-06-08 16:27:12.187506 -07:00
datetimeoffset(7)	10	2012-06-08 16:27:12.1875059 -07:00

Query

SET NOCOUNT ON;DECLARE @now    datetime2(7)        = SYSDATETIME();DECLARE @nowU   datetime2(7)        = SYSUTCDATETIME();DECLARE @nowO   datetimeoffset(7)   = SYSDATETIMEOFFSET();DECLARE @d      date                = @now;DECLARE @s      smalldatetime       = @now;DECLARE @dt     DATETIME            = @now;DECLARE @t0     time(0)             = @now;DECLARE @t1     time(1)             = @now;DECLARE @t2     time(2)             = @now;DECLARE @t3     time(3)             = @now;DECLARE @t4     time(4)             = @now;DECLARE @t5     time(5)             = @now;DECLARE @t6     time(6)             = @now;DECLARE @t7     time(7)             = @now;DECLARE @dt0    datetime2(0)        = @now;DECLARE @dt1    datetime2(1)        = @now;DECLARE @dt2    datetime2(2)        = @now;DECLARE @dt3    datetime2(3)        = @now;DECLARE @dt4    datetime2(4)        = @now;DECLARE @dt5    datetime2(5)        = @now;DECLARE @dt6    datetime2(6)        = @now;DECLARE @dt7    datetime2(7)        = @now;DECLARE @dto0   datetimeoffset(0)   = @nowO;DECLARE @dto1   datetimeoffset(1)   = @nowO;DECLARE @dto2   datetimeoffset(2)   = @nowO;DECLARE @dto3   datetimeoffset(3)   = @nowO;DECLARE @dto4   datetimeoffset(4)   = @nowO;DECLARE @dto5   datetimeoffset(5)   = @nowO;DECLARE @dto6   datetimeoffset(6)   = @nowO;DECLARE @dto7   datetimeoffset(7)   = @nowO;
SELECT 'date'           AS 'Type',   DATALENGTH(@d)  AS 'ByteCt', CONVERT(VARCHAR(100), @d, 121) AS 'Example Precision'UNION ALLSELECT 'smalldatetime',             DATALENGTH(@s),                 CONVERT(VARCHAR(100), @s, 121)UNION ALLSELECT 'datetime',                  DATALENGTH(@dt),                CONVERT(VARCHAR(100), @dt, 121)UNION ALLSELECT 'time(0)',                   DATALENGTH(@t0),                CONVERT(VARCHAR(100), @t0, 121)UNION ALLSELECT 'time(1)',                   DATALENGTH(@t1),                CONVERT(VARCHAR(100), @t1, 121)UNION ALLSELECT 'time(2)',                   DATALENGTH(@t2),                CONVERT(VARCHAR(100), @t2, 121)UNION ALLSELECT 'time(3)',                   DATALENGTH(@t3),                CONVERT(VARCHAR(100), @t3, 121)UNION ALLSELECT 'time(4)',                   DATALENGTH(@t4),                CONVERT(VARCHAR(100), @t4, 121)UNION ALLSELECT 'time(5)',                   DATALENGTH(@t5),                CONVERT(VARCHAR(100), @t5, 121)UNION ALLSELECT 'time(6)',                   DATALENGTH(@t6),                CONVERT(VARCHAR(100), @t6, 121)UNION ALLSELECT 'time(7)',                   DATALENGTH(@t7),                CONVERT(VARCHAR(100), @t7, 121)UNION ALLSELECT 'datetime2(0)',              DATALENGTH(@dt0),               CONVERT(VARCHAR(100), @dt0, 121)UNION ALLSELECT 'datetime2(1)',              DATALENGTH(@dt1),               CONVERT(VARCHAR(100), @dt1, 121)UNION ALLSELECT 'datetime2(2)',              DATALENGTH(@dt2),               CONVERT(VARCHAR(100), @dt2, 121)UNION ALLSELECT 'datetime2(3)',              DATALENGTH(@dt3),               CONVERT(VARCHAR(100), @dt3, 121)UNION ALLSELECT 'datetime2(4)',              DATALENGTH(@dt4),               CONVERT(VARCHAR(100), @dt4, 121)UNION ALLSELECT 'datetime2(5)',              DATALENGTH(@dt5),               CONVERT(VARCHAR(100), @dt5, 121)UNION ALLSELECT 'datetime2(6)',              DATALENGTH(@dt6),               CONVERT(VARCHAR(100), @dt6, 121)UNION ALLSELECT 'datetime2(7)',              DATALENGTH(@dt7),               CONVERT(VARCHAR(100), @dt7, 121)UNION ALLSELECT 'datetimeoffset(0)',         DATALENGTH(@dto0),              CONVERT(VARCHAR(100), @dto0, 121)UNION ALLSELECT 'datetimeoffset(1)',         DATALENGTH(@dto1),              CONVERT(VARCHAR(100), @dto1, 121)UNION ALLSELECT 'datetimeoffset(2)',         DATALENGTH(@dto2),              CONVERT(VARCHAR(100), @dto2, 121)UNION ALLSELECT 'datetimeoffset(3)',         DATALENGTH(@dto3),              CONVERT(VARCHAR(100), @dto3, 121)UNION ALLSELECT 'datetimeoffset(4)',         DATALENGTH(@dto4),              CONVERT(VARCHAR(100), @dto4, 121)UNION ALLSELECT 'datetimeoffset(5)',         DATALENGTH(@dto5),              CONVERT(VARCHAR(100), @dto5, 121)UNION ALLSELECT 'datetimeoffset(6)',         DATALENGTH(@dto6),              CONVERT(VARCHAR(100), @dto6, 121)UNION ALLSELECT 'datetimeoffset(7)',         DATALENGTH(@dto7),              CONVERT(VARCHAR(100), @dto7, 121);

SQL Fiddle

SQL Fiddle is a new on-line tool that allows you to illustrate SQL DDL/DML. It currently defaults to SQL Server 2008 R2 but also supports 2012 and five other databases.

Check out a simple example here. This example shows the table and queries I built to answer this Stack Overflow question. When you build something in the tool, it automatically generates a unique URL for that schema/query pair.

Bravo to Jake Feasel for creating such a great tool.

Rob

UPDATE a Column While Simultaneously Setting a Local Variable

I saw an interesting pattern in a Microsoft-supplied stored procedure today. They update a column and write a local variable at the same time.

Here is an illustration of the technique.

Code:
USE tempdb; 
SET NOCOUNT ON;
GO

CREATE TABLE test1 (
   ColId       INT             IDENTITY,
   ColValue1   VARCHAR(20),
   ColValue2   VARCHAR(20)
);

INSERT INTO test1 (ColValue1)
VALUES ('Col1-Initial'); 

INSERT INTO test1 (ColValue2)
VALUES ('Col2-Initial');

DECLARE @value VARCHAR(20);

SELECT ColValue1, ColValue2 FROM test1;

UPDATE test1 

SET @value = ColValue2 = ColValue1 + '-Updated'

WHERE ColId = 1;

SELECT @value AS '@value';
SELECT ColValue1, ColValue2 FROM test1;

Results:

ColValue1            ColValue2
-------------------- --------------------
Col1-Initial         NULL
NULL                 Col2-Initial

@value
--------------------
Col1-Initial-Updated

ColValue1            ColValue2
-------------------- --------------------
Col1-Initial         Col1-Initial-Updated
NULL                 Col2-Initial

Comparing Nullable Strings

To test if nullable @String1 is different from nullable @String2, I had this fairly complex code:

IF   (@String1 <> @String2
  OR (@String1 IS     NULL AND @String2 IS NOT NULL)
  OR (@String1 IS NOT NULL AND @String2 IS     NULL)) ...
 
Someone saw this and decided it would be simpler to do this instead:

IF (COALESCE(@String1, '') <> COALESCE(@String2, '')) ...

The problem is, that doesn't work in all cases. Here is the case where it doesn't work, followed by the cases where it does work.

First, declare and set the two string variables:

DECLARE @String1    VARCHAR(20);
DECLARE @String2    VARCHAR(20);

SET @String1 = NULL; 
SET @String2 = '';
 
Then the test:

IF (COALESCE(@String1, '') <> COALESCE(@String2, ''))
   PRINT 'Strings Differ';

IF   (@String1 <> @String2
  OR (@String1 IS     NULL AND @String2 IS NOT NULL)
  OR (@String1 IS NOT NULL AND @String2 IS     NULL))
   PRINT 'Strings Differ';

The first test does not recognize the difference, but the second test does.

All of these values pass the test:

-- 1: Both NOT NULL, different

SET @String1 = 'OldDesc';
SET @String2 = 'Desc'; 

-- 2: Both NOT NULL, same

SET @String1 = 'Desc';
SET @String2 = 'Desc';

-- 3: Both NULL

SET @String1 = NULL;
SET @String2 = NULL;

-- 4: One NULL, one NOT NULLSET @String1 = 'OldDesc'; 
SET @String2 = NULL;