Category Archives: T-SQL Problem-Solving

Deployment and Usage of a CLR Stored Procedure

Posted by on March 3, 2013 No comments

Once we have setup our environment properly, written the code we are wanting to deploy, and decided how we are going to handle code security for SQL CLR, the remaining steps are very straightforward.  Unless we are referencing other libraries that are not part of .NET, but that will be discussed in detail later.

Problem with the master database

Before we deploy, there is one error that you may run into on Visual Studio 2010, if you attempt to deploy your code directly to the “master” database.  If when you deploy to master, you receive the following error in the “Build” Output:

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 15657, Level 16, State 1, Procedure sp_db_vardecimal_storage_format, Line 65 Vardecimal storage format is not available in system database 'master'.

This is due to the deploy script attempting to enable the vardecimal storage format on master, but you cannot perform this action in master. So, to work around this problem, you will either have to deploy to a different database, or you can implement a new “Database Project” that references your SQL Server CLR Object.  This process is described in detail on MSDN, so I will not repeat it.

Deploy Your Code

If you have .NET setup properly, all you have to do to deploy your code is right-click on the project in Solution Explorer and click “Deploy”.  If you haven’t already built your code, it will build (compile) it before deploying the code to SQL Server:

Deploy Project

 

 

 

 

 

 

 

 

 

 

 

 

You should be able to see the results of your deployment in the “Output” pane within Visual Studio. Toward the end of this output, hopefully you will see something like:

Build succeeded.

Time Elapsed 00:00:06.84
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Testing Your Code

At this point, your SQL CLR stored procedure should be deployed and ready to use on your SQL Server database.  Connect to SQL Server through SSMS and connect to the database to which you deployed your code.

I have created a very simple Test that you can use to test your code:

DECLARE @isMatched	BIT,
		@pattern	VARCHAR(256) = '([A-Za-z0-9\-]+)',
		@testString	VARCHAR(256) = 'some text string'
 
EXEC @isMatched = RegExTestMatch @pattern, @testString
 
SELECT @isMatched
 
SET @pattern = '([0-9\-]+)'
 
EXEC @isMatched = RegExTestMatch @pattern, @testString
 
SELECT @isMatched

Assuming that you get no errors, the first Execution should return a “1” because the test String matches the Regular Expression. The second Execution should return a “0” because the test String does not match the Regular Expression defined.

I will be adding some more complex objects soon, but in the meantime, I hope that this short tutorial gives you an insight into how SQL CLR can improve your environment.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

  1. The Origination and Uses of SQL CLR
  2. Infrastructure Requirements and Configuration
  3. Writing a Simple CLR Stored Procedure
  4. Security and Signing
  5. Deployment and Usage
  6. Table-Valued Functions and Other SQL CLR (Coming Soon)
  7. Incorporating Existing Libraries (Coming Soon)

Security and Code Signing in SQL CLR

Posted by on March 3, 2013 1 comment

Before we publish our newly created SQL CLR Stored Procedure, we need to take a moment to discuss security.  SQL Server is a highly-protected environment.  As Database Administrators, we strive to work based off of least-privilege principles to keep the environment’s data secure and trustworthy.  Introducing a completely new set of code, running inside of our environment, should make us all take particular caution.  Unfortunately, there is quite a lot of debate across the internet about this, in which one side is saying, “Don’t worry about nitpicking over signed vs. unsigned code.  Protecting the front door is your real concern.”  While, yes, the front door (logins, roles, so forth) is often left vulnerable, shouldn’t we protect the  back door, especially when it is a NEW back door that we are opening willfully?

So, what is the risk?

The risk is that while signed code is guaranteed to match the code that was originally compiled, unsigned code could be manipulated to inject malicious commands that can be executed with very high privileges, without the DBA being aware at all.  This is another subject that could be spoken about for pages and pages worth of text, but suffice it to say, that I believe it is worth the 60 seconds (literally) that it takes to sign your code.

If it is so simple, why is there a debate?

Because, there are complications that can arise.  For one, if you are going to include another library with your code that was not signed, then you cannot sign your code.  Signed code requires that all dependencies are also signed.  This may be insurmountable.  Also, there may be a precedence set within your environment that is hard to overcome.  If downstream developers are not on board, and you need to use their libraries in your code, then you will either have to recompile their code as signed code, or convince them to move toward a signed assembly mindset.

So, how do I sign my code?

Signing code involves asymmetrically encrypting a hash (like a CRC) of the compiled code within the resultant assembly.  This enables the calling process to verify that the code has remained unchanged since the developer compiled it.  Since we are dealing with encryption, you will either need an existing signing certificate, issued by a CA (like Verisign) or do what is known as “Self-Signing”.  I am going to walk through Self-Signing our code, since it is free.

From within Visual Studio, in the Solution Explorer pane, right-click on the project and select “Properties”.  Then select the “Signing” tab to the left.  You will see a screen similar to this:

Signing Code

I click “Sign the assembly” and then I need to choose the “Strong Name Key File” I will use to sign the code. If I did not have an existing certificate, I could select “New..” from the drop-down:

New Strong Name Key File

 

 

 

 

All I have to do in the “Create Strong Name Key” dialog box is define a name for my new key file and setup a good strong password.  Once I click “OK” in this dialog box and then save the project, my code will automatically be signed at compile time (as long as I do not have any unsigned references in my code).

Create Strong Name Key

 

 

 

 

 

 

What if I cannot sign my code?

If you cannot sign your code, you are going to have to tell SQL Server that you want it to trust “unsafe” assemblies.  You can do this through the ALTER ASSEMBLY command.

Now that we have made our decision about signing our code and taken the necessary steps, we are ready to deploy and test our new SQL CLR Stored Procedure.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

  1. The Origination and Uses of SQL CLR
  2. Infrastructure Requirements and Configuration
  3. Writing a Simple CLR Stored Procedure
  4. Security and Signing
  5. Deployment and Usage
  6. Table-Valued Functions and Other SQL CLR (Coming Soon)
  7. Incorporating Existing Libraries (Coming Soon)

Writing a Simple CLR Stored Procedure

Posted by on March 3, 2013 3 comments

Now that we have a basic understanding of SQL CLR and have an environment configured to support the code we want to write, we can move on to writing our first SQL CLR stored procedure.  I am going to start out with a very simple piece of code, but one that solves a common problem in SQL Server: Matching Regular Expressions.

As I noted in a previous blog post, SQL Server does not have good native support for Regular Expressions, but when you are trying to sanitize data, Regular Expressions can save you a ton of processing time, development time, and effort.  So, let’s fill this need right now, by using SQL CLR.

Open the “Visual C# SQL CLR Database” Project that we created earlier and add a “Stored Procedure” class to the project, if you did not already do so.  I am going to call the file that will contain my class, “RegExTestMatch.cs”.  When you open this class for editing, you will see code that looks similar to the following:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RegExTestMatch()
    {
        // Put your code here
    }
};

Now, without delving too deeply into programming, I need to bring up just a few points of information, for those not familiar with high-level programming languages (HLPLs).

Libraries

To make programming languages easier to use and to encourage proper code reuse, most HLPLs incorporate some form of libraries.  These are collections of classes, methods, and properties that can be used in other projects and classes.  Programmers can also create their own libraries and include them in future projects or make them available to other developers, or even the general public.  In C#, if a library is to be used within a particular set of code, that usage must first be communicated to the compiler, by way of the “using” statements at the top of the code listing.  You will notice, the code that was auto-generated when we added our class to our project already had five “using” statements, meaning that our code is already able to utilize the objects defined inside those five libraries.

Return Types

A method is similar to a procedure within SQL Server.  A method is generally a part of a class (not always) and defines some action that can be taken from within the specific class.  In the code that was generated for us, our method is named “RegExTestMatch”.  The word that directly preceeds that name, is called the return type.  This defines the data type that any calling process should expect to return from this method. By default, this method’s return type is “void” meaning that nothing will return from this method.  We can change this to suit our needs.

Object-Oriented Programming

For the sake of brevity, I will state that Object-Oriented Programming (OOP) is a style of programming that focuses on objects, which are usually classes (but certainly not always).  A class is defined by a programmer and then can be reused many times either within the same project or in other projects that reference the library containing this object.  For the purpose of this demonstration, the key point to understand is that we can create discrete instances of a specific class and that instance is what we use to perform whatever tasks we need.  This entire subject would require a lot more discussion to flesh out completely.

To Solve Our Problem

Now that we have a basic understanding of a few points within our managed language, we can address the problem at hand.  We are trying to create a CLR stored procedure that will accept a Regular Expression Pattern and a String to test for matching against said pattern.  We will then want to respond to the calling process with either a true or a false.

We will need to first be able to use Regular Expressions within our code, so we need to include a reference to the Regular Expressions library found within .NET, so let’s add that “using” statement:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RegExTestMatch()
    {
        // Put your code here
    }
};

Next, we need to decide what we are going to pass to the method and what we will return to the calling process.  There are many different ways that we could handle this, but to keep things simple, I am going to pass in two strings, a pattern and a test string, and then return an int, which I will either set to “1” to indicate that the test string did indeed match the pattern, or a “0” to indicate that it did not match:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int RegExTestMatch(SqlString regExPattern, SqlString testString)
    {
        // Put your code here
    }
};

After we have our method setup, we need to instantiate an instance of the Regex class (which is the class within the “System.Text.RegularExpressions” library we will be using). During our instantiation, we can go ahead and pass in the pattern we want to match, but we will have to first cast the pattern to the correct datatype, “string”. That code looks like this:

Regex patternTester = new Regex((string)regExPattern);

The last part is to use our newly instantiated variable, which we named “patternTester”, to perform the test against our Test String. Once again, we must cast the variable to the correct datatype. Putting all of this together results in our final code, which looks like:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int RegExTestMatch(SqlString regExPattern, SqlString testString)
    {
        Regex patternTester = new Regex((string)regExPattern);
        if (patternTester.IsMatch((string)testString))
            return 1;
        else
            return 0;
    }
};

Now, we will need to have a brief look at security before we move on to deploying and using this code.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

  1. The Origination and Uses of SQL CLR
  2. Infrastructure Requirements and Configuration
  3. Writing a Simple CLR Stored Procedure
  4. Security and Signing
  5. Deployment and Usage
  6. Table-Valued Functions and Other SQL CLR (Coming Soon)
  7. Incorporating Existing Libraries (Coming Soon)

Infrastructure Requirements and Configuration for SQL CLR

Posted by on March 3, 2013 1 comment

To create and execute SQL CLR procedures (as well as other objects), there are several prerequisites that must first be met.  We are going to need:

  • Visual Studio (I’ll be using Visual Studio 2010 Ultimate)
  • A SQL Server Instance (I’ll be using SQL Server 2008 R2)
  • The .NET framework (For SQL Server 2008 R2, I’ll be using .NET 3.5)

These three components give us an application in which we can write and compile code, a database engine to host the code we will write, and the CLR we need to execute the code.

Configuration

The configuration steps for each component are simple, but I want to review them quickly here.  You may need to take particular consideration to setting up Visual Studio with your environment’s source control, but I will not be covering that in this series.  Each shop implements their source control a little differently and it would be best to stay consistent with the rest of your development endeavors.

SQL Server

For basic SQL CLR procedures, the only configuration needed on SQL Server is to enable CLR. I have included the script from MSDN here, for quick reference:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

.NET Framework

To enable the .NET Framework on the SQL Server, you will need to install the .NET 3.5 Framework (available from Microsoft).  If you are on Windows 2008 or Windows 2008 R2, you will have to Enable the Application Server Role first.  To enable the Application Server Role, you simply open “Server Manager”, right-click on “Roles” and select “Add Roles”.  Then select “Application Server” from the “Server Roles” list in the “Add Roles Wizard” dialog box, and click through the rest of the wizard to complete installation:

Add Application Server Role

Visual Studio

Lastly, we need to configure Visual Studio.  After we have Visual Studio installed, we can open it up and Create a New Project.  In the “New Project” Dialog box, select “Database” and then “SQL Server” from the Left pane, called “Installed Templates”.  In the center pane, select “Visual C# SQL CLR Database Project”.  I suggest C# just because it is the language I prefer and also the language I will be writing all future examples in, but you can also choose Visual Basic, if that suits you better.  At the top of the dialog, change the .NET Framework to 3.5 (this can be changed later, if needed), give the project a name, and click “OK” to create your project:

Visual Studio 2010 - New SQL CLR Project

 

Now that we have an open project, we need to setup our connection to the database we will be using.  In the Solution Explorer pane of Visual Studio, right-click on the project you created, and select “Properties”. Find the “Database” tab, click on it, and then click the “Browse…” button next to the “Connection String” field.  A small dialog will come up, allowing you to chose an existing “Database Reference” or add a new one, if needed.  Select the appropriate database reference, and close out of this dialog:

Add Database Reference To Project

 

Lastly, we need to add a class that will contain the object(s) that we will be creating.  For example, we can create a “Stored Procedure” class by right-clicking on the Project in the Solution Explorer, and then select “Add”, and then “New Item…”.  This brings up the “Add New Item” dialog box, where we can select “Stored Procedure”.  Select that item, give it a name, and click the “Add” button:

Add Item To Project

Now we have an environment that is ready to start creating SQL CLR objects.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

  1. The Origination and Uses of SQL CLR
  2. Infrastructure Requirements and Configuration
  3. Writing a Simple CLR Stored Procedure
  4. Security and Signing
  5. Deployment and Usage
  6. Table-Valued Functions and Other SQL CLR (Coming Soon)
  7. Incorporating Existing Libraries (Coming Soon)

The Origination and Uses of SQL CLR

Posted by on March 3, 2013 4 comments

Executive Summary

SQL CLR is a feature which allows database developers to create powerful code that can be executed from within SQL statements, utilizing familiar programming languages.

More Detail

SQL CLR is a feature, added in SQL Server 2005, that allows a database developer to host .NET managed code inside of a SQL Server database instance.  The hosted code can then be used within SQL DML statements to execute procedures/methods that were written in a .NET language.  To understand what is going on here, a brief understanding of .NET, CLR, CIL, and CLI is useful.

Starting in the early 2000s, Microsoft released a set of languages under a common framework, called .NET (Visual Basic .NET, C# .NET, and J# .NET).  The intent was to be able to bring a common set of features together from the different leading high-level programming languages at the time (Visual Basic, C/C++, and Java).  Microsoft published their own versions of each of these languages (Microsoft already had versions of all of these languages in the market), some being changed more greatly than others.

They were all published under one framework so that features available to one language would be available to all, although with different implementations.  Each of these languages would then compile down to a common code type, known as Common Intermediate Language (CIL).  This is not to be confused with the Common Language Infrastructure (CLI) which is a standard, developed by Microsoft, in conjunction with the .NET endeavor.  The CLI is the standard, .NET/CIL/CLR is just the way that Microsoft implemented this standard.

This Intermediate Language would then be converted by the Common Language Runtime (CLR) into machine code, which is specific to a particular processor family. Having this relationship between the CIL and the CLR is what makes .NET languages Interoperable and Portable.  The languages can be said to be Interoperable in that any language that compiles down to CIL can be linked with other similarly compiled code.  When I say Portable, I mean that, theoretically, a CLR-type interpreter (virtual machine) could be created for basically any platform you wish, and that new interpreter would be able to convert that CIL into machine code that would run on that platform.

This is where SQL CLR comes back into the picture.  The SQL Common Language Runtime hosts a distinct engine within SQL Server that runs the CIL that the database developer has created.  This enables programmers to work in the managed languages, with which they are already familiar.  They can take advantage of many of the features that are only available to managed and compiled code.  It is important to remember, however, that there is overhead in running through the CLR and poorly written or implemented methods can actually cause huge performance problems.  Like anything in database development, you have to learn how to use the tools effectively.  There are no magic bullets.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

  1. The Origination and Uses of SQL CLR
  2. Infrastructure Requirements and Configuration
  3. Writing a Simple CLR Stored Procedure
  4. Security and Signing
  5. Deployment and Usage
  6. Table-Valued Functions and Other SQL CLR (Coming Soon)
  7. Incorporating Existing Libraries (Coming Soon)

SQL CLR: An Introduction

Posted by on March 3, 2013 3 comments

Microsoft SQL Server has a ton of features and tools built into the product that we use every day. But sometimes we need to be able to step outside of the base feature-set and jump into traditional programming to accomplish tasks that are either harder or impossible without doing so. In SQL Server 2005, Microsoft introduced SQL CLR to enable this sort of extensiblity. I have used SQL CLR to accomplish several different types of tasks and I have found that there are a lot of different nuances to consider when using this technology. I am going to attempt to distill these points into a short series of blog posts that will outline the basics of SQL CLR. The points I am hoping to cover are as follows:

  1. The Origination and Uses of SQL CLR
  2. Infrastructure Requirements and Configuration
  3. Writing a Simple CLR Stored Procedure
  4. Security and Signing
  5. Deployment and Usage
  6. Table-Valued Functions and Other SQL CLR (Coming Soon)
  7. Incorporating Existing Libraries (Coming Soon)

I will update this post with links to the other blog posts as I write them.  I hope that this short series will prove useful to those trying to wade through this technology.

My Red-Gate Hyperbac + DBCC CheckDB Script

Posted by on January 28, 2013 6 comments

Several people have asked me to post my script for automatically cycling through each database in an instance, restoring a virtual hyperbac database, performing consistency checks, and then e-mailing out the results, in reference to my previous post.

I am sorry it took me so long to get this put together, but pulling it out of my environment was the tough part.  I have a lot of infrastructure built up around this, so I had to extract the parts and try to cull it into one consistent script.  I have not tested this script very thoroughly, so please use with caution.  Make sure it works in your environment.  Keep in mind that you must have hyperbac already installed and setup to make proper user of this script.

Thank you for your patience:

SET NOCOUNT ON;
 
-- Constants
DECLARE @BACKUP_DAYS_THRESHOLD				INT = 8,
		@NEW_BACKUP_FILE_LOCATION			VARCHAR(256) = 'C:\Temp\BackupFileCopies\',
		@DATAFILE_RESTORATION_LOCATION		VARCHAR(256) = 'C:\Temp\DataFileRestores\',
		@DB_MAIL_PROFILENAME				SYSNAME = 'Database Administrators', 
		@EMAIL_RECIPIENTS					VARCHAR(1000) = 'yourEmail@yourDomain.com'
 
-- Retrieve List of Last Full Backups and their Backup Files
IF OBJECT_ID('TempDB..#lastBackupsTaken') IS NOT NULL
	DROP TABLE [#lastBackupsTaken]
 
CREATE TABLE [#lastBackupsTaken]
(
[serverName]		VARCHAR(256),
[databaseName]		VARCHAR(256),
[physicalFileSpec]	VARCHAR(256),
[backupFinishDate]	DATETIME
)
 
INSERT INTO
	[#lastBackupsTaken]
	(
	[serverName],
	[databaseName],
	[physicalFileSpec],
	[backupFinishDate]
	)
SELECT
	@@SERVERNAME,
	[BackupSets].[database_name],
	[MediaFamily].[physical_device_name],
	[BackupSets].[backup_finish_date]
FROM
	[msdb].[dbo].[backupset] AS BackupSets
 
		INNER JOIN (
					SELECT
						[database_name],
						MAX([backup_start_date]) AS MaxBackup_Start_Date
					FROM
						[msdb].[dbo].[backupset]
					WHERE
							[type] = 'D'
						AND	[backup_finish_date] IS NOT NULL
					GROUP BY
						[database_name]
					) AS Constrained ON
				[BackupSets].[database_name] = [Constrained].[database_name]
			AND	[BackupSets].[backup_start_date] = [Constrained].[MaxBackup_Start_Date]
			AND	[BackupSets].[type] = 'D'
 
		INNER JOIN [msdb].[dbo].[backupmediafamily] AS MediaFamily ON
			[BackupSets].[media_set_id] = [MediaFamily].[media_set_id]
 
-- Retrieve List of the FileGroups in the Databases as of the Last Full Backups
IF OBJECT_ID('TempDB..#logicalFilesForlastBackupsTaken') IS NOT NULL
	DROP TABLE [#logicalFilesForlastBackupsTaken]
 
CREATE TABLE [#logicalFilesForlastBackupsTaken]
(
[serverName]	VARCHAR(256),
[databaseName]	VARCHAR(256),
[logicalName]	VARCHAR(256)
)
 
INSERT INTO
	[#logicalFilesForlastBackupsTaken]
SELECT
	@@SERVERNAME,
	[BackupSets].[database_name],
	[LogicalFiles].[logical_name]
FROM
	[msdb].[dbo].[backupset] AS BackupSets
 
		INNER JOIN (
					SELECT
						[database_name],
						MAX([backup_start_date]) AS MaxBackup_Start_Date
					FROM
						[msdb].[dbo].[backupset]
					WHERE
							[type] = 'D'
						AND	[backup_finish_date] IS NOT NULL
					GROUP BY
						[database_name]
					) AS Constrained ON
				[BackupSets].[database_name] = [Constrained].[database_name]
			AND	[BackupSets].[backup_start_date] = [Constrained].[MaxBackup_Start_Date]
 
		INNER JOIN [msdb].[dbo].[backupfile] AS LogicalFiles ON
				[BackupSets].[backup_set_id] = [LogicalFiles].[backup_set_id]
			AND	[LogicalFiles].[is_present] = 1
 
-- Create a list of all databases we are going to restore and Consistency Check (DBCC CHECKDB)
IF OBJECT_ID('TempDB..#databasesToCheck') IS NOT NULL
	DROP TABLE [#databasesToCheck]
 
CREATE TABLE [#databasesToCheck]
(
[databaseName]		VARCHAR(256),
[executionOrder]	INT IDENTITY(1,1)
)
 
INSERT INTO
	[#databasesToCheck]
	(
	[databaseName]
	)
SELECT
	[databaseName]
FROM
	[#lastBackupsTaken]
WHERE
	[databaseName] NOT IN (
							'master',
							'tempdb'
							)
GROUP BY
	[databaseName]
 
-- For each database in the list, copy the backup files, run a Red-Gate Hyperbac Virtual Restore, Run Consistency Check, Record the results, and then drop the database
DECLARE @currentDBIdentifier			INT,
		@maxDBIdentifier				INT,
		@currentDBName					VARCHAR(256),
		@lastBackupTime					DATETIME,
		@statusMessage					VARCHAR(MAX) = '',
		@statusCode						INT = 0,
		@errorMessage					VARCHAR(MAX) = '',
		@messageSubject					VARCHAR(256),
		@sql							VARCHAR(MAX) = '',
		@backupCopyResults				VARCHAR(MAX) = '',
		@currentBackupFileIdentifier	INT,
		@maxBackupFileIdentifier		INT,
		@commandLine					VARCHAR(2000),
		@databaseWasRestored			BIT = 0
 
IF OBJECT_ID('TempDB..#backupFilesToHandle') IS NOT NULL
	DROP TABLE [#backupFilesToHandle]
 
CREATE TABLE [#backupFilesToHandle]
(
[oldFileSpec]		VARCHAR(256),
[newFileSpec]		VARCHAR(256),
[executionOrder]	INT IDENTITY(1,1)
)
 
IF OBJECT_ID('TempDB..#commandLineResults') IS NOT NULL
	DROP TABLE [#commandLineResults]
 
CREATE TABLE [#commandLineResults]
(
[outputLine]		NVARCHAR(255)
)
 
IF OBJECT_ID('TempDB..#checkDBResults') IS NOT NULL
	DROP TABLE [#checkDBResults]
 
CREATE TABLE [#checkDBResults]
(
[ServerName] [varchar](100) NULL,
[DatabaseName] [varchar](256) NULL,
[Error] [varchar](256) NULL,
[Level] [varchar](256) NULL,
[State] [varchar](256) NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [varchar](256) NULL,
[Status] [varchar](256) NULL,
[DbId] [varchar](256) NULL,
[Id] [varchar](256) NULL,
[IndId] [varchar](256) NULL,
[PartitionId] [varchar](256) NULL,
[AllocUnitId] [varchar](256) NULL,
[File] [varchar](256) NULL,
[Page] [varchar](256) NULL,
[Slot] [varchar](256) NULL,
[RefFile] [varchar](256) NULL,
[RefPage] [varchar](256) NULL,
[RefSlot] [varchar](256) NULL,
[Allocation] [varchar](256) NULL,
[insert_date] [datetime] NULL
)
 
-- Begin Database Loop
SELECT
	@currentDBIdentifier = MIN([executionOrder]),
	@maxDBIdentifier = MAX([executionOrder])
FROM
	[#databasesToCheck]
 
WHILE (@currentDBIdentifier < @maxDBIdentifier) 	BEGIN 		SELECT 			@currentDBName = [databaseName] 		FROM 			[#databasesToCheck] 		WHERE 			[executionOrder] = @currentDBIdentifier 		-- Let's make sure the last database backup isn't too old (in case some third-party script clears off old database backups) 		SELECT 			@lastBackupTime = MAX([backupFinishDate]) 		FROM 			[#lastBackupsTaken] 		WHERE 			[databaseName] = @currentDBName 		IF (DATEDIFF(DAY,@lastBackupTime,GETDATE()) > @BACKUP_DAYS_THRESHOLD)
			BEGIN
				-- The oldest backup for this database was taken too long ago
				SET @statusMessage =  @statusMessage + 'DBCC FOR ' + @currentDBName + ' was not properly performed (Last Backup Too Old)
'
				SET @statusCode = @statusCode + 1
			END
		ELSE
			BEGIN
				-- Prepare the Backup Files
				TRUNCATE TABLE [#backupFilesToHandle]
 
				INSERT INTO
					[#backupFilesToHandle]
					(
					[oldFileSpec],
					[newFileSpec]
					)
				SELECT
					[physicalFileSpec],
					@NEW_BACKUP_FILE_LOCATION + SUBSTRING([physicalFileSpec],((LEN([physicalFileSpec]))-(CHARINDEX('\',REVERSE([physicalFileSpec])))+2),(CHARINDEX('\',REVERSE([physicalFileSpec])))-1)
				FROM
					[#lastBackupsTaken]
				WHERE
					[databaseName] = @currentDBName
 
				-- Start the restore script and copy the backup files
				SET @sql = 'RESTORE DATABASE [' + @currentDBName + '_Virtual] FROM
							'
 
				-- Begin Backup File Loop
				SELECT
					@currentBackupFileIdentifier = MIN([executionOrder]),
					@maxBackupFileIdentifier = MAX([executionOrder])
				FROM
					[#backupFilesToHandle]
 
				WHILE (@currentBackupFileIdentifier <= @maxBackupFileIdentifier)
					BEGIN
 
						-- Create Command Line syntax for file copy
						SELECT
							@commandLine = 'copy "' + [oldFileSpec] + '" "' + [newFileSpec] + '" /Y'
						FROM
							[#backupFilesToHandle]
						WHERE
							[executionOrder] = @currentBackupFileIdentifier
 
						-- Truncate the Command Line Results Table
						TRUNCATE TABLE [#commandLineResults]
 
						INSERT INTO
							[#commandLineResults]
						EXEC
							[master].[dbo].[xp_cmdshell] @commandLine
 
						-- Record Copy Results
						SET @backupCopyResults = @backupCopyResults + '
For command issued=' + @commandLine + '
'
 
						SELECT
							@bakCopyResults = @bakCopyResults + '
' + ISNULL([outputLine],'NULL')
						FROM
							[#commandLineResults]
 
						-- Add this file to the restore script
						SELECT
							@sql = @sql + 'DISK=N''' + [newFileSpec] + ''','
						FROM
							[#backupFilesToHandle]
						WHERE
							[executionOrder] = @currentBackupFileIdentifier
 
						SET @currentBackupFileIdentifier = @currentBackupFileIdentifier + 1
					END -- Loop to next Backup File
 
				-- Now that all backup files have been moved and we have added their new locations to the restore script,
					-- we now need to remove the trailing comma
				SET @sql = LEFT(@sql,LEN(@sql)-1) + '
				WITH 
				'
 
				-- Now we need to add the database files to the restore script
				SELECT
					@sql = @sql + 'MOVE N''' + [logicalName] + ''' TO N''' + @DATAFILE_RESTORATION_LOCATION + LEFT(@currentDBName,35) + '_Virtual_' + LEFT([logicalName],35) +'.vmdf'','
				FROM
					[#logicalFilesForlastBackupsTaken]
				WHERE
					[databaseName] = @currentDBName
 
				-- Remove the trailing comma
				SET @sql = @sql + 'NORECOVERY, STATS=1, REPLACE
				'
				-- Now, we have the files moved and the restoration script created.  Next thing to do is to restore the database (using hyperbac)
				SET @databaseWasRestored = 0
 
				BEGIN TRY
 
					-- Restore the database
					EXEC(@sql)
 
					-- Recover the database
					SET @sql = 'RESTORE DATABASE [' + @currentDBName + '_Virtual] WITH RECOVERY, RESTRICTED_USER'
					EXEC(@sql)
 
					-- Put the virtual DB in Simple Recovery Model, since we do not need anything higher than that for the DBCC CHECKDB
					SET @sql = 'ALTER DATABASE [' + @currentDBName + '_Virtual] SET RECOVERY SIMPLE WITH NO_WAIT'
					EXEC(@sql)
 
					SET @databaseWasRestored = 1
 
				END TRY
 
				BEGIN CATCH
 
						SET @errorMessage = @errorMessage + @currentDBName + '
' + @backupCopyResults + '
 
' + 'Error Number: ' + CONVERT(VARCHAR,ERROR_NUMBER()) + ', Error Message: ' + ERROR_MESSAGE() + '
'
						SET	@statusMessage = @statusMessage + 'DBCC FOR ' + @currentDBName + ' was not restored properly (error message below). 
'
						SET @statusCode = @statusCode + 1	
 
				END CATCH
 
				-- Only continue if the database was properly restored
				IF (@databaseWasRestored = 1)
					BEGIN
 
						-- Run DBCC CHECKDB and Save the results to a table
						INSERT INTO
							[#checkDBResults]
							(
							[Error],
							[Level],
							[State],
							[MessageText],
							[RepairLevel],
							[Status],
							[DbId],
							[Id],
							[IndId],
							[PartitionId],
							[AllocUnitId],
							[File],
							[Page],
							[Slot],
							[RefFile],
							[RefPage],
							[RefSlot],
							[Allocation]
							)
						EXEC('DBCC CHECKDB(''' + @currentDBName + '_Virtual'') WITH TABLERESULTS')
 
						-- Fill in missing information
						UPDATE
							[#checkDBResults]
						SET
							[ServerName] = @@SERVERNAME,
							[DatabaseName] = @currentDBName
						WHERE
							[ServerName] IS NULL
 
						-- Drop the restored database
						EXEC('DROP DATABASE ['+ @currentDBName + '_Virtual]')
 
						-- analyze all DBCC checkdb results, e-mail out when an error is encountered
						IF EXISTS (
									SELECT
										[ServerName]
									FROM
										[#checkDBResults]
									WHERE
											[ServerName] = @@SERVERNAME
										AND	[DatabaseName] = @currentDBName
										AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database %'
									)
							BEGIN
								SET	@statusMessage = @statusMessage + 'DBCC FOR ' + @currentDBName + ' Passed.
'
							END	-- Condition: A passing entry for this DB in DBCC Results
						ELSE IF EXISTS (
										SELECT
											[ServerName]
										FROM
											[#checkDBResults]
										WHERE
												[ServerName] = @@SERVERNAME
											AND	[DatabaseName] = @currentDBName
										)
							BEGIN
								SET	@statusMessage = @statusMessage + 'DBCC FOR ' + @currentDBName + ' Failed! (Check the [#checkDBResults] table)
'
								SET @statusCode = @statusCode + 1
							END	-- Condition: No passing entry for this DB in DBCC Results
						ELSE
							BEGIN
								SET	@statusMessage = @statusMessage + 'DBCC FOR ' + @currentDBName + ' was not properly performed (Check Configuration)
'
								SET @statusCode = @statusCode + 1
							END	-- Condition: No entry whatsoever for this DB in DBCC Results
 
					END -- End of "Database was properly restored"
 
				SET @currentDBIdentifier = @currentDBIdentifier + 1
			END -- End of "Check if last backup is too old"
 
	END -- Loop to next Database
 
SET	@statusMessage = @statusMessage + '
DBCC CheckDB Process has completed for ' + @@SERVERNAME + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '
'
 
IF @statusCode = 0
	BEGIN
		SET @messageSubject = 'SUCCESS - DBCC CheckDB for ' + @@SERVERNAME
	END	-- Condition: There were no errors or failures in the consistency checking of this instance
ELSE
	BEGIN
		SET @messageSubject = 'FAILURE - DBCC CheckDB for ' + @@SERVERNAME
		SET @statusMessage = @statusMessage + @errorMessage
	END	-- Condition: At least one consistency check either failed or resulted in an error
 
EXEC [msdb].[dbo].[sp_send_dbmail]
	@profile_name = @DB_MAIL_PROFILENAME, 
	@recipients = @EMAIL_RECIPIENTS,
	@body = @statusMessage,
	@subject = @messageSubject,
	@body_format = 'HTML';

SQL Agent Job Frequency Intervals in Plain English

Posted by on January 27, 2013 No comments

A DBA I was talking to was trying to create a simple process that finds each SQL Agent Job on a given instance, extracts the basic information regarding it, and compiles a list that can be presented to non-DBAs for information about what sort of “undiscovered” processes are running in a given environment.  He had gotten a lot of the information compiled, but was having a problem dissecting the Job Schedule “Frequency Interval” field.  Now, to anyone who is familiar with binary, this is a fairly straight-forward bit-flag usage of a higher-order data-type.  But, not all DBAs have this in their background, and I think many could benefit from seeing it spelled out.

In the SQL Agent, you have the ability to have week-based job schedules, in which, you can specify particular days of the week.  For example, you may have a weekly job that performs your FULL backups, that only runs on Saturday, and then have your DIFFs run on Monday, Tuesday, Wednesday, Thursday, Friday, and Sunday.  Well, SQL Agent stores this sort of information in a single field as an integer.  This may seem awkward, but the logic behind it is very straight-forward.  Each of the lowest-order 7 bits represents a yes/no flag for a particular day of the week:

SQLAgentIntervalFrequenceBinaryExplaination
So, now that we have examined what this integer really means, the question naturally follows: “How do we represent this integer as a series of days?” I have written two different pieces of code to accomplish this. The first simply takes an integer at the beginning of the script and returns a simple string containing the days represented by that integer (this could easily be turned into a UDF or stored procedure). The second piece of code goes about things a little differently, but works well for the particular use case that we were originally discussing.

The first T-SQL:

SET NOCOUNT ON;
DECLARE @numberToTest   TINYINT = 119,
		@weekString     CHAR(21) = 'SunMonTueWedThuFriSat',
		@resultString	VARCHAR(35) = ''
 
IF OBJECT_ID('TempDB..#daysOfWeek') IS NOT NULL
	DROP TABLE #daysOfWeek
 
CREATE TABLE #daysOfWeek
(
[dayNumber]   TINYINT,
[dayCode]     TINYINT
)
 
INSERT INTO
	[#daysOfWeek]
	(
	[dayNumber],
	[dayCode]
	)
VALUES
	(1,1),
	(2,2),
	(3,4),
	(4,8),
	(5,16),
	(6,32),
	(7,64)
 
SELECT
	@resultString = @resultString + CASE
						WHEN (@numberToTest &amp; [dayCode]) = [dayCode] THEN
							SUBSTRING(@weekString,(([dayNumber] - 1) * 3 + 1),3) + ', '
						ELSE ''
					END
FROM
	[#daysOfWeek]                                    
 
-- Strip off the last comma
IF (LEN(@resultString) &gt; 1)
	SET @resultString = LEFT(@resultString,LEN(@resultString) - 1)
 
SELECT
	@resultString

And, the second T-SQL:

SET NOCOUNT ON;
 
IF OBJECT_ID('TempDB..#daysOfWeek') IS NOT NULL
       DROP TABLE #daysOfWeek
 
CREATE TABLE #daysOfWeek
(
[dayNumber]   TINYINT,
[dayCode]     TINYINT,
[dayName]		VARCHAR(11)
)
 
INSERT INTO
       [#daysOfWeek]
       (
       [dayNumber],
       [dayCode],
	   [dayName]
       )
VALUES
       (1,1, 'Sunday '),
       (2,2, 'Monday '),
       (3,4, 'Tuesday '),
       (4,8, 'Wednesday '),
       (5,16, 'Thursday '),
       (6,32, 'Friday '),
       (7,64, 'Saturday ')
 
;WITH BaseData AS (
			SELECT
				[SYSJOB].[job_id],
				[SYSJOB].[name],
				[SYSSCH].[freq_interval],
				[DOW].[dayName]
			FROM
				[msdb].[dbo].[sysjobs] AS SYSJOB
 
					INNER JOIN [msdb].[dbo].[sysjobschedules] AS SYSJOBSCH ON
						[SYSJOB].[job_id] = [SYSJOBSCH].[job_id]
 
					INNER JOIN [msdb].[dbo].[sysschedules] AS SYSSCH ON
						[SYSJOBSCH].[schedule_id] = [SYSSCH].[schedule_id]
 
						LEFT OUTER JOIN [#daysOfWeek] AS DOW ON
							([SYSSCH].[freq_interval] &amp; [DOW].[dayCode]) = [DOW].[dayCode]    
			)
SELECT
	[job_id],
	[name],
	REPLACE(REPLACE([DayNames],'',''),'','') AS DayNames
FROM
	(
	SELECT
		[job_id],
		[name],
		(
		SELECT
			[dayName]
		FROM
			[BaseData] 
		WHERE
			[job_id] = [Grouped].[job_id]
		FOR XML PATH ('')
		) AS DayNames
	FROM
		[BaseData] AS Grouped
	GROUP BY
		[job_id],
		[name]
	) AS ConcatenatedData

Poor Execution Plan While Ripping Large XML in SQL Server 2008

Posted by on January 27, 2013 3 comments

A few days ago, I ran into a very frustrating problem while trying to rip a large XML document into a table-structure in T-SQL on 2008.  I am setting up a new procedure to process large files (Download Sample File) that we get from the market into our databases.  These files are each about 92K lines of XML that I need to get into a table-structure, for further processing.  So, I start with my normal X-Path style SQL:

DECLARE @priceDataString		VARCHAR(MAX),
		@priceDataXML			XML
 
SELECT
	@priceDataString = BulkColumn
FROM
	OPENROWSET(BULK'C:\Temp\EnergyPrices92KLines.xml', SINGLE_BLOB) fileData
 
SET @priceDataXML = @priceDataString
 
IF OBJECT_ID('TempDB..#extractedData') IS NOT NULL
		DROP TABLE [#extractedData]
 
CREATE TABLE [#extractedData]
	(
	[deliveryDate]			CHAR(10),
	[hourEnding]			CHAR(5),
	[settlementPoint]		VARCHAR(50),
	[price]					DECIMAL(20,13),
	[dstFlag]				CHAR(1)
	)
 
INSERT INTO
	[#extractedData]
	(
	[deliveryDate],
	[hourEnding],
	[settlementPoint],
	[price],
	[dstFlag]
	)
SELECT
	ref.value('DeliveryDate[1]', 'CHAR(10)') AS deliveryDate,
	ref.value('HourEnding[1]', 'CHAR(5)') AS hourEnding,
	ref.value('SettlementPoint[1]', 'VARCHAR(50)') AS settlementPoint,
	ref.value('SettlementPointPrice[1]', 'DECIMAL(20,13)') AS price,
	ref.value('DSTFlag[1]', 'CHAR(1)') AS dstFlag
FROM
	@priceDataXML.nodes ('/EnergyPrices/EnergyPrice') R(ref)

Normally, in my experience, this type of code would execute in a few seconds, making it an efficient way to rip through XML.  This time was different though.  This time, the clock kept ticking, after playing with it a few times, assuming I had done something dreadfully wrong, I finally just let it go all the way until it finished.  It took 3 hours to complete this query on my work desktop.  I’m sure it is clear that this is unacceptable.  So, I start troubleshooting.

My first step in troubleshooting was to remove the “INSERT INTO [#extractedData]” clause, just to see how slowly it is returning each row.  To my surprise, the entire process returns in 3 seconds.  That is quite a change from 3 hours to 3 seconds, so I at least know that the problem is that a bad plan is being chosen due to the insert.  I’m confusing the optimizer somehow.

Now, I know I could have just went into SSIS, or had the developers rip the XML in C# before passing it on to me, but this SHOULD work, and I wanted to know why it wasn’t. So, I jump on Twitter and send out a “#sqlhelp” for anyone who can help me think this one through.  Right away, Paul Randal (Blog | Twitter), Jonathan Kehayias (Blog | Twitter), Robert Davis (Blog | Twitter) all jump in to try to help me sort out why this strange behavior is occurring.  They each gave me some great suggestions to try, but nothing was working.  Jonathan tried out my exact code, to try to replicate my problem, but it wasn’t working the same way for him, his was returning in just a few seconds.

At this point, I was really confused, we had tried every thing we could think of, and could only surmise that it was something with my configuration that was causing the problem.  But, I went ahead and tried my exact query on one of my 2008 R2 instances and it worked perfectly!  Just like on Jonathan’s server.  This made me a bit relieved, since it didn’t seem to be my code that was bad, but this left me even more baffled as to why the optimizer would interpret my code so poorly on 2008.  Jonathan is opening a case for this and I will post a link to that once he has done so.

Ultimately, Jonathan had another suggestion to try, one that made the query perform optimally on 2008 as well, I will post that here, in case anyone else runs into the same problem:

DECLARE @priceDataString		VARCHAR(MAX),
		@priceDataXML			XML
 
SELECT
	@priceDataString = BulkColumn
FROM
	OPENROWSET(BULK'C:\Temp\EnergyPrices92KLines.xml', SINGLE_BLOB) fileData
 
SET @priceDataXML = @priceDataString
 
IF OBJECT_ID('TempDB..#sourceData') IS NOT NULL
	DROP TABLE [#sourceData]
 
CREATE TABLE [#sourceData]
	(
	[xmlData] XML
	)
 
INSERT INTO
	[#sourceData]
	(
	[xmlData]
	)
VALUES
	(
	@priceDataXML
	)
 
IF OBJECT_ID('TempDB..#extractedData') IS NOT NULL
	DROP TABLE [#extractedData]
 
CREATE TABLE [#extractedData]
	(
	[deliveryDate]			CHAR(10),
	[hourEnding]			CHAR(5),
	[settlementPoint]		VARCHAR(50),
	[price]					DECIMAL(20,13),
	[dstFlag]				CHAR(1)
	)
 
INSERT INTO
	[#extractedData]
	(
	[deliveryDate],
	[hourEnding],
	[settlementPoint],
	[price],
	[dstFlag]
	)
SELECT
	ref.value('(EnergyPrice/DeliveryDate)[1]', 'CHAR(10)') AS deliveryDate,
	ref.value('(EnergyPrice/HourEnding)[1]', 'CHAR(5)') AS hourEnding,
	ref.value('(EnergyPrice/SettlementPoint)[1]', 'VARCHAR(50)') AS settlementPoint,
	ref.value('(EnergyPrice/SettlementPointPrice)[1]', 'DECIMAL(20,13)') AS price,
	ref.value('(EnergyPrice/DSTFlag)[1]', 'CHAR(1)') AS dstFlag
FROM
	(
	SELECT
		ref.query('.') AS ref
	FROM
		[#sourceData]
 
			CROSS APPLY [xmlData].nodes ('/EnergyPrices/EnergyPrice') R(ref)
	) AS SourceData

Ripping XML in T-SQL can be a huge chore sometimes, but it makes it even worse when the database engine is not behaving the way that you expect it to.  I hope this post can save someone else from the headaches I had with this sort of code.  Huge thanks to Jonathan for his continued help on troubleshooting this annoying problem.

Precision in Date/Time Data Types

Posted by on October 30, 2012 6 comments

There is a general misconception, that is common across many different programming/scripting languages, regarding Date/Time data types, their precision, and how this differs in theory versus practice.  For the sake of this article, I will be discussing the following data types: DATETIME in T-SQL, DATETIME2(7) in T-SQL, and DateTime in C#.NET.

Data Type Precision (Theoretical Precision)

Data Type Theoretical Precision
SQL DATETIME 3.33 ms
SQL DATETIME2(7) 100 ns
C# DateTime 100 ns

When viewing the information presented above, the natural assumption is that each of these data types will give you a unique value at intervals of their precision.  For example, if you were using a SQL DATETIME and queried the time every 1 ms, you would expect to see a unique value roughly once out of every three queries.  The same would be expected of the higher precision data types, should they be queried on relatively similar timelines.

This, unfortunately, is not how Date/Time data types work.  This theoretical precision is representative only of what the data type structure is physically capable of holding, not of what the variable will ever practically contain.

Operating System Precision (Practical Precision)

The important factor that most programmers forget is that every program/query/script is running in the context of some Operating System and these Operating Systems are further running in the context of a processor and RAM.  The processor, while very fast, is consumed with many different actions happening to handle multitasking loads, and thus cannot dedicate a large percentage of its cycles to updating a clock.  The way processors get around this issue is by updating a portion of memory with the current date and time, at some predetermined interval.  The Operating System exposes this data through any one of several counters.  When a program requests the current time from the OS, the OS gives the program the latest reading of the “current” time.  In this way, the counter can be said to be highly, but not perfectly accurate, and highly precise to within some threshold (which we will see next).

When a program requests the current date and time from the operating system, it does so through a built-in function/method.  SQL DATETIME can get its data from GETDATE(), SQL DATETIME2(7) should get its data from SYSTEMDATETIME(), and C# DateTime can get its data from DateTime.Now.  GETDATE() hits an OS counter that has a precision of approximately 15 ms, while SYSTEMDATETIME() and DateTime.Now hit an OS counter that has a precision of approximately 1 ms.

Q.E.D., when you call GETDATE(), you will receive a data value that, while capable of holding a time down to a precision of 3.33 ms, will actually only be precise to about 15 ms (in Windows 2000).  In our previous example of querying every 1 ms, you would only see a unique value roughly once out of every 15 queries, as opposed to the 3 we previously would have assumed.

Demonstration

In order to see the effect we have discussed, the following code will capture the number of times we saw unique values and the average time between unique values.

-- SQL GETDATE
DECLARE @TimeStart DATETIME
DECLARE @Time DATETIME
DECLARE @TimeEnd DATETIME
DECLARE @I INT
DECLARE @Count INT
 
SET @I = 0
SET @Count = 0
SET @TimeStart = GETDATE()
SET @Time = @TimeStart
 
WHILE @I < 10000000
      BEGIN
            SET @TimeEnd = GETDATE()
            IF @TimeEnd != @Time
                  BEGIN
                        SET @Count = @Count + 1
                        SET @Time = @TimeEnd
                  END
            SET @I = @I + 1
      END
PRINT CAST(@Count AS VARCHAR) + ' unique values'
PRINT CAST(DATEDIFF(millisecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL) AS VARCHAR) + ' milliseconds'
 -- SQL SYSTEMDATETIME
DECLARE @TimeStart DATETIME2
DECLARE @Time DATETIME2
DECLARE @TimeEnd DATETIME2
DECLARE @I INT
DECLARE @Count INT
 
SET @I = 0
SET @Count = 0
SET @TimeStart = SYSDATETIME()
SET @Time = @TimeStart
 
WHILE @I < 10000000
      BEGIN
            SET @TimeEnd = SYSDATETIME()
            IF @TimeEnd != @Time
                  BEGIN
                        SET @Count = @Count + 1
                        SET @Time = @TimeEnd
                  END
            SET @I = @I + 1
      END
 
PRINT CAST(@Count AS VARCHAR) + ' unique values'
PRINT CAST(DATEDIFF(microsecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL) AS VARCHAR) + ' microseconds'
// C# DateTime
DateTime timeStart;
DateTime time;
DateTime timeEnd = DateTime.Now;
 
int i = 0;
int count = 0;
timeStart = DateTime.Now;
time = timeStart;
 
while(i < 10000000)
{
       timeEnd = DateTime.Now;
       if(timeEnd != time)
       {
              count++;
              time = timeEnd;
       }
       i++;
}
 
Console.WriteLine("{0} unique values",count);
Console.WriteLine("{0} microseconds", ((timeEnd.Ticks - timeStart.Ticks) / count) / (TimeSpan.TicksPerMillisecond / 1000));

From executing these I found the following results:

Data Type Theoretical Precision Practical Precision
SQL DATETIME (on Win2000) 3.33 ms 15.5556 ms
SQL DATETIME (on Win2K3) 3.33 ms 3.33333 ms
SQL DATETIME2(7) 100 ns 1003.71 µs
C# DateTime 100 ns 1000.21 µs

From these results, we can discern a couple things.  First, the counter that SQL Server accesses in Windows 2000 has a practical precision of approximately 15 ms, while the counter that SQL Server accesses in Windows 2003 (and beyond) has a practical precision of approximately 3.33 ms, which is basically the same as the theoretical precision.  Secondly, In both the case of SQL DATETIME2(7) and C# Datetime, the practical precision is 1 ms, which falls woefully short of the 100 ns theoretical precision.

Conclusion

Developers should be aware of what their data types and functions are doing under the surface.  In the case of SQL DATETIME2 and C# DateTime, developers must keep in mind that the true precision of the system date and time retrieved through those data types only has a precision of 1 ms.