Interview Questions.

Question 1. What Are The Lock Types?

Answer :

SQL server supports following locks:

Shared lock
Update lock
Exclusive lock
Shared lock

Shared Lock allows simultaneous access of document by more than one Select statements.
Shared Lock blocks record from updating and will remain in queue ready while document is accessed for analyzing.
If update system is going on then read command will should wait until updating manner finishes.
Update locks

This lock is used with the assets to be updated.

Exclusive locks

This form of lock is used with statistics change operations like update, insert or delete.

Question 2. Define Data, Entity, Domain And Referential Integrity?

Answer :

Data Integrity

Data Integrity validates the facts earlier than getting stored within the columns of the desk.

SQL Server helps 4 sort of information integrity:

Entity Integrity

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

Domain Integrity:

Domain integrity validates records for a column of the table.

It may be enforced the use of:

Foreign key constraints,

Check constraints,

Default definitions

NOT NULL.

Referential Integrity

FOREIGN KEY and CHECK constraints are used to put in force Referential Integrity.

User-Defined Integrity

It permits you to create business common sense which isn't always feasible to develop the usage of device constraints. You can use saved system, trigger and functions to create consumer-defined integrity.

SQL Server 2000 Interview Questions
Question three. What Is Isolation Levels?

Answer :

Isolation keeps the transactions of more than one users isolated from every other. Transaction isolation degree controls the degree of locking which takes place while deciding on information. This is important to keep away from conditions like:

Lost updates:when  transactions examine the same data.

Dirty study: Occurs when a transaction reads statistics that has not been up to date.

Non repeatable reads: occur when exclusive outcomes are study multiple times.

Phantoms: Occurs when row data matches the first time however does no longer healthy next instances.

Question 4. Define Transaction And Transaction Isolation Levels?

Answer :

Transaction

A transaction is a set of operations that works as a single unit. The transactions can be labeled into explicit, autocommit, and implicit transactions. Every transaction have to observe four homes called the ACID houses i.E. Atomicity, consistency, isolation, and sturdiness.

Atomicity

Transaction guarantees both modification is committed or now not committed.

Consistency

The statistics ought to be in constant state whilst transaction manner is finished. This way that every one associated tables are up to date.

Isolation

SQL server supports concurrency while imply that statistics may be get admission to or shared with the aid of many customers. A transaction works in isolation and does not allow other transaction to work simultaneously on the identical piece of data.

Durability

Data is everlasting as soon as transaction is completed and it may be recovered even though machine fails.

There are four transaction isolation levels:

Read uncommitted
Read devoted
Repeatable examine
Serializable
Read uncommitted isolation tiers
This is the lowest isolation stage which can also be called as grimy study. Using this, you may examine uncommitted facts which can be rolled returned at any point. With this stage, SQL server uses proportion lock even as studying records.
Read dedicated isolation levels

With this degree, uncommitted facts can not be read. This is default isolation degree and makes use of shared lock whilst studying information.
Repeatable examine isolation levels
It locks all of the records that is used in the question.
Serializable isolation levels
SQL Server 2008 Tutorial
Question 5. Explain Openquery Function And Openrowset Function?

Answer :

OPENQUERY: - Used to execute the query surpassed at the connected server.

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

E.G. Select * from OPENQUERY (Oracleserver, ‘choose fname, FROM Employee);

OPENROWSET: - Used to get right of entry to tables in a related server. It can be used one time for having access to remote facts via the usage of OLE DB. It includes all of the records required to get entry to the faraway facts.

Syntax:

OPENROWSET

(  'provider_name' ,  'provider_string' 

,  [ catalog. ] [ schema. ] object

question'

 SINGLE_NCLOB 

 )

MSBI Interview Questions
Question 6. What Is A Linked Server?

Answer :

A connected server allows far off get entry to. They have the ability to trouble distributed queries, replace, instructions, and transactions across different facts resources. A connected server has an OLE DB provider and statistics source.

Question 7. What Is Distributed Queries?

Answer :

Distributed queries can access information from exclusive facts assets. These sources can live on the equal server or a special server. This method that distributed queries can query more than one databases.

SQL Database Tutorial SQL Server 2008 Interview Questions
Question eight. Define Distributed Query And Linked Server?

Answer :

Distributed Query:

Distributed Query is a question which could retrieve data from a couple of data resources consisting of distributed information.
SQL Server helps dispensed question via the usage of OLE DB provider.
OLE DB issuer exposes records within the row sets which can be utilized in SQL Statement.
SQL Server can use allotted question inside the SQL Statement the usage of
Linked Server:

It is the virtual server this is created to get entry to OLE DB statistics supply.
It includes all of the facts needed to get right of entry to OLE DB records source.
Linked server definition includes all the data needed to locate OLE DB data source.
You can join far off records and local information the usage of Linked Server.
Question 9. Explain The Difference Between A Primary Key And A Unique Key?

Answer :

Both are described to ensure specific row.
Primary key creates a clustered index on the column by default.
Unique creates a non-clustered index by using default.
Primary key does not allow NULLs, however precise key lets in one NULL best.
SQL Server 2005 Interview Questions
Question 10. Define Primary And Unique Key?

Answer :

Primary Key

The column or columns of the desk whose price uniquely identifies each row inside the desk is called primary key.
You can define column as primary key the usage of primary key constraint whilst you create desk.
When you define a column as primary key, a unique index is created which restricts reproduction information and speedy get admission to to statistics.
A column defined as number one key doesn't allow null price.
By default, clustered index in created with the column having primary key.
Unique key

Unique key also ensures information specialty like number one key.
A column with specific key defined allows null value. 
By default, it creates non-clustered index.
Question eleven. What Are The Restrictions Applicable While Creating Views?

Answer :

Views may be created referencing tables and views most effective inside the present day database.
A view call have to not be the same as any table owned through that user.
You can construct views on different views and on procedures that reference perspectives.
Rules or DEFAULT definitions can not be related to perspectives.
Only INSTEAD OF triggers may be related to perspectives.
The question that defines the view cannot encompass the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
You cannot define complete-text index definitions for perspectives.
You can't create temporary perspectives
You can not create perspectives on transient tables.
SQL Database Interview Questions
Question 12. What Are The Basic Components Of Notification Services?

Answer :

The following terms are the components of Notification Service:

Event: an action that occurred affecting the required facts. 

Subscriber: an entity that desires being notified on occurrence of an occasion.

Subscription: an act through which subscriber describes when and what he desires to be notified as. 

Notification: a channel of conversation.

SQL Server 2000 Interview Questions
Question thirteen. Explain Architecture Of Sql Server Notification Services?

Answer :

Notification Services is composed 4 additives

Subscription

Subscription statistics may be added using (SMO) Subscription Management Objects to Notification Services software.

Events

Events get populated to activities desk with the help of Event vendors.

Generator

When activities are populated into activities table, the generator wakes up and starts offevolved processing policies which can be attached with subscriptions. The generator tests to see if any activities suit them. The generator begins creating notifications and fills the Notifications table, if suits are observed.

Delivery (notifications)

When notifications arrive in Notifications table, the distributor wakes up and starts offevolved processing every notification, formats it and delivers them the use of distinctive channel.

Question 14. What Are Notification Services?

Answer :

Notification Services are services which send notifications to the fascinated entities based on what they would really like be notified on.

It is one of the capabilities of the SQL Server.

Question 15. How Does Notification Services Work?

Answer :

Subscription statistics can be delivered using (SMO) Subscription Management Objects to Notification Services software. Events get populated to activities table with the assist of Event providers. When events are populated into activities desk, the generator wakes up and starts offevolved processing policies that are connected with subscriptions. The generator checks to peer if any activities suit them. The generator starts growing notifications and fills the Notifications table, if fits are discovered. When notifications arrive in Notifications table, the distributor wakes up and begins processing each notification, codecs it and offers them the use of precise channel.

SQL DBA Interview Questions