Database Management Systems

SQL Server 2005 has four system databases that are essential for server operation. Master is the core database with system metadata, Model serves as a template for new databases, msdb stores SQL Agent jobs and SSIS packages, and tempdb is for temporary objects. The option 'Temporary database' is misleading - the actual name is 'tempdb'.

Upgrade Advisor analyzes SQL Server 2000 objects to identify potential issues before upgrading to 2005. It can examine Stored procedures, DDL triggers, and Trace files for deprecated features or breaking changes. It cannot analyze desktop applications or encrypted stored procedures (encryption prevents analysis).

SQL Server 2005 provides three main methods to access a Linked Server: Distributed Queries using four-part naming convention (LinkedServer.Database.Schema.Table), Remote Stored Procedures that execute directly on the linked server, and the EXECUTE statement with the AT Linked Server clause for pass-through queries. OPENROWSET is an ad-hoc method that doesn't require a predefined linked server, making it distinct from these linked server-specific access methods.

Full-Text Search in SQL Server 2005 provides specialized indexing for fast keyword-based queries on text data. Unlike standard SELECT with LIKE patterns which scan entire tables, Full-Text Search builds inverted indexes allowing efficient searches for words, phrases (even with proximity), and inflectional forms. Option B incorrectly describes Service Broker (message-based communication), and Option C describes Database Mail, both unrelated to Full-Text Search.

The OPENQUERY function requires a linked server to be configured first. The error occurs because SQL 1 doesn't have SQL 2 registered as a linked server. Adding SQL 2 as a linked server (option A) creates the necessary connection object that OPENQUERY can then reference. A remote server (option B) is an older SQL Server concept mainly for remote stored procedures, not for OPENQUERY queries. Aliases (option C) are for local naming, not for cross-server connections.

This is an exact duplicate of question 143704. The OPENQUERY function requires a linked server to be configured first. The error occurs because SQL 1 doesn't have SQL 2 registered as a linked server. Adding SQL 2 as a linked server (option A) creates the necessary connection object that OPENQUERY can then reference. A remote server (option B) is an older SQL Server concept mainly for remote stored procedures, not for OPENQUERY queries. Aliases (option C) are for local naming, not for cross-server connections.

SQL Server recovery models control logging behavior. In Simple recovery, bulk operations like BULK INSERT are minimally logged for performance - only page allocations are logged, not individual row changes. Full recovery logs all operations completely. Bulk-Logged recovery minimally logs bulk operations but fully logs other transactions. Option D is correct because Simple recovery specifically uses minimal logging for bulk operations to reduce log space and improve performance.

A database schema functions as a logical namespace to group related database objects (such as tables, views, and indexes) under a common owner or organization. It does not group separate databases or define catalogs.

When determining which backups to use for restoration, SQL Server error log (A) contains backup and restore operation messages. The Windows Application Event Log (C) also records SQL Server backup events. The msdb.dbo.backupset table (D) stores complete backup history including timestamps, LSNs, and file paths. The msdb.dbo.sysbackuphistory table (B) doesn't exist - it's actually msdb.dbo.backupset and msdb.dbo.backupfile that contain this information.

When moving a database between servers while keeping reports functional, Backup/Restore (C) preserves all database objects, permissions, and metadata without users needing to modify anything. SMO (B) can also transfer databases with full metadata preservation. Detach/Attach (A) can work but is more disruptive and may require fixing orphaned users. Option D 'All' is incorrect because not all methods are equally suitable - Detach/Attach has significant limitations for this use case.

Oracle's DATETIME data types include TIMESTAMP (stores date and time), INTERVAL YEAR TO MONTH (for periods of years and months), and INTERVAL DAY TO SECOND (for periods of days, hours, minutes, seconds). Option B 'INTERVAL MONTH TO DAY' is not a valid interval type - intervals go from larger to smaller units. Option C 'TIMESTAMP WITH DATABASE TIMEZONE' is incorrect - the correct syntax is TIMESTAMP WITH DATABASE TIMEZONE or TIMESTAMP WITH TIMEZONE.

The function lr_resume_transaction_instance resumes the collection of transaction instance data for performance analysis after it has been paused. This is typically used in application performance monitoring tools to control when transaction data is being gathered. Option A incorrectly suggests resuming transaction status rather than data collection, while option B omits the word 'instance'.

In XML DTD, ENTITIES are used to define storage units and reusable content. Entities allow you to define shortcuts for repeated text, special characters, or external content. ATTLIST defines attributes, ELEMENTS defines element structures, and DOCTYPE declares the DTD itself.

Drill-through reports in Cognos allow users to navigate from a summary report to a detailed report by clicking on data points, such as values in a chart. This is the primary purpose of drill-through functionality.

Rogers' Customer Profile Database (CPD2), which supports caller identification and business routing rules for their national IVR system, was historically developed and deployed on the Oracle 10g database engine to handle high-concurrency queries and ensure platform reliability.