Create a Database Snapshot

Sometimes it’s handy to be able to quickly revert to a previous version of a database. During testing, it’s handy to set up your test data, snapshot the database, and test over and over again without having to restage the data each time. Backups can be time-consuming in a large database. Snapshots to the rescue!

Prerequisites:

  1. Obviously, you’ll need to be using a version of SQL Server that supports snapshots.
  2. You’ll need the appropriate permissions on the database server. The fixed server “sysadmin” role will suffice, naturally.
  3. You’ll need the appropriate permissions on the directory(ies) where your database files are located. If not, you can always change the script to use a different directory.

Set your SSMS to output results to text (Ctrl + T) and execute the following:

SET NOCOUNT ON

DECLARE @currdate VARCHAR(20)
SET @currdate = CONVERT(VARCHAR(20), GETDATE(), 120)
SET @currdate = REPLACE(@currdate, ':', '')
SET @currdate = REPLACE(@currdate, '-', '')
SET @currdate = REPLACE(@currdate, ' ', '_')
SET @currdate = SUBSTRING(@currdate, 0, 14)

DECLARE @SnapshotName varchar(100)
DECLARE @CreateSQL VARCHAR(MAX) = ''
DECLARE @RestoreSQL VARCHAR(MAX) = ''
DECLARE @names table ([Name] varchar(100), [Physical_Name] varchar(200))
DECLARE @DatabaseName varchar(100) = 'DATABASE NAME GOES HERE'

------------------------

SET @SnapshotName = @DatabaseName + '-' + @currdate + '-1.ss'
DELETE @names

INSERT @names ([Name], [Physical_Name])
SELECT name, physical_name
FROM sys.master_files
WHERE type = 0
AND (database_id = DB_ID(@DatabaseName))

IF (SELECT COUNT(*) FROM @names) > 0
BEGIN
    SELECT @CreateSQL = @CreateSQL + 'CREATE DATABASE ' + QUOTENAME(@SnapshotName) + ' ON ' 

    SELECT @CreateSQL = @CreateSQL + CHAR(13) + CHAR(10) + 
            '(NAME = ' + QUOTENAME([Name]) 
            + ', FILENAME = ''' + [Physical_Name] + '-' + @currdate + '.snap''),' 
    FROM @names

    SELECT @CreateSQL = LEFT(@CreateSQL,len(@CreateSQL)-1) + CHAR(13) + CHAR(10)  + 'AS SNAPSHOT OF [' + @DatabaseName + ']
'
    SELECT @RestoreSQL = @RestoreSQL + 'RESTORE DATABASE [' + @DatabaseName + '] FROM DATABASE_SNAPSHOT = ''' + @SnapshotName + ''';
'
END  

PRINT '/*'

PRINT @CreateSQL 
PRINT '*/

/*'
PRINT @RestoreSQL 

PRINT '*/'

You’ll get an output that looks like the following (with names and locations specific to your server):

/*
CREATE DATABASE [{databasename}-1.ss] ON 
(NAME = [{filename01}], FILENAME = '{filepath01}-20170419_0802.snap'),
(NAME = [{filename02}], FILENAME = '{filepath02}-20170419_0802.snap'),
(NAME = [{filename03}], FILENAME = '{filepath03}-20170419_0802.snap')
AS SNAPSHOT OF [{databasename}]
*/

/*
RESTORE DATABASE [{databasename}] FROM DATABASE_SNAPSHOT = '{databasename}-20170419_0802-1.ss';
*/

Nothing will actually execute yet. I leave that up to you.

Be careful. Don’t play with snapshots unless you understand the possible ramifications, namely, you could inadvertently trash your databases. There’s a good article with lots more details on MSDN here: Create a Database Snapshot. Enjoy!

Unpivot

None of the “simple” examples for an SQL UNPIVOT were in any way simple, so here’s as simple as I can make it.

I’m using the following table definition for this discussion:

CREATE TABLE [dbo].[MyTable](
    [MyHeaderColumn] [varchar](100) NULL,
    [MyColumn1] [int] NULL,
    [MyColumn2] [int] NULL,
    [MyColumn3] [int] NULL
) 

It contains the following data:

output1

But I don’t want to output the data in that format. I want to output all three integer columns in a single column, thusly:

output2

I could use UNION statements, but that’s inefficient and decidedly uncool. UNPIVOT to the rescue!

I’m going to build it up one step at a time, rather than throw it all at you at once. I’m going to be very explicit and verbose. You can slim it down at your leisure.

First you need the source of the data. Simple enough.

    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T

Then you need to encapsulate it so it looks like a single object. In this context, the AS [{name}] is required, since all objects need a name.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]

Then you add the UNPIVOT shell, again with the required alias:

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(

) AS TR

Then you add the column that will contain the consolidated output values. Note: you’re defining the column here; it doesn’t have to match anything in the source, but it can if you like.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn]
) AS TR

Then we define another output column that’s going to tell us which source column the output value came from. Again, you’re defining the column; it can be named whatever you like.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (

                                            )
) AS TR

Then you enumerate the source columns that will be combined in the single output column:

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

At this point, we’ve only defined a data source, so now we need actually define the projection. Note: any columns that you included in the UNPIVOT cannot be included in the output. Think of the UNPIVOT like an aggregate: you can’t output an unaggregated or ungrouped column.

SELECT
TR.[MyHeaderColumn], 
TR.[OutputColumn], 
TR.[OutputColumnIdentifier] 
FROM 
(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

And here’s the result:

output3

Notice the contents of the [OutputColumnIdentifier] column. That’s the column the value was retrieved from! Neat.

If you’re not manipulating the original source, you can simplify by just specifying the table directly. This produces the identical output:

SELECT
TR.[MyHeaderColumn], 
TR.[OutputColumn], 
TR.[OutputColumnIdentifier] 
FROM 
[dbo].[MyTable] AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

And there you have it! Combining multiple columns into a single column using SQL UNPIVOT.

More information is available in TechNet: Using PIVOT and UNPIVOT, FROM (Transact-SQL).

Comparing Dates

Prior to SQL Server 2008, there’s no such thing as a date. There is only datetime. Because of the time, you can’t simply use a WHERE clause like this:

WHERE (@MyFirstDate = getdate())

They’ll never match! Unless it’s actually exactly midnight.

It gets even more unpredictable if you’re making relative comparisions:

WHERE (@MyFirstDate > '3/2/2015')

If @MyFirstDate is ‘3/2/2015 3:27 PM’, that WHERE clause will actually return True. If the date parts are the same, you’re actually asking if 3:27 PM > 12:00 AM.

To reduce the uncertainty, you should always use DATEDIFF. If you’re comparing just dates, you can specify to compare based on days where the difference in days is zero.

WHERE (DATEDIFF(d, @MyFirstDate, getdate()) = 0)

Luckily, in 2008 they introduced the date and time data types. Now you can store just the part you need and avoid all this silliness.

Here’s some light reading for other date and time functions: Date and Time Data Types and Functions

Filtered Unique Constraints

If you primarily employ logical deletes and very rarely actually remove a row from a table, a traditional unique constraint often results in a bad user experience. Consider the following scenario:

Let’s say I have a table called [Color] with a unique constraint on the [Name] column.
User1 adds the color “Red”.
User2 decides “Red” is a dumb color and deletes it (which merely sets the [IsActive] flag to False).
User1 then sees “Red” is missing and attempts to add it in again.
The system tells him he can’t add “Red” because it already exists.
User1 says, “!&#^@%”, because “Red” clearly doesn’t exist.

The solution is a filtered unique index.

CREATE UNIQUE NONCLUSTERED INDEX [UIX_Color_Name] ON [Client].[Color]
(
       [Name] ASC
)
WHERE ([IsActive] = 1)

The key here is the WHERE clause. Now when User1 attempts to add “Red”, the system will only consider the active records when checking for uniqueness. The result is that I can have multiple inactive records for a given value, but only one active record.

To handle this in the calling code, enclose your insert statement in a Try…Catch (SqlException ex). When the unique filtered index is violated, SqlException.Number will be 2601. Check for that number in your Catch and craft a friendly message accordingly.

SCOPE_IDENTITY vs @@IDENTITY

SCOPE_IDENTITY and @@IDENTITY both return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. A scope is a module: a stored procedure, trigger, function, or batch.

If you have triggers in play, @@IDENTITY may not return the value you expected.

Use SCOPE_IDENTITY to ensure you’re returning the key of the record you just created.

Sticky Transaction Isolation Level in Pooled Connections

omg!  Why are we getting so many deadlocks on the web site?!

When you change the isolation level on a pooled connection, it stays changed.  If you want to run a few things in a higher isolation level and you set the level to Serializable on a TransactionScope, finish your work, and complete the TransactionScope, the connection is returned to the pool with the current isolation level.  It does not revert to its default isolation level.

Our friends at Microsoft say this is by design: Article ID: 972915.

If you set it up, set it back down before releasing the connection.