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.
(
[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.
(
[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,
[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.
(
[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.
(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
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
Greetz,
Hennie
