25 ways creating a table in SQL Server 2012

Introduction
In this blogpost I'll describe the different ways to create a table in SQL Server 2012 (and before). Mostly, creating a table can be done with the CREATE TABLE Statement but there are other ways too. For instance with the SELECT INTO or a virtual table. I've gathered a number of  ways to create a table:
1) Create table with a Primary key (columnlevel).
2) Create table with a Primary key (tablelevel).
3) Create table with a foreign key (column level).
4) Create table with a foreign key (table level).
5) Create table with a DEFAULT.
6) Create table with a CHECK.
7) Create table with a ON DELETE CASCADE Foreign Key relation.
8) Create table with a ON DELETE SET NULL Foreign Key relation.
9) Create table with Row compression.
10) Create table with a sparse column.
11) Use a SEQUENCE object as an alternative for the identity (autonumbering).
12) Create a table with  FILESTREAM.
13) Create a table with a Filetable.
14) Create a Table with a partition.
15) Create a temporary table.
16) SELECT INTO.
17) Create a computed column based table.
18) Create a persisted computed column based table.
19) Create a table with a GUID.
20) Declare a TABLE variable.
21) Creating a Table in a specific Filegroup.
22) Creating a table with an XML column typed to an XML schema collection.
23) CREATE A Table with a Index with a couple of options.
24) CREATE TABLE with an attribute with specific collation.
25) Create a Derived Table.

Creating table examples


1) Create table with a Primary key (columnlevel)
The following CREATE TABLE statement creates a simple table with a primary key at column level.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL PRIMARY KEY, 
[CustomerTypeDescription] NVARCHAR(50) NULL
)

2) Create table with a Primary key (tablelevel)

The following CREATE TABLE statement creates a simple table with a primary key at table level.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) NULL, 
CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID])
)

3) Create table with a foreign key (column level) 

The following CREATE TABLE statement creates a simple table with a primary key at columnlevel  and a foreign key relation (also at column level) to another table.

CREATE TABLE [dbo].[Order]

(
[OrderID] INT NOT NULL PRIMARY KEY,

[CustomerID] INT NOT NULL CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID]),

[Ordernumber] NCHAR(10) NULL, 

[OrderAmount] MONEY NULL, 

)

4) Create table with a foreign key (table level)

The following CREATE TABLE statement creates a simple table with a primary key at columnlevel  and a foreign key relation to another table. 

CREATE TABLE [dbo].[Order]

(

[CustomerID] INT NOT NULL PRIMARY KEY, 

[Ordernumber] NCHAR(10) NULL, 

[OrderAmount] MONEY NULL, 

CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID])

)

5) Create table with a DEFAULT

In the next CREATE TABLE Statement a default is introduced.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) NULL DEFAULT 'UNKNOWN'
CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID]) 
)

6) Create table with a CHECK

With the Check constraint you can control the values that are entered. In this case the amount entered should be greater than 0.

CREATE TABLE [dbo].[Order]
(
[OrderID] INT NOT NULL PRIMARY KEY,
[CustomerID] INT NOT NULL CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID]),
[Ordernumber] NCHAR(10) NULL, 
[OrderAmount] MONEY NULL
CONSTRAINT chk_OrderAmount CHECK ([OrderAmount]>0)
)

7) Create table with a ON DELETE CASCADE Foreign Key relation

You can extend the forign key relation constraint with some more sophisticated action. The ON DELETE specifies what action happens to rows in the table, if those rows have a referential relationship and the referenced row is deleted from the parent table. With the CASCADE option the corresponding rows are deleted from the referenced table.

CREATE TABLE [dbo].[Order]
( 
[OrderID] INT NOT NULL PRIMARY KEY,
[CustomerID] INT NOT NULL CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID]) ON DELETE CASCADE,
[Ordernumber] NCHAR(10) NULL, 
[OrderAmount] MONEY NULL CONSTRAINT chk_OrderAmount CHECK ([OrderAmount]>0)
)

8) Create table with a ON DELETE SET NULL Foreign Key relation

In the next CREATE TABLE statement The record in the referenced table is not deleted, but the Forign key   field is set to NULL. Off course, the Foreign key field should be NULL.

CREATE TABLE [dbo].[Order]
( 
[OrderID] INT NOT NULL PRIMARY KEY,
[CustomerID] INT NOT NULL CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID]) ON DELETE SET NULL,
[Ordernumber] NCHAR(10) NULL, 
[OrderAmount] MONEY NULL CONSTRAINT chk_OrderAmount CHECK ([OrderAmount]>0)
)

9) Create table with Row compression

With row compression you can reduce the size of the database. In addition, data compression improves the I/O because of less data that is stored in the pages and the query needs fewer pages to load.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) NULL DEFAULT 'UNKNOWN' CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID]) 
)
WITH (DATA_COMPRESSION = ROW)

10) Create table with a sparse column

Sparse columns are ordinary columns that have an optimized storage for null values.  

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
    [CustomerTypeDescription] NVARCHAR(50) SPARSE NULL
CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID]) 
)

11) Use a SEQUENCE object as an alternative for the identity (autonumbering)

SEQUENCE is an object that has a start value, increment value and end value defined and this sequence can be added to a column. A SEQUENCE can be shared between multiple tables in contrast with the autonumbering option of a field.

DROP SEQUENCE [dbo].[Sequence_Numbering]
GO


CREATE SEQUENCE [dbo].[Sequence_Numbering]
AS BIGINT
START WITH 5
INCREMENT BY 3
NO MAXVALUE
NO CYCLE
CACHE 10
GO


DELETE FROM [dbo].[CustomerType]


SELECT NEXT VALUE FOR [dbo].[Sequence_Numbering]


Insert into [dbo].[CustomerType] ([CustomerTypeID], [CustomerTypeDescription]) VALUES  (NEXT VALUE for [dbo].[Sequence_Numbering],'Small')


Insert into [dbo].[CustomerType] ([CustomerTypeID], [CustomerTypeDescription]) VALUES  (NEXT VALUE for [dbo].[Sequence_Numbering],'Medium')


Insert into [dbo].[CustomerType] ([CustomerTypeID], [CustomerTypeDescription]) VALUES  (NEXT VALUE for [dbo].[Sequence_Numbering],'Large')


SELECT * FROM [dbo].[CustomerType]

12) Create a table with  FILESTREAM

FILESTREAM is introduced in SQL Server 2008 and enables storing blobs in SQL Server instead of storing it seperately on the file system.

CREATE TABLE [dbo].[Customer]
(
[CustomerID] INT NOT NULL PRIMARY KEY, 
[CustomerName] NVARCHAR(50) NULL, 
[CustomerAddress] NVARCHAR(50) NULL, 
[CustomerTypeID] SMALLINT NULL, 
[CustomerGender] CHAR(1) NULL, 
[CustomerPictureID] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[CustomerPicture] VARBINARY(MAX) FILESTREAM NULL
CONSTRAINT [FK_Customer_ToTable] FOREIGN KEY (CustomerTypeID) REFERENCES [CustomerType](CustomerTypeID)  ON DELETE CASCADE
)

13) Create a table with a FILETABLE

FILETABLE is new in SQL Server 2012. File Tables are special tables which can be used to store your files and documents and access them from windows application as if they are stored in the file system. File Tables are built on top of File Stream feature. 

-- Creating a new FileTable  
 CREATE TABLE [dbo].[CustomerFiles]   
 AS FILETABLE ON [PRIMARY]   
 FILESTREAM_ON [FG_2]
 WITH (  
    FILETABLE_DIRECTORY = N'CustomerFileStreamGroup',  
    FILETABLE_COLLATE_FILENAME = DATABASE_DEFAULT  
 )  
 GO 

14) Create a Table with a partition

To improve the scalability and manageability of large tables you can implement the partition functionality. This enables you to manage (Very) Large Database in a more flexible way.

--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION CREATETable_PartitionFunc (int)
AS RANGE LEFT FOR
VALUES (2);
GO


CREATE PARTITION SCHEME CREATETable_PartitionSchema
AS PARTITION CREATETable_PartitionFunc
TO ([PRIMARY], FG_1);
GO


DROP TABLE [dbo].[CustomerType]
GO


CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] INT NOT NULL, 
    [CustomerTypeDescription] NVARCHAR(50) NULL
CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID]) 
) ON  CREATETable_PartitionSchema ([CustomerTypeID]);

15) Create a temporary table

Creating a temporary table can become very handy in case you want to store temporary results. The table is created in the Tempfb database and will be cleaned when the session is closed.

CREATE TABLE #tmp
(
[tmpID] INT NOT NULL, 
[tmpDescription] NVARCHAR(50) NULL
) 

16) SELECT INTO 

With SELECT INTO a copy of a table is created.

SELECT * 
INTO CustomerTypeCOPY 
FROM [CustomerType]

17)  Create a computed column based table

A computed column is computed from an expression that can use other columns in the same table. This is introduced in SQL Server 2005. If it's not defined as persisted then it's a virtual column. The data is not stored on disk.

CREATE TABLE CustomerType2
(TypeID INT,
TypeDescription VARCHAR(50),
TypeDescription2 VARCHAR(50),
Concatenated as (TypeDescription  + TypeDescription2) 
)

18) Create a persisted computed column based table

This property for computed columns has been introduced in SQL Server 2005 and onwards. If  persisted property is set active then the data of the computed column will be stored on disk.

CREATE TABLE CustomerType2
(TypeID INT,
TypeDescription VARCHAR(50),
TypeDescription2 VARCHAR(50),
Concatenated as (TypeDescription  + TypeDescription2) PERSISTED
)

19) Create a table with a GUID.

Normally, we use UNIQUEIDENTIFIER column for PRIMARY KEY and we assign newid() as the DEFAULT value for the column. The ROWGUIDCOL is more efficient. It's used for FILESTREAM.

CREATE TABLE dbo.GuidCustomer
(Customerguid uniqueidentifier DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
 Customer_Name varchar(60)
 CONSTRAINT pkGuid PRIMARY KEY (Customerguid) );

20) Declare a TABLE variable

A table variable is much like a temporary table but there are differences.

CREATE TABLE [dbo].[CustomerType]

(

[CustomerTypeID] SMALLINT NOT NULL PRIMARY KEY, 

[CustomerTypeDescription] NVARCHAR(50) NULL

)

21) Creating a Table in a specific Filegroup 

In case you want to partition tables in different filegroups you can choose to use multiple filegroups.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL PRIMARY KEY, 
[CustomerTypeDescription] NVARCHAR(50) NULL
) ON FG2

22) Creating a table with an XML column typed to an XML schema collection

Below an table example with typed XML column.

CREATE TABLE HumanResources.EmployeeResumes 
   (LName nvarchar(25), FName nvarchar(25), 
    Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );

23) CREATE A Table with a Index with a couple of options

An example of some mixed  primary key options.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) NULL,
CONSTRAINT [PK_CustomerTypex] PRIMARY KEY CLUSTERED
(
[CustomerTypeID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON FG2

24) CREATE TABLE with an attribute with different collation

Sometimes you want to collate a field in another collation than the database default or server default.

CREATE TABLE [dbo].[CustomerTypeCollate]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) COLLATE Latin1_General_CS_AS_KS_WS NULL 
)

25) Create a Derived Table

The last example is a derived table example.

SELECT 
Name
FROM (SELECT * 
FROM Customer  
WHERE Name LIKE 'Hen%') AS CustomerDerivedTable 
WHERE Gender = 'M'
ORDER BY Name

Conclusion

Pfew...That are a lot of ways to create a table. I've learned a couple of new things and one thing that i didn't expected was that the usage of persisted column can improve sql queries like a JOIN on a function isnull(field).

Greetz,

Hennie