SQL Complete Test 2008

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By MUKESH443
M
MUKESH443
Community Contributor
Quizzes Created: 2 | Total Attempts: 688
| Attempts: 289
SettingsSettings
Please wait...
  • 1/73 Questions

     How many Primary key constraints can be included in a table definition?    

    • One
    • Two
Please wait...
About This Quiz

This advanced SQL quiz, titled 'sql complete test 2008', evaluates knowledge on XML instances, catalog views, stored procedures, cursors, and triggers. It's designed to assess understanding of complex SQL features and their practical applications.

SQL Quizzes & Trivia

Quiz Preview

  • 2. 

    READ COMMITTED is the default isolation level.

    • True

    • False

    Correct Answer
    A. True
    Explanation
    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.

    Rate this question:

  • 3. 

    By default, SQL Server will create one data file and one log file for each newly created database.

    • True

    • False

    Correct Answer
    A. True
    Explanation
    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.

    Rate this question:

  • 4. 

    What is the default "SORT" order for a SQL?      

    • Ascending

    • Descending

    Correct Answer
    A. Ascending
    Explanation
    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.

    Rate this question:

  • 5. 

    Which of the following below are Compound Operators in sql server 2008          

    • +=

    • %=

    • *=

    • All of above

    Correct Answer
    A. All of above
    Explanation
    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.

    Rate this question:

  • 6. 

    Can a clustered index also be a unique index?      

    • Yes

    • No

    Correct Answer
    A. Yes
    Explanation
    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.

    Rate this question:

  • 7. 

    The SQL Server Agent is a service that lets you configure scheduled tasks and system alerts    

    • True

    • False

    Correct Answer
    A. True
    Explanation
    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.

    Rate this question:

  • 8. 

      Difference between UNION and UNION ALL.        

    • Both are similar

    • UNION ALL is no longer supported in sql server 2000 onward

    • UNION returns only unique records from both tables. UNION ALL returns all records from both tables

    Correct Answer
    A. UNION returns only unique records from both tables. UNION ALL returns all records from both tables
    Explanation
    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.

    Rate this question:

  • 9. 

    SQL Server supports three recovery modes: simple, full and Bulk.

    • True

    • False

    Correct Answer
    A. True
    Explanation
    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

    Rate this question:

  • 10. 

    Which statement is used to define a cursor?          

    • OPEN

    • FETCH

    • DECLARE CURSOR

    • @@FETCH_STATUS

    Correct Answer
    A. DECLARE CURSOR
    Explanation
    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.

    Rate this question:

  • 11. 

    Problems occurs if we don’t implement proper locking strategy            

    • Dirty reads

    • Phantom reads

    • Lost updates

    • Unrepeatable reads

    • ALL of the above

    Correct Answer
    A. ALL of the above
    Explanation
    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.

    Rate this question:

  • 12. 

    The SQL Query Analyzer is the main interface for running SQL queries against your database    

    • True

    • False

    Correct Answer
    A. True
    Explanation
    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.

    Rate this question:

  • 13. 

    With SQL Server, parameters and variables in T-SQL start with the "%" sign.

    • True

    • False

    Correct Answer
    A. False
    Explanation
    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.

    Rate this question:

  • 14. 

    Default is a type of constraint although it does not really enforce anything    

    • Yes

    • No

    Correct Answer
    A. Yes
    Explanation
    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.

    Rate this question:

  • 15. 

    The INSTEAD of trigger can be applied to a view and triggered based on the modifications to the view. State True or False.

    • True

    • False

    Correct Answer
    A. True
    Explanation
    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.

    Rate this question:

  • 16. 

    Which type of integrity preserves the defined relationship between tables when records are entered or deleted?          

    • Entity integrity

    • Domain integrity

    • Referential integrity

    • User-defined integrity

    Correct Answer
    A. Referential integrity
    Explanation
    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.

    Rate this question:

  • 17. 

    How inserting data through stored procedure do reduces network traffic and increase database performance?        

    • Stored procedure can accept parameter

    • Permission check is not required

    • The execution plan is stored in the cache after it was executed the first time.

    Correct Answer
    A. The execution plan is stored in the cache after it was executed the first time.
    Explanation
    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.

    Rate this question:

  • 18. 

    Capabilities of RAISERROR        

    • A. It can be logged in the error log

    • B. It can print a message to the application

    • C. It can assign an error number, state and severity

    • A,b and c

    Correct Answer
    A. A,b and c
    Explanation
    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.

    Rate this question:

  • 19. 

    Which of the following statements is/are true for SQL server?            

    • SQL Server has Logical Architecture and Physical Architecture.

    • SQL Server is a DBMS, a complete database.

    • SQL Server is compatible with structured query language and has rich support for XML

    • Both a and c

    Correct Answer
    A. Both a and c
    Explanation
    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.

    Rate this question:

  • 20. 

    By default sql server has ___________ isolation level        

    • READ COMMITTED

    • READ UNCOMMITTED

    • SERIALIZABLE

    • REPEATABLE READ

    Correct Answer
    A. READ COMMITTED
    Explanation
    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.

    Rate this question:

  • 21. 

    Which of the following commands below are used to run a stored procedure?          

    • EXEC MyStoredProcedure

    • EXECUTE MyStoredProcedure

    • RUN MyStoredProcedure

    • Both a and b above

    Correct Answer
    A. Both a and b above
    Explanation
    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".

    Rate this question:

  • 22. 

     Which of the following is a perfect match with reference to an Index?

    • Smallest unit of data storage

    • Automatically created at the time of creating a table

    • Allows an application program to find specific data without scanning through the entire table

    • Contains information about modifications carried out in the database

    Correct Answer
    A. Allows an application program to find specific data without scanning through the entire table
    Explanation
    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.

    Rate this question:

  • 23. 

    SQL Server automatically creates an index on:

    • Primary keys only.

    • Foreign key only.

    • Both primary and foreign keys.

    • SQL Server never automatically creates an index.

    Correct Answer
    A. Primary keys only.
    Explanation
    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.

    Rate this question:

  • 24. 

    Cursor that reflects the changes made to the database table even after the result set is returned          

    • Static

    • Dynamic

    • FORWARD_ONLY

    • Keyset

    Correct Answer
    A. Dynamic
    Explanation
    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.

    Rate this question:

  • 25. 

    How do XML instances work with respect to a computed column?

    • A computed columns can have XML instances as a source as well as a type

    • Computed columns can have XML instances only as a source

    • Computed columns can have a XML instancesonly as a type.

    • Computed columns cannot have XML instances

    Correct Answer
    A. A computed columns can have XML instances as a source as well as a type
    Explanation
    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.

    Rate this question:

  • 26. 

    Which of the following SQL statement create a sequence SE with starting value as 30 and in increments of 20?

    • GENERATE SEQUENCE SE START WITH 30 ADD BY 20

    • CREATE SEQUENCE SE START WITH 30 ADD BY 20

    • CREATE SEQUENCE SE START WITH 30 INCREMENT BY 20

    • GENERATE SEQUENCE SE INITIATE WITH 30 INCREMENT BY 20

    Correct Answer
    A. CREATE SEQUENCE SE START WITH 30 INCREMENT BY 20
    Explanation
    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.

    Rate this question:

  • 27. 

     For which of the following it is ideal to define non-clustered indexes?

    • Queries that do not return large result sets

    • Columns that are frequently used in the WHERE clause that return exact matches

    • Coumns that have many distinct values

    • All of the listed options

    Correct Answer
    A. All of the listed options
    Explanation
    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.

    Rate this question:

  • 28. 

     You are writing a distributed query to query data from another SQL Server database.  What is the fully qualified naming convention?

    • Linked_server_name.database.owner.table

    • Linked_server_name.database.table

    • Linked_server_name…table

    • Database.table

    Correct Answer
    A. Linked_server_name.database.owner.table
    Explanation
    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.

    Rate this question:

  • 29. 

    Which statement is used to delete all rows in a table without having the action logged?        

    • Drop table statement

    • Delete table statement

    • Truncate table statement

    Correct Answer
    A. Truncate table statement
    Explanation
    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.

    Rate this question:

  • 30. 

    Consider a table 'employees' and a stored procedure 'sp_process' The following statements are executed: SELECT * from employees EXEC sp_process What is the outcome of the above execution?

    • The SELECT statement does not execute but stored procedure sp_process executes

    • The SELECT statement executes but stored procedure sp_process does not execute

    • Both SELECT and stored procedure sp_process fail to execute

    • Both SELECT and stored procedure sp_process execute successfully

    Correct Answer
    A. Both SELECT and stored procedure sp_process execute successfully
    Explanation
    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.

    Rate this question:

  • 31. 

    A transaction attempting to modify data on a row or page on which a shared lock is placed can acquire an update lock on the resource. State True or False.

    • True

    • False

    Correct Answer
    A. True
    Explanation
    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.

    Rate this question:

  • 32. 

    Which of the following is limitation of INSTEAD of trigger?

    • INSTEAD trigger does not support recursion

    • Only one INSTEAD of trigger can be defined for each action on a given table

    • A table cannot have an INSTEAD of trigger and FOREIGN KEY constraint with CASCADE defined for the same action

    • All of the listed options

    Correct Answer
    A. All of the listed options
    Explanation
    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.

    Rate this question:

  • 33. 

    With this type of index, the bottom level of an index does not contain data, but rather it contains pointers to data.

    • Nonclustered

    • Clustered

    • Primary

    • Secondary

    Correct Answer
    A. Nonclustered
    Explanation
    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.

    Rate this question:

  • 34. 

     Assume that you have a Database ERP in which there is a field called ERP_NUM which is used in several places.  The column has a specific behavior which is different from the traditional SQL server data types.         Which of the following feature we can use to address the above requirement?

    • View

    • Temp table

    • User Defined Type

    • Extension

    Correct Answer
    A. User Defined Type
    Explanation
    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.

    Rate this question:

  • 35. 

    With this type of index, the data are stored in the bottom level of the index and in the same order as that index.

    • Nonclustered

    • Clustered

    • Primary

    • Secondary

    Correct Answer
    A. Clustered
    Explanation
    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.

    Rate this question:

  • 36. 

    A filtered index is an optimized non-clustered index    

    • False

    • True

    Correct Answer
    A. True
    Explanation
    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.

    Rate this question:

  • 37. 

    Which of the following databases are created on installing SQL server?       -

    • Master

    • Msdb

    • Pub

    • Both a and b above

    Correct Answer
    A. Both a and b above
    Explanation
    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".

    Rate this question:

  • 38. 

    Choose the incorrect option about the sql server index          

    • Two types of indexes - clustered indexes and non-clustered indexes

    • Both types use B-TREE for searching data

    • Only one clustered index on a table

    • More than one clustered index on a table

    Correct Answer
    A. More than one clustered index on a table
  • 39. 

    How many isolation levels can be active for a user at any point of time?

    • 1

    • 2 2

    • Unlimited

    • 1024

    • None of the listed options

    Correct Answer
    A. 1
    Explanation
    There can only be one isolation level active for a user at any point in time.

    Rate this question:

  • 40. 

    Which of the below is the correct syntax for a VIEW?        

    • Both b and d below

    • Create view viewname as insert

    • Create view viewname as update

    • Create view viewname as select

    Correct Answer
    A. Create view viewname as select
  • 41. 

    Nonclustered indexes are faster than clustered indexes for retrieval.

    • True

    • False

    Correct Answer
    A. False
    Explanation
    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.

    Rate this question:

  • 42. 

    Which of the following is a way to create and modify tables using SQL Server?

    • Write SQL code using either CREATE or ALTER SQL statements only.

    • Use the graphical facilities of SQL Server Enterprise Manager only.

    • Both of the two methods above will work.

    • Neither of the two methods above will work.

    Correct Answer
    A. Both of the two methods above will work.
    Explanation
    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.

    Rate this question:

  • 43. 

    Which of the following is/are false for RAW mode of FOR XML?   .      

    • XMLSCHEMA option does not returns an in-line XSD schema

    • BINARY BASE32 returns the binary data in base32-encoded format.

    • Each row in the query result is transformed into an XML element

    • Both a and c above

    Correct Answer
    A. Both a and c above
    Explanation
    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.

    Rate this question:

  • 44. 

    . Consider the following scenario: A company ABC has many stored procedures each one querying customer database using multiple conditions. The DBA thinks that having too many stored procedures is bad for maintenance and recommends to reduce the number of procs. Which of the following will be a solution to the above recommendation?

    • Use Dynamic SQL in stored procedures

    • Use Nested stored procedures

    • Use extended stored procedures

    • Drop the stored procedures that are not in use

    Correct Answer
    A. Use Dynamic SQL in stored procedures
    Explanation
    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.

    Rate this question:

  • 45. 

    Which of the following DBCC command is used to see when was the last time the index rebuild?          

    • DBCC SHOW_STATISTICS

    • DBCC SHOWCONFIG

    • DBCC DBREINDEX

    Correct Answer
    A. DBCC SHOW_STATISTICS
    Explanation
    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.

    Rate this question:

  • 46. 

    Which global variables can be used to determine if a transaction is still open?        

    • @@NESTLEVEL

    • @@FETCH_STATUS

    • @@TRANCOUNT

    • @@CONNECTIONS

    Correct Answer
    A. @@TRANCOUNT
    Explanation
    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.

    Rate this question:

  • 47. 

    ____________ is a set of graphical tools that allows you to transfer data between disparate sources into one or more destinations.          

    • Data Transfer Services

    • Data Transformation Services

    • Data Disperse Services

    • Data transform Services

    Correct Answer
    A. Data Transformation Services
    Explanation
    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.

    Rate this question:

  • 48. 

    For which of the following is a  two-phase commit, consisting of prepare and commit used?

    • Transactions that might require a rollback

    • Transactions updating multiple tables

    • Distributed transactions

    • Transactions requiring user confirmation of update

    Correct Answer
    A. Distributed transactions
    Explanation
    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.

    Rate this question:

  • 49. 

    Consider the following statements with respect to cursors: Statement 1 :Cursor processing is faster than set-oriented processing and  causes locks to be held for shorter period of time. Statement 2: Cursors can be used to minimize locking contention for updates and deletions of a large number of rows in a table. Which of the following is applicable for above?

    • Statement 1 is true, 2 is false

    • Statement 1 is false, 2 is true

    • Both Statements 1 and 2 are true

    • Both Statements 1 and 2 are false

    Correct Answer
    A. Statement 1 is false, 2 is true
    Explanation
    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.

    Rate this question:

Quiz Review Timeline (Updated): Mar 17, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Mar 17, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Jan 16, 2012
    Quiz Created by
    MUKESH443
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.