SQL Server 2008 - Interview Questions and Answers

  1. What is CLR?
    In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers. You can develop such CLR add-ons to SQL Server using Visual Studio 2008.
  2. What are synonyms?
    Synonyms give you the ability to provide alternate names for database objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with three and four part names; for example, shortening server.database.owner.object to object.
  3. What is LINQ?

    Language Integrated Query (LINQ) adds the ability to query objects using .NET languages. The LINQ to SQL object/relational mapping (O/RM) framework provides the following basic features:

    1. Tools to create classes (usually called entities) mapped to database tables
    2. Compatibility with LINQ's standard query operations
    3. The DataContext class, with features such as entity record monitoring, automatic SQL statement generation, record concurrency detection, and much more
  4. What is Isolation Levels?

    Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

    Transaction isolation levels control:
    1. Whether locks are taken when data is read, and what type of locks are requested.
    2. How long the read locks are held.
    3. Whether a read operation referencing rows modified by another transaction:
      1. Blocks until the exclusive lock on the row is freed.
      2. Retrieves the committed version of the row that existed at the time the statement or transaction started.
      3. Reads the uncommitted data modification.
  5. What is use of EXCEPT Clause?
    EXCEPT clause is similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.
  6. How would you handle error in SQL SERVER 2008?
    SQL Server now supports the use of TRY...CATCH con handling. TRY...CATCH lets us build error handling at the level we need, in the way w to, by setting a region where if any error occurs, it will break out of the region and head to an error handler.

    The basic structure is as follows: