This post is part of my series on an “Introduction to SQL Server”. A note before you start reading, this is not a modelling post. data modelling falls outside the scope of this post and probably this series. However, if you are just getting started with SQL Server, it is important you understand tables. Tables are the fundamental building blocks of a SQL Server database. They store data in rows and columns, with each table representing a specific entity within the database. For example, if you are creating a database to manage customer information, you would have a table for customers, with each row representing a single customer.

Let’s use a subset of the Adventureworks database to clarify what I mean. I have used LucidChart to create a diagram that showing some of the tables or entities of the adventure works database. You can see we have Customer, SalesPerson and Employee Entities or tables that represent what that database is modelling in the real world.

Columns in a table are the attributes that define the characteristics of the entity the table represents. In a customer table, common attributes for employee might include:

• Name: The employees full name.
• Email: The employee’s email address.
• Phone Number: The employee’s contact number.
• Address: The employees ‘s physical address. (This might be stored in a separate entity)

These are not displayed on the diagram above. Each of these attributes is represented by a column in the employee table.

1. Numeric Data Types:

   • int: An integer data type for whole numbers. Range: -2,147,483,648 to 2,147,483,647.
   • smallint: A smaller range integer data type. Range: -32,768 to 32,767.
   • tinyint: An even smaller range integer data type. Range: 0 to 255.
   • bigint: A larger range integer data type for very large whole numbers. Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
   • decimal (p, s): A fixed precision and scale numeric data type. Example: decimal(10,2) can store numbers up to 10 digits with 2 decimal places.
   • numeric (p, s): Functionally equivalent to decimal.
   • float: A floating-point number data type for storing very large or very small numbers. Range: -1.79E+308 to 1.79E+308.
   • real: A floating-point number with a smaller range than float. Range: -3.40E+38 to 3.40E+38.

2. Character String Data Types:

   • char(n): A fixed-length character string. Example: char(10) always stores 10 characters.
   • varchar(n): A variable-length character string. Example: varchar(50) can store up to 50 characters.
   • text: A large variable-length character string (deprecated in favour of varchar(max)).

3. Unicode Character String Data Types:

   • nchar(n): A fixed-length Unicode character string.
   • nvarchar(n): A variable-length Unicode character string.
   • ntext: A large variable-length Unicode character string (deprecated in favour of nvarchar(max)).

4. Date and Time Data Types:

   • date: Stores only the date. Example: 2024-05-29. Range: 0001-01-01 to 9999-12-31.
   • time: Stores only the time. Example: 13:45:30. Range: 00:00:00.0000000 to 23:59:59.9999999.
   • datetime: Stores both date and time. Example: 2024-05-29 13:45:30. Range: 1753-01-01 to 9999-12-31.
   • smalldatetime: Stores both date and time with less precision. Range: 1900-01-01 to 2079-06-06.
   • datetime2: An extended version of datetime with larger range and precision. Range: 0001-01-01 to 9999-12-31.
   • datetimeoffset: Includes time zone awareness. Range: 0001-01-01 to 9999-12-31.

5. Binary Data Types:

   • binary(n): A fixed-length binary data.
   • varbinary(n): A variable-length binary data.
   • image: A large variable-length binary data (deprecated in favor of varbinary(max)).

6. Other Data Types:

   • bit: A boolean data type, can store 0, 1, or NULL.
   • uniqueidentifier: Stores a globally unique identifier (GUID).
   • xml: Stores XML data.
   • sql_variant: Stores data of various SQL Server-supported data types, except text, ntext, and image.

Creating a table in SQL Server involves defining its columns and their data types. You can do this using the GUI (SSMS oro Azure Data Studio) Or you can use T-SQL code. If you are new to SQL Server and don’t know the T-SQL code, the GUI can be very helpful.

Follow these steps to create a table in SSMS, I will create a table called customer using the definition above:

1. Open SQL Server Management Studio: Launch SSMS and connect to your SQL Server instance.

2. Connect to the Database Engine: In the “Connect to Server” window, select the appropriate server type, server name, and authentication method, then click “Connect.” I will use Windows Authentication

3. Expand the Database: In the Object Explorer, expand the database in which you want to create the new table. I’ll use AdventureWorks2024

4. Navigate to Tables: Right-click the “Tables” folder and select “New Table…”

5. Define Columns: In the table designer window, define the columns for your table:

   • Column Name: Enter the name of the column (e.g., CustomerID, Name, Email).
   • Data Type: Select the appropriate data type from the drop-down list (e.g., int, varchar(50), datetime).
   • Allow Nulls: Check or uncheck the box to specify whether the column can accept NULL values.

6. Set Primary Key (Optional): To set a primary key, right-click the row selector of the column you want to set as the primary key (e.g., CustomerID) and select “Set Primary Key.” While the primary key is optional for SQL Server, it’s a good practice for all meaningful tables to have a primary key.

7. Save the Table: After defining all columns, click “Save” in the toolbar. Enter a name for your table (e.g., Customers) and click “OK.”

8. Verify Table Creation: In the Object Explorer, expand the “Tables” folder to see your newly created table. Expand the table to view its columns and properties.

By following these steps, you can create a well-structured table in SQL Server, ensuring that your data is organised, accessible, and secure.

Conclusion and Summary

Creating tables in SQL Server is a foundational skill that every database administrator and developer should master. By understanding how to define tables and their columns, you are taking the first step towards building robust and efficient databases. This post has walked you through the essentials of table creation, from understanding the role of tables and columns to defining data types and using SQL Server Management Studio (SSMS) for practical implementation.

Remember, tables are the building blocks of your database, and getting them right from the start will save you time and effort in the long run. Whether you are managing customer data, sales information, or any other entity, well-structured tables ensure your data is organized, accessible, and secure.

Sign Up for a Free SQL Server Health Check

Are you confident that your SQL Server environment is running at its best? Regular health checks can identify performance bottlenecks, security vulnerabilities, and configuration issues that might be hindering your database’s performance.

Sign up for a Free SQL Server Health Check today! Our team of experts will analyze your SQL Server setup, provide detailed insights, and recommend best practices to optimize your database’s performance and security. Don’t miss out on this opportunity to ensure your SQL Server environment is in top shape. Click here to schedule your free health check now!