Special Table Types
Objective of this post is to provide a list of special
tables available in Sql Server and brief description of that. If you wish to
study them in details, I will be providing the links. Let's get started –
There are following special types of table are available in
Sql Server.
1. Temporal table
2. Memory-Optimized table
3. Partitioned tables
4. Temporary table
5. System tables
6. Wide tables
Let's explore them one by
one.
Temporal Table - Also known as system versioned tables are introduced in
Sql Server
2016 and this kind of
table keeps the full history of data changes and it's almost like Slowly
changing dimensions in Data warehouse. Each such table has two additional
column of data type datetime2 to maintain the validity period of the record. For
details visit the below link.
Memory Optimized Table -
Memory-optimized tables
are completely conformed to ACID properties of database and it resides in main
memory. Sql server keeps a copy of it in the disc just for durability.
These kinds of tables provide better performance than a disk based tables but
it depends on many factors and volume of data is the major factor. Here is the
syntax to create a Memory-optimized table –
Create Table
[dbo].[DemoTable]
(
ID Int PRIMARY KEY,
Email nvarchar(256),
FullName nvarchar(256)
) With (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
Partitioned tables
In this kind of table you
can partition the data on the basis of a partition function or a column.
Suppose you have data of employees and you want to partition the data on the
basis of office location. If you have five work locations , so you will be
creating five partition. Again there are two kind of partitioning Vertical and
Horizontal. For details visit below link.
Temporary tables
Temporary tables provide work
space to store intermediate result set. They are created on the fly and doesn’t
store in disk. They are fast and provide all the features of a table. Again
there are three kind of temporary tables.
A. Local Temporary Table
Syntax –
Create Table #DemoT
(
ID int,
Name varchar(256)
)
B.Global Temporary Table
Syntax -
Create Table ##DemoT
(
ID int,
Name varchar(256)
)
C.Table Variables
Declare @DemoT
(
ID int,
Name varchar(256)
)
There are difference between
these three types like you can create index on Temp tables and Sql server maintains
the statistics for Temp tables but it’s not true for table variables . For
details check this –
System Table –
These tables hold data about
Database itself and should not be modified or delete.
Be extra careful while working
with system tables, these are good source of information about and inside of
database. Example are Sys.objects, Sys.tables, Sys.columns
Wide Tables
As the name suggest wide table
can have up to 30,000 columns. This kind of tables has very limited use because
most of the column should be NULL to adhere to the total row size 8019 bytes.
Wide table can be created or existing table can be converted to wide
table, be extra careful before using them. Wide table require performance
considerations, you can find the details below –
https://technet.microsoft.com/en-us/library/cc645884%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
