SQL Server Create Tables

In previous posts, we have learnt about dropping database. In this post, we will learn about how to create tables in SQL server database. Before reading this post, you should read the post about data types in SQL Server because the concept of data type is co-requisite to learn about tables.

How to Create Tables in SQL Server database

Tables in SQL Server can be created in two ways - GUI approach and SQL Command. First, you see how a table can be created in SQL Server database using GUI.

How to Create Tables using GUI

SSMS (SQL Server Management Studio) also provides graphical approach to create a table. To create a table in a database, select the database and right click it. Now click the ‘New’ menu item. When you click it, it provides different sub menu items such as Table Temporary Table, External Table, File Table, Memory Optimize Table etc. Click the Table item to create table. 

As you have created AppliedkDB in the previous post, right click the AppliedkDB database. Click ‘New’ menu item and then ‘Table’ submenu item.

You find that there are other types of tables available in SQL Server e.g. Temporal and External Tables etc. You will learn about them in later posts. In this post, you will focus on creating Table which stores data permanently in database.

Table Designer: On clicking Table item, you get the Table Designer in the right pane as depicted below. Table Designer has two panes- upper and lower. Upper pane is used to add columns in table and lower pane to add properties to column. Fill the column name field and its data type e.g. Id, int to add a column as shown in the snapshot.

Similarly, more columns can be added with their data types in the Designer.

The bottom pane allows adding additional properties to a column. If you select a column in upper pane then corresponding column properties are reflected in the lower pane. Fill all the various information like column name, data type and Allow Nulls in the table Designer.

Additional properties of a column can be added by using Column Properties provided in the lower pane.

Setting properties on a column: You can set primary key on a column or can delete the column by right clicking it and clicking the relevant menu item. This is depicted in the below snapshot.

Save Table: After inserting columns in the table, you should save the table by pressing CTRL + S. Create Name dialog box appears to name the table. For example, write tbl_Persons name for the table. The table name should prefix with tbl for recognizing database object as table.

Refresh Database or Tables: The newly created table is saved in the database. Refresh AppliedkDB database or Tables to view the newly created table.

Generate script: The table created using Designer generates CREATE TABLE script which can be viewed as shown below. Right click the table which script is to be generated. Click Script Table as and then CREATE To and then click New Query Editor Window. The table script will be generated.

SQL Script Approach

To create a table in a database, you use CREATE TABLE statement. The syntax is as follows.

After CREATE TABLE command you write the name of the table to be created and thereafter all its columns and columns’ properties are written inside square brackets.

CREATE TABLE table_name (c1 d1 p1, c2 d2 p2, c3 d3 p3,…) where c, d and p stands for column name, its data type and other properties respectively.

Each column name of the table must follow the rules for identifiers defined in SQL Server. After the column name data type must be a specified. Apart from data type, you can also mention different constraints or properties if applicable on the column. Finally you can apply nullability property on the column. Identity is a special property of a column which is used to automatically generate the primary key values. Identity property should be applied on a column with its seed value and step value; by default they are 1.

Fully qualified table name will be database name then owner name and then table name. They will be separated by dot operator. The database name and owner name are optional. If you are referring the table in the same database in which the table belongs then database name can be omitted. Likewise, if table belongs to default schema DBO then schema name can be omitted as well.

Designing Database Tables

Note that a table must be designed properly after a lot of thought because once you design a table it is very difficult to alter table data types without some side effect. Also, table represents database entity and in many cases one table has relation with other tables. So, designing database tables is of prime importance for performance of the database.

When should you use choose char data type over varchar data type?

There is a tradeoff between performances of database versus size of database when you choose a data type. The char data type is fixed length string representation. It occupies more space but provides better performance or speed of the database.

Remember that the size of row of a table depends upon the data type defined for each column of the row.

What is SELECT INTO command? 

Select into command is used to create a new table from an existing table all the data are copied into the new table you can also filter the existing table before inserting into the new table.

Note that just like TRUNCATE TABLE command SELECT INTO operation does not log the new records into the log file.

In the next post, you will learn how to insert rows into table in SQL Server in different ways.