TABLE

To create a table, SQL Server uses the ANSI SQL standard CREATE TABLE syntax.

At the basic level, creating a table requires little more than knowing what you want to name it, what columns it contains, and what range of values (domain) each column can store.

Here’s the basic syntax for creating the customer table in the dbo schema, with three fi xed-length character (char) columns. (Note that this table defi nition isn’t necessarily the most effi cient way to store data because it always requires 46 bytes per entry for data plus a few bytes of overhead, regardless of the actual length of the data.)

CREATE TABLE dbo.customer

(

name char(30),

phone char(12),

emp_id char(4)

);

This example shows each column on a separate line for readability. As far as the SQL Server parser is concerned, white spaces created by tabs, carriage returns, and the spacebar are identical. From the system’s standpoint, the following CREATE TABLE example is identical to the preceding one, but it’s harder to read from a user’s standpoint:

CREATE TABLE customer (name char(30), phone char(12), emp_id char(4));

A table is always created within one schema of one database. Tables also have owners, but unlike in versions of SQL Server prior to 2005, the table owner is not used to access the table. The schema is used for all object access. Normally, a table is created in the default schema of the user who is creating it, but the CREATE TABLE statement can specify the schema in which the object is to be created. A user can create a table only in a schema for which the user has ALTER permissions.

Any user in the sysadmin, db_ddladmin, or db_owner roles can create a table in any schema. A database can contain multiple tables with the same name, so long as the tables are in different schemas. The full name of a table has three parts, in the following form:

       Database_name.schema_name.table_name

The first two parts of the three-part name specification have default values. The default for the name of the database is whatever database context in which you’re currently working. The schema_name actually has two possible defaults when querying. If no schema name is specified when you reference an object, SQL Server first checks for an object in your default schema. If there is no such table in your default schema, SQL Server then checks to see if there is an object of the specified name in the dbo schema.

Note: To access a table or other object in a schema other than your default schema or the dbo schema, you must include the schema name along with the table name. In fact, you should get in the habit of always including the schema name when referring to any object in SQL Server 2008.

Not only does this remove any possible confusion about which schema you are interested in, but it can lead to some performance benefits.

The sys schema is a special case. For compatibility views, such as sysobjects, SQL Server accesses the object in the sys schema prior to any object you might have created with the same name.

Obviously, it is not a good idea to create an object of your own called sysobjects, as you will never be able to access it. Compatibility views can also be accessed through the dbo schema, so the objects sys.sysobjects and dbo.sysobjects are the same. For catalog views and Dynamic Management Objects, you must specify the sys schema to access the object.

You should make column names descriptive, and because you’ll use them repeatedly, you should avoid wordiness. The name of the column (or any object in SQL Server, such as a table or a view) can be whatever you choose, so long as it conforms to the SQL Server rule for regular identifiers: it must consist of a combination of 1 through 128 letters, digits, or the symbols #, $, @, or _.

 In some cases, you can access a table using a four-part name, in which the first part is the name of the SQL Server instance. However, you can refer to a table using a four-part name only if the SQL Server instance has been defi ned as a linked server.

Reserved Keywords

Certain reserved keywords, such as TABLE, CREATE, SELECT, and UPDATE, have special meaning to the SQL Server parser, and collectively they make up the SQL language implementation. You should avoid using reserved keywords for your object names. In addition to the SQL Server reserved keywords, the SQL-92 standard has its own list of reserved keywords. In some cases, this list is more restrictive than the SQL Server list; in other cases, it’s less restrictive.

Watch out for the SQL-92 reserved keywords. Some of the words aren’t reserved keywords in SQL Server yet, but they might become reserved keywords in a future SQL Server version. If you use a SQL-92 reserved keyword, you might end up having to alter your application before upgrading it if the word becomes a SQL Server reserved keyword.

Delimited Identifiers

You can’t use keywords in your object names unless you use a delimited identifier. In fact, if you use a delimited identifi er, not only can you use keywords as identifiers, but you can also use any other string as an object name—whether or not it follows the rules for identifiers.

This includes spaces and other non-alphanumeric characters that are normally not allowed.

Two types of delimited identifiers exist:

■ Bracketed identifiers, which are delimited by square brackets ([object name])

■ Quoted identifiers, which are delimited by double quotation marks (“object name”)

You can use bracketed identifiers in any environment, but to use quoted identifiers, you must enable a special option using SET QUOTED_IDENTIFIER ON. If you turn on QUOTED_IDENTIFIER, double quotes are interpreted as referencing an object. To delimit string or date constants, you must use single quotes.

Let’s look at some examples. Because column is a reserved keyword, the fi rst statement that follows is illegal in all circumstances. The second statement is illegal unless QUOTED_IDENTIFIER is on. The third statement is legal in any circumstance:

CREATE TABLE dbo.customer(name char(30), column char(12), emp_id char(4));

CREATE TABLE dbo.customer(name char(30), "column" char(12), emp_id char(4));

CREATE TABLE dbo.customer(name char(30), [column] char(12), emp_id char(4));

The SQL Native Client ODBC driver and SQL Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. You can configure this in ODBC data sources, ODBC connection attributes, or OLE DB connection properties. You can determine whether this option is on or off for your session by executing the following query:

SELECT quoted_identifier

FROM sys.dm_exec_sessions

WHERE session_id = @@spid;

A result value of 1 indicates that QUOTED_IDENTIFIER is ON. If you’re using Management Studio, you can check the setting by running the preceding command in a query window or by choosing Options from the Tools menu and then expanding the Query Execution/SQL Server node and examining the ANSI properties information.

Tip: Technically, you can use delimited identifiers with all object and column names, so you never have to worry about reserved keywords. However, I don’t recommend this. Many third-party tools for SQL Server don’t handle quoted identifiers well, and they can make your code difficult to read.

Using quoted identifiers might also make upgrading to future versions of SQL Server more difficult.

Rather than using delimited identifiers to protect against reserved keyword problems, you should simply adopt some simple naming conventions. For example, you can precede column names with the first few letters of the table name and an underscore. This naming style makes the column or object name more readable and also greatly reduces your chances of encountering a keyword or reserved word conflict.

Naming Conventions

Many organizations and multiuser development projects adopt standard naming conventions. This is generally a good practice. For example, assigning a standard moniker of cust_id to represent a customer number in every table clearly shows that all the tables share common data. If an organization instead uses several monikers in the tables to represent a customer number, such as cust_id, cust_num, customer_number, and customer_#, it won’t be as obvious that these monikers represent common data.

One naming convention is the Hungarian-style notation for column names. Hungarian-style notation is a widely used practice in C programming, whereby variable names include information about their data types. This notation uses names such as sint_nn_custnum to indicate that the custnum column is a small integer (smallint of 2 bytes) and is NOT NULL (Doesn’t allow nulls). Although this practice makes good sense in C programming, it defeats the data type independence that SQL Server provides; therefore, I recommend against using it.

Data Types

SQL Server provides many data types, most of which are straightforward. Choosing the appropriate data type is simply a matter of mapping the domain of values you need to store to the corresponding data type. In choosing data types, you want to avoid wasting storage space while allowing enough space for a suffi cient range of possible values over the life of your application.