T-SQL Code Review Checklist

In software development, code review is a simple and effective way to reduce the number of bugs that creep in. Merely having a second pair of eyes look at a piece of code can often reveal logic that was implemented incorrectly or weakly, or misses use cases never considered. (Test-driven development is a separate discussion entirely; T-SQL unit testing is not the easiest thing in the world to do, and compromises sometimes have to be made, usually due to time constraints or politics.)

There are many good reasons why code review is a good practice, and I’m not going to get into that here. You can use Google as well as I can, or you can start by reading some of these questions on Software Engineering Stack Exchange. The purpose of this post is to consolidate many of the different things I look at when doing code review, or even when I’m looking over my own code. While this won’t be a complete list, and the items will vary somewhat depending on your company’s environment, it should be a good start.

In no particular order:

  • Syntax, including version-specific language use. This seems really obvious, but it can be easy to slip up. For example, if you normally do your development on a 2008+ server and the application still has to support 2005, it’s easy to write something like DECLARE @i int = 0 and only have this get caught during testing. (Testing is done against all versions of the database engine that are supported by the application, right? …right?) In terms of outright syntax, pay particular attention to T-SQL that’s contained in string variables, meaning dynamic SQL, or SQL statements embedded in client applications, as these aren’t validated until they are run through the containing code. Do all INSERT statements have a column list, and are all required columns specified?
  • Data/business logic protection. This could apply to either designing tables (which I do recommend code-reviewing, too), or when writing code to manipulate data (in the tables or otherwise). Are there enough constraints on the tables to prevent invalid data from entering the system? Are there any logical gaps in a piece of code? Does the piece of code accomplish the prescribed task? If in doubt, ask the developer what was intended. Sometimes logical gaps can be okay if a condition is rare, and the code is appropriately guarded. These same ideas apply to things like procedure parameters, particularly if any of the parameter values come from application user input.
  • Dynamic SQL. Follow through all logic paths to make sure the statements are being concatenated correctly for both syntax and potential injection attack vectors. Make sure the statements are parameterized appropriately.
  • Code Cleanliness. This is a bit tough to define. I personally don’t expect strict adherence to a style guide, for example, but I do expect code to be legible, and make appropriate use of indentation, line breaks and comments. Everyone has their own natural style — and even that can change over time — so I think giving people more or less free reign over style is fine. Again, as long as the code is legible to others, because that is the most important thing.
  • Version- or edition-specific feature use. This shouldn’t come up, as it should have been decided during the design phase, but at the end of the day, a developer can check anything in to the version control system.
  • Use of unexpected methods to accomplish a task. Does the code use a Rube-Goldberg-like approach to accomplish the task? Are cursors being used when all that’s necessary is a slightly more difficult to write set-based operation? In these types of cases, I will always go back to the developer and find out why this kind of approach was taken, and what was intended to be accomplished. Most of the time, this kind of situation provides a great learning opportunity.
  • Query plan reuse. Does the code reuse (or not reuse) query plans as expected? Pay particular attention to dynamic SQL: for example, there can be subtle string concatenation bugs where two generated statements (via different code paths) only differ by whitespace, but this is enough to produce a separate query plan. Sometimes dynamic SQL is used to force the creation of an alternative plan for the expected data size — does this work as expected?
  • Performance. Does the code meet the performance requirements, if any, and/or does it execute in a “reasonable” amount of time with a “reasonable” amount of resource usage? Does the code use table variables or temporary tables, and how does this interact with the query plan(s)? Does the code produce efficient query plans that will scale? It’s important to test code on a real data set to find out these things. Some of this is technically a “testing” activity; however, I would say it’s unlikely a testing department will have the technical skill required to determine if the code is doing the right thing. Reading and understanding query plans is a huge topic in itself; in short, look for inefficient operators that are unexpected, such as table scans (particularly as the bottom operator in a nested loops join), huge row counts (thick arrows), missing indexes, etc. Speaking of indexes, determine if the code would benefit from index changes, even if query performance is currently acceptable. Even if you don’t make those changes now, record your findings, as it could be valuable information to have later if performance does become a problem. This could potentially save an incredible amount of time and effort doing a workload analysis.
  • Deprecated features. This is fairly broad, as deprecation can happen for a wide range of things, from major features (i.e., database mirroring) to minor T-SQL syntax (i.e., using HOLDLOCK without surrounding parentheses). That said, for every new release of SQL Server, it’s a good idea to thoroughly read the official list of deprecated features, and make sure no new code is using any of it unless absolutely necessary (and in that case, there needs to be a plan to modify the system to remove the deprecated feature usage). I have a script on the Scripts & Code resource page that dumps the internal performance counters that track usage of deprecated features, which I think may be more broad than the official reference list. This method is great if you want to find out what your application is really doing in production, so you can proactively fix it long before it becomes a problem.

That’s a pretty good start! If you think I’ve left out something important, let me know in the comments and I’ll add it to the post, as I want this to be a good reference guide.

Using Table-Valued Parameters in SSIS

Table-valued parameters (TVPs) are a great way to move chunks of data between your application and SQL Server, while still retaining the ability to abstract database functionality with stored procedures and functions.

TVPs were introduced in SQL Server 2008, but I feel they may be underused, not known, or only just now being put on the table for the developers of vendor applications where support for SQL Server 2005 is finally being discontinued.

I’m putting this post out there for DBAs, because while TVPs are relatively easy to implement as a programmer, not all DBAs have the same set of programming skills. There are many, many good reasons for a DBA to learn the .NET framework in at least one modern language (probably C#), but that’s a topic for another post. The point here is to put TVPs in the context of SSIS, where they can be very useful. The only reason this is remotely tricky is because TVPs are not exposed in SSIS directly — you have to implement them yourself in code.

The first thing we need to do is set up some database objects to play with. The following script creates a user-defined table type (which is the table-valued parameter type), a table into which we’ll dump some data, and a stored procedure that has a TVP, which we’ll call from our SSIS package.

CREATE TYPE [dbo].[TestTable] AS table
(
	Column1 varchar(10) NOT NULL,
	Column2 varchar(10) NOT NULL
);
GO

CREATE TABLE [dbo].[TestData]
(
	Id int IDENTITY PRIMARY KEY,
	Col1 varchar(10) NOT NULL,
	Col2 varchar(10) NOT NULL
);
GO

CREATE PROCEDURE [dbo].[TestProcedure]
(
	@testData [dbo].[TestTable] READONLY
)
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO [dbo].[TestData](Col1, Col2)
		SELECT
			Column1,
			Column2
			FROM @testData;

END
GO

That’s pretty straight-forward. All we’re going to do is accept a table as a parameter to the stored procedure, and insert the rows of that table parameter into the base table.

In order to use the procedure, we have to write a little bit of code. Essentially what we must end up with is our data in a DataTable object, which we’ll pass as a parameter when we call the stored procedure. There are many different ways to do this in SSIS, depending on where our data is coming from, and what, if anything, we need to do with the data before it gets sent off to the stored procedure.

In this example, I chose to use a Script Component destination inside a Data Flow task. If you’re following along at home, the design surface should look like this, noting that the connection manager must be an ADO.NET Connection Manager:

TVP 1

The Target Connection points to our testing database, wherever it was landed.

In the ADO.NET Source, I chose to fabricate some junk data using this query:

SELECT 'a' AS c1, 'b' AS c2 UNION ALL
SELECT 'c', 'd' UNION ALL
SELECT 'e', 'f'

Super junky.

There are two other settings to change in the Script Component before we start coding. First, select both input columns on the Input Columns tab:

TVP 2

and then expose the Target Connection connection manager:

TVP 3

Now we’re ready to write the code that calls the stored procedure. Edit the script of the Script Component and update the methods as follows:

using System.Data.SqlClient;  // Add this

...

public class ScriptMain : UserComponent
{
    private DataTable _data = null;  // Add this

    public override void PreExecute()
    {
        base.PreExecute();

        _data = new DataTable();

        // This matches the schema of the user-defined table type
        _data.Columns.Add("Column1", typeof(string));
        _data.Columns.Add("Column2", typeof(string));
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Make sure the order of the values matches the schema above
        _data.Rows.Add(new object[] { Row.c1, Row.c2 });
    }

    public override void PostExecute()
    {
        base.PostExecute();

        // Create a connection
        using (SqlConnection conn = (SqlConnection)Connections.Target.AcquireConnection(null))
        {
            try
            {
                // Open the connection
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                // Prepare a stored procedure command to execute
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "[dbo].[TestProcedure]";

                // Add the parameters for the command
                cmd.Parameters.AddWithValue("@testData", _data);

                // Execute the stored procedure
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Clean up
                if (conn != null)
                    Connections.Target.ReleaseConnection(conn);
            }
        }
    }
}

 

  • The PreExecute method creates the DataTable object with a schema that matches the user-defined table type.
  • The ProcessInputRow method is called once for each incoming row to the Script Component, so we add a row to the DataTable for every input row.
  • In PostExecute, we connect to SQL Server and call the stored procedure. The magic happens in the cmd.Parameters.AddWithValue method — it’s smart enough to realize that we passed in a DataTable instead of a simple type, and automatically handles sending the data to SQL Server. How it does this I will leave as an exercise for the reader to discover using Profiler.

As I said before, there are many different ways to incorporate this code into an SSIS package. It’s not a lot of code, but may be a little bit tricky for those who aren’t fluent .NET programmers.

Tuning Diagnostic Queries

Most of the time when we talk about tuning, it’s related to improving the performance of a production workload. Maybe (hopefully) we’ve gone as far as tuning more “hidden” code like stored procedures, triggers, and the black boxes that are user-defined functions. But what about the other queries we run against our production servers?

Maybe we use 3rd-party management/monitoring software, or in-built tools like Activity Monitor or Management Data Warehouse (aka Data Collector) that periodically checks up on our servers. In these cases, if we identify these systems as a performance problem, we’re pretty much stuck with either reducing the polling frequency, abandoning the use of the system entirely, or pestering the vendor until the problem is fixed. That’s pretty straight-forward.

There’s another class of queries we run against our production servers, though: diagnostic queries. If a server is in performance trouble, or we need to go troubleshooting for blocking, or even if we’re proactively searching for issues, we automatically whip out our trusty folder of scripts and start running queries to start to narrow things down; queries that could potentially make the problem worse by either adding extra load to the server, or even adding unnecessary time to the troubleshooting process.

It’s impossible, of course, to eliminate the need for the database engine to do some processing to return the results of our queries (the observer effect), but how much impact are our queries having on the server? Maybe in the grand scheme of things, not a lot. During a troubleshooting session, however, things are very different: people are anxious for answers, the problem needs to be solved now, and we’re working as hard as we can to not only fix the problem, but above all else, to not make things worse.

Now, I’m going to give an example, and I have to apologize in advance to Paul Randal and/or Glenn Barry because I’m about to single them out. Sorry, guys.

A good while back, Paul posted an excellent diagnostic query to analyze wait stats (note: I never saw Glenn’s original query), and I had stolen borrowed used it to troubleshoot some issues in the environment I work in. Of course, I played around with it before that, to see it was indeed a useful script to keep around. But when I ran it against the production server in peril, I was floored because it took so long to return the results I needed to see. I was also puzzled that after I reset the wait stats and then ran the query a few seconds later, the results… just didn’t look right for what I was expecting. What was going on? It was just a relatively simple SELECT statement from a DMV, which is usually pretty quick. I turned on STATISTICS IO and STATISTICS TIME and ran the query again:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(16 row(s) affected)
Table 'Worktable'. Scan count 7, logical reads 4664, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 812 ms,  elapsed time = 3469 ms.

Shock and/or awe. I think we found the problem! The diagnostic query was consuming a tremendous amount of resources just to tell us that the system was having its resources consumed tremendously.

Well we’ve got to do something about that, haven’t we? Ultimately the query got pulled apart, and using a pre-computation approach involving table and scalar variables, this is where it ended up:

SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 22 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table '#58739F6F'. Scan count 0, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 192, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 3 ms.
Table '#58739F6F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(16 row(s) affected)
Table '#58739F6F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Much better. (You can find the modified script on the Scripts & Code Resources Page. Note that it doesn’t do exactly the same thing as the original.)

So what can we learn from this?

  • While DMVs are usually fast, doing further analysis on the results can be expensive, depending on what we do, of course.
  • Test diagnostic queries for performance just as much as for functionality.
  • Fully test diagnostic queries long before they get thrust into the heat of battle. This is sword-sharpening for DBAs.
  • For the scripts that just… perform poorly… and there’s nothing more we can do about it, add comments to the script body that say as much. Whoever is about to run the script should be made aware that the query will take some time to finish. The warning will prevent situations like worrying about whether or not it’s working, or it even may alert the person to not run the script at all and use a different/similar method instead.

Registered Servers and Central Management Server: Setup

Edit 2013-10-14: This post is now available in video form.

Whether you manage one SQL Server instance or hundreds, you need to have a way to keep track of them all.

Just like best-practices when it comes to security, it would be nice to create logical groups of instances and manage them based on their group membership. Perhaps also inspect, or perform actions against groups of instances:

  • Run queries, including management-type activities like creating or running jobs
  • Check if the settings match a pre-determined configuration, and if not, fix the settings

While there isn’t really an inbuilt group-based way to manage these kinds of things, there are two out-of-the-box solutions to mimic many of the advantages, and which help you accomplish these tasks. These solutions are, of course, Registered Servers, and Central Management Server.

In terms of requirements, Registered Servers was available starting with the Management Studio packaged with SQL Server 2005, and support for registering an instance as a Central Management Server was added in SQL Server 2008. I don’t believe there’s any limitation on version of the instances you can register within either system (and I can’t find it in the documentation); off the top of my head I would say SQL Server 2000 and higher sounds pretty safe. From personal experience, 2005 and higher definitely works.

The UI to access both of these features is through the View | Registered Servers menu item in Management Studio. While it might be a bit strange that both of them are contained in one UI, we’ll see in a second that that’s actually an advantage, because it’s likely you’ll need to use both in your environment.

What is a Registered Server?

While Registered Servers and Central Management Server have different names, both ultimately do the same thing: store a user-defined hierarchy of SQL Server instance connection strings. In other words, a tree view that lists and categorizes your instances. The behind-the-scenes way this is accomplished is very different between the two systems, and this means there are advantages and disadvantages to each.

Although both names contain the word “servers,” what we’re actually dealing with are instances of SQL Server. I guess it was a design decision by the SQL Server team to call them Registered Servers instead of Registered Instances. It’s also a bit confusing because Registered Servers refers to one user-defined hierarchy, while Central Management Servers (note the plural) would refer to multiple hierarchies.

Differences

The main difference is that a Registered Servers list is only visible to one Windows user, while a Central Management Server list is publicly available to anyone with enough permissions to view the list.

The best way to show all the differences is probably just to put them in a table, so let’s do that:

Registered Servers Central Management Server
Storage Method XML file in Windows roaming profile msdb database
List Visibility Current user only Everyone who is authorized (view nothing, view all, edit all)
Security of Instance List Based on Windows user context Managed by SQL Server permissions, and msdb database roles
Types of Instances Database engine, SSAS, SSIS, SSRS Database engine only
Connection String Authentication Windows Authentication and SQL Server authentication Windows Authentication only

So now you can see the big tradeoffs between the two systems, and why I said it’s likely you’ll need to use both. Central Management Servers should in theory be able to support all types of instances, but sadly, it just isn’t implemented for some reason. My fingers are crossed that it’s implemented in the next version.

Setup

Since the storage method of the two system is very different, there are very different concerns when it comes to setting them up. Really there isn’t much to Registered Servers because of the fact that the list is isolated to a single user. With a Central Management Server, however, there are many more parts in play — the price we have to pay for power and flexibility — and so there are many more guidelines.

Briefly, the way I like to set things up is as follows:

  • Use Registered Servers for instances that I keep on my local machine (for development, or playing around) that aren’t usually accessible from the rest of the network, and for SSAS/SSIS/SSRS because there’s currently no other choice.
  • Set up a dedicated SQL Server instance just to be the Central Management Server instance, and register all publicly-available instances within a given domain. (One Central Management Server instance per domain is a good idea, particularly if that boundary separates development and production, for example.) The instance doesn’t have to be powerful in terms of either CPU or memory, but it does need to be very reliable because we will be using it to manage our servers in good times and bad. Even Express Edition can be used to set up a Central Management Server, but for obvious reasons, you will want to take regular backups of at least the msdb database by some means.
  • I recommend installing a Central Management Server instance on a separate management machine (again, probably one per domain), and not on a production machine. (I wrote a bit about this in the comments on my answer here). Definitely do not register a Central Management Server on an existing production instance — not only is that a bad practice in terms of separation of concerns for many reasons, but there’s also a limitation that a Central Management Server instance cannot appear in the hierarchy within “itself” as would happen in this case.
  • (If necessary) Configure security to determine who can view, and who can edit a Central Management Server list. As eluded to in the table, this is accomplished by assigning membership to the msdb fixed database roles ServerGroupReaderRole and ServerGroupAdministratorRole (MSDN page) as appropriate. It may also be desirable to use multiple Central Management Server instances to separate tiers of registered servers, such as development, test/QA, and production, because there isn’t any other way to control instance-level visibility besides the aforementioned roles.

Organizing the List

One of the great features of both Registered Servers and Central Management Server is that registered instances can appear in the hierarchy multiple times. Why is that great? Because it allows us to organize the instances in multiple ways within the same hierarchy.

We may want to group our instances together by:

  • Version and/or edition
  • Criticality (24/7, business working day, etc.)
  • Business area or department (development, test/QA, production, etc.)

And I’m sure you can think of a few others for your environment as well.

In fact, we may wish to categorize by more than one of these things for frequently-used parts of the hierarchy. When arranging the list for maximum usefulness, think about how to organize it such that if a single group is selected in the tree, changes would happen to all the instances within that group folder.

If you’re setting this up for the first time, and assuming there aren’t too many instances in play, it may just be a good idea to start with a relatively flat hierarchy, and build the groups as you find they’re needed. The reason why this strategy isn’t a good idea with many servers is because the ability to quickly move servers between groups in the UI is extremely limited. For a Central Management Server, the system tables in msdb could probably be edited directly, but I can’t say I’d recommend that, and I haven’t tried it myself either.

 

In a future post (likely a video demo), we’ll dive into using and leveraging Registered Servers and Central Management Server to their full potential through the UI.

To leverage Central Management Servers externally, such as through PowerShell or an external application, we can run queries against the following two views in msdb: dbo.sysmanagement_shared_registered_servers and dbo.sysmanagement_shared_server_groups, or use the various stored procedures to manage the list.

How much overhead do CHECK constraints have?

When designing new database structures, I feel it’s really important to make sure that the data goes into the new tables as cleanly as possible. Doing this early on helps prevent bugs from creeping into the system as it’s developed. And preventing bugs as early as possible in the development cycle is almost certainly the most cost-efficient strategy — cleaning up bad data later on is extremely expensive, time consuming, and frustrating.

One of the tools that can be used to reject bad data values going into tables are CHECK constraints. This type of constraint is meant to be used to validate a single row based on a predicate that’s a function of any combination of the table columns in that row. (CHECK constraints can be used for things beyond this definition… but doesn’t mean they should be.)

In this post, I’m not going to address any more of the advantages of constraining data values. What I do want to investigate is performance.

The extra logic to do this validation necessarily has additional processing cost associated with it. The question is: how much extra? Does it really make a difference? What kind of general guidelines can be established for performance purposes?

The Setup

I decided to run two series of tests: one with a very narrow table (3 columns), and one with a wide table (21 columns). We’ll INSERT a bunch of test data into each of the tables. A baseline would be established for each test, where the tables have no constraints. Then, different constraints would be added, and hopefully we’ll be able to see a difference in the load times.

Here is an abridged version of the table creation script (note: all scripts are available to download in full at the bottom of this post):

-- Narrow, no constraints
CREATE TABLE [dbo].[NarrowWithoutConstraints]
(
	Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Col1 int NOT NULL,
	Col2 varchar(10) NOT NULL
);

-- Wide, no constraints
CREATE TABLE [dbo].[WideWithoutConstraints]
(
	Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Col10 int NOT NULL,
	...
	Col19 int NOT NULL,
	Col20 varchar(10) NOT NULL,
	...
	Col29 varchar(10) NOT NULL,
);

-- Narrow, simple constraints
CREATE TABLE [dbo].[NarrowWithConstraints1]
(
	Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Col1 int NOT NULL CHECK (Col1 >= 0),
	Col2 varchar(10) NOT NULL CHECK (LEN(Col2) > 0)
);

-- Wide, simple constraints
CREATE TABLE [dbo].[WideWithConstraints1]
(
	Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Col10 int NOT NULL CHECK (Col10 >= 0),
	...
	Col19 int NOT NULL CHECK (Col19 >= 0),
	Col20 varchar(10) NOT NULL CHECK (LEN(Col20) > 0),
	...
	Col29 varchar(10) NOT NULL CHECK (LEN(Col29) > 0)
);

-- Narrow, complex constraints
CREATE TABLE [dbo].[NarrowWithConstraints2]
(
	Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Col1 int NOT NULL
		CHECK ((Col1 >= 0) AND (Col1 > -9999) AND (Col1 < 5000) AND (Col1 < 9999)),
	Col2 varchar(10) NOT NULL
		CHECK ((LEN(Col2) > 0) AND (DATALENGTH(Col2) = DATALENGTH(LTRIM(RTRIM(Col2)))))
);

-- Wide, complex constraints
CREATE TABLE [dbo].[WideWithConstraints2]
(
	Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Col10 int NOT NULL
		CHECK ((Col10 >= 0) AND (Col10 > -9999) AND (Col10 < 5000) AND (Col10 < 9999)),
	...
	Col19 int NOT NULL
		CHECK ((Col19 >= 0) AND (Col19 > -9999) AND (Col19 < 5000) AND (Col19 < 9999)),
	Col20 varchar(10) NOT NULL
		CHECK ((LEN(Col20) > 0) AND (DATALENGTH(Col20) = DATALENGTH(LTRIM(RTRIM(Col20))))),
	...
	Col29 varchar(10) NOT NULL
		CHECK ((LEN(Col29) > 0) AND (DATALENGTH(Col29) = DATALENGTH(LTRIM(RTRIM(Col29))))),
);

-- Narrow, many complex constraints
CREATE TABLE [dbo].[NarrowWithConstraints3]
(
	Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Col1 int NOT NULL
		CHECK ((Col1 >= 0) AND (Col1 > -9999) AND (Col1 < 5000) AND (Col1 < 9999)),
	Col2 varchar(10) NOT NULL
		CHECK ((LEN(Col2) > 0) AND (DATALENGTH(Col2) = DATALENGTH(LTRIM(RTRIM(Col2)))))
);

ALTER TABLE [dbo].[NarrowWithConstraints3]
	ADD CHECK ((Col1 > -1) AND (Col1 > -9998) AND (Col1 < 5001) AND (Col1 < 9998));

...

ALTER TABLE [dbo].[NarrowWithConstraints3]
	ADD CHECK ((Col1 > -9) AND (Col1 > -9990) AND (Col1 < 5009) AND (Col1 < 9990));

ALTER TABLE [dbo].[NarrowWithConstraints3]
	ADD CHECK ((LEN(Col2) > -1) AND ((DATALENGTH(Col2) + 1) = (DATALENGTH(LTRIM(RTRIM(Col2))) + 1)));

...

ALTER TABLE [dbo].[NarrowWithConstraints3]
	ADD CHECK ((LEN(Col2) > -9) AND ((DATALENGTH(Col2) + 9) = (DATALENGTH(LTRIM(RTRIM(Col2))) + 9)));

The Test

Testing these scenarios is pretty simple: shove a bunch of data into a table, and time how long it takes. One of the test harnesses is shown below:

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @i int = 1250;

SET NOCOUNT ON;

TRUNCATE TABLE [dbo].[<table>];
DBCC CHECKIDENT('dbo.<table>', RESEED, 1);

SET @startTime = SYSDATETIME();

WHILE @i >= 0
BEGIN

	INSERT INTO [dbo].[<table>](Col1, Col2)
		SELECT
			number,
			CAST(number AS varchar(10))
			FROM master..spt_values v
			WHERE v.type = 'P'

	SET @i -= 1;

END

SET @endTime = SYSDATETIME();

SELECT DATEDIFF(MILLISECOND, @startTime, @endTime);

So that’s pretty standard. I ran the narrow table test with @i = 1250, and the wide table test with @i = 750, which resulted in 2,560,000 and 1,536,000 rows inserted, respectively. For each table, I ran the test harness 11 times; the first result was discarded, and the rest got recorded and averaged together. Note that the table is TRUNCATEd and the identity seed reset before each run. Also, the database was in SIMPLE recovery.

The Results

My hypothesis was that the constraints (at least the simple ones) would add a little bit of overhead, but I wasn’t sure if it would be measurable, and it was unclear how much (if anything) SQL Server would do to simplify the expressions I used (turns out it didn’t simplify them at all).

Average (ms) ms/1000 Rows % Increase
Narrow (no constraints) 12,401 0.484
Narrow (simple constraints) 13,034 0.509 5.1%
Narrow (complex constraints) 14,505 0.567 17.0%
Narrow (many complex constraints) 25,101 0.981 102.4%

There are a couple of very interesting things here. First of all, the difference between no constraints and a small number of simple constraints is measurable. Second — this is what really surprised me — a complex constraint is significantly more expensive than the simple constraint. This makes sense because functions are always going to be more expensive than something like a basic comparison. It seems that perhaps this difference is magnified in this case because the amount of work to do the actual INSERT is small relative to the total amount of work needed to be done. That said, it appears that many complex constraints could at the very least hobble an INSERT-only workload.

Average (ms) ms/1000 Rows % Increase
Wide (no constraints) 12,477 0.812
Wide (simple constraints) 14,647 0.954 17.4%
Wide (complex constraints) 20,238 1.318 62.2%

In this test, we would expect that the differences are larger because of the number of constraints used. As a sanity check, we can see that the ratio of the increases (62.2% / 17.4% = 3.6) vs (17.0% / 5.1% = 3.3) is approximately the same.

If we compare the narrow table with many complex constraints with the wide table with complex constraints, they both have the same number and type of constraints, and the ms/1000 rows numbers have the same sort of increases (0.981 – 0.484 = 0.497) vs (1.318 – 0.812 = 0.506), respectively. This says that evaluating constraints has a fixed cost, independent of the table schema.

Conclusion

I certainly learned a lot from doing this experiment. There are a few takeaways from the results:

  • Disabling CHECK constraints will reduce the load time, perhaps significantly.
  • A CHECK constraint’s overhead is determined by it’s complexity of evaluation (comparisons good; function calls not so much).
  • Evaluating CHECK constraints has a fixed cost, and thus a smaller relative cost when the other parts of the process are more expensive.

While these aren’t Earth-shattering conclusions, we did get to validate the best-practice of disabling constraints for loading, and see just how much overhead CHECK constraints can add.

What I want to stress here is that this test was for a relatively narrow situation involving only INSERTs. If your workload is 100% INSERTs, then you definitely need to pay attention to these kinds of things, but if your system isn’t INSERTing 200k rows/sec — or even anywhere near that — it’s likely that the benefits of the constraints will outweigh the performance penalty, as the overhead will be a small portion of the overall workload.

Full scripts and test results:

01 – Create Tables.sql

02 – Narrow Table Test Harness.sql

03 – Wide Table Test Harness.sql

Test Results.xlsx