SQL SERVER Interview Questions & Answers – SET 1 (50 Questions & Answers) Download – Pawan Kumar Khowal – SQL SERVER Interview Questions with Answers

PAGE – 4

PREV

NEXT

Question 31- I have a table with millions of rows. I want to retain only last 5% of the rows? How does u do it?

Answer – 

Transfer last 5 % rows in a temp table 2. Delete all the rows from the table. 3. Move back data from temp table to the main table.

Question 32 – Explain transaction log in detail

Answer –

A transaction log stores every transaction made to a SQL Server database, except some which are minimally logged like BULK IMPORT or SELECT INTO. Internally it is split into the smaller parts called Virtual Log Files (VLFs). When one VLF becomes full logging continue to write into the next available in the transaction log. The transaction log file can be represented as a circular file. When the logging reaches the end of the file it starts again from the beginning, but only if all the requirements has been met and the inactive parts has been truncated. The truncation process is necessary to mark all inactive parts so they can be used again and overwritten. A Log Sequence Number (LSN) identifies every transaction in the transaction log.

Question 33. Can we call a procedure from a function?

Answer – No.

Question 34. Can we write DML inside a function?

Answer – You can only use SELECT inside a function. You cannot use Insert, Update and Delete inside a function.

Question 35. How to avoid bookmark lookup in execution plan?

Answer-

Key lookup operation occurs when index seek is done on a non-clustered index to locate one or more rows, but the non-clustered index does not contain all the columns necessary for the query. The clustered index key (which is always included in all non-clustered indexes) is then used to locate the row in the clustered index, to retrieve the remaining data. We can include all the necessary columns in the NonClustered key.

Question 36. What is XACT_ABORT ON?

Answer – When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. Sample e.g.

--

SET XACT_ABORT OFF;
SET XACT_ABORT ON;

--

Question 37. Does temp tables and table variables both stored in tempDB?

Answer-

Temp tables and table variables are both objects created INSIDE TEMPDB. They are not created in memory. Their presence in memory is a result of their usage. Meaning: their pages are transferred and stored inside the buffer pool because this is the common way SQL Server works with all data pages – when a data page is requested for reads or writes it is read from disk and saved in the Buffer pool. So unless they are too big or server memory is under pressure they will be cached in memory anyway. For details please visit – http://maginaumova.com/temp-tables-or-temp-variables/

Question 38 – Explain Column Store Index

Answer- It’s a new feature introduced in SQL 2012. The column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request. A ColumnStore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored. We can only create non clustered ColumnStore index. Column store index has been designed to substantially accelerate common data warehouse queries, which require scanning, aggregation and filtering of large amounts of data or joining multiple tables like a star schema. With column store index, you can get interactive response time for queries against billions of rows on an economical SMP server with enough RAM to hold your frequently accessed data. Creating a ColumnStore index is a parallel operation, subject to the limitations on the number of CPUs available and any restrictions set on MAXDOP setting.

Question 39. What happens when a rollback happens in inside a nested stored procedure?

Answer- If the rollback is present in the outermost transaction then everything will be rolled back irrespective of what is written in the inner transactions. If the rollback is present in the inner transaction then we will not have any transaction in the outer most transaction. Also note that the output depends on the statements written. For details please visit – https://msbiskills.com/2015/05/12/sql-server-nested-transactions-rollback-or-commit-confusions/

Question 40. Why cursors are so costly?

Answer-

SQL Server like any good relational database management system (RDBMS) is optimized for set-based operations and NOT for row by row operation. Cursors will fetch data on a row by row basis, and it takes heck of a lot longer to do so. This is because the set-based logic for which RDBMS systems like SQL Server are optimized is completely broken and the entire query process has to be repeated for each row. For details please visit – http://www.sqlshack.com/sql-server-cursor-performance-problems/

PREV

NEXT