Create table in SQL Server


Before to start to create table in SQL Server we have to know some basic points regarding SQL Table. We use MS SQL Server table to store the information / data. SQL Server allows creating 2,147,483,647 tables in single database, where one table can have 1024 columns. While creating database we must have to have basic knowledge of data types in SQL Server , so we can store required data properly.



Tables are use to store data in rows and columns format. It's very easy to create table in SQL Server, We can create table in SQL Server by two different ways.

1) Create table in SQL Server using command.
2) Create table in SQL Server using UI.

Below table is called as physical table because it is physically available in database and scope of this table is till database remain in server.


Before create table create the database and after that, open new query editor by right click on respected database. Find below image.

Create table in SQL server.

Create table in SQL Server using command

Follow the steps to create the table.
Step 1 : Open text editor as above image show.

Step 2 : Write the create table command. Example given below.


Syntax to create table in SQL Server

CREATE TABLE < Table Name >
(
column1 < data type > ,
column2 < data type > ,
column3 < data type >
)

Create table in SQL Server with primary key get cover in below example because Id is primary key of table. Also we cover create table in SQL Server with primary key and identity because Id columns set as identity(1, 1).


Create table in SQL Server with example

CREATE TABLE tblStudentMaster
(
Id INT PRIMARY KEY IDENTITY (1,1),
StudentName VARCHAR (100) ,
Std TINYINT ,
ContactNumber VARCHAR (10)
)

See, after execution of query in below image new table created tblStudentMaster in StudentDB.

Create table in SQL server


Step 3 : Once table get created, now insert data in SQL Server table, to insert data in table refer the below command.

Syntax to insert data in SQL Server table

INSERT INTO < Table Name > (
Column1 ,
Column2 ,
Column3
) VALUES (
Value 1 ,
Value 2 ,
Value 3
)

Example

INSERT INTO tblStudentMaster (
StudentName ,
Std ,
ContactNumber
) VALUES (
'Vikrant Dalvi' , --String
10 , --INT
'90XXXXXX56'
)

Output

Select data from table in sql server


Note : If you observe the above statement you will see that we not provide the ID columns value using query. Because ID is primary key of tblStudentMaster and ID columns we set as identity columns, it means we not need to provide the value to ID column at time of data insertion. For more example refer above create statement of tblStudentMaster.
At the time of data insertion in SQL Server table we provides string value in single cote ex. 'Vikrant Dalvi' and numerical value without single cote ex. 10

Lear how to create primary key and foreign key in SQL Server with examples.

Add a column in existing table in SQL Server

To create new column in existing table we use ALTER keyword with table name and use ADD keyword to add new columns with table. We add new column in SQL table with allow null values.
Find below example which explain how to add new column in SQL Server table with command.

Example

-- Add new columns in table tblStudentMaster with allow null values
ALTER TABLE tblStudentMaster
ADD City VARCHAR(50) NULL

-- Select record from table.
SELECT * FROM tblStudentMaster

Output

add new column in SQL table with allow null values


In above example City columns we add, it shows null values default because while creating columns we specify NULL keyword. It mean that columns can have store null values in it.

Rename existing table column in SQL Server

SQL Server provides in-build procedure called sp_rename to rename the table or table columns. To rename the exiting columns in SQL table need to provide three parameters to sp_rename procedure that are table name along with column name, New column name and COLUMN as fix parameter. Last parameter that COLUMN is important because it tells the sp_rename procedure that we have to rename the given column.

Find below example which explain how to rename exiting column in SQL Server table with command.

Example

-- rename existing columns City from tblStudentMaster table to UserCity using sp_rename in built procedure
EXEC sp_rename 'tblStudentMaster.City' , 'UserCity' , 'COLUMN'

-- Select record from table.
SELECT * FROM tblStudentMaster

Output

rename existing column in SQL table example

Remove column from existing table in SQL Server

To remove columns from existing table in SQL Server we need to alter table and use DROP COLUMN keyword along with columns name to drop / remove columns from SQL Server.

Find below example which explain how to remove column from existing table in SQL Server table with command.

Example

-- drop existing columns from tblStudentMaster table
ALTER TABLE tblStudentMaster
DROP COLUMN City

-- Select record from table to refresh data.
SELECT * FROM tblStudentMaster

Output

rename existing column in SQL table example

Create table in SQL Server using UI

This is a very simple way to create table using UI, Follow the steps to create the table.
Expand the database where you want to create database.

Step 1 : Write click on table tab and click on Table.

create table in sql server using design UI

Step 2 : It will open table editor, enter the name of columns and select proper data types.

create table in sql server using design UI

Step 3 : Save Table (Table name tblStudentMarksDetail), and refresh table so, you will get new table created.

create table in sql server using design UI

Step 4 : To insert data in tblStudentMarksDetail right click on table and select Edit Top 200 Row.

create table in sql server using design UI


Note : We insert data in Student table. Now we will insert data in to tblStudentMarksDetail table. We are trying to show insert data in table using UI and crate relation in two table.

Step 5 : Insert the values in table and click enter, new row will create automatically.

create table in sql server using design UI

Lear how to create primary key and foreign key in SQL Server with examples.

If you select the both table data (tblStudentMaster, tblStudentMarksDetail) in view data will look like. Select data from both table for comparison purpose.

create table in sql server using design UI step 1

Add a column in existing table in SQL Server


To add new column in existing table in SQL Server using UI follow the below steps.

Step 1 : Open the Database from Object Explorer and expand table directory.

Step 2 : Right click on respected table click on design menu.
Add a column in existing table in SQL Server using UI step 2


Step 3 : Add new column in table design and click on save(or use shortcut Ctrl + S). Once save the column just refresh the table from SQL Server Object Explorer.
Add a column in existing table in SQL Server using UI step 3

Rename existing table column in SQL Server

To rename column in existing table in SQL Server using UI follow the below steps.

Step 1 : Follow the first two steps from above example and open the table in design mode.
rename existing column in sql server table


Step 2 : Click on column needs to rename, rename the column name and save. Once Save the columns refresh the table from object explorer to refresh the column names.
Add a column in existing table in SQL Server using UI step 2

Remove column from existing table in SQL Server

To remove column from existing table in SQL Server follow the below steps.
Step 1 : Expand tables columns from object explorer in SQL Server and right click and click on delete option.
Add a column in existing table in SQL Server using UI step 2


Step 2 : It will open delete object dialog box , click on OK. Now column will get removed from table.
Add a column in existing table in SQL Server using UI step 2

Move Top

To learn more about table creation click on Microsoft.

 



WebNetGuru provides the developer to learn and understand the technical concepts. We try to provide solution in easy and step by step way. We continuously keep updating content and topics, so we cannot warrant full correctness of all contents.
WebNetGuru team will try to keep updating new technology tutorial.
Current Tutorials
Upcoming Tutorials
  • ASP.NET MVC
  • Angular JS
  • Mongo DB
CONTACT US
  • contact@webnetguru.com
WORK WITH US
© Copyright 2020 WebNetGuru.com