Manage SQL Server tables

The tutorials in this section explains how to create and manage SQL Server tables. And shows the syntax by example. These queries allow you to manage SQL Server tables, which are the core of Microsoft’s relational databases.

In other words, how to create, modify, copy, dump and delete one or more tables. That is, how to manipulate these objects with T-SQL commands.

Basic SQL Server examples for creating and managing tables

Here are some computer tutorials in French to manage tables stored in SQL Server, i.e. in the Microsoft DBMS. The first part explains the basic queries for management.

The second part of the tutorials explains advanced concepts such as table partitioning for example.

This example script uses the CREATE TABLE command to create a sales table. The script uses columns to store the time at month level, the employee number and the sales amounts.

T-SQL example to create an MS SQL table

This table uses several data types to show a range of formats available with SQL Server. However, it also makes sense to calculate amounts on the fly with the VAT value stored once in another table, for example with the year.

First, how do you create a SQL Server table with the CREATE TABLE command?

-- Test if the table already exists
-- If it doaes then delete the table
IF EXISTS(
   SELECT 1 FROM sys.objects
   WHERE object_id = object_id(N'[dbo].[SALES]')
      AND type in (N'U')
)
DROP TABLE [dbo].[Sales]
GO

-- Create the example table to store SALES
CREATE TABLE [dbo].[Sales] (
   [MonthName] nvarchar(20),
   [Amount] numeric(8)
)
GO
Script to manage SQL tables with a CREATE TABLE SQL Server script
Script to manage SQL tables with a CREATE TABLE SQL Server script

After the table creation, insert a few rows in the table to handle data for the first three months of the calendar year.

-- Insert sales data for our example
INSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'January', 1000);
INSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'February', 2000);
INSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'March', 3000);
GO

-- Select results to check table contents
SELECT    *
FROM    dbo.Sales;
Script to insert data into SQL Server tables with the INSERT INTO statement
Script to insert data into SQL Server tables with the INSERT INTO statement

Script to modify a table (ALTER TABLE)

Next, how to modify a SQL Server table with ALTER TABLE to add or remove a column for example. Or to change the size of a text field or the type of a column.

First, to modify a table and add constraints to control the integrity of the data entered, use the ALTER TABLE and ADD CONSTRAINT commands.

Add a constraint on the Sales table to check that the month name column is not null.

ALTER TABLE [dbo][SALES] WITH CHECK
ADD CONSTRAINT Month_check CHECK (MonthName IS NOT NULL);
GO

It is now possible to insert only non-zero values for the month column into the sales table.

-- Insert three more rows in the sales table
INSERT INTO [DBO].[SALES] VALUES ('March', 1000);
INSERT INTO [DBO].[SALES] VALUES ('April', 2000);
INSERT INTO [DBO].[SALES] VALUES ('May', 3000);

-- This line will not be inserted because there is a null value for the month
INSERT INTO [DBO].[SALES] VALUES (null, 5000);

If the month is null then this SQL Server error appears:

The INSERT statement conflicted with the CHECK constraint “Month_check”.
The conflict occurred in database “Expert-Only”, table “dbo.SALES”, column ‘MonthName’.

INSERT INTO query from a SELECT with SQL Server

To go further and insert rows from a selection query, use the Insert Into query directly from a SELECT statement.

INSERT INTO [dbo].[SALES] ([MonthName], [Amount])
SELECT N'January', '1', 1000 UNION ALL
SELECT N'February', '2', 2000 UNION ALL
SELECT N'March', '3', 3000;

Adding a column to a SQL table (ALTER COLUMN)

Then, to evolve a table, adapt this example to change the length of the columns with an ALTER COLUMN script.

-- Change the length of the month name from 20 to 100 characters
ALTER TABLE [dbo].[SALES].
ALTER COLUMN [MonthName] NVARCHAR(100);
GO

Deleting data from a SQL table with Microsoft (DELETE FROM)

Third, to delete data with a filter, use this script and adapt it to the needs of the project. This script deletes the data for the first three months, January, February and March.

DELETE FROM [DBO][SALES]
WHERE MonthName in ('January', 'February', 'March');

Empty all data from an MSSQL table (TRUNCATE TABLE)

Furthermore, before completely deleting a table and thus the contents and structure, it is possible to empty the contents of the MS SQL table with the TRUNCATE TABLE command.

To clear all data from a SQL Server table without any filters, use the TRUNCATE TABLE script.

TRUNCATE TABLE [dbo].[SALES];

Check if a table exists and delete it (IF EXISTS and DROP TABLE)

It is possible to test if a SQL Server table exists before deleting it to avoid errors like “Can’t find the object because it doesn’t exist or you don’t have the necessary permissions“?

IF EXISTS (
	SELECT 1 FROM sys.objects
	WHERE object_id = object_id(N'[dbo].[SALES]')
	AND type in (N'U') )
BEGIN
	DROP TABLE [dbo].[SALES]
END;
GO

Delete a SQL Server table (DROP TABLE)

To completely delete a SQL Server table, use the T-SQL DROP TABLE command. This means that the structure and contents are deleted. Note that this action is irreversible and that it is therefore necessary to save the code of the table beforehand.

Use this T-SQL command to delete a SQL Server table, i.e. the contents of the table and its structure.

DROP TABLE [dbo].[SALES];
GO
Check if table exists in SQL Server

Administer and optimize query performance on SQL Server tables

This second part on SQL tables deals with advanced topics. For example, listing certain objects and their characteristics or optimizing query performance.

Create a SQL table with a primary key

A primary key is used to uniquely identify a row in a table. Here is a script to create a table and define a column as a primary key.

CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

Creating a SQL Server table with partitions

To improve performance it is possible to create a SQL Server table with partitions. Indeed, the SQL partitioning allows to separate the data of a same table on different groups of independent files. The data is split according to the value of a column.

This intelligent partitioning allows for optimised performance each time the partitioning column is used in a query.

Display a list with the size of the MSSQL tables and the disk space used

Here is a short tutorial to list the tables with their size and the disk space used by each object.

SELECT
     sch.name as SchemaName,
     tab.name as TableName,
     par.rows as RowCounts, 
     sum(alc.total_pages) * 8 as TotalSpace,
     sum(alc.used_pages) * 8 as UsedSpace,
     (sum(alc.total_pages) - sum(alc.used_pages)) * 8 as UnusedSpace
FROM sys.tables tab 
INNER JOIN sys.indexes ind 
     ON tab.object_id = ind.object_id 
INNER JOIN sys.partitions par 
     ON ind.object_id = par.object_id 
     and ind.index_id = par.index_id 
INNER JOIN sys.allocation_units alc 
     ON par.partition_id = alc.container_id 
LEFT OUTER JOIN sys.schemas sch 
     ON tab.schema_id = sch.schema_id 
GROUP BY 
     tab.name, 
     sch.name, 
     par.rows 
ORDER BY 1,2;

SQL query to list user-defined table types

This SQL tutorial explains how to list user-defined table types with a T-SQL script. These User Defined Tables (UDT) are objects of type table. Or to be more precise, table variables.

SELECT
     name, 
     system_type_id, 
     user_type_id, 
     schema_id, 
     principal_id, 
     max_length, 
     precision, 
     scale, 
     collation_name, 
     is_nullable, 
     is_user_defined, 
     is_assembly_type, 
     default_object_id, 
     rule_object_id, 
     is_table_type
FROM      SYS.TABLE_TYPES
WHERE      IS_USER_DEFINED = 1;

Finally, these simple query examples show how to create, delete, modify, dump, delete or optimise SQL tables.

This article, which I recommend to keep as a favorite and to share, reminds the basic syntaxes on SQL Server tables.

Moreover, the details of each operation are not listed here, because the objective is to have the syntax at hand as quickly as possible.

List user defined table types in SQL Server (UDT)