Identifiers

The names that you give to databases and database objects when you create them are called identifiers. You don’t have to supply an identifier with the same fanfare and excitement that you went through when your parents named you, but identifiers and how you use them are important.

Most of the time you need to supply an identifier at the instant you create, or define, an object. In some cases SQL Server does this for you. A good example of an object that automatically gets its own identifier from SQL Server is the default constraint object. In the next chapter I talk about referencing this identifier in your code.

The following code:

 CREATE TABLE CoffeeTable (KeyCol INT PRIMARY KEY, Wood NVARCHAR(8)) SELECT Wood FROM CoffeeTable . . . .

is an example of naming the table and then referencing it by the identifier in the same stroke.

Often it becomes necessary to delimit identifiers with open/close square brackets ([]) when the identifiers do not conform to the rules for well-formed identifiers in T-SQL. For example, the table ImportedCoffee is fine, but Imported Coffee needs to be delimited.

It also makes good business sense as a T-SQL programmer to help the optimizer reuse execution plans and connecting identifiers as a SQL Server database namespace is important. In the following code examples both styles are accepted by SQL Server, but the latter is preferred:

 --one way USE Northwind SELECT CustomerID FROM Customers --better way SELECT CustomerID FROM Northwind.dbo.Customers

As discussed earlier, the at sign (@) at the beginning of an expression is an identifier reserved for SQL Server use, as are the pound (#) sign, the dollar ($) sign, and the underscore (_). You cannot use pound as a subsequent character, but @, $, and underscore will fly.