SQL Complete Test 2008

In a table definition, only one primary key constraint can be included. The primary key constraint ensures that each row in the table is uniquely identified by a specific column or combination of columns. It is used to enforce data integrity and prevent duplicate or null values in the primary key column(s). Having multiple primary key constraints in a table definition would be redundant and unnecessary.

The statement is true because in most database management systems, including popular ones like Oracle and SQL Server, the default isolation level is READ COMMITTED. This means that each transaction can only see data that has been committed by other transactions, ensuring consistency and preventing dirty reads. However, it is important to note that the default isolation level can be changed by the database administrator if needed.

SQL Server does indeed create one data file and one log file for each newly created database by default. The data file is used to store the actual data within the database, while the log file is used to record all the changes made to the database. This allows for recovery and rollback operations in case of any errors or failures.

The default "SORT" order for a SQL is ascending. This means that the data will be sorted in ascending order, from the lowest value to the highest value.

Compound operators are shorthand notations that combine an arithmetic or logical operation with an assignment operation. In SQL Server 2008, the following are compound operators: += (addition and assignment), %= (modulus and assignment), and *= (multiplication and assignment). Therefore, the correct answer is "all of above" as all the given options are compound operators in SQL Server 2008.

A clustered index can also be a unique index because a clustered index determines the physical order of data in a table. This means that each row in the table can have a unique value for the clustered index key. Therefore, by defining a clustered index as unique, it ensures that no two rows in the table can have the same value for the clustered index key.

The SQL Server Agent is a service that allows users to schedule and automate tasks in SQL Server. It provides functionality for creating jobs, defining schedules, and setting up alerts. With SQL Server Agent, users can schedule tasks such as backups, data imports, and report generation, making it a valuable tool for managing and maintaining SQL Server databases. Therefore, the statement "The SQL Server Agent is a service that lets you configure scheduled tasks and system alerts" is true.

The correct answer explains the difference between UNION and UNION ALL. It states that UNION returns only unique records from both tables, while UNION ALL returns all records from both tables. This means that if there are any duplicate records between the two tables, UNION will eliminate them, while UNION ALL will include them in the result. Additionally, the answer mentions that UNION ALL is no longer supported in SQL Server 2000 onward.

SQL Server provides three different recovery models that allow you to specify the way SQL Server manages log files and prepares your enterprise for a disaster. Each of these models represents a different approach to balancing the tradeoff between conserving disk space and providing for granular disaster recovery options. The three disaster recovery models offered by SQL Server are:

1. Simple Recovery Model
2. Full Recovery Model
3. Bulk-logged Recovery Model

The correct answer is DECLARE CURSOR. This statement is used to define a cursor in SQL. A cursor is a database object that allows the retrieval and manipulation of data row by row. The DECLARE CURSOR statement is used to specify the query that will be used to populate the cursor, as well as any other options or parameters. Once the cursor is declared, it can be used to fetch data from the result set and perform operations on it.

If we don't implement a proper locking strategy, it can lead to various problems such as dirty reads, phantom reads, and lost updates. Dirty reads occur when a transaction reads data that has been modified by another transaction but not yet committed. Phantom reads occur when a transaction reads a set of rows that satisfy a certain condition, but another transaction inserts or deletes rows that also satisfy the same condition, causing the first transaction to see a different set of rows. Lost updates occur when two or more transactions try to update the same data simultaneously, resulting in one transaction's update being overwritten by another. Therefore, implementing a proper locking strategy is crucial to avoid these issues.

The SQL Query Analyzer is indeed the main interface for running SQL queries against a database. It provides a platform for users to write and execute SQL queries, view query results, and analyze query performance. This tool is commonly used by database administrators and developers to interact with the database and retrieve or manipulate data.

In T-SQL, parameters and variables do not start with the "%" sign. The "%" sign is used as a wildcard character in SQL Server for pattern matching. Parameters and variables in T-SQL are typically declared using the "@" symbol. Therefore, the given statement is incorrect.

The statement suggests that default is a type of constraint, but it does not actually enforce any rules or restrictions. This implies that default is more of a default value or setting that is automatically applied when no other value is specified. Therefore, the correct answer is "Yes" to agree with the statement.

The statement is true. An INSTEAD OF trigger can indeed be applied to a view and it is triggered based on modifications made to the view. This trigger allows the user to specify custom actions to be performed instead of the default actions that would occur when modifying the view. It gives the user more control over the behavior of the view and allows for more complex operations to be performed.

Referential integrity is the type of integrity that preserves the defined relationship between tables when records are entered or deleted. It ensures that any foreign key values in a table reference existing primary key values in another table, thus maintaining the consistency and validity of the data.

When data is inserted through a stored procedure, the execution plan is stored in the cache after it is executed for the first time. This means that subsequent executions of the stored procedure can use the cached execution plan, reducing the need for the database to generate a new plan each time. This reduces network traffic because the execution plan does not need to be sent over the network each time the stored procedure is executed. Additionally, since the execution plan is already stored in the cache, it can be quickly retrieved and executed, improving the overall performance of the database.

RAISERROR is a Transact-SQL statement used to generate custom error messages. The statement has the capability to log the error message in the error log (option a), print the message to the application (option b), and assign an error number, state, and severity to the error message (option c). Therefore, the correct answer is a, b, and c.

SQL Server has both Logical Architecture and Physical Architecture. This means that it has a logical structure that defines how data is organized and accessed, as well as a physical structure that determines how the data is stored and retrieved from the disk. SQL Server is also a DBMS (Database Management System), which means it is a complete database solution that allows users to create, manage, and manipulate databases. Additionally, SQL Server is compatible with structured query language (SQL) and provides rich support for XML, making it a versatile and powerful tool for database management and data manipulation.

By default, SQL Server has the READ COMMITTED isolation level. This means that each transaction can only see committed data from other transactions. It ensures that dirty reads are not allowed, meaning that a transaction cannot read uncommitted data from another transaction. This isolation level provides a balance between data integrity and concurrency, allowing multiple transactions to access and modify data simultaneously while still maintaining consistency.

Both the "EXEC" and "EXECUTE" commands are used to run a stored procedure. The "EXEC" command is a shorthand form of "EXECUTE" and both can be used interchangeably to execute a stored procedure in SQL. Therefore, the correct answer is "Both a and b above".

An index is a data structure that allows an application program to efficiently find specific data without having to scan through the entire table. It acts as a roadmap, pointing to the location of the desired data, making searches faster and more efficient. By using an index, the application program can directly access the specific data it needs, rather than having to search through every record in the table. Therefore, the statement "Allows an application program to find specific data without scanning through the entire table" is a perfect match with reference to an index.

When a primary key is defined in SQL Server, it automatically creates a clustered index on that column. This is because primary keys are used to uniquely identify each row in a table, and having a clustered index on the primary key helps improve the performance of queries that involve searching, sorting, and joining data based on that key. On the other hand, SQL Server does not automatically create an index on foreign keys, as they are used to establish relationships between tables rather than uniquely identify rows. Therefore, the correct answer is that SQL Server automatically creates an index on primary keys only.

A dynamic cursor is able to reflect any changes made to the database table even after the result set has been returned. This means that if any modifications are made to the table, such as inserting, updating, or deleting rows, the dynamic cursor will still show the updated data. This is in contrast to a static cursor, which only shows the data as it was when the result set was initially returned. A dynamic cursor provides real-time access to the database table's data, making it a more flexible and responsive option.

Computed columns in a database can have XML instances as both a source and a type. This means that the values of the computed column can be derived from XML data, and the computed column itself can also be defined as an XML type. This allows for the integration of XML data into the computed column, providing flexibility and versatility in working with XML instances within the database.

The correct answer is "CREATE SEQUENCE SE START WITH 30 INCREMENT BY 20". This statement creates a sequence named SE with a starting value of 30 and increments of 20.

Non-clustered indexes are ideal for all of the listed options.

- Queries that do not return large result sets benefit from non-clustered indexes as they can improve the query performance by providing a quick lookup to the data.
- Columns that are frequently used in the WHERE clause for exact matches can be efficiently searched using non-clustered indexes.
- Columns with many distinct values can benefit from non-clustered indexes as they allow for faster searching and retrieval of specific values.

Therefore, defining non-clustered indexes is ideal for all of these scenarios.

The correct answer is "linked_server_name.database.owner.table". In a distributed query, the fully qualified naming convention includes the linked server name, followed by the database name, owner (schema) name, and table name. This ensures that the query is executed on the specific server, database, and table specified.

The Truncate table statement is used to delete all rows in a table without having the action logged. Unlike the Drop table statement, which permanently removes the table and its data, the Truncate table statement only removes the data within the table, making it a more efficient way to delete all rows without logging each individual deletion. The Delete table statement is not a valid SQL statement.

Both the SELECT statement and the stored procedure sp_process will execute successfully. The SELECT statement will retrieve all the data from the employees table, and the stored procedure sp_process will be executed without any issues.

When a transaction has a shared lock on a row or page, it means that it is only reading the data and not modifying it. However, if the transaction wants to modify the data on that same row or page, it can acquire an update lock on the resource. This allows the transaction to transition from a shared lock (read-only) to an update lock (read and write). Therefore, the statement is true.

The correct answer is "All of the listed options". This means that all of the statements mentioned in the options are limitations of INSTEAD of triggers. INSTEAD of triggers do not support recursion, meaning they cannot call themselves within their own execution. Additionally, only one INSTEAD of trigger can be defined for each action on a given table, and a table cannot have an INSTEAD of trigger and a FOREIGN KEY constraint with CASCADE defined for the same action. Therefore, all of these options are limitations of INSTEAD of triggers.

A nonclustered index is a type of index where the bottom level does not contain actual data, but instead contains pointers to the data. This means that the index structure is separate from the actual data, allowing for faster retrieval of specific data based on the indexed columns.

A User Defined Type can be used to address the requirement of having a specific behavior for the ERP_NUM field in the Database ERP. User Defined Types allow for the creation of custom data types in SQL Server that can have specific properties and behaviors. By creating a User Defined Type for the ERP_NUM field, we can define its specific behavior and use it in multiple places within the database. This provides a way to ensure consistency and enforce specific rules for the ERP_NUM field throughout the database.

In a clustered index, the data is physically stored in the same order as the index. This means that the rows of the table are organized based on the key values of the clustered index. This type of index is efficient for retrieving data in the order specified by the index, as it eliminates the need for additional sorting. It is commonly used on columns that are frequently searched or sorted.

A filtered index is a type of non-clustered index that is optimized to improve query performance by including only a subset of rows that meet a specific filter criteria. This allows for more efficient storage and retrieval of data, especially when dealing with large tables. Therefore, the given statement that a filtered index is an optimized non-clustered index is true.

The databases "master" and "msdb" are created on installing SQL server. The "master" database contains system-level information for the SQL server instance, while the "msdb" database is used by SQL Server Agent for scheduling jobs, managing alerts, and storing backup and restore information. Therefore, the correct answer is "both a and b above".

not-available-via-ai

There can only be one isolation level active for a user at any point in time.

not-available-via-ai

Nonclustered indexes are not necessarily faster than clustered indexes for retrieval. The speed of retrieval depends on various factors such as the size of the index, the number of rows in the table, and the specific query being executed. In some cases, a well-designed clustered index can provide faster retrieval times compared to a nonclustered index. Therefore, the statement that nonclustered indexes are always faster than clustered indexes for retrieval is false.

Both of the two methods mentioned will work for creating and modifying tables using SQL Server. One can write SQL code using either CREATE or ALTER SQL statements to create or modify tables. Additionally, one can also use the graphical facilities of SQL Server Enterprise Manager to perform these tasks.

The RAW mode of FOR XML in SQL does not return an in-line XSD schema when the XMLSCHEMA option is used. Additionally, each row in the query result is transformed into an XML element. Therefore, both options a and c are false for RAW mode of FOR XML.

Using Dynamic SQL in stored procedures allows for the flexibility to generate and execute SQL statements at runtime. This means that instead of having separate stored procedures for each query condition, a single stored procedure can be used with dynamic SQL to handle multiple conditions. This approach reduces the number of stored procedures needed, making maintenance easier as changes can be made in a single location. Additionally, it allows for more efficient code reuse and can improve performance by minimizing the need for recompilation.

DBCC SHOW_STATISTICS is the correct answer because it is a command used to display statistical information about the distribution of values in an index or column of a table. This includes information about the last time the index was updated or rebuilt.

The global variable @@TRANCOUNT can be used to determine if a transaction is still open. This variable keeps track of the number of active transactions in the current session. If the value of @@TRANCOUNT is greater than 0, it means that there is an open transaction.

Data Transformation Services is a set of graphical tools that allows you to transfer data between disparate sources into one or more destinations. It provides the capability to extract data from various sources, transform it according to the desired format or structure, and load it into the desired destination. This process helps in integrating and consolidating data from different sources, making it easier to analyze and use for decision-making purposes.

A two-phase commit, consisting of prepare and commit, is used for distributed transactions. Distributed transactions involve multiple databases or systems, where updates need to be synchronized and committed across all the participating systems. The two-phase commit protocol ensures that all the participating systems agree to commit the transaction before it is finalized, and if any system fails to agree, the transaction can be rolled back to maintain consistency across all the systems involved.

Cursors are not faster than set-oriented processing, as set-oriented processing is optimized for processing data in bulk. Cursors are used when processing data row by row is necessary. However, Statement 2 is true because cursors can be used to minimize locking contention for updates and deletions of a large number of rows in a table. This is because cursors allow for more granular control over the locking of individual rows.

Remote BLOB storage is a client-side API which reduces the application building complexity. This means that the API is designed to be used on the client-side of an application, rather than on the server-side. By using this API, developers can offload the storage and retrieval of large binary objects (BLOBs) to a remote location, reducing the complexity of their application code and improving performance.