TABLE

Tables in Sql Server stores data in the form of Rows and columns.

Below is the basic syntax for creating Table in Sql Server.

CREATE TABLE <TableName>
 (  
    <ColumnName1> <Datatype> [CONSTRAINT], 
    <ColumnName2> <Datatype> [CONSTRAINT],                                               
                      .  
                      .
    <ColumnNameN> <Datatype> [CONSTRAINT] 
)

Example:

CREATE TABLE Customer
(
CustomerId INT NOT NULL,
Name NVARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(15) NULL
)

INSERTING RECORDS

We can add records to the Table by using INSERT statement as shown below

INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);

Adding records to the Customer Table with all the column values.

INSERT INTO Customer (CustomerId, Name, PhoneNumber)
VALUES (1, ‘Shree’, ‘1234567890’)

RETRIEVING DATA FROM TABLE

We can use the SELECT statement to fetch records from the Table.

SELECT column1, column2, … From <Table name>

We can use * instead of the column names for fetch all records from table

SELECT * from <Table name>

Try to use column names instead of * to ensure better performance.

Fetch data from customer table

ALTER TABLE

We can modify the Table structure using ALTER TABLE Statement.

TO ADD COLUMN IN TABLE

ALTER TABLE <table_name> ADD <column_name> <column-definition>;

Example

ALTER TABLE Custome ADD DateOfBirth DATETIME NULL

MODIFY COLUMN IN TABLE

ALTER TABLE <table_name> ALTER COLUMN <column_name> <column_type>;

EXAMPLE

ALTER TABLE Customer ALTER COLUMN Name NVARCHAR(500);

DROP COLUMN IN TABLE

ALTER TABLE <table_name> DROP COLUMN <column_name>;

EXAMPLE

ALTER TABLE Customer DROP COLUMN DateOfBirth;

RENAME COLUMN IN TABLE

The syntax to rename a column in an existing table in SQL Server (Transact-SQL) is:

sp_rename ‘table_name.old_column_name’, ‘new_column_name’, ‘COLUMN’;

Example

SP_RENAME ‘Customer.Name’, ‘CustomerName’, ‘COLUMN’;

The syntax to rename a table in SQL Server (Transact-SQL) is:

sp_rename ‘old_table_name’, ‘new_table_name’;

Example

sp_rename ‘Customer’, ‘CustomerDetails’;

TRUNCATE TABLE

The TRUNCATE TABLE statement is used to remove all records from a table in SQL Server. It performs the same function as a DELETE statement without a WHERE clause.

Syntax

TRUNCATE TABLE <table name>

Example

TRUNCATE TABLE CustomerDetails

DROP TABLE

DROP TABLE statement deletes both data and table definition permanently from the database.

This action cannot be undone.

Syntax:

DROP TABLE <Table name>

Example

DROP TABLE CustomerDetails