Feb 18, 2023

SQL Server Create Table Statement

Common Syntax

The syntax for creating a table using T-SQL (Transact-SQL) is as follows:

CREATE TABLE table_name
(
   column1 datatype1 [ NULL | NOT NULL ],
   column2 datatype2 [ NULL | NOT NULL ],
   ...
   columnN datatypen [ NULL | NOT NULL ]
);

where:

table_name is the name of the table that you want to create.
column1, column2, ... columnN are the names of the columns that you want to include in the table.
datatype1, datatype2, ... datatypen are the data types of the columns.
NULL or NOT NULL specifies whether the column can contain null values or not.

Sql Table Creation Example

For example, the following T-SQL code creates a table named "Customers" with three columns: "CustomerID", "Name", and "Address":

CREATE TABLE Customers
(
   CustomerID INT NOT NULL,
   Name VARCHAR(50) NOT NULL,
   Address VARCHAR(200) NULL
);

In this example, "CustomerID" and "Name" columns cannot contain null values, whereas "Address" can contain null values. The "INT" and "VARCHAR" are the data types of the "CustomerID", "Name", and "Address" columns, respectively.

Primary Key Columns

A primary key is a column or a set of columns in a database table that uniquely identifies each row in that table. It is used to enforce data integrity and is a crucial component of relational databases. In T-SQL, a primary key is implemented as a unique index on a table.

To create a primary key in a "CREATE TABLE" command using T-SQL, you can use the "CONSTRAINT" keyword followed by the name of the primary key constraint, and the "PRIMARY KEY" keyword followed by the name of the column or columns that make up the primary key. Here's an example:

CREATE TABLE Customers
(
   CustomerID INT NOT NULL,
   Name VARCHAR(50) NOT NULL,
   Address VARCHAR(200) NULL,
   CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
);

In this example, the "CustomerID" column is designated as the primary key of the "Customers" table. The "NOT NULL" constraint ensures that each row has a valid value for the primary key. You can also create a primary key that consists of multiple columns by specifying multiple column names after the "PRIMARY KEY" keyword, separated by commas.

Note that you can also create a primary key constraint after creating the table using the ALTER TABLE statement. In this case, you would use the following syntax:

ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID);

This adds a primary key constraint to the existing "Customers" table.

Use FOREIGN KEY Constraints

To create a foreign key constraint in SQL Server, you can use the "ALTER TABLE" statement to add a foreign key constraint to an existing table. Here's the basic syntax:

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column);

Where:

child_table is the name of the table that will contain the foreign key.
fk_name is the name of the foreign key constraint that you are creating.
child_column is the name of the column in the child table that will reference the parent table.
parent_table is the name of the table that contains the primary key.
parent_column is the name of the primary key column that the foreign key will reference.

For example, if you have a "Orders" table with a foreign key that references the "Customers" table, you can create the foreign key constraint using the following SQL statement:

ALTER TABLE Orders
ADD CONSTRAINT fk_CustomerID
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID);

In this example, the "Orders" table has a foreign key named "fk_CustomerID" that references the "CustomerID" column in the "Customers" table. This ensures that every order in the "Orders" table has a valid reference to a customer in the "Customers" table.

Use CHECK Constraints

A CHECK constraint is a type of constraint in SQL that allows you to specify a condition that each row in a table must satisfy. If a row violates the condition, the data modification operation will fail.

In contrast to a foreign key constraint, which enforces a relationship between two tables, a CHECK constraint is used to enforce rules that are specific to the data in a single table. For example, you could use a CHECK constraint to ensure that a numeric column contains only positive values or that a text column does not exceed a certain length.

Here's an example of creating a CHECK constraint in SQL Server:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    CONSTRAINT CK_Salary CHECK (Salary > 0)
);

In this example, a CHECK constraint named "CK_Salary" is created on the "Salary" column of the "Employees" table. The CHECK constraint specifies that the "Salary" column must be greater than zero for each row in the table.

A foreign key constraint, on the other hand, enforces a relationship between two tables by ensuring that the values in a column or set of columns in one table exist as values in a column or set of columns in another table. This ensures referential integrity between the two tables.

In summary, CHECK constraints are used to enforce rules within a single table, while foreign key constraints are used to enforce relationships between tables.

SQL Server Supports A Wide Range Of Data Types That Can Be Used To Define Columns In Tables. Here Is A List Of The Most Commonly Used Data Types:

Exact Numeric Data Types

bit: stores 0 or 1
tinyint: stores whole numbers from 0 to 255
smallint: stores whole numbers from -32,768 to 32,767
int: stores whole numbers from -2,147,483,648 to 2,147,483,647
bigint: stores whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
decimal/numeric: stores fixed-point numbers with a specified precision and scale

Approximate Numeric Data Types

float: stores approximate numeric values with a specified precision
real: stores approximate numeric values with a precision of 7 digits

Date and Time Data Types

date: stores date values ranging from January 1, 0001 to December 31, 9999
time: stores time values with a precision of up to 7 decimal places
datetime: stores date and time values from January 1, 1753 to December 31, 9999
datetime2: stores date and time values with a higher precision than datetime
smalldatetime: stores date and time values from January 1, 1900 to June 6, 2079

Character and String Data Types

char: stores fixed-length strings up to 8,000 characters
varchar: stores variable-length strings up to 8,000 characters
nvarchar: stores variable-length Unicode strings up to 4,000 characters
text: stores large variable-length strings up to 2^31-1 characters
ntext: stores large variable-length Unicode strings up to 2^30-1 characters

Binary Data Types

binary: stores fixed-length binary data up to 8,000 bytes
varbinary: stores variable-length binary data up to 8,000 bytes
image: stores large variable-length binary data up to 2^31-1 bytes

Other Data Types

uniqueidentifier: stores a 128-bit globally unique identifier (GUID)
sql_variant: stores values of various data types
xml: stores XML data up to 2^31-1 characters
SQL Server also supports user-defined data types, which can be created based on one or more of the built-in data types to provide a more specific or customized data type.

Nullability rules within a table definition

In T-SQL, the nullability of a column in a table can be specified using the NULL or NOT NULL keyword when defining the column.

Here are the rules for nullability in a table definition in T-SQL:

By default, columns allow NULL values. If you don't specify either NULL or NOT NULL, the column will allow NULL values.
If you specify NULL when defining a column, it means that the column allows NULL values.
If you specify NOT NULL when defining a column, it means that the column does not allow NULL values.
If a column is part of a primary key, it cannot allow NULL values.
If a column is part of a unique constraint, it can allow NULL values, but only one NULL value is allowed per column.
If a column is part of a foreign key constraint, it must have the same nullability as the referenced column.
If a column is part of an index, it can allow NULL values, but they may affect the performance of the index.

Here's an example of a table definition that specifies nullability for each column:

CREATE TABLE MyTable (
    ID INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(50) NULL,
    LastName VARCHAR(50) NOT NULL,
    Age INT NULL,
    DateOfBirth DATE NOT NULL
);

In this example, the ID column does not allow NULL values and is part of the primary key. The FirstName column allows NULL values, the LastName column does not allow NULL values, the Age column allows NULL values, and the DateOfBirth column does not allow NULL values.

Use the UNIQUEIDENTIFIER data type in a column

The UNIQUEIDENTIFIER data type in SQL Server is used to store a 128-bit globally unique identifier (GUID) that can be generated by SQL Server or an external application. To use the UNIQUEIDENTIFIER data type in a column, you can follow these steps:

Create a table with a column of data type UNIQUEIDENTIFIER:

CREATE TABLE my_table (
    id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    name VARCHAR(50)
);

In this example, the id column is of data type UNIQUEIDENTIFIER and is set as the primary key.

Insert data into the table, including a value for the UNIQUEIDENTIFIER column:

INSERT INTO my_table (id, name) VALUES (NEWID(), 'John Doe');

In this example, we're inserting a new row into the my_table table with a new GUID generated by the NEWID() function and a name of "John Doe".

Query the data in the table:

SELECT * FROM my_table;

This query will return all rows in the my_table table, including the GUID values.

Note that the UNIQUEIDENTIFIER data type can also be used in foreign keys to establish relationships between tables. When referencing a UNIQUEIDENTIFIER column in a foreign key constraint, you can use the same data type in the referencing column. For example:

CREATE TABLE orders (
    id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    customer_id UNIQUEIDENTIFIER NOT NULL,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

In this example, the orders table has a foreign key constraint that references the id column of the customers table, which is also of data type UNIQUEIDENTIFIER.

Temporary Tables

Temporary tables in SQL Server are tables that are created and used to store data temporarily within a session or a specific scope. They are similar to regular tables but are created with a special prefix (# for local temporary tables and ## for global temporary tables) to differentiate them from permanent tables.

There are two types of temporary tables in SQL Server:

Local temporary tables - These tables are visible only within the current session and are dropped automatically when the session is terminated.
Global temporary tables - These tables are visible to all sessions and are dropped automatically when the last session referencing them is terminated.

Here's an example of how to create and use a local temporary table:

CREATE TABLE #TempTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

INSERT INTO #TempTable (ID, Name)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');

SELECT * FROM #TempTable;

In this example, a local temporary table named "#TempTable" is created with two columns: "ID" and "Name". Data is then inserted into the table using the INSERT statement, and the contents of the table are queried using the SELECT statement.

Note that local temporary tables are only visible to the current session, so you can't reference them from another session. They are also automatically dropped when the session ends, so you don't need to explicitly drop them.

Here's an example of how to create and use a global temporary table:

CREATE TABLE ##TempTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

INSERT INTO ##TempTable (ID, Name)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');

SELECT * FROM ##TempTable;

In this example, a global temporary table named "##TempTable" is created using a double hash prefix. The table is then populated with data and queried in the same way as a local temporary table.

Global temporary tables are visible to all sessions, so they can be referenced by other sessions. They are also automatically dropped when the last session referencing them is terminated.

Syntax for memory optimized tables

Memory-optimized tables are a type of table in SQL Server that are optimized for in-memory data access. Unlike traditional disk-based tables, memory-optimized tables are stored entirely in memory, which can result in significant performance gains for certain types of workloads.

Memory-optimized tables were introduced in SQL Server 2014, and they are designed to support high-performance OLTP workloads with large numbers of concurrent users and frequent read and write operations.

Here's the syntax for creating a memory-optimized table in SQL Server:

CREATE TABLE MyMemoryOptimizedTable (
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Age INT NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

In this example, we're creating a memory-optimized table called MyMemoryOptimizedTable with four columns: ID, FirstName, LastName, and Age. The ID column is defined as the primary key, and we've specified that the table should be created with memory-optimized and durable storage. We've also set the durability option to SCHEMA_AND_DATA, which means that both the schema and the data will be persisted in memory.

Here's an example of how to insert data into a memory-optimized table:

INSERT INTO MyMemoryOptimizedTable (ID, FirstName, LastName, Age)
VALUES (1, 'John', 'Doe', 30);

INSERT INTO MyMemoryOptimizedTable (ID, FirstName, LastName, Age)
VALUES (2, 'Jane', 'Doe', 28);

INSERT INTO MyMemoryOptimizedTable (ID, FirstName, LastName, Age)
VALUES (3, 'Bob', 'Smith', 35);

In this example, we're inserting three rows of data into the MyMemoryOptimizedTable table using the INSERT statement.

Memory-optimized tables can provide significant performance improvements for certain types of workloads, especially those that require frequent read and write operations. However, they are not suitable for all types of workloads, and they can require more memory than traditional disk-based tables. It's important to carefully consider the performance and resource requirements of your application before deciding to use memory-optimized tables.

Use an expression for a computed column

In SQL Server, you can use an expression to create a computed column, which is a column that is derived from the values in one or more other columns in the table. To use an expression for a computed column, you can define the column using the AS keyword, followed by the expression. Here's an example:

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    quantity INT,
    price DECIMAL(10,2),
    total_cost AS (quantity * price)
);

In this example, the my_table table has columns for id, name, quantity, and price. The total_cost column is defined as a computed column using the expression (quantity * price), which multiplies the values in the quantity and price columns to calculate the total cost.

Note that computed columns are not stored in the table, but are instead calculated on the fly when the table is queried. This means that computed columns can have a performance impact on queries, especially if the expressions used to calculate them are complex or involve large amounts of data. Additionally, computed columns cannot be updated directly, but must be updated by updating the columns they depend on.

Create a table with an xml column typed to an XML schema collection

XML columns in SQL Server are used to store XML data in a table column. XML data can be used to represent structured and semi-structured data, and can be queried and manipulated using the XML capabilities of SQL Server.

To create a table with an XML column in SQL Server, you can use the following syntax:

CREATE TABLE MyTable (
    ID INT PRIMARY KEY,
    XMLData XML
);

In this example, we're creating a table called MyTable with two columns: ID and XMLData. The ID column is defined as the primary key, and the XMLData column is defined as an XML data type.

Once the table is created, you can insert XML data into the XMLData column using the INSERT statement. Here's an example:

INSERT INTO MyTable (ID, XMLData)
VALUES (1, '<Person><FirstName>John</FirstName><LastName>Doe</LastName></Person>');

INSERT INTO MyTable (ID, XMLData)
VALUES (2, '<Person><FirstName>Jane</FirstName><LastName>Doe</LastName></Person>');

In this example, we're inserting two rows of data into the MyTable table. Each row includes an ID value and an XMLData value, which contains XML data representing a person's first name and last name.

You can also query XML data stored in an XML column using the XML capabilities of SQL Server, such as the XQuery language. For example, you could retrieve the first name of a person in the MyTable table with the following query:

SELECT XMLData.value('(/Person/FirstName)[1]', 'nvarchar(50)') AS FirstName
FROM MyTable
WHERE ID = 1;

This query uses the value() method to extract the first name of the person with an ID of 1 from the XMLData column.

Create a table that uses row compression

To create a table that uses row compression in SQL Server, you can add the ROW COMPRESSION option to the CREATE TABLE statement. Here's an example:

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    address VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(50)
) WITH (DATA_COMPRESSION = ROW);

In this example, the my_table table has columns for id, name, address, phone, and email. The DATA_COMPRESSION = ROW option is added to enable row compression for the table.

Row compression is a data compression technique that reduces the amount of storage required to store data in a table by compressing each row of data. It works by identifying repeating patterns in the data and storing them more efficiently. Row compression is a good choice when the data in a table has a high degree of repetition or is sorted in a particular order.

Note that row compression can improve the performance of some queries by reducing the amount of data that needs to be read from disk and into memory. However, it can also increase CPU usage when compressing and decompressing data, so it's important to consider the performance tradeoffs when using compression.

Enable Data Retention Policy on a table

To enable a data retention policy on a table in SQL Server, you can use the sys.sp_add_retention_policy system stored procedure. Here's an example of how to use this procedure to enable a data retention policy on a table:

EXEC sys.sp_add_retention_policy @name = N'MyDataRetentionPolicy',
    @description = N'Defines a retention policy for MyTable',
    @object_name = N'dbo.MyTable',
    @retention_days = 365;

In this example, the sys.sp_add_retention_policy procedure is used to create a new data retention policy named MyDataRetentionPolicy for the dbo.MyTable table. The @retention_days parameter is set to 365, which means that any rows that are older than 365 days will be deleted.

Note that to use this procedure, you must have the ALTER ANY SCHEMA permission or be a member of the db_owner or db_ddladmin role. Additionally, the table must have a primary key defined.

Once the retention policy is created, SQL Server will automatically delete rows from the table that are older than the specified retention period. This can help keep the size of the table under control and ensure that only relevant data is retained.

Create a table that has sparse columns and a column set

Sparse columns in SQL Server are columns that have most of their values set to NULL. These columns are designed to save space by only storing non-NULL values, which can be especially useful when dealing with tables that have many columns or when dealing with large data sets.

To create a table that has sparse columns and a column set in SQL Server, you can use the SPARSE option when defining the columns, and the COLUMN_SET option to group related sparse columns together. Here's an example:

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50) SPARSE,
    address VARCHAR(100) SPARSE,
    phone VARCHAR(20) SPARSE,
    email VARCHAR(50) SPARSE,
    info XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);

In this example, the my_table table has columns for id, name, address, phone, and email, all of which are defined as sparse columns using the SPARSE option. The info column is defined as an XML column set using the COLUMN_SET option, which groups together all of the sparse columns in the table.

Note that in order to use sparse columns in SQL Server, you must have the Enterprise or Developer edition. Additionally, you should carefully consider whether sparse columns are appropriate for your use case, as they can have an impact on query performance and indexing strategies.

Partitioned tables

Partitioned tables are database tables that are divided into multiple smaller pieces or partitions based on specific criteria, such as a range of values or a hash function. Partitioning can help improve query performance, reduce maintenance time, and enable faster data loading.

To create a partitioned table, you'll need to follow these general steps:

Choose the partitioning method: There are different types of partitioning methods, such as range partitioning, list partitioning, hash partitioning, and composite partitioning. The method you choose depends on your data and query patterns.
Define the partition key: The partition key is the column or columns used to determine how data is divided into partitions. It's important to choose the right partition key to ensure that data is evenly distributed and queries can take advantage of partition pruning.
Create the table with partitions: When you create a partitioned table, you'll specify the partitioning method and the partition key. You'll also need to define each partition, including its name, the values it contains, and any specific attributes.

Here's an example of how to create a partitioned table using range partitioning in SQL:

CREATE TABLE my_table (
    id INT,
    created_at TIMESTAMP,
    data TEXT
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

In this example, we're partitioning the table based on the year in the created_at column. The table is divided into four partitions: p0 for data created before 2020, p1 for data created in 2020, p2 for data created in 2021, and p3 for data created after 2021. You can customize the partition names and values based on your needs.