ALTER TABLE Messages.Topic
   ADD CONSTRAINT CHKTopic_Name_NotEmpty
       CHECK (LEN(RTRIM(Name)) > 0);
ALTER TABLE Messages.MessageTopic
   ADD CONSTRAINT CHKMessageTopic_UserDefinedTopicName_NotEmpty
       CHECK (LEN(RTRIM(UserDefinedTopicName)) > 0);

The other domain we specifically mentioned was for the UserHandle, as repeated in Table 6-9.

To implement this domain, things get a bit more interesting:

ALTER TABLE Attendees.MessagingUser
  ADD CONSTRAINT CHKMessagingUser_UserHandle_LengthAndStart
     CHECK (LEN(Rtrim(UserHandle)) >= 5
             AND LTRIM(UserHandle) LIKE ’[a-z]’ +
                            REPLICATE(’[a-z1-9]’,LEN(RTRIM(UserHandle)) -1));

The first part of the CHECK constraint Boolean expression simply checks to see if the string is greater than five characters long. The latter part creates a like expression that checks that the name starts with a letter and that the following characters are only alphanumeric. It looks like it might be slow, based on the way we are taught to write WHERE clause expressions, but in this case, you aren’t searching but are working on a single row already in memory.

Finally, we have one other predicate that we need to implement. Back in the requirements, it was specified that the MessageTopic table, we need to make sure that the UserDefinedTopicName is NULL unless the Topic that is chosen is the one set up for the UserDefined topic. So we will create a new row. Since the surrogate key of MessageTopic is a default constraint using a sequence, we can simply enter the row specifying the TopicId as 0:

INSERT INTO Messages.Topic(TopicId, Name, Description)
VALUES (0,’User Defined’,’User Enters Their Own User Defined Topic’);

Then, we add the constraint, checking to make sure that the UserDefinedTopicId is NULL if the TopicId = 0 and vice versa:

ALTER TABLE Messages.MessageTopic
  ADD CONSTRAINT CHKMessageTopic_UserDefinedTopicName_NullUnlessUserDefined
   CHECK ((UserDefinedTopicName is NULL and TopicId <> 0)
              or (TopicId = 0 and UserDefinedTopicName is NOT NULL));

Be sure to be as specific as possible with your predicate, as it will make implementation a lot safer. Now, we have implemented all of the CHECK constraints we are going to for our demonstration database. In the testing section later in this chapter, one of the most important things to test are the CHECK constraints (and if you have done any advanced data integrity work in triggers, which we will leave to later chapters).

Triggers to Maintain Automatic Values

For all of our tables, we included two columns that we are going to implement as automatically maintained columns. These columns are the RowCreateTime and RowLastUpdateTime columns that we added earlier in this chapter (shown in Figure 6-27). These columns are useful to help us get an idea of some of the actions that have occurred on our row without resorting to looking through change tracking. Automatically generated values are not limited to implementation columns like these Row% prefixed columns, but most often, we are implementing them strictly for software’s sake, hence the reason that we will implement them in such a manner that the client cannot modify the values. A big difference between implementation columns and user-facing columns is that we will code our triggers to ensure that the data in the column cannot be overridden without disabling the trigger.

I will do this with an “instead of” trigger, which will, for the most part, be a very smooth way to manage automatic operations on the base table, but it does have a few downsides:

• SCOPE_IDENTITY() will no longer return the identity value that is set in the row, since the actual insert will be done in the trigger, outside of the scope of the code. @@IDENTITY will work, but it has its own issues, particularly with triggers that perform cascading operations.
• An OUTPUT clause on a modification DML statement will not work if you have triggers on the table.

The SCOPE_IDENTITY() issue can be gotten around by using an AFTER trigger for an insert (which I will include as a sample in this section). I personally suggest that you consider using a SEQUENCE-based key, or use one of the natural keys you have implemented to get the inserted value if you are inserting a single row. I only made one table use a SEQUENCE because so many tables will use an IDENTITY column for surrogate keys because they are easy to implement.

One of the downsides of triggers can be performance, so sometimes, automatically generated values will simply be maintained by the SQL code that uses the tables, or perhaps the columns are simply removed. I far prefer a server-based solution, because clock synchronization can be an issue when even two distinct servers are involved with keeping time. So if an action says it occurred at 12:00 AM by the table, you look in the log and at 12:00 AM, everything looks fine, but at 11:50 PM there was a glitch of some sort. Are they related? It is not possible to know to the degree you might desire.

As it is my favored mechanism for maintaining automatically maintained columns, I will implement triggers for tables, other than Attendees.AttendeeType, because, you should recall, we will not enable end users to make changes to the data, so tracking changes will not be needed.

To build the triggers, I will use the trigger templates that are included in Appendix B as the basis for the trigger. If you want to know more about the basics of triggers and how these templates are constructed, check Appendix B. The basics of how the triggers work should be very self explanatory. The code added to the base trigger template from the appendix will be highlighted in bold.

In the following “instead of” insert trigger, we will replicate the operation of INSERT on the table, passing through the values from the user insert operation, but replacing the RowCreateTime and RowLastUpdateTime with the function SYSDATETIME(). One quick topic we should briefly mention about triggers is multirow operations. Well-written triggers take into consideration that any INSERT, UPDATE, or DELETE statement may affect multiple rows in one execution. The inserted and deleted virtual tables house the rows that have been inserted or deleted in the operation. (For an update, think of rows as being deleted and then inserted, at least logically.)

CREATE TRIGGER MessageTopic$InsteadOfInsertTrigger
ON Messages.MessageTopic
INSTEAD OF INSERT AS
BEGIN
   DECLARE @msg varchar(2000),    --used to hold the error message
   --use inserted for insert or update trigger, deleted for update or delete trigger
   --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number
   --that is equal to number of merged rows, not rows being checked in trigger
           @rowsAffected int = (select count(*) from inserted)
           @rowsAffected = (select count(*) from deleted)
   --no need to continue on if no rows affected
   IF @rowsAffected = 0 RETURN;
   SET NOCOUNT ON; --to avoid the rowcount messages
   SET ROWCOUNT 0; --in case the client has modified the rowcount
   BEGIN TRY
          --[validation section]
          --[modification section]
          --<perform action>
          INSERT INTO Messages.MessageTopic (MessageId, UserDefinedTopicName,
                                            TopicId,RowCreateTime,RowLastUpdateTime)
          SELECT MessageId, UserDefinedTopicName, TopicId, SYSDATETIME(), SYSDATETIME()
          FROM   inserted ;
   END TRY
   BEGIN CATCH
      IF @@trancount > 0
          ROLLBACK TRANSACTION;
      THROW; --will halt the batch or be caught by the caller’s catch block
  END CATCH
END

For the UPDATE operation, we will do very much the same thing, only when we replicate the UPDATE operation, we will make sure that the RowCreateTime stays the same, no matter what the user might send in the update, and the RowLastUpdateTime will be replaced by GETDATE():

CREATE TRIGGER Messages.MessageTopic$InsteadOfUpdateTrigger
ON Messages.MessageTopic
INSTEAD OF UPDATE AS
BEGIN
   DECLARE @msg varchar(2000),    --used to hold the error message
   --use inserted for insert or update trigger, deleted for update or delete trigger
   --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number
   --that is equal to number of merged rows, not rows being checked in trigger
           @rowsAffected int = (select count(*) from inserted)
   --@rowsAffected = (select count(*) from deleted)
   --no need to continue on if no rows affected
   IF @rowsAffected = 0 RETURN;
   SET NOCOUNT ON; --to avoid the rowcount messages
   SET ROWCOUNT 0; --in case the client has modified the rowcount
   BEGIN TRY
          --[validation section]
          --[modification section]
          --<perform action>
         UPDATE MessageTopic
          SET   MessageId = Inserted.MessageId,
                UserDefinedTopicName = Inserted.UserDefinedTopicName,
                TopicId = Inserted.TopicId,
                RowCreateTime = MessageTopic.RowCreateTime, --no changes allowed
                RowLastUpdateTime = SYSDATETIME()
          FROM  inserted
                   JOIN Messages.MessageTopic
                        ON inserted.MessageTopicId = MessageTopic.MessageTopicId;
   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION;
      THROW; --will halt the batch or be caught by the caller’s catch block
  END CATCH;
END;

If you find that using an “instead of” insert trigger is too invasive of a technique, particularly due to the loss of SCOPE_IDENTITY(), you can change to using an after trigger. For an after trigger, you only need to update the columns that are important. It is a bit slower because it is updating the row after it is in the table, but it does work quite well. Another reason why an “instead of” trigger may not be allowed is if you have a cascade operation. For example, consider our relationship fromMessgingUser to AttendeeType:

ALTER TABLE Attendees.MessagingUser  
    ADD  CONSTRAINT FKMessagingUser$IsSent$Messages_Message
            FOREIGN KEY(AttendeeType)
            REFERENCES Attendees.AttendeeType (AttendeeType)
ON UPDATE CASCADE;

Since this is a cascade, we will have to use an after trigger for the UPDATE trigger, since when the cascade occurs in the base table, the automatic operation won’t use the trigger, but only the base table operations. So we will implement the update trigger as

CREATE TRIGGER MessageTopic$UpdateRowControlsTrigger
ON Messages.MessageTopic
AFTER UPDATE AS
BEGIN
   DECLARE @msg varchar(2000),    --used to hold the error message
   --use inserted for insert or update trigger, deleted for update or delete trigger
   --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number
   --that is equal to number of merged rows, not rows being checked in trigger
           @rowsAffected int = (select count(*) from inserted)
           @rowsAffected = (select count(*) from deleted)
   --no need to continue on if no rows affected
   IF @rowsAffected = 0 RETURN;
   SET NOCOUNT ON; --to avoid the rowcount messages
   SET ROWCOUNT 0; --in case the client has modified the rowcount
   BEGIN TRY
          --[validation section]
          --[modification section]
          UPDATE MessageTopic
          SET    RowCreateTime = SYSDATETIME(),
                 RowLastUpdateTime = SYSDATETIME()
          FROM   inserted
                    JOIN Messages.MessageTopic
                        on inserted.MessageTopicId = MessageTopic.MessageTopicId;
   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION;
      THROW; --will halt the batch or be caught by the caller’s catch block
  END CATCH;
END;

In the downloads for this chapter, I will include triggers for all of the tables in our database. They will follow the same basic pattern, and because of this, I will almost always use some form of code generation tool to create these triggers. We discussed code generation earlier for building default constraints for these same columns, and you could do the very same thing for building triggers. I generally use a third-party tool to do code generation, but it is essential to the learning process that you code the first ones yourself so you know how things work.

Documenting Your Database

In your modeling process, you’ve created descriptions, notes, and various pieces of data that will be extremely useful in helping the developer understand the whys and wherefores of using the tables you’ve created. A great way to share this data is using extended properties that allow you to store specific information about objects. It allows you to extend the metadata of your tables in ways that can be used by your applications using simple SQL statements.

By creating these properties, you can build a repository of information that the application developers can use to do the following:

• Understand what the data in the columns is used for
• Store information to use in applications, such as the following:
  • Captions to show on a form when a column is displayed
  • Error messages to display when a constraint is violated
  • Formatting rules for displaying or entering data
  • Domain information, like the domain you have chosen for the column during design

To maintain extended properties, you’re given the following functions and stored procedures:

• sys.sp_addextendedproperty: Used to add a new extended property
• sys.sp_dropextendedproperty: Used to delete an existing extended property
• sys.sp_updateextendedproperty: Used to modify an existing extended property
• fn_listextendedproperty: A system-defined function that can be used to list extended properties
• sys.extendedproperties: Can be used to list all extended properties in a database; less friendly than fn_listextendedproperty

Each (other than sys.extendedproperties) has the following parameters:

• @name: The name of the user-defined property.
• @value: What to set the value to when creating or modifying a property. The datatype is sql_variant, with a maximum length limitation of 7500.
• @level0type: Top-level object type, often schema, especially for most objects that users will use (tables, procedures, and so on).
• @level0name: The name of the object of the type that’s identified in the @level0type parameter.
• @level1type: The name of the type of object such as Table, View, and so on.
• @level1name: The name of the object of the type that’s identified in the @level1type parameter.
• @level2type: The name of the type of object that’s on the level 2 branch of the tree under the value in the @level1Type value. For example, if @level1type is Table, then @level2type might be Column, Index, Constraint, or Trigger.
• @level2name: The name of the object of the type that’s identified in the @level2type parameter.

For our example, let’s use the Messages.Topic table, which was defined by the following DDL:

CREATE TABLE Messages.Topic (
        TopicId int NOT NULL CONSTRAINT DFLTMessage_Topic_TopicId
                                DEFAULT(NEXT VALUE FOR  dbo.TopicIdGenerator),
        Name                 nvarchar(30)  NOT NULL ,
        Description          varchar(60)  NOT NULL ,
        RowCreateTime        datetime2(0)  NULL ,
        RowLastUpdateTime    datetime2(0)  NULL
);

For simplicity sake, I will just be adding a property with a description of the table, but you can add whatever bits of information you may want to enhance the schema, both in usage and for management tasks. For example, you might add an extended property to tell the reindexing schemes when or how to reindex a table’s indexes. To document this table, let’s add a property to the table and columns named Description. You execute the following script after creating the table (note that I used the descriptions as outlined in the start of the chapter for the objects):

--Messages schema
EXEC sp_addextendedproperty @name = ’Description’,
   @value = ’Messaging objects’,
   @level0type = ’Schema’, @level0name = ’Messages’;
--Messages.Topic table
EXEC sp_addextendedproperty @name = ’Description’,
   @value = ’ Pre-defined topics for messages’,
   @level0type = ’Schema’, @level0name = ’Messages’,
   @level1type = ’Table’, @level1name = ’Topic’;
--Messages.Topic.TopicId
EXEC sp_addextendedproperty @name = ’Description’,
   @value = ’Surrogate key representing a Topic’,
   @level0type = ’Schema’, @level0name = ’Messages’,
   @level1type = ’Table’, @level1name = ’Topic’,
   @level2type = ’Column’, @level2name = ’TopicId’;
--Messages.Topic.Name
EXEC sp_addextendedproperty @name = ’Description’,
   @value = ’The name of the topic’,
   @level0type = ’Schema’, @level0name = ’Messages’,
   @level1type = ’Table’, @level1name = ’Topic’,
   @level2type = ’Column’, @level2name = ’Name’;
--Messages.Topic.Description
EXEC sp_addextendedproperty @name = ’Description’,
   @value = ’Description of the purpose and utilization of the topics’,
   @level0type = ’Schema’, @level0name = ’Messages’,
   @level1type = ’Table’, @level1name = ’Topic’,
   @level2type = ’Column’, @level2name = ’Description’;
--Messages.Topic.RowCreateTime
EXEC sp_addextendedproperty @name = ’Description’,
   @value = ’Time when the row was created’,
   @level0type = ’Schema’, @level0name = ’Messages’,
   @level1type = ’Table’, @level1name = ’Topic’,
   @level2type = ’Column’, @level2name = ’RowCreateTime’;
--Messages.Topic.RowLastUpdateTime
EXEC sp_addextendedproperty @name = ’Description’,
   @value = ’Time when the row was last updated’,
   @level0type = ’Schema’, @level0name = ’Messages’,
   @level1type = ’Table’, @level1name = ’Topic’,
   @level2type = ’Column’, @level2name = ’RowLastUpdateTime’;

Now, when you go into Management Studio, right-click the Messages.Topic table, and select Properties. Choose Extended Properties, and you see your description, as shown in Figure 6-32.

The fn_listExtendedProperty object is a system-defined function you can use to fetch the extended properties (the parameters are as discussed earlier—the name of the property and then each level of the hierarchy):

SELECT objname, value
FROM   fn_listExtendedProperty ( ’Description’,
                                 ’Schema’,’Messages’,
                                 ’Table’,’Topic’,
                                 ’Column’,null);

This code returns the following results:

objname              value
-------------------- ---------------------------------------------------------
TopicId              Surrogate key representing a Topic                       
Name                 The name of the topic                                    
Description          Description of the purpose and utilization of the topics
RowCreateTime        Time when the row was created                            
RowLastUpdateTime    Time when the row was last updated                       

There’s some pretty cool value in using extended properties, and not just for documentation. Because the property value is a sql_variant, you can put just about anything in there (within the 7,500-byte limitation). A possible use could be to store data entry masks and other information that the client could read in once and use to make the client experience richer. In the code download, I have included descriptions for all of the columns in the database.

You aren’t limited to tables, columns, and schemas either. Constraints, databases, and many other objects in the database can have extended properties.

Viewing the Basic System Metadata

In the process of creating a model, knowing where to look in the system metadata for descriptive information about the model is extremely useful. Futzing around in the UI will give you a headache and is certainly not the easiest way to see all of the objects at once, particularly to make sure everything seems to make sense.

There is a plethora of sys schema objects. However, they can be a bit messier to use and aren’t based on a standard that is generally applied to SQL Server, Oracle, etc., so they’re apt to change in future versions of SQL Server, just as these views replaced the system tables from versions of SQL Server before 2005. Of course, with the changes in 2005, it became a lot easier to use the sys schema objects (commonly referred to as the system catalog) to get metadata as well.

First, let’s get a list of the schemas in our database. To view these, use the INFORMATION_SCHEMA.SCHEMATA view:

SELECT SCHEMA_NAME, SCHEMA_OWNER
FROM   INFORMATION_SCHEMA.SCHEMATA
WHERE  SCHEMA_NAME <> SCHEMA_OWNER;

Note that I limit the schemas to the ones that don’t match their owners. SQL Server tools automatically creates a schema for every user that gets created.

SCHEMA_NAME     SCHEMA_OWNER
--------------- ----------------
Messages        dbo
Attendees       dbo

For tables and columns, we can use INFORMATION SCHEMA.COLUMNS, and with a little massaging, you can see the table, the column name, and the datatype in a format that is easy to use:

SELECT table_schema + ’.’ + TABLE_NAME as TABLE_NAME, COLUMN_NAME,
             --types that have a character or binary length
        case WHEN DATA_TYPE IN (’varchar’,’char’,’nvarchar’,’nchar’,’varbinary’)
                      then DATA_TYPE + CASE WHEN character_maximum_length = -1 then ’(max)’
                                            ELSE ’(’ + CAST(character_maximum_length as
                                                                    varchar(4)) + ’)’ end
                 --types with a datetime precision
                 WHEN DATA_TYPE IN (’time’,’datetime2’,’datetimeoffset’)
                      THEN DATA_TYPE + ’(’ + CAST(DATETIME_PRECISION as varchar(4)) + ’)’
                --types with a precision/scale
                 WHEN DATA_TYPE IN (’numeric’,’decimal’)
                      THEN DATA_TYPE + ’(’ + CAST(NUMERIC_PRECISION as varchar(4)) + ’,’ +
                                            CAST(NUMERIC_SCALE as varchar(4)) +  ’)’
                 --timestamp should be reported as rowversion
                 WHEN DATA_TYPE = ’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 DATA_TYPE END AS DECLARED_DATA_TYPE,
        COLUMN_DEFAULT
FROM   INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME,ORDINAL_POSITION;

In the database that we have been working on throughout the chapter, the preceding returns

TABLE_NAME                  DECLARED_DATA_TYPE  COLUMN_DEFAULT  
--------------------------- ------------------- ----------------
Attendees.AttendeeType      varchar(20)         NULL
Attendees.AttendeeType      varchar(60)         NULL
Attendees.MessagingUser     int                 NULL
Attendees.MessagingUser     varchar(20)         NULL
Attendees.MessagingUser     char(10)            NULL
Attendees.MessagingUser     char(8)             NULL
Attendees.MessagingUser     varchar(50)         NULL
Attendees.MessagingUser     varchar(50)         NULL
Attendees.MessagingUser     varchar(20)         NULL
Attendees.MessagingUser     bit                 ((0))
Attendees.MessagingUser     datetime2(0)        (sysdatetime())
Attendees.MessagingUser     datetime2(0)        (sysdatetime())
Attendees.UserConnection    int                 NULL
Attendees.UserConnection    int                 NULL
Attendees.UserConnection    int                 NULL
Attendees.UserConnection    datetime2(0)        (sysdatetime())
Attendees.UserConnection    datetime2(0)        (sysdatetime())
Messages.Message            int                 NULL
Messages.Message            datetime2(0)        NULL
Messages.Message            int                 NULL
Messages.Message            int                 NULL
Messages.Message            nvarchar(200)       NULL
Messages.Message            datetime2(0)        NULL
Messages.Message            datetime2(0)        (sysdatetime())
Messages.Message            datetime2(0)        (sysdatetime())
Messages.MessageTopic       int                 NULL
Messages.MessageTopic       int                 NULL
Messages.MessageTopic       nvarchar(30)        NULL
Messages.MessageTopic       int                 NULL
Messages.MessageTopic       datetime2(0)        (sysdatetime())
Messages.MessageTopic       datetime2(0)        (getdate())
Messages.Topic              int                 (NEXT VALUE FOR...
Messages.Topic              nvarchar(30)        NULL
Messages.Topic              varchar(60)         NULL
Messages.Topic              datetime2(0)        (sysdatetime())
Messages.Topic              datetime2(0)        (sysdatetime())

To see the constraints we have added to these objects (other than defaults, which were included in the previous results), use this code:

SELECT TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  CONSTRAINT_SCHEMA IN (’Attendees’,’Messages’)
ORDER  BY  CONSTRAINT_SCHEMA, TABLE_NAME;

This returns the following results (with the CONSTRAINT_NAME column truncated for some of the results to fit the data in):

TABLE_SCHEMA   TABLE_NAME       CONSTRAINT_NAME       CONSTRAINT_TYPE
-------------- ---------------- --------------------- ---------------
Attendees      AttendeeType     PKAttendeeType        PRIMARY KEY
Attendees      MessagingUser    PKMessagingUser       PRIMARY KEY
Attendees      MessagingUser    AKMessagingUser_Us... UNIQUE
Attendees      MessagingUser    AKMessagingUser_At... UNIQUE
Attendees      MessagingUser    CHKMessagingUser_U... CHECK
Attendees      MessagingUser    FKMessagingUser$Is... FOREIGN KEY
Attendees      UserConnection   FKMessagingUser$Co... FOREIGN KEY
Attendees      UserConnection   FKMessagingUser$Is... FOREIGN KEY
Attendees      UserConnection   AKUserConnection_U... UNIQUE
Attendees      UserConnection   PKUserConnection      PRIMARY KEY
Messages       Message          PKMessage             PRIMARY KEY
Messages       Message          AKMessage_TimeUser... UNIQUE
Messages       MessageTopic     AKMessageTopic_Top... UNIQUE
Messages       MessageTopic     FKTopic$Categorize... FOREIGN KEY
Messages       MessageTopic     FKMessage$isCatego... FOREIGN KEY
Messages       MessageTopic     PKMessageTopic        PRIMARY KEY
Messages       MessageTopic     CHKMessageTopic_Us... CHECK
Messages       MessageTopic     CHKMessageTopic_Us... CHECK
Messages       Topic            CHKTopic_Name_NotE... CHECK
Messages       Topic            PKTopic               PRIMARY KEY
Messages       Topic            AKTopic_Name          UNIQUE

Doing this will minimally help you get an idea of what tables you have created and if you have followed your naming standards. Finally, the following query will give you the list of triggers that have been created:

SELECT OBJECT_SCHEMA_NAME(parent_id) + ’.’ + OBJECT_NAME(parent_id) AS TABLE_NAME,
           name AS TRIGGER_NAME,
           CASE WHEN is_instead_of_trigger = 1 THEN ’INSTEAD OF’ ELSE ’AFTER’ END
                        AS TRIGGER_FIRE_TYPE
FROM   sys.triggers
WHERE  type_desc = ’SQL_TRIGGER’ --not a clr trigger
    AND  parent_class_desc = ’OBJECT_OR_COLUMN’ --DML trigger on a table or view
ORDER BY TABLE_NAME, TRIGGER_NAME;

In the text of the chapter, we created three triggers. In the downloads, I have finished the other seven triggers needed to implement the database. The following results include all ten triggers that are included in the downloads:

TABLE_NAME                TRIGGER_NAME                            TRIGGER_FIRE_TYPE
------------------------- --------------------------------------- -----------------
Attendees.MessagingUser   MessagingUser$InsteadOfInsertTrigger    INSTEAD OF
Attendees.MessagingUser   MessagingUser$UpdateRowControlsTrigger  AFTER
Attendees.UserConnection  UserConnection$InsteadOfInsertTrigger   INSTEAD OF
Attendees.UserConnection  UserConnection$InsteadOfUpdateTrigger   INSTEAD OF
Messages.Message          Message$InsteadOfInsertTrigger          INSTEAD OF
Messages.Message          Message$InsteadOfUpdateTrigger          INSTEAD OF
Messages.MessageTopic     MessageTopic$InsteadOfInsertTrigger     INSTEAD OF
Messages.MessageTopic     MessageTopic$InsteadOfUpdateTrigger     INSTEAD OF
Messages.Topic            Topic$InsteadOfInsertTrigger            INSTEAD OF
Messages.Topic            Topic$InsteadOfUpdateTrigger            INSTEAD OF

Finally, if you need to see the CHECK constraints in the database, you can use the following:

SELECT  TABLE_SCHEMA + ’.’ + TABLE_NAME AS TABLE_NAME,
        TABLE_CONSTRAINTS.CONSTRAINT_NAME, CHECK_CLAUSE
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS
               ON TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA =
                                CHECK_CONSTRAINTS.CONSTRAINT_SCHEMA
                  AND TABLE_CONSTRAINTS.CONSTRAINT_NAME = CHECK_CONSTRAINTS.CONSTRAINT_NAME

This will return

TABLE_NAME                CONSTRAINT_NAME                        CHECK_CLAUSE
------------------------- -------------------------------------- -----------------------
Messages.MessageTopic     CHKMessageTopic_UserDefinedTopicNa...  (len(rtrim([UserDefined...
Messages.MessageTopic     CHKMessageTopic_UserDefinedTopicNa...  ([UserDefinedTopicName]...
Attendees.MessagingUser   CHKMessagingUser_UserHandle_Length...  (len(rtrim([UserHandle]...
Messages.Topic            CHKTopic_Name_NotEmpty                  (len(rtrim([Name]))>(0))

This is just a taste of the metadata available, and we will make use of the information schema and other catalog views throughout this book, rather than give you any screenshots of SSMS or Data Tools.

Unit Testing Your Structures

Coding tables is a lot of fun, but it is by no means the end of the process. Now that you have structures created, you need to create test scripts to insert good and bad data into your tables to make sure that what you expect to occur does. There are some automated tools that are set up to help you with this task (a cursory scan of SourceForge will show at least five tools that you can use, at a minimum).

Many people have different ideas of testing, particularly trying to treat a database like it is a normal coded object and set up a state for the objects (create or read in some data), try their code, often a stored procedure, then delete the data. This technique works when you are trying to test a single module of code, but it is pretty tedious when you want to test the entire database and you have to load 20 tables to test one procedure, one constraint, etc.

In this section, I will give you a simplistic version of testing your database structures that you can easily do for free. In it, I will use a single script that will run and basically insert data into your entire database.

So far in this chapter, we have created a script to create a completely empty database. This is the database that we will use for our testing. Performance is of no concern, nor is concurrency. For this pass of testing, we want to make sure that the database will save and deal with data and will catch data outside of the reasonable norm. I say “reasonable” because unless we have a real reason to do so, we won’t be testing minimum and maximum values for a datatype, since we will trust that SQL Server can handle minimum and maximum values. We will also assume that foreign key constraints work to validate inserts and updates and will not take time seeing what happens when we violate a constraint with an invalid key value. We will check deletes to make sure that cascading operations work where they need to and not where they do not. We will test any check constraints we have built because these are apt to be an issue, and we will check to make sure that the triggers we have created work as well.

In the text, I will include an abridged version of the test script that you can get with the downloads. We will work with two types of scenarios. Those we expect to work, and those we expect to fail. For the scenarios we expect to succeed, we will check the row count after the statement and see if it is what we expect. If an error is raised, that will be self-explanatory. The tests follow the pattern:

<statement to test>
if @@ROWCOUNT <> N THROW 50000,’Description of Operation Failed’,16;

For statements that we expect to fail, we will use a statement that uses a TRY . . . CATCH block to capture the error. If no error occurs, the THROW statement will force an error to occur. Then in the CATCH block, we check to make sure the error message references the constraint we are testing.

BEGIN TRY
        <statement to test>
        THROW 50000,’No error raised’,1;
END TRY
BEGIN CATCH
        if ERROR_MESSAGE() NOT LIKE ’%<constraint being violated>%’
                THROW 50000,’<Description of Operation> Failed’,16;
END CATCH

The preceding example is a very minimalist method to test your structures, but even this will take quite a while to build, even for a smallish database. As the number of tables climbs, the complexity rises exponentially because of the likely make inter-table relationships that have to be violated. The goal is to build a test script that loads up a complete database full of data and tests failures along the way (using our technique to quash errors that are expected) and end up with a full database.

The first step is to include DELETE statements to clear out all of the data in the database, except for any data that is part of the base load. The goal here is to make your test script repeatable so you can run your script over and over, particularly if you get an error that you don’t expect and you have to go fix your structures.

SET NOCOUNT ON;
USE ConferenceMessaging;
GO
DELETE FROM Messages.MessageTopic ;
DELETE FROM Messages.Message;
DELETE FROM Messages.Topic WHERE TopicId <> 0; --Leave the User Defined Topic
DELETE FROM Attendees.UserConnection;
DELETE FROM Attendees.MessagingUser;

By deleting the data in the table, you will reset the data, but you won’t reset the identity values and the sequence objects. This will help you to make sure that you aren’t relying on certain identity values to test with. Next, I will add a legal user to the MessagingUser table:

INSERT INTO [Attendees].[MessagingUser]
           ([UserHandle],[AccessKeyValue],[AttendeeNumber]
           ,[FirstName],[LastName],[AttendeeType]
           ,[DisabledFlag])
VALUES (’FredF’,’0000000000’,’00000000’,’Fred’,’Flintstone’,’Regular’,0);
IF @@ROWCOUNT <> 1 THROW 50000,’Attendees.MessagingUser Single Row  Failed’,16;

Next, I will test entering data that fails one of the check constraints. In the next statement, I will enter data with a user handle that is too small:

BEGIN TRY --Check UserHandle Check Constraint
        INSERT INTO [Attendees].[MessagingUser]
                           ([UserHandle],[AccessKeyValue],[AttendeeNumber]
                           ,[FirstName],[LastName],[AttendeeType]
                           ,[DisabledFlag])
        VALUES (’Wil’,’0000000000’,’00000001’,’Wilma’,’Flintstone’,’Regular’,0);
        THROW 50000,’No error raised’,1;
END TRY
BEGIN CATCH
        IF ERROR_MESSAGE() NOT LIKE
                              ’%CHKMessagingUser_UserHandle_LengthAndStart%’
                THROW 50000,’Check Messages.Topic.Name didn’’t work’,1;
END CATCH;

When you execute this batch, you won’t get an error if the constraint you expect to fail is mentioned in the error message (and it will be if you have built the same database I have). Then, I will enter another row that fails the check constraint due to use of a nonalphanumeric character in the handle:

BEGIN TRY --Check UserHandle Check Constraint
        INSERT INTO [Attendees].[MessagingUser]
                           ([UserHandle],[AccessKeyValue],[AttendeeNumber]
                           ,[FirstName],[LastName],[AttendeeType]
                           ,[DisabledFlag])
        VALUES (’Wilma@’,’0000000000’,’00000001’,’Wilma’,’Flintstone’,’Regular’,0);
        THROW 50000,’No error raised’,1;
END TRY
BEGIN CATCH
        IF ERROR_MESSAGE() NOT LIKE
                         ’%CHKMessagingUser_UserHandle_LengthAndStart%’
                THROW 50000,’Check Messages.Topic.Name didn’’t work’,16;
END CATCH;

Skipping some of the simpler test items, we now arrive at a test of the unique constraint we set up based on the RoundedMessageTime that rounds the MessageTime to the hour. (Some of the data to support these tests are included in the sample code.) To test this, I will enter a row into the table and then immediately enter another at exactly the same time. If you happen to run this on a slow machine right at the turn of the hour, although it is extremely unlikely, the two statements execute in the same second (probably even the same millisecond).

INSERT INTO [Messages].[Message]
           ([MessagingUserId]
                   ,[SentToMessagingUserId]
           ,[Text]
           ,[MessageTime])
     VALUES
        ((SELECT MessagingUserId FROM Attendees.MessagingUser WHERE UserHandle = ’FredF’)
        ,(SELECT MessagingUserId FROM Attendees.MessagingUser WHERE UserHandle = ’WilmaF’)
        ,’It looks like I will be late tonight’
         ,SYSDATETIME());
IF @@ROWCOUNT <> 1 THROW 50000,’Messages.Messages Single Insert Failed’,16;
GO

Then, this statement will cause an error that should be caught in the CATCH block:

BEGIN TRY --Unique Message Error...
        INSERT INTO [Messages].[Message]
                           ([MessagingUserId]
                           ,[SentToMessagingUserId]
                           ,[Text]
                   ,[MessageTime])
                 VALUES
                           --Row1
                           ((SELECT MessagingUserId FROM Attendees.MessagingUser
                             WHERE UserHandle = ’FredF’)
                           ,(SELECT MessagingUserId FROM Attendees.MessagingUser
                             WHERE UserHandle = ’WilmaF’)  --
                           ,’It looks like I will be late tonight’
                           ,SYSDATETIME()),
                           --Row2
                           ((SELECT MessagingUserId FROM Attendees.MessagingUser
                             WHERE UserHandle = ’FredF’)
                           ,(SELECT MessagingUserId FROM Attendees.MessagingUser
                             WHERE UserHandle = ’WilmaF’)  --
                           ,’It looks like I will be late tonight’
                           ,SYSDATETIME());
        THROW 50000,’No error raised’,1;
END TRY
BEGIN CATCH
        IF ERROR_MESSAGE() NOT LIKE ’%AKMessage_TimeUserAndText%’
                 THROW 50000,’Unique Message Error didn’’t work (check times)’,1;
END CATCH;

If the error occurs, it is trapped and we know the constraint is working. If no error occurs, then the no error THROW will. Finally, I will show in the text the most complicated error checking block we have to deal with for this database. This is the message, and the message Topic. In the download, I insert the two successful cases, first for a specific topic, then with a user-defined topic. In the next block, I will show the failure case.

--Do this in a more natural way. Usually the client would pass in these values
DECLARE @messagingUserId int, @text nvarchar(200),
        @messageTime datetime2, @RoundedMessageTime datetime2(0);
SELECT @messagingUserId = (SELECT MessagingUserId FROM Attendees.MessagingUser
                           WHERE UserHandle = ’FredF’),
       @text = ’Oops Why Did I say That?’, @messageTime = SYSDATETIME();
--uses the same algorithm as the check constraint to calculate part of the key
SELECT @RoundedMessageTime = (
DATEADD(HOUR,DATEPART(HOUR,@MessageTime),CONVERT(datetime2(0),CONVERT(date,@MessageTime))));
IF NOT EXISTS (SELECT * FROM  Messages.Topic WHERE Name = ’General Topic’)
    INSERT INTO Messages.Topic(Name, Description)
    VALUES(’General Topic’,’General Topic’);
BEGIN TRY
   BEGIN TRANSACTION;
                --first create a new message
                INSERT INTO [Messages].[Message]
                                        ([MessagingUserId],[SentToMessagingUserId]
                                        ,[Text]        ,[MessageTime])
                VALUES (@messagingUserId,NULL,@text, @messageTime);
                --then insert the topic, but this will fail because General topic is not
                --compatible with a UserDefinedTopicName value
                INSERT INTO Messages.MessageTopic(MessageId, TopicId, UserDefinedTopicName)
                VALUES(
                (SELECT MessageId
                 FROM   Messages.Message
                 WHERE  MessagingUserId = @messagingUserId
                   AND  Text = @text
                   AND  RoundedMessageTime = @RoundedMessageTime),
                                        (SELECT TopicId
                                         FROM Messages.Topic
                                         WHERE Name = ’General’),’Stupid Stuff’);
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
        IF ERROR_MESSAGE() NOT LIKE
                            ’%CHKMessageTopic_UserDefinedTopicName_NullUnlessUserDefined%’
                THROW 50000,’UserDefined Message Check Failed’,1;
END CATCH;

The test script provided with the download is just a very basic example of a test script, and it will take a while to get a good unit test script created. It took me more than several hours to create this one for this simple six-table database. In reality, I usually start with a simple script that creates data only without testing the CHECK constraints, uniqueness constraints, and so on, because it is rare that I have time to do the complete test script before turning the database over to developers.

Once the process of building your unit tests is completed, you will find that it will have helped you find issues with your design and any problems with constraints. In many cases, you may not want to put certain constraints on the development server immediately and work with developers to know when they are ready. As a DB developer, and a lapsed UI developer, I personally liked it when the database prevented me from breaking a fundamental rule, so your mileage may vary as to what works best with the people you work with/against. I will say this, as I created this script, I discovered a few semi-significant issues with the demo design I created for this chapter that wouldn’t have likely been noticed by myself, except that I was testing the design.

Best Practices

The following are a set of some of the most important best practices when implementing your database structures. Pay particular attention to the advice about UNIQUE constraints. Just having a surrogate key on a table is one of the worst mistakes made when implementing a database.

• Understand the relational engines and choose wisely: With the classic On-Disk engine model and the In-Memory model coexisting in SQL Server, you have more choices than ever to produce highly concurrent database solutions. The vast majority of relational database projects do not need the In-Memory model, but for those that do, you can get tremendous performance for some or all of the tables in your database.
• Invest in database generation tools: Do this after you know what the tool should be doing. Implementing tables, columns, relationships, and so on is a tedious and painful task when done by hand. There are many great tools that double as logical data modeling tools and also generate these objects, as well as sometimes the objects and code to be covered in the upcoming three chapters.
• Maintain normalization: As much as possible, try to maintain the normalizations that were designed in Chapter 5. It will help keep the data better protected and will be more resilient to change.
• Develop a real strategy for naming objects: Keep the basics in mind:
  • Give all objects reasonably user-friendly names. Make sure that it’s obvious—at least to support personnel—what the purpose of every object, column, and so on is without digging into documentation, if at all possible.
  • Have either all plural or all singular names for tables. Consistency is the key.
  • Have all singular names for columns.
  • I will use singular names for tables and columns.
• Develop template domains: Reuse in every case where a similar datatype is needed. This cuts down on time spent implementing and makes users of the data happy, because every time they see a column called Description, it’s likely that it will have the same characteristics of other like columns.
• Carefully choose the datatype and nullability for each column: These are the first level of data protection to keep your data clean and pure. Also, improper datatypes can cause precision difficulties with numbers and even performance issues.
• Make certain that every table has at least one uniqueness constraint that doesn’t include an artificial value: It’s a good idea to consider using an IDENTITY column as the primary key. However, if that is the only uniqueness constraint on the table, then there can (and usually will) be duplication in the real columns of the table—a bad idea.
• Implement foreign keys using foreign key constraints: They’re fast, and no matter what kind of gaffes a client makes, the relationship between tables cannot be messed up if a foreign key constraint is in place.
• Document and script everything: Using extended properties to document your objects can be extremely valuable. Most of all, when you create objects in the database, keep scripts of the T-SQL code for later use when moving to the QA and production environments. A further step of keeping your scripts in a source control repository is a definite good next step as well so you can see where you are, where you are going, and where you have been in one neat location.
• Develop a test script: Test your structures as much as possible. Testing is often the forgotten step in database implementation, but good testing is essential to know that your design works.

Deployment Lifecycle

As you complete the DDL and complete as much of the unit testing as you can, usually there are other people who are waiting to build UIs, reports, object models, etc. So when you arrive at that point in the dev process that your DDL is ready to go, things get real.

The next step is to provide a real database for your teammates to work with, one that you don’t drop and re-create over and over. There are two steps to deploying a database script:

• Brand new database: This is easy. Simply use the script you used for testing. Sadly, this usually only works once per database, out of the 100s to 1000s of times you will need the following step.
• Subsequent builds of the database: Databases are different from most other types of software programming because tables have state information. If you change the design, you have to retrofit the data into the new objects. The method I have adopted is to generate the database that I test with, and consider it the “model” database. No code, no data (other than model/domain data), just what was in the design. From there I use a comparison tool to generate the differences between the model and the live database, verify the script, and apply.

We need to cover just a bit about the second step of the process. What is described is rather simplified from the myriad of possible methods of applying changes. I use the mentioned process of creating a model database, and then I compare it to a database that is mirrored in source control using Redgate’s SQL Source Control tool and apply the changes. (Microsoft’s Data Tools has similar functionality as part of the tools as well.) After checking in the changes, this is used with Redgate’s SQL Compare tools to apply structure changes to multiple developer servers, and then to dev, test, and finally prod servers.

SQL Server Data Tools has lifecycle steps built in, and that tool works a lot more like Visual Studio programmers are used to, if that suits you. And other companies have other tools to help manage the database-changes life cycle.

The primary idea that I am advocating here in this section is that you keep a pristine copy of your database that you create from a modeling tool or script set, and then run your unit tests on it. From there, figure out a method of getting your changes to the other members of your team that works for you.

Summary

This has been a long chapter covering a large amount of ground. Understanding how to build tables, and how they’re implemented, is the backbone of every database designer’s knowledge.

After getting satisfied that a model was ready to implement, I took a deep look at SQL Server tables, walking through the process of creating the database using the CREATE TABLE and ALTER TABLE syntax for adding and creating tables, adding constraints and modifying columns, and even creating triggers to manage automatically maintained columns. General guidelines were given for naming tables, columns, indexes, and constraints. The key to good naming is consistency, and if the naming standards I suggested here seem too ugly, messy, or just plain weird to you, choose your own. Consistency is the most important thing for any naming convention.

The two most important sections of this chapter were on choosing datatypes and implementing uniqueness constraints. I completed this chapter by discussing the process of choosing primary keys and at least one natural key per table. Of course, the section on testing is pretty important too, as good testing is the key to finding those obvious errors that some developers will eagerly point out to anyone will listen to about how much slower the process is with constraints, triggers, and such.

In the rest of the book, we will continually expand on the topics of this chapter, building more and more complex objects, and digging deeper into the behaviors of SQL Server that will take your designs from the simplicity of this chapter and allow you to create complex, high-performance database solutions.