sql server faq link

http://dotnetguts.blogspot.com/2007/08/faq-on-view-in-sql-server.html

TRY…CATCH and ERROR Handling-Sql Server 2005

Syntax:
BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH



The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

Example of TRY…CATCH:
BEGIN TRY
DECLARE @X INT
---- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks'
Above code will return following result:

Error Detected
Command after TRY/CATCH blocks

If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.

Limitation of TRY…CATCH:

• Compiled errors are not caught.
• Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY…CATCH will be not effective)
• Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.

For errors that are not trapped, SQL Server 2005 passes control back to the application immediately, without executing any CATCH block code.

Functions to be used in CATCH block are :

• ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
• ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
• ERROR_STATE: returns the state number of the error.
• ERROR_LINE: returns the line number where the error occurred.
• ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
• ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

Similar example of TRY…CATCH which includes all the ERROR functions:
USE AdventureWorks;
GO
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO


Table Variables

Q1: Why were table variables introduced when temporary tables were already available?

A1: Table variables have the following advantages over temporary tables:

• As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
• Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
• Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

Q2: What does it mean by saying that table variables result in fewer recompilations of a stored procedure than when temporary tables are used?

A2: The following article discusses some reasons when stored procedures are recompiled:

243586 (http://support.microsoft.com/kb/243586/ ) Troubleshooting stored procedure recompilation

The "Recompilations Due to Certain Temporary Table Operations" section also lists some requirements to avoid such as a recompilation because of temporary tables. These restrictions do not apply to table variables.

Table variables are completely isolated to the batch that creates them so no 're-resolution' has to occur when a CREATE or ALTER statement takes place, which may occur with a temporary table. Temporary tables need this 're-resolution' so the table can be referenced from a nested stored procedure. Table variables avoid this completely so stored procedures can use plan that is already compiled, thus saving resources to process the stored procedure.

Q3: What are some of the drawbacks of table variables?

A3: These are some of the drawbacks as compared to temporary tables:

• Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
• Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
• The table definition cannot be changed after the initial DECLARE statement.
• Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
• CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
• You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Q5: Do I have to use table variables instead of temporary tables?

A5: The answer depends on these three factors:

• The number of rows that are inserted to the table.
• The number of recompilations the query is saved from.
• The type of queries and their dependency on indexes and statistics for performance.

In some situations, breaking a stored procedure with temporary tables into smaller stored procedures so that recompilation takes place on smaller units is helpful.

In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure.

What is Cross Join and in which scenario do we use Cross Join?

CROSS JOIN: Cross join is used to return all records where each row from first table is combined with each row in second table.

Cross join is also called as Cartesian Product join.

The cross join does not apply any predicate to filter records from the joined table. Programmers can further filter the results of a cross join by using a WHERE clause.

For Example:- We have following two tables.

Look at the "Product" table:

Note that the "P_Id" column is the primary key in the "Product" table.

Next, we have the "SubProduct" table:

Note that the "Sub_Id" column is the primary key in the "SubProduct" table.

There are lots of scenarios where we use the cross join(permutation and combination), below are the example of hotel where customer's gets the detail of combined product and its total cost, So that it is easy to select their respective choice.

Query:- select
Product.ProductName,SubProduct.SubProductName,(Product.Cost+SubProduct.Amount)as
TotalCost from Product cross join SubProduct

The output look like below:

How does index makes search faster?

For better understanding, let us consider a simple search example which shows differences between a table, declared with index and without index.

Let's first see an example for a table which is created without declaring an index and look how exactly the SQL search engine will perform action. Below diagram will give u better idea…





In the above example, SQL search engine will search from initial till it finds the respective record and once the record is found it will basically display the record. Further, When we create an index on any column of a table then the large data get divided like following B-Tree diagram, so that search becomes easier and faster.


B-tree structure of a SQL Server index

For Example-

Suppose we have to search value 25 in an indexed column, the query engine will first look in the “Root Node” to determine which node to refer in the “Branch Nodes”. In the above example first “Branch Node” has Value 1 to 20 and the second “Branch Node” has Value 21 to 40, so the query engine will go to the second “Branch Node” and will skip the first “Branch Node” as we have to search Value 25. Same like “Branch Nodes” the query engine will operate the “Leaf Node” to retrieve respected result.

What are difference between Cluster index and Non-Cluster index?

Both of these indexes uses "B-tree" structure but in Cluster index the "Leaf Node" actually points the physical data, but in Non-Cluster index it point’s to the "Row ID" and then the "Row ID" points to the "Leaf Node" of Cluster Index.

Below is the diagram of Cluster and Non-Clustered index.

Difference between Stored Procedure and Function?

• Function are compiled and executed at run time.
• Stored Procedure are stored in parsed and compiled format in the database.
• Function cannot affect the state of the database which means we cannot perform CRUD operation on the database.
• Stored Procedure can affect the state of the database by using CRUD operations.
• Store Procedure can return zero or n values whereas Function can return only one value.
• Store Procedure can have input, output parameters for it whereas functions can have only input parameters.
• Function can be called from Stored Procedure whereas Stored Procedure cannot be called from Function.

SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table
The following solution is for getting 6th highest salary from Employee table ,

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

You can change and use it for getting nth highest salary from Employee table as follows

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

Same example converted in SQL Server 2005 to work with Database AdventureWorks.
USE AdventureWorks;
GO
SELECT TOP 1 Rate
FROM (
SELECT DISTINCT TOP 4 Rate
FROM HumanResources.EmployeePayHistory
ORDER BY Rate DESC) A
ORDER BY Rate
GO