SQL Complete Test 2008
Final test for sql server 2008. . . . All the best. . . .
Questions and Answers
- 1.
How do XML instances work with respect to a computed column?
- A.
A computed columns can have XML instances as a source as well as a type
- B.
Computed columns can have XML instances only as a source
- C.
Computed columns can have a XML instancesonly as a type.
- D.
Computed columns cannot have XML instances
Correct Answer
A. A computed columns can have XML instances as a source as well as a typeExplanation
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:
-
- 2.
Why the catalogs views are the preferred choice for accesing the information in system catalogs? (choose 3)
- A.
Catalog view names as well as the names of their columns are discriptive.
- B.
The metadata information is presented in a format that is dependent on system tables . hence is in a fixed format which is easy to understand.
- C.
The metadata information is presented in a format that is independent of any catalog table implementation.
- D.
They provide the most direct way to obtain , transform & represent customized forms of the catalog metadata
- E.
They provide access to all the metadata.
Correct Answer(s)
B. The metadata information is presented in a format that is dependent on system tables . hence is in a fixed format which is easy to understand.
D. They provide the most direct way to obtain , transform & represent customized forms of the catalog metadata
E. They provide access to all the metadata.Explanation
Catalog views are the preferred choice for accessing information in system catalogs because they have descriptive names for both the views and their columns. Additionally, the metadata information is presented in a fixed format that is easy to understand as it is dependent on system tables. Catalog views also provide the most direct way to obtain, transform, and represent customized forms of the catalog metadata. Lastly, they provide access to all the metadata, making them a comprehensive option for accessing information in system catalogs.Rate this question:
-
- 3.
Consider the following scenario: The creator of a stored procedure also owns the tables that it references. Statement 1: An user can execute the stored procedure but cannot access the referenced table unless the creator grants access to the table Statement 2: An user can execute the stored procedure and inherits the rights on the referenced table from the owner within the context of the stored procedure Which of the following is applicable for the above?
- A.
Statement 1 is true, 2 is false
- B.
Statement 1 is false, 2 is true
- C.
Both Statements 1 and 2 are true
- D.
Both Statements 1 and 2 are false
Correct Answer
B. Statement 1 is false, 2 is trueExplanation
An user can execute the stored procedure and inherits the rights on the referenced table from the owner within the context of the stored procedure. This means that the user does not need explicit access granted to the table, as they will have the necessary rights to access it while executing the stored procedure. Therefore, Statement 2 is true. However, Statement 1 is false because the user can access the referenced table without the creator granting access.Rate this question:
-
- 4.
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?
- A.
Statement 1 is true, 2 is false
- B.
Statement 1 is false, 2 is true
- C.
Both Statements 1 and 2 are true
- D.
Both Statements 1 and 2 are false
Correct Answer
B. Statement 1 is false, 2 is trueExplanation
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:
-
- 5.
The INSTEAD of trigger can be applied to a view and triggered based on the modifications to the view. State True or False.
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
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:
-
- 6.
Which of the following is limitation of INSTEAD of trigger?
- A.
INSTEAD trigger does not support recursion
- B.
Only one INSTEAD of trigger can be defined for each action on a given table
- C.
A table cannot have an INSTEAD of trigger and FOREIGN KEY constraint with CASCADE defined for the same action
- D.
All of the listed options
Correct Answer
D. All of the listed optionsExplanation
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:
-
- 7.
For which of the following it is ideal to define non-clustered indexes?
- A.
Queries that do not return large result sets
- B.
Columns that are frequently used in the WHERE clause that return exact matches
- C.
Coumns that have many distinct values
- D.
All of the listed options
Correct Answer
D. All of the listed optionsExplanation
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:
-
- 8.
Referential integrity can be enforced by using DML triggers. Which of the following are other methods by which the same enforcement can be achieved?(Choose 2)
- A.
Using Stored procedures
- B.
Defining indexes
- C.
Defining foreign key constraints
- D.
Defining views
Correct Answer(s)
A. Using Stored procedures
C. Defining foreign key constraintsExplanation
Referential integrity can be enforced by using stored procedures because they allow the database to perform specific actions whenever data is inserted, updated, or deleted. By including logic in the stored procedures, the database can ensure that any changes made to the data maintain the integrity of the relationships between tables.
Defining foreign key constraints is another method to enforce referential integrity. Foreign key constraints ensure that values in a column of one table match the values in a primary key column of another table. This ensures that any changes made to the data maintain the integrity of the relationships between tables.Rate this question:
-
- 9.
Which of the following is a perfect match with reference to an Index?
- A.
Smallest unit of data storage
- B.
Automatically created at the time of creating a table
- C.
Allows an application program to find specific data without scanning through the entire table
- D.
Contains information about modifications carried out in the database
Correct Answer
C. Allows an application program to find specific data without scanning through the entire tableExplanation
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:
-
- 10.
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?
- A.
View
- B.
Temp table
- C.
User Defined Type
- D.
Extension
Correct Answer
C. User Defined TypeExplanation
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:
-
- 11.
Which of the following are correct for indexed views?(Choose 2)
- A.
An indexed view is any view that has a clustered index defined on it
- B.
The indexed view is a logical entity and is not materialized in the database
- C.
You can create only one index on a view
- D.
Indexed views aid in optmizing performance
Correct Answer(s)
A. An indexed view is any view that has a clustered index defined on it
D. Indexed views aid in optmizing performanceExplanation
An indexed view is any view that has a clustered index defined on it. This means that the view has a physical structure that allows for faster data retrieval and improved query performance. Indexed views aid in optimizing performance by precomputing and storing the results of complex queries, reducing the need for expensive join operations and aggregations.Rate this question:
-
- 12.
Which of the following is used to locate specific content and elements within an XML document?
- A.
XPATH
- B.
XML
- C.
XTEMPLETS
- D.
XSD
Correct Answer
B. XML -
- 13.
Which of the following SQL statement create a sequence SE with starting value as 30 and in increments of 20?
- A.
GENERATE SEQUENCE SE START WITH 30 ADD BY 20
- B.
CREATE SEQUENCE SE START WITH 30 ADD BY 20
- C.
CREATE SEQUENCE SE START WITH 30 INCREMENT BY 20
- D.
GENERATE SEQUENCE SE INITIATE WITH 30 INCREMENT BY 20
Correct Answer
C. CREATE SEQUENCE SE START WITH 30 INCREMENT BY 20Explanation
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:
-
- 14.
You are writing a distributed query to query data from another SQL Server database. What is the fully qualified naming convention?
- A.
Linked_server_name.database.owner.table
- B.
Linked_server_name.database.table
- C.
Linked_server_name…table
- D.
Database.table
Correct Answer
A. Linked_server_name.database.owner.tableExplanation
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:
-
- 15.
For which of the following is a two-phase commit, consisting of prepare and commit used?
- A.
Transactions that might require a rollback
- B.
Transactions updating multiple tables
- C.
Distributed transactions
- D.
Transactions requiring user confirmation of update
Correct Answer
C. Distributed transactionsExplanation
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:
-
- 16.
Which of the following is correct for a shared lock?
- A.
It is a lock acquired by a process on the resources that it intends to modify
- B.
It is a lock acquired by a process on a resource prior to modifying it
- C.
It is lock acquired by a process on a resource for the duration of read
- D.
None of the listed options
Correct Answer
C. It is lock acquired by a process on a resource for the duration of readExplanation
A shared lock is acquired by a process on a resource for the duration of read. This means that multiple processes can acquire a shared lock on the same resource simultaneously, allowing them to read the resource concurrently. However, a shared lock does not allow the process to modify the resource.Rate this question:
-
- 17.
A transaction attempting to modify data on a row or page on which a shared lock is placed can acquire an exclusive lock on the resource. State True or False.
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
When a transaction attempts to modify data on a row or page on which a shared lock is placed, it cannot acquire an exclusive lock on the resource. This is because a shared lock allows multiple transactions to read the data simultaneously, but it restricts any transaction from modifying the data until the shared lock is released. Therefore, the statement is false.Rate this question:
-
- 18.
When a SELECT * command is executed on a table, when is the shared lock on the first row released?
- A.
After all the rows are read
- B.
After the first row is read
- C.
After the second row is read
- D.
Only when the transaction is committed or rolled back
Correct Answer
B. After the first row is readExplanation
When a SELECT * command is executed on a table, the shared lock on the first row is released after it is read. This means that the lock is released immediately after the first row is accessed and retrieved by the SELECT statement. The lock is not held for the entire duration of reading all the rows or until the transaction is committed or rolled back.Rate this question:
-
- 19.
Which type of SQL Server cursor concurrency places an update lock on a row when the row is read?
- A.
READ_ONLY
- B.
SCROLL_LOCK
- C.
OPTIMISTIC
- D.
READCOMMITTED
Correct Answer
D. READCOMMITTEDExplanation
The READCOMMITTED concurrency type in SQL Server cursor places an update lock on a row when the row is read. This means that other transactions can still read the row, but they cannot modify it until the lock is released. This ensures that the data being read is consistent and prevents dirty reads.Rate this question:
-
- 20.
Which of the following is a way to create and modify tables using SQL Server?
- A.
Write SQL code using either CREATE or ALTER SQL statements only.
- B.
Use the graphical facilities of SQL Server Enterprise Manager only.
- C.
Both of the two methods above will work.
- D.
Neither of the two methods above will work.
Correct Answer
C. 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:
-
- 21.
With this type of SQL Server cursor concurrency no lock is obtained until the user updates the data.
- A.
READ_ONLY
- B.
SCROLL_LOCK
- C.
OPTIMISTIC
- D.
PESSIMISTIC
Correct Answer
C. OPTIMISTICExplanation
With optimistic concurrency, locks are not obtained until the user updates the data. This means that multiple users can read the same data simultaneously without blocking each other. When a user tries to update the data, the system checks if any other user has made changes since the data was last read. If no changes have been made, the update is allowed. If changes have been made, the update is blocked to prevent conflicts. This approach allows for better performance and scalability in situations where conflicts are rare.Rate this question:
-
- 22.
With this type of index, the data are stored in the bottom level of the index and in the same order as that index.
- A.
Nonclustered
- B.
Clustered
- C.
Primary
- D.
Secondary
Correct Answer
B. ClusteredExplanation
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:
-
- 23.
With this type of index, the bottom level of an index does not contain data, but rather it contains pointers to data.
- A.
Nonclustered
- B.
Clustered
- C.
Primary
- D.
Secondary
Correct Answer
A. NonclusteredExplanation
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:
-
- 24.
SQL Server automatically creates an index on:
- A.
Primary keys only.
- B.
Foreign key only.
- C.
Both primary and foreign keys.
- D.
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:
-
- 25.
Which of the following refers to the SQL Server transaction isolation level which places and holds locks on all rows that are read?
- A.
REPEATABLE READ
- B.
SERIALIZABLE
- C.
READ COMMITTED
- D.
READ UNCOMMITTED
Correct Answer
A. REPEATABLE READExplanation
The REPEATABLE READ isolation level in SQL Server ensures that any rows read during a transaction are locked, preventing other transactions from modifying or deleting them until the current transaction is completed. This guarantees that the data read by the transaction remains consistent throughout the transaction, even if other transactions make changes to the data.Rate this question:
-
- 26.
With SQL Server, parameters and variables in T-SQL start with the "%" sign.
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
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:
-
- 27.
Nonclustered indexes are faster than clustered indexes for retrieval.
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
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:
-
- 28.
READ COMMITTED is the default isolation level.
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
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:
-
- 29.
SQL Server supports three recovery modes: simple, full and Bulk.
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
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 ModelRate this question:
-
- 30.
SQL Server AFTER Triggers may be assigned views.
- A.
True
- B.
False
Correct Answer
B. False -
- 31.
'dbo' stands for database object.
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
The statement "dbo stands for database object" is incorrect. In the context of databases, "dbo" stands for "database owner" and is used to identify the default schema in SQL Server. It does not stand for "database object." Therefore, the correct answer is False.Rate this question:
-
- 32.
By default, SQL Server will create one data file and one log file for each newly created database.
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
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:
-
- 33.
Which statement is used to delete all rows in a table without having the action logged?
- A.
Drop table statement
- B.
Delete table statement
- C.
Truncate table statement
Correct Answer
C. Truncate table statementExplanation
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:
-
- 34.
How many Primary key constraints can be included in a table definition?
- A.
One
- B.
Two
Correct Answer
A. OneExplanation
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.Rate this question:
-
- 35.
Which type of integrity preserves the defined relationship between tables when records are entered or deleted?
- A.
Entity integrity
- B.
Domain integrity
- C.
Referential integrity
- D.
User-defined integrity
Correct Answer
C. Referential integrityExplanation
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:
-
- 36.
Is a composite index key always part of a covering index?
- A.
Yes
- B.
No
Correct Answer
B. NoExplanation
A composite index key is not always part of a covering index. A composite index key is formed by combining multiple columns into a single index key, while a covering index is an index that includes all the columns needed to satisfy a query, eliminating the need to access the actual table data. While a composite index key can be part of a covering index, it is not a requirement. A covering index can include any combination of columns that are needed for efficient query execution.Rate this question:
-
- 37.
Can a clustered index also be a unique index?
- A.
Yes
- B.
No
Correct Answer
A. YesExplanation
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:
-
- 38.
Which of the following DBCC command is used to see when was the last time the index rebuild?
- A.
DBCC SHOW_STATISTICS
- B.
DBCC SHOWCONFIG
- C.
DBCC DBREINDEX
Correct Answer
A. DBCC SHOW_STATISTICSExplanation
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:
-
- 39.
Choose the incorrect option about the sql server index
- A.
Two types of indexes - clustered indexes and non-clustered indexes
- B.
Both types use B-TREE for searching data
- C.
Only one clustered index on a table
- D.
More than one clustered index on a table
Correct Answer
D. More than one clustered index on a table -
- 40.
What is the default "SORT" order for a SQL?
- A.
Ascending
- B.
Descending
Correct Answer
A. AscendingExplanation
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:
-
- 41.
Problems occurs if we don’t implement proper locking strategy
- A.
Dirty reads
- B.
Phantom reads
- C.
Lost updates
- D.
Unrepeatable reads
- E.
ALL of the above
Correct Answer
E. ALL of the aboveExplanation
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:
-
- 42.
By default sql server has ___________ isolation level
- A.
READ COMMITTED
- B.
READ UNCOMMITTED
- C.
SERIALIZABLE
- D.
REPEATABLE READ
Correct Answer
A. READ COMMITTEDExplanation
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:
-
- 43.
Which global variables can be used to determine if a transaction is still open?
- A.
@@NESTLEVEL
- B.
@@FETCH_STATUS
- C.
@@TRANCOUNT
- D.
@@CONNECTIONS
Correct Answer
C. @@TRANCOUNTExplanation
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:
-
- 44.
Capabilities of RAISERROR
- A.
A. It can be logged in the error log
- B.
B. It can print a message to the application
- C.
C. It can assign an error number, state and severity
- D.
A,b and c
Correct Answer
D. A,b and cExplanation
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:
-
- 45.
Cursor that reflects the changes made to the database table even after the result set is returned
- A.
Static
- B.
Dynamic
- C.
FORWARD_ONLY
- D.
Keyset
Correct Answer
B. DynamicExplanation
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:
-
- 46.
Which statement is used to define a cursor?
- A.
OPEN
- B.
FETCH
- C.
DECLARE CURSOR
- D.
@@FETCH_STATUS
Correct Answer
C. DECLARE CURSORExplanation
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:
-
- 47.
How inserting data through stored procedure do reduces network traffic and increase database performance?
- A.
Stored procedure can accept parameter
- B.
Permission check is not required
- C.
The execution plan is stored in the cache after it was executed the first time.
Correct Answer
C. 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:
-
- 48.
Difference between UNION and UNION ALL.
- A.
Both are similar
- B.
UNION ALL is no longer supported in sql server 2000 onward
- C.
UNION returns only unique records from both tables. UNION ALL returns all records from both tables
Correct Answer
C. UNION returns only unique records from both tables. UNION ALL returns all records from both tablesExplanation
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:
-
- 49.
Which of the following is/are false for RAW mode of FOR XML? .
- A.
XMLSCHEMA option does not returns an in-line XSD schema
- B.
BINARY BASE32 returns the binary data in base32-encoded format.
- C.
Each row in the query result is transformed into an XML element
- D.
Both a and c above
Correct Answer
D. Both a and c aboveExplanation
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:
-
- 50.
Which of the following statements is/are true for SQL server?
- A.
SQL Server has Logical Architecture and Physical Architecture.
- B.
SQL Server is a DBMS, a complete database.
- C.
SQL Server is compatible with structured query language and has rich support for XML
- D.
Both a and c
Correct Answer
D. Both a and cExplanation
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:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 17, 2023Quiz Edited by
ProProfs Editorial Team -
Jan 16, 2012Quiz Created by
MUKESH443
Back to top