Basing a Primary Key on Existing Columns

In many cases, a table will have an obvious, easy-to-use primary key. This is especially true when talking about independent entities. For example, take a table such as product. It would often have a productNumber defined. A person usually has some sort of identifier, either government or company issued. (For example, my company has an employeeNumber that I have to put on all documents, particularly when the company needs to write me a check.)

The primary keys for dependent tables can often generally take the primary key of the independent tables, add one or more attributes, and—presto!—primary key.

For example, I have a Ford SVT Focus, made by the Ford Motor Company, so to identify this particular model, I might have a row in the Manufacturer table for Ford Motor Company (as opposed to GM or something). Then, I’d have an automobileMake row with a key of manufacturerName = 'Ford Motor Company' and makeName = 'Ford' (instead of Lincoln, Mercury, Jaguar, and so on), style = 'SVT', and so on, for the other values. This can get a bit messy to deal with, because the key of the automobileModelStyle table would be used in many places to describe which products are being shipped to which dealership. Note that this isn’t about the size in terms of the performance of the key, just the number of values that make up the key. Performance will be better the smaller the key, as well, but this is true not only of the number of columns, but this also depends on the size of the values.

Note that the complexity in a real system such as this would be compounded by the realization that you have to be concerned with model year, possibly body style, different prebuilt packages, and so on. The key of the table may frequently have many parts, particularly in tables that are the child of a child of a child, and so on.

Basing a Primary Key on a New, Surrogate Value

The other common key style is to use only a single column for the primary key, regardless of the size of the other keys. In this case, you’d specify that every table will have a single primary key and implement alternate keys in your tables, as shown in Figure 6-4.

Note that in this scenario, all of your relationships will be implemented in the database as nonidentifying type relationships, though you will implement them to all be required values (no NULLs). Functionally, this is the same as if the parentKeyValue was migrated from parent through child and down to grandChild, though it makes it harder to see in the model.

In the model in Figure 6-4, the most important thing you should notice is that each table not only has the primary key but also an alternate key. The term “surrogate” has a very specific meaning, even outside of computer science, and that is that it serves as a replacement. So the surrogate key for the parent object of parentKeyValue can be used as a substitute for the defined key, in this case otherColumnsForAltKey .

This method does have some useful advantages:

• Every table has a single-column primary key: It’s much easier to develop applications that use this key, because every table will have a key that follows the same pattern. It also makes code generation easier to follow, because it is always understood how the table will look, relieving you from having to deal with all the other possible permutations of key setups.
• The primary key index will be small: Thus, operations that use the index to access a row in the table will be faster. Most update and delete operations will likely modify the data by accessing the data based on primary keys that will use this index.
• Joins between tables will be easier to code: That’s because all migrated keys will be a single column. Plus, if you use a surrogate key that is named TableName + Suffix, there will be less thinking to do when setting up the join.

There are also disadvantages to this method, such as always having to join to a table to find out the meaning of the surrogate key value, plus, as in our example table in Figure 6-2, you would have to join from the grandChild table through the child table to get values from parent. Another issue is that some parts of the self-documenting nature of relationships are obviated, because using only single-column keys eliminates the obviousness of all identifying relationships. So in order to know that the logical relationship between parent and grandchild is identifying, you will have trace the relationship and look at the uniqueness constraints.

Assuming you have chosen to use a surrogate key, the next choice is to decide what data to use for the key. Let’s look at two methods of implementing these keys, either by deriving the key from some other data or by using a meaningless surrogate value.

A popular way to define a primary key is to simply use a meaningless surrogate key like we’ve modeled previously, such as using a column with the IDENTITY property, which automatically generates a unique value. In this case, you rarely let the user have access to the value of the key but use it primarily for programming.

It’s exactly what was done for most of the entities in the logical models worked on in previous chapters: simply employing the surrogate key while we didn’t know what the actual value for the primary key would be. This method has one nice property:

You never have to worry about what to do when the primary key value changes.

Once the key is generated for a row, it never changes, even if all the data changes. This is an especially nice property when you need to do analysis over time. No matter what any of the other values in the table have been changed to, as long as the surrogate key value represents the same thing, you can still relate it to its usage in previous times. (This is something you have to be clear about with the DBA/programming staff as well. Sometimes, they may want to delete all data and reload it, but if the surrogate changes, your link to the unchanging nature of the surrogate key is likely broken.) Consider the case of a row that identifies a company. If the company is named Bob’s Car Parts and it’s located in Topeka, Kansas, but then it hits it big, moves to Detroit, and changes the company name to Car Parts Amalgamated, only one row is touched: the row where the name is located. Just change the name, and it’s done. Keys may change, but not primary keys. Also, if the method of determining uniqueness changes for the object, the structure of the database needn’t change beyond dropping one UNIQUE constraint and adding another.

Using a surrogate key value doesn’t in any way prevent you from creating additional single part keys, like we did in the previous section. In fact, it pretty much demands it. For most tables, having a small code value is likely going to be a desired thing. Many clients hate long values, because they involve “too much typing.” For example, say you have a value such as “Fred’s Car Mart.” You might want to have a code of “FREDS” for it as the shorthand value for the name. Some people are even so programmed by their experiences with ancient database systems that had arcane codes that they desire codes such as “XC10” to refer to “Fred’s Car Mart.”

In the demonstration model, I set all of the keys to use natural keys based on how one might do a logical model, so in a table like MessagingUser in Figure 6-5, it uses a key of the entire handle of the user.

This value is the most logical, but this name, based on the requirements, can change. Changing this to a surrogate value will make it easier to make the name change and not have to worry about existing data in the table. Making this change to the model results in the change shown in Figure 6-6, and now, the key is a value that is clearly recognizable as being associated with the MessagingUser, no matter what the uniqueness of the row may be. Note that I made the UserHandle an alternate key as I switched it from primary key.

Next up, we will take a look at the Message table shown in Figure 6-7. Note that the two columns that were named UserHandle and SentToUserHandle have had their role names changed to indicate the change in names from when the key of MessagingUser was UserHandle.

We will transform this table to use a surrogate key by moving all three columns to nonkey columns, placing them in a uniqueness constraint, and adding the new MessageId column. Notice, too, in Figure 6-8 that the table is no longer modeled with rounded corners, because the primary key no longer is modeled with any migrated keys in the primary key.

One additional benefit of your tables having a single column surrogate key for a key is that all tables follow a common pattern. Having a common pattern for every table is useful for programming with the tables as well. Because every table has a single-column key that isn’t updatable and is the same datatype, it’s possible to exploit this in code, making code generation a far more straightforward process. Note once more that nothing should be lost when you use surrogate keys, because a surrogate of this style replaces an existing natural key. Many of the object relational mapping (ORM) tools that are popular (if controversial in the database community) require a single column integer key as their primary implementation pattern. I don’t favor forcing the database to be designed in any manner to suit client tools, but sometimes, what is good for the database is the same as what is good for the tools, making for a relatively happy ending, at least.

By implementing tables using this pattern, I’m covered in two ways: I always have a single primary key value, but I always have a key that cannot be modified, which eases the difficulty for loading a warehouse. No matter the choice of human-accessible key, surrogate keys are the style of key that I use for all tables in databases I create, for every table. In Figure 6-9, I have completed the transformation to using surrogate keys.

Keep in mind that I haven’t specified any sort of implementation details for the surrogate key at this point, and clearly, in a real system, I would already have done this during the transformation. For this chapter example, I am using a deliberately detailed process to separate each individual step, so I will put off that discussion until the DDL section of this book, where I will present code to deal with this need along with creating the objects.

Deprecated or Bad Choice Types

I didn’t include several datatypes in the previous list listed because they have been deprecated for quite some time, and it wouldn’t be surprising if they were completely removed from the version after 2012, even though I said the same thing in the previous version of the book so be sure to stop using them as soon as possible). Their use was common in versions of SQL Server before 2005, but they’ve been replaced by types that are far easier to use:

• image: Replace with varbinary(max)
• text or ntext: Replace with varchar(max) and nvarchar(max)

If you have ever tried to use the text datatype in SQL code, you know it is not a pleasant thing. Few of the common text operators were implemented to work with it, and in general, it just doesn’t work like the other native types for storing string data. The same can be said with image and other binary types. Changing from text to varchar(max), and so on, is definitely a no-brainer choice.

The second types that are generally advised against being used are the two money types:

• money: –922,337,203,685,477.5808 through 922,337,203,685,477.5807 (8 bytes)
• smallmoney: Money values from –214,748.3648 through 214,748.3647 (4 bytes)

In general, the money datatype sounds like a good idea, but using has some confusing consequences. In Appendix A, I spend a bit more time covering these consequences, but here are two problems:

• There are definite issues with rounding off, because intermediate results for calculations are calculated using only four decimal places.
• Money data output includes formatting, including a monetary sign (such as $ or £), but inserting $100 and £100 results in the same value being represented in the variable or column.

Hence, it’s generally accepted that it’s best to store monetary data in decimal datatypes. This also gives you the ability to assign the numeric types to sizes that are reasonable for the situation. For example, in a grocery store having the maximum monetary value of a grocery item over 200,000 dollars is probably unnecessary, even figuring for a heck of a lot of inflation. Note that in Appendix A I will include a more thorough example of the types of issues you will see.

Common Datatype Configurations

In this section, I will briefly cover concerns and issues relating to Boolean/logical values, large datatypes, and complex types and then summarize datatype concerns in order to discuss the most important thing you need to know about choosing a datatype.

The Most Important Consideration for Choosing Datatypes

When all is said and done, the most important consideration when choosing a datatype is to keep things simple and choose the right types for the job. SQL Server gives you a wide range of datatypes, and many of them can be declared in a wide variety of sizes. I never cease to be amazed by the number of databases around where every single column is either an integer or a varchar(N) (where N is the same for every single string column) and varchar(max). One particular example I’ve worked with had everything, including GUID-based primary keys, all stored in nvarchar(200) columns! It is bad enough to store your GUIDs in a varchar column at all, since it is stored as a 16-byte binary value, and as a varchar column, it will take 36 bytes; however, store it in an nvarchar column, and now, it takes at least 72 bytes! What a hideous waste of space. Even worse, now all data could be up to 200 characters wide, even if you plan to give entry space for only 30 characters. Now, people using the data will feel like they need to allow for 200 characters on reports and such for the data. Time wasted, space wasted, money wasted.

As another example, say you want to store a person’s name and date of birth. You could choose to store the name in a varchar(max) column and the date of birth in a varchar(max) column. In all cases, these choices would certainly store the data that the user wanted, but they wouldn’t be good choices. The name should be in something such as a varchar(30) column and the date of birth in a date column. Notice that I used a variable size type for the name. This is because you don’t know the length and not all names are the same size. Because most names aren’t nearly 30 bytes, using a variable-sized type will save space in your database.

Of course, in reality, seldom would anyone make such poor choices of a datatype as putting a date value in a varchar(max) column. Most choices are reasonably easy. However, it’s important keep in mind that the datatype is the first level of domain enforcement. Thinking back to our domain for UserHandle, we had the following datatype definition, and value limitations:

You can enforce the first part of this at the database level by declaring the column as a varchar(20). A column of type varchar(20) won’t even allow a 21-character or longer value to be entered. It isn’t possible to enforce the rule of greater than or equal to five characters using only a datatype. I’ll discuss more about how to enforce simple domain requirements later in this chapter, and in Chapter 9, we will discuss patterns of integrity enforcements that are more complex.

In the earlier part of the process, we defined domains for every one of our columns (well, theoretically, in actuality some of them are simply named now, but we will make assumptions about each column in this chapter, so we can bring it in under 200 pages).

Initially, we had the model in Figure 6-18 for the MessagingUser table.

Choosing types, we will use an int for the surrogate key (and in the DDL section, we will set the implementation of the rest of the optionality rule set in the domain: “Not optional auto generated for keys, optionality determined by utilization for nonkey”, but will replace items of SurrogateKey domain with int types. User handle was discussed earlier in this section. In Figure 6-19, I chose some other basic types for Name. AccessKeyValue , and the AttendeeType columns .

Sometimes, you won’t have any real domain definition, and you will use common sizes. For these, I suggest using either a standard type (if you can find them, like on the Internet) or look through data you have in your system. Until the system gets into production, changing types is fairly easy from a database standpoint, but the more code that accesses the structures the more difficult it gets to make changes.

For the Message table in Figure 6-20, we will choose types.

The text column isn’t datatype text but is the text of the message, limited to 200 characters. For the time columns, in Figure 6-21, I choose datetime2(0) for the MessageTime, since the requirements specified time down to the second. For RoundedMessageTime, we are rounding to the hour, and so I chose also will expect it to be datetime2(0), though it will be a calculated column based on the MessageTime value. Hence, MessageTime and RoundedMessageTime are two views of the same data value.

So I am going to use a calculated column as shown in Figure 6-21, I will specify the type of RoundedMessageTime as a nonexistent datatype (so if I try to create the table it will fail). A calculated column is a special type of column that isn’t directly modifiable, as it is based on the result of an expression.

Later in this chapter, we will specify the actual implementation, but for now, we basically just set a placeholder. Of course, in reality, I would specify the implementation immediately, but again, for this first learning process, I am doing things in this deliberate manner to keep things orderly. So, in Figure 6-22, I have the model with all of the datatypes set.

Schema

As discussed in the earlier section where we defined schemas for our database, a schema is a namespace: a container where database objects are contained, all within the confines of a database. One thing that is nice is that because the schema isn’t tightly tied to a user, you can drop the user without changing the exposed name of the object. Changing owners of the schema changes owners of the table. (This is done using the ALTER AUTHORIZATION statement.)

In SQL Server 2000 and earlier, the table was owned by a user, which made using two (or more) part names difficult. Without getting too deep into security, objects owned by the same user are easier to handle with security due to ownership chaining. If one object references another and they are owned by the same user, the ownership chain isn’t broken. So we had every object owned by the same user.

Starting with SQL Server 2005, a schema is owned by a user, and tables are contained in a schema. Just as in 2000, the generally suggested best practice was that all tables were owned by the dbo user. Now, this is done by having the schema owned by dbo, but this doesn’t mean you have to have every schema named dbo since an object in schema A can reference objects in schema B without breaking the ownership chain as long as they are both owned by dbo.

Not just tables are bound to a given schema; just about every object is schema bound. You can access objects using the following naming method:

 [<databaseName>.][<schemaName>.]objectName

The <databaseName> defaults to the current database. The <schemaName> defaults to the user’s default schema. In general, it is best to always specify the schema in any and all SQL statements because it saves SQL the work of having to decide which schema to use (when the schema is not specified, the call is considered to be caller dependent because what it refers to may change user to user).

Schemas are of great use to segregate objects within a database for clarity of use. In our database, we have already specified two schemas, Messages and Attendees. The basic syntax is simple, just CREATE SCHEMA <schemaName> (it must be the first statement in the batch.) So I will create them using the following commands:

 CREATE SCHEMA Messages; --tables pertaining to the messages being sent

 GO

 CREATE SCHEMA Attendees; --tables pertaining to the attendees and how they can send messages

 GO

You can view the schemas created using the sys.schemas catalog view:

 SELECT name, USER_NAME(principal_id) as principal

 FROM   sys.schemas

 WHERE  name <> USER_NAME(principal_id); --don't list user schemas

This returns

Sometimes, schemas end up owned by a user other than dbo, like when a developer without db_owner privileges creates a schema. You can change the ownership using ALTER AUTHORIZATION, just like for the database:

 ALTER AUTHORIZATION ON SCHEMA::Messages To DBO;

As a note, it is suggested to always specify the two-part name for objects in code. It is safer, because you know what schema it is using, and it doesn’t need to check the default on every execution. However, for ad-hoc access, it can be annoying to type the schema if you are commonly using a certain schema. You can set a default schema for a user in the CREATE and ALTER USER statements, like this:

 CREATE USER <schemaUser>

        FOR LOGIN <schemaUser>

        WITH DEFAULT SCHEMA = schemaname;

There’s also an ALTER USER command that allows the changing of default schema for existing users (and in SQL Server 2012, it now works for Windows Group–based users as well. For 2005-2008R2, it only worked for standard users).

Columns and Base Datatypes

The next part of the CREATE TABLE statement is for the column specifications:

 CREATE TABLE [<database>.][<schema>.]<tablename>

  (

  <columnName> <datatype> [<NULL specification>]

        [IDENTITY [(seed,increment)]

  --or

  <columnName> AS <computed definition>

  );

The <columnName> placeholder is where you specify the name of the column.

There are two types of columns:

• Implemented: This is an ordinary column, in which physical storage is allocated and data is stored for the value.
• Computed (or virtual): These columns are made up by a calculation derived from any of the physical columns in the table.

Most of the columns in any database will be implemented columns, but computed columns have some pretty cool uses, so don’t think they’re of no use just because they aren’t talked about much. You can avoid plenty of code-based denormalizations by using computed columns. In our example tables, we specified one computed column shown in Figure 6-27.

So the basic columns (other than the computed column) are fairly simple, just name and datatype:.

 MessageId               int,

 SentToMessagingUserId   int,

 MessagingUserId         int,

 Text                    nvarchar(200),

 MessageTime             datetime2(0),

 RowCreateTime           datetime2(0),

 RowLastUpdateTime       datetime2(0)

The requirements called for the person to not send the same message more than once an hour. So we construct a function that takes the MessageTime in datetime2(0) datatype. That time is at a level of seconds, and we need the data in the form of hours. So first, we develop an expression that will do this. I start out with a variable of the type of the column we are deriving from and then set it to some value. I start with a variable of datetime2(0) and load it with the time from SYSDATETIME():

 declare @time datetime2(0)

 set @time = SYSDATETIME()

Next, I write the following expression:

 dateadd(hour,datepart(hour,@time),cast(cast(@time as date)as datetime2(0)) )

which can be broken down fairly simply, but basically takes the number of hours since midnight and adds that to the date-only value by casting it to a date and then to a datetime2, which allows you to add hours to it. Once the column is tested, you replace the variable with the MessageTime column. So, our calculated column will be specified as follows:

 ,RoundedMessageTime as dateadd(hour,datepart(hour,MessageTime),

    cast(cast(MessageTime as date)as datetime2(0)) ) PERSISTED

The persisted specification indicates that the value will be calculated and saved as part of the definition and storage of the table, just like a fully implemented column. In order to be persisted, the expression must be deterministic, which basically means that for the same input, you will always get the same output (much like we covered in normalization). You can also use a deterministic expression as a column in an index (we will use it as part of the uniqueness constraint for this table). So an expression like getdate() is possible, but you could not index it.

Nullability

In the column-create phrase, simply change the <NULL specification> in your physical model to NULL to allow NULLs, or NOT NULL not to allow NULLs:

 <columnName><data type>[<NULL specification>]

There’s nothing particularly surprising here. For the noncomputed columns in the Messages.Message table back in Figure 6-27, we will specify the following nullabilities:

 MessageId                 int             NOT NULL ,

 SentToMessagingUserId     int             NULL ,

 MessagingUserId           int             NOT NULL ,

 Text                      nvarchar(200)   NOT NULL ,

 MessageTime               datetime2(0)    NOT NULL ,

 RowCreateTime             datetime2(0)    NOT NULL ,

 RowLastUpdateTime         datetime2(0)    NOT NULL

Managing Nonnatural Primary Keys

Finally, before getting too excited and completing the table creation script, there’s one more thing to discuss. Earlier in this chapter, I discussed the basics of using a surrogate key in your table. In this section, I’ll present the method that I typically use. I break down surrogate key values into the types that I use:

• Manually managed, where you let the client choose the surrogate value, usually a code or short value that has meaning to the client. Sometimes, this might be a meaningless value, if the textual representation of the code needs to change frequently, but often, it will just be a simple code value. For example, if you had a table of U.S. states, you might use 'TN' for the code of the state of Tennessee.
• Automatically generated, using the IDENTITY property or a GUID stored in a uniqueidentifier type column.
• A cross between the two, where you use the IDENTITY property for user created data but manually load some values to give programmers direct access to the surrogate value.

Of course, if your tables don’t use any sort of surrogate values, you can move on to the next section.

Adding Primary Key Constraints

The first set of constraints we will add to the tables will be the primary key constraints. The syntax of the primary key declaration is straightforward:

 [CONSTRAINT constraintname] PRIMARY KEY [CLUSTERED | NONCLUSTERED]

As will all constraints, the constraint name is optional, but you should never treat it as such. I’ll name primary key constraints using a name such as PK_<schema>_<tablename>. In almost all cases, you will want to make the primary key clustered, especially when it is the most frequently used key for accessing rows. In Chapter 10, I will describe the physical/internal structures of the database and will give more indications of when you might alter from the clustered primary key path, but I generally start with clustered and adjust if the usage patterns lead you down a different path.

You can specify the primary key constraint when creating the table, just like we did the default for the sequence object. If it is a single column key, you could add it to the statement like this:

 CREATE TABLE Messages.Topic (

  TopicId int NOT NULL CONSTRAINT DFLTMessage_Topic_TopicId

  DEFAULT(NEXT VALUE FOR dbo.TopicIdGenerator)

 CONSTRAINT PK_Messages_Topic PRIMARY KEY,

  Name                  nvarchar(30) NOT NULL ,

  Description            varchar(60) NOT NULL ,

  RowCreateTime            datetime2(0) NOT NULL ,

  RowLastUpdateTime         datetime2(0) NOT NULL

 );

Or if it was a multiple column key, you can specify it inline with the columns like the following example:

 CREATE TABLE Examples.ExampleKey

 (

 ExampleKeyColumn1 int NOT NULL,

 ExampleKeyColumn2 int NOT NULL,

 CONSTRAINT PK_Examples_ExampleKey

  PRIMARY KEY (ExampleKeyColumn1, ExampleKeyColumn2)

 )

The more common method is use the ALTER TABLE statement and simply alter the table to add the constraint, like the following, which is the code in the downloads that will add the primary keys:

 ALTER TABLE Attendees.AttendeeType

  ADD CONSTRAINT PK_Attendees_AttendeeType PRIMARY KEY CLUSTERED (AttendeeType);

 ALTER TABLE Attendees.MessagingUser

  ADD CONSTRAINT PK_Attendees_MessagingUser PRIMARY KEY CLUSTERED (MessagingUserId);

 ALTER TABLE Attendees.UserConnection

  ADD CONSTRAINT PK_Attendees_UserConnection PRIMARY KEY CLUSTERED (UserConnectionId);

 ALTER TABLE Messages.Message

  ADD CONSTRAINT PK_Messages_Message PRIMARY KEY CLUSTERED (MessageId);

 ALTER TABLE Messages.MessageTopic

  ADD CONSTRAINT PK_Messages_MessageTopic PRIMARY KEY CLUSTERED (MessageTopicId);

 ALTER TABLE Messages.Topic

  ADD CONSTRAINT PK_Messages_Topic PRIMARY KEY CLUSTERED (TopicId);

Building Default Constraints

If a user doesn’t know what value to enter into a table, the value can be omitted, and the default constraint sets it to a valid predetermined value. This helps, in that you help users avoid having to make up illogical, inappropriate values if they don’t know what they want to put in a column yet they need to create a row. However, the true value of defaults is lost in most applications, because the user interface would have to honor this default and not reference the column in an insert operation (or use the DEFAULT keyword for the column value for a default constraint to matter).

We used a default constraint earlier to implement the primary key generation, but here, I will spend a bit more time describing how it works. The basic syntax for the default constraint is

 [CONSTRAINT constraintname] DEFAULT (<simple scalar expression>)

The scalar expression must be a literal, or it can use a function, even a user defined one that accesses a table. A literal is a simple single value in the same datatype that requires no translation by SQL Server. For example, Table 6-7 has sample literal values that can be used as defaults for a few datatypes.

As an example in our sample database, we have the DisabledFlag on the Attendees.MessagingUser table. I’ll set the default value to 0 for this column here:

 ALTER TABLE Attendees.MessagingUser

  ADD CONSTRAINT DFAttendees_MessagingUser_DisabledFlag

  DEFAULT (0) FOR DisabledFlag;

Beyond literals, you can use system functions to implement default constraints. In our model, we will use a default on all of the table’s RowCreateTime and RowLastUpdateTime columns. To create these constraints, I will demonstrate one of the most useful tools in a DBA’s toolbox: using the system views to generate code. Since we have to do the same code over and over, I will query the metadata in the INFORMATION_SCHEMA.COLUMN view, and put together a query that will generate the default constraints (you will need to set your output to text and not grids in SSMS to use this code):

 SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(13) + CHAR(10) +

  ' ADD CONSTRAINT DFLT' + TABLE_SCHEMA + '_' + TABLE_NAME + '_' +

  COLUMN_NAME + CHAR(13) + CHAR(10) +

  ' DEFAULT (SYSDATETIME()) FOR ' + COLUMN_NAME + ';'

 FROM INFORMATION_SCHEMA.COLUMNS

 WHERE COLUMN_NAME in ('RowCreateTime', 'RowLastUpdateTime')

  and TABLE_SCHEMA in ('Messages','Attendees')

 ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

This code will generate the code for ten constraints:

 ALTER TABLE Attendees.MessagingUser

  ADD CONSTRAINT DFLTAttendees_MessagingUser_RowCreateTime

  DEFAULT (SYSDATETIME()) FOR RowCreateTime;

 ALTER TABLE Attendees.MessagingUser

  ADD CONSTRAINT DFLTAttendees_MessagingUser_RowLastUpdateTime

  DEFAULT (SYSDATETIME()) FOR RowLastUpdateTime;

 ALTER TABLE Attendees.UserConnection

  ADD CONSTRAINT DFLTAttendees_UserConnection_RowCreateTime

  DEFAULT (SYSDATETIME()) FOR RowCreateTime;

 ALTER TABLE Attendees.UserConnection

  ADD CONSTRAINT DFLTAttendees_UserConnection_RowLastUpdateTime

  DEFAULT (SYSDATETIME()) FOR RowLastUpdateTime;

 ALTER TABLE Messages.Message

  ADD CONSTRAINT DFLTMessages_Message_RowCreateTime

  DEFAULT (SYSDATETIME()) FOR RowCreateTime;

 ALTER TABLE Messages.Message

  ADD CONSTRAINT DFLTMessages_Message_RowLastUpdateTime

  DEFAULT (SYSDATETIME()) FOR RowLastUpdateTime;

 ALTER TABLE Messages.MessageTopic

  ADD CONSTRAINT DFLTMessages_MessageTopic_RowCreateTime

  DEFAULT (SYSDATETIME()) FOR RowCreateTime;

 ALTER TABLE Messages.MessageTopic

  ADD CONSTRAINT DFLTMessages_MessageTopic_RowLastUpdateTime

  DEFAULT (SYSDATETIME()) FOR RowLastUpdateTime;

 ALTER TABLE Messages.Topic

  ADD CONSTRAINT DFLTMessages_Topic_RowCreateTime

  DEFAULT (SYSDATETIME()) FOR RowCreateTime;

 ALTER TABLE Messages.Topic

  ADD CONSTRAINT DFLTMessages_Topic_RowLastUpdateTime

  DEFAULT (SYSDATETIME()) FOR RowLastUpdateTime;

Obviously it’s not the point of this section, but generating code with the system metadata is a very useful skill to have, particularly when you need to add some type of code over and over.

Adding Relationships (Foreign Keys)

I’ve covered relationships in previous chapters already, so I’ll try to avoid saying too much more about why to use them. In this section, I’ll simply discuss how to implement relationships. It’s common to add constraints using the ALTER TABLE statement, but you can also do this using the CREATE TABLE statement. However, because tables are frequently created all at once, it’s usually more likely that the ALTER TABLE command will be used, because parent tables needn’t be created before dependent child tables in scripts.

The typical foreign key is implemented as a primary key of one table migrated to the child table that represents the entity from which it comes. You can also reference a unique constraint as well, but it is pretty rare and a very atypical implementation. An example is a table with an identity key and a textual code. You could migrate the textual code to a table to make it easier to read, if user requirements required it and you failed to win the argument against doing something that will confuse everyone for years to come.

The syntax of the statement for adding foreign key constraints is pretty simple:

 [CONSTRAINT <constraintName>]

 FOREIGN KEY REFERENCES <referenceTable> (<referenceColumns>)

 [ON DELETE <NO ACTION | CASCADE | SET NULL | SET DEFAULT> ]

 [ON UPDATE <NO ACTION | CASCADE | SET NULL | SET DEFAULT> ]

where

• <referenceTable> is the parent table in the relationship.
• <referenceColumns> is a comma-delimited list of columns in the child table in the same order as the columns in the primary key of the parent table.
• ON DELETE or ON UPDATE clauses specify what to do when a row is deleted or updated. Options are
  • NO ACTION: Raises an error if you end up with a child with no matching parent after the statement completes
  • CASCADE: Applies the action on the parent to the child, either updates the migrated key values in the child to the new parent key value or deletes the child row
  • SET NULL: If you delete or change the value of the parent, you set the child key to NULL
  • SET DEFAULT: If you delete or change the value of the parent, the child key is set to the default value from the default constraint, or NULL if no constraint exists.

If you are using surrogate keys, you will very rarely need either of the ON UPDATE options, since the value of a surrogate is rarely editable. For deletes, 98 % of the time you will use NO ACTION, because most of the time, you will simply want to make the user delete the children first to avoid accidentally deleting a lot of data. Lots of NO ACTION foreign key constraints will tend to make it much harder to execute an accidental DELETE FROM <tableName> when you accidentally didn’t highlight the WHERE clause in SSMS. The most useful of the actions is ON DELETE CASCADE, which is frequently useful for table sets where the child table is, in essence, just a part of the parent table. For example, invoice ←invoiceLineItem. Usually, if you are going to delete the invoice, you are doing so because it is bad ,and you will want the line items to go away too. On the other hand, you want to avoid if for relationships like Customer ←Invoice. Deleting a customer who has invoices is probably a bad idea.

Thinking back to our moeling, there were optional and required relationships such as the one in Figure 6-29.

The child.parentId column needs to allow NULLs (which it does on the model). For a required relationship, the child.parentId would not be null, like in Figure 6-30.

This is all you need to do, because SQL Server knows that when the referencing key allows a NULL, the relationship value is optional. You don’t need to have a NULL primary key value for the relationship because, as discussed, it’s impossible to have a NULL attribute in a primary key. In our model, represented here in Figure 6-31, we have seven relationship modeled.

In Figure 6-9, you will remember that we had given the relationships a verb phrase, which is used to read the name. For example, in the relationship between User and Message, we have two relationships. One of them was verb phrased as "Is Sent" as in User-Is Sent-Message. In order to get interesting usage of these verb phrases, I will use them as part of the name of the constraint, so that constraint will be named:

 FK__Attendees_MessagingUser$IsSent$Messages_Message

By doing this, it greatly improves the value of the names for constraints, particularly when you have more than one foreign key going between the same two tables. Now, let’s go through the seven constraints and decide the type of options to use on the foreign key relationships. First up is the relationship between AttendeeType and MessagingUser . Since it uses a natural key, it is a target for the UPDATE CASCADE option. However, it should be noted that if you have a lot of MessagingUser rows, this operation can be a very costly, so it should be done during off hours. And, if it turns out it is done very often, the choice to use a volatile natural key value ought to be reconsidered. We will use ON DELETE NO ACTION, because we don’t usually want to cascade a delete from a table that is strictly there to implement a domain.

 ALTER TABLE Attendees.MessagingUser

 ADD CONSTRAINT

 FK__Attendees_MessagingUser$IsSent$Messages_Message

  FOREIGN KEY (AttendeeType) REFERENCES Attendees.AttendeeType(AttendeeType)

  ON UPDATE CASCADE

  ON DELETE NO ACTION;

Next, let’s consider the two relationships between the MessagingUser and the UserConnection table. Since we modeled both of the relationships as required, if one user is deleted (as opposed to being disabled), then we would delete all connections to and from the MessagingUser. Hence, you might consider implement both of these as DELETE CASCADE. However, if you execute the following statements:

 ALTER TABLE Attendees.UserConnection

 ADD CONSTRAINT

 FK__Attendees_MessagingUser$ConnectsToUserVia$Attendees_UserConnection

 FOREIGN KEY (MessagingUserId) REFERENCES Attendees.MessagingUser(MessagingUserId)

 ON UPDATE NO ACTION

 ON DELETE CASCADE;

 ALTER TABLE Attendees.UserConnection

 ADD CONSTRAINT

 FK__Attendees_MessagingUser$IsConnectedToUserVia$Attendees_UserConnection

 FOREIGN KEY (ConnectedToMessagingUserId)

  REFERENCES Attendees.MessagingUser(MessagingUserId)

 ON UPDATE NO ACTION

 ON DELETE CASCADE;

Basically, this is stating that you cannot have two CASCADE operations on the same table. This even more limits the value of the CASCADE operations. Instead, we will use NO ACTION for the DELETE and will just have to implement the cascade in the client code or using a trigger. I will also note that, in many ways, this is probably a good thing. Too much automatically executing code is going to make developers antsy about what is going on with the data, and if you accidentally delete a user, having NO ACTION specified can actually be a good thing to stop dumb mistakes. So I will change the constraints to NO ACTION and re-create (dropping the one that was created first):

 ALTER TABLE Attendees.UserConnection

  DROP CONSTRAINT

 FK__Attendees_MessagingUser$ConnectsToUserVia$Attendees_UserConnection

 GO

 ALTER TABLE Attendees.UserConnection

  ADD CONSTRAINT

 FK__Attendees_MessagingUser$ConnectsToUserVia$Attendees_UserConnection

  FOREIGN KEY (MessagingUserId) REFERENCES Attendees.MessagingUser(MessagingUserId)

  ON UPDATE NO ACTION

  ON DELETE NO ACTION;

 ALTER TABLE Attendees.UserConnection

  ADD CONSTRAINT

 FK__Attendees_MessagingUser$IsConnectedToUserVia$Attendees_UserConnection

  FOREIGN KEY (ConnectedToMessagingUserId)

  REFERENCES Attendees.MessagingUser(MessagingUserId)

  ON UPDATE NO ACTION

  ON DELETE NO ACTION;

 Go

For the two relationships between MessagingUser and Message , you again might want to consider using ON DELETE CASCADE because if you delete a user, we will again want to get rid of all of that user’s messages. Note that we would do this here because we implemented a disabled indicator, and if the user really needed to be deleted, it is very likely that all of the messages would need to be deleted. If a reasonable user was disabled for quitting the service or repeatedly using the wrong access key, you wouldn’t delete them, and you would want to keep their messages. However, for the same reasons as for the previous constraints, you will have to do it manually if you decide to delete all of the messages, and that will help to give the user a choice if they have a lot of messages and maybe just need to be disabled.

 ALTER TABLE Messages.Message

  ADD CONSTRAINT FK__Messages_MessagingUser$Sends$Messages_Message FOREIGN KEY

 (MessagingUserId) REFERENCES Attendees.MessagingUser(MessagingUserId)

  ON UPDATE NO ACTION

  ON DELETE NO ACTION;

 ALTER TABLE Messages.Message

  ADD CONSTRAINT FK__Messages_MessagingUser$IsSent$Messages FOREIGN KEY

 (SentToMessagingUserId) REFERENCES Attendees.MessagingUser(MessagingUserId)

  ON UPDATE NO ACTION

  ON DELETE NO ACTION;

The next relationship we will deal with is between Topic and MessageTopic . We don’t want Topics to be deleted once set up to be used, other than by the administrator as a special operation perhaps, where special requirements are drawn up and not done as a normal thing. Hence, we use the DELETE NO ACTION.

 ALTER TABLE Messages.MessageTopic

  ADD CONSTRAINT

 FK__Messages_Topic$CategorizesMessagesVia$Messages_MessageTopic FOREIGN KEY

 (TopicId) REFERENCES Messages.Topic(TopicId)

  ON UPDATE NO ACTION

  ON DELETE NO ACTION;

The next to the last relationship to implement is the MessageTopic to Message relationship. Just like the Topic to Message topic relationship, there is no need to automatically delete messages if the topic is deleted.

 ALTER TABLE Messages.MessageTopic

  ADD CONSTRAINT FK_Message$iscCategorizedVia$MessageTopic FOREIGN KEY

 (MessageId) REFERENCES Messages.Message(MessageId)

  ON UPDATE NO ACTION

  ON DELETE NO ACTION;

One of the primary limitations on constraint-based foreign keys is that the tables participating in the relationship cannot span different databases. When this situation occurs, these relationship types need to be implemented via triggers.

It’s generally a bad idea to design databases with cross-database relationships. A database should be considered a unit of related tables that are always kept in sync. When designing solutions that extend over different databases or even servers, carefully consider how spreading around ­references to data that isn’t within the scope of the database will affect your solution. You need to understand that SQL Server cannot guarantee the existence of the value, because SQL Server uses databases as its “container,” and another user could restore a database with improper values, even an empty database, and the cross-database RI would be invalidated. Of course, as is almost always the case with anything that isn’t best-practice material, there are times when cross-database relationships are unavoidable, and I’ll demonstrate building triggers to support this need in the next chapter on data protection.

In the security chapter (Chapter 9), we will discuss more about how to secure cross database access, but it is generally considered a less than optimal usage. In SQL Server 2012, the concepts of contained databases, and even SQL Azure, the ability to cross database boundaries is changing in ways that will generally be helpful for building secure databases that exist on the same server.

Adding Basic Check Constraints

In our database, we have specified a couple of domains that need to be implemented a bit more strictly. In most cases, we can implement validation routines using simple check constraints. Check constraints are simple, single-row predicates that can be used to validate the data in a row. The basic syntax is

 ALTER TABLE <tableName> [WITH CHECK | WITH NOCHECK]

  ADD [CONSTRAINT <constraintName>]

  CHECK <BooleanExpression>

There are two interesting parts of check constraints that are different from what you have seen in previous constraints, and we need to cover these briefly before we start creating them. The first is <BooleanExpression>. The <BooleanExpression> component is similar to the WHERE clause of a typical SELECT statement, but with the caveat that no subqueries are allowed. (Subqueries are allowed in standard SQL but not in T-SQL). In T-SQL, you must use a function to access other tables, something I will use later in this book as I create more interesting check constraints to implement data protection patterns of Chapter 8.)

CHECK constraints can reference system and user-defined functions and use the name or names of any columns in the table. However, they cannot access any other table, and they cannot access any row other than the current row being modified (except through a function, and the row values you will be checking will already exist in the table). If multiple rows are modified, each row is checked against this expression individually.

The interesting thing about this expression is that unlike a WHERE clause, the condition is checked for falseness rather than truth. Without going completely into a discussion of NULL, it’s important to understand that CHECK constraints fail only on rows that are explicitly False. If the result of a comparison is UNKNOWN because of a NULL comparison, the row will pass the check constraint and be entered.

Even if this isn’t immediately confusing, it is often confusing when figuring out why an operation on a row did or did not work as you might have expected. For example, consider the Boolean expression value <> 'fred'. If value is NULL, this is accepted, because NULL <> 'fred' evaluates to UNKNOWN. If value is 'fred', it fails because 'fred' <> 'fred' is False. The reason for the way CHECK constraints work with Booleans is that if the column is defined as NULL, it is assumed that you wish to allow a NULL value for the column value. You can look for NULL values by explicitly checking for them using IS NULL or IS NOT NULL. This is useful when you want to ensure that a column that technically allows nulls does not allow NULLs if another column has a given value. As an example, if you have a column defined name varchar(10) null, having a check constraint that says name = 'fred' technically says name = 'fred' or name is null. If you want to ensure it is not null if the column NameIsNotNullFlag = 1, you would state (NameIsNotNullFlag = 1 and Name is not null) or (Name = 0).

The second interesting part of the statement is the [WITH CHECK | WITH NOCHECK] specification. When you create a CHECK constraint, the WITH CHECK setting (the default) gives you the opportunity to create the constraint without checking existing data. Using NOCHECK and leaving the values unchecked is a pretty bad thing to do, in my opinion. First off, when you try to resave the exact same data, you will get an error. Plus, if the constraint is built WITH CHECK, the optimizer could possibly make use of this fact when building plans if the constraint didn’t use any functions and just used simple comparisons such as less than, greater than, and so on. For example, imagine you have a constraint that says that a value must be less than 10. If, in a query, you look for all values 11 and greater, the optimizer can use this fact and immediately return zero rows, rather than having to scan the table to see whether any value matches.

In our model, we had two domains specified in the text that we will implement here. The first is the TopicName , which called for us to make sure that the value is not an empty string or all space characters. I repeat it here in Table 6-8 for review.

The maximum length of 30 characters was handled by the datatype of nvarchar(30) we used but now will implement the rest of the value limitations. The method I will use for this is to do a ltrim on the value and then check the length. If it is 0, it is either all spaces or empty. We used the topicName domain for two columns, the name column from Messages.Topic, and the UserDefinedTopicName from the Messages.MessageTopic table:

 ALTER TABLE Messages.Topic

  ADD CONSTRAINT CHK__Messages_Topic_Name_NotEmpty

  CHECK (LEN(RTRIM(Name)) > 0);

 ALTER TABLE Messages.MessageTopic

  ADD CONSTRAINT CHK__Messages_MessageTopic_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, we will get a bit more interesting.

 ALTER TABLE Attendees.MessagingUser

  ADD CONSTRAINT CHK__Attendees_MessagingUser_UserHandle_LenthAndStart

  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 had 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 CHK__Messages_MessageTopic_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 check criteria, 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 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. Sometimes, these values have meaning to the end users as well, but most often, we are implementing them strictly for to software’s sake, hence the reason that we will implement them in such a manner that the client cannot modify the values.

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 returned, since the actual insert will be done in the trigger, outside of the scope of the code. @@identity will work, but it is own issues, particularly with triggers that perform cascading operations.
• The output clause 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 ought to use one of the natural keys you have implemented to get the inserted value if you are inserting a single row anyhow.

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 hit on here is multirow operations. Well written triggers take into consideration that any INSERT, UPDATE, or DELETE operation must support multiple rows being operated on simultaneously. 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

 GO

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 SYSDATETIME().

 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, 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 from Messging User to Attendee Type:

 ALTER TABLE [Attendees].[MessagingUser]

  ADD CONSTRAINT [FK__Attendees_MessagingUser$IsSent$Messages_Message]

  FOREIGN KEY([AttendeeType])

  REFERENCES [Attendees].[AttendeeType] ([AttendeeType])

 ON UPDATE CASCADE;

Since this is 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 “instead of” triggers for all of the tables in our database. They will all follow the same 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, 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. In previous versions of SQL Server, it was difficult to make any use of this data directly in the server. In SQL Server 2000, Microsoft introduced extended properties that allow you to store specific information about objects. This is great, because 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.
• @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:

There’s some pretty cool value in there 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 a 7,500-character limitation, that is). 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, column, and schemas either. Constraints, databases, and many other objects in the database can have extended properties. For more information, check the SQL Server 2012 Books Online section “Using Extended Properties on Database Objects.”

Viewing the Basic 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 are a plethora of sys schema objects. However, they can be a bit messier to use and aren’t based on standards, 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 user the sys schema objects (commonly referred to as the system catalog) to get metadata as well. I will stick to the information schema as much as I can because they are based on the SQL standards.

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 automatically creates a schema for every user that gets created.

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 lenght

  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

which in our database, we have been working on throughout the chapter, returns: