SQL Server Advanced Concepts

In SQL Server, @@ERROR returns the error number (which corresponds to message_id in sys.messages) of the last executed statement. If the statement executes successfully, it returns 0. Thus, when an error occurs, it directly yields that ID.

ACID stands for Atomicity, Consistency, Isolation, and Durability - the four essential properties that guarantee database transactions are processed reliably. Without these properties, the operation cannot be considered a proper transaction. A transaction by definition must exhibit all four ACID characteristics to ensure data integrity.

The Read Uncommitted isolation level does not issue shared locks and does not honor exclusive locks, allowing transactions to read data that has been modified by other transactions but not yet committed (dirty reads).

A deterministic function always returns the same result for the same input. Non-deterministic functions can return different results. ISDATE is non-deterministic because its behavior depends on session settings like DATEFORMAT and LANGUAGE - the same string might be interpreted differently. CASE, ISNULL, and ISNUMERIC are deterministic functions.

The COUNT(*) function in SQL counts all rows that match the query criteria, including rows containing duplicate values and rows containing NULL values, unlike COUNT(column_name) which ignores NULLs.

The sys.sql_modules catalog view returns a row for each object that is a SQL language module. This includes stored procedures (P, RF), views (V), triggers (TR), scalar functions (FN), inline table-valued functions (IF), table-valued functions (TF), and rules (R). The type codes listed match SQL Server's object type catalog entries.

TRUNCATE TABLE is a minimally logged operation that cannot fire triggers. Unlike DELETE statements which activate row-level triggers, TRUNCATE deallocates data pages without logging individual row deletions, bypassing trigger execution entirely. If you need trigger behavior, use DELETE instead. TRUNCATE is faster but less flexible.

The extended stored procedure xp_fileexist checks whether a specified file exists on the server's file system and returns output parameters indicating file existence. It returns 1 in the 'File Exists' parameter if the file is found. Note: Extended stored procedures are deprecated in modern SQL Server versions in favor of FILESTREAM and other methods.

DBCC CHECKDB is a comprehensive command that performs database validation including checks done by DBCC CHECKALLOC (allocation structure validation), CHECKTABLE, and CHECKCATALOG. Running CHECKDB already includes allocation checks, so running CHECKALLOC separately is redundant. CHECKDB is the all-in-one integrity check command for production databases.

In ADO.NET, MARS stands for Multiple Active Result Sets, a feature that allows multiple batches of results to be executed and retrieved over a single connection.

Relational databases require transaction logging to ensure ACID properties, particularly durability and recovery. Thus, there must be at least one transaction log file (or log group) associated with each database.

The XML data type in SQL Server 2005 can store up to 2 GB per instance, matching the maximum size for LOB types (varchar(max), nvarchar(max), varbinary(max)). This 2 GB limit applies to the XML document stored in the column. XML data is stored in an internal binary representation that preserves Unicode fidelity.

Trigger names cannot begin with # or ## because those prefixes designate temporary and global temporary objects respectively. Triggers are permanent schema objects that must follow regular identifier naming rules. The # prefix is reserved for temporary tables and temporary stored procedures that are automatically cleaned up when the session ends.

When ANSI_NULLS is OFF (non-standard behavior), the comparison 'value = NULL' returns TRUE instead of UNKNOWN when value is NULL. This violates SQL standard three-valued logic. By default, ANSI_NULLS is ON, where any comparison involving NULL returns UNKNOWN. The OFF setting makes NULL behave like a regular value rather than a special marker for unknown data.

A Common Table Expression (CTE) is defined using the WITH clause followed by the CTE name and its query. The WITH statement initiates a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are not created with SELECT or CREATE keywords.

sp_lock is a legacy system stored procedure that returns information about locks. However, it has been deprecated since SQL Server 2005, and the recommended approach is to use the sys.dm_tran_locks DMV, which provides more detailed and current lock information.

DENY is a Data Control Language (DCL) command that explicitly denies a permission to a principal, preventing permission inheritance. GRANT, REVOKE, and DENY are the core DCL statements. SELECT and UPDATE are DML (Data Manipulation Language) commands, while APPLY is a table operator used in JOINs.