Pro T-SQL 2019

Toward Speed, Scalability, and Standardization for SQL Server Developers

Github repository: Pro T-SQL 2019

Main Book page: Pro T-SQL 2019

Part I: Building Understandable T-SQL

Chapter 1: Data Types

Number Data Types

Exact Number Data Types

BIT
1, 0 or NULL
Very efficient for storing boolean values. Be careful that your use in design because frequently knowing when some is true is often needed.
Consider using in groups of eight (1 byte) for optimal storage. Also you might double storage requirements if where to go from 8 bits into 9 bits because storage is allocated in 8-bit (byte) increments.
TINYINT: 1 byte, range 0 to 255
SMALLINT: 2 bytes, range -32,768 to 32,767, if unsigned: 0 to 65,535
Consider using SMALLINT if as an efficient primary key for small tables or as a foreign key in a table that references a small table.
INT: 4 bytes, range -2,147,483,648 to 2,147,483,647
Most commonly used integer data type in SQL Server.
Consider using INT as a primary key for most tables, Note: that if you are not careful most people don't use unsigned integers and the primary starts with 1, thus halving the available range. Or set the starting value to -2,147,483,648.
NOTE: SQL Server does not support unsigned integer types.
BIGINT: 8 bytes, range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
NOTE: When designing tables for transactional data, consider the expected transaction rate. Higher rates, like tens of thousands per second, will result in far greater data growth over time than a few hundred per second, affecting storage and design choices. You can easily reduce storage requirements by using smaller data types where appropriate.
DECIMAL or alternatively it is called a NUMERIC data type.
There is no difference between DECIMAL and NUMERIC in SQL Server; they are functionally equivalent.
You specify the total number of digits and the number of digits to the right of the decimal point.
Use this most general purpose numbers, decimals, measurements, and money values.
There is a big difference to floating-point types (FLOAT and REAL) in that DECIMAL/NUMERIC are exact, while floating-point are base-2 approximations if used incorrectly can lead to rounding issues.
SMALLMONEY, MONEY
SMALLMONEY: 4 bytes, range -214,748.3648 to 214,748.3647
MONEY: 8 bytes, range -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Use these for financial calculations and are accurate to a ten-thousandth.
While they work fine for currency, Microsoft often recommends using DECIMAL/NUMERIC for more control over precision and scale.

Best Practice: If you're working with financial data:

```sql

-- Instead of: MONEY

-- Use: DECIMAL(19,4) -- or adjust precision/scale as needed

```

Sources: money and smallmoney (Transact-SQL)

Note: currency symbols are listed in above documentation. DO NOT USE for caludlations money and smallmoney (Transact-SQL) - SQL Server | Microsoft Learn

Approximate Number Data Types

Approximate Number Data Types aka REAL or FLOAT
There are some rounding issues that come into play when dealing with approximate numbers.
These have rounding errors.

Converting Number Data Types

Converting data types
Same category data types stay the same.
- Resulting precision and scale are determined by the stating precision and scale of the input data types. This might result in unexpected behavior if not properly accounted for, especially when exporting data.
This is important to note when performing calculations or comparisons between different numeric types.
INT calculated with DECIMAL(5,2) datatype; Server uses the process of type precedence.

Data type precedence (Transact-SQL)

Note: That float is higher than Decimal and money. This means that when performing calculations involving these data types, float will take precedence and may lead to unexpected results if not properly managed.

String Data Types

The last category of string data type includes images and other binary data (file streams).

Character String Data Types

CHAR(n): Fixed-length character data type. Use for storing strings with a known, fixed length.
VARCHAR(n): Variable-length character data type. Use for storing strings with varying lengths.
CHAR vs VARCHAR:
Use CHAR(n) when:
- Data has a fixed length (e.g., phone numbers, zip codes).
- You want consistent storage size and potentially better performance.
Use VARCHAR(n) when:
- Data has a variable length (e.g., address lines, notes).
- You want to save space by storing only the actual length of the data.
Use VARCHAR(MAX) only when:
- You expect to store more than 8,000 characters.
- You need to handle large text blocks (up to 2 GB).Here's a concise summary of the guidance:
When declaring variables or columns:
CHAR and VARCHAR default to 1 character if no length is specified.
When using CAST or CONVERT:
The default length is 30 characters.
Best Practice:
Always explicitly specify the length to avoid truncation or unexpected behavior.

sql -- Best practice: always specify length explicitly SELECT CAST(OriginalValue AS VARCHAR(100)) AS CastExplicitLength FROM DemoStrings;

Starting with SQL Server 2019, it is now possible to save unicode values in CHAR or VARCHAR. However, this is only possible if UTF-8 encoding is enabled.
TEXT: Use for storing large amounts of text data (up to 2 GB). However, it is recommended to use VARCHAR(MAX) instead, as TEXT is deprecated.

Unicode String Data Types

Prior to SQL Server 2019, any Unicode text data would need to be saved as a special data type. This is still true for situations where UTF-8 encoding cannot be enabled.

NCHAR and NVARCHAR: Used to store Unicode character data. NCHAR is for fixed-length strings, NVARCHAR for variable-length.
NTEXT: Legacy data type for large Unicode text. It's deprecated and replaced by NVARCHAR(MAX). Going forward, it is no longer best practice to use this data type. Instead, use the NVARCHAR(MAX) data type.

Binary String Data Types

BINARY and VARBINARY
Using binary strings for storing items that are strings without characters may be useful. These can include audio, video, images, or other similar items.
BINARY and VARBINARY are used to store binary string data. Use BINARY for fixed-length binary data and VARBINARY for variable-length. If data may exceed 8000 bytes, use VARBINARY(MAX), which supports up to 2 GB. If no length is specified during declaration, the default is 1 byte; for CAST or CONVERT, it's 30 bytes. Be cautious with conversions, as SQL Server may pad or truncate data. BINARY stores exactly the specified number of bytes, while VARBINARY adds 2 bytes for length tracking.
IMAGE
In the case of the IMAGE data type, you should use the VARBINARY(MAX) data type going forward as the IMAGE data type is deprecated.

Date and Time Data Types

DATE

When converting a DATE to DATETIME, SMALLDATETIME, DATETIME2, or DATETIMEOFFSET, the time defaults to 00:00:00 (midnight). Also, DATE values do not include time zone or daylight saving time information.

TIME

TIME data type has up to 100-nanosecond precision and defaults to 00:00:00. Its format is hh:mm:ss[.nnnnnnn]. When converted to a date-time type, the date defaults to 1900-01-01. TIME does not include time zone or daylight saving time information.

SMALLDATETIME, DATETIME, DATETIME2, DATETIMEOFFSET

SMALLDATETIME is not ANSI compliant and rounds seconds based on their value, which can affect minute accuracy. DATETIME also has limited precision, rounding to .000, .003, or .007 seconds. Both types do not support time zones or daylight saving time. Use caution when choosing these types for time-sensitive data.
DATETIME2 is a SQL Server data type that stores date and time values with higher precision than DATETIME. It supports fractional seconds up to 7 digits and a wider date range. It does not include time zone or daylight saving time awareness. It's recommended over DATETIME for more accurate and flexible date-time storage.

DATETIME2 offers high precision and is commonly used for conversions. When converting:

From DATE: time defaults to 00:00:00.
From TIME: date defaults to 1900-01-01.
From SMALLDATETIME: values are copied, extra precision is filled with zeros.
From DATETIMEOFFSET: time zone info is removed.
From DATETIME: use explicit conversion to avoid unexpected results.

DATETIME2 is not time zone or daylight saving time aware.

DATETIMEOFFSET is a SQL Server data type that stores date and time values along with a time zone offset. It supports high precision and is useful for applications that need to track time zones. Unlike other date-time types, it is time zone aware but not daylight saving time aware.

CREATE TABLE Events (
    EventID INT PRIMARY KEY,
    EventName VARCHAR(100),
    EventTime DATETIMEOFFSET
);

-- Insert a record with a specific time zone offset (e.g., UTC-5)
INSERT INTO Events (EventID, EventName, EventTime)
VALUES (1, 'Conference Call', '2025-08-15 14:00:00 -05:00');

Working with timezones
AT TIME ZONE
Converts a datetime, datetime2, or datetimeoffset value to a specific time zone.
Useful for assigning or converting time zone offsets.
Example:

sql SELECT SYSDATETIME() AT TIME ZONE 'Eastern Standard Time';
You can also chain conversions:

sql SELECT SYSDATETIME() AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC';
Learn more 1
CURRENT_TIMEZONE()
Returns the name of the time zone observed by the SQL Server instance.
Example:

sql SELECT CURRENT_TIMEZONE();
Learn more 2
sys.time_zone_info
A system view that lists all supported time zones on the server.
Example:

sql SELECT * FROM sys.time_zone_info;
Learn more 3
SWITCHOFFSET
Changes the time zone offset of a datetimeoffset value without changing the actual UTC time.
Example:

sql DECLARE @dt datetimeoffset = '2025-08-15 14:00:00 -05:00'; SELECT SWITCHOFFSET(@dt, '-08:00');
Learn more4
Get the offset of a given time zone name

You can use the DATEPART function with the tz argument to get the offset in minutes:

sql SELECT DATEPART(tz, SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time');

This returns the current UTC offset for the specified time zone. Note that the offset may vary depending on daylight saving time.1 6. Convert a DATETIMEOFFSET to another time zone

Use the AT TIME ZONE function to convert between time zones:

```sql DECLARE @dt DATETIMEOFFSET = '2025-08-15 14:00:00 -05:00';

-- Convert to UTC SELECT @dt AT TIME ZONE 'UTC';

-- Convert to Eastern Standard Time SELECT @dt AT TIME ZONE 'Eastern Standard Time'; ```

This function adjusts the time and offset according to the rules of the target time zone.2

Other Data Types

UNIQUEIDENTIFIER

Stores globally unique identifiers (GUIDs), useful for unique keys across systems.

XML

Stores XML data and allows querying and validation using XML methods.

For the XML data type, the data must be in a valid XML format.

Spatial Geometry Types

Represents geometric shapes like points, lines, and polygons in a flat (Euclidean) space.

Spatial Geography Types

Represents geographic data on a round Earth model, useful for GPS and mapping.

SQL_VARIANT

Can store values of different data types in a single column, but only one type per value.

Rowversion

Automatically generated binary number used for versioning rows. Only one ROWVERSION column can be added per table.

HIERARCHYID

Represents hierarchical data like organizational charts or file systems.

Table

Used to define table-valued variables or return table data from functions.

Cursor

Enables row-by-row processing of query results, typically used in procedural logic.

Warning: If SQL Server needs to compare two different types, SQL Server will need to convert at least one of the data types so that both data types are the same. This process is known as implicit conversion. The CPU cost associated with implicit conversion can be significant and should be avoided if possible. The best way to avoid implicit conversion is to use the same data types for fields that will be compared. The biggest challenge is that sometimes it takes several years to realize how an incorrect data type may be negatively impacting application performance.

Chapter 1: Data Types : Flash Cards

BIT / Values / Description / Important Note == 1, 0 or NULL, Very efficient for storing Boolean values. Be careful that your use in design because frequently knowing when some is true is often needed. Consider using in groups of eight (1 byte) for optimal storage. Also you might double storage requirements if where to go from 8 bits into 9 bits because storage is allocated in 8-bit (byte) increments.
TINYINT / Values / Description / Important Note == 2^8 = 256 values / Range: 0 to 255 / 1 byte / Smallest integer type. Useful for compact storage when values are guaranteed to be within range. SQL Server only supports signed types, but TINYINT behaves like unsigned due to its 0-255 range.
SMALLINT / Values / Description / Important Note == 2^16 = 65,536 values / Range: -32,768 to 32,767 / 2 bytes / Efficient for small tables or foreign keys. SQL Server does not support unsigned integers, so range is split evenly between negative and positive values.
INT / Values / Description / Important Note == 2^32 = 4,294,967,296 values / Range: -2,147,483,648 to 2,147,483,647 / 4 bytes / Most commonly used integer type. Be mindful that starting at 1 instead of the minimum value halves the usable range.
BIGINT / Values / Description / Important Note == 2^64 = 18,446,744,073,709,551,616 values / Range: -2^63 to 2^63 - 1 / 8 bytes / Use for very large datasets or high-frequency transactional systems. Consider storage implications.
DECIMAL / Values / Description / Important Note == User-defined precision / Exact numeric type /Use for financial, scientific, or measurement data requiring precision. Avoid FLOAT/REAL for money due to rounding errors.
MONEY / Values / Description / Important Note == 8 bytes / Range: ±922 trillion (approx) / Accurate to ten-thousandths. Microsoft recommends using DECIMAL for better control over precision and scale.
SMALLMONEY / Values / Description / Important Note == 4 bytes / Range: ±214 thousand (approx) / Suitable for smaller financial values. Consider DECIMAL for consistency and precision.
FLOAT / Values / Description / Important Note == Approximate numeric / IEEE 754 format / Use with caution. Can introduce rounding errors. Not suitable for financial calculations.
CHAR(n) / Values / Description / Important Note == Fixed-length / n bytes / Use when data has a consistent length (e.g., ZIP codes). Better performance due to predictable storage. Legacy only!
VARCHAR(n) / Values / Description / Important Note == Variable-length / Up to 8,000 bytes / Use for variable-length strings. Saves space by storing only actual length. Legacy only!
VARCHAR(MAX) / Values / Description / Important Note == Variable-length / Up to 2^31-1 bytes (2 GB) / Use for large text blocks. Avoid unless necessary due to performance overhead. Legacy only!
NCHAR(n) / Values / Description / Important Note == Fixed-length Unicode / 2n bytes / Use for fixed-length multilingual data. Each character uses 2 bytes.
NVARCHAR(n) / Values / Description / Important Note == Variable-length Unicode / Up to 4,000 characters / Preferred for Unicode strings. Use NVARCHAR(MAX) for longer text.[Half VARBINARY(n)]
NVARCHAR(MAX) / Values / Description / Important Note == Variable-length Unicode / Up to 2^30 characters (1 GB) / Use for large Unicode text. Replaces deprecated NTEXT.
BINARY(n) / Values / Description / Important Note == Fixed-length binary / n bytes / Use for binary data like hashes or fixed-size blobs. Pads or truncates to exact length.
VARBINARY(n) / Values / Description / Important Note == Variable-length binary / Up to 8,000 bytes / Use for binary data with variable size. Use VARBINARY(MAX) for >8,000 bytes. [Double NVARCHAR(n)]
VARBINARY(MAX) / Values / Description / Important Note == Variable-length binary / Up to 2^31-1 bytes (2 GB) / Use for large binary objects like images, audio, or documents.
DATE / Values / Description / Important Note == Date only / No time component / Defaults to midnight when converted to datetime types. No time zone awareness.
TIME / Values / Description / Important Note == Time only / Up to 100-nanosecond precision / Defaults to 1900-01-01 when converted. No time zone awareness.
SMALLDATETIME / Values / Description / Important Note == Date + time / Rounded to nearest minute / Limited precision. Not ANSI compliant. Use with caution.
DATETIME / Values / Description / Important Note == Date + time / Rounded to .000, .003, or .007 seconds / Legacy type. Consider DATETIME2 for better precision.
DATETIME2 / Values / Description / Important Note == Date + time / Up to 7 fractional seconds / Recommended over DATETIME. No time zone awareness.
DATETIMEOFFSET / Values / Description / Important Note == Date + time + time zone offset / Time zone aware / Use for global applications. Not daylight saving aware.
UNIQUEIDENTIFIER / Values / Description / Important Note == 16 bytes / Globally unique identifier / Use for distributed systems or merging data across databases.
XML / Values / Description / Important Note == Structured text / Valid XML format required / Use for storing and querying XML data. Supports indexing and validation.
GEOMETRY / Values / Description / Important Note == Spatial data / Flat (Euclidean) model / Use for shapes like points, lines, polygons. Useful in mapping applications.
GEOGRAPHY / Values / Description / Important Note == Spatial data / Round Earth model / Use for GPS coordinates and geospatial queries.
SQL_VARIANT / Values / Description / Important Note == Mixed types / One type per value / Use when column must store different types. Adds complexity. use function SQL_VARIANT_PROPERTY to get information of the value store in this catch all stored data.
ROWVERSION / Values / Description / Important Note == Auto-generated binary / 8 bytes / Used for row versioning and concurrency control. Only one per table.
HIERARCHYID / Values / Description / Important Note == Hierarchical data / Specialized type / Use for representing tree structures like org charts or file systems.
TABLE / Values / Description / Important Note == Table-valued variable / Used in functions / Enables returning sets of rows from functions. Useful for modular logic.
CURSOR / Values / Description / Important Note == Row-by-row processing / Procedural logic / Avoid unless necessary. Slower than set-based operations.

Chapter 2: Database Objects

Views

What is a view? Like the definition of the word view, a view in T-SQL is a means of taking several different items and putting them together to form one cohesive image.

User-Defined Views

One of the results of a view is simplicity. It is one way for applications and users to access complex sets of information without needing to understand all the relationships in a database.

SELECT meal.MealTypeName, rec.RecipeName, rec.ServingQuantity, ing.IngredientName FROM dbo.Recipe rec
INNER JOIN dbo.MealType meal
ON rec.MealTypeID = meal.MealTypeID
INNER JOIN dbo.RecipeIngredient recing
ON rec.RecipeID = recing.RecipeID
INNER JOIN dbo.Ingredient ing
ON recing.IngredientID = ing.IngredientID

CREATE VIEW dbo.AvailableMeal AS
SELECT meal.MealTypeName, rec.RecipeName, rec.ServingQuantity, ing.IngredientName
FROM dbo.Recipe rec
INNER JOIN dbo.MealType meal
ON rec.MealTypeID = meal.MealTypeID
INNER JOIN dbo.RecipeIngredient recing
ON rec.RecipeID = recing.RecipeID
INNER JOIN dbo.Ingredient ing
ON recing.IngredientID = ing.IngredientID

UPDATE dbo.AvailableMeal SET IngredientName = 'Spicy Italian Sausage' WHERE RecipeName = 'Spaghetti'

SCHEMABINDING

Definition: SCHEMABINDING ties a view to the schema of its underlying objects.
Rules:
Must reference objects with explicit schema names (dbo.TableName).
Cannot use * (all columns); all columns must be named.
Restrictions:
Prevents dropping or altering underlying objects/columns that the view depends on.
Schema changes require removing or altering the view first.
Benefits:
Ensures data integrity (no accidental schema changes that break the view).
Required for indexed views.
Improves reliability and performance in some cases.
Best Practice: Use SCHEMABINDING for critical or indexed views where stability and integrity are important.
However, there is also a potential loophole regarding protecting data. Once a view is created, the original column name can be replaced to use a different column as long as the field is aliased with the same column name.

Avoid using nested views. There is a performance cost associated with them, as each view adds an additional layer of complexity to the query execution plan.

Indexed View

If you find yourself in a situation where you need to improve the performance of view, you have the option of adding indexes to a view. The first index added to a view must be a clustered index.

CREATE UNIQUE CLUSTERED INDEX CX_AvailableMeal_RecipeNameIngredientName
ON dbo.AvailableMeal (RecipeName, IngredientName);

Remember that while there are situations where indexed views can help performance when pulling back the data, there can still be performance issues that happen when data is inserted, updated, or deleted on the affected tables.

Functions

Scalar Functions

Scalar functions have greatly improved on SQL Server 2019 compared to other versions of SQL Server.

An inline function is one that can be included as part of the execution plan. One of the largest advantages of inlining scalar UDFs is the significantly improved performance when it comes to using scalar UDFs. When wanting to simplify complex processes and reuse code, scalar UDFs are the ideal option when the function only needs to return one result.

CREATE FUNCTION dbo.Ingredient_Price
(
    @Cost DECIMAL(6,3),
    @Count DECIMAL(6,3)
)
RETURNS DECIMAL (6,3) AS
BEGIN
    RETURN @Cost / @Count;
END

The ability to inline scalar UDFs in SQL Server 2019 does not apply only to single query scalar UDFs. There is also improved functionality when it comes to using multi-statement scalar UDFs.

Regardless of the scalar UDF you decide to use, SQL Server 2019 has been improved so that you can see improved performance with these functions.

Table-Valued Functions

There will be situations where you find yourself needing to perform complex logic, but you need to return more than one value. When these situations come up, you may want to consider using a table-valued function.

Inline Table-Valued Functions

CREATE FUNCTION dbo.IngredientsByRecipe (@RecipeID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT
        meal.MealTypeName,
        rec.ServingQuantity,
        ing.IngredientName
    FROM dbo.Recipe rec
        INNER JOIN dbo.MealType meal
            ON rec.MealTypeID = meal.MealTypeID
        INNER JOIN dbo.RecipeIngredient recing
            ON rec.RecipeID = recing.RecipeID
        INNER JOIN dbo.Ingredient ing
            ON recing.IngredientID = ing.IngredientID
    WHERE rec.RecipeID = @RecipeID
);
GO

These types of functions can only allow for one select statement and one result set. In addition, the data that is returned in these functions cannot be modified in the database. However, data returned from an inline table-valued UDF that is displayed in a select statement can be modified. This is a change to the data that is cosmetic and does not affect the data that is stored in the database.

Multi-statement Table-Valued Functions

A table-valued function (TVF) returns a set of rows, similar to a view, but it's encapsulated in a function. Unlike inline TVFs, which consist of a single RETURN (SELECT …) statement, MSTVFs allow you to define multiple T-SQL statements inside the body.
Because of this, MSTVFs are structured like stored procedures (you can declare variables, use control flow, loops, and conditionals), but instead of just returning a result set, they return a table variable that you define up front.
The function body typically looks like this:
Declare and initialize a table variable with column definitions.
Insert data into that table variable through one or more INSERT statements (which can be conditional, iterative, etc.).
RETURN the table variable at the end.
This makes MSTVFs powerful when you need to build up a result set step by step—for example, combining data from multiple queries, applying conditional logic, or accumulating rows across loops.
However, MSTVFs also come with performance trade-offs. Unlike inline TVFs, they don't benefit from query optimization in the same way, since the optimizer treats them as "black boxes." Execution plans may be less efficient, sometimes leading to row-by-row operations instead of set-based optimizations.

In short: MSTVFs are a hybrid between stored procedures and views—flexible, procedural, and able to return table data—but with potential performance drawbacks compared to inline TVFs.

Performance considerations:

Multi-statement table-valued functions (MSTVFs) are best used sparingly. They exist for cases where inline table-valued functions (iTVFs), views, or stored procedures cannot capture the required logic. Here's when they make sense:

When complex procedural logic is required MSTVFs let you declare variables, use loops, branching (IF, WHILE, etc.), and build result sets in multiple steps. If your transformation can't be expressed as a single SELECT, MSTVFs give you that flexibility.
When you need to return a table from reusable logic Unlike stored procedures, MSTVFs can be called inline in FROM and JOIN clauses. If you want a reusable routine that behaves like a table source, but the logic is too involved for an iTVF, MSTVFs fit the role.
When iterative row construction is unavoidable Some business rules require accumulating results over iterations or applying conditional inserts into a table variable. MSTVFs allow this step-by-step buildup before returning the final set.
When encapsulation is more important than performance MSTVFs provide a way to encapsulate complex logic inside a schema-bound function. This can simplify client code and centralize business rules, even if performance is not optimal.
When row count is small and predictable For small result sets, the performance penalty of table variables and lack of statistics is less severe. In those cases, MSTVFs can be acceptable.

Situations where MSTVFs are not ideal

Large data sets (scans, joins, aggregations) → better handled with inline TVFs or views.
Performance-critical queries that need optimizer integration and parallelism.
Cases where statistics matter for plan quality.

In short: MSTVFs are the best option when you need reusable, table-returning routines with procedural logic that cannot be expressed in a single query, and the result set is relatively small or performance is not the top priority. For most other scenarios, inline TVFs or views are preferred.

Do you want me to also give you a couple of real-world examples (like auditing, step-based calculations, or rule validation) where MSTVFs are often chosen despite their trade-offs?

Possible use cases:

The MD-PHD program they want to calculate the applicants' best MCAT score by combining each test section best score and they need to also be scores within the last three years. However this could be accomplished via a nested select.

Other User-Defined Objects

These allow you to create a data type based on an existing system type, often with an alias name and optional rules/defaults bound to it. For example, you could define a PhoneNumber type based on VARCHAR(12). UDDTs can improve consistency, but they are also considered somewhat legacy. New development often favors user-defined table types or simply standardized column definitions.

User-Defined Table Types

User-Defined Table Types (UDTTs) let you define a custom table structure that can be reused, especially for passing a set of rows into stored procedures or functions as a parameter.

They are useful for bulk operations, batch updates, and avoiding row-by-row logic.

Simple example:

-- Create a user-defined table type
CREATE TYPE dbo.EmployeeList AS TABLE
(
    EmployeeID INT,
    EmployeeName NVARCHAR(100)
);
GO

-- Use it as a table-valued parameter
CREATE PROCEDURE dbo.ProcessEmployees
    @Employees dbo.EmployeeList READONLY
AS
BEGIN
    INSERT INTO dbo.Employees (EmployeeID, EmployeeName)
    SELECT EmployeeID, EmployeeName
    FROM @Employees;
END;
GO

You can now declare a variable of type dbo.EmployeeList, fill it with rows, and pass it to the procedure in a single call.

Here's how you would call the procedure using the dbo.EmployeeList table type we created earlier:

-- Declare a variable of the user-defined table type
DECLARE @NewEmployees dbo.EmployeeList;

-- Insert rows into the table variable
INSERT INTO @NewEmployees (EmployeeID, EmployeeName)
VALUES (1, 'Alice'),
       (2, 'Bob'),
       (3, 'Charlie');

-- Pass the table variable into the stored procedure
EXEC dbo.ProcessEmployees @Employees = @NewEmployees;

This way, you can send multiple rows in one go to the procedure instead of looping or calling it multiple times.

Table-Valued Parameters

Table-Valued Parameters (TVPs) are a feature that lets you pass a set of rows into a stored procedure or function as a single parameter. Instead of sending one row at a time or concatenating values into a string, you define a User-Defined Table Type (UDTT) and then use it as a parameter type.

Key points from Pro T-SQL 2019 Chapter 2:

TVPs are always READONLY inside procedures/functions.
They're ideal for batch inserts, updates, or passing lists (e.g., multiple IDs).
They reduce the need for loops and dynamic SQL, improving performance and readability.
They integrate well with set-based operations since you can join a TVP with other tables.

Simple example:

-- 1. Define a table type
CREATE TYPE dbo.OrderList AS TABLE
(
    OrderID INT,
    ProductID INT,
    Quantity INT
);
GO

-- 2. Create a procedure that accepts a TVP
CREATE PROCEDURE dbo.InsertOrders
    @Orders dbo.OrderList READONLY
AS
BEGIN
    INSERT INTO dbo.Orders (OrderID, ProductID, Quantity)
    SELECT OrderID, ProductID, Quantity
    FROM @Orders;
END;
GO

-- 3. Use the TVP
DECLARE @NewOrders dbo.OrderList;
INSERT INTO @NewOrders VALUES (101, 1, 5), (102, 2, 3);

EXEC dbo.InsertOrders @Orders = @NewOrders;

In short: TVPs = pass whole tables as parameters → cleaner, faster, and more set-based operations.

NOTES: Difference Between User-Defined Table Types and Table-Valued Parameters in SQL Server

User-Defined Table Types (UDTTs):

A schema definition for a table.
Defines the columns and datatypes you want to reuse.
It doesn't do anything by itself until you use it.
Think of it like creating a blueprint for a table variable.

-- Define a user-defined table type (the blueprint)
CREATE TYPE dbo.EmployeeList AS TABLE
(
    EmployeeID INT,
    EmployeeName NVARCHAR(100)
);

Table-Valued Parameters (TVPs):

A parameter in a procedure/function that is declared using a UDTT.
This is how you actually pass a set of rows into T-SQL code.
TVPs must be marked as READONLY.
Think of it as the actual use of the blueprint in a stored procedure.

-- Use the UDTT as a TVP
CREATE PROCEDURE dbo.InsertEmployees
    @Employees dbo.EmployeeList READONLY
AS
BEGIN
    INSERT INTO dbo.Employees (EmployeeID, EmployeeName)
    SELECT EmployeeID, EmployeeName
    FROM @Employees;
END;

Putting it together:

UDTT = definition (dbo.EmployeeList)
TVP = usage of that definition as a parameter in a procedure/function (@Employees dbo.EmployeeList READONLY)

Then you can call the procedure like this:

DECLARE @NewEmployees dbo.EmployeeList;

INSERT INTO @NewEmployees VALUES (1, 'Alice'), (2, 'Bob');

EXEC dbo.InsertEmployees @Employees = @NewEmployees;

In short:

UDTT = the custom table type (blueprint).
TVP = a parameter of that type inside a procedure/function (actual use).

Warning: Table-valued parameters simplify code but may impact performance. Always check the execution plan to ensure SQL Server processes them efficiently for your data.

Common Table Expressions (CTEs)

Common Table Expressions (CTEs) are a feature in T-SQL that let you define a temporary, named result set within a query.

Key points from Pro T-SQL 2019 Chapter 2:

A CTE is defined with the WITH keyword and can be referenced just like a table or view in the main query.
It improves readability and organization of complex queries by breaking them into logical building blocks.
CTEs can be recursive, making them useful for hierarchical data (e.g., org charts, parent-child trees).
They exist only for the duration of the statement — they are not stored objects like views.

Simple example:

-- Define a CTE for recent orders
WITH RecentOrders AS
(
    SELECT OrderID, CustomerID, OrderDate
    FROM Sales.Orders
    WHERE OrderDate >= '2025-01-01'
)
SELECT CustomerID, COUNT(*) AS OrderCount
FROM RecentOrders
GROUP BY CustomerID;

In short: CTEs = temporary, inline views that simplify query structure and support recursion, without persisting as database objects.

Temporary Objects

Sometimes you only need an object for a short time, such as working with a data subset, simplifying complex logic, or improving readability. In these cases, SQL Server lets you create temporary objects.

Temp tables → best for larger, complex intermediate results.
Table variables → lightweight, best for small sets.
Temp stored procedures → temporary routines for ad hoc or session-specific work.

Temporary Tables (Temp Tables)

Created in tempdb with names like #Temp (local) or ##Temp (global).
Behave like normal tables: can have indexes, constraints, and statistics.
Good for storing intermediate results when handling large datasets or needing query optimizer statistics.
Dropped automatically when the session ends (or manually with DROP TABLE).

CREATE TABLE #TempOrders (OrderID INT, CustomerID INT);
INSERT INTO #TempOrders VALUES (1, 42);
SELECT - FROM #TempOrders;

Local temp tables work in many cases, but they shouldn't be your first choice for temporary storage since other objects may perform better. Always test, including under load, before moving code to production.

Local Temporary Tables: Named with #, visible only in the current session, and dropped automatically when the session ends.
Global Temporary Tables: Named with ##, visible to all sessions, and dropped when the creating session ends and no others are using it.
Persistent Temporary Tables: Permanent tables created in tempdb, remain until explicitly dropped, useful for reusable staging or testing.

1. Create a Persistent Temporary Table This is just a regular permanent table created inside the tempdb database. It stays in tempdb until explicitly dropped (or until SQL Server is restarted, depending on your use).

USE tempdb;
GO

CREATE TABLE dbo.PersistentTempTable
(
    ID INT PRIMARY KEY,
    DataValue NVARCHAR(100)
);
GO

-- Insert and query like a normal table
INSERT INTO dbo.PersistentTempTable (ID, DataValue)
VALUES (1, 'Test Row');

SELECT * FROM dbo.PersistentTempTable;

2. Create a Stored Procedure That Runs at SQL Server Startup SQL Server allows you to mark a stored procedure to execute automatically when the instance starts.

USE master;
GO

-- Create the procedure in master
CREATE PROCEDURE dbo.StartupProc
AS
BEGIN
    PRINT 'SQL Server has started. Running startup procedure...';

    -- Example: ensure the persistent temp table exists
    IF NOT EXISTS (
        SELECT 1
        FROM tempdb.sys.tables
        WHERE name = 'PersistentTempTable'
    )
    BEGIN
        EXEC ('USE tempdb;
               CREATE TABLE dbo.PersistentTempTable
               (
                   ID INT PRIMARY KEY,
                   DataValue NVARCHAR(100)
               );');
    END
END;
GO

-- Mark it to run on startup
EXEC sp_procoption
     @ProcName   = 'dbo.StartupProc',
     @OptionName = 'startup',
     @OptionValue = 'on';
GO

In short:

A persistent temporary table is just a table created in tempdb that sticks around until dropped.
A startup stored procedure is created in master and flagged with sp_procoption so SQL Server runs it every time the instance starts.

Table Variables (Table Vars)

Declared with DECLARE @var TABLE (…).
Stored in memory (though larger ones may spill to tempdb).
Don't have statistics, so the optimizer assumes a small row count → can lead to less efficient plans.
Best for small result sets or when performance predictability is less critical.

DECLARE @OrderList TABLE (OrderID INT, CustomerID INT);
INSERT INTO @OrderList VALUES (2, 99);
SELECT - FROM @OrderList;

Temporary Stored Procedures

Created with # or ## prefix (like temp tables) in tempdb.
Useful for one-off routines or testing code without cluttering permanent schema.
Local temp procs (#Proc) exist only in your session; global temp procs (##Proc) exist until the creating session ends and no other sessions are using them.

CREATE PROCEDURE #TempProc AS
BEGIN
    SELECT 'Hello from a temp proc';
END;
EXEC #TempProc;

Triggers

Triggers are special procedures that run automatically in response to specific events — on login (logon triggers), schema changes (DDL triggers), or data changes (DML triggers).

Logon Triggers

Fire when a user session is established.
Commonly used to enforce login policies, track connections, or restrict access (e.g., limiting connections outside business hours).

Data Definition Language (DDL) Triggers (DDL Triggers)

Fire in response to schema changes (e.g., CREATE, ALTER, DROP).
Useful for auditing changes, preventing unauthorized schema modifications, or enforcing naming standards.

Data Manipulation Language (DML) Triggers (DML Triggers)

Fire when data is modified with INSERT, UPDATE, or DELETE.
Often used for enforcing complex business rules, maintaining audit trails, or cascading actions not handled by foreign keys.

Sample SQL:

CREATE TRIGGER dbo.LogIngredientCostHistory
ON dbo.IngredientCost
AFTER INSERT, UPDATE AS
BEGIN
    IF (ROWCOUNT_BIG() = 0)
        RETURN;

    INSERT INTO dbo.IngredientCostHistory (IngredientCostID, Cost, DateCreated)
    SELECT inserted.IngredientCostID, inserted.Cost, GETDATE()
    FROM inserted;
END;
GO

The T-SQL code IF (ROWCOUNT_BIG() = 0) RETURN; prevents the trigger from running when no rows are affected, a best practice to save resources.

Cursors

Cursors let you process rows one at a time. Forward-only is fastest but limited; static gives a fixed snapshot; keyset allows updates but not new/deleted rows; dynamic reflects all changes but is most expensive.

Forward-Only Cursors (FO Cursors)

Simplest and fastest.
Can only move forward through the result set.
Useful when you only need to read rows once, sequentially.

Static Cursors (Static Cursors)

Take a full snapshot of the result set when opened.
Data does not reflect subsequent changes in the underlying tables.
Allow scrolling (forward/backward) but consume more memory.

Keyset Cursors (Keyset Cursors)

Store the keys (primary keys/unique identifiers) of the rows in the result set.
Changes to non-key columns are visible; inserts/deletes are not.
Provide a balance between static and dynamic behavior.

Dynamic Cursors (Dynamic Cursors)

Fully reflect changes in the underlying tables (inserts, updates, deletes).
Support scrolling in all directions.
Most resource-intensive, with the highest overhead.

Chapter 2: Database Objects : Flash Cards

VIEW / Definition / Purpose / Important Note == A virtual table based on the result of a SELECT query. Simplifies access to complex joins and logic. Does not store data itself. Useful for abstraction and security.
USER-DEFINED VIEW / Definition / Purpose / Important Note == A custom view created by the user to simplify access to related data. Helps applications and users query without needing to understand all table relationships.
SCHEMABINDING / Definition / Rules / Important Note == Binds a view to the schema of its underlying tables. Requires explicit schema names and column listing. Prevents changes to dependent objects unless the view is altered first. Required for indexed views.
INDEXED VIEW / Definition / Performance / Important Note == A view with a clustered index. Improves performance for read-heavy queries. First index must be clustered. Be cautious: insert/update/delete operations may be slower due to index maintenance.
SCALAR FUNCTION / Definition / Performance / Important Note == Returns a single value. SQL Server 2019 supports inlining for better performance. Ideal for reusable logic that returns one result.
INLINE TABLE-VALUED FUNCTION / Definition / Usage / Important Note == Returns a table from a single SELECT statement. Can be used in FROM or JOIN clauses. Efficient and optimizer-friendly.
MULTI-STATEMENT TABLE-VALUED FUNCTION / Definition / Usage / Important Note == Returns a table built from multiple T-SQL statements. Allows procedural logic. Less efficient than inline TVFs due to limited optimizer integration.
USER-DEFINED TABLE TYPE (UDTT) / Definition / Usage / Important Note == A custom table structure used for table-valued parameters. Useful for passing multiple rows into procedures. Acts as a blueprint for table variables.
TABLE-VALUED PARAMETER (TVP) / Definition / Usage / Important Note == A parameter declared using a UDTT. Allows passing sets of rows into stored procedures. Must be READONLY. Improves performance and readability.
COMMON TABLE EXPRESSION (CTE) / Definition / Usage / Important Note == A temporary named result set defined with WITH. Improves query readability and supports recursion. Exists only during query execution.
TEMPORARY TABLE / Definition / Scope / Important Note == Created in tempdb. Local (#Temp this sessions only) visible to session; Global (##Temp all sessions while active) visible to all. Automatically dropped when session ends. Good for intermediate results.
PERSISTENT TEMP TABLE / Definition / Scope / Important Note == Permanent table created in tempdb. Remains until explicitly dropped. Useful for reusable staging or testing.
TEMPORARY STORED PROCEDURE / Definition / Scope / Important Note == Created in tempdb with Local (#Temp this sessions only) or Global (##Temp all sessions while active) prefix. Used for ad hoc routines. Local temp procs exist only in session; global temp procs persist until unused.
TABLE VARIABLE / Definition / Scope / Important Note == Declared with DECLARE @var TABLE. Stored in memory (may spill to tempdb). No statistics → optimizer assumes small row count. Best for small sets.
TRIGGER / Definition / Types / Important Note == Special procedure that runs automatically on events (login, schema change, data change). Used for auditing, enforcing rules, or cascading actions.
LOGON TRIGGER / Definition / Usage / Important Note == Fires when a user session starts. Used to enforce login policies or restrict access.
DDL TRIGGER / Definition / Usage / Important Note == Fires on schema changes (CREATE, ALTER, DROP). Useful for auditing and enforcing standards.
DML TRIGGER / Definition / Usage / Important Note == Fires on data changes (INSERT, UPDATE, DELETE). Used for business rules and audit trails.
CURSOR / Definition / Performance / Important Note == Enables row-by-row processing. Slower than set-based logic. Use only when necessary.
FORWARD-ONLY CURSOR / Definition / Behavior / Important Note == Fastest cursor type. Can only move forward. Ideal for simple sequential reads.
STATIC CURSOR / Definition / Behavior / Important Note == Snapshot of result set. Does not reflect changes. Allows scrolling. Higher memory usage.
KEYSET CURSOR / Definition / Behavior / Important Note == Stores keys of result set. Reflects changes to non-key columns. Inserts/deletes not visible.
DYNAMIC CURSOR / Definition / Behavior / Important Note == Fully reflects changes in underlying tables. Supports full scrolling. Most resource-intensive.

Chapter 3: Standardizing T-SQL

Goal is to make sql code easier to read, understand, and maintain.

Formatting T-SQL

These standards help ensure consistency across codebases, making it easier for teams to collaborate and maintain code over time. The sample formatting standards provided here are not applicable to our team, all upper case keywords, camel case for columns and tables, column names. However, this books has the idea that a columns that has some specail buisness logic to be included as a subquery within the selected statement top of within the column list.

Also when implementing big changes to record execute them as a single transaction so that if something goes wrong the entire change can be rolled back.

begin tran;

-- Your SQL statements here

commit;

In general, it's a good practice to use the same formatting style consistently throughout your codebase. This improves readability and maintainability. So tables, temp tables, and table variables should be formatted the same way. Lastly, use subqueries as needed, but avoid overusing them in a way that makes the code harder to read.

Also it is recommended not to use 'order by', this might create confusion becasue pulled data might be in a different order than expected.

Naming T-SQL

Here there is an orgument of camel case vs snake case. The book prefers camel case, but our team uses snake case.

When naming database objects, it's important to choose names that are descriptive and meaningful. This helps ensure that anyone working with the database can easily understand the purpose of each object.

Lastly we name tables in singular form, not plural. Views are always plural.

The book suggests using prefixes for different object types, but our team does not use prefixes, this includes not using fk_ for foreign keys, pk_ for primary keys, sp_ for stored procedures, fn_ for functions, or vw_ for views.

Commenting T-SQL

Comments are essential for explaining the purpose and logic of your code. They help others (and your future self) understand what the code is doing and why certain decisions were made.

Chapter 4: Designing T-SQL

Highlights the importance of designing T-SQL code that is not only functional but also performant, manageable, and maintainable. This mainly focuses on stored procedures, parameters, and complex logic. This is in an effort to encapsulate business logic, improve performance, and enhance code reusability.

Using Stored Procedures

The advantages of using stored procedures include:

Performance: Stored procedures are precompiled and optimized by the database engine, which can lead to faster execution times compared to ad-hoc queries.
Also, if you were to rexecute the same query multiple times, the execution plan is reused, reducing overhead.
Lastly adhoc queries can lead to older execution plans to be pushed out of memory, leading to performance issues.

Here's a concise summary of the differences between views and stored procedures in SQL Server, based on our discussion:

📘 View vs Stored Procedure Summary

Feature	View	Stored Procedure
Purpose	Encapsulates a reusable `SELECT` query	Encapsulates logic (queries, control flow)
Execution Plan Caching	❌ No (plan is compiled per calling query)	✅ Yes (plan is cached and reused)
Performance	May be less efficient for frequent use	More efficient for repeated execution
Parameter Support	❌ Not supported	✅ Fully supported
Flexibility	Limited to query logic	Supports complex logic, branching, temp tables
Optimization	Limited	High (due to plan reuse and tuning options)
Security	Basic (can restrict columns)	Advanced (can restrict table access)

✅ Recommendation For frequently used logic in applications, especially with parameters or complex operations, stored procedures are generally more efficient and flexible than views.

Using Parameters

Parameters allow you to pass values into stored procedures, making them more flexible and reusable. They help avoid hardcoding values, which can lead to maintenance challenges.

Here is the general syntax for defining parameters in a stored procedure:

CREATE PROCEDURE dbo.ProcedureName
    @Parameter1 DataType [= DefaultValue],
    @Parameter2 DataType [= DefaultValue]
AS
BEGIN
    -- Procedure logic here
    -- use @Parameter1 and @Parameter2 as needed
    -- where 1=1
    -- and var=@Parameter1
    -- and otherVar=@Parameter2
END;

Sample usage:

EXEC dbo.ProcedureName @Parameter1 = Value1, @Parameter2 = Value2;

When calling stored procedures with parameters, it's a good practice to use declarative syntax by specifying parameter names. This is to call them the same way as programming languages do it. This improves readability and reduces errors, especially when dealing with multiple parameters or optional ones.

Sample usage with declarative syntax:

DECLARE @Parameter1 DataType;
DECLARE @Parameter2 DataType;

SET @Parameter1 = Value1;
SET @Parameter2 = Value2;

EXECUTE dbo.ProcedureName @Parameter1, @Parameter2;

Note: You can use Paramter in ad-hoc queries.

DECLARE @CustomerID INT = 42;
SELECT * FROM dbo.Orders WHERE CustomerID = @CustomerID;

Note a major benefit of using parameters and stored procedures is that this allows for plan reuse, improving performance.

You want to test and monitor the performance of your stored procedures with parameters that they are performing proper parameter sniffing and plan reuse. Data might not be distributed evenly, so you want to ensure that the execution plan is optimal for all parameter values. You can test by executing the stored procedure with different parameter values and analyzing the execution plans.

Using Complex Logic

The text discusses the challenges of using complex logic in T-SQL and emphasizes how simplicity and clarity are key to achieving good performance in SQL Server. Basic operations like inserting, reading, updating, and deleting data are relatively simple, but as data structures and requirements grow, developers often face situations that require more complex logic.

When dealing with complex queries, it's crucial to break down large requests into smaller, simpler parts. This includes identifying what data is actually needed, minimizing the dataset as early as possible, and organizing logic into manageable steps. Many challenges arise from working with legacy databases or systems not designed for relational optimization, forcing developers to use less efficient coding techniques such as loops, cursors, recursion, correlated subqueries, or working with XML and JSON data. While these methods can produce correct results, they often hurt performance and make the code more difficult to maintain.

A major performance issue in SQL Server comes from row-by-row processing—especially when using cursors or WHILE loops. These constructs make SQL Server repeat logic for every record, which can drastically slow execution. Though cursors may work fine for small datasets, they scale poorly and can strain hardware as data grows. Even well-written cursors can become inefficient over time as database size or structure changes. A solution that initially seems effective can quickly turn into a major bottleneck.

The author stresses that performance problems often become visible only when the dataset becomes large. For instance, two different T-SQL methods might perform equally well on small samples but differ drastically when processing tens of thousands of records. An example comparing queries for listing recipes and their ingredients illustrates this point: while both queries return the same results, the set-based version performs far better than the loop-based cursor approach when handling large volumes of data.

To manage complex logic effectively, the author recommends focusing on:

Breaking down logic into simpler steps early in the design process.
Minimizing data as soon as possible.
Using set-based operations instead of iterative loops.
Writing T-SQL code that is clear, modular, and maintainable.

The discussion then shifts toward best practices for writing efficient and understandable T-SQL. Developers should aim for code that balances clarity, performance, and maintainability. Using stored procedures helps create reusable, consistent code. Parameters make code flexible and dynamic but must be used carefully to avoid parameter sniffing issues. It's often better to write simple, straightforward code rather than overly complex logic that appears elegant but performs poorly.

The author also emphasizes writing understandable T-SQL by:

Choosing the best data types for efficiency and accuracy.
Understanding SQL Server's internal database objects to decide which to use in each scenario.
Balancing readability vs. performance depending on the situation.
Maintaining clear formatting, naming conventions, and comments so others can easily understand the code.

In conclusion, the author asserts that the most effective T-SQL code is both readable and efficient. Developers should learn to segment complex problems, simplify logic, and prioritize set-based thinking. Once they are comfortable writing clear, maintainable T-SQL, they can then focus on deeper optimization. In short, simplicity in design leads to better scalability, performance, and long-term reliability in SQL Server applications.

Chapter 3: Standardizing T-SQL : Flash Cards

What is the goal of standardizing T-SQL? == To make SQL code easier to read, understand, and maintain.
Why is formatting T-SQL important? == It ensures consistency across codebases, improving collaboration and maintainability.
What formatting style does the book suggest for keywords and identifiers? == Uppercase for keywords, camelCase for columns and tables.
What formatting style does our team use? == Snake_case for identifiers.
How should special business logic columns be handled? == Include them as subqueries within the SELECT statement.
Why should big changes be executed as a single transaction? == So the entire change can be rolled back if something goes wrong.
What is the syntax for a transaction in T-SQL? == sql\nbegin tran;\n-- SQL statements\ncommit;\n
Why should formatting be consistent across tables, temp tables, and table variables? == To improve readability and maintainability.
What is the recommendation regarding subqueries? == Use them as needed, but avoid overuse that reduces readability.
Why should 'ORDER BY' be avoided in some cases? == It may cause confusion due to unexpected data order.
What naming convention does the book prefer? == CamelCase.
What naming convention does our team use? == Snake_case.
How should database object names be chosen? == Use descriptive and meaningful names.
What form do we use for table names? == Singular.
What form do we use for view names? == Plural.
Does our team use prefixes like fk_, pk_, sp_, fn_, vw_? == No, we do not use prefixes.
Why are comments important in T-SQL? == They explain the purpose and logic of code, aiding understanding and maintenance.
What is the focus of Chapter 4: Designing T-SQL? == Creating functional, performant, manageable, and maintainable code.
What are the benefits of using stored procedures? == Performance, plan reuse, encapsulation of logic, and reusability.
Why are stored procedures more performant than ad-hoc queries? == They are precompiled and reuse execution plans.
What issue can ad-hoc queries cause? == They can push older execution plans out of memory, reducing performance.
What is the main purpose of a view in SQL Server? == To encapsulate a reusable SELECT query.
What is the main purpose of a stored procedure? == To encapsulate logic including queries, control flow, and parameters.
Do views support execution plan caching? == No.
Do stored procedures support execution plan caching? == Yes.
Do views support parameters? == No.
Do stored procedures support parameters? == Yes.
Which is more flexible: views or stored procedures? == Stored procedures.
Which is more optimized for repeated execution? == Stored procedures.
Which offers better security options? == Stored procedures.
When should stored procedures be preferred over views? == For frequently used logic with parameters or complex operations.
What do parameters in stored procedures allow? == Passing values to make procedures flexible and reusable.
What is the syntax for defining parameters in a stored procedure? == sql\nCREATE PROCEDURE dbo.ProcedureName\n@Parameter1 DataType,\n@Parameter2 DataType\nAS\nBEGIN\n-- logic\nEND;\n
What is the syntax for calling a stored procedure with parameters? == sql\nEXEC dbo.ProcedureName @Parameter1 = Value1, @Parameter2 = Value2;\n
Why use declarative syntax when calling stored procedures? == Improves readability and reduces errors.
Can parameters be used in ad-hoc queries? == Yes.
What is an example of using a parameter in an ad-hoc query? == sql\nDECLARE @CustomerID INT = 42;\nSELECT * FROM dbo.Orders WHERE CustomerID = @CustomerID;\n
What is a major performance benefit of using parameters and stored procedures? == They allow for execution plan reuse, improving performance.
Why should you test stored procedures with different parameter values? == To ensure proper parameter sniffing and optimal execution plans for all values.
What can uneven data distribution affect in stored procedures? == It can lead to suboptimal execution plans if not tested properly.
How can you test for parameter sniffing issues? == Execute the stored procedure with various parameter values and analyze the execution plans.
What is the key to good performance in SQL Server when using complex logic? == Simplicity and clarity.
What are basic T-SQL operations? == Inserting, reading, updating, and deleting data.
What should developers do when facing complex queries? == Break them into smaller, simpler parts.
Why is minimizing the dataset early important? == It improves performance by reducing the amount of data processed.
What challenges arise from legacy systems in SQL Server? == They often require inefficient techniques like loops, cursors, or recursion.
What are some less efficient coding techniques mentioned? == Loops, cursors, recursion, correlated subqueries, XML/JSON processing.
What is a major performance issue in SQL Server? == Row-by-row processing using cursors or WHILE loops.
Why are cursors problematic for large datasets? == They scale poorly and can strain hardware resources.
What happens to well-written cursors as data grows? == They can become inefficient and cause bottlenecks.
Why might two T-SQL methods perform differently on large datasets? == Set-based operations scale better than loop-based approaches.
What is the recommended approach for handling large volumes of data? == Use set-based operations instead of cursors or loops.
What are the best practices for managing complex logic in T-SQL? == Break logic into steps, minimize data early, use set-based operations, write maintainable code.
What should developers prioritize when writing T-SQL? == Clarity, performance, and maintainability.
How do stored procedures help with maintainability? == They encapsulate reusable and consistent logic.
What is a risk when using parameters in stored procedures? == Parameter sniffing issues.
Why is simple code often better than elegant complex logic? == It performs better and is easier to maintain.
What helps make T-SQL understandable? == Choosing efficient data types, understanding internal objects, balancing readability and performance.
What formatting and naming practices improve code clarity? == Consistent formatting, meaningful names, and clear comments.
What is the most effective T-SQL code like? == Readable, efficient, and scalable.
What should developers focus on before deep optimization? == Writing clear, maintainable, and set-based T-SQL.
What does simplicity in design lead to in SQL Server? == Better scalability, performance, and long-term reliability.

Pro T-SQL 2019

Part I: Building Understandable T-SQL

Chapter 1: Data Types

Number Data Types

Exact Number Data Types

Approximate Number Data Types

Converting Number Data Types

String Data Types

Character String Data Types

Unicode String Data Types

Binary String Data Types

Date and Time Data Types

DATE

TIME

SMALLDATETIME, DATETIME, DATETIME2, DATETIMEOFFSET

Other Data Types

UNIQUEIDENTIFIER

XML

Spatial Geometry Types

Spatial Geography Types

SQL_VARIANT

Rowversion

HIERARCHYID

Table

Cursor

Chapter 1: Data Types : Flash Cards

Chapter 2: Database Objects

Views

User-Defined Views

SCHEMABINDING

Indexed View

Functions

Scalar Functions

Table-Valued Functions

Multi-statement Table-Valued Functions

Situations where MSTVFs are not ideal

Other User-Defined Objects

User-Defined Table Types

Table-Valued Parameters

Common Table Expressions (CTEs)

Temporary Objects

Temporary Tables (Temp Tables)

Table Variables (Table Vars)

Temporary Stored Procedures

Triggers

Logon Triggers

Data Definition Language (DDL) Triggers (DDL Triggers)

Data Manipulation Language (DML) Triggers (DML Triggers)

Cursors

Forward-Only Cursors (FO Cursors)

Static Cursors (Static Cursors)

Keyset Cursors (Keyset Cursors)

Dynamic Cursors (Dynamic Cursors)

Chapter 2: Database Objects : Flash Cards

Chapter 3: Standardizing T-SQL

Formatting T-SQL

Naming T-SQL

Commenting T-SQL

Chapter 4: Designing T-SQL

Using Stored Procedures

Using Parameters

Using Complex Logic

Chapter 3: Standardizing T-SQL : Flash Cards

Part II: Building Performant T-SQL

Chapter 5: Set-Based Design

Introduction to Set-Based Design

Thinking in Data Sets

Writing Code for Data Sets

Chapter 6: Hardware Usage

Considering Memory with T-SQL Design

Considering Storage with T-SQL Design

Considering CPU with T-SQL Design

Chapter 7: Execution Plans

Reading Execution Plans

Index Usage in Execution Plans

Logical Join Types in Execution Plans

Chapter 8: Optimize T-SQL

Optimizing Logical Reads

Optimizing Duration

Automatic Database Tuning