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.

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

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
  • [DOB]: DATE NOT NULL
• NULL properties as defined above
• Yes, [ID] column is a primary key column
• No, only clustered indexes needed

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)
)

Visibility into it all.

Monitor your databases and resolve performance issues quickly.

Try Foglight

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.

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).

• 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.

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