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.
Table 6-9. Domain: UserHandle
Property |
Setting |
---|---|
Name |
UserHandle |
Optional |
No |
Datatype |
Basic character set, maximum of 20 characters |
Value Limitations |
Must be 5–20 simple alphanumeric characters and start with a letter |
Default Value |
n/a |
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:
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:
To maintain extended properties, you’re given the following functions and stored procedures:
Each (other than sys.extendedproperties) has the following parameters:
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.
Figure 6-32. Descriptions in Management Studio (reward for hard work done)
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.
Note As of SQL Server 2016, extended properties are not available on memory-optimized tables.
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
Note If you are really paying attention, you probably are thinking “didn’t he use the sys.schema catalog view before?” And yes, that is true. I tend to use the INFORMATION_SCHEMA views for reporting on metadata that I want to view, and the catalog views when doing development work, as they can be a bit easier on the eyes since these views include the database name, often as the first column at 128 characters. However, the INFORMATION_SCHEMA view has a lot of niceties that are useful, such as returning readable names for all of the layers of database structure I am interested in, and the schema is based on standards so is less likely to change from version to version.
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.
Tip The INFORMATION_SCHEMA and catalog views are important resources for the DBA to find out what is in the database. Throughout this book, I will try to give insight into some of them, but there is another book’s worth of information out there on the metadata of SQL Server.
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.
Note A comprehensive discussion of testing is out of scope for this book because complete testing requires involvement of the entire team. The unit test is generally the first of several testing steps, which include integration testing, user testing, performance testing, and so on. These will flow out of the application implementation that will be tested simultaneously. During unit testing our database structures, the goal will be simply to prove to ourselves that the code we created does the minimum that it is programmed to do.
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.
Note In the download, I have included a script file named Chapter 6 – Database Create Objects.sql that includes the minimal script to create the database and return the metadata. This will allow you to start with a clean database over and over without working through the entire chapter script.
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;
Tip In the previous block of code, the statement fails, but no error is returned. The goal is that you can run your test script over and over and get no output other than seeing rows in your tables. However, in practice, it is a lot cleaner to see only problematic output. If you would prefer, add more output to your test script as best suits your desire.
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.
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:
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.