Summary:
From this post you will learn about CREATE TABLE statement in SQL.
About CREATE TABLE Statement: Creates a new table in SQL Server and Azure SQL Database. There are two types of tables i.e. Constant and Temporary Table.
Syntax for Constant Table:
CREATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name | table_name }
( { <column_definition> } [ ,...n ] )
[ ; ]
Arguments:
database_name Is the name of the database in which the table is created. database_name must specify the name of an existing database. If not specified, database_name defaults to the current database. The login for the current connection must be associated with an existing user ID in the database specified by database_name, and that user ID must have CREATE TABLE permissions.
schema_name Is the name of the schema to which the new table belongs.
table_name Is the name of the new table. Table names must follow the rules for identifiers. table_name can be a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.
column_definition Specifies the properties of a column that are added to a table.
Example:
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.
A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
Transact-SQL statements reference the temporary table by using the value specified for table_name in the CREATE TABLE statement, for example:
Example:
CREATE TABLE #MyTempTable (col1 INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);
NOTE: Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE.
