Creating tables and choosing types

So far you have read BuildMart’s tables. Now you will build your own. CREATE TABLE names the table and lists its columns, each with a type and a NULL rule. BuildMart is about to start buying from suppliers, so it needs a Suppliers table:

1CREATE TABLE dbo.Suppliers (2    SupplierId  int           NOT NULL IDENTITY PRIMARY KEY,3    Name        nvarchar(60)  NOT NULL,4    Phone       varchar(20)   NULL,5    CreditLimit decimal(10,2) NOT NULL,6    Rating      tinyint       NULL,7    CreatedAt   datetime2(0)  NOT NULL8);9 10SELECT * FROM dbo.Suppliers;

SupplierId  Name  Phone  CreditLimit  Rating  CreatedAt
----------  ----  -----  -----------  ------  ---------

The table exists and is empty: the SELECT shows its columns and no rows. Line by line:

• dbo.Suppliers: always include the schema when you create something.
• NOT NULL / NULL: whether a value is required. If you leave it out, SQL Server usually assumes NULL is allowed, so say it every time.
• IDENTITY: SQL Server numbers new rows itself (1, 2, 3, …). It has its own lesson later in this module.
• PRIMARY KEY: the column that identifies each row. It must be unique and can never be NULL.

Design it yourself

The design below starts with several classic mistakes. Fix the types, NULL rules and key until the warnings disappear:

1CREATE TABLE dbo.Suppliers (2    SupplierId  int           NOT NULL,3    Name        varchar(60)   NOT NULL,4    Phone       int           NULL,5    CreditLimit float         NOT NULL,6    Rating      tinyint       NOT NULL,7    CreatedAt   nvarchar(20)  NOT NULL8);

Choosing a type: a checklist

Primary keys: natural or surrogate?

A natural key is a real-world value that is already unique, such as an ISBN or a country code. A surrogate key is a number made up for the purpose, such as SupplierId. Natural keys sound neat but have a habit of changing (people change email addresses, companies change names) or turning out not to be unique. Most tables use a surrogate int IDENTITY key and add a UNIQUE constraint on the natural value; the Constraints lesson shows how.

Computed columns

A column can be defined as an expression of other columns. It is worked out whenever you read it, so it can never disagree with them:

1CREATE TABLE dbo.QuoteLines (2    QuoteLineId int           NOT NULL IDENTITY PRIMARY KEY,3    Product     nvarchar(60)  NOT NULL,4    Quantity    int           NOT NULL,5    UnitPrice   decimal(10,2) NOT NULL,6    LineTotal   AS (Quantity * UnitPrice)7);8 9INSERT INTO dbo.QuoteLines (Product, Quantity, UnitPrice)10VALUES (N'Cement 25 kg', 20, 7.20), (N'Sand 25 kg', 15, 4.10);11 12SELECT Product, Quantity, UnitPrice, LineTotal FROM dbo.QuoteLines;

Product       Quantity  UnitPrice  LineTotal
------------  --------  ---------  ---------
Cement 25 kg        20       7.20     144.00
Sand 25 kg          15       4.10      61.50

Temporary tables

A table whose name starts with # is temporary: it lives in tempdb, only your connection can see it, and it disappears when you disconnect. It is handy for holding intermediate results while you work something out.

1CREATE TABLE #Basket (2    ProductId int NOT NULL,3    Quantity  int NOT NULL4);5 6INSERT INTO #Basket (ProductId, Quantity) VALUES (4, 10), (5, 10);7 8SELECT * FROM #Basket;

ProductId  Quantity
---------  --------
        4        10
        5        10

Scripts you can run twice

Run the same CREATE TABLE twice and the second run fails, because the name is taken:

1CREATE TABLE dbo.Products (ProductId int);

Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Products' in the database.

DROP TABLE IF EXISTS removes a table only when it is there, which is how BuildMart’s setup script can be re-run to reset everything. Use it in scripts that build test data; never casually against real data, because the rows go with the table.

1DROP TABLE IF EXISTS dbo.Deliveries;2 3CREATE TABLE dbo.Deliveries (4    DeliveryId  int          NOT NULL IDENTITY PRIMARY KEY,5    OrderId     int          NOT NULL,6    DeliveredOn date         NULL7);8 9SELECT name FROM sys.tables WHERE name = 'Deliveries';

name
----------
Deliveries

1CREATE TABLE dbo.Vans (2    VanId        int        NOT NULL IDENTITY PRIMARY KEY,3    Registration varchar(8) NOT NULL,4    PayloadKg    int        NOT NULL,5    LastServiced date       NULL6);7 8SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE9FROM INFORMATION_SCHEMA.COLUMNS10WHERE TABLE_NAME = 'Vans'11ORDER BY ORDINAL_POSITION;

COLUMN_NAME   DATA_TYPE  IS_NULLABLE
------------  ---------  -----------
VanId         int        NO
Registration  varchar    NO
PayloadKg     int        NO
LastServiced  date       YES