SQL - Structured Query Language

CREATE TABLE

Table is used to stores and maintains data in the User Defined Database. CREATE TABLE is used to define Structure of Table in the database files that can access any authenticated database users. Table object is used to stores and maintain data in the database. Table names must be unique for each schema within a database.

Structure of Table can define up to 1024 columns. Table and Column names must follow up SQL Server Identifiers Rules. Table and Column name can be a maximum of 128 characters. Each column must define with a data type and data range or data size. Column(s) may have domain, key and referential integrity constraints.

Example 01

Create New Table

The following example is used to create structure of EMP table in the eBusiness database. EMP table is used to maintain Employee's Emp_Code, Emp_Name, DOJ, Salary and Phone information.

-- Create New Table --

USE eBusiness

CREATE TABLE EMP

(

Emp_Code char(5),

Emp_Name varchar(50),

DOJ datetime,

Salary numeric(7,2),

Phone varchar(50)

)

IDENTITY

When a new row is added to the table, the Database Engine provides a unique, incremental value for the column that used with PRIMARY KEY constraint to maintain the unique row identifier for the table. IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0) or numeric(p,0) columns.

Seed is the initial value and Increment is the incremental value that added with previous row IDENTITY value. The seed and increment or neither must be define with IDENTITY that default value is (1,1).

Example 02

Create New Table with IDENTITY

The following example is used to create structure of Employee table in the eBusiness database. Employee table is used to maintain Employee's Emp_Code, Emp_Name, DOJ, Salary and Phone information.

-- Create New Table with IDENTITY --

USE eBusiness

CREATE TABLE Employee

(

Emp_Code int IDENTITY(1,1),

Emp_Name varchar(50),

DOJ datetime,

Salary numeric(7,2),

Phone varchar(50)

)

Temporary TABLE

Temporary TABLE is used to stores and maintains data in the tempdb System Database. Temporary TABLE name can be a maximum of 116 characters. Temporary Table can be replaced with Table variable that have the table data type.

Temporary Tables can categories into local and global. Global Temporary Table name prefixed with double number signs ## and Local Temporary Table name prefixed with single number sign #.

Local Temporary Table is used to stores and maintains data for particular database session, once the session will expire the local temporary table should delete automatically from tempdb. Global Temporary Table is used to stores and maintains data for all database users that will expire when all users referencing the table disconnect from the instance of SQL Server.

Example 03

Create Temporary Table

The following example is used to create structure of Employee table in the tempdb database. EMP temporary table is used to maintain Employee's Emp_Code, Emp_Name, DOJ, Salary and Phone information.

-- Create Temporary Table --

USE eBusiness

CREATE TABLE #EMP

(

Emp_Code char(5),

Emp_Name varchar(50),

DOJ datetime,

Salary numeric(7,2),

Phone varchar(50)

)

* * * * *

Your Comment To AUTHOR