Interview questions and answers in SQL Server

1)What is trigger?

Triggers allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.
Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the insert, delete and update operation.

2)How many types of triggers are there?

There are two types of triggers.
1)After triggers:
These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as
(a) AFTER INSERT Trigger.
(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger.
2)Insteadof triggers:
These can be used as an interceptor for anything that anyone tried to do on our table or view.
If you define an Instead Of trigger on a table for the Delete operation, they try to delete rows,and they will not actually get deleted (unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:-
(a) INSTEAD OF INSERT Trigger.
(b) INSTEAD OF UPDATE Trigger.
(c) INSTEAD OF DELETE Trigger.

3)How to change Database name in SQL Server?

Ans: Use following code
Supported in SQL Server 2000 and 2005
exec sp_renamedb "test", "test1"
Supported in SQL Server 2005 and later version
ALTER Database "test1" Modify Name="test"

4) What is a transaction and what are ACID properties?

Ans: A transaction is a logical unit of work in which , all the steps must be performed or none.
ACID stands for Atomicity, Consistency, Isolation, Durability.
These are the properties of a transaction.

5) Difference Between Implicit Transaction And Explicit Transaction

Ans: Implicit Transaction is the auto commit. There is no beginning or ending of the transaction.
Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction
Commit Transaction and
Rollback Transaction
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.

6)What's the difference between a primary key and a unique key?

Ans: Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non clustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

7) What is the maximum limit for Primary Key?

Ans:10 fields in MS Acces
900 Bytes in SQL Server

8)What are three SQL keywords used to change or set someone’s permissions?

Ans:Grant, Deny and Revoke

9) what is cursor?

Ans:A cursor is a set of rows together with a pointer that identifies a current row.
In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis

10) Explain the cursor types.

-DYNAMIC: It reflects changes happened on the table while scrolling through the row.
-STATIC: It works on snapshot of record set and disconnects from the server. This kind doesn’t reflects changes happened on the table while scrolling through the row. KEYSET: In this kind, new record is not reflected, but data modification can be seen

11) Explain different types of cursors.

-- Different types of cursors:
Implicit cursors: these cursors are invoked implicitly. User need not create, open, fetch or close the cursor.
Explicit cursors: these cursors are not invoked implicitly. User needs to create, open, fetch or close the cursor.
Static Cursor: Stores a complete copy of the result set. Used mostly where scrolling is required. Static cursors don’t support updates.
Forward – only cursors: This cursor supports updates but not scrolling. It supports only fetching serially. Rows are not retrieved from the database until they are fetched.
Forward – only cursors / Read only cursor: These are the fastest of the cursors and cannot be updated. They cannot be created on query that returns only read only columns.
Key set driven : It is a scrollable cursor that cannot be updated. These cursors are controlled by a set of physical identifiers called as key set.
The keyset is built in a temporary table when the cursor is opened.

12)Define the cursor lock types.

Three types of locks
READ ONLY: This prevents any updates on the table.
SCROLL LOCK: This allows you to make changes to the table.
OPTIMISTIC: This checks if any change in the table record since the row fetched before updating. If there is no change, the cursor can update

13)What are the disadvantages of cursors?

--Disadvantages of cursors
Uses more resources because Each time you fetch a row from the cursor, it results in a network roundtrip
There are restrictions on the SELECT statements that can be used.
Because of the round trips, performance and speed is slow

14)What is Replication?

---Replication is the process of distributing data from one database to another on the same server or servers connected through LAN or the internet.
Replication is used to synchronize data between databases that may be at remote location.
Using this, you can maintain copies of database at more than one locations.
Maintaining copies of data at different locations provide a standby server.
It can be used for load balancing of server by maintaining area specific data independently and replicate later to have single version of data.

15)what are the Components of Replications ?

---Components of Replications:
* Publisher :
A publisher is any database unit that makes data available to other sources via replication.
The publisher can have multiple publications which defines the data to replicate.
* Subscriber
A subscriber is any database unit that receives the replicated data.
It can receive data from multiple publishers or publications.
Data can be republished to the other subscribers.
* Article
Article is the basic unit of publication. It shows what is published.
An article can simply be a table or a stored procedure.
When tables are used as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.
* Publication
It is a collection of one or more articles from one database.
* Distributor
A distributor is any database unit that acts as a store for the data to be replicated.
Each publisher is associated with a single database called as distribution database.
Replication status data is stored in distribution database.
A distributor can be local or remote.
* Pull Subscription
In a pull subscription, changes to a subscriber cannot be publicized without any request from subscriber.
This allows the user at the Subscriber to determine when the data changes are synchronized.
Push Subscription
In a push subscription, changes to a subscriber can be publicized without any request from subscriber.
The changes can be pushed either periodically or on demand.
* Snapshot Agent
A snapshot agent is used in snapshot replication. Snapshots can be created as soon as the subscription is created.
The snapshot agent prepares snapshot files that contain details of the published data.
The snapshot also assists in establishing connection from the Distributor to the Publisher.
* Log Reader Agent
The replication Log Reader Agent is an executable that is used for monitoring transaction logs of each database.
It copies the transactions marked for replication from the transaction log into the distribution database.
* Distribution Agent
The distribution Agent is an executable that is used to move the snapshot and the transactions held in the distribution database tables to the destination tables at the Subscribers.
* Merge Agent
The replication merge agent is an executable that is used to apply the database snapshot to the subscriber.
After the initial snapshot, any other changes if made are also merged.
* Queue Reader Agent
The Replication Queue Reader Agent is an executable that is used to read messages stored in a Microsoft SQL Server queue or a Microsoft Message Queue and then applies those messages to the Publisher.

16)What are the types of Replication?

Ans: Snapshot Replication
Merge Replication
Transactional Replication

17)What is the Snapshot Replication ?

--In this kind, snapshot of entire data is copied from publisher to the subscriber's database on regular interval.
This kind of replication is used for replicating data that doesn’t change frequently.
It is used where the source can manage with out of date data.
This replication is considered when amount of data to be replicated is small.

18)What is the Merge Replication ?

--This allows both publisher and subscriber to work independently, online or offline and merges the changes later.
Here changes are track on both publisher and subscriber and then merged.

19)What is the Transactional Replication ?

--This kind is used when changes are frequent.
The replication agent monitors the changes at the publisher and distributes the changes to the subscribers.
Transactional Replication is required where up to date data is required.

20)What are constraints in SQL Server?

--Constraints enforce the data integrity to the database and protect columns of the table from unwanted values.
NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of contraints define in SQL Server.

21)Define default constraint.

--Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data.
IDENTITY columns and timestamp columns can't be associated with default constraint.

22)what is normalization?

--Normalization is the process of reducing data redundancy and maintains data integrity.
This is performed by creating relationships among tables through primary and foreign keys.
Normalization procedure includes 1NF, 2NF, 3NF, BCNF, and then the data is normalized

23)what are the Steps of normalization?

--First Normal form:
Entities of the table must have unique identifier or entity key.
i.e spliting table into number of tables to reduce data redundancy
Second Normal Form:
All the attributes of the table must depend on the entity key for that entity.
i.e all dependent columns in one table and all independent columns in another table
Third Normal Form:
All attributes that are not part of the key must not depend on any other non-key attributes.
i.e derived columns must be avoided
BCNF:
Due to removal of child rows , there should not be any loss in master data

24) what is Denomalization?

--Denomalization on the contrary is the process of adding redundant data to speed up complex queries involving multiple table JOINS.
One might just go to a lower form of Normalization to achieve Denormalization and better performance.
Data is included in one table from another in order to eliminate the second table which reduces the number of JOINS in a query and thus achieves performance.

25)what is Stored Procedures ?

--A stored procedure is a precompiled stored object
The business logic can be encapsulated using stored procedure.
It improves network traffic by running set of Transact-SQL statements at one go.

26)what is Trigger?

--A trigger is a special type of event driven stored procedure.
It gets initiated when Insert, Delete or Update event occurs.
It can be used to maintain referential integrity.
A trigger can call stored procedure.

27)what is the View?

--View can be created to retrieve data from one or more tables.
Query used to create view can include other views of the database.
We can also access remote data using distributed query in a view.

28)What are the difference between Stored Procedure and Trigger in SQL Server?

1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.
2) We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table.
3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.
4) Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.
5) Stored procedures can return values but a trigger cannot return a value.
6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.

29)Define acid property in SQL Server

ACID Property:- It is a concept for evaluation of databases and their architecture.
A:- (Atomicity) – Atomicity states the principle of All or none.
This means that either all the SQL Statements within the transaction will be executed or no Sql statement will be executed.
C:- Consistency:- Consistency states that only valid data will be written into the database.
That means if any Sql transaction violets the rules or constraints define on the database to make it consistent, then all the statements within the transaction will be Rollback. Or in other words the whole transaction will be rolled back.
I:- Isolation :- The Isolation state that if multiple transaction try to work on the database at the same time, then these transaction will not interfere with each other.
It means the second transaction will not work unless and until the previous transaction completes its work and the work is committed.
D:- Durability:- Durability states that once the transaction is committed,
then the database should not be lost in case of Software failures or hardware failures.
Durability is maintained with the help of the database backups and transaction logs.

30)What is SQL Functions and what are their different types?

Function:- Function in a database can be defined as the code segment consisting of a logical group of SQL statements which takes some input arguments (if required), run in a sequential order and returns the output either in the form of a single value or in the form of a table.
In SQL, Functions can be categorized into two categories:-
System Defined Function (SDF)
User Defined Function (UDF)

System defined function(SDF) can again be further divided into further subsections which are given below:-
Aggregate function. Example: - Avg (), Min (), Max ()
Configuration function. Example:-@@servername (), @@version()
Cursor function. Example: -@@Fetch_status
Date and Time function. Example: - Getdate (), Month (), Day (), Year ()
Mathematical function. Example: - Floor (), Abs ()
Metadata function. Example: - DbName (), FileName ()
Other functions. Example: - cast (), convert ()
Rowset function. Example: - Openxml (), Openrowset ()
Security function. Example:-user(), UserId (), UserName ()
String function. Example: - Char (), Left (), Len ()
System Statistical function. Example:-@@connections
Text and Image function Example: - Textvalid ()
Types of UDF:-
Similarly, UDF can be divided Into 3 categories:-
Scalar UDF
Inline Table UDF, Multi statements UDF