Naming Objects
There are a few things to watch out for as you name databases, tables, columns, or any other object in the database. In TSQL jargon, object names are called "identifiers," in case you want to look this up in BOL. These identifiers are created when the object is created and stored in the bowels of the master or user database. A handy place to find these names is the sysobjects table—if it still exists. The identifier specification breaks objects down into two groups: "regular" and "delimited" identifiers. The only real difference is that if the identifier does not comply with the rules for creating identifiers, it must be bracketed with double quotes or the bracket ([ ]) symbols. For example, "This is a column name" and [This is another column name] are delimited identifiers.
- Object names must be unique in their scope. That is, database names must be unique, table names must be unique within a database, column names must be unique within a table, and so on.
IMHO - I suggest you code table names plural. For example, "Customers", "Orders", "Addresses".
- The first character must be a letter from a to z or A to Z, or the underscore (_), "at" sign (@), or number sign (#). Yes, you can use Unicode8 letter characters.
8 Unicode Standard 2.0.
- Subsequent characters in an identifier can be any Unicode letter, decimal numbers, the underscore (_), "at" sign (@), or number sign (#).
- Don't use embedded spaces in object names. Yes, you can define table and other object names that contain spaces in Access and in some of the tools (including Visual Studio), but SQL Server frowns on it. The problem is that every step along the way, you'll wish you had taken the time to remove the spaces. Each time you define a SQL query, you'll have to remember to bracket the long name so the TSQL parser and the development tools and wizards won't get confused.
- Stay away from reserved words. This means you can't use the word "Name" to refer to a customer's name—not without taking special care9 when you write your TSQL queries. Every time SQL Server ships, the reserved words list grows longer. Microsoft actually includes a list of words they plan to reserve in future versions, so it's not a good idea to use these, either. Look up "Reserved Keywords in TSQL " in BOL to get a complete list. It's usually (but not always) safe to concatenate two words together with an underscore, such as "Name_Like".
9 Look up "Quoted Identifiers" for more information. It's actually easier to avoid using these names in the first place.
- Capitalization does not matter in TSQL identifiers—unless you configure your server to be case-sensitive10. I often define identifiers using CamelCase notation, where each word in the identifier is capitalized. This improves readability and helps get around the reserved word restrictions. Capitalization does matter with CLR object names—but I'll get to that later.
10 It's not necessary to configure your server in case-sensitive mode anymore. You can write individual queries or define specific columns to be case-sensitive.
- Most types of identifiers have length restrictions. If you stay under 117 characters for identifiers, you'll stay on safe ground.
Note - When naming stored procedures, don't begin the name with "sp_". Doing so tells the server that the procedure is a "system" procedure, so it takes longer to locate the object.
Tables contain one or more columns whose properties define what's to be stored therein and how the table is to be addressed when you want to return data from the table. The basic properties include:
- The column name (identifier): I suggest choosing a name (without spaces) that clearly describes the contents of the column. The name cannot be longer than 128 characters. There are many schools of thought that dictate how tables and columns should be named, and if you work in a shop of any size at all, you'll find that your development team has already settled on a standard of some kind. It could be a "Hungarian" convention that dictates that the first few letters specify the datatype (intDaysInProduction), or some convention that your development team has adopted. Timestamp columns should be named "timestamp".
- The column datatype: This determines how much space the column consumes in the database, and the "type" of data it's permitted to store. No, choosing the right datatype is not nearly as important as it was for typical DBMS implementations, as hard disks are far larger than ever; and for small databases, space considerations should be well down on the list of concerns. However, for larger databases or those with high-volume demands, reducing the size of column footprint can help performance. In any case, I usually define the datatype to handle strings, numbers, graphics, or XML. I'll discuss the rudiments of choosing the right datatype in the next few pages.
- Specifying a user-defined datatype: When I create tables, I sometimes do so by specifying custom, user-defined datatypes. This way, I can define rules and defaults on these columns that apply to the entire database. See the discussion on UDTs, rules, and defaults later in this chapter. They are also discussed in Chapter 2.
- If the column is permitted to accept NULL values: If you expect to store information that might not be known as the row is added (like "Date_Married"), you need to define the column as permitting NULLs. However, you can't define a column that's going to be the table's primary key as permitting a NULL value.
- Is the column the primary key? If this column is the primary key (or one of the columns that together constitute the primary key), you can so indicate. You can also indicate if the PK is to be kept unique within the table.
- Is the column value to be generated as an "Identity" value? As described earlier in this chapter, SQL Server can automatically generate a primary key value for your table—just request that the column be designated as IDENTITY.
- Is the column value to contain a GUID? In this case, request that the server designate the column as ROWGUIDCOL (using the uniqueidentifier datatype) —you might want to generate the GUID yourself as new rows are added, but it's easier to use the NEWID function as the default column value.
- How should the column be collated? You can specify the dictionary order, case-sensitivity, and accent-sensitivity (especially if it is different from the collation specified for the database). This means you can define columns holding a name as case-sensitive and others as non-case-sensitive (or leave them to default to the database collation sequence). The collation also determines how the data is sorted. This is important for anyone working with Unicode data or character sets that don't sort the same way as the database default. See "Using SQL Collations" in BOL for more information.
- What action should be taken when a row is deleted or updated? By setting the ON DELETE and ON UPDATE attributes, you can get SQL Server to implement cascading deletes or updates.
Sure, there are many other options you can specify as you define your table, but the options shown here are enough to get you started. This process needs to be repeated for each column in the table and for each table in the database.
Frankly, I expect that most of you will use the Visual Studio or Management Studio tools to define tables. You'll find it's pretty easy to define your tables, primary keys, and relationships using the interactive Database Diagram tool in Visual Studio. Your other alternative is to figure out which TSQL or SMO commands are required to configure a new table (or alter an existing table). If you're getting paid by the hour, this is your best bet. All kidding aside, some folks really like the approach of creating scripts to record how their tables are defined. Fortunately, the tools can do that, too—they can take an existing database and write a file that includes all of the TSQL needed to build it up from scratch. Sure, you're going to have to add data on your own.
Using User-Defined Types, Rules, and Defaults
In SQL Server, non-CLR UDTs are pretty straightforward—they're simply aliases to the base types11. This way, you can define a UDT for "PostalCode" (based on a varchar(11) and specify the PostalCode UDT when the table is created. Once defined, a UDT can be assigned a global default. That is, when a new row is added to the table and no value is supplied, SQL Server substitutes the registered default for the column value and any other columns defined with the UDT.
11 CLR user-defined types are far more complex. I'll defer the discussion of those to Chapter 13.
In a similar manner, you can also define SQL Server rules12 or (better yet) check constraints for specific columns or to UDTs, as I discussed in Chapter 2. These constraints are used to implement your business rules—they define what's permissible in a specific column and what's not. For example, you know (based on how you run your business) that customer discounts can range from 0% to 15% and correct shipping delays are between 1 and 90 days. Setting up SQL Server rules to enforce these business rules is fairly simple—check constraints are a bit harder. Both rules and constraints can be any expression valid in a WHERE clause and can include such elements as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). However, the constraints cannot reference columns or other database objects. Let's walk through the process of creating a new UDT (alias) and associated check constraints.
12 According to BOL, CREATE RULE will be removed in a future version of SQL Server. Microsoft suggests that I avoid using CREATE RULE in new development work and plan to modify applications that currently use it. I don't think Microsoft can drop rules anytime soon without causing a riot, so I wouldn't worry too much just yet.
Start by creating a new User-Defined data type in the database by using the SQL Server Management Studio wizard that starts when you right-click on User-defined Data Types | New User-defined Data Type, as shown in Figure 3.5.
