Certifiably SQL: Building Tables

Tips for SQL Server certification exams

[Editor's Note: Each month, Certifiably SQL will cover topics you'll need to understand to get your certification in SQL Server Administration and Implementation or Data Warehousing with SQL Server. The column includes an explanation of the topic and some questions that approximate what you might see on the MCDBA exams.]

This month, I address one of the exam topics—building tables—from exam 70-29, the exam for the "Implementing a Database Design on SQL Server 7.0" course. In a forthcoming article, I'll discuss constraints and integrity issues. As always in SQL Server, you have two options for building tables: the GUI and the Query Analyzer. I look first at the GUI, and then consider the use of the Query Analyzer. The GUI has changed in SQL Server 7.0, but it offers the same functionality as in the previous release plus some new features, so making the switch won't take long.

Graphical Table Design

To start the table builder interface from Enterprise Manager, select the database, right-click it, and select New, Table. Or in the database, select Tables, right-click, and select New Table. Screen 1 shows the resulting Design Table window. First, put spaces in table names, especially if you plan to use the same table definitions in a database on a different relational database management system (RDBMS). Even if you supply a name, SQL Server opens the Design Table window with the caption put spaces in table names, especially if you plan to use the same table definitions in a database on a different relational database management system (RDBMS). Even if you supply a name, SQL Server opens the Design Table window with the caption New Table in dbname on servername because it has not yet saved the table with the name you specified.

Creating Columns

A table can have up to 1024 columns in SQL Server 7.0. A row can't span pages, so the upper limit of the row size, adding all the column widths, is 8060 bytes. Columns using the char, varchar, binary, and varbinary data types can be up to 8000 bytes. Column names can be up to 128 Unicode characters. In the graphical interface, type in the name of the column. (The note about spaces in object names also applies to the column names.) A new column defaults to character data, but you can select any available data type from a drop-down list. Some data types, such as char and binary, require that you specify a length, but SQL Server supplies a default value (which you can override). Other data types, such as integer and datetime, are fixed length. For decimal and numeric data, add the precision (the total number of digits) and scale (the number of digits after the decimal point). In SQL Server 7.0, the graphical table builder's default is to let a column contain nulls. Clear this check box to require the user to input a value for a column.

The table builder lets you specify a default value, which must match the data type, for each column. Also, you can select a column to be an identity column. The identity property means that the column automatically assigns values to rows as they are inserted, starting with the identity seed value and incrementing by the identity increment value. People often use this property to generate invoice numbers, order numbers, etc. An identity column can't contain null values and is often a primary key for the table.

New in SQL Server 7.0 is the option to designate a column as the RowGUID. A Globally Unique Identifier (GUID) is unique not only in this table but also on all SQL Servers. The GUID becomes useful when you look at some advanced features of SQL Server such as merge replication and OLAP Services. The GUID identifies the row and its origin. Merge replication adds a column of this type if it doesn't find one in a published table. And when you combine data from many sources into a data warehouse, you need to know where the data originated—especially if you're making decisions based on this data.

Table Properties

Add each column, with the appropriate data type and other values, to the table. You can designate one or more columns as the primary key for the table. The second icon on the Enterprise Manager toolbar, Table and Index Properties, brings up another dialog box, from which you can set the file group for the table and a file group for the text data, as you see in Screen 2. You can even change the table name here. Check constraints for table and columns is an option on this dialog box that I'll cover in another column. (Briefly, constraints limit the values or format of the data a user is allowed to input.) In the Relationships tab, you can set up primary-to-foreign key relationships between tables. You use the third tab, Indexes/Keys, to define your indexes on the table. Also, you can define the file group to place the index on. Some database designers prefer to place the tables and indexes on separate file groups on different physical disks to improve performance. (You can design the database storage in such a way that while one set of disk heads is retrieving the index values, another set of disk heads is starting to return data rows to the user.)

Generating an SQL Script

The graphical interface is great for building tables, but what if you have to recreate the table on either the same server or a different server? Fortunately, you don't need to repeat all the steps of typing column names and selecting options. SQL Server can generate an SQL script that you can run from a Query Analyzer window to regenerate a table or even a complete database. You can bring up the script-generating dialog boxes in several ways. You can select a table, then right-click, and select All Tasks, Generate SQL Scripts. The scripting dialog box opens, as you see in Screen 3, with only that table selected, but you can add other tables and database objects to the list. To generate a script for the entire database, right-click on Databases and select All Tasks, Generate SQL Scripts. By default, this approach scripts all the tables. You can turn on an option under the formatting tab to also generate scripts for the dependent objects that reference these tables. In either case, you can add other objects to the script, including indexes, views, triggers, stored procedures, and security settings for logins and database users. Save the script, preferably on another computer, in case disaster strikes. If nothing else, the script documents what you built using the GUI.

Modifying an Existing Table

Modifying a table is easy in SQL Server 7.0. Select the table, right-click, and choose Design Table. You can add columns, delete columns, and even change the column data types and sizes, with certain restrictions. Also, you can change the nullability of a column. When you change a table, SQL Server can generate a change SQL script for you. When rebuilding, run the script you generated, then run the change script to rebuild your database the way you want it. Or, you can edit the original script to reflect the changes.

Creating Tables with an SQL Script

Knowing how to read, interpret, and write SQL scripts is useful, even if you use the GUI most of the time. For recreating a database, a script is much easier than the typing-and-clicking option. For example, the script that creates the authors table in the Pubs database looks like this:

USE pubsGOCREATE TABLE dbo.authors (  au_id varchar(11) NOT NULL ,  au_lname varchar(40) NOT NULL ,  au_fname varchar(20) NOT NULL ,  phone char(12) NOT NULL ,  address varchar(40) NULL ,  city varchar(20) NULL ,  state char(2) NULL ,  zip char(5) NULL ,  contract bit NOT NULL )

This script simply builds the table; each line adds a column to the table. You can use Transact SQL (T-SQL) for creating indexes, adding constraints, and other features, all of which I'll cover in future columns.

Specify Nullability

The author table example script specifies NULL or NOT NULL for each column. Specifying nullability is a good habit to get into. SQL Server defaults to NOT NULL for all columns. However, ODBC and OLE DB applications set the null default to the ANSI standard, to allow nulls, which is why the GUI sets all the columns to allow nulls. (For more details on using NULLs, see SQL by Design, "The Reason for NULL," page 65.) If you rely on the defaults, remember that you can change the default nullability for the server, the database, or the session you used to run the script.

For more practice, see the sidebar, "Practice Questions: Building Tables." WebSQL subscribers can find a practice lab at the link to this article at http://www.sqlmag.com. Also at this site, you can find references to books that can help you acquire the knowledge you need for certification.