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 data stored in SQL Server tables can be treated as case-sensitive or not, depending on the configuration.

The complete reference for T-SQL is found at the following link: https://learn.microsoft.com/en-us/sql/t-sql/language-reference. From that documentation starting page, use the left side navigation to view topics like Data types, Queries, and Statements.

T-SQL data types

T-SQL has data types that are used for columns, variables, parameters, and so on, as shown in Table 2.2:

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

Table 2.2: Categories of SQL Server data types

There is an xml data type but no JSON data type. Use nvarchar to store JSON values. 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 /* at 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), which 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.-,], which looks like a simplified regular expression syntax but keep in mind that it is not regular expression syntax.

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 Table 2.3:

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. Table name prefixes are not needed when there is only one table, but become useful to disambiguate when there are multiple tables that have columns with the same name, for example, Customers.CustomerId and Orders.CustomerId.
SELECT emp.FirstName, emp.LastName FROM Employees emp	Give an alias for the table name without needing the AS keyword.
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 used as values in the Country column of the Employees table 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. This is a modern alternative syntax to using WHERE, and it allows outer joins, which would also include non-matches. 77 rows.

Table 2.3: Example SELECT statements with descriptions

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

Use your favorite database querying tool, like Visual Studio’s 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.11 and Figure 2:12:

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

Figure 2.12: 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 those shown in Table 2.4:

Example	Description
INSERT Employees(FirstName, LastName) VALUES('Mark', 'Price')	Add a new row to 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 and record those deletions in the transaction log.
TRUNCATE TABLE Employees	Delete all rows in the Employees table more efficiently because it does not log the individual row deletions.

Table 2.4: Example DML statements with descriptions

The above examples use the Employees table in the Northwind database. That table has referential integrity constraints that would 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, as shown in Table 2.5:

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. If it does not exist, then this throws an error.
DROP TABLE IF EXISTS Employees	Delete the Employees table if it already exists. This avoids the potential error from using the statement in the previous row.
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.

Table 2.5: Example DDL statements with descriptions