Creating Tables

Now that you've seen the various datatypes that SQL Server uses to store data, it's time to proceed with creating tables.

SQL Server 2000 supports creating tables through T-SQL, as well as the Table Designer in Enterprise Manager. Regardless of the tool you choose, creating a table involves naming the table, defining the columns, and assigning properties to the columns.

Naming Tables

When you create a table, it is always created in a database and is generally owned by the user creating the table. The combination of databasename.owner.tablename constitutes the table name. The members of the sysadmin, dbowner, and ddladmin roles can specify a different owner by using the owner's name explicitly in the CREATE command; for example:

CREATE TABLE yourdb.paul.employee 

The preceding code fragment specifies that the table employee, owned by user Paul, be created in the northwind database. This table is said to be in Paul's schema, which is a fancy database term for everything Paul has created. Because the user Steve could also have a table called employee in his schema, the owner must be specified when referencing the object (unless you are Paul or Steve, in which case the default would be your own schema). One way around this is to create all objects in the database in the database owner schema. Specifying dbo as the owner does this. The command would then look like this:

CREATE TABLE yourdb.dbo.employee 

Creating objects in the dbo schema is the recommended practice, as it not only simplifies things, it also has performance and security implications. If an object, such as a view, references another object, such as a table, and the two objects have different owners, permissions must be checked on each object.

By default, only members of the sysadmin, dbowner, and ddladmin roles have the CREATE TABLE permission. When a member of the sysadmin role or a login aliased to the dbo user creates a table, the owner, by default, is dbo. See Chapter 15, "Security and User Administration," for more information on permissions.

When naming tables, try to be descriptive without being too verbose. "Customers of Acme Limited" is a poor choice, but "customer" would be just fine. Standard table names must begin with an alphabetic character. Names should be singular, can be up to 128 characters, and can include letters, numbers, special characters, and spaces. Although including spaces and special characters is allowed, I recommend avoiding them. Also avoid using ANSI-92 or SQL Server reserved keywords. If keywords, spaces, or special characters other than the underscore (_) are included in a table name, all references to the name must use delimiters. The standard delimiter is the square bracket([]). You can also use quoted identifiers ("") if the option SET QUOTED_IDENTIFIER ON is enabled. Following is an example of a delimiter being used:

Select * from [order details] 
Renaming Tables

After creating a table, if you find you must rename it, you can do so using Enterprise Manager or the sp_rename stored procedure. In Enterprise Manager, right-click the table name and select Rename. This will allow you to edit the name of the table from the Details window. When you press Enter to accept the change, a dialog box warns you that changes to the table name could affect dependent objects such as stored procedures, triggers, and views, and presents you with the options to accept or reject the change or to view dependencies. I recommend that you opt to view the dependencies, and if you are satisfied that the name change won't invalidate other objects, then select Yes to accept the change. If you opt to use sp_rename, the syntax is EXEC sp_rename 'employee', 'emp'. This statement would change the name of the employee table to emp. Before you change the table name using this method, make sure you check for dependencies, either through the Enterprise Manager, or with the sp_depends stored procedure.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features