A Beginner’s Guide to SQL Tables

In this article, we’ll explore the different types of SQL tables and best practices for creating user-defined tables in SQL Server.

Tables are primary objects for storing data in relational databases. To visualize a table, consider an Excel spreadsheet. The spreadsheet organizes data in a row and column format. Similarly, relational databases use multiple tables (sheets) to organize data.

  • Row: It uniquely identifies a record. For example, row 1 data belongs to an employee named [Raj]. It cannot store data for another employee.
  • Column: Each column defines an attribute of the table. For example, the [FirstName] column stores the first name for all employees. You cannot store other data such as [City] in this column.

SQL table example

Suppose we need an [Employee] table in a SQL database. This table stores a company’s employee records. For the [EmpID] column, we require a positive number (without any decimal point). In this case, the [integer] data type is the most suitable. Once you have defined the [EmpID] column as integer, SQL Server does not allow you to insert any values that do not satisfy the integer data type. For example, you cannot insert the string ‘Raj’ in the [EmpID[ column.

Creating a SQL table

Creating SQL table parameters

At a high level, to create a SQL table, we should have the following information:

  1. What is the SQL instance and database name for object storage?
  2. In which schema do you want to store the table?
  3. What are the column names for your specific table?
  4. What are the data types for these columns?
  5. Do the columns allow storing NULL values?
  6. Do you use the primary key column? If yes, which column will act as the primary key column?
  7. Do you want to create any non-clustered indexes?

For this demonstration, let’s use the following answers to these questions to create the employee table.

  • Answer 1: The target database name is [AzureDemoDatabase]
  • Answer 2: We want to store tables in the default DBO schema
  • Answer 3: The [Employee] table should have five columns: [ID], [FirstName], [LastName], [City] and [DOB]
  • Answer 4: The [Employee] table should use the following data types.
    • [ID]: Integer
    • [FirstName]: Varchar(50) NOT NULL
    • [LastName]: Varchar(30) NOT NULL
    • [City]: Varchar[50] NULL
  • NULL properties as defined above
  • Yes, [ID] column is a primary key column
  • No, only clustered indexes needed

Quest Blog Promo Banner

Quest Blog Promo Banner

Types of SQL tables

There are a variety of different SQL table types. SQL Server tables can be divided into the following categories.

System tables

SQL Server stores instance configuration and database properties in a special set of tables. These tables are known as system tables. Users are not permitted to make changes to these tables directly. SQL Server does not allow specific system tables to be queried directly. Instead, it provides system stored procedures, functions, SQL Server Management Objects and Replication Management Objects for querying these components. You can refer to Microsoft docs to understand system tables in greater detail.

Temporary tables

Sometimes, we need to store data temporarily in the database for calculation, manipulation or storing intermediate results. In these cases, we can utilize temporary tables that are always kept in the TempDB system database.

SQL Server has two types of temporary tables:

  • Local: Each local temporary table starts with a sign (#). Its scope is limited to the current connection. SQL Server automatically drops these tables once the user disconnects.
  • Global: Each global temporary table starts with a sign (##). All users can reference the global temporary tables. If all users that are referencing the global table are connected, SQL Server drops it.

–TEMPORARY Table ( Local )
Create table #TableA
ID int,
[Name] varchar(50)

–Global Temporary Table
Create table ##TableB
ID int,
[Name] varchar(50)

Permanent or user-defined table

Users can define their own table structure, columns, data types, constraints and indexes as per their application requirements. These tables are called user-defined tables.

These tables are always stored in the database unless someone explicitly drops them. Therefore, these are also known as permanent tables.

There are multiple ways to create a user-defined table in SQL Server.

  • SQL Server Management Studio GUI
  • Using T-SQL script

–Permanent Table
Create table TableA
ID int,
[Name] varchar(50)

Monitor the health and performance of all your database platforms with a robust database monitoring tool Foglight.

Visibility into it all.

Monitor your databases and resolve performance issues quickly.

External tables

External tables are a particular type of table that can be used in SQL Server 2016 onward. These tables reference other data sources such as Azure blob storage, Hadoop, Oracle, Excel, ODBC, Bigdata, MongoDB and Teradata using the PolyBase feature of SQL Server.

database platform logos

You can refer to this documentation to further explore PolyBase in SQL Server.

Graph tables

SQL Server graph databases use a collection of different nodes (vertices) and edges (relationships).

SQL table example of a graph table

  • Node table: The node table is a collection of nodes having similar types. For example, the person node table has all person nodes in a graph.
  • Edge table: The edge table is a collection of similar edges. For example, a friend table holds all edges that connect a person to another person.

Node and Edge table data in a SQL table

To explore this further, reference Graph database categories.

Guidelines for creating a user-defined table in SQL Server

  • Define an appropriate naming convention for table names.
  • Use different schemas to group similar table objects.
  • Always ensure that the referential integrity is maintained using the SQL Server constraints such as Primary key and Foreign key.
  • Always define a suitable data type and its length to quickly read and write data.
  • Use database normalization techniques to reduce data redundancy and improve data integrity.
  • Understand the different normalization forms and how to use them. (You can reference this article: What is Database Normalization in SQL Server?)
  • Define suitable indexes for your query workloads.
  • Always consult with database professionals for advice on database modeling and applying best practices

The zombie survival guide to database performance tuning

Don’t be a database zombie. Learn 6 expert steps for approaching modern database performance tuning.

Get the Guide

About the Author

Rajendra Gupta

Rajendra Gupta is a MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" in 2020 and 2021 at SQLShack.

Related Articles