Category Archives: General Programming

Deployment and Usage of a CLR Stored Procedure

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

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

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

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

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

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.

SQL Agent Job Frequency Intervals in Plain English

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 & [dayCode]) = [dayCode] THEN
							SUBSTRING(@weekString,(([dayNumber] - 1) * 3 + 1),3) + ', '
						ELSE ''
					END
FROM
	[#daysOfWeek]                                    
 
-- Strip off the last comma
IF (LEN(@resultString) > 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] & [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

Precision in Date/Time Data Types

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.

Command Line and Quotes: Fun with C#

Most DBAs don’t get to jump into programming often, but I came from a development background, so I try to use some legitimate code every once in a while.  It helps me keep that skill-set fresh.  Recently, in my company, we had a need for a unified solution for dealing with archiving, deleting, compressing, and so forth of various types of files.  Having an application that can be passed a bunch of parameters and can be triggered by a simple scheduled task is very useful in many aspects of IT.

We used to use ABAKT for these sort of operations, but the project was abandoned a long time ago and we wanted something that we could add to as we needed.  So, I had a few hours of downtime one weekend and decided to write one.  Everything went very well with the project and it worked almost flawlessly.  There was just one problem I ran into that stumped me for a couple days.

The problem cropped up when I was trying to pass a UNC to the application that had a space in it.  Such as:

\\someserver.mydomain\theShare\folder\some folder with spaces\anotherFolder\

Initially, I thought, well that’s no problem, just encapsulate the string in double-quotes and we are fine, as so:

"\\someserver.mydomain\theShare\folder\some folder with spaces\anotherFolder\"

However, the command line interface interprets that last \” as an escaped double-quote and NOT a closing double-quote to the beginning of the UNC.  This, in turn, causes my C# argument parser to see the string as 4 parameters, rather than 1 long one.  The parameters passed to my C# application would be:

  • \\someserver.mydomain\theShare\folder\some
  • folder
  • with
  • spaces\anotherFolder”

The way this issue presented really threw me off track several times.  I couldn’t figure out why my parameters were so messed up.  My solution was to escape out the last backslash, this worked perfectly:

"\\someserver.mydomain\theShare\folder\some folder with spaces\anotherFolder\\"

A little deviation from SQL stuff, but some useful information I wish I could have found when i was working on this project.