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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
<span style="color: #008000; font-size: small;">-- -- Create training database -- -- Which database to use. USE [master] GO -- Delete existing database IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TRAINING') DROP DATABASE [TRAINING] GO -- Add new database CREATE DATABASE [TRAINING] ON PRIMARY ( NAME = N'TRAINING_DAT', FILENAME = N'C:\MSSQL\DATA\TRAINING.MDF' , SIZE = 32MB , MAXSIZE = UNLIMITED, FILEGROWTH = 4MB) LOG ON ( NAME = N'TRAINING_LOG', FILENAME = N'C:\MSSQL\LOG\TRAINING.LDF' , SIZE = 8MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB ); GO -- -- Create pubs schema -- -- Which database to use. USE [TRAINING] GO -- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'PUBS') DROP SCHEMA [PUBS] GO -- Add new schema. CREATE SCHEMA [PUBS] AUTHORIZATION [dbo] GO</span> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<span style="color: #008000; font-size: small;">-- -- Create authors table -- -- Delete existing table IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[PUBS].[AUTHORS]') AND type in (N'U')) DROP TABLE [PUBS].[AUTHORS] GO -- Create new table CREATE TABLE [PUBS].[AUTHORS] ( au_id varchar(11) not null, au_lname varchar(40), au_fname varchar(20), phone char(12), address varchar(40), city varchar(20), state char(2), zip char(5), contract bit ); -- Alter the authors table ALTER TABLE [PUBS].[AUTHORS] ADD CONSTRAINT PK_AUTHORS_ID PRIMARY KEY CLUSTERED (au_id); </span> |
What use is a table without some data. The code below inserts 5 rows into our newly built table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<span style="color: #008000; font-size: small;">-- -- Add five records -- INSERT INTO [PUBS].[AUTHORS] VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1); INSERT INTO [PUBS].[AUTHORS] VALUES('213-46-8915', 'Green', 'Marjorie', '415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618', 1); INSERT INTO [PUBS].[AUTHORS] VALUES('238-95-7766', 'Carson', 'Cheryl', '415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705', 1); INSERT INTO [PUBS].[AUTHORS] VALUES('998-72-3567', 'Ringer', 'Albert', '801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1); INSERT INTO [PUBS].[AUTHORS] VALUES('899-46-2035', 'Ringer', 'Anne', '801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1); </span> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
<span style="color: #008000; font-size: small;">-- -- Create new table (SELECT INTO) -- SELECT au_id as author_id, au_lname AS last_name, au_fname AS first_name INTO [PUBS].[NAMES] FROM [PUBS].[AUTHORS]; </span> |
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).
