DERIVED TABLE BASICS IN SQL SERVER 2005

A derived table is one that is created runtime using the select statement and referenced by the outer select statement like a normal table or a view.Derived tables are not saved permanently. Derived tables exists in memory of the SQL SERVER and can only be referred by the outer select in which they are created.

Example of a simple derived table is given below.

Select * from employee ( select top 5 * from employee ) as DerivedTable.

The inner query select produces a derived table and replaces a regular table.Important thing to notice here is whenever you are using a derived tables is that you must always use an alias. If you remove the alias the system will give you an error like incorrect syntax near ‘)’

You can also use derived tables to implement the database paging.Example is given below.

Declare @StartRowNum INT , @EndRowNum INT

SET @StartRowNum = 100

SET @EndRowNum  = 120

SELECT * FROM

(

SELECT id, EmpName , Salary , Row_number() over ( order by select 2 ) AS RowNumber From Employee

)

Employee

Where RowNumber >= @StartRowNum AND RowNumber <= @EndRowNum

You can also achieve the above functionality with common types expressions.( CTE )

Pawan Kumar

Pawankkmr@hotmail.com

RECOMPILE OPTION IN STORED PROCEDURES IN SQL SERVER 2005

Reasons when recompilation occurs are :-
Inserting column to a table or view.
Dropping column to a table or view.
Inserting constraints to a table.
Dropping constraints to a table.
Inserting an indexing to a table or view.
Inserting a trigger to a table.
Dropping a trigger to a table.

If the database is changed by any reason like adding new indexes or modifying data in the indexed columns , the original query plans should be optimized again by recompiling them.

This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server 2005 is restarted.It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted.

In this situation, it can be useful to force the stored procedure to recompile the next time it executes.

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.

SQL Server provides three ways to force a stored procedure to recompile:

1 ) The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.

2 ) Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled each time it is executed.
If you only want individual queries inside the stored procedure to be recompiled, rather than the entire stored procedure, specify the RECOMPILE query hint inside each query you want recompiled. This behavior mimics SQL Server’s statement-level recompilation behavior noted above, but in addition to using the stored procedure’s current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when compiling the statement. Use this option when atypical or temporary values are used in only a subset of queries belonging to the stored procedure. For more information, see Query Hint (Transact-SQL).

3 ) You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.

Examples are given below for recompilation.

1. CREATE PROCEDURE EmployeeData

(

@EmpName varchar(50)

)
WITH RECOMPILE
AS
SELECT * FROM Employee WHERE EmployeeName = @EmpName

2. EXEC  dbo.EmployeeData  ‘Rahul’  WITH RECOMPILE

Pawan Kumar

Pawankkmr@hotmail.com

ADVANTAGES OF STORED PROCEDURES

1. They are PRECOMPILED which is executed as a unit.

2. They provide you faster execution.

3.You don’t have to recompile it again and again.

4.They are less prone to errors as they are written by experienced programmers.

5.Same business  logic can be used across multiple applications.

6.It reduces network congestion.

7.It provides better security.Only DBA owner has permission to modify and make changes to stored procedures. The logic of the stored procedures are also not revealed to others.

8. SP are compiled and their execution plan is cached in SQL SERVER memory and used again to when the same SP is executed again. This means when you execute the SP second time it will take less amount of time as compared to the time taken at the first time.

Other Notes:

1. You have to execute a stored procedure but you cannot select from a stored procedure.

2. Stored Procedures are logical components of SQL Server.

3.Stored Procedures by default does not return any value. They only return whether the Stored procedure has been successfully executed or not.

4.Stored procedures can have two types to parameters. Input and output parameters.

5.Stored Procedures also have recompile option.If we use this option then every time we execute a stored procedure so first it will first compile the stored procedure first then execute it. This is a bad practice to use this option.It is not recommended.

6.If you want to return some thing from the stored procedure then you have to use stored procedures with output parameters.

Pawan Kumar

Pawankkmr@hotmail.com

There are 12 rules formulated by E.F Codd for RDBMS in 1970.If a RDBMS satisfies all these 12 rules then full benefits of the relational database results can be obtained.

The 12 rules are having the following main points.

1.Information Representative : In a relational database model , all information should be explicitly and logically represented by entering the data values in the form of tables.The information such as  view and column names should be in the table form.Data stored in data dictionary should be in the tabular form.

2.Guaranteed Access: Every value of data item must be logically addressable by using a combination of table-name , primary key and the column name.

3.Systematic treatment of NULL Values: In a relational database management null values should be supported for the representation of missing and inapplicable information only.The DBMS must have a consistent method for representing null values. Eg- NULL values for character data for numeric it must be different data from a strings of blanks , distinct from zero or any other non numeric values.

4.Database Description Rule : The description of a database is stored and maintained in the form of tables. This allows the users with appropriate authority to query information using the same language. This implies that a data dictionary should be present within the RDBMS that is constructed out of tables and/or views that can be examined using SQL.

5.Comprehensive data sub language : The RDBMS must be completely manageable through its own extension of SQL. The SQL should support DD , Views , DM , integrity , constraints and transactions boundary.

6.View Updating Rule: All the views thats are theoretically update able can updated through the system.

7.High level update , Insert , Delete :- An RDBMS must do more than just be able retrieve relational data sets. It must also be possible to insert , update and delete data items from relational set.

8.Physical Data Independence : Changes made to physical storage or access methods do not require changes to be made to the application programs used to manipulate data in tables.

9.Logical Data Independence : Application program should not be affected by the changes made to the base tables. Changes made to the table should not require changes to be made to application programs , operating on the table.

10.Integrity Independence : ( Reliable , Correct , Consistent ) means integrity hence for that we require constraints. The database language must be capable of defining integrity rules.The integrity constraints must be store-able in the catalog and not in the application programs.

11.The Distribution Rule : The application programs and ad hoc requests are logically unaffected when data is first distributed or when it is redistributed.Database systems built on the relational framework are also well suited for todays client server database design.

12. Non Sub-Version : It must not be possible to by pass the integrity rules defined through the database language by using the lower level language.

Pawan Kumar

Pawankkmr@hotmail.com