SQL Server provides many ways to work with tables. You can create new tables by using the New Table feature in SQL Server Management Studio or the CREATE TABLE command. You can modify existing tables by using the Modify Table feature in SQL Server Management Studio or the ALTER TABLE command. You can also perform other table management functions, including copy, rename, and delete.
Before you create a table, you should consider the table name carefully. Table names can be up to 128 characters long. Table names must begin with an alphabetic character, but can also contain underscores (_), "at" symbols (@), pound signs (#), and numerals. The exceptions to this rule are temporary tables. Local temporary tables have names that begin with # and are accessible to you only during the current user session. Global temporary tables have names that begin with ## and are accessible to anyone as long as your user session remains connected. Temporary tables are created in tempdb and are automatically deleted when your user session ends.
Table names must be unique for each schema within a database. Different schemas, however, can contain like-named tables. This means you could create multiple contacts tables as long as they are defined in separate schemas. Thus, the Customers, Employees, and Services schemas all could have a contacts table.
Each table can have up to 1,024 columns. Column names follow the same naming rules as tables and must be unique only on a per table basis. That is, a specific table can have only one StreetAddress column, but any number of other tables can have this same column.
In SQL Server Management Studio, you create a new table by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work. You must have CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
To create a new table, right-click the Tables node, and then select New Table from the shortcut menu. Then access the Table Designer in SQL Server Management Studio, and you will see a window similar to Figure 9-2.
You can now design the table using the views provided. These views are:
Active File/Table view. Provides quick access tabs for switching between open files and a summary. If you select the Table view in the Active File view, you will see an overview of the table’s columns. Each column is listed by Column Name, Data Type, and Allow Nulls. For fixed or variable-length data types, you follow the data type designator with the field length. If you select the Summary view in the Active File view, you will see all the tables in the current database listed by name, associated schema, and creation date. You can double-click a listed table to see the objects it contains.
Column Properties view. When you select a column in the Table view, you can use the Column Properties view to configure the settings for that column. Column properties that appear dimmed are fixed in value and cannot be changed. The values of these fixed properties typically depend on the column data type and properties inherited from the Database object itself.
Table Properties view. Allows you to view and set general table properties, including the table name, description and schema. You can open this view by pressing F4. Any dimmed properties cannot be changed at the table level and must be managed at the database level.
The Table Designer menu also provides options for designing the table. Because the options apply to the selected column in the Table view, you can apply them by selecting a column first and then choosing the appropriate option on the Table Designer menu. The same options are displayed when you right-click a column in the Table view.
When you have started the table creation process, you will want to:
Use the Table Properties view to set the table name, description, and schema. Type the name and description in the boxes provided. Use the drop-down list to select the schema that will contain this table.
Use the Table Properties view to specify the filegroup (or filegroups) in which the table data will be stored. Regular data and large object data are configured separately.
To specify the storage location for regular data, expand the Regular Data Space Specification node, and then use the Filegroup or Partition Schema drop-down list to specify the filegroup.
Use the Text/Image Filegroup drop-down list to specify the storage location for large object data.
Use the Table view to create and manage columns.
Rows in the Table view correspond to columns in the table in which you are working. In Figure 9-2, columns listed include CreditCardID, CardType, and Card Number.
Columns in the Table view correspond to column properties in the table in which you are working. In Figure 9-2, column properties listed include Name, Data Type, and Allow Nulls.
Use Table Designer menu options to work with a selected column. You can mark the column as the primary key, establish foreign key relationships, check constraints, and more.
Use the Column Properties view to specify the characteristics for the column you are creating. The characteristics include:
Name. Shows or determines the name of the column.
Allow Nulls. Shows or determines whether or not null values are allowed in this column.
Default Value or Binding. Shows or determines the default value or binding for the column, which is used whenever a row with a null value for this column is inserted into the database and nulls are not allowed in this column.
Precision. Shows or determines the maximum number of digits for values in the column. This property only applies when the column contains numeric or decimal data type values.
Scale. Shows or determines the maximum number of digits that can appear to the right of the decimal point for values in the column. This property only applies when the column contains numeric or decimal data type values.
Is Identity. Shows or determines if the column is used as an identifier column.
Identity Seed. Shows or sets the base value for generating unique identifiers. This property only applies to columns whose Is Identity option is set to Yes.
Identity Increment. Shows or sets the increment for generating unique identifiers. This property only applies to columns whose Is Identity option is set to Yes.
Is RowGuid. Shows or determines if the column contains globally unique identifiers. This property only applies to columns whose Is Identity option is set to Yes or Yes (Not for Replication).
Formula. Shows or sets the formula for a computed column.
Collation. Shows or sets the default collating sequence that SQL Server applies to the column whenever the column values are used to sort rows of a query result.
When you are finished creating the table, click Save or press Ctrl+S.
You can create tables with Transact-SQL using the CREATE TABLE command.Example 9-4 shows the syntax and usage for this command. Here you create the Customers table under the Sales schema. You must have CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.
Example 9-4. CREATE TABLE Syntax and Usage
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint >] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| " DEFAULT " } ]
[ { TEXTIMAGE_ON { filegroup | " DEFAULT " } ]
[ ; ]
<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
| [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] ]
[ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] ) ]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] (logical_expression )
}
<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[ [ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] ) ]
| [ FOREIGN KEY ]
REFERENCES referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ] ]
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
(column [ ASC | DESC ] [ ,...n ] )
[ WITH FILLFACTOR = fillfactor
|WITH ( <index_option> [ , ...n ] ) ]
[ ON { partition_scheme_name (partition_column_name)
| filegroup | "default" } ]
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
<index_option> ::=
{ PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF}
| ALLOW_PAGE_LOCKS ={ ON | OFF}
}Usage
USE OrderSystemDB CREATE TABLE Sales.Customers ( cust_lname varchar(40) NOT NULL, cust_fname varchar(20) NOT NULL, phone char(12) NOT NULL, uid uniqueidentifier NOT NULL DEFAULT newid() )
In SQL Server Management Studio, you modify an existing table by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
Expand the Tables node, and then right-click the table you want to modify. From the shortcut menu, choose Modify. Then you can access the views for designing tables, which were shown previously in Figure 9-2.
Make any necessary changes to the table, and then click Save or press Ctrl+S. If the changes you make affect multiple tables, you will see a prompt showing which tables will be updated and saved in the database. Click Yes to continue and complete the operation.
The Transact-SQL command for modifying tables is ALTER TABLE. Example 9-5 shows the syntax and usage for this command. Here you alter the Customers table under the Sales schema. You must have ALTER TABLE permission.
Example 9-5. ALTER TABLE Syntax and Usage
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{ALTER COLUMN column_name
{[ type_schema_name. ] type_name [ ( { precision [ , scale ]
| max | xml_schema_collection } ) ]
[ NULL | NOT NULL ]
[ COLLATE collation_name ]
| {ADD | DROP } { ROWGUIDCOL | PERSISTED }
} }
| [ WITH { CHECK | NOCHECK } ] ADD
{ <column_definition>
| <computed_column_definition>
| <table_constraint> ] } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
[ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
| COLUMN column_name } [ ,...n ]
| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
| SWITCH [ PARTITION source_partition_number_expression ]
TO [ schema_name. ] target_table
[ PARTITION target_partition_number_expression ]
}
[ ; ]
<drop_clustered_constraint_option> ::=
{ MAXDOP = max_degree_of_parallelism
| ONLINE = {ON | OFF }
| MOVE TO { partition_scheme_name ( column_name ) | filegroup
| "default"} } }Usage
USE OrderSystemDB ALTER TABLE Sales.Customers ADD uid2 uniqueidentifier NOT NULL DEFAULT newid() ALTER TABLE Sales.Customers ALTER COLUMN cust_fname CHAR(10) NOT NULL ALTER TABLE Sales.Customers DROP Address2
In SQL Server Management Studio, you can view table row and size information by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
Expand the Tables node, right-click the table you want to examine, and then select Properties from the shortcut menu. This displays the Table Properties dialog box shown in Figure 9-3.
On the General page, entries under the Storage node provide details about space used:
Data Space shows the amount of space the table uses on disk.
Index Space shows the size of the table’s index space on disk.
Row Count shows the number of rows in the table.
You can also view row, size, and space statistics for individual tables using the sp_spaceused stored procedure. The following code accesses the OrderSystemDB database and then checks the statistics for the Customers table under the Sales schema:
USE OrderSystemDB EXEC sp_spaceused 'Sales.Customers'
In SQL Server Management Studio, you can display table properties and permissions by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
Expand the Tables node, right-click the table you want to examine, and then select Properties from the shortcut menu to display the Table Properties dialog box shown previously in Figure 9-3.
Use the General, Permissions, and Extended Properties pages of the dialog box to view the table’s properties and permissions.
In SQL Server Management Studio, you view a table’s current data by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
Expand the Tables node, right-click the table you want to examine, and then select Open Table from the shortcut menu to display all the row data contained in the table.
The lower portion of the Query Results pane provides buttons for moving between the rows and a status area. If you select a read-only cell, the status area displays "Cell Is Read Only." If you have modified a cell, the status area displays "Cell Is Modified."
You can also list a table’s current data using the SELECT FROM statement. In the following example, you are selecting from the Department table under the HumanResources schema in the Personnel database:
SELECT * FROM [Personnel].[HumanResources].[Department] or SELECT DepartmentID,Name,GroupName,ModifiedDate FROM [Personnel].[HumanResources].[Department]
The easiest way to create a copy of a table is to use a Transact-SQL command. Use SELECT INTO to extract all the rows from an existing table into the new table. The new table must not exist already. The following example will copy the Customers table under the Sales schema to a new table called CurrCustomers under the BizDev schema:
SELECT * INTO BizDev.CurrCustomers FROM Sales.Customers
You can also create the new table from a specific subset of columns in the original table. In this case, you specify the names of the columns to copy after the SELECT keyword. Any columns not specified are excluded from the new table. The following example copies specific columns to a new table:
SELECT CustName, Address, Telephone, Email INTO BizDev.CurrCustomers FROM Sales.Customers
In SQL Server Management Studio, the easiest way to rename or delete a table is to complete the following steps:
In SQL Server Management Studio, access a database, and then expand the Tables node to list the tables in the database.
Right-click the table you want to rename or delete. From the shortcut menu, choose View Dependencies. The Object Dependencies dialog box shows the database objects that must be present for this object to function properly and the objects that depend upon the selected object. Use the information in this dialog box to understand any dependencies that may be affected by renaming or deleting the selected table. Click OK to close the View Dependencies dialog box.
To rename a table, right-click the table, and then choose Rename from the shortcut menu. You can now type a new name for the table.
To delete a table, right-click the table, and then choose Delete from the shortcut menu to display the Delete Object dialog box. Click OK.
You can also rename tables using the sp_rename stored procedure. You must have ALTER TABLE permission and be a member of the sysadmin or dbcreator fixed server roles to rename a table with sp_rename. In the following example, you rename the Customers table under the Sales schema CurrCustomers:
EXEC sp_rename 'Sales.Customers', 'CurrCustomers'
As long as you have ALTER permission on the schema to which the table belongs or CONTROL permission on the table, you can remove a table from the database using the DROP TABLE command:
DROP TABLE Sales.CurrCustomers
If you want to delete the rows in a table but leave its structure intact, you can use DELETE. The following DELETE command deletes all the rows in a table but does not remove the table structure:
DELETE Sales.CurrCustomers
To use DELETE, you must be a member of the sysadmin fixed server role, the db_owner or db_datawriter fixed database roles, the table owner, or be granted DELETE permission.
You learned how to add or remove columns in a table in SQL Server Management Studio in the section titled "Working with Tables" earlier in this chapter. In Transact-SQL, you modify table columns using the ALTER TABLE command, which was shown previously in Example 9-5.
The following example adds a unique identifier column to the Customers table under the Sales schema:
USE OrderSystemDB ALTER TABLE Sales.Customers ADD uid uniqueidentifier NOT NULL DEFAULT newid()
You can recreate and store all the SQL commands used to create tables in a database in an .sql file for later use. To do this, complete the following steps:
In SQL Server Management Studio, access a database, and then expand the Tables node to list the tables in the database.
Select a table, right-click its name, and then select Script Table As from the shortcut menu.
Point to CREATE TO and select File to open the Select A File dialog box.
In the dialog box, set a folder and file path for the .sql script, and then click Save.
If you open the .sql file, you will find all the Transact-SQL statements required to recreate the table structure. The actual data in the table is not stored with this procedure, however.