Relational Databases 101—Identifiers

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

Identifiers

The database, its "owner" (the user or schema that created the object), the table, and the columns are all referenced (addressed) using SQL Server identifier object names. These names can be up to 128 bytes in length, but I generally keep the names short. I don't encourage anyone to embed spaces in the name, as it trips up the tools and your code—I also won't support you if you do. Yes, you can name your column Customer Last Name, but you'll need to surround this column name (or any object name that contains spaces) with square brackets: [Customer Last Name]. Most of the tools do this anyway to protect themselves from folks that insist on using embedded spaces. I'm not nearly as tolerant. I prefer to separate these long names using the underscore (_) character or by using CamelCase, as in CustomerLastName.

When addressing a table in SQL Server and the server is named Fred\SS1, the database is Biblio and the schema5 is Dev1, you could address the Sales column in the Customers table by using the following identifier:

Fred1\SS1.Biblio.Dev1.Sales.Customers

Identifiers are case-sensitive only if you install your server in case-sensitive mode—I rarely do and I never encourage customers to do so. Installing an SQL Server as non-case-sensitive means you can define your columns using your company's standard naming convention and not have to worry about the case.

No, you won't be able to use special characters such as "-[]{}\|;:'"<,>.!@#$%^&*( )+=" in any identifier. You'll also discover that there is a long list of "reserved" keywords that can't (should not) be used as object identifiers. This means you can't call a database Authorization, name a column Sort, or name a Table Select. It also turns out that the ANSI SQL standards body has defined even more names that are not yet reserved words in SQL Server. I would stay away from these, too. Actually, if you create compound names separated by an underscore (_) character, you should be safe with virtually any name. When I get to naming stored procedures a bit later, I'll also show why using sp_ as a prefix for a stored procedure name is a bad idea—it forces the server to search for your stored procedure in the master database before looking in the current catalog.