Tables in SQL Server

Tables in database are responsible to store data in form of a grid like structure. Tables consist of Columns and Rows. The columns are the variables for which we want to store values and these values for each column forms a row of data. Each row is a collection of values of columns.

Table Types

There are couple of types of tables provided by SQL Server which are discussed below.

  • Heap Table: It’s a normal table that we usually create without a clustered index to store data.
  • Clustered Table: It’s a normal table that we usually create but with clustered index.
  • Local Temporary Table: These tables are used as temporary tables inside code blocks, usually starts with symbol #. These are created in tempdb system database.
  • Global Temporary Table: These tables are also created inside tempdb for temporary purposes and are available to use for all system users. These tables starts with symbol ## and need to be removed manually.
  • File Table: These special kind of tables were introduced in SQL Server 2012 and are used to store stream of data.

Columns and Data Types

Every column in table has a data type that defines what kind of data can be stored in that column. For example, to store name of a person column of type VARCHAR(any valid int value) will be used. There are plenty of data types available but the commonly used are INT, VARCHAR, CHAR, BIT, DATETIME.

Primary Keys

Primary keys are used to identified rows individually of the table. Every table should have a primary key to perform search operations efficiently. Primary keys can be made up of one or more than one columns.

Primary key is a special type of index. If we define primary key for a column then by default it is clustered index. To make primary key non-clustered we have a define nonclustered explicitly. Mostly in all tables primary column is identity column which increments automatically as it is more natural to make primary key column to be of either INT or BIGINT type.

Here is the syntax to make primary key.

CONSTRAINT pk_table-name PRIMARY KEY NONCLUSTERED (ColumnName)