Explore topic-wise InterviewSolutions in .

This section includes InterviewSolutions, each offering curated multiple-choice questions to sharpen your knowledge and support exam preparation. Choose a topic below to get started.

1.

___________ lets you affinitize a resource pool to one or more schedulers or NUMA nodes for greater isolation of CPU resources.(a) MAX_IOPS_PER_VOLUME(b) AFFINITY(c) MAX_CPU_PERCENT(d) MAX_MEMORY_PERCENTI had been asked this question in unit test.I would like to ask this question from Resource Governor in portion Performance Tuning and Optimization of SQL Server

Answer»

Right answer is (b) AFFINITY

To ELABORATE: By using the AFFINITY option the SALES and Marketing WORKLOADS can be SCHEDULED on different CPUs.
Discussion
2.

____________is a hard cap limit on the CPU bandwidth for all requests in the resource pool.(a) MIN_CPU_PERCENT(b) CAP_CPU_PERCENT(c) MAX_CPU_PERCENT(d) MAX_MEMORY_PERCENTThe question was posed to me in class test.My question is taken from Resource Governor in chapter Performance Tuning and Optimization of SQL Server

Answer»

The CORRECT option is (b) CAP_CPU_PERCENT

Best EXPLANATION: WORKLOADS associated with the pool can USE CPU CAPACITY above the value of MAX_CPU_PERCENT if it is available, but not above the value of CAP_CPU_PERCENT.
Discussion
3.

Which of the following query disables the resource governor?(a) ALTER RESOURCE GOVERNOR ENABLE CONFIGURE;(b) ALTERGOVERNOR DISABLE;(c) ALTER RESOURCE GOVERNOR DISABLE;(d) None of the mentionedThe question was posed to me during an online interview.I'd like to ask this question from Resource Governor topic in section Performance Tuning and Optimization of SQL Server

Answer»

Correct OPTION is (C) ALTER RESOURCE GOVERNOR DISABLE;

The best I can explain: You can disable the Resource Governor by using either SQL Server MANAGEMENT STUDIO or Transact-SQL.
Discussion
4.

Which of the following query enables the Resource Governor?(a) ALTER RESOURCE GOVERNOR CONFIGURE;(b) ALTER RESOURCE RECONFIGURE;(c) ALTER RESOURCE GOVERNOR RECONFIGURE;(d) None of the mentionedThe question was posed to me by my school teacher while I was bunking the class.Question is taken from Resource Governor topic in division Performance Tuning and Optimization of SQL Server

Answer» CORRECT ANSWER is (c) ALTER RESOURCE GOVERNOR RECONFIGURE;

EXPLANATION: The Resource Governor is turned off by DEFAULT.
Discussion
5.

Point out the wrong statement.(a) Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server is installed(b) A resource group serves as a container for session requests that have similar classification criteria(c) Resource Governor also supports user-defined workload groups(d) Resource Governor does not impose any controls on a dedicated administrator connection (DAC)I got this question in a job interview.This question is from Resource Governor topic in portion Performance Tuning and Optimization of SQL Server

Answer»

Right ANSWER is (b) A resource group serves as a container for session REQUESTS that have similar classification criteria

Easy explanation: A workload allows for aggregate MONITORING of the SESSIONS, and defines POLICIES for the sessions.
Discussion
6.

Enabling the Resource Governor requires _________ permission.(a) CONTROL SERVER(b) CONTROL(c) SERVER(d) SELECTThe question was asked during an interview for a job.My question is based upon Resource Governor topic in division Performance Tuning and Optimization of SQL Server

Answer»

Correct answer is (a) CONTROL SERVER

Easiest explanation: The classifier FUNCTION is run for NEW connections so that their workloads can be ASSIGNED to WORKLOAD groups.
Discussion
7.

You cannot use the ____________ statement to enable Resource Governor when in a user transaction.(a) ALTER RESOURCE GOVERNOR(b) ALTER GOVERNOR(c) ALTER RESOURCE(d) All of the mentionedI got this question by my school teacher while I was bunking the class.My enquiry is from Resource Governor topic in portion Performance Tuning and Optimization of SQL Server

Answer»

The correct choice is (a) ALTER RESOURCE GOVERNOR

To elaborate: You can enable the Resource Governor by using EITHER SQL Server MANAGEMENT STUDIO or Transact-SQL.
Discussion
8.

Point out the correct statement.(a) You can set IO thresholds on the internal resource pool(b) There is workload monitoring or workload management between SQL Server instances(c) A resource group represents the physical resources of the server(d) Resource management is limited to the SQL Server Database EngineThe question was posed to me in an online quiz.My doubt is from Resource Governor topic in division Performance Tuning and Optimization of SQL Server

Answer»

The CORRECT choice is (d) Resource management is limited to the SQL Server Database Engine

To explain I WOULD say: Resource GOVERNOR can not be USED for Analysis Services, INTEGRATION Services, and Reporting Services.
Discussion
9.

Resource Governor is a feature that enables you to specify limits on the amount of ____________(a) CPU(b) Physical IO(c) Memory(d) None of the mentionedThe question was posed to me in an interview.This interesting question is from Resource Governor topic in chapter Performance Tuning and Optimization of SQL Server

Answer»

The correct OPTION is (d) None of the mentioned

To EXPLAIN I would say: SQL Server Resource Governor is a feature than you can use to MANAGE SQL Server WORKLOAD and system resource consumption.
Discussion
10.

Which of the following partitioned attribute can appear in the following physical and logical operators?(a) Index Seek(b) Drop(c) Alter(d) None of the mentionedI had been asked this question in homework.My question is from Partitioning topic in portion Performance Tuning and Optimization of SQL Server

Answer» RIGHT choice is (a) INDEX Seek

For explanation I WOULD say: When an operator such as an Index Seek is executed on a PARTITIONED table or index, the Partitioned attribute appears in the compile-time and run-time plan and is SET to True.
Discussion
11.

SQL Server 2008 provides enhanced partitioning information for __________ execution plans.(a) compile-time(b) run time(c) both compile-time and run-time(d) all of the mentionedThe question was asked in final exam.The doubt is from Partitioning in section Performance Tuning and Optimization of SQL Server

Answer»

The correct answer is (c) both compile-time and run-time

The best explanation: Execution plans now PROVIDE an optional Partitioned ATTRIBUTE that indicates that an operator, such as a SEEK, scan, insert, UPDATE, merge, or delete, is PERFORMED on a partitioned table.
Discussion
12.

With a larger number of partitions, ______ commands could take longer to execute as the number of partitions increases.(a) DML(b) DDL(c) DBCC(d) TCLI got this question in an international level competition.The question is from Partitioning in division Performance Tuning and Optimization of SQL Server

Answer»

Correct OPTION is (c) DBCC

To EXPLAIN: The Transact-SQL PROGRAMMING language provides DBCC statements that act as Database Console Commands for SQL SERVER.
Discussion
13.

Point out the wrong statement.(a) Computed columns that participate in a partition function must be explicitly marked PERSISTED(b) The new, higher limit of 15,000 partitions affects memory, partitioned index operations, DBCC commands, and queries(c) Partitioning column is the process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query(d) All of the mentionedThis question was posed to me during an online exam.This interesting question is from Partitioning topic in portion Performance Tuning and Optimization of SQL Server

Answer»

The correct choice is (c) Partitioning column is the process by which the QUERY optimizer ACCESSES only the relevant PARTITIONS to satisfy the filter criteria of the query

For explanation I WOULD SAY: Partition elimination is the process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query.
Discussion
14.

At least _______ of RAM is recommended if a large number of partitions are in use.(a) 16 GB(b) 4 GB(c) 8 GB(d) 2 GBThis question was addressed to me in a job interview.This intriguing question originated from Partitioning in division Performance Tuning and Optimization of SQL Server

Answer»

Correct answer is (a) 16 GB

To explain: SYSTEMS with 16 GB of RAM that run many memory-intensive PROCESSES may run out of memory on OPERATIONS that run on a large number of PARTITIONS.
Discussion
15.

Designing an nonaligned partitioned index can be useful in the following case?(a) The base table has not been partitioned(b) The index key is unique and it does not contain the partitioning column of the table(c) You want the base table to participate in collocated joins with more tables using different join columns(d) All of the mentionedI have been asked this question during an interview.My doubt is from Partitioning topic in portion Performance Tuning and Optimization of SQL Server

Answer»

The CORRECT CHOICE is (d) All of the mentioned

Best explanation: Nonaligned index is an index partitioned INDEPENDENTLY from its CORRESPONDING table.
Discussion
16.

__________ is a database object that defines how the rows of a table or index are mapped to a set of partitions based on the values of certain column.(a) Partition function(b) Partition scheme(c) Partition column(d) All of the mentionedI had been asked this question during an internship interview.I would like to ask this question from Partitioning in section Performance Tuning and Optimization of SQL Server

Answer»

Correct answer is (a) PARTITION function

Best EXPLANATION: Partition function DEFINES the NUMBER of partitions that the table will have and how the BOUNDARIES of the partitions are defined.
Discussion
17.

Point out the correct statement.(a) On x86-based systems, creating a table or index with more than 1000 partitions is possible(b) Compressing large tables or indexes can have manageability and performance benefit(c) Partitioning slow tables or indexes can have the following manageability and performance benefit(d) None of the mentionedI got this question during an interview.I'd like to ask this question from Partitioning in chapter Performance Tuning and Optimization of SQL Server

Answer» CORRECT choice is (a) On x86-based systems, creating a table or index with more than 1000 partitions is possible

The explanation is: Partitioning feature is not supported on x86 SYSTEM.
Discussion
18.

SQL Server 2014 supports up to ________ partitions by default.(a) 20000(b) 2000(c) 1000(d) 15000The question was posed to me during an online exam.Asked question is from Partitioning topic in portion Performance Tuning and Optimization of SQL Server

Answer»

The correct CHOICE is (d) 15000

To EXPLAIN: In VERSIONS earlier than SQL SERVER 2012, the number of partitions was limited to 1,000 by default.
Discussion
19.

sp_estimate_data_compression_savings requires ______ permission on the table.(a) UPDATE(b) SELECT(c) DELETE(d) None of the mentionedThe question was posed to me in an international level competition.This interesting question is from Data Compression in section Performance Tuning and Optimization of SQL Server

Answer»

Correct option is (b) SELECT

Explanation: USE sp_estimate_data_compression_savings to ESTIMATE the savings that can occur when you enable a table or partition for row or PAGE compression.
Discussion
20.

Which of the following compression type is supported by SQL Server 2014?(a) Row(b) Column(c) Both row and column(d) None of the mentionedI had been asked this question by my school teacher while I was bunking the class.Query is from Data Compression in chapter Performance Tuning and Optimization of SQL Server

Answer»

The correct answer is (c) Both row and column

For EXPLANATION: SQL Server 2014 SUPPORTS columnstore and columnstore archival compression for columnstore tables and INDEXES.
Discussion
21.

Point out the correct statement.(a) The details of data compression are subject to change without notice in service packs or subsequent releases(b) Compression is not available for system tables(c) If you specify a list of partitions or a partition that is out of range, an error will be generated(d) All of the mentionedThis question was posed to me in my homework.My enquiry is from Data Compression topic in portion Performance Tuning and Optimization of SQL Server

Answer» CORRECT choice is (d) All of the mentioned

To elaborate: These are considerations made before using ROW and PAGE COMPRESSION.
Discussion
22.

Internal checkpoint is generated in response to which of the following event?(a) A database snapshot is deleted, whether explicitly or internally for DBCC CHECK(b) Bringing a SQL Server failover cluster instance (FCI) offline(c) Bringing a SQL Server failover cluster instance (FCI) online(d) None of the mentionedThis question was posed to me during an interview for a job.This intriguing question comes from Working With Transaction Log in portion Performance Tuning and Optimization of SQL Server

Answer»

Right option is (b) Bringing a SQL Server failover cluster instance (FCI) offline

The explanation is: Internal Checkpoints are generated by various server components to GUARANTEE that disk images match the CURRENT STATE of the log.
Discussion
23.

Which of the following checkpoint has ‘recovery interval’ less than zero?(a) Automatic(b) Direct(c) Indirect(d) All of the mentionedThis question was posed to me during an internship interview.My doubt stems from Working With Transaction Log in section Performance Tuning and Optimization of SQL Server

Answer» CORRECT option is (a) Automatic

The explanation is: Automatic checkpoints whose TARGET recovery interval is SPECIFIED by the USER defined SETTING of the sp_configure recovery interval option.
Discussion
24.

Index page deallocation during a ________ operation is always fully logged.(a) DROP INDEX(b) CREATE INDEX(c) ALTER INDEX(d) None of the mentionedThe question was asked during a job interview.This interesting question is from Working With Transaction Log topic in section Performance Tuning and Optimization of SQL Server

Answer»

The correct answer is (a) DROP INDEX

Explanation: The DBCC DBREINDEX STATEMENT is deprecated so you should avoid using it in NEW APPLICATIONS.
Discussion
25.

________ logging is not supported for memory-optimized tables.(a) Full(b) Minimal(c) Bulk(d) None of the mentionedI got this question in an online quiz.This is a very interesting question from Working With Transaction Log in chapter Performance Tuning and Optimization of SQL Server

Answer»

The correct choice is (B) MINIMAL

To ELABORATE: Minimal logging involves logging only the information that is required to RECOVER the transaction without SUPPORTING point-in-time recovery.
Discussion
26.

Which of the log_reuse_wait_desc value has log_reuse_wait value 0?(a) CHECKPOINT(b) ACTIVE_TRANSACTION(c) NOTHING(d) All of the mentionedI have been asked this question in exam.My doubt stems from Working With Transaction Log in division Performance Tuning and Optimization of SQL Server

Answer»

The CORRECT ANSWER is (c) NOTHING

For explanation: log_reuse_wait value 0 indicates CURRENTLY there are one or more REUSABLE VIRTUAL log files.
Discussion
27.

ACTIVE_TRANSACTION has log_reuse_wait value is _________(a) 1(b) 2(c) 3(d) 4I got this question in an online interview.My doubt stems from Working With Transaction Log topic in portion Performance Tuning and Optimization of SQL Server

Answer»

The correct choice is (d) 4

To EXPLAIN: A long-running transaction might exist at the START of the log backup. In this case, freeing the SPACE might REQUIRE another log backup.
Discussion
28.

Point out the wrong statement.(a) For performance reasons, the Database Engine performs modifications to database pages in memory(b) Log truncation does reduce the size of the physical log file(c) Log truncation is essential to keep the log from filling(d) None of the mentionedI got this question in an online quiz.I'm obligated to ask this question of Working With Transaction Log topic in chapter Performance Tuning and Optimization of SQL Server

Answer»

The correct option is (B) LOG truncation does reduce the size of the physical log FILE

Explanation: To reduce the physical size of a physical log file, you NEED to shrink the log file.
Discussion
29.

Point out the correct statement.(a) The Database Engine supports two types of checkpoints(b) Long-running uncommitted transactions decrease recovery time for all types of checkpoints(c) Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database(d) All of the mentionedI have been asked this question at a job interview.This intriguing question comes from Working With Transaction Log topic in section Performance Tuning and Optimization of SQL Server

Answer»

The correct choice is (c) Log truncation DELETES inactive virtual log files from the LOGICAL transaction log of a SQL Server database

The best I can explain: Log truncation FREES space in the log file for reuse by the transaction log.
Discussion
30.

The transaction log supports which of the following operations?(a) Recovery of all incomplete transactions when SQL Server is started(b) Recovery of individual transactions(c) Supporting transactional replication(d) All of the mentionedThis question was posed to me by my college professor while I was bunking the class.The doubt is from Working With Transaction Log topic in division Performance Tuning and Optimization of SQL Server

Answer»

Correct option is (b) Recovery of individual transactions

Easy explanation: The transaction LOG also SUPPORTS HIGH availability and DISASTER recovery solutions.
Discussion
31.

Which of the following occurs when two connections need access to same piece of data concurrently and the meanwhile another is blocked because at a particular time, only one connection can have access?(a) Lock(b) Block(c) Deadlock(d) Exclusive lockI got this question in a job interview.Query is from Blocking in chapter Performance Tuning and Optimization of SQL Server

Answer»

The CORRECT CHOICE is (B) Block

Best explanation: Lock is done by database when any CONNECTION access the same piece of data CONCURRENTLY and another is demanding for it.
Discussion
32.

Point out the wrong statement.(a) SQL Server lock monitor has a performance monitor that periodically checks the locks(b) A deadlock graph shows us the sessions and resources that were involved in a deadlock(c) Prior to SQL Server 2008, if a deadlock occurred in SQL Server, we’d have to enable trace flag(d) None of the mentionedI had been asked this question in semester exam.The origin of the question is Blocking in section Performance Tuning and Optimization of SQL Server

Answer»

Right answer is (a) SQL Server lock monitor has a PERFORMANCE monitor that PERIODICALLY CHECKS the locks

Best explanation: SQL Server lock monitor has a deadlock DETECTOR that periodically checks the locks to see if there are any circular LOCKING chains.
Discussion
33.

An XML deadlock graph has ______ main sections.(a) 2(b) 3(c) 4(d) 5This question was posed to me in exam.Asked question is from Blocking in chapter Performance Tuning and Optimization of SQL Server

Answer» RIGHT answer is (a) 2

The best I can explain: An XML deadlock GRAPH has TWO main sections: Processes SECTION and Resources section.
Discussion
34.

Which of the following lock is incompatible with all lock modes?(a) Intent exclusive (IX)(b) Schema stability(c) Schema modification(d) Bulk updateThe question was asked in an internship interview.My question comes from Locking in portion Performance Tuning and Optimization of SQL Server

Answer»

The CORRECT option is (C) Schema modification

Easiest explanation: SQL Server ALLOWS only ONE Sch-M LOCK on an object at a time.
Discussion
35.

Which of the following locking hint is similar to NOLOCK?(a) PAGLOCK(b) READCOMMITTED(c) READUNCOMMITTED(d) SERIALIZABLEThis question was posed to me by my school teacher while I was bunking the class.The above asked question is from Locking in division Performance Tuning and Optimization of SQL Server

Answer» CORRECT answer is (C) READUNCOMMITTED

Explanation: Do not ISSUE shared locks and do not HONOR exclusive locks.
Discussion
36.

__________ locks allow processes to bulk copy data concurrently into the same table.(a) Bulk update(b) Bulk import(c) Bulk export(d) Bulk copyI have been asked this question in an online quiz.Question is from Locking in chapter Performance Tuning and Optimization of SQL Server

Answer» CORRECT answer is (a) BULK UPDATE

For explanation I would say: Bulk update (BU) locks are used when bulk copying data into a table and either the TABLOCK hint is SPECIFIED.
Discussion
37.

Which of the following mode indicates the intention of the transaction to read all of the resources lower in the hierarchy?(a) IS(b) IX(c) SIX(d) All of the mentionedI have been asked this question by my college professor while I was bunking the class.This question is from Locking in portion Performance Tuning and Optimization of SQL Server

Answer» CORRECT answer is (c) SIX

Explanation: SIX STANDS for Shared with intent exclusive and can modify some (but not all) resources LOWER in the hierarchy by placing IX LOCKS on those INDIVIDUAL resources.
Discussion
38.

Point out the wrong statement.(a) Schema modification (Sch-M) locks are used when a table data definition language operation is being performed(b) By default, SQL Server transactions times out after a certain time(c) Disallowing a locking level can affect concurrency adversely(d) Table-level locking hints can be used when a finer control of the types of locks acquired on an object is requiredI have been asked this question in an interview for job.My question is from Locking topic in chapter Performance Tuning and Optimization of SQL Server

Answer»

Right choice is (b) By DEFAULT, SQL Server transactions times out after a certain time

The EXPLANATION: SQL Server transactions do not time out (unless LOCK_TIMEOUT is SET).
Discussion
39.

______________ allow concurrent transactions to read (SELECT) a resource.(a) Update locks(b) Shared locks(c) Exclusive Locks(d) All of the mentionedThe question was posed to me in quiz.I want to ask this question from Locking in division Performance Tuning and Optimization of SQL Server

Answer»

Correct option is (b) SHARED LOCKS

To explain: Shared (S) locks on a resource are RELEASED as SOON as the data has been read.
Discussion
40.

Which is the most restrictive isolation level in transaction?(a) Repeatable Read(b) Snapshot(c) Read Committed(d) SERIALIZABLEI had been asked this question in examination.I'd like to ask this question from Locking topic in chapter Performance Tuning and Optimization of SQL Server

Answer» CORRECT answer is (d) SERIALIZABLE

The best explanation: When SERIALIZABLE isolation level is set – a range LOCK is placed on the data set, preventing other users from updating or INSERTING rows into the data set until the TRANSACTION is complete.
Discussion
41.

Point out the correct statement.(a) Deadlocking is same as normal blocking(b) No other transactions can read or modify data locked with shared lock(c) A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources(d) All of the mentionedI had been asked this question by my college director while I was bunking the class.Asked question is from Locking in portion Performance Tuning and Optimization of SQL Server

Answer» CORRECT choice is (C) A deadlock occurs when there is a cyclic DEPENDENCY between two or more threads for some set of resources

The best EXPLANATION: Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system.
Discussion
42.

Which of the following resource can be locked in SQL Server?(a) TID(b) RID(c) PID(d) SIDThe question was asked in quiz.Asked question is from Locking topic in portion Performance Tuning and Optimization of SQL Server

Answer»

Correct option is (b) RID

The EXPLANATION is: RID STANDS for Row IDENTIFIER and is used to lock a SINGLE row within a table.
Discussion
43.

ROLLBACK WORK always rolls back to the outermost BEGIN TRANSACTION statement for ___________ transactions.(a) Simple(b) Nested(c) Distributed(d) None of the mentionedThe question was asked during an internship interview.The origin of the question is Transaction in portion Performance Tuning and Optimization of SQL Server

Answer»

Correct choice is (B) Nested

The explanation: When nesting TRANSACTIONS, ROLLBACK WORK decrements the @@TRANCOUNT system function to 0.
Discussion
44.

What is the fundamental difference between COMMIT and COMMIT WORK?(a) COMMIT TRANSACTION accepts a user-defined transaction name(b) COMMIT WORK accepts a user-defined transaction name(c) COMMIT TRANSACTION accepts a user-defined function(d) COMMIT WORK accepts a user-defined stored procedureI got this question during an interview for a job.My enquiry is from Transaction in portion Performance Tuning and Optimization of SQL Server

Answer» CORRECT ANSWER is (a) COMMIT TRANSACTION accepts a user-defined transaction name

Easiest explanation: COMMIT SYNTAX, with or without specifying the optional keyword WORK, is compatible with SQL-92.
Discussion
45.

Implicit transactions are maintained by SQL Server for ___________(a) SELECT(b) DDL(c) DML(d) All of the mentionedI got this question in examination.My question is taken from Transaction in section Performance Tuning and Optimization of SQL Server

Answer» RIGHT answer is (d) All of the mentioned

Explanation: If there is an ERROR occurring within these STATEMENTS individually, SQL Server will roll BACK the complete statement.
Discussion
46.

A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, with the exception of __________(a) escalations(b) containment(c) contention(d) none of the mentionedThis question was posed to me by my college professor while I was bunking the class.My doubt stems from Transaction in portion Performance Tuning and Optimization of SQL Server

Answer» RIGHT OPTION is (a) escalations

To EXPLAIN I would say: These LOCKS are not released, and they are not converted back to their previous lock mode.
Discussion
47.

Point out the wrong statement.(a) It is the responsibility of the Transact-SQL programmer to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct(b) The Database Engine increments the transaction count within a statement only when the transaction count is 1 at the start of the statement(c) You cannot roll back a transaction after a COMMIT TRANSACTION statement is issued(d) COMMIT transaction requires membership in the public roleThe question was asked in an interview for internship.This is a very interesting question from Transaction in chapter Performance Tuning and Optimization of SQL Server

Answer»

Right option is (b) The Database Engine INCREMENTS the transaction count WITHIN a statement only when the transaction count is 1 at the start of the statement

The EXPLANATION: The Database Engine increments the transaction count within a statement only when the transaction count is 1 at the start of the statement.
Discussion
48.

_________ permissions default to any valid user.(a) ROLLBACK WORK(b) ROLLBACK(c) COMMIT(d) None of the mentionedThe question was asked in unit test.My enquiry is from Transaction topic in division Performance Tuning and Optimization of SQL Server

Answer»

The correct option is (a) ROLLBACK WORK

The best explanation: When nesting TRANSACTIONS, ROLLBACK WORK always rolls back to the OUTERMOST BEGIN TRANSACTION statement and decrements the @@TRANCOUNT system FUNCTION to 0.
Discussion
49.

Which of the following argument is ignored by database engine in COMMIT TRANSACTION?(a) @tran_name_variable(b) @tran_name(c) transaction_name(d) all of the mentionedThis question was posed to me by my school teacher while I was bunking the class.I would like to ask this question from Transaction in chapter Performance Tuning and Optimization of SQL Server

Answer» CORRECT option is (a) @tran_name_variable

Best explanation: transaction_name specifies a transaction NAME assigned by a previous BEGIN TRANSACTION. transaction_name must CONFORM to the rules for IDENTIFIERS
Discussion
50.

The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the _________ interface.(a) ITransJoin(b) ITransactionJoin(c) IactionJoin(d) None of the mentionedThe question was posed to me by my college director while I was bunking the class.My query is from Transaction in section Performance Tuning and Optimization of SQL Server

Answer» CORRECT CHOICE is (b) ITransactionJoin

For explanation: ITransactionJoin is exposed only by providers that SUPPORT distributed TRANSACTIONS.
Discussion