CODE SPREAD

SQL provides a variety of Tables. Lets summarize each one of them.

Permanent Tables:

Why do we require it?

It is required for data persistence across the database. Once tables are created and filled with the data, this data is available for all the stored procedures and any DML statements can be run on them.

Example: Create a Student table having id,name as attributes.

Create table Student (Id int, Name varchar(50))

Here Table Student will have two columns, Id and Name. Id will accept only integers and Name will accept  maximum 50 characters.

Any Table created will have row and columns. Please refer the image below for visual representation.

Student

Temporary Tables:

This tables have a limited time duration and they are required to be dropped once they have served their purpose of existence. They are used to store data while defining a stored procedure and data is required to be persist beyond the procedure.

We have two type of temporary tables

1. Local Temporary Tables. (#table_name)
2. Global Temporary Tables.(##table_name)

Their name only describe their existence period and their usefulness. The symbols mentioned above is how they are described individually.

• Local Temporary Tables: 

These tables are created within a procedure and stored in the temp_db provided by SQL server. They can be identified with a session specific identifier. It can be used when data is coming from another stored procedure. It also reduces the amount of locking required and also involves less logging. Still, there are few limitations such as character limit is 116 and transactions can create unnecessary locks in temp_db.

Syntax:

Create table #Student (Id int, Name varchar(50))

• Global Temporary Tables:

These tables are same as local temporary table but the difference lies in the lifetime as it is available for all the sessions. This can be useful when the same set of data is required by one or more users. But the issue will come when the user, who should not be given access to this data, will have access to it as it is a global table and available for all users.

Syntax:

Create table ##Student (Id int, Name varchar(50))

See the difference of two #.

Table Variables:

Same structure as a normal table but only difference is the shortest life time among all the varieties. This table is created and stored in memory and its lifetime is decided by the stored procedure who have created it. Once stored procedure/DML statement exits, this table gets auto cleaned and memory gets free. Apart from that, log activity is truncated immediately. An important note, If we have a requirement to use a table structure in user defined function then we have only one option as Table variable and no other variety can be used.

Syntax:

Declare @Student Table (Id int, Name varchar(50))

I hope this article covered all the variation of a table. If you can contribute to improve this article, please write a comment below.Thanks.