Problem
Tables are one of the most used database objects and are used to store data in SQL Server databases. In my last tip I coveredcreating tables in SQL Server using SQL Server Management Studio Table Designer. In this tip, we will cover how to work with tables using T-SQL code.
Solution
Most of you may already know, T-SQL is Transact-SQL which is an extension of Structured Query Language (SQL). It is broadly used in all SQL Server databases and database objects like tables, functions, stored procedures, etc. One of the ways to run T-SQL statements is to connect to an instance of the SQL Server Database Engine and execute code in SQL Server Management Studio (SSMS).
Let's move forward and see how we can employ T-SQL to create tables in SQL Server.
Prerequisites
For a demonstration of this article, we need access to a SQL Server instance using SSMS. I am using SQL Server 2017 and SSMS v18.1, in case you don't have it, download SSMS from here.
Create Demo Database
Let's open SSMS and create a database named DemoDB (for demo purposes). You can use any existing database or you can simply create a new database to follow along. To create a new database, in SSMS right click on Databases, select New Databaseand enter your Database name.
Since, we are talking about T-SQL here, let's quickly create a database using a T-SQL statement, CREATE DATABASE. Execute the below command to create this database.
Now, let's consider an arbitrary case, where there is a requirement to monitor and track Grade 3 students data. As a layman, one would think of sorting and storing student-related information in different spreadsheets in Excel. For instance, personal details in one tab, departmental details in other and so on. This arrangement would work fine if there are not many changes in the personal data (like address, contact number, etc.) of students, but as you begin to get new admissions and also frequent updates in the existing data, it will cause redundancy leading to inefficiency. Also, you might have to put way more effort into maintaining data consistency and integrity.
As a SQL developer, the first thought that would cross your mind is to create a table in a SQL Server database and store Grade 3 students data in it. You can further create tables to build relationships between them. Before creating a table, we need to go over certain factors like, what will be the table name, what columns will it have, what will be the data types and if the columns will contain null/not null values. From this brief discussion, let's quickly derive the basic syntax to create a table using T-SQL.
Basic simple syntax to create a table using T-SQL in SQL Server
Here, the syntax uses the CREATE TABLE statement to create a new table with a specified existing schema and in the specified existing database name. This table name has to be unique and we can list the column names in the column definition along with itsdata type and indicate if the column will allow nulls or not.
CREATE TABLE examples using T-SQL
Let's understand and explore the above syntax with a few examples.
Note, I will be using the IF EXISTS clause to drop a table if it exists, this was introduced in SQL Server 2016. This tip shows the old and new syntax.
Execute the below query to create a table named, Grade3Students in the current database. Since we have not mentioned any schema name, it will be contained in the default schema dbo (dbo is the default, but a database user could have a different default schema). This table will store basic student information like StudentId, FirstName, LastName, DateOfBirth, Address, PhoneNumber and DepartmentId.
After the execution, refresh the Databases folder and you will be able to see table dbo.Grade3Students in the database DemoDB as shown below.
Creating tables under a different schema and a different database
If the database name is not explicitly specified, it will create the table in the current database. To create a table in a different database and under a different schema, we have to specify the database name followed by the schema name and the table name.
Say, we intend to create a table in the existing AdventureWorksDW2017 database and under schema Grade3, see four part namingfor more info.
Let's first create the schema Grade3 using the T-SQL CREATE SCHEMA statement and execute the following query to have a table created in the specified database and schema.
After executing the above query, the below table is created in the AdventureWorksDW2017 database using schema Grade3.
Quick note – From now until the end of this tip, we will be working on the database DemoDB and default schema dbo. In case, you want to target any specific database and a schema, you can mention their names while creating the table.
Creating Keys for Tables
We just discussed the simple syntax of creating a table using T-SQL, however, in relational databases like SQL Server, we mostly deal with constraints like Primary, Foreign, Unique, etc. to maintain logical relationships between tables and to enforce referential integrity.
A Primary Key is a special column or a combination of columns that uniquely identify a row or a record in a table. We can create only one primary key constraint per table. We usually use the keyword 'PRIMARY KEY' after the primary key column name to define a column as a primary key column, and if the primary key is comprised of two or more columns, we can move this primary key constraint to the table_constraints section. If these terms are too much for you to absorb for now, don't worry, we will look at them in detail shortly.
Below is the general syntax of creating a table with a column as a primary key and other table constraints.
Let's go ahead and work on the implementation with some examples using T-SQL.
Adding a Primary Key and Identity Column using T-SQL
It is pretty simple to set a column as a primary key and enable the identity property for it in a SQL table. In case, you are not aware of the identity property in a SQL table, it is basically a column whose value increments automatically with a given starting point and increment number. For information about Identity property in SQL Server, see here.
In the below syntax, to create StudentId as both a Primary Key constraint and an Identity column, we added keywords PRIMARY KEY and IDENTITY(1,1) to it. IDENTITY(1,1) means this column will start with Id 1 and increment the Id by 1 for a new record inserted. The primary key constraint column represents unique data, so it is a good idea to define it on an Identity column. In a nutshell, the StudentId column is an auto-incremented Primary Key in this table.
Highlight the table name, Grade3Students and use keyboard shortcut Alt + F1, to retrieve information about this table. It works the same way as this statement - sp_help Grade3Students. StudentId column has been identified as an Identity column and also set up as a Primary Key constraint, marked in red below. You can also see SQL Server has generated the constraint_name as pk_tablename_somerandomstring to keep it unique. By default, Database Engine creates a unique clustered index on the primary key column, if no particular index is specified.
At times, we have to refer to the name of the Primary Key constraint, especially in cases where we have to alter/drop this constraint. In such cases, it can be a hassle for you, other team members or DBA's to identify such complicated constraint names.
There is a way to both create and specify the primary key constraint name at the same time. Let's rewrite the above query by simply placing the keyword CONSTRAINT after the primary key column definition and followed by constraint_name(pk_Grade3Students_StudentId, I have followed this convention here -pk_tablename_primarycolumnname) and PRIMARY KEY as shown below.
One more way to address the above query is by specifying the constraint name (pk_Grade3Students_StudentId) and the Primary key column (StudentId) at the end in the table constraints section as shown below.
Options b) and c) have the upper side in the sense that it avoids system-generated constraint name, and creates a comprehendible primary key constraint name. Such practices help things be descriptive and clearer to understand especially when we have a team-based database development for the larger databases.
Enforcing Foreign Key relationships using T-SQL
One of the important concepts of relational databases is its referential integrity and Foreign Key (FK) constraint in SQL Server helps to enforce data integrity between tables. In simple words, FK is a column or a combination of columns in one table referencing the Primary Key of another table, thereby, it ensures that value in one table must be present in another table.
Let's try to understand this with the help of some simple scenarios:
The records in the table, Grade3Students we created above, can be associated with one or more records in other tables, say department they belong to, what courses they are enrolled in, extra-curricular activities they are participating in, and a lot more. Assuming, we have another table, named, Departments, each department can have many students enrolled in, leading to a one-to-many relationship.
We don't want to insert any record in the Grade3Students table that does not belong to any department. To ensure this data integrity, we will enforce an FK constraint in the CREATE Table statement.
To implement this, let's quickly create this master table, 'Departments' that contains only 2 columns, DepartmentId (Primary key) and DepartmentName. Below is the T-SQL syntax to create this table.
CREATE SQL Table with a Foreign Key reference
In order to enforce a relationship between these 2 tables, let's create a Foreign Key constraint. The following code creates the Grade3Students table, with the StudentId as a Primary Key and Identity column. The FK constraint references another table and links the DepartmentId in the Departments table to the DepartmentId in the Grade3Students table.
Also, FK (DepartmentId) in the Grade3Students table, can allow multiple instances of the same value. To dig in more about FK constraint, you can read this tip by one of my fellow authors and refer to this category as well.
Summary
We learned to create tables using T-SQL in this tip and also explored various important properties associated while creating these tables in SQL Server.
By: Gauri Mahajan