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.
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.
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:
- Tools to
create classes (usually called entities) mapped to database tables.
- Compatibility
with LINQ’s standard query operations.
- The
DataContext class, with features such as entity record monitoring,
automatic SQL statement generation, record concurrency detection, and much
more.
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:
- Whether locks
are taken when data is read, and what type of locks are requested.
- How long
the read locks are held.
- Whether a read
operation referencing rows modified by another transaction:
- Blocks until
the exclusive lock on the row is freed.
- Retrieves the
committed version of the row that existed at the time the statement or
transaction started.
- Reads the
uncommitted data modification.
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.
What is XPath?
XPath uses a set of expressions to select nodes to be processed. The
most common expression that you’ll use is the location path expression, which
returns back a set of nodes called a node set. XPath can use both an unabbreviated and an abbreviated syntax. The
following is the unabbreviated syntax for a location path:
/axisName::nodeTest[predicate]/axisName::nodeTest[predicate]
What is NOLOCK?
Using the NOLOCK query optimizer hint is generally considered good
practice in order to improve concurrency on a busy system. When the NOLOCK hint
is included in a SELECT statement, no locks are taken when data is read. The
result is a Dirty Read, which means that another process could be updating the
data at the exact time you are reading it. There are no guarantees that your
query will retrieve the most recent data. The advantage to performance is that
your reading of data will not block updates from taking place, and updates will
not block your reading of data. SELECT statements take Shared (Read) locks.
This means that multiple SELECT statements are allowed simultaneous access, but
other processes are blocked from modifying the data. The updates will queue
until all the reads have completed, and reads requested after the update will
wait for the updates to complete. The result to your system is delay
(blocking).
How would you handle error in SQL
SERVER 2008?
SQL Server now supports the use of TRY…CATCH constructs for providing
rich error handling. TRY…CATCH lets us build error handling at the level we
need, in the way we need 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
<code>
END TRY
BEGIN CATCH
<code>
END CATCH
So if any error occurs in the TRY block, execution is diverted to the
CATCH block, and the error can be dealt.
0 comments
Post a Comment