Lesson 1

Thus far we have used Transact-SQL to write queries that capture information in your database. This module covers ways in which you can use Transact-SQL to interact with your server in other ways. I call it the programming module because it covers ways that you can integrate programming concepts into your Transact-SQL statements to give you greater control over your database.

In SQL Server 2022, you can use built-in system functions to obtain information about database operations, system status, session context, and query execution. These functions are invaluable for monitoring, diagnostics, and enforcing business logic.
1. 🧠 System Information Functions
Used to retrieve server-level and system-level metadata.

Function	Description
@@VERSION	Returns the current SQL Server version.
SERVERPROPERTY('ProductLevel')	Returns product update level (e.g., RTM, CU1).
HOST_NAME()	Returns the name of the client machine.
APP_NAME()	Returns the name of the client application.
DB_NAME()	Returns the current database name.

SELECT @@VERSION, HOST_NAME(), APP_NAME(), DB_NAME();

Access information about database objects and schemas.

Function	Description
`OBJECT_NAME(object_id)`	Returns the name of the object for a given ID.
`OBJECT_ID('schema.table')`	Returns the object ID of a table or view.
`COL_LENGTH('table', 'column')`	Returns the length of a column in bytes.
`COLUMNPROPERTY(OBJECT_ID('table'), 'column', 'Precision')`	Returns column metadata.

3. 🗂️ Session and Context Functions
Use these for query context or security-sensitive operations.

Function	Description
SESSION_USER	Returns the user name of the current session.
SUSER_NAME()	Returns the login name for the current user.
CURRENT_USER	Returns the name of the current context.
ORIGINAL_LOGIN()	Returns the original login before impersonation.
CONTEXT_INFO()	Returns or sets up to 128 bytes of binary data associated with the session.

Monitor and control transactions or locking behavior.

Function	Description
@@TRANCOUNT	Returns the number of open transactions.
XACT_STATE()	Returns the state of the current transaction.
APPLOCK_MODE()	Returns the lock mode on an application resource.

IF @@TRANCOUNT > 0
    PRINT 'Transaction in progress';

5. 🧮 Execution & Performance Functions
Used within queries or stored procedures to monitor execution.

Function	Description
`@@ROWCOUNT`	Number of rows affected by the last statement.
`GETDATE()`	Returns the current date and time.
`SYSDATETIME()`	More precise version of `GETDATE()` (returns `datetime2`).
`NEWID()`	Returns a uniqueidentifier (UUID).
`ISNULL()`	Replaces NULL with specified value.

Helps with diagnostics and optimization.

Function	Description
sys.dm_exec_requests	Shows active requests currently executing.
sys.dm_exec_query_stats	Shows performance statistics for cached queries.
sys.dm_exec_sql_text(sql_handle)	Returns the SQL text for a specific handle.
sys.dm_exec_query_plan(plan_handle)	Returns the execution plan XML.

📌 Example: Combine Functions in a Real Query

SELECT
    s.session_id,
    s.login_name,
    r.status,
    r.command,
    r.cpu_time,
    t.text AS query_text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t;

This query lets you track what each session is running in real time.

After completing this module, you will be able to:

1. Declare and use variables
2. Use functions to obtain information and perform SQL Server 2012 operations
3. Handle errors generated by SQL Server 2012
4. Program your code so that errors are raised to notify you of events

In the next lesson, declaration and use of variables in your Transact-SQL code will be discussed.

Well, you now know how to declare variables, but the question that follows is, How do you change their values?
There are three ways to set the value in a variable. You can initialize it in the DECLARE statement, use a SELECT statement, or use a SET statement. Functionally, SET and SELECT work almost the same, except that a SELECT statement can do a couple more things:

1. SELECT can assign a value from a column in the SELECT statement
2. SELECT can assign values to many variables in the same statement

So why have two ways of doing this? SELECT predates SET back in SQL Server history, so why go to the trouble to implement SET at all?
Suffice to say that SET is now part of the ANSI/ISO standard, and that is why it has been put in there. However, I cannot find anything wrong with the same functionality in SELECT, even ANSI/ISO seems to think that it is okay.