What is the difference between Stored Procedure (SP) and User Defined Function (UDF)?

Following are some major differences between a stored procedure and user defined functions:-
√ UDF can be executed using the "SELECT" clause while SP’s can not be.
√ UDF can not be used in XML FOR clause but SP’s can be used.
√ UDF does not return output parameters while SP’s return output parameters.
√ If there is an error in UDF its stops executing. But in SP’s it just ignores the error and moves to the next statement.
√ UDF can not make permanent changes to server environments while SP’s can change some of the server environment.

A)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. As name suggest we inject SQL which can be relatively dangerous for the database. Example this is a simple SQL SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' Now somebody does not put "x" as the input but puts "x ; DROP TABLE members;". So the actual SQL which will execute is :-
SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' ; DROP TABLE members; Think what will happen to your database.

A)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. All the other triggers are in an undefined order which you cannot control.

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 Salestable, 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.

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. Below is a sample which shows BCP in action.

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. Transactional 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 continuously 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 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 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.They 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,DBCC CHECKCATALOG etc , they perform validation operations on database. Note :- Check MSDN for list of all DBCC commands, it is very much possible specially during DBA interviews they can ask in depth individual commands. Below is a sample screen in which DBCC SHOWCONTIG command is run. DBCC SHOWCONTIG is used to display fragmentation information for the data and indexes of the specified table.In the sample screen "Customer" table is checked for fragmentation
information. If you look at the figure "Scan density" is 100 if everything isucontigious in this image it is 95.36% which is decent percentage. So such type of useful information can be collected by DBCC command and database performance and maintenance can be improved.

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. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.
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.

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. You can use this statement with the error handling code presented in the previous section to implement custom error messages in your applications. The syntax of the statement is shown here.
RAISERROR ({msg_id msg_str }{,severity ,state } [ ,argument [ ,,...n ] ] )) [ WITH option [ ,,...n ] ]
A description of the components of the statement follows.
msg_id :-The ID for an error message, which is stored in the error column in sysmessages.
msg_str :-A custom message that is not contained in sysmessages.
severity :- The severity level associated with the error. The valid values are 0–25. Severity levels 0–18 can be used by any user, but 19–25 are only available to members of the fixed-server role sysadmin. When levels 19–25 are used, the WITH LOG option is required. state A value that indicates the invocation state of the error. The valid values are 0–127. This value is not used by SQL Server. Argument, . . . One or more variables that are used to customize the message. For example, you could pass the current process ID (@@SPID) so it could be displayed in the message. WITH option, . . . The three values that can be used with this optional argument are described here. LOG - Forces the error to log in the SQL Server error log and the NT application log. NOWAIT - Sends the message immediately to the client. SETERROR - Sets @@ERROR to the unique ID for the message or 50,000. The number of options available for the statement makes it seem complicated, but it is actually easy to use. The following shows how to create an ad hoc message with a severity of 10 and a state of 1. RAISERROR ('An error occurred updating the NonFatal table',10,1) --Results-- An error occured updating the NonFatal table The statement does not have to be used in conjunction with any other code, but for our purposes it will be used with the error handling code presented earlier.
The following alters the ps_NonFatal_INSERT procedure to use RAISERROR.
USE tempdb go ALTER PROCEDURE ps_NonFatal_INSERT @Column2 int =NULL AS
DECLARE @ErrorMsgID int INSERT NonFatal VALUES (@Column2) SET @ErrorMsgID =@@ERROR IF @ErrorMsgID <>0 BEGIN RAISERROR ('An error occured updating the NonFatal table',10,1) END When an error-producing call is made to the procedure, the custom message is passed to the client. The following shows the output generated by Query Analyzer.

What is the difference between UNION and UNION ALL SQL syntax ?

UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table, while UNION ALL selects all records from both the tables.
Note :- Selected records should have same datatype or else the syntax will not work.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

You can use Having Clause with the GROUP BY function in a query and WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What are advantages of SQL 2000 over SQl 7.0 ?

√ User-Defined Functions: User-Defined Functions (UDFs) -- one or more Transact-SQL statements can be used to encapsulate code for reuse. User-defined functions cannot make a permanent change to the data or modify database tables. UDF can change only local objects for a UDF, such as local cursors or variables.
√ Distributed Partitioned Views: Distributed partitioned views allow you to partition tables horizontally across multiple servers. So, you can scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers. Due to distributed partitioned views, SQL Server 2000 now on the first place in the tpc-c tests.
√ New Data Types: These include: bigint, an 8-byte integer type; sql_variant, a data type that allows the storage of data of different data types; and the table data type, which allows applications to store results temporarily for later use.
√ INSTEAD OF and AFTER Triggers: There are INSTEAD OF and AFTER Triggers in SQL Server 2000. INSTEAD OF triggers are executed instead of the INSERT, UPDATE or DELETE triggering action. AFTER triggers are executed after the triggering action.
√ Cascading Referential Integrity Constraints: There are new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. The ON DELETE clause controls what actions are taken if you attempt to delete a row to which existing foreign keys point. The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing foreign keys point.
√ The ON DELETE and ON UPDATE clauses have two options:
NO ACTION :-NO ACTION specifies that the deletion/update fail with an error.
CASCADE :-CASCADE specifies that all the rows with foreign keys pointing to the deleted/updated row are also deleted and updated.
√ 32 CPU and 64GB Memory Support: SQL Server 2000 Enterprise Edition running under Windows 2000 DataCenter can support up to 32 CPUs and up to 64GB of physical memory (RAM) on a computer.
√ XML Support: SQL Server 2000 can use XML to insert, update, and delete values in the database, and the database engine can return data as Extensible Markup Language (XML) documents

What are the different ways of moving data between databases in SQL Server?

There are lots of option available; you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

What is LOCK escalation?

Lock escalation is the process of converting of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

Can we suggest locking hints to SQL SERVER ?

We can give locking hints that helps you over ride default decision made by SQL Server. For instance, you can specify the ROWLOCK hint with your UPDATE statement to convince SQL Server to lock each row affected by that data modification. Whether it's prudent to do so is another story; what will happen if your UPDATE affects 95% of rows in the affected table? If the table contains 1000 rows, then SQL Server will have to acquire 950 individual locks, which is likely to cost a lot more in terms of memory than acquiring a single table lock. So think twice before you bombard your code with ROWLOCKS.

What are the different locks in SQL SERVER ?

Depending on the transaction level six types of lock can be acquired on data :-
Intent The intent lock shows the future intention of SQL Server's lock manager to acquire locks on a specific unit of data for a particular transaction. SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated. Intent locks come in three flavors: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
IS locks indicate that the transaction will read some (but not all) resources in the table or page by placing shared locks.
IX locks indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks.
SIX locks indicates that the transaction will read all resources, and modify some(but not all) of them. This will be accomplished by placing the shared locks on the resources read and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at one time; therefore, SIX locks prevent other connections from modifying any data in the resource (page or table), although they do allow reading the data in the same resource.
Shared Shared locks (S) allow transactions to read data with SELECT statements. Other connections are allowed to read the data at the same time; however, no transactions are allowed to modify data until the shared locks are released.
Update Update locks (U) are acquired just prior to modifying the data. If a transaction modifies a row, then the update lock is escalated to an exclusive lock; otherwise, it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time. Using update locks prevents multiple connections from having a shared lock that want to eventually modify a resource using an exclusive lock. Shared locks are compatible with other shared locks, but are not compatible with Update locks.
Exclusive Exclusive locks (X) completely lock the resource from any type of access including reads. They are issued when data is being modified through INSERT, UPDATE and DELETE statements.
Schema Schema modification locks (Sch-M) are acquired when data definition language statements, such as CREATE TABLE, CREATE INDEX, ALTER TABLE, and so on are being executed. Schema stability locks (Sch-S) are acquired when store procedures are being compiled.
Bulk Update Bulk update locks (BU) are used when performing a bulk-copy of data into a table with TABLOCK hint. These locks improve performance while bulk copying data into a table; however, they reduce concurrency by effectively disabling any other connections to read or modify data in the table.

What are different transaction levels in SQL SERVER?

Transaction Isolation level decides how is one process isolated from other process.Using transaction levels you can implement locking in SQL SERVER.
There are four transaction levels in SQL SERVER :-
READ COMMITTED The shared lock is held for the duration of the transaction, meaning that no other transactions can change the data at the same time. Other transactions can insert and modify data in the same table, however, as long as it is not locked by the first transaction.
READ UNCOMMITTED No shared locks and no exclusive locks are honored. This is the least restrictive isolation level resulting in the best concurrency but the least data integrity. REPEATABLE READ This setting disallows dirty and non-repeatable reads. However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be interpreted by the transaction.
SERIALIZABLE This is the most restrictive setting holding shared locks on the range of data. This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed.

)If locking is not implemented what issues can occur ?

Following are the problems that occur if you do not implement locking properly in SQL SERVER. Lost Updates Lost updates occur if you let two transactions modify the same data at the same time, and the transaction that completes first is lost. You need to watch out for lost updates with the READ UNCOMMITTED isolation level. This isolation level disregards any type of locks, so two simultaneous data modifications are not aware of each other. Suppose that a customer has due of 2000$ to be paid. He pays 1000$ and again buys a product of 500$. Lets say that these two transactions are now been entered from two different counters of the company. Now both the counter user starts making entry at the same time 10:00 AM. Actually speaking at 10:01 AM the customer should have 2000$-1000$+500 = 1500$ pending to be paid. But as said in lost updates the first transaction is not considered and the second transaction overrides it. So the final pending is 2000$+500$ = 2500$.....I hope the company does not loose the customer.
Non-Repeatable Read Non-repeatable reads occur if a transaction is able to read the same row multiple times and gets a different value each time. Again, this problem is most likely to occur with the READ UNCOMMITTED isolation level. Because you let two transactions modify data at the same time, you can get some unexpected results. For instance, a customer wants to book flight, so the travel agent checks for the flights availability. Travel agent finds a seat and goes ahead to book the seat.While the travel agent is booking the seat, some other travel agent books the seat. When this travel agent goes to update the record, he gets error saying that "Seat is already booked". In short the travel agent gets different status at different times for the seat. Dirty Reads Dirty reads are a special case of non-repeatable read. This happens if you run a report while transactions are modifying the data that you're reporting on. For example there is a customer invoice report which runs on 1:00 AM in afternoon and after that all invoices are sent to the respective customer for payments. Let us say one of the customer has 1000$ to be paid. Customer pays 1000$ at 1:00 AM and at the same time report is run. Actually customer has no money pending but is still issued an invoice.
Phantom Reads Phantom reads occur due to a transaction being able to read a row on the first read, but not being able to modify the same row due to another transaction deleting rows from the same table. Lets say you edit a record in the mean time somebody comes and deletes the record, you then go for updating the record which does not exist....Panicked.
Interestingly, the phantom reads can occur even with the default isolation level supported by SQL Server: READ COMMITTED. The only isolation level that doesn't allow phantoms is SERIALIZABLE, which ensures that each transaction is completely isolated from others. In other words, no one can acquire any type of locks on the affected row while it is being modified.

What is the difference between DELETE TABLE and TRUNCATE TABLE commands?

Following are difference between them :-
√ DELETE TABLE syntax logs the deletes thus make the delete operation slow. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table so TRUNCATE table is faster as compared to delete table.
√ DELETE table can have criteria while TRUNCATE can not.
√ TRUNCATE table can not trigger.