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