1. How does a stored procedure differ from a T-SQL batch?
2. Where is a stored procedure stored?
3. What is the scope of the stored procedure?
4. What is the scope of the @@ERROR system function?
5. What is a nested stored procedure?
6. What are the advantages and disadvantages of using stored procedures?
7. How is a trigger different from a stored procedure? From a T-SQL batch?
8. What events can a trigger respond to?
9. What are the two virtual tables SQL Server maintains for triggers?
10. What does the INSTEAD OF trigger do?
11. What is a SQL Server cursor?
12. What are the four different cursor types?
13. What is concurrency and how does it apply to cursors?
14. What is an index in the context of SQL Server?
15. What is the difference between a clustered and a non-clustered index?
16. How many clustered indices can you define for one table? Non-clustered?
17. Would it be a good idea to create an index on a table that always con-tains
10 records? Why or why not?
18. What columns would you use for a non-clustered index?
19. What are the four types of integrity?
20. What types of integrity are enforced by a foreign-key constraint?
21. When can you add the CHECK constraint to a table?
22. In order for a RULE to be functional what do you need to do after it is
23. What is a NULL in SQL Server? How does it differ from zero?
24. What is a transaction?
25. What do the letters in the acronym ACID stand for?
26. What are explicit and implicit transactions?
27. What are the two types of concurrency?
28. What are the four isolation levels?
29. What is locking escalation? When does it occur?
30. What is a deadlock? How do you avoid deadlocks?
1. A stored procedure is a database object compiled and stored under a
unique name and is stored in SQL Server; it can have input and output
parameters as well as a return value. A T-SQL batch is stored as a file and
must be executed through one of the SQL Server interfaces; it does not
have input or output parameters and it is always interpreted.
2. It is stored in the Stored Procedures collection of a particular database on
SQL Server. Stored procedures global in their scope reside in the Master
3. The scope of the stored procedure is usually within the database in which
it is created; stored procedures prefixed with sp_ and stored in the
Master database are global for the SQL Server instance.
4. It always has a global scope.
5. Calling a stored procedure from within another stored procedure makes it
a nested stored procedure.
6. Stored procedures usually execute faster than T-SQL batches and consume
fewer resources; they are maintained by SQL Server; they enable you
to create reusable libraries; also they give you benefits of structured
However, they shift control to SQL Server from the client application and
are usually more difficult to modify than batches.
7. Unlike a stored procedure, a trigger is tied to a specific table and is exe-cuted
only in response to certain actions performed on this table; you
cannot call a trigger directly; and a trigger is compiled.
8. Any events that modify data: A trigger can be set to respond to an
INSERT, UPDATE, or DELETE event.
9. DELETED and INSERTED.
10. An INSTEAD OF trigger is executed instead of the T-SQL statement that
triggered the action.
11. A programming object (structure) established on the result-set base. It
enables you to manipulate records in the set row by row.
12. Static, dynamic, forward-only, and keyset-driven.
13. Concurrency refers to the visibility of the data set, which may be accessi-ble
by several users at the same time; you set the cursor’s concurrency in
the cursor declaration or client application.
14. An index speeds up search operations by creating a system of pointers to
the actual data. It is maintained separately from the table it indexes.
15. A clustered index physically re-organizes data in the table; a non-clustered
index just maintains pointers to the actual locations of the records.
16. You can define only one clustered index and up to 249 non-clustered
17. No. For a small amount of data, a table scan is more efficient than an
18. Columns used in JOIN queries, aggregate functions, GROUP BY, or ORDER
19. Entity, domain, referential and user-defined.
20. Domain integrity (range of values) and referential integrity (preserves
relationship among tables).
21. You can define the CHECK constraint during the creation of the table, or
later with the ALTER TABLE statement.
22. You need to bind it to a column or user-defined type.
23. NULL indicates an absence of data; unless your server is set to compare
NULLs, one NULL is never equal to another. Zero is an actual value repre-sented
by a number.
24. A SQL Server transaction is a collection of T-SQL statements that either
executes as a whole or fails and leaves data unchanged.
25. Atomicity, consistency, isolation and durability.
26. Any transaction that you explicitly start (BEGIN TRANSACTION), end (END
TRANSACTION), or commit (COMMIT TRANSACTION) is an explicit transac-tion.
Implicit transactions are those that SQL Server automatically starts
when it encounters one of the following T-SQL statements:
27. Optimistic and pessimistic.
28. Read uncommitted, read committed, repeatable read, and serializable. 29. Lock escalation converts fine-grained locks into coarser-grained locks (for
example, row-level locking to table-level locking) when SQL Server figures
out that another lock will use less system resources.
30. A deadlock refers to a situation wherein a process (transaction) has
obtained a lock on a resource and tries to get a lock on a second resource
while another process tries to simultaneously obtain a lock on that same
resource and keep a lock on some third resource; neither of the processes
can get the lock on the resource, nor can they commit or abort.
To avoid deadlocks applications should access database objects in a spe-cific
order, user interaction during transactions should be eliminated,
transactions should be kept as short as possible, and you should use the
lowest isolation level possible.