SQL Server Notification Services Interview Questions and Answers

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.

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

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
NOT NULL.
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.

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

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.

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

4 :: Define transaction and transaction isolation levels?

Transaction

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.

Atomicity
Transaction ensures either modification is committed or not committed.

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

Isolation
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.

Durability
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
Serializable
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

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

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.

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

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No