SQL Server Create Table in Management Studio

SQL Server Create Table – Learn how to create table using SQL Server Management Studio and also use of important data types. At the very being digital data store as the only file system. But now time to time improve the technology and store database as Relational Database System.

To increase the performance to read and write of data the technology use some techniques like indexing, organised data structure. So In this tutorial, we discuss those things related to Database Table.

Table of Contents

1. What is Table in Database?
2. Create Table in SQL Server Management Studio.
3. Write a script to create Table in SQL Server.
4. Data Types in SQL Server.

What is Table in Database?

In case of Database, the Table is a structure of data. Database management system store data as a two-dimensional table format. So the table is a pre define structure where we can insert data and fetch data when needed.

The table has multiple columns to store different types of data. So in the table, you need to define columns with a particulars data type.

Create Table in SQL Server Management Studio.

The SQL Server Management Studio is a very user-friendly tool to manage MS SQL Database. Before start create a table you should have knowledge about Data Type, Primary Key, Foreign Key length of data type etc.

After connecting with SQL Server Management Studio and creation of Database create the table. Follow the following steps to create a table in database.

Expand the database where you want to create the table the Right Click on “Tables” then click on “Table“. You will get a Table Creation window. In the table creation window three columns available.

1. Column Name
2. Data Type
3. Allow Nulls

Column Name

This is for the name of a unique column of your table. The column name of a table should be unique. Try to use data relevant column name. For example, if you want to store Id, Name, Age and DOB of a particular person. Then try to keep the column name of the table is “Id”, “Name”, “Age” and “DOB” respectively.

Data Type

In case of Table creation, the Data Type is very important. When you will create a table, should mention the type of data. It helps you to prevent invalid input.

For example, if you want to store an integer value then you need to set data type “int” or “biting”. similarly for store string value use “varchar” datatype. We will discuss the details of some important data types below. Now we create a table with some sample columns.

Allow Nulls

This option is for allowing “NULL” value therefore set this option “True” the value of this column can be “NULL” otherwise not.

Add column name with the appropriate data type. Please see the above screenshot. After entering all fields save the table. To save the table press [CTRL]+S and type the name of the table.

Write a script to create Table in SQL Server.

You can also write a script to create a table in SQL Server. We will use SQL Server Management Studio to run the script. See the syntax for Creation a Table in SQL Server.

CREATE TABLE
    { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( { <column_definition> } [ ,...n ] )
[ ; ]

Simplify form of the above syntax

CREATE TABLE [NAME OF TABLE]
(
  [COLUMN NAME 1] DATATYPE PRIMARY KEY
 ,[COLUMN NAME 2] DATATYPE NOT NULL
 ,[COLUMN NAME 3] DATATYPE
)

To create a table follow the step by step process.

Step 1: Open New Query window in SQL Server Management Studio – To open the “Query Window” press [CTRL]+N or click on “New Query” option from Management Studio tool.

Step 2: Type the script to create a Table – For example hare we create a table, the name of the table is “MyTable” with sample three column. The script is below.

CREATE TABLE MyTable
(
	Id INT PRIMARY KEY
	,Name VARCHAR(200) NOT NULL
	,Age INT
	,DOB DATE
)

Step 3: Click on “Execute” option or press the “F5” button to execute the script. Your table will create with the name.

Data Types in SQL Server

In the case of table creation, you can use so many data types in SQL Server as per your requirement. Data Types in SQL Server divided into seven different categories. The categories are below.

Categories of Data Types

1. Exact numerics
2. Approximate numerics
3. Date and time
4. Character strings
5. Unicode character strings
6. Binary strings
7. Other data types

Importance of Data Types

Being a good database design you should always remember so many things like data validation, use of perfect data type and space of the database. For example, you need to store a numeric value then you should be the use of the data types under Exact numerics.

Another example of the use of data type. Suppose you need to store some data of an employee like is “Age”, “EmployeeNo” and “Salary” then you need to use three different types of data type.

Though “Age” and “EmployeeNo” both are numeric type but you need to use “tinyint” data type for age and “int” for “EmployeeNo”. Because “tinyint” can store maximum 255 and the age of any employee can not greater than 255 but EmployeeNo can greater than 255. Therefore in the case of “EmployeeNo” use “int” data type. For salary, you can use “money” data type

As “int” data type takes 4 bites of data where “tinyint” takes 1 bite of data. So you can save 3 bites of data in case of age. So to design best database carefully choose the data types.

Exact numerics

In this Data Type, you can store numeric value only, Choose this data types depend on your requirement.

• bigint – 8 bites of storage required to store.
• bit – bit can store 1-bit data, the “True” converted to 1 and “False” converted to 0
• decimal – Storase depend on precision
• int– 4 Bytes
• money – 8 Bytes
• numeric– Same as decimal storage depend on Precision
• smallint– 2 Bytes
• smallmoney– 4 Bytes
• tinyint– 1 Byte

Approximate numerics

• float – Depend on n value like. 1-24 4 Bytes 25-53 8 Bytes
• real – 4 Bytes

Date and time

• date
• time
• datetime
• datetime2
• datetimeoffset
• smalldatetime

Character strings

• char
• varchar
• text

Unicode character strings

• nchar
• nvarchar
• ntext

Binary strings

• binary
• varbinary
• image

Other data types

• cursor
• rowversion
• hierarchyid
• uniqueidentifier
• sql_variant
• xml
• Spatial Geometry Types
• Spatial Geography Types
• table