Table - An introduction

Table

* A table is a database object which stores data in an organized format as rows and columns.

* All the data present in a row can be called as a record and it represents a single unit.

* The data in a record can be organized as fields (columns) to represent the various attributes of the unit.

* The columns can host any type of data that is pre-defined using what is called data type.

* Data type – int, char, varchar, bit, float, money, text, varbinary, Table etc.

* The data type selection depends on the business requirements.

* There are four special types of tables

        1.       Partitioned tables

        2.       Temporary tables

        3.       System tables

        4.       Wide tables

Partitioned tables are those which are horizontally divided so that they can spread over more than one file group in a database. It makes the large tables or indexes more manageable.

Temporary tables, as the name suggests, are those which are created (in tempdb automatically) on demand to achieve the logic and can be killed later. They are of two types- Local and Global

Local temporary tables are identified by # prefix and exist until dropped or till the session exists.

Global temporary tables are identified by ## prefix and exist until any of the session that is referencing it exists.

In other words, local temporary table is local to the session whereas global temporary table can span across multiple sessions.

There are exists a TABLE variable, identified by the @ prefix like @table, which when created allocates the SQL memory to store the data.

*** Using table variables is suggested in scenarios where we come across very less data else if large data is stored in a table variable. It uses the available SQL memory to store the data and only the remaining memory is used for other manipulations which may even lead contention or bottle-neck scenarios that hit the performance of the server. If we have to store large data temporary tables are the best. If we use the temporary tables to store very small data, the database IO operations cost more than when the data is stored in memory.  So, in this case table variable is preferable.

System tables are those in which SQL Server stores the data of the server configuration and all the database objects. In common words, they store the meta-data (data of the data). Those cannot be updated or modified except though a Dedicated Administrator Connection (DAC). The applications referencing the system tables may have to re-write the code before they are upgraded to other versions.

Wide tables are those which have a defined column set but use sparse columns.

Sparse columns mostly contain a NULL value in them.

Some maximum limitations on a table:

Property	SQL Server 2008R2(32-bit)	SQL Server 2005(32-bit)
Maximum row-size of any table	8060 Bytes	8060 Bytes
Columns per base table	1024	1024
Columns per primary/foreign/index key	16	16
Columns per SELECT	4096	4096
Columns per INSERT	4096	1024
Foreign key table references per table (recommended)	253	253
Clustered indexes per table	1	1
References per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Triggers per table	Limited by number of objects in a database	Limited by number of objects in a database
Tables per database	Limited by number of objects in a database	Limited by number of objects in a database