SQL Server Interview Questions: Part 2

  1. What is blocking and how would you troubleshoot it?
    Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
  2. How to restart SQL Server in single user mode?
    SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode.
  3. what are the DBCC commands that you commonly use for database maintenance?
    DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
  4. What are statistics?
    Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.
  5. Under what circumstances they go out of date, how do you update statistics?
    Some situations under which you should update statistics:
    1) If there is significant change in the key values in the index
    2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
    3) Database is upgraded from a previous version Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats
  6. What are the different ways of moving data/databases between servers and databases in SQL Server?
    BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.
  7. Explian different types of BACKUPs avaialabe in SQL Server?
    Full database backup, differential database backup, transaction log backup, filegroup backup.
  8. What is database replicaion?
    Replication is the process of copying/moving data between databases on the same or different servers.
  9. What are the different types of replication you can set up in SQL Server?
    SQL Server supports the following types of replication scenarios:
    * Snapshot replication
    * Transactional replication (with immediate updating subscribers, with queued updating subscribers)
    * Merge replication
  10. How to determine the service pack currently installed on SQL Server?
    The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed.
  11. What are cursors?
    Cursors allow row-by-row prcessing of the resultsets.
  12. Explain different types of cursors.
    Types of cursors:
    Static, Dynamic, Forward-only, Keyset-driven.
  13. What are the disadvantages of cursors?
    Disadvantages of cursors:
    Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
  14. How can you avoid cursors?
    Most of the times, set based operations can be used instead of cursors. Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row.
  15. What is a join?
    Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
  16. Explain different types of joins.
    Types of joins:
    INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
  17. Can you have a nested transaction?
    Yes, very much.
  18. What is an extended stored procedure?
    An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
  19. Can you instantiate a COM object by using T-SQL?
    Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. 
  20. What is the system function to get the current user's user id?
    USER_ID(). Some more system functions: USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().