Managing data with Transact-SQL

Transact-SQL (T-SQL) is SQL Server’s dialect of Structured Query Language (SQL). Some pronounce it tee-sequel, others tee-es-queue-el.

Unlike C#, T-SQL is not case-sensitive; for example, you can use int or INT to specify the 32-bit integer data type, and you can use SELECT or select to start a query expression. Text stored in SQL Server can be treated as case-sensitive or not depending on configuration.

The complete reference for T-SQL is found at the following link: https://learn.microsoft.com/en-us/sql/t-sql/language-reference.

T-SQL data types

T-SQL has data types that are used for columns, variables, parameters, and so on.

Category	Examples
Numbers	bigint, bit, decimal, float, int, money, numeric, real, smallint, smallmoney, tinyint
Date and time	date, datetime2, datetime, datetimeoffset, smalldatetime, time
Text	char, nchar, ntext, nvarchar, text, varchar
Binary	binary, image, varbinary
Other	cursor, hierarchyid, sql_variant, table, rowversion, uniqueidentifier, xml

T-SQL also has support for spatial geometry and geography types.

Documenting with comments

To comment out the rest of a line, use -- which is the equivalent of //.

To comment out a block, use /* and the start and */ at the end, just like in C#.

Declaring variables

Local variable names are prefixed with @ and they are defined using SET, SELECT, or DECLARE, as shown in the following code:

DECLARE @WholeNumber INT; -- Declare a variable and specify its type.
SET @WholeNumber = 3; -- Set the variable to a literal value.
SET @WholeNumber = @WholeNumber + 1; -- Increment the variable.
SELECT @WholeNumber = COUNT(*) FROM Employees; -- Set to the number of employees.
SELECT @WholeNumber = EmployeeId FROM Employees WHERE FirstName = 'Janet';

Global variables are prefixed with @@. For example, @@ROWCOUNT is a context-dependent value that returns the number of rows affected by a statement executed within the current scope, for example, the number of rows updated or deleted.

Specifying data types

Most types have a fixed size. For example, an int uses four bytes, a smallint uses two bytes, and a tinyint uses one byte.

For text and binary types, you can either specify a type prefixed with var or nvar (meaning variable size) that will automatically change its size based on its current value up to a maximum, as shown in the following example: varchar(40); or you can specify a fixed number of characters that will always be allocated, as shown in the following example: char(40).

For text types, the n prefix indicates Unicode, meaning it will use two bytes per character. Text types not prefixed with n use one byte per character.

Controlling flow

T-SQL has similar flow control keywords as C#, for example, BREAK, CONTINUE, GOTO, IF...ELSE, CASE, THROW, TRY...CATCH, WHILE, and RETURN. The main difference is the use of BEGIN and END to indicate the start and end of a block, the equivalent of curly braces in C#.

Operators

T-SQL has similar operators as C#, for example, = (assignment), +, -, *, /, %, <, >, <=, ==, !=, &, |, ^, and so on. It has logical operators like AND, OR, NOT, and LINQ-like operators like ANY, ALL, SOME, EXISTS, BETWEEN, and IN.

LIKE is used for text pattern matching. The pattern can use % for any number of characters. The pattern can use _ for a single character. The pattern can use [] to specify a range and set of allowed characters, for example, [0-9A-Z.-,].

If a table or column name contains spaces, then you must surround the name in square brackets like [Order Details]. The SQL scripts to create the Northwind database include the command set quoted_identifier on, so you can also use double-quotes like "Order Details". Single quotes are used for literal text like 'USA'.

Data Manipulation Language (DML)

DML is used to query and change data.

The most common statement in DML is SELECT, which is used to retrieve data from one or more tables. SELECT is extremely complicated because it is so powerful. This book is not about learning T-SQL, so the quickest way to get a feel for SELECT is to see some examples, as shown in the following table:

Example	Description
SELECT * FROM Employees	Get all columns of all the employees.
SELECT FirstName, LastName FROM Employees	Get the first and last name columns of all employees.
SELECT emp.FirstName, emp.LastName FROM Employees AS emp	Give an alias for the table name.
SELECT emp.FirstName, emp.LastName FROM Employees emp	Give an alias for the table name.
SELECT FirstName, LastName AS Surname FROM Employees	Give an alias for the column name.
SELECT FirstName, LastName FROM Employees WHERE Country = 'USA'	Filter the results to only include employees in the USA.
SELECT DISTINCT Country FROM Employees	Get a list of countries without duplicates.
SELECT UnitPrice * Quantity AS Subtotal FROM [Order Details]	Calculate a subtotal for each order detail row.
SELECT OrderId, SUM(UnitPrice * Quantity) AS Total FROM [Order Details] GROUP BY OrderId ORDER BY Total DESC	Calculate a total for each order and sort with the largest order value at the top.
SELECT CompanyName FROM Customers UNION SELECT CompanyName FROM Suppliers	Return all the company names of all customers and suppliers.
SELECT CategoryName, ProductName FROM Categories, Products	Match every category with every product using a Cartesian join and output their names (not what you normally want!) 616 rows (8 categories x 77 products).
SELECT CategoryName, ProductName FROM Categories c, Products p WHERE c.CategoryId = p.CategoryId	Match each product with its category using a WHERE clause for the CategoryId column in each table, and output the category name and product name. 77 rows.
SELECT CategoryName, ProductName FROM Categories c INNER JOIN Products p ON c.CategoryId = p.CategoryId	Match each product with its category using an INNER JOIN...ON clause for the CategoryId column in each table, and output the category name and product name. 77 rows.

You can read the full documentation for SELECT at the following link: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql.

Use your favorite database querying tool, like Visual Studio Server Explorer or Visual Studio Code’s mssql extension, to connect to your Northwind database and try out some of the queries above, as shown in Figure 2.10 and Figure 2.11:

Figure 2.10: Executing T-SQL queries using Visual Studio’s Server Explorer

Figure 2.11: Executing T-SQL queries using Visual Studio Code’s mssql extension

DML for adding, updating, and deleting data

DML statements for adding, updating, and deleting data include:

Example	Description
INSERT Employees(FirstName, LastName) VALUES('Mark', 'Price')	Add a new row into the Employees table. The EmployeeId primary key value is automatically assigned. Use @@IDENTITY to get this value.
UPDATE Employees SET Country = 'UK' WHERE FirstName = 'Mark' AND LastName = 'Price'	Update my employee row to set my Country to UK.
DELETE Employees WHERE FirstName = 'Mark' AND LastName = 'Price'	Delete my employee row.
DELETE Employees	Delete all rows in the Employees table.
TRUNCATE TABLE Employees	Delete all rows in the Employees table more efficiently because it does not log the individual row deletions.

The above examples use the Employees table in the Northwind database. That table has referential integrity constraints that mean that, for example, deleting all rows in the table cannot happen, because every employee has related data in other tables like Orders.

Data Definition Language (DDL)

DDL statements change the structure of the database, including creating new objects like tables, functions, and stored procedures. The following table shows some examples of DDL statements to give you an idea, but the examples are simple and cannot be executed within the Northwind database.

Example	Description
CREATE TABLE dbo.Shippers ( ShipperId INT PRIMARY KEY CLUSTERED, CompanyName NVARCHAR(40) );	Create a table to store shippers.
ALTER TABLE Shippers ADD Country NVARCHAR(40)	Add a column to a table.
CREATE NONCLUSTERED INDEX IX_Country ON Shippers(Country)	Add a non-clustered index for a column in a table.
CREATE INDEX IX_FullName ON Employees(LastName, FirstName DESC) WITH (DROP_EXISTING = ON)	Change an aggregate index with multiple columns and control the sort order.
DROP TABLE Employees	Delete the Employees table.
DROP TABLE IF EXISTS Employees	Delete the Employees table if it already exists.
IF OBJECT_ID(N'Employees', N'U') IS NOT NULL	Check if a table exists. The N prefix before a text literal means Unicode. 'U' means a user table as opposed to a system table.