SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31

Click here to get free chapters (PDF) in the mailbox

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?

In SQL Server 2005 and earlier versions, there is no inbuilt functionality to know which row was recently changed and what the changes were. However, in SQL Server 2008, a new feature known as Change Data Capture (CDC) has been introduced to capture the changed data. (Read more here)

What is the CPU Pressure?

CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started. (Read more here)

How can I Get Data from a Database on Another Server?

If you want to import data only through T-SQL query, then use OPENDATASOURCE function. To repeatedly get data from another server, create a linked server and then use the OPENQUERY function or use 4-part naming. If you are not adhered with T-SQL, then it is better to use import/export wizard, and you can save it as a SSIS package for future use. (Read more here)

What is the Bookmark Lookup and RID Lookup?

When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query. If the query requests data from columns not present in the non-clustered index, then the SQL Server must go back to the data pages to get the data in those columns. Even if the table has a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data.

In the above scenario, if table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called RID lookup. (Read more here)

What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE:

Specifies whether to roll back after a specified number of seconds or immediately if transaction is not complete.

NO_WAIT:

Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, then the request will fail.(Read more here)

What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?

In case of GETDATE, the precision is till milliseconds, and in case of SYSDATETIME, the precision is till nanoseconds.(Read More Here)

How can I Check that whether Automatic Statistic Update is Enabled or not?

The following query can be used to know if Automatic Statistic Update:

SELECT is_auto_create_stats_on,is_auto_update_stats_on
FROM sys.databases
WHERE name =‘YOUR DATABASE NAME

(Read more here)

How to Find Index Size for Each Index on Table?

We can use the following query to find the size of index.

SELECT *
FROM sys.indexes
WHERE OBJECT_ID=OBJECT_ID('HumanResources.Shift')

(Read more here)

What is the Difference between Seek Predicate and Predicate?

Seek Predicate is the operation that describes the b-tree portion of the Seek. Predicate is the operation that describes the additional filter using non-key columns. Based on the description, it is very clear that Seek Predicate is better than Predicate as it searches indexes, whereas in Predicate, the search is on non-key a column – which implies that the search is on the data in page, files itself.

(Read more here)

What are Basics of Policy Management?

SQL server 2008 has introduced a policy management framework, which is the latest technique for SQL server database engine. SQL policy administrator uses SQL Server Management Studio to create policies that can handle entities on the server side like the SQL Server objects and the instance of SQL Server databases. It consists of three components: policy administrators (who create policies), policy management, and explicit administration. Policy-based management in SQL Server assists the database administrators in defining and enforcing policies that tie to database objects and instances. These policies allow the administrator to configure and manage SQL server across the enterprise. (Read more here)

What are the Advantages of Policy Management?

The following advantages can be achieved by appropriate administration of policy management system.

• It interacts with various policies for successful system configuration.
• It handles the changes in the systems that are the result of configuration against authoring policies.
• It reduces the cost of ownership with simple elaboration of administration tasks.
• It detects various compliance issues in SQL Server Management Studio.

(Read more here)

List of all the Interview Questions and Answers Series blogs

Reference: Pinal Dave (https://blog.sqlauthority.com)