Multiple way to create tables in SQL Server

Tables are the backbone of every database. In the SQL server there are multiple way to create table. Based on our need we are creating table. Let’s see how and when we are creating the table.

Ø  CREATE TABLE

Ø  SELECT INTO

Ø  Temp Table (#, ##)

Ø  Table Variable

Ø  External Table

Ø  System Versioned Temporal Table

Ø  Table with Clustered Index/Filegroup

Let’s see the demo and use of this type of table.

Create table

CREATE TABLE is used to create a new table in SQL Server where our data is stored in rows and columns. This is a Standard Way to creating a table.

Syntex to create table

CREATE TABLE schema_name.table_name (     column_name datatype [NULL | NOT NULL] [constraint],     column_name datatype [NULL | NOT NULL] [constraint],     ... );

Ø  schema_name = usually dbo

Ø  datatype = INT, VARCHAR, DATETIME, etc.

Ø  NULL / NOT NULL = allow or restrict nulls

Ø  Constraints control data rules

Creating a table and inserting the records into this table.

CREATE TABLE dbo.Employee (     EmpId INT NOT NULL,     EmpName VARCHAR(100) NOT NULL,     Dept VARCHAR(50),     Salary DECIMAL(10,2),     CreatedOn DATETIME DEFAULT GETDATE(),               CONSTRAINT PK_Employee_ID PRIMARY KEY (EmpId asc) on [PRIMARY],               CONSTRAINT CHK_Employee_Salary CHECK (Salary > 0) ); --inserting few records. INSERT INTO dbo.Employee(EmpId, EmpName, Dept, Salary) VALUES (101,'Bagesh','IT',85000),        (102,'Rajesh','HR',65000);

See the records into this table

SELECT INTO

This is a fastest way to create table from query result. It will copy structure and data but it does NOT copy constraints, indexes, triggers. It is useful when we are ETL or creating temp table. Generally, we are using this table when we are debugging the code or need to quick data storage. It is faster. With the help of this we can create permanent table as well as temp table. We are not recommending to create permanent table using this method.

Read more

https://bageshkumarbagi-msbi.blogspot.com/2024/08/select-into-vs-insert-select-statement.html

see the demo

select EmpId, EmpName, Dept, Salary, CreatedOn into dbo.EmployeeBK from dbo.Employee

Table created. See the data.

Limitations of SELECT INTO

Ø  Primary Keys

Ø  Foreign Keys

Ø  Indexes

Ø  Constraints

Ø  Triggers

Ø  Identity property (unless same column selected)

Meaning table is created without relational rules, which can break data integrity if used wrongly.

See the below example

INSERT INTO dbo.EmployeeBK(EmpId, EmpName, Dept, Salary) VALUES (101,'Manoj','IT',0),        (102,'Raheem','HR',-50); select * from EmployeeBK;

Which break the pk, check and default constrain.

Create Table with Clustered Index on Specific Filegroup

This is useful for performance tuning & storage optimization

See the example below

CREATE TABLE dbo.OrderHeader (     OrderId INT NOT NULL,     OrderDate DATE );  CREATE CLUSTERED INDEX CX_OrderHeader ON dbo.OrderHeader(OrderId) ON FG_SalesData;

Temporary Tables

There are two types of temp table

Ø  Local Temp Table (#Table) : scope of this table is in current session.

Ø  Global Temp Table (##Table): scope of this table is current server session.

Read more about this below

https://bageshkumarbagi-msbi.blogspot.com/2017/08/types-for-temporary-tables.html

 Table Variable

Variable table is a one kind of temp table scope of this table is current session only.

https://bageshkumarbagi-msbi.blogspot.com/2016/05/difference-between-temp-table-and.html

DECLARE @Employee TABLE (   EmpId INT NOT NULL,   EmpName VARCHAR(100) NOT NULL,   Dept VARCHAR(50),   Salary DECIMAL(10,2) );  INSERT INTO @Employee (EmpId,EmpName,Dept,Salary) SELECT EmpId,EmpName,Dept,Salary FROM Employee; select * from @Employee

See the data

This table is created in memory but temp table is created on the temp db.

System Versioned Temporal Table

A temporal table automatically keeps full history of data changes with time validity. SQL Server maintains a current table or a history table and automatically tracks Insert / Update / Delete with timestamps. This feature is introduced in SQL Server 2016.

CREATE TABLE Customer (    CustId INT PRIMARY KEY,    CustName VARCHAR(50),    CustAdd VARCHAR(100),    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON);

Table is created successfully. SQL Server automatically creates a history table.

Inserting some records into this table

insert into Customer(CustId,CustName,CustAdd) values (1,'Bagesh','Noida') insert into Customer(CustId,CustName,CustAdd) values (2,'Amit','Noida')

See the records in the table

Let’s update the record

Latest record is stored in main table and old versions stored on history table. When we are inserting new record No entry in history table yet. When we are updating or deleting the table records into main table and historical table.

Let’s update the record

To see the historical table

Ø  Use FOR SYSTEM_TIME

Ø  past point-in-time 

SELECT * FROM Customer FOR SYSTEM_TIME ALL;

If we want to see the point of time

SELECT * FROM Customer FOR SYSTEM_TIME AS OF '2025-12-24 07:10:40.6467336';

 

 External Table (PolyBase / Azure / Hadoop)

Such type of table is used in Big Data / Azure Synapse

CREATE EXTERNAL TABLE dbo.ExtCustomer (    Id INT,    Name VARCHAR(100) ) WITH (    LOCATION='/customer/',    DATA_SOURCE = MyExternalSource,    FILE_FORMAT = MyFileFormat );