Basic Training – Tables

Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING.

The most basic part of a database is a TABLE which consists of COLUMNS. A important decision during the initial design is to choose the data types that will capture the information you want in the least amount of space. I did a good job covering the various data types supported by SQL Server.

Now, I am going to talk about the two ways in which a table can be created via TSQL.

To prepare for some up coming conversations, I am going to create a sample database named [TRAINING] that contains a sample schema named [PUBS]. I will be tearing apart the pubs sample database, that shipped with Microsoft SQL Server 2000, for teaching purposes. The snippet below accomplishes these actions.

First way to create a table is to use the CREATE TABLE, ALTER TABLE and DROP TABLE statements are part of ANSI SQL. These statements allow a database developer make, change, and remove tables from the database. We are going to create the [AUTHORS] table inside the [PUBS] schema. If this table exists before hand, it will be dropped. Also, I am going to alter the table to add a primary key constraint. I will talk more about declarative data integrity in future articles.

What use is a table without some data. The code below inserts 5 rows into our newly built table.

Second way to create a table is to use the INTO clause of the SELECT statement. The table is created with the output results from the SELECT statement. A column alias can be used to change the names of the destination columns in the new table. All other characteristics such as indexes are not transferred during this operation.

The following TSQL snippet creates a table [NAMES] which has three columns.

One way to look at the details of these tables is to view them through the SQL Server Management Studio (SSMS).

Another way to look at the details of the [AUTHORS] table is to use the sp_help system stored procedure.

Regardless of the way in which you create a table via TSQL, you must have been granted the CREATE TABLE rights for the database otherwise a permission denied error will occur.

Next time, I will be talking about the nullability of COLUMNS in a table. This constraint can be used to enforce data entry (NOT NULL) or allow empty values (NULL).

Table Examples

Related posts

Leave a Comment