Creating and Altering Tables

The first step in the databasedesign process is creating the entity relationship model, which is a conceptual representation of the database. The entity relationship model is comprised of entities, attributes, and relationships. An entity represents a real-world object, such as cars, employees, orders, students, courses, and teachers. Each entity has characteristics,which are called attributes. For example, the entity called employee has these attributes: Social Security number (SSN), last name, and first name. The last piece is the relationship, which is a link between two or more tables. There are three types of relationships: one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N). For example, there's a one-to-many relationship between employees and orders because one employee can place many orders, and an order can be placed by just one employee.

The study of entity-relationship models is out of the scope of this book. However, it's important that you understand that entity-relationship modeling is the core of database design, and that good applications cannot be developed without a good database design.

After the entity-relationshipmodel is finished, the next step is to convert it into the database structure. Specifically, a new table is created to represent each entity, and the table will have as many columns as attributes in the entity. Also, a table is created to represent each many-to-many relationship. The columns of this table will be the primary keys of the tables involved in the many-to-many relationship.

Some CASE (computer-aided software engineering)tools out there transform an entity-relationship model into a script that you can run against a database server to create the database schema.

In SQL Server, the smallest unit of storage is the page. A page is 8KB in size and can store one or more rows; however, a row cannot span pages. For this reason, a single row can store up to 8,060 bytes, unless you use TEXT, NTEXT, or IMAGE data types, which are stored separately in their own pages.

Each group of eight pages formsanextent, which is the unit of storage of tables and indexes. There are two types of extents: uniform and mixed. A uniform extent stores data for only a single object, whereas a mixed extent stores multiple objects'data. In other words, a uniform extent is a set of eight pages (64KB) that stores a single object's (table or index) data (see Figure 3.1). Refer to Chapter 6, "Optimizing Access to Data: Indexes," for more information on data storage.

Types of Tables

In versions of SQL Serverprior to SQL Server 2000, there were only two types of tables:permanent and temporary. The TABLE data type is a new feature of SQL Server 2000 that we can add to our set of tools.

Permanent Tables

Permanent tables store the actual data in a database. These are the tables you create as a result of the conversion of the entity relationship model to a database structure.

Permanent tables are storedin the database where they are created. There are system tables (sysobjects, syscolumns, and sysconstraints) that keep track of the configuration information of tables such as owner, creation date, name and type of each column, and constraints defined on the table, among others.

System tables are permanent tables that are created automatically by SQL Server at installation time. Their name begins with sys—for example, sysobjects. The purpose of system tables is to store metadata of databases, such as objects in the database (tables, views, stored procedures, extended stored procedures, and user-defined functions), users, roles, permissions, data and log files, and so on. By default, users cannot insert or modify the data in system tables. If you want to modify the data in system tables— which is not recommended—use the sp_configure system stored procedure to enable the 'allow updates' configuration option. Listing 3.1 shows how to enable this option.

Code Listing 3.1. Using sp_configure to Allow Modifications to System Tables

							
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

One of the most important system tables in every database is sysobjects, which stores information of every object in a database. The object type is stored in the type column of sysobjects. Table 3.1 lists the possible values of the type column of the sysobjects system table.

For example, if you want to list all system tables in the Northwind database, you can query sysobjects and filter by the 'S' type, as shown in Listing 3.2.

Code Listing 3.2. Listing System Tables in the Northwind Database

							
USE Northwind

SELECT name,crdate
FROM sysobjects
WHERE type = 'S'

 name                 crdate
-------------------- ---------------------------
sysobjects           2000-04-18 01:51:58.910
sysindexes           2000-04-18 01:51:58.910
syscolumns           2000-04-18 01:51:58.910
systypes             2000-04-18 01:51:58.910
syscomments          2000-04-18 01:51:58.910
sysfiles1            2000-04-18 01:51:58.910
syspermissions       2000-04-18 01:51:58.910
sysusers             2000-04-18 01:51:58.910
sysproperties        2000-04-18 01:51:58.910
sysdepends           2000-04-18 01:51:58.910
sysreferences        2000-04-18 01:51:58.910
sysfulltextcatalogs  2000-04-18 01:51:58.910
sysindexkeys         2000-04-18 01:51:58.910
sysforeignkeys       2000-04-18 01:51:58.910
sysmembers           2000-04-18 01:51:58.910
sysprotects          2000-04-18 01:51:58.910
sysfulltextnotify    2000-04-18 01:51:58.910
sysfiles             2000-04-18 01:51:58.910
sysfilegroups        2000-04-18 01:51:58.910

(19 row(s) affected)

Temporary Tables

Temporary tables, like any other temporary object in SQL Server, are stored in tempdb and dropped automatically by SQL Server if they are not dropped explicitly. This type of table is used as a temporary working area for many purposes, such as multistep calculations and, also, to split up large queries.

There are two types of temporary tables: local and global. The name of local temporary tables begins with #, whereas the name of global temporary tables begins with ##. Local temporary tables are available only in the connection that created them, and when the connection is finished, the table is automatically dropped by SQL Server, unless it is dropped explicitly using DROP TABLE. This type of table is very useful for applications that run more than one instance of a process simultaneously, because each connection can have its own copy of the temporary table, without interfering with the other connections executing the same code. For example, if you create a stored procedure that uses temporary tables, every user or application running the stored procedure will have its own copy of these temporary tables.

On the other hand, global temporary tables are available to all connections in SQL Server. Therefore, when a connection creates a global temporary table, and other connections reference the table, they will be accessing the same table. Global temporary tables last until the connection that created them finishes its execution.

Table Variables

In previous versions of SQL Server,temporary tables were the only way to store temporary data or result sets. In SQL Server 2000, the TABLE data type can be used for this purpose. This new data type is more efficient than temporary tables because it is stored in memory, whereas a temporary table is stored in tempdb.

Regarding scope, the TABLE data type is similar to local temporary tables, which have only local scope. As a result, any variable that uses the TABLE data type is available only in the session where the variable is used, and if this session calls a stored procedure, for example, table variables are not visible inside the stored procedure, whereas temporary tables are visible.

To define a variable whose data typeis TABLE, use the DECLARE statement specifying TABLE as the data type followed by the table structure. After declared, it is treated like any other table in SQL Server. Listing 3.3 shows how to declare a variable that uses the TABLE data type. This example also inserts a row in the table, and then gets all rows on it.

Code Listing 3.3. Using the TABLE Data Type

							
DECLARE @employees TABLE (ssn INT, firstname VARCHAR(20), lastname VARCHAR(30))

INSERT @employees (ssn, firstname, lastname)
VALUES ('555555555','Rojas','Yumaira')

SELECT *
FROM @employees
(1 row(s) affected)

 ssn         firstname            lastname
----------- -------------------- ------------------------------
555555555   Rojas                Yumaira

(1 row(s) affected)

Table variables can also be used as the return value of user-defined functions. This will be covered in Chapter 10, "Enhancing Business Logic: User-Defined Functions (UDF)."

Creating Tables

To create a table, you must specify the table's name, the columns that make up the table, and the data types of the columns. You can create tables using a graphical interface in Enterprise Manager or using Transact-SQL in Query Analyzer. The Transact-SQL statement used is CREATE TABLE,and the syntax is

CREATE TABLE Table_name (
column_1 data_type,
column_2 data_type,
.
.
column_n data_type
)

Listing 3.4 shows an example that creates the Drivers table, which contains three columns: license, firstname, and make.

Code Listing 3.4. Creating Tables Using Transact-SQL

						
USE Northwind

CREATE TABLE Drivers (
license VARCHAR(15),
firstname VARCHAR(30),
lastname VARCHAR(30)
)

In SQL Server, a table can have up to 1,024 columns and a database can contain up to 2,147,483,647 objects, including tables. As you already know, the maximum size of a row in a table is 8,060 bytes. Nonetheless, you can still create tables that have columns of variable data types (VARCHAR, NVARCHAR, and VARBINARY) whose row size exceeds 8,060 bytes. In these cases, SQL Server creates the table, but it gives you a warning. Be careful when creating these tables because you won't have all this space available. For example, suppose you create a table that has two columns which use the VARCHAR data type, and the length is 5,000 bytes on each column. You won't have 10,000 bytes available for each row—you will have only 8,060; therefore, if you try to insert a row with more than 8,060 bytes, you will get an error. Listing 3.5 shows this example and the warning message.

Code Listing 3.5. Creating Tables with a Row Size That Exceeds 8,060 Bytes

						
USE Northwind

CREATE TABLE Bigtable(
firstcolumn VARCHAR(5000),

secondcolumn VARCHAR(5000)
)
GO
Warning: The table 'Bigtable'has been created but its maximum row size
(10025) exceeds the maximum number of bytes per row (8060). INSERT or
UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.

When a table is created, SQL Server automatically adds a new row to sysobjects (in the local database) with the information about the newly created table. Also, a new row is added to syscolumns for each column in the table. Be aware that dealing directly with system tables is not recommended, because their functionality might change in future versions of SQL Server. The alternative is to use the INFORMATION_SCHEMA views (INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES in this case), which, by the way, are ANSI standard. Moreover, you can use the system stored procedure sp_help to show information of any object in SQL Server, including tables.

Listing 3.6 demonstrates the use of sp_help to get a table's information. Notice that, for reasons of space, two columns were removed from the output of sp_help (TrimTrailingBlanks and FixedLenNullInSource).

Code Listing 3.6. Displaying a Table's Information

						
USE Northwind
GO
sp_help 'Drivers'

Name            Owner     Type             Created_datetime
--------------- --------- ---------------- ---------------------------------
 Drivers         dbo       user table       2000-11-16 02:10:31.320


Column_name  Type     Computed  Length  Prec Scale Nullable 
------------ -------- --------- ------- ---- ----- ---------
license      varchar  no        15                 yes      
firstname    varchar  no        30                 yes      
lastname     varchar  no        30                 yes      


Identity                        Seed      Increment    Not For Replication
------------------------------- --------- ------------ -------------------
No identity column defined.     NULL      NULL         NULL

RowGuidCol
--------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------
PRIMARY


The object does not have any indexes.

No constraints have been defined for this object..

No foreign keys reference this table.
No views with schema binding reference this table.

As already stated, another option to get an object's metadata is using INFORMATION_SCHEMAviews. Basically, metadata is information about the object and its components. The advantages of using these views is that, first, they're ANSI compatible (SQL-92 Standard), and second, they are independent of system tables, in the way that if the functionality or schema of system tables changes in future versions, these views will still be supported. Listing 3.7 shows how to get a table's metadata (the Drivers table) using two INFORMATION_SCHEMA views: TABLES and COLUMNS.

Code Listing 3.7. Using INFORMATION_SCHEMA Views to Get Metadata

						
USE Northwind

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'Drivers'

SELECT *.
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Drivers'

GO

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  TABLE_TYPE
-------------- ------------- ----------- ----------
Northwind      dbo           Drivers     BASE TABLE

(1 row(s) affected)

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  COLUMN_NAME  ORDINAL_POSITION DEFAULT
-------------- ------------- ----------- ------------ ---------------- -------
Northwind      dbo           Drivers     license      1                NULL
Northwind      dbo           Drivers     firstname    2                NULL
Northwind      dbo           Drivers     lastname     3                NULL

(3 row(s) affected)

After a table is created, it must be populated. If you want to know how much space a table is using, use the sp_spaceused system stored procedure. It takes the table's name as the parameter and displays the number of rows and the used space. Listing 3.8 shows space information of the Employees table in the Northwind database.

Code Listing 3.8. Displaying Space Information of Tables

						
USE Northwind
.
EXEC sp_spaceused 'Employees'
GO

name          rows    reserved   data       index_size    unused
------------- ------- ---------- ---------- ------------- ------------------
Employees     9       320 KB     232 KB     48 KB         40 KB

Any object created by users, including tables, can be renamed using the sp_rename system stored procedure, which has three arguments (the last one is optional). The first parameter is the old name of the object, the second is the new name, and the third is the type of object to rename: column, database, index, and userdatatype. When renaming tables the third parameter is not necessary, but it is required to rename columns. When renaming columns, in the first parameter the name of the table that contains the column must be specified, using the following syntax: 'table_name.column_name'.

User databases can be renamed if you specify database as the third parameter. Another system-stored procedure, sp_renamedb, is used only to. rename user databases.

Listing 3.9 renames the Drivers table to Safedrivers, and then renames the column license to licensenumber.

Code Listing 3.9. Renaming User Objects Using sp_rename

						
USE Northwind

EXEC sp_rename 'Drivers','Safedrivers'
GO

EXEC sp_rename 'Safedrivers.license','licensenumber','column'
GO

Caution: Changing any part of an object name could break
scripts and stored procedures.
The object was renamed to 'Safedrivers'.
Caution: Changing any part of an object name could break
scripts and stored procedures.
The column was renamed to 'licensenumber'.

In tables, columns can allow null values, which represent unknown values. A null doesn't mean that the value is either 0 for numbers or a zero-length string for characters, it just means unknown or undefined. The nullability of a column is specified when the table is created and can be modified by altering the properties of the column.

By default, Query Analyzer assumes that columns allow nulls if the nullability status is not specified when tables are created using Transact-SQL. This is due to the fact that Query Analyzer automatically activates ANSI settings every time a connection is made to SQL Server (the statement used for this setting is SET ANSI_NULL_DFLT_ON).

In particular, when you create tables using the CREATE TABLE statement, the nullability status is specified after the column data type. If the column allows null values, use the NULL keyword; otherwise, use NOT NULL. For example, if you take a look at Listing 3.4, which creates the Drivers table, it doesn't specify the nullability status for any of the columns, and this is why all of them allow null values (this can be seen in the output of Listing 3.6).

Listing 3.10 illustrates how to explicitly specify the nullability status of columns when creating a table. It also executes sp_help on this table to show whether each column is nullable.

Code Listing 3.10. Specifying Nullability Status When Creating Tables

						
USE Northwind

CREATE TABLE Cars (
serial VARCHAR(200) NOT NULL,
make VARCHAR(100) NOT NULL,
model VARCHAR(100) NOT NULL,
color VARCHAR(50) NULL
)

EXEC sp_help 'Cars'
GO

Name       Owner     Type             Created_datetime
---------- --------- ---------------- ------------------------------
Cars       dbo       user table       2000-11-19 16:06:09.947


Column_name    Type        Computed    Length    Prec  Scale Nullable
-------------- ----------- ----------- --------- ----- ----- ----------
serial         varchar     no          200                   no
make           varchar     no          100                   no
model          varchar     no          100                   no
color          varchar     no          50                    yes


Identity                       Seed     Increment   Not For Replication
------------------------------ -------- ----------- -------------------
No identity column defined.    NULL     NULL        NULL

RowGuidCol
--------------------------------
No rowguidcol column defined.

Data_located_on_filegroup
--------------------------------
PRIMARY

The object does not have any indexes.

No constraints have been defined for this object.


No foreign keys reference this table.
No views with schema binding reference this table.

A table should always have a primary key, which is a column or set of columns that uniquely identifies each row in a table. For example, the Social Security number can be the primary key in the Employees table because there aren't two employees with the same Social Security number. Primary keys are part of the integrity of tables (entity integrity), and this will be covered in Chapter 7, "Enforcing Data Integrity."

In case a table doesn't have aninherent primary key, the IDENTITY property can be used, which is basically a number that auto-increments by itself and cannot be NULL. The IDENTITY property is similar to the AUTONUMBER data type in Access. A seed and an increment can be specified with the IDENTITY column when creating a table. If they're not specified, the default value is 1 for each of them.

Listing 3.11 creates the Dealers table, which contains an IDENTITY column, dealerid, with a seed of 10 and increment of 1.

Code Listing 3.11. Usingthe IDENTITY Property

						
USE Northwind

CREATE TABLE Dealers(
dealerid INT IDENTITY(10,1) NOT NULL,
dealername VARCHAR(100) NOT NULL,
address VARCHAR(200) NULL,
city VARCHAR(100) NULL,
state CHAR(2) NULL,
zipcode VARCHAR(10) NULL
)
GO

In SQL Server, only one IDENTITY column is allowed per table, and it cannot be updated. Also, because the value of an IDENTITY column is automatically incremented every time a new row is inserted, you don't need to specify this column when inserting data in the table. However, if you want to insert a specific value in an IDENTITY column, use the SET IDENTITY_INSERT statement. The syntax of this statement is the following:

SET IDENTITY_INSERT Name_of_the_table { ON|OFF}

Every time you activate IDENTITY_INSERTin a table, don't forget to deactivate it (using the OFF keyword in the SET IDENTITY_INSERT statement) after values are explicitly inserted in the IDENTITY column. Listing 3.12 shows how to insert data in the IDENTITY column of the Dealers table. In the first INSERT statement the dealerid column is not specified, therefore, the next identity value is used (1 in this case). In the second INSERT statement, a value of 8 is explicitly inserted in the dealerid column (notice the use of SET IDENTITY_INSERT).

Code Listing 3.12. Inserting Data in IDENTITY Columns

						
USE Northwind

INSERT Dealers (dealername) VALUES ('Acme BMW')
GO

SET IDENTITY_INSERT Dealers ON
INSERT Dealers (dealerid,dealername) VALUES (18,'USA Toyota')
SET IDENTITY_INSERT Dealers OFF
GO

SELECT * FROM Dealers
GO

(1 row(s) affected)

(1 row(s) affected)

dealerid    dealername      address         city            state zipcode
----------- --------------- --------------- --------------- ----- ----------
10          Acme BMW        NULL            NULL            NULL  NULL
18          USA Toyota      NULL            NULL            NULL  NULL

(2 row(s) affected)

There are some systemfunctions related to IDENTITY columns, including IDENT_SEED, IDENT_INCR, IDENT_CURRENT, SCOPE_IDENTITY, and @@IDENTITY. The first two functions, IDENT_SEED and IDENT_INCR, return information about the increment and seed of an IDENTITY column in a specified table, which is passed as the argument. Listing 3.13 demonstrates how these two functions are used.

Code Listing 3.13. Using IDENTITY System Functions (IDENT_SEED and IDENT_INCR)

						
USE Northwind

SELECT IDENT_SEED('Dealers'), IDENT_INCR('Dealers')
GO

----- -----

10    1

(1 row(s) affected)

The last three functions, IDENT_CURRENT, SCOPE_IDENTITY, and @@IDENTITY, return last-generated identity values. IDENT_CURRENT takes the name of a table as a parameter and returns the last identity value inserted in this table. SCOPE_IDENTITY returns the last identity generated in the current scope, which can be a stored procedure, trigger, user-defined function, or batch. Similarly, @@IDENTITY returns the last identity value generated in the current session. The difference between these two functions is that @@IDENTITY is not limited to the current scope; instead, it is limited to the current session, whereas SCOPE_IDENTITY is limited to the current scope. A session might have one or more than one scope. Listing 3.14 shows how to call these functions.

Code Listing 3.14. Using IDENT_CURRENT, SCOPE_IDENTITY, and @@IDENTITY

						
USE Northwind

SELECT IDENT_CURRENT('Dealers'), SCOPE_IDENTITY(), @@IDENTITY
GO
----- ----- -----

18    18    18

(1 row(s) affected)

After tables are created, you might want to change their owners. The sp_changeobjectowner system stored procedure is used to change the owner of any object in SQL Server. This system-stored procedure is very useful when you want to delete a user from a database and want to transfer all objects owned by this user to another user in the database.

The statement used to drop user tables (system tables cannot be dropped) is DROP TABLEfollowed by the name of the table. Be aware that if you create views or user-defined functions with the SCHEMABINDING option and they reference tables, these objects (views or user-defined functions) have to be dropped first before dropping the tables they reference.

Listing 3.15 illustrates how to drop a user table.

Code Listing 3.15. Using the DROP TABLE Statement

						
USE Northwind

DROP TABLE Dealers
GO

Notice that when you delete all rows from a table and it becomes an empty table, it still exists in the database; therefore, it isn't dropped when it has no rows. want

Altering a Table's Definition

After tables are created,their structure can be modified using the ALTER TABLE statement.This statement can be used to add columns, drop columns, change column properties (including data types), add constraints, drop constraints, disable constraints and triggers, and re-enable constraints and triggers.

If you want to add a columnor columns to a table, use the following syntax:

ALTER TABLE Table_name ADD column_name data_type [NULL|NOT NULL]

Listing 3.16 adds a new column, mileage, to the Cars table.

Code Listing 3.16. Adding a New Column to a Table

						
USE Northwind

ALTER TABLE Cars ADD mileage INT NULL
GO

To drop a column or columns, the following syntax is used:

ALTER TABLE Table_name DROP COLUMN column_name

For example, Listing 3.17 drops the mileage and color columns of the Cars table.

Code Listing 3.17. DroppingColumns from a Table

						
USE Northwind

ALTER TABLE Cars DROP COLUMN mileage,color
GO

If you want to change the properties of a specific column, use this syntax:

ALTER TABLE Table_name ALTER COLUMN column_name new_data_type [NULL|NOT NULL]

Listing 3.18 changes the properties of three columns in the Safedrivers table. The first statement sets the length of the licensenumber column to 30 and sets this column to not allow nulls. The second and the third statements leave intact the data type and length of the firstname and lastname columns, but change the nullability statusof these columns to NOT NULL.

Code Listing 3.18. Changing the Data Types and Nullability Status of Columns

						
USE Northwind

ALTER TABLE Safedrivers ALTER COLUMN licensenumber VARCHAR(30) NOT NULL
GO

ALTER TABLE Safedrivers ALTER COLUMN firstname VARCHAR(30) NOT NULL
GO
ALTER TABLE Safedrivers ALTER COLUMN lastname VARCHAR(30) NOT NULL
GO

Be aware that when adding a new column that doesn't allow NULL values, you must specify a default value for all the rows in the table (using a DEFAULT constraint). The statement needed to add a new column with a default constraint appears in Listing 3.19, which adds the state column (this column doesn't allow NULL values) to the Safedrivers table, with a default value of FL.

Code Listing 3.19. Adding New Columns with a Constraint

						
USE Northwind

ALTER TABLE Safedrivers ADD state CHAR(2) NOT NULL
CONSTRAINT addstate DEFAULT 'FL'
GO

To drop a constraint:

ALTER TABLE Table_name DROP constraint_name

To disable a constraint (allowing data that normally would be rejected by the constraint):

ALTER TABLE Table_name NOCHECK CONSTRAINT constraint_name

Then, to re-enable the constraint:

ALTER TABLE Table_name CHECK CONSTRAINT constraint_name

To disable a trigger of a table (preventing the trigger from being executed):

ALTER TABLE Table_name DISABLE TRIGGER trigger_name

Then, to re-enable the trigger:

ALTER TABLE Table_name ENABLE TRIGGER trigger_name

Refer to Chapter 7, "Enforcing Data Integrity," for more information on constraints, and to Chapter 9, "Implementing Complex Processing Logic: Programming Triggers," for more information on triggers.