Transact-SQL (TSQL): basics for beginners

Transact-SQL (TSQL) directory will look like this, first I will give a small table of contents with navigation, then the directory itself will start, on each item will be comments, explanations and examples. Also, if we have already looked at or used an object or action in detail somewhere in the materials on our site, I will of course link to see the detailed examples or how you can use the action in practice.

Since it is simply impossible to cover everything, so don’t be surprised if you don’t find something here. Once again, this guide is designed for beginner programmers in Transact-SQL, as well as for simple admins who periodically need to offload some data from the SQL server.

Database in TSQL

Even a novice Transact-SQL programmer should know how to create a database or change its properties, so before we look at tables, views, functions and everything else, let’s take a look at the process of creating, modifying and deleting a database in Transact-SQL.

Creating a Database

In order to create a database, you need to perform the following query:

CREATE DATABASE test

where, test is the name of the database.

Database deletion

If you need to delete the database, you can use the query:

DROP DATABASE test

Changing the database

To change the database parameters, you can use the Management Studio GUI, where all parameters are described in detail, or you can send ALTER DATABASE queries, for example, to enable automatic compression of the test database, we use the following query

ALTER DATABASE test SET AUTO_SHRINK ON;
--A for shutdown
ALTER DATABASE test SET AUTO_SHRINK OFF;

Hopefully understandable, ALTER DATABASE command to change, test name of the changed database, SET command indicating that we will change the database parameters, AUTO_SHRINK parameter itself, ON/OFF parameter value.

Data Types in TSQL

Most common and frequently used

Exact numbers

• tinyint – 1 byte
• smallint – 2 bytes
• int – 4 bytes
• bigint – 8 bytes
• Fixed-accuracy and decimal (type with fixed precision and scale)
• money – 8 bytes
• smallmoney – 4 bytes

Approximate numbers

• float [(n) ] – size depends on n (n can be from 1 to 53, by default 53)
• real – 4 bytes

Date and time

• date – date
• time – time
• datetime – a date that includes the time of day with fractions of a second in 24-hour format.

Symbolic strings

• char [ ( n ) ] – a fixed length string, where n is the length of the string (from 1 to 8000). The size when stored is n bytes.
• varchar [ ( ( n | max ) ] – fixed length string, where n is the string length (from 1 to 8000). If you specify max, the maximum size during storage will be 2^31-1 bytes (2 GB), and if you specify n, the actual length of the data entered plus 2 bytes.
• text – string data of variable length, the maximum size will be 2 147 483 647 bytes (2 GB).
• nchar [ ( n ) ] – fixed length string in Unicode, where n is the string length (from 1 to 4000). The storage size is twice the value of n in bytes.
• nvarchar [ ( ( n | max ) ] is a fixed length string in Unicode, where n is the length of the string (from 1 to 4000). If max is specified, the maximum storage size will be 2^31-1 bytes (2 GB), and if n, the actual length of the entered data plus 2 bytes.
• ntext – string data of variable length, with a maximum string length of 1 073 741 823 bytes.

Binary data

• binary [ n ) ] – binary data with a fixed length, the size of n bytes, where n value from 1 to 8000. The size during storage is n bytes.
• varbinary [ ( n | max) ] – binary data with variable length, where n may have a value from 1 to 8000. If you specify max, the maximum size during storage is 2^31-1 bytes (2 GB). If n is specified, the storage size is the actual length of entered data plus 2 bytes.
• image – binary data of variable length, size from 0 to 2^31 – 1 (2 147 483 647) bytes.

Others

• xml – storage of xml data. We discussed in detail in the material Transact-sql – working with xml, and if you do not know what XML is at all, we talked about it in the article XML Fundamentals for beginners.
• table – storing the resulting string set.

Tables in TSQL

There are enough examples of creating tables on this site, because in almost every SQL related article I give an example of creating a test table, but to consolidate knowledge, let’s create, modify and delete a test table. Let’s take a look at how field data types are defined in Transact-SQL tables.

Create

CREATE TABLE test_table(
[id] [int] IDENTITY(1,1) NOT NULL,--identifier, integer int, not allowed value NULL
[fio] [varchar](50) NULL, -FIO, string length 50 characters, NULL values allowed
[summa] [float] NULL, -- sum, approximate numerical value, NULL values are allowed
[date_create] [datetime] NULL, -- date and time, NULL values allowed.
[comment] [varchar](100) NULL -- 100 character string, NULL values allowed
) ON [PRIMARY]
GO

Adding a column

ALTER TABLE test_table ADD prosto_pole numeric(18, 0) NULL

Where,

• test_table is a table name;
• add is the command to add;
• prosto_pole – column name;
• pole numeric(18, 0) – data type of the new column;
• NULL – parameter meaning that in this field you can store the value NULL.

Change of data type

Let’s change the data type of the new field that we just created (prosto_pole) from numeric(18, 0) to bigint and increase the comment field length to 300 characters.

ALTER TABLE test_table ALTER COLUMN prosto_pole bigint;
ALTER TABLE test_table ALTER COLUMN comment varchar(300);

Note! SQL server will not be able to perform data type changes if conversion of values in these fields is impossible, in this case you will have to delete the column with all data and re-add or clear all data in this field.

Deleting a column

To delete a certain column we use the drop command, for example, to delete the prosto_pole field we use the following query:

ALTER TABLE test_table DROP COLUMN prosto_pole

Deleting the table

In order to remove a table, let’s write this simple query where test_table is the table to be removed.

DROP TABLE test_table

Submissions in TSQL

A very useful object in the database is a view (VIEW) or in our opinion a simple view. If anyone does not know, a view is a kind of stored query that can be accessed in the same way as a table. Let’s create a view based on the test_table, and let’s assume that very often we need to write a query, for example, a sum greater than 1000, so in order not to write this query every time we will write a view once and then we will refer to it later.

Creation

CREATE VIEW test_view
AS
SELECT id, fio, comment
FROM test_table
WHERE summa > 1000
GO

An example of how to address a performance:

SELECT * FROM test_view

Modification

ALTER VIEW test_view
AS
SELECT id, fio, comment
FROM test_table
WHERE summa > 1500
GO

Deletion

DROP VIEW test_view

System Views

MS SQL Server has system objects that can sometimes provide quite useful information, such as system views. We’re gonna go through a couple of these shows now. They can be accessed in the same way as normal views (e.g. select * from the name of the view).

• sys.all_objects – contains all database objects, including such parameters as: name, type, creation date and others.
• sys.all_columns – returns all columns of tables with their detailed characteristics.
• sys.all_views – returns all database views.
• sys.tables – all database tables.
• sys.triggers – are all database triggers.
• sys.databases – all databases on the server.
• sys.sysprocesses – active processes, sessions in the database.

There are actually a lot of them, so it’s not going to work out. If you want to see how they can be used in practice, we have already done this, for example, in the materials How to learn about active user sessions in MS Sql 2008.

Functions

MS SQL server allows you to create functions that will return certain data, in other words, the user himself can write a function and further use it, for example, when it is necessary to obtain values that require complex calculations or complex data sampling. Sometimes just to reduce the code, when a function call will replace often required values in different queries and applications.

Creation

CREATE FUNCTION test_function
(@par1 bigint, @par2 float)
RETURNS varchar(300)
AS
BEGIN
DECLARE @rezult varchar(300)
SELECT @rezult=comment
FROM test_table
WHERE id = @par1 AND summa > @par2

RETURN @rezult
END

Where,

• CREATE FUNCTION – command to create an object function;
• test_function – name of the new function;
• @par1 and @par2 – are the input parameters;
• RETURNS varchar(300) – type of return result;
• DECLARE @rezult varchar(300) – declaration of a variable with the type varchar(300);
• Instruction select in our case is the function action;
• RETURN @rezult – return the result;
• BEGIN and END are the beginning and end of the function code respectively.

An example of using it in a query:

Modification

ALTER FUNCTION test_function
(@par1 bigint, @par2 float)
RETURNS varchar(300)
AS
BEGIN
DECLARE @rezult varchar(300)
SELECT @rezult=comment
FROM test_table_new
WHERE id = @par1 AND summa >= @par2

RETURN @rezult
END

Deletion

DROP FUNCTION test_function

Built-in features in Transact-SQL

Besides the fact that SQL server allows you to create custom functions, it also provides an opportunity to use built-in functions that have already been written for you by DBMS developers. There are a lot of them, so I divided the most common ones into groups and tried to describe them briefly.

System functions

Here I will give some examples of functions that return different system data:

• @@VERSION – returns the SQL server version;
• @@SERVERNAME – returns the server name;
• SUSER_NAME() – the name of the user entering the server, in other words, the login under which this or that user works;
• user_name() – database user name;
• @@SERVICENAME – DBMS service name;
• @@IDENTITY is the last identifier inserted in the table;
• db_name() – name of the current database;
• db_id() – database identifier.

Aggregate functions

Functions that calculate a value based on a set (group) of values. If you need to specify a column to output the result when calling these functions, you need to perform grouping of data (group by) by this field. We considered this construction in detail in the article Transact-SQL grouping of data group by:

• avg – returns the average value;
• count is the number of values;
• max is the maximum value;
• min is the minimum value;
• sum is the sum of values.

Example of use:

SELECT COUNT(*) as count,
SUM(summa) as sum,
MAX(id) as max,
MIN(id) as min,
AVG(summa) as avg
FROM test_table

String functions

This type of functions works with strings, respectively.

Left (string expression, number of characters) – returns the specified number of characters of the string starting from the left.

Example:

SELECT LEFT("Example of the left function operation", 10)
-- Output 'Example by'

Right (string expression, number of characters) – returns the specified number of characters of the string starting from the right

Example:

SELECT Right("Example of the Right function operation", 10)
-- The result of the 'Right'.

Len (string) – returns the string length.

Example:

SELECT len("Example of len function operation")
-- Result 28

Lower (string) – returns a string in which all characters are in lower case.

Example:

SELECT lower("Example of the lower function operation")
-- Output 'example of a lower function'.

Upper – returns a string in which all characters are upper case.

Example:

SELECT Upper('Example of Upper Function Operation')
-- Output 'UPPER FUNCTION EXAMPLE'.

Ltrim (string) – returns a string in which all initial spaces have been removed.

Example:

SELECT ltrim(' Example of the ltrim function's operation')
-- Result 'Example of ltrim function operation'.

Rtrim (line) – returns a line in which all spaces on the right are deleted

Example:

SELECT Rtrim (' Example of Rtrim function operation ')
-- Result' Example of the Rtrim function.

Replace (string what we are looking for, what we replace with) – replaces in a string expression all occurrences specified in the second parameter with characters specified in the third parameter.

Example:

SELECT Replace ('Example of the Replace', 'Operation', 'Replace' functions)
-- Result 'Example of Replace'.

Replicate (string, number of repetitions) – repeats the string (first parameter) as many times as specified in the second parameter.

Example:

SELECT Replicate ("Example Replicate ', 3 )
-- Result 'Example Replicate Example Replicate '.

Reverse (string) – returns everything in reverse order.

Example:

SELECT Reverse ("Reverse function example")
-- Result of 'elpmaxe noitcnuf esreveR'.

Space (number of spaces) – returns a string as the specified number of spaces.

Example:

SELECT Space(10)
-- " The result '          '

Substring (string, initial position, how many characters) – returns a string that is the length of the number specified in the third parameter, starting with the character specified in the second parameter.

Example:

SELECT Substring("Example of Substring Function Work", 11, 14)
-- Result of the 'function work'

Mathematical functions

Round (number, accuracy of rounding) – rounds the numeric expression to the number of characters specified in the second parameter

Example:

SELECT Round(10.4569, 2)
-- Result '10.4600'

Floor (number) – returns an integer number rounded down to the smaller side.

Example:

SELECT Floor(10.4569)
-- Result '10'

Ceiling – returns an integer number rounded up.

Example:

SELECT Ceiling (10.4569)
-- Result '11'

Power (number, degree) – returns the number elevated to the degree specified in the second parameter.

Example:

SELECT Power(5,2)
-- Result '25'

Square (number) – returns a numeric value placed in a square

Example:

SELECT Square(5)
-- Result '25'

Abs (number) – returns an absolute positive value

Example:

SELECT Abs(-5)
-- Result '5'

Log(number) – natural floating point logarithm.

Example:

SELECT Log(5)
-- Result '1,6094379124341'

Pi is the number of pi.

Example:

SELECT Pi()
-- Result '3,14159265358979'

Rand – returns a random floating point number from 0 to 1

Example:

SELECT rand()
-- Result '0.713273187517105'

Date and time functions

Getdate() – returns the current date and time

Example:

SELECT Getdate()
-- Result '2020-10-24 16:36:23.683'

Day(date) – returns the day from the date.

Example:

SELECT Day(Getdate())
-- Result '24'

Month(date) – returns the month number from the date.

Example:

SELECT Month(Getdate())
-- Result '10'

Year (date) – Returns the year from the date

Example:

SELECT year(Getdate())
-- Result '2020'

DATEPART (date section, date) – returns the specified section from the date (DD,MM,YYYY etc.).

Example:

SELECT DATEPART(MM,GETDATE())
-- Result '10'

Isdate – checks the entered expression if it is a date

Example:

SELECT Isdate(GETDATE())
-- Result '1'

Conversion Functions

Cast (expression as data type) – function to convert one type into another. In this example, we convert float type to int.

Example:

SELECT CAST(10.54 as int)
-- Result 10

Convert – (data type, expression, date format) – function to convert one data type into another. Very often it is used to convert the date, using the third optional parameter – date format.

Example:

SELECT GETDATE(), CONVERT(DATE, GETDATE(), 104)
-- Result
-- 2020-10-24 15:20:45.270 - without conversion;
-- 2020-10-24 after conversion.

Table functions in Transact-SQL

They are created in order to receive data from them as from tables, but after various calculations. We talked about table functions in detail in the material Transact-sql – Table functions and time tables.

Creation

--title of our function
CREATE FUNCTION fun_test_tables
(
--input parameters and their type
@id INT
)
--rotating value, i.e. table
RETURNS TABLE
AS
--instantly return the result
RETURN
(
--query or some kind of calculation
SELECT * FROM test_table where id = @id
)
GO

Modification

--title of our function
ALTER FUNCTION fun_test_tables
(
--input parameters and their type
@id INT
)
--rotating value, i.e. table
RETURNS TABLE
AS
--instantly return the result
RETURN
(
--query or some kind of calculation
SELECT * FROM test_table where id = @id and summa > 100
)
GO

Deletion

DROP FUNCTION fun_test_tables

As you can see, in order to create, change or delete such functions, the same operators are used as for normal functions, the only difference is the type of the function returns.

Example of accessing this function:

SELECT * FROM fun_test_tabl(1)

Procedures

Procedures are a set of SQL instructions that are compiled once and can accept, as well as functions, different parameters. Used to simplify calculations, to perform group actions.

Creation

CREATE PROCEDURE sp_test_procedure
(@id INT)
AS
-- declare variables
DECLARE @sum FLOAT
-SQL instructions
SET @sum = 100
UPDATE test_table SET summa = summa + @sum
WHERE id = @id

GO

Modification

ALTER PROCEDURE sp_test_procedure
(@id int)
AS
-- declare variables
DECLARE @sum float
-- SQL instructions
SET @sum = 500
UPDATE test_table SET summa = summa + @sum WHERE id = @id
GO

Deletion

DROP PROCEDURE sp_test_procedure

Procedure call

You can call in many different ways, like:

EXECUTE sp_test_procedure 1
--or
EXEC sp_test_procedure 1

Where, EXECUTE and EXEC procedure call, sp_test_procedure respectively the name of our procedure, 1 value of parameter.

Systemic procedures

System procedures are procedures for performing various administrative actions both over objects on the server and over the configuration of the server itself. They are called in the same way as normal procedures, but in the context of any database.

There are a lot of them, so I’ll just give a few examples.

sp_configure – a procedure for displaying and altering the DBMS kernel configuration. First parameter name of configuration parameter, second parameter value.

Example:

-- change the parameter value
EXEC sp_configure 'Ad Hoc Distributed Queries',1
reconfigure  --we will apply
EXEC sp_configure  --just look through the values of all parameters

where, ‘Ad Hoc Distributed Queries’ is the name of the parameter, 1 respectively the value we want to change to will apply the entered value.

sp_executesql – executes a Transact-SQL instruction or set of instructions that can be formed dynamically. This procedure we used in the material logging of data changes in the table to Transact-SQL

Example:

EXECUTE sp_executesql N'SELECT * FROM test_table WHERE id = @id', N'@id int', @id = 1

Where, the first parameter is the sql instruction (string in Unicode), the second is the definition of all parameters built into the sql instruction, the third is the value of parameters.

sp_help – returns detailed information about any database object.

Example:

EXECUTE sp_help 'test_table'

sp_rename – renames the object in the database. You can use it to rename tables, indexes, column names in tables. It is not recommended to use this procedure to rename custom procedures, triggers, functions.

Example of table renaming:

EXEC sp_rename 'test_table', 'test_table_new'

where the first parameter is the object with the old name, and the second parameter is the new name of the object.

Example of renaming a column in a table:

EXEC sp_rename 'test_table.summa', 'summa_new', 'COLUMN'

The third parameter indicates that the column is renamed.

Triggers

A trigger is a normal procedure, but it is called by an event, not a user. An event, for example, may be to insert a new row in the table (insert), update the data in the table (update) or delete data from the table (delete).

Creation

CREATE TRIGGER trg_test_table_update ON test_table
for UPDATE -- can also delete, insert
AS
BEGIN
--sql instructions in case of UPDATE
END
GO

Modification

ALTER TRIGGER trg_test_table_update ON test_table
for insert -- can also delete, update
AS
BEGIN
--sql instructions in case of insert
END
GO

Deletion

DROP TRIGGER trg_test_table_update

Switching on/off

-- Disconnect
DISABLE TRIGGER trg_test_table_update ON test_table;
---inclusion
ENABLE TRIGGER trg_test_table_update ON test_table;

Indexes in TSQL

This is a database object that enhances data search performance by sorting data by a specific field. If we draw an analogy, then, for example, to search for certain information in a book is much easier and faster on its table of contents than if this table of contents did not exist. The following index types exist in MS SQL Server:

Classified index – with this index, the rows in the table are sorted with the specified key, i.e. the specified field. This type of indexes in a table in MS SQL server can be only one and, starting from MS SQL 2000, it is automatically created when a primary key (PRIMARY KEY) is specified in a table.

Declassified index – when using this type of indexes, the index contains row indexes sorted by the specified field, not the rows themselves, due to which a quick search for the necessary row is performed. A table can have several such indices.

Columnstore index – this type of index is based on the technology of storing table data not as rows but as columns (hence the name), a table may have one columnstore index.

When using this type of indexes, the table immediately becomes read-only, in other words, you can no longer add or change data in the table, you will have to disable the index, add/modify data, and then enable the index back.

Such indexes are suitable for a very large set of data used in storages.

Operations that use aggregate functions with grouping are performed much faster (several times!) with this index.

Columnstore index is available from 2012 version of SQL server in Enterprise, Developer and Evaluation editions.

Creation

Clustered index

CREATE CLUSTERED INDEX idx_clus_one
ON test_table(id)
GO

Where, CREATE CLUSTERED INDEX is an instruction to create a clustered index, idx_clus_one index name, test_table(id) table and key field for sorting respectively.

Unclassified index

CREATE INDEX idx_no_clus
ON test_table(summa)
GO

Columnstore index

CREATE columnstore INDEX idx_columnstore
ON test_table(date_create)
GO

Shutdown

-- Disconnect
ALTER INDEX idx_no_clus ON test_table DISABLE
-- inclusion, reshaping
ALTER INDEX idx_no_clus ON test_table REBUILD

Deletion

DROP INDEX idx_no_clus ON test_table
GO

Cursors

A cursor is a kind of data type that is used mainly in procedures and triggers. It is a common data set, i.e. the result of a query.

Example (all this in the procedure code):

-- declare variables
DECLARE @id BIGINT
DECLARE @fio VARCHAR(100)
DECLARE @summa FLOAT
-- declare cursor
DECLARE test_cur CURSOR FOR
SELECT id, fn, summa FROM test_table
-- open the cursor
OPEN test_cur
-read the first line of the cursor.
-and we put them in variables.
FETCH NEXT FROM test_cur INTO @id, @fio, @summa
-run the loop until the cursor lines finish.
WHILE @@FETCH_STATUS = 0
BEGIN
-- we can execute sql instructions for every iteration of the loop.
--...SQL instructions....
-read the following cursor line
FETCH NEXT FROM test_cur INTO @id, @fio, @summa
END
-cursor close
CLOSE test_cur
DEALLOCATE test_cur

DML queries

DML (Data Manipulation Language) are SQL statements with which data is manipulated. These include select, update, insert, delete.

SELECT

SQL statement with the help of which data sampling is performed. We talked about it in detail in the material SQL Query Language – SELECT Operator.

Example:

SELECT * FROM test_table

UPDATE

Used to update data.

Example:

-- all rows in the table will be updated
UPDATE test_table SET summa=500
-- only strings with an id greater than 10 are updated
UPDATE test_table SET summa=100
WHERE id > 10

INSERT

Operator to add data:

--single line add-on
INSERT INTO test_table (fio, summa, date_create, comment)
VALUES ('name',100, '26.10.2014', 'test record')
-- request-driven mass addition
INSERT INTO test_table
SELECT fio, summa, date_create, comment
FROM test_table

DELETE

You can use this operator to delete data.

Example:

--Clearing the whole table
DELETE test_table
-delete only strings that fall into the condition
DELETE test_table
WHERE summa > 100

Introduction to Transact SQL (T-SQL) using Microsoft SQL Server