1. What are the lock types?

SQL server supports following locks

Shared lock
Update lock
Exclusive lock

Shared lock
Shared Lock allows simultaneous access of record by multiple Select statements.
Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading.
If update process is going on then read command will have to wait until updating process finishes.

Update locks
This lock is used with the resources to be updated.

Exclusive locks
This kind of lock is used with data modification operations like update, insert or delete.

2. Define data, entity, domain and referential integrity?

Data Integrity
Data Integrity validates the data before getting stored in the columns of the table.
SQL Server supports four type of data integrity:
Entity Integrity

Entity Integrity can be enforced through indexes, UNIQUE constraints and PRIMARY KEY constraints.

Domain Integrity
Domain integrity validates data for a column of the table.
It can be enforced using:

Foreign key constraints,
Check constraints,
Default definitions
Referential Integrity

FOREIGN KEY and CHECK constraints are used to enforce Referential Integrity.
User-Defined Integrity

It enables you to create business logic which is not possible to develop using system constraints. You can use stored procedure, trigger and functions to create user-defined integrity.

3. What is Isolation Levels?

Isolation keeps the transactions of multiple users isolated from each other. Transaction isolation level controls the degree of locking which occurs when selecting data. This is necessary to avoid situations like:

► Lost updates- when two transactions read the same data.
► Dirty read- Occurs when a transaction reads data that has not been updated.
► Non repeatable reads- occur when different results are read multiple times.
► Phantoms- Occurs when row data matches the first time but does not match subsequent times.

4. Define transaction and transaction isolation levels?


A transaction is a set of operations that works as a single unit. The transactions can be categorized into explicit, autocommit, and implicit transactions. Every transaction should follow four properties called the ACID properties i.e. atomicity, consistency, isolation, and durability.

Transaction ensures either modification is committed or not committed.

The data should be in consistent state when transaction process is completed. This means that all related tables are updated.

SQL server supports concurrency when mean that data can be access or shared by many users. A transaction works in isolation and doesn't allow other transaction to work concurrently on the same piece of data.

Data is permanent once transaction is completed and it can be recovered even if system fails.
There are four transaction isolation levels:

Read uncommitted
Read committed
Repeatable read
Read uncommitted isolation levels

This is the lowest isolation level which can also be called as dirty read. Using this, you can read uncommitted data which can be rolled back at any point. With this level, SQL server uses share lock while reading data.
Read committed isolation levels

With this level, uncommitted data can't be read. This is default isolation level and uses shared lock while reading data.
Repeatable read isolation levels

It locks all the data that is used in the query.
Serializable isolation levels

5. Explain OPENQUERY function and OPENROWSET function?

OPENQUERY: - Used to execute the query passed on the linked server.

Syntax: OPENQUERY (Linked_server_identifier, query). It can also be refernced from a FROM clause of selecte query.

e.g. Select * from OPENQUERY (Oracleserver, ‘select fname, FROM Employee);

OPENROWSET: - Used to access tables in a linked server. It can be used one time for accessing remote data by using OLE DB. It includes all the information required to access the remote data.

( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
} )

6. What is a linked server?

A linked server allows remote access. They have the ability to issue distributed queries, update, commands, and transactions across different data sources. A linked server has an OLE DB provider and data source.

7. What is Distributed Queries?

Distributed queries can access data from different data sources. These sources can reside on the same server or a different server. This means that distributed queries can query multiple databases.

8. Define Distributed Query and Linked Server?

Distributed Query:
► Distributed Query is a query which can retrieve data from multiple data sources including distributed data.
► SQL Server supports distributed query through the use of OLE DB provider.
► OLE DB provider exposes data in the row sets which can be used in SQL Statement.

SQL Server can use distributed query in the SQL Statement using

Linked Server:
It is the virtual server that is created to access OLE DB data source.
It includes all the information needed to access OLE DB data source.
Linked server definition contains all the information needed to locate OLE DB data source.
You can join remote data and local data using Linked Server.

9. Explain the difference between a primary key and a unique key?

► Both are defined to ensure unique row.
► Primary key creates a clustered index on the column by default.
► Unique creates a non-clustered index by default.
► Primary key doesn't allow NULLs, but unique key allows one NULL only.

10. Define Primary and Unique key?

Primary Key

► The column or columns of the table whose value uniquely identifies each row in the table is called primary key.
► You can define column as primary key using primary key constraint while you create table.
► When you define a column as primary key, a unique index is created which restricts duplicate data and fast access to data.
► A column defined as primary key doesn't allow null value.
► By default, clustered index in created with the column having primary key.

Unique key
Unique key also ensures data uniqueness like primary key.
A column with unique key defined allows null value.
By default, it creates non-clustered index.

Download Interview PDF