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.

T-SQL data types

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

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

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:

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:

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.