SQL Server 2008 - Interview Questions and Answers

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.

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.

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

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.

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.

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.

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:
BEGIN TRY
stmts..
END TRY
BEGIN CATCH
stmts..
END CATCH