Very often, I find myself wanting to query system metadata for columns. Some of the metadata is fairly easy to deal with, like if you want to find column names, just simply querying sys.columns is very straightforward.  But if you want to query data types, it gets stickier.  There are type types listed, one that is the declared type, and another for the base data type, which if you are using CREATE TYPE to create alias data types. So I started working on the query based on INFORMATION SCHEMA values, because it is a lot easier to work with. In my design book, I used that version (and will link this version in erratta/book updates.

But in the Books Online entry for the COLUMNS object (and others too), I discovered the following warning, a bit too late the change: “Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.” So I rewrote it to use the catalog views.

The query is fairly complex looking, but overall is pretty simple.  I put comments through the code that explains what it does.  I will note that I have tested the database using a case sensitive collation, but I haven’t tried it with a and CLR types.  The query returns the following:

• table_type – The type from sys.tables. Could be a user table, system table, user view, internal table
• schema_name – The name of the schema of the table that the column is in
• table_name – The name of the table
• column_name – The name of the column
• nullability – Whether that column is nullable, plus if the column is an identity column
• declared_datatype – The data type as declared. For alias types (like sysname), this will be the name the user specified.
• base_datatype – The data type as is implemented. For alias types this will be the base intrinsic type (like for sysname this will be nvarchar(128))
• primary_key_column – A bit value, indicating that the column participates in a primary key constraint
• column_id – The internal key of the column, which is used for ordering the columns
• default_value – If there is a default constraint on the column, this will be the text of the declaration
• column_check_constraint – If there is a check constraint that only deals with the one column, it will be considered a column check constraint. This will contain the text of the check constraint
• table_check_constraint_reference – A bit value that indicates that there is one or more table level check constraints that reference the column

The query text follows.  It is pretty unwieldy, so instead of the derived table you might want to consider building it into a view or even a procedure.  I keep a SSMS project with all of my metadata queries that I maintain over time to call in any place I need them, so I use use this one ad-hoc.  You can download the query from my website’s downloadable packages page here: http://www.drsql.org/Documents/ColumnMetadataQuery.sql

SELECT  *
FROM    ( SELECT    REPLACE(LOWER(objects.type_desc), '_', ' ') AS table_type, schemas.name AS schema_name, objects.name AS table_name,
                    columns.name AS column_name, CASE WHEN columns.is_identity = 1 THEN 'IDENTITY NOT NULL'
                                                      WHEN columns.is_nullable = 1 THEN 'NULL'
                                                      ELSE 'NOT NULL'
                                                 END AS nullability,
                   --types that have a ascii character or binary length
                    CASE WHEN columns.is_computed = 1 THEN 'Computed'
                         WHEN types.name IN ( 'varchar', 'char', 'varbinary' ) THEN types.name + CASE WHEN columns.max_length = -1 THEN '(max)'
                                                                                                      ELSE '(' + CAST(columns.max_length AS VARCHAR(4)) + ')'
                                                                                                 END
                
                         --types that have an unicode character type that requires length to be halved
                         WHEN types.name IN ( 'nvarchar', 'nchar' ) THEN types.name + CASE WHEN columns.max_length = -1 THEN '(max)'
                                                                                           ELSE '(' + CAST(columns.max_length / 2 AS VARCHAR(4)) + ')'
                                                                                      END

                          --types with a datetime precision
                         WHEN types.name IN ( 'time', 'datetime2', 'datetimeoffset' ) THEN types.name + '(' + CAST(columns.scale AS VARCHAR(4)) + ')'

                         --types with a precision/scale
                         WHEN types.name IN ( 'numeric', 'decimal' )
                         THEN types.name + '(' + CAST(columns.precision AS VARCHAR(4)) + ',' + CAST(columns.scale AS VARCHAR(4)) + ')'

                        --timestamp should be reported as rowversion
                         WHEN types.name = 'timestamp' THEN 'rowversion'
                         --and the rest. Note, float is declared with a bit length, but is
                         --represented as either float or real in types 
                         ELSE types.name
                    END AS declared_datatype,

                   --types that have a ascii character or binary length
                    CASE WHEN baseType.name IN ( 'varchar', 'char', 'varbinary' ) THEN baseType.name + CASE WHEN columns.max_length = -1 THEN '(max)'
                                                   ELSE '(' + CAST(columns.max_length AS VARCHAR(4)) + ')'
                                              END
               
                         --types that have an unicode character type that requires length to be halved
                         WHEN baseType.name IN ( 'nvarchar', 'nchar' ) THEN baseType.name + CASE WHEN columns.max_length = -1 THEN '(max)'
                                                                                                 ELSE '(' + CAST(columns.max_length / 2 AS VARCHAR(4)) + ')'
                                                                                            END

                         --types with a datetime precision
                         WHEN baseType.name IN ( 'time', 'datetime2', 'datetimeoffset' ) THEN baseType.name + '(' + CAST(columns.scale AS VARCHAR(4)) + ')'

                         --types with a precision/scale
                         WHEN baseType.name IN ( 'numeric', 'decimal' )
                         THEN baseType.name + '(' + CAST(columns.precision AS VARCHAR(4)) + ',' + CAST(columns.scale AS VARCHAR(4)) + ')'

                         --timestamp should be reported as rowversion
                         WHEN baseType.name = 'timestamp' THEN 'rowversion'
                         --and the rest. Note, float is declared with a bit length, but is
                         --represented as either float or real in types
                         ELSE baseType.name
                    END AS base_datatype, CASE WHEN EXISTS ( SELECT *
                                                             FROM   sys.key_constraints
                                                                    JOIN sys.indexes
                                                                        ON key_constraints.parent_object_id = indexes.object_id
                                                                           AND key_constraints.unique_index_id = indexes.index_id
                                                                    JOIN sys.index_columns
                                                                        ON index_columns.object_id = indexes.object_id
                                                                           AND index_columns.index_id = indexes.index_id
                                                             WHERE  key_constraints.type = 'PK'
                                                                    AND columns.column_id = index_columns.column_id
                                                                    AND columns.OBJECT_ID = index_columns.OBJECT_ID ) THEN 1
                                               ELSE 0
                                          END AS primary_key_column, columns.column_id, default_constraints.definition AS default_value,
                    check_constraints.definition AS column_check_constraint,
                    CASE WHEN EXISTS ( SELECT   *
                                       FROM     sys.check_constraints AS cc
                                       WHERE    cc.parent_object_id = columns.OBJECT_ID
                                                AND cc.definition LIKE '%~[' + columns.name + '~]%' ESCAPE '~'
                                                AND cc.parent_column_id = 0 ) THEN 1
                         ELSE 0
                    END AS table_check_constraint_reference
          FROM      sys.columns
                    JOIN sys.types
                        ON columns.user_type_id = types.user_type_id
                    JOIN sys.types AS baseType
                        ON columns.system_type_id = baseType.system_type_id
                           AND baseType.user_type_id = baseType.system_type_id
                    JOIN sys.objects
                            JOIN sys.schemas
                                   ON schemas.schema_id = objects.schema_id
                        ON objects.object_id = columns.OBJECT_ID
                    LEFT OUTER JOIN sys.default_constraints
                        ON default_constraints.parent_object_id = columns.object_id
                              AND default_constraints.parent_column_id = columns.column_id
                    LEFT OUTER JOIN sys.check_constraints
                        ON check_constraints.parent_object_id = columns.object_id
                             AND check_constraints.parent_column_id = columns.column_id ) AS rows
WHERE   table_type = 'user table'
              AND schema_name LIKE '%'
              AND table_name LIKE '%'
              AND column_name LIKE '%'
              AND nullability LIKE '%'
              AND base_datatype LIKE '%'
              AND declared_datatype LIKE '%'
ORDER BY table_type, schema_name, table_name, column_id

Finally… took longer than I had expected when I wrote this a while back, but I had to move my website and get DNS moved before I could post code…

When I write code, I do my best to test that code in as many ways as necessary. One of the last types of tests that is necessary is concurrency testing. Concurrency testing is one of the most difficult types of testing because it takes running multiple processes simultaneously and making sure that you get the correct answers multiple times. This is really difficult when you need to make > 1 statement that takes only a second or two simultaneously with another (or even 10 other) connections.  What I am trying to replicate is the WAITFOR statement with a DELAY, but multiple connections use the same delay.

As I am writing my sequences presentation for SQL Rally, I wanted to test how values were allocated to each row based on different caching levels. I was looking at the clock, creating WAITFOR TIME statements, and copying the values into multiple windows as I have done so many times before. I was also thinking that I should start blogging again, and (like my sequence presentation) do something code oriented rather than design for a bit (I just finished my design book, so I am going to rest on that subject for a bit, well, other than the SQL Rally precon that I am subliminally trying to get you to sign up for before prices go up again).

So envisioned a kind of multi-user WAITFOR statement that would say: Start all processes in 20 seconds, then start multiple connections. (As I write this, I am envisioning a kind of semaphore version of this that you could hold until ready to go, but more on that some other day.)  I initially used some 2012 features like FORMAT, but I wanted to make sure this was useful to a more wide audience.  The usage of the system is really simple, to have your connection wait for 20 seconds, you execute:

EXEC Utility.WaitForSync$StartWait @WaitSeconds=20

To have multiple connections start at the same second, you execute the same statement on a different connection.  The stored procedure calculates the time in 20 seconds, stores the value off and executes a WAITFOR TIME statement on each connection you have executed this statement on.  After the time passes and the connection continues, you have to reset the connection or you will receive the following message (not an error, just a PRINT statement, since you may want the batch to complete):

WaitForSync: An error occurred. Message:
Too late to start another WAITFOR session. Last session DELAY time was 2012-03-20 17:55:03. Use Utility.WaitForSync$reset to start new sessions

To reset the session, as the message says, just execute Utility.WaitForSync$reset. There are also procedures to view the time when the processes will continue. 

If you want to not do the action if the WAITFOR TIME statements have already completed before you start an operation, the procedure will return a –100.  So you might use something like the following TRY…CATCH Block to control execution:

BEGIN TRY
DECLARE @RETVAL int
EXEC @RETVAL = Utility.WaitForSync$StartWait @WaitSeconds=10
IF @RETVAL = -100
     RAISERROR ('Time Elapsed',16,1)

--Other code

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH

Which would return something like:

WaitForSync: An error occurred. Message:
Too late to start another WAITFOR session. Last session DELAY time was 2012-03-20 21:20:06. Use Utility.WaitForSync$reset to start new sessions

Time Elapsed

You can download the code from my website’s downloadable code page. 

Of course, the most interesting thing about downloading code is learning how to write your own code that uses the same patterns and techniques, so let me cover the highlights.  The table is pretty simple:

CREATE TABLE Utility.WaitForSync
(
    WaitforDelayValue nchar(8) NOT NULL,
    StartTime          datetime2(0) NOT NULL, --used to make sure you can do a waitfor that crosses a day boundry
    OnlyOneRow          tinyint PRIMARY KEY DEFAULT (1) CHECK (OnlyOneRow = 1) 
);

It only allows a single row using the PK Constraint, a default, and a check constraint. A bit of a trick, but definitely workable trick to ensure a given cardinality in a table.  The important two columns are one for the value that will be used in the WAITFOR statement, and the StartTime column is used to allow you to do your demos right around midnight (and yes, I put this in because when I was first using the procedure, it was taking a really long time to complete late one night.)

The idea is, the first user will put a row in here at a given time, then every caller after that (and before the StartTime) will use the same time.  After the StartTime, the process has already started.

The simplest procedure is the reset procedure.  It just deletes the row from the table (even if other users are still in progress, but this is just a utility for demos and testing).

CREATE PROCEDURE Utility.WaitForSync$Reset
AS
----------------------------------------------------------------------------------------------
-- Louis Davidson        drsql.org
--
-- Simple enough, just delete the row from the table
----------------------------------------------------------------------------------------------
    DELETE FROM Utility.WaitForSync
GO

The StartWait procedure is a bit more complicated that that..  It takes a parameter of @WaitSeconds that lets the first caller to set the time.  Usually 10 seconds works great for a few connections, but it depends on just how much you need to coordinate.  The code is commented, and is really pretty simple.  The basics are that it checks to see if a row exists and grabs the value, if it doesn’t, then it creates a new row. 

ALTER PROCEDURE Utility.WaitForSync$StartWait
(
    @WaitSeconds    int --minimum amount of time to wait. The WAITFOR statement
                            --always starts as minute changes..
)
AS
------------------------------------------------------------------------------------------------
---- Louis Davidson        drsql.org
----
---- Either starts a new wait for session or uses the existing one
------------------------------------------------------------------------------------------------
SET NOCOUNT ON
BEGIN TRY
    DECLARE @StartTime datetime2(0),
            @WaitforDelayValue NCHAR(8),
            @ProcStartTime datetime2(0) = SYSDATETIME();
    --Get the row from the table where we hold the sync value.
    SELECT  @StartTime = StartTime,
            @WaitforDelayValue = WaitforDelayValue
    FROM   Utility.WaitForSync;

    --if a value was not already stored, we are just starting
    IF @StartTime IS NULL
      BEGIN
            --set the startTime to the current time + the number of seconds in parame
              SET @StartTime = DATEADD(second,@waitSeconds,SYSDATETIME());

            --then I use a good old style convert with a format to get the time for the delay
            SET @WaitforDelayValue = CONVERT(nchar(8),@starttime, 108);

            --insert the value into the WaitForSyncing table. StartTime willbe used to make sure
            --the time is later, even if it crosses the day boundry
             INSERT INTO Utility.WaitForSync(WaitforDelayValue, StartTime)
            VALUES (@WaitforDelayValue,@StartTime);
      END
    --if the time has already passed, we raise an error to the client that you can't piggy back on the
    --existing session, reset   
    ELSE IF @StartTime <= SYSDATETIME()
      BEGIN
        DECLARE @msg nvarchar(1000)
        SET @msg = N'Too late to start another WAITFOR session. Last session DELAY time was '
                    + CAST(@startTime AS NVARCHAR(20)) + '.'
                   + N' Use Utility.WaitForSync$reset to start new sessions';
        RAISERROR (@msg,16,1);
      END
    --finally, we take the delay value we created and use it in an execute statement
    --note that SSMS won't how the SELECT until after the batch resumes.
    DECLARE @queryText NVARCHAR(100) = 'WAITFOR TIME ' + QUOTENAME(@WaitforDelayValue,'''');
   
    EXECUTE (@queryText);
    PRINT 'WaitForSync: Starting at: ' + CAST(@startTime AS NVARCHAR(20)) +
           ' Waited for: ' + CAST(DATEDIFF(SECOND,@procStartTime, SYSDATETIME()) AS VARCHAR(10)) + ' seconds.'
  END TRY
 
  BEGIN CATCH
    --benign output that won't stop anything. Simply keep going
    PRINT 'WaitForSync: An error occurred. Message: ' + CHAR(13) + CHAR(10)
            + ERROR_MESSAGE()
    RETURN -100 --The caller can use the return value to decide if they want to stop what they are doing
  END CATCH
GO

Finally, there is a viewing procedure that lets you see what the time that the procedure starts is.  Note that it has a 1 return value if there is no session started, and a –100 if an expired session is out there or there is an error.

I skipped last year making blog resolutions, but this year I need to get myself back on the straight and narrow and encourage myself to do a few things.

1. Finish my book quickly and efficiently – Well, duh, I supposed, but the quickly and efficiently is the biggest important part.  I have no idea when SQL Server 2012 will be released, but with RC0 having been publicly released, it isn’t going to be SQL Server 2013 now is it?

2. Blog regularly – Note I didn’t exactly say more, but I do want to be regular. Perhaps a substantive blog (more than telling people about some event) once a month here on SQL Blog while working on a book, and 2 or 3 times otherwise.  On simple-talk my goal is to average 2 What Counts For A DBA blog entries.

2.1 Blog about my other (computer) love occasionally – I love gadgets (I now carry 6 music/video players, including 1 Windows Phone 7, 4 Zunes, my new Android player, and a Nintendo 3DS) and software (user interfaces are my favorite pet peeve), and I occasionally I feel like talking about them.

3. Do 1 or 2 new presentations, and reduce doing the ones on database design. I love talking about database design, and I will still put my main db design session in for conferences, but I have 3 new ideas for sessions that are about 50% code and 50% design (for example, a session on triggers, which I want to show not only how they can be used, but why they should be used (or honestly not be used, which is a far larger list indeed).

4. Develop and present a DB design a variable length (1-3 day?) seminar.  I am pretty happy how the book is organized, based on how I forced myself to work on it this time. I had a bunch of ideas how I wanted to change things us and shorten the book, but during the process, I realized the natural progression of how it ought to be done. I feel like it translates well to a class, even a bit more than the pre-con I have written that I did for the Orlando SQL Saturday (and which I will be happy to do for any SQL Saturday for any size group (I don’t care so much about making money, as long as my expenses are covered after the room is paid for…though I wouldn’t mind making a buck :)

5. Get more involved with our local group. I attend, I help out, I did some work with trying to SQL Rally in Nashville, but I think I could do more to help out there. It helps that I work with Christine Leo, Kevin Kline, and Joe Webb, all super dynamic folks who kick butt.

In my personal life, I have to make a number of changes that are probably apparent to anyone who has met me or seen a picture of me… That will probably be the hardest of them all, though possibly the most important.

Wow, looking at the list I realize why I stopped making resolutions… Maybe I should just resolve to go to Disney World more? Well, that would be way too easy!

Well, we are finally here at what is the secular version of the holiday season for Microsoft SQL Server nerd types, the week of the SQLPASS Summit. This year, I am speaking 3 times and will also be doing the Quiz Bowl at the Welcome Reception, so I am going to be busy. If you are here and are interested in database design, please do stop by and check out my sessions.

Monday and Tuesday I will be in side sessions that are NDA for much of the day, and that is probably all that I can say. Tuesday night will be the Quiz Bowl where Tim Ford and I will attempt to one up ourselves and attempt to (along with some unwitting (but hopefully full of wit) experts) entertain you with our yearly Jeopardy-esque wanna be game show.

Wednesday, from 1-1:30, a bunch of the writers of the SQL Server MVP Deep Dives 2 book will be signing copies for you (there is a second signing at 7:15 - 8 AM on Friday, if you can stomach the earliness). If you want to see what is in the book, check http://www.manning.com/delaney/. The book will be on sale starting Tuesday afternoon for you if you want is signed or not. Note that all author proceeds of the book are going to charity (this edition’s proceeds going to http://www.operationsmile.org/), so you get a good book with lots of different subjects, and make a donation to a worthy cause. I am almost certain that you get an ebook edition of the book for free with the paper version that you can read on your portable device as well.

At 4:45 - 6 PM, I will be doing my "Characteristics of a Great Relational Database " session that was picked up as an alternate . This needs to be a very interactive session, and my hope is to learn a bit from your ideas as well as the slides I have prepared. The session is fairly light and a bit humorous, so if you are feeling particularly serious and sour, well, my Thursday session is far less fun:

Thursday afternoon, from 3-4:15 PM, I will be presenting "Database Design Fundamentals" which presents a more deep dive on the concepts that go into designing a relational database. My goal here is to present the basics of the process of creating a database from conception until you are ready to start typing CREATE TABLE statements.

Friday, I will be in a panel discussion called "Are you a Linchpin? Career management lessons to help you become indispensible.", representing the corporate developers in the world who like that they have one set of problems to solve that while the basis never varies, can never really be solved because the demand outstrips the realities of the day. Other members of the panel include Jeremiah Peschka, Stacia Misner, Kevin Kline, Brent Ozar, Thomas LaRock, Andy Warren, Andy Leonard

Saturday I go home...

You probably have noticed that I haven’t blogged all that much these days. Part of the reason has been taking on way too many projects/speaking engagements/writing projects etc. I am generally proud of all of these things, but the most project that I am probably the most proud of is the MVP Deep Dives 2 book.  The project was helmed by the one and only Kalen Delaney, whom I have always admired for many reasons. Working with her on the project has been awesome, even when she had to crack the whip on us pokey editors.

The other editors are a group of names that, let’s face it, if you have heard of me, you have heard of them and probably long before me (well, unless you are a family member of mine!) Greg Low who rules www.sqldownunder.com), Brad McGehee the smiling face of many Red Gate ads and blogger at http://www.bradmcgehee.com/, Paul Nielsen, writer of the SQL Server Bible series until 2008 and a good friend of mine who loves database design like I do and Paul Randal and Kimberly Tripp of SQLSkills fame. It is an honor to be even mentioned along with these names.

You see the table of contents and purchase the book here: http://www.manning.com/delaney/. Just like last time, the book starts out with a section on Architecture, edited by me. It is the smallest section, which is probably to be expected. The 6 chapters in Architecture focus on the softer topics, including constraints/uniqueness, storage, generalization of designs, and general characteristics of designs.  I hope you like it, but one thing that is excellent about a book like this is that there are 60 chapters with something for everyone.

If you are going to be at SQLPASS, there is scheduled to be a good number of books for purchase there, and we will have a mass signing again. I always feel funny about signing books, but the book signing was a lot of fun last time.  The charity for this book is Operation Smile (http://www.operationsmile.org/). All author and editor proceeds will go to them. Last time we raised well into a 5 digit sum for War Child, so it isn’t chicken feed at the least.

One last note, I should note that over the project I lost an author due to his time commitments and some difficultly with the size of the chapter. I feel bad that we couldn’t work it out and I hope that his material (that was really good in its original form, though far too large for this format, and when we cut it down it just couldn’t be saved. The terrible part of being an editor is occasionally having to make a hard decision. In any case, this person’s writing, reasoning, etc were all good and if I see it posted later I will link to it for you.

When I was editing my chapter on implementing a database, I noticed a really nice improvement in the error message I had from the previous edition of the book. Instead of just telling me that there was a value in my modification statement that duplicated an existing value (or multiple values affected by the statement), it told me the duplicated value.

To demo, I created the following quickie table in tempdb.

USE tempdb
GO

--drop the object if it initially existed
if object_id('test.testErrorMessage') IS NOT null
    DROP TABLE test.testErrorMessage
IF schema_id('test') IS NOT NULL
    DROP SCHEMA test
go

CREATE SCHEMA test
GO
CREATE TABLE test.testErrorMessage
(
    testErrorMessageId INT NOT NULL
        CONSTRAINT PKtestErrorMessage PRIMARY KEY,
    otherColumn varchar(10) NOT NULL
        CONSTRAINT AKtestErrorMessage UNIQUE (otherColumn),
)
GO
INSERT INTO test.testErrorMessage (testErrorMessageId, otherColumn)
VALUES (1,'First')
GO

Then, inserting a duplicate row for the primary key value:

INSERT INTO test.testErrorMessage (testErrorMessageId, otherColumn)
VALUES (1,'First')
GO

And on 2008 R2, I get:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'.

Now on Denali CTP3, you get a little bit more:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'. The duplicate key value is (1).

Then, to show the same thing for the UNIQUE CONSTRAINT:

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (2,'First')

On 2008 R2, you get the following

Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'.

And again on Denali CTP3:

Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'. The duplicate key value is (First).

You can see if you duplicate > 1 value, it gives you one of the items. It might be better if the message didn’t imply that it was the only duplicate value, but hey, it is a great improvement. If you think it ought to be tweaked to say “A duplicated key value is (…) or something, click here)

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (5,'Third'),(6,'Third'),(3,'Second'),(4,'Second')

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'. The duplicate key value is (Third).

It works with indexes also:

ALTER TABLE test.testErrorMessage
     DROP CONSTRAINT AKtestErrorMessage

CREATE UNIQUE INDEX UXtestErrorMessage ON test.testErrorMessage(otherColumn)

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (5,'Third'),(6,'Third'),(3,'Second'),(4,'Second')

You get the following:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'test.testErrorMessage' with unique index 'UXtestErrorMessage'. The duplicate key value is (Third).

Much nicer!

As the book progresses, I find myself veering from the original stated outline quite a bit, because as I teach about this more (and I am teaching a daylong db design class in August at http://www.sqlsolstice.com/… shameless plug, but it is on topic :) I start to find that a given order works better. Originally I had slated myself to talk more about modeling here for three chapters, then get back to the more implementation topics to finish out the book, but now I am going to keep plugging through the implementation tasks, then finish up with modeling task (which I hope I might end up getting some help with…emails are going out once I talk it over with my editor).

In the last edition, the chapter on data protection was more inclusive, including programmatic data protection, including client code and stored procedures. But, keeping with the basic, implementation type chapters (and trying my best to shorten chapters to more realistic chunks (the free chapter shouldn’t be 1/2 of the book, or so I am told), I will put that off to probably the final chapter.

This chapter was broken up into two main sections, Check Constraints and Triggers.  I will demonstrate the following scenarios, and if you see anything missing, please do make suggestions

Check Constraints

• Simple value checks – Like when you want to make sure there are no blank string values CHECK (len(value) > 0)
• Value reasonableness checks – Like if a value should be a non-negative integer, CHECK (value >= 0)
• Checks using different tables – Like setting up a data driven format check

Triggers – Broken down by AFTER and INSTEAD OF Triggers

• AFTER
• Range checks on multiple rows – Like when you want to make sure that the sum of rows related to (and including) the newly inserted rows is > 0
• Maintaining summary values (only as necessary) – Denormalization, pure and simple, but if you are going to do it, triggers are the way to go (you really shouldn’t)
• Cascading inserts – Like creating child rows to ensure a 1 to at least 1 row relationship is met, or creating a parent
• Child-to-parent cascades – Like deleting parent rows when the last child row is deleted
• Maintaining an audit trail – Also something that will come up in security, but implementing an audit trail of actions on a table. Less needed these days with auditing, but
• Relationships that span databases and servers – sometimes you just have to implement RI between databases, so it is back to 6.0 style RI
• INSTEAD OF
• Automatically maintaining values – For example, if you want to implement a bulletproof rowLastUpdatedTime column to know when the row last changed, but don’t trust the client (who does?)
• Formatting user input – Like formatting words in all caps, or proper case. Another thing that might be better done outside of SQL Server, but it is very straightforward to implement
• Redirecting invalid data to an exception table – For example, eliminating data that is outside of the norm. Possibly done better outside of SQL Server code, but if you really want to build something that takes previous data into consideration, this might be a reasonable way.
• Forcing no action to be performed on a table, even by someone who technically has proper rights – Simple do nothing trigger that works because in an instead of trigger you have to replicate the action, so you don’t.

It might seem weird to consider formatting data or redirecting invalid data to another table as data protection, but the point of data protection is to make sure that they data ends up in a reasonable state, and triggers can do some “magical” seeming stuff. Admittedly, triggers are not a fan favorite with many programmers because they do those magical stuff that they cannot directly control, but in many ways that is the point.  If the dev forgets to update the last update date, the ETL may not see the row, and oops your data is out of sync.

In any case, I do my best to make it clear that you don’t in fact have to do any of this, but here are the tools in the tool bag.