SQL- Table

SQL Server Tables

Tables are used to store data in the database. It store data in in rows and columns. Each table contains one or more columns. And each column has a corresponding data type that specifies the kind of data it can store Ex - numbers, strings, or temporal data. Within a database and schema, each table has a distinct name.

To create a new table, you use the CREATE TABLE statement as follows:

CREATE TABLE [database_name.][schema_name.]table_name ( 
column_name1 data_type, 
column_name2 data_type,
column_name3 data_type, 
........, 
table_constraints 
); 

The above syntax defines following:

1. database_name - The database_name must be the name of an existing database in which we are going to create a table. It must be existing in the defined database. Otherwise, it will assume the current database by default.
2. schema_name - It specify the schema to which our newly created table belongs.
3. table_name - It is the name of the new table and must be unique in the selected database. It should be a maximum of 128 characters.
4. column_name - It specify the column names of the table along with data types for each column. These data types can be int, float, char, varchar, text, datetime, and Boolean. The columns in the table definition are separated by the comma operator.
5. table_constraints - It indicates the table constraints such as PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK, etc.

SQL Server Create Table Example

The following statement creates a new table named Employee.

CREATE TABLE Employee(
Emp_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(20),
Email_id varchar(100)
);

Now open Query window and run the above table script.

SQL Server Rename Table Example

The below example defines how to rename a table in SQL Server.

-- Rename Table name 
EXEC sp_rename 'EmployeeCopy', 'Employee';

--Rename table column name
EXEC sp_rename 'Employee.first_name', 'first_name1';

SQL Server Rename column, index, database Example

--You can specify the object type to indicate whether it is a column, index, database, etc.
EXEC sp_rename 'Employee.first_name1', 'first_name', 'COLUMN';

-- Database
EXEC sp_rename 'test', 'test1', 'database';

--index
EXEC sp_rename 'test', 'test1', 'index';

Add Columns in Table

The following adds a new column Address of type varchar and size 500 column to the Employee table.

ALTER 
TABLE dbo.Employee
Add Address varchar(500) NOT NULL;

The following adds three columns to the Employee table.

ALTER TABLE dbo.Employee
Add 
Address varchar(500) NOT NULL,
Designation varchar(50) NOT NULL,
Qualification varchar(100);

Rename Columns in Table

To rename a column in a table, use the following syntax:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Modify Column Data Type in Table

We can also use the ALTER command to change the column's data type into the specified table. SQL Server provides the ALTER TABLE ALTER COLUMN statement to modify the column data type. We can do this by using the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name 
new_data_type(size);

In this syntax, we will first specify the table name in which we want to make the modification. Then, we will specify the column name that we are going to modify and finally define its new data type with size.

We must ensure that old and new column data types must be compatible. Otherwise, SQL Server gives a conversion error if the column contains a value.

Example

We have already defined the 'Gender' column's datatype of the 'Student' table as 'VARCHAR' and length 20. Now, we want to change it from VARCHAR to NVARCHAR, and the size is 10. To do this, we will use the following syntax:

ALTER TABLE [Student] ALTER COLUMN Gender NVARCHAR(10); 

We can verify whether this column is changed or not from the table design option in SQL Server Management Studio as below:

Drop Columns in Table

Use ALTER TABLE DROP COLUMN statement to delete one or more columns of a table using T-SQL.

Syntax

ALTER TABLE 
[schema_name.]table_name 
DROP column column_name1, column_name2,... 
column_nameN;

The following deletes the Address column of the Employee table.

ALTER TABLE dbo.Employee
DROP COLUMN Address;

The following deletes multiple columns of the Employee table.

ALTER TABLE 
dbo.Employee
DROP COLUMN Address, PhoneNo, Email;

SQL Server Drop Table examples

When SQL Server drops a table, it also deletes all data, triggers, constraints, permissions of that table. Moreover, SQL Server does not explicitly drop the views and stored procedures that reference the dropped table.

Let’s see some examples of using the SQL Server DROP TABLE statement.

Drop a table that does not exist

The following statement removes a table named revenues in the sales schema:

DROP TABLE IF EXISTS sales.revenues;

In this example, the revenues table does not exist. Because it uses the IF EXISTS clause, the statement executes successfully with no table deleted.

Drop a single table example

The following statement creates a new table named delivery in the sales schema:

CREATE TABLE delivery (
deliveryid INT 
PRIMARY KEY,
deliverydate DATE NOT 
NULL
);

To remove the delivery table, you use the following statement:

DROP TABLE delivery;