SQL Server : Programmability

What are the advantages of using Stored Procedures?

• Stored procedure can reduced network traffic and latency, boosting application performance.
• Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
• Stored procedures help promote code reuse.
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
• Stored procedures provide better security to your data.

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

• Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. 
• Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem.
•  A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps.
•  Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. 
• You can nest stored procedures and managed code references up to 32 levels.

How do you optimize stored procedures in SQL Server 2005

• Use as much as possible WHERE clause filters. Where Clause is the most important part for optimization 
• Select only those fields which really require. 
• Joins are expensive in terms of time. Make sure that use all the keys that relate the two tables together and don't join to unused tables, always try to join on indexed fields. The join type is important as well (INNER, OUTER).

What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?

1024

What is a difference between stored procedures and user-defined functions

Characteristics	Stored Procedure	Function
Parameters	Procedures can have input/output parameters	Functions can have only input parameters.
O/P	Procedure can return zero or n values	Function can return one value which is mandatory.
DMLs	Procedure allows select as well as DML statement	Function allows only select statement in it.
Calls	Functions can be called from procedure	Procedures cannot be called from function
Exception Handling	Exception can be handled by try-catch block in a procedure	try-catch block cannot be used in a function.
Transaction Support	Procedures support transaction Management.	Functions don’t support transaction management.
Embed in WHERE/HAVING/SELECT	Procedures cannot be utilized in these statement	Function can be embedded in a these statement.

 

How We can Get List of Store Procedures?

select * from Sys.Objects where Type='p'

·

What is a "trigger"?

• Microsoft SQL Server includes support for a special type of stored procedure called a trigger.
•  A trigger is a stored procedure that executes whenever an update, delete or insert statement is executed against a table or a view.
• Triggers are created in order to enforce integrity rules in a database. In other words, you can associate a trigger with a table in such a way that it fires whenever a change is made to the contents of the table.
• Basically, trigger is a set of SQL statements that execute in response to a data modification/retrieval event on a table.
• A trigger is a solution to the restrictions of a constraint. For instance :

Ø  A database column cannot carry PSEUDO columns as criteria where a trigger can.

Ø  A database constraint cannot refer old and new values for a row where a trigger can.

• There are

Ø   table triggers,

Ø  schema triggers.

Ø  and database triggers

These can be made to fire when new objects are created, when a user logs in, when the database shutdown etc.

Table level triggers can be classified into row and statement level triggers and those can be further broken down into

Ø  before triggers

Ø  after triggers.

• Triggers are generally used to implement

Ø   business rules,

Ø  auditing.

Ø  extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

How many types of triggers are there?

There are four types of triggers.

• Insert 
• Delete 
• Update 
• Instead of

What is a SQL View?

• A view can be thought of as either a virtual table or a stored query.
•  The data accessible through a view is not stored in the database as a distinct object.
• What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view.
•  A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions:

Ø  Restrict a user to specific rows in a table. For example, allow an employee to see only the rows recording his or her work in a labor-tracking table.

Ø  Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.

Ø  Join columns from multiple tables so that they look like a single table.

Ø  Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.

• Views are created by defining the SELECT statement that retrieves the data to be presented by the view.
•  The data tables referenced by the SELECT statement are known as the base tables for the view.

 In this example, titleview in the pubsdatabase is a view that selects data from three base tables to present a virtual table of commonly needed data:

CREATE VIEW titleview

AS

SELECT title, au_ord, au_lname, price, ytd_sales, pub_id

FROM authors AS a

     JOIN titleauthor AS ta ON (a.au_id = ta.au_id)

     JOIN titles AS t ON (t.title_id = ta.title_id)

You can then reference titleview in statements in the same way you would reference a table:

SELECT *

FROM titleview

• A view can reference another view.
• Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view, for example:

UPDATE titleview

SET price = price * 1.10

WHERE pub_id = '0736'

GO

• Views are called virtual tables because the result set of a view is not usually saved in the database. The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time.

What is cursor in SQL Server?

• 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, its like recordset in the ASP and visual basic.
  
  Typical syntax of cursor is
  
  

DECLARE @fName varchar(50), @lName varchar(50)

DECLARE cursorName CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM myTable

OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

WHILE @@FETCH_STATUS = 0

BEGIN

   FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

END

CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

•