SQL Server Administration and Query Fundamentals

The sys.servers catalog view explicitly lists all linked servers configured on the SQL Server instance, along with their properties. You can identify linked servers by checking the is_linked column value, making this claim false.

In SQL, when a WHERE clause filters on an INTEGER column, the comparison value must not be enclosed in quotes. The query WHERE Employee_Id='1232' treats '1232' as a string literal, which may cause type mismatch errors or implicit conversion issues depending on the database system. The correct syntax is WHERE Employee_Id=1232.

ORDER BY sorts records in ascending order by default, not descending. To sort in descending order, you must explicitly use the DESC keyword (e.g., ORDER BY column_name DESC). This is standard SQL behavior across all major database systems.

A LEFT JOIN returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL from the right side. Thus, the left table always returns all rows.

UNION removes duplicate rows by default, combining the result sets and eliminating duplicates. UNION ALL preserves all rows including duplicates from both SELECT statements, which is more efficient when you don't need duplicate elimination. The options 'UNION ENTIRE' and 'FULL UNION' are not valid SQL keywords.

The SQL query fails because aggregate functions like count(*) cannot be evaluated in a WHERE clause. To filter groups created by GROUP BY, the HAVING clause must be used. Note that the query also selects employee_name which is not in the GROUP BY clause, but the syntax error of WHERE count(*) is resolved by changing it to HAVING count(*) in standard SQL execution rules.

In DB2 and other SQL databases, UR stands for Uncommitted Read, an isolation level that allows reading uncommitted data. This is also known as a dirty read isolation level. Option A (Under Read) and D (Uncommitted Ride) are not standard SQL terminology.

UR (Uncommitted Read) isolation level allows reading both committed and uncommitted data. An application using UR can read data that has been changed but not yet committed, as well as committed data. The question asks what UR 'can' do, and it can do both - the 'both a and b' option correctly captures this flexibility.

Both DELETE FROM table (without WHERE clause) and TRUNCATE TABLE table remove all rows while keeping the table structure. DELETE is logged and can be rolled back, while TRUNCATE is a DDL operation that's faster but cannot be rolled back. 'DELETE * FROM' is incorrect syntax, and DROP TABLE removes the entire table structure.

sp_who2 is a system stored procedure that returns information about current users, sessions, and processes in SQL Server. The query 'select * from sys.sysprocesses' directly accesses the system view containing process information. sp_helpdb shows database information, and sp_helpprocess is not a valid SQL Server stored procedure. Both C and D are valid methods.

SQL Server's default port for TCP/IP communication is 1433. This is the well-known port used by the SQL Server Database Engine to listen for incoming client connections. Port 1434 is used for the SQL Server Browser Service (which helps clients find named instances), while 1432 and 1435 are not standard SQL Server ports.

Database mirroring requires the FULL recovery model because it needs to maintain a complete transaction log to ship log records from the principal to the mirror server. The Simple recovery model truncates the log automatically, preventing log shipping. Bulk-logged recovery also has limitations that make it unsuitable for mirroring. 'Half' is not a valid recovery model in SQL Server.

SQL Server supports two authentication modes: Windows Authentication (uses Windows Active Directory accounts) and Mixed Mode (allows both Windows Authentication and SQL Server authentication). 'Builtin/Administrator' is not a mode name. 'SQL authentication' alone refers to the SQL Server-specific authentication used within Mixed mode, not a standalone mode.

In SQL Server, transaction logs are truncated by backing them up (BACKUP LOG). This marks the inactive portion of the log as truncatable and reuses the space. Simply firing a TRUNCATE command doesn't work on transaction logs. The database must be in Full or Bulk-Logged recovery model for log backups to be meaningful. Logs CAN be truncated in SQL Server - this is what backup and maintenance plans do.

SQL Server has multiple index types: Clustered indexes (one per table, stores data physically sorted), Non-clustered indexes (multiple per table, store separate pointer structures), Filtered indexes (non-clustered indexes with a WHERE clause), Covering indexes (non-clustered indexes that include all columns needed for a query), and Composite indexes (indexes on multiple columns). Bit Map indexes exist in Oracle but not SQL Server.

SQL Server has 5 system databases: Master (stores system-level information), Model (template database), MSDB (stores SQL Agent jobs, alerts, and operators), TempDB (temporary workspace), and Resource (read-only system objects). MSDB specifically stores information about scheduled jobs running on SQL Server. The correct answer is '5,MSDB'.

In SQL Server, a page is the fundamental unit of data storage, sized at 8 KB. An extent consists of 8 contiguous pages, making it 64 KB total. Options A and D incorrectly use MB instead of KB, while option C has wrong values.

A view in SQL Server is a virtual table whose contents are defined by a query, serving as a logical view of a table. It does not physically store data, ruling out "Physical view", and is based on select queries rather than stored procedures.

SQL Server 2005 offers three main high availability solutions: failover clustering for server-level protection, database mirroring for database-level redundancy, and log shipping for disaster recovery. Data Guard and RAC are Oracle technologies, not SQL Server features.

SQL Server Enterprise Edition provides full high availability features, including asynchronous database mirroring with automatic failover using a witness server. Standard Edition only supports synchronous mirroring without a witness, while Workgroup and Express have very limited mirroring capabilities.