SQL Server Database Fundamentals

TIMESTAMP columns track row version changes but not actual date/time. A DATETIME column with getdate() default only captures creation time, not updates. UNIQUEIDENTIFIER stores GUIDs, not timestamps. To track every INSERT/UPDATE operation, you need a DATETIME column with a trigger that fires on these events.

In SQL Server, casting a datetime to a float represents the date as the number of days since a base date (1900-01-01). Applying floor() removes the decimal part (which represents the time portion), and casting it back to datetime returns the original date at midnight (00:00:00.000), stripping the time.

SQLCLR's SAFE permission set allows only specific .NET Framework classes that have been tested and verified for SQL Server use. Not all framework classes are available - SAFE restricts to a subset of supported assemblies designed for SQL Server hosting. Option A is wrong because SAFE restricts more than just unmanaged code. Option B is wrong because some classes work with SAFE. Option D is wrong because permission sets directly control SQLCLR capabilities.

Database snapshots in SQL Server use a copy-on-write mechanism and are nearly instantaneous regardless of database size. The snapshot creation process just initializes the sparse file structure - actual data copying happens gradually as writes occur to the source database. Modern hardware with RAID 5 can complete this in seconds, not minutes or hours.

Navigational systems required programmers to explicitly navigate through the database by knowing the physical structure of tables, indexes, and relationships. This meant you had to understand HOW data was stored and accessed, not just WHAT data you wanted. SQL's key advantage is being declarative - you specify the desired result without specifying the access path.

A core challenge in SQL is query performance variability - the same logical query can be expressed in syntactically different ways (different JOIN orders, subqueries vs. JOINs, EXISTS vs. IN, etc.), and each variation may generate a completely different execution plan with vastly different performance characteristics. This makes optimization crucial.

SQL statement tuning centers on optimization quality - whether the query optimizer finds an efficient execution plan. The number of CPUs (A) and degree of parallelism (B) are system resources, not tuning considerations. Bitmap indexes (C) are specific access methods, not general tuning principles. Optimization quality directly affects performance.

Counterintuitively, introducing controlled redundancy (denormalization) often improves SQL performance by reducing joins and table scans. Removal of redundancy (A) is normalization, which can hurt performance. Non-first normal form (C) relates to complex values, not performance. SQL*Plus (D) is an Oracle tool, irrelevant to performance.

Indexes are bypassed when: NOT operator negates index usability (A), functions/expressions on indexed columns prevent direct index lookup (C), and ORDER BY with NULL columns on non-unique indexes may not use indexing effectively (E). IN operator (B) can use indexes, and SELECT clause columns (D) don't affect predicate indexing.

In SQL, the LIKE operator is used for pattern matching. The % wildcard matches any sequence of characters (including zero characters), while * is not a valid wildcard in SQL LIKE clauses. The = operator performs exact matching, not pattern matching.

For SQL Server 2000, the DATETIME type only supports dates from January 1, 1753 onwards, which cannot store historical dates like February 22, 1732. Storing dates as INT (days since a reference point) or VARCHAR allows working with pre-1753 dates. This is a SQL Server-specific limitation.

The public role exists in every database including master, msdb, tempdb, model, and all user databases. It cannot be dropped and captures default permissions. The statement that it is NOT in the master database is false, making this the correct NOT answer.

A signed INT in SQL Server holds values from -2,147,483,648 to 2,147,483,647. Option C (4,294,967,295) would be the maximum for an unsigned INT, but SQL Server INT is signed. Option D exceeds even the unsigned maximum.

The expression 2000/02/25 is interpreted as integer division (2000 divided by 2 divided by 25), not as a date. 2000/2 = 1000, then 1000/25 = 40. So 40 + 4 = 44. To add days to a date, you must use the DATEADD function with a proper DATETIME value.

SQL Server's DATETIME data type can store dates from January 1, 1753 through December 31, 9999. The 1753 lower bound is due to the transition from the Julian to the Gregorian calendar in the British Empire.

The db_accessadmin fixed database role has permission to add and remove user IDs from the database. db_securityadmin manages role memberships and permissions, db_setupadmin manages configuration, and there is no db_sysadmin role (sysadmin is a server role, not a database role).

In SQL Server's data type precedence hierarchy, BIGINT has the lowest precedence among the options. The order from lowest to highest is: BIGINT, INT, SMALLINT, TINYINT, DECIMAL/NUMERIC, MONEY, FLOAT, REAL. When different types interact, the lower precedence type converts to the higher one.

SQL Server 2000 stored procedures can have up to 1,024 parameters. This limit was increased from earlier versions. Options A, B, and C are too low. The 1,024 limit is a well-documented SQL Server 2000 specification.

In SQL Server, user-defined functions cannot call non-deterministic functions like GETDATE(). GETDATE() returns different values each time it's called, making it non-deterministic, which violates UDF restrictions. The rest of the function (stripping time, adding days) is syntactically correct, but the GETDATE() call makes it invalid.