SQL Create table Options

Create a new table
Example for creating Employees table :
CREATE TABLE Employees(
    Id int identity(1,1) primary key not null,
    FName varchar(20) not null,
    LName varchar(20) not null,
    PhoneNumber varchar(10) not null
);

identity(1,1) : states that column will have auto generated values starting at 1 and incrementing by 1 for each new row. 

Create a Temporary or In-Memory Table 

To create a temporary table local to the session:

CREATE TABLE #TempPhysical(...);

To create a temporary table visible to everyone:

CREATE TABLE ##TempPhysicalVisibleToEveryone(...);

To create an in-memory table:

DECLARE @TempMemory TABLE(...);

Create table from Select

CREATE TABLE ClonedEmployees AS SELECT * FROM Employees;

Above query will create clone of Employees table.

CREATE TABLE ModifiedEmployees AS

SELECT Id, CONCAT(FName," ",LName) AS FullName FROM Employees

WHERE Id > 10;

CREATE TABLE With FOREIGN KEY

We could find the table Employees with a reference to the table Cities

CREATE TABLE Cities(

    CityID INT IDENTITY(1,1) NOT NULL,

    Name VARCHAR(20) NOT NULL,

    Zip VARCHAR(10) NOT NULL

);

CREATE TABLE Employees(

    EmployeeID INT IDENTITY (1,1) NOT NULL,

    FirstName VARCHAR(20) NOT NULL,

    LastName VARCHAR(20) NOT NULL,

    PhoneNumber VARCHAR(10) NOT NULL,

    CityID INT FOREIGN KEY REFERENCES Cities(CityID)

);

Find database diagram.

Important: You couldn't make a reference to a table that not exists in the database. Be source to make first the table Cities and second the table Employees. If you do it vise versa, it will throw an error.

Duplicate a table 

To duplicate a table

CREATE TABLE newtable LIKE oldtable; 

INSERT newtable SELECT * FROM oldtable;

Creating a table with a foreign key
Example
CREATE TABLE HeroPowers
(ID int NOT NULL PRIMARY KEY, Name nvarchar(MAX) NOT NULL, HeroId int REFERENCES SuperHeros(ID))
The column HeroId is a foreign key to the table SuperHeros