Sql Server Interview Questions

How can you raise custom errors from stored procedure?

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sysmessages table.

what is ACID fundamental? What are transactions in SQL SERVER?

A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:

Atomicity
A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them is performed.

Consistency
When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity.

Isolation
Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either see data in the state it was before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

Durability
After a transaction has completed, its effects are permanently in place in the system.The modifications persist even in the event of a system failure.

What is DBCC?

DBCC (Database Consistency Checker Commands) is used to check logical and physical consistency of database structure.DBCC statements can fix and detect problems. These statements are grouped in to four categories:-

Maintenance commands like DBCC DBREINDEX, DBCC DBREPAR etc, they are mainly used for maintenance tasks in SQL SERVER.

Miscellaneous commands like DBCC ROWLOCK, DBCC TRACEO etc, they are mainly used for enabling row-level locking or removing DLL from memory.

Status Commands like DBCC OPENTRAN, DBCC SHOWCONTIG etc , they are mainly used for checking status of the database.

Validation Commands like DBCC CHECKALLOC, DBCCCHECKCATALOG etc , they perform validation operations on database.

What is the purpose of Replication?

Replication is way of keeping data synchronized in multiple databases. SQL server replication has two important aspects publisher and subscriber.

Publisher Database server that makes data available for replication is called as Publisher.

Subscriber Database Servers that get data from the publishers is called as Subscribers.

What are the different types of replication supported by SQL Server?

There are three types of replication supported by SQL SERVER:-

Snapshot Replication
Snapshot replication takes snapshot of one database and moves it to the other database. After initial load data can be refreshed periodically. The only disadvantage of this type of replication is that all data has to be copied each time the table is refreshed.

Trnsactional Replication
In transactional replication data is copied first time as in snapshot replication, but later only the transactions are synchronized rather than replicating the whole database. you can either specify to run continuosly or on periodic basis.

Merge Replication
Merge replication combines data from multiple sources into a single central database. Again as usual, the initial load is like snapshot but later it allows change of data both on subscriber and publisher, later when they come on-line it detects and combines them and updates accordingly.

What is BCP utility in SQL SERVER?

BCP (Bulk Copy Program) is a command line utility by which you can import and export large amounts of data in and out of SQL SERVER database.

What are the different types of triggers in SQL SERVER 2000?

There are two types of triggers :

INSTEAD OF triggers
INSTEAD OF triggers fire in place of the triggering action. For Example, if an INSTEAD OF UPDATE trigger exists on the Sales table and an UPDATE statement is executed against the Sales table, the UPDATE statement will not change a row in the sales table. Instead, the UPDATE statement causes the INSTEAD OF UPDATE trigger to be executed, which may or may not modify data in the Sales table.

AFTER triggers
AFTER triggers execute following the SQL action, such as an insert, update, or delete. This is the traditional trigger which existed in SQL SERVER.
INSTEAD OF triggers gets executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers gets executed after these constraints are checked.
Unlike AFTER triggers, INSTEAD OF triggers can be created on views.

If we have multiple AFTER Triggers on table how can we define the sequence of the triggers?

If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder.

What is SQL injection?

It is a Form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization’s host computers through the computer that is hosting the database.

SQL injection attacks typically are easy to avoid by ensuring that a system has strong input validation.