Please read the Introductory Post before continue reading interview question and answers.
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)
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)
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)
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)
ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE:
Specifies whether to roll back after a specified number of seconds or immediately if transaction is not complete.
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)
In case of GETDATE, the precision is till milliseconds, and in case of SYSDATETIME, the precision is till nanoseconds.(Read More Here)
The following query can be used to know if Automatic Statistic Update:
WHERE name =‘YOUR DATABASE NAME
We can use the following query to find the size of index.
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.
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)
The following advantages can be achieved by appropriate administration of policy management system.
Reference: Pinal Dave (http://blog.SQLAuthority.com)