SQL Server 2005 Questions and Answers

Q. What is CTE (Common Table Expression)?
Ans: CTE is a temporary Table created from a simple SQL query. You can say it's a view. Below is a sample CTE created "PurchaseOrderHeaderCTE" from "PurchaseOrderHearder"

With PurchaseOrderHeaderCTE (Orderdate, status) As
(
SELECT order date, status
FROM Purchasing.PurchasingOrderheader

)

SELECT * FROM PurchasingOrderheader

The WITH Statement define the CTE and later using the CTE name I have display the CTE data.

Q. Why would you use CTE rather than simple View?
With CTE you can use a recursive query with CTE itself. That's not possible with view.

Q. What is TRY/CATCH block in T-SQL?
NO I am referring to .NET TRY/CATCH block, this is a new way of handling error in SQL Server. For instance in the below T-SQL code any error during delete statement is caugh and the neccessary error information is displayed:

BEGIN TRY
DELETE table 1 WHERE id=122
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER As ErrNum,
ERROR_SEVERITY ( ) As ErrSev,
ERROR_STATE ( ) As ErrSt,
ERROR_MESSAGE ( ) As ErrMsg;
END CATCH

Q. What is PIVOT feature in SQL Server?
PIVOT feature convert data row to column for better analytical view. Below is a sample PIVOT filed using CTE. OK the first section is the CTE which is the input and later PIVOT is applied Over it:

WITH PURCHASEORDERHEADERCTE(Orderdate, Status, Subtotal) As
(

select year (orderdate), status, isnull(SubTotal, 0) from purchasing. PURCHASEORDER HEADER

)

Select Status as OrderStatus, isnull ([2006], 0 ) as 'YR 2007', isnull([2007], 0) as 'Yr 2007' from PURCHASEORDERHEADERCTE Pivot (sum(subtotal) for Orderdate in ([2006], [2007] ) ) as pivoted
You can see from the above SQL the top WITH statement is the CTE supplied to the PIVOT. After that PIVOT is applied on subtotal and orderdate. You have to secify in what you want the pivot (here it is 2006 and 2007). So below is the output of CTE table.

(No column name) Status (No column Name)
1 2006 4 201.4
2 2006 1 272.1015
3 2006 4 8847.30
4 2006 3 171.0765
5 2006 4 20397.30
6 2006 4 14628.075
7 2006 4 58685.55
8 2006 4 693.378
9 2007 4 694.1655
10 2007 4 1796.0355
11 2007 4 501.1965

CTE ouput
After the PIVOT is applied you can see the rows now grouped column wise with the subtotal assigned to each. You can summarize that PIVOT summarizies your data in cross tab format.

Order Status Yr 2006 Yr 2007
1 3 171.0765 383552.904
2 1 272.1015 0.00
3 4 103452.643 3842580.126

Q. What is UNPIVOT?
It's exactly the vice versa of PIVOT. That means you have a PIVOTED data and you want to UNPIVOTED.

Q. What are RANKING functions?
they add columns that are calculated based on a ranking algorithm. These functions include ROW_NUMBER( ), RANK( ), DENSE_RANK(), and NTILE().

Q. What is ROW_NUMBER()?
The ROW_NUMER() function adds a column that display a number corresponding the row's position is the query result. If the column that you specify in the OVER clause is not unique, it still produces an incrementing column based on the column specified in the OVER Clause. YOu can see in the figure below I have applied ROW_NUMBER function over column col2 and you notive the incrementing numbers generated.

Select col1, col2,
row_number(), over (order by col2) as ROW_NUMER from table _1

col1 col2 RowNumber
1 1 2 1
2 2 3 2
3 4 3 3
4 4 3 4
5 5 6 5
6 5 6 6

Q. What is RANK()?
The Rank() function works much like ROW_NUM() function in that is numbers records in order. When the column specified by the ORDER BY clause contains unique values, then ROW_NUMBER() and RANK() produce identical results. They differ in the way they work when duplicate values are contained in the ORDER BY expression. ROW_NUMBER will increment the number by one every record. regardless of duplicates. RANK() produces a single number for each value in the result set. You can see duplicate value it does not increment the TOP number.

Select col1, col2, RANK(), over (order by col2) as ROW_NUMER from table _1

col1 col2 ROW NUMBER
1 2 1
2 3 2
4 3 3
4 3 4
5 6 5

Q. What is DENSE_RANK()
DENSE_:RANK() works the same way as RANK() does but eliminates the gaps in thr numbering. When I say GAPS you can see in previous results it has eliminated 4 and 5 from the count because of the gap in between COL@. BUT for dense_rank it overlooks the gap.

Select col1, col2, Dense_rank(), over (order by col2) as ROW_NUMER from table _1
col1 col2 RowNumber
1 2 1
2 3 2
4 3 2
4 3 2
5 6 3
5 6 3

Q. What is NTILE()?
NTILES() breaks the result set into a specified number of groups and assigns the same number to each record in a group. OK NTILE just groups depending on the number given or you can say devide the data. For instance I have said to NTILE it to 3. It has 6 total rows so it grouped in number of 2.

Select col1, col2,NTILE(3) over (order by col2) as ROW_NUMER from table _1

col1 col2 RowNumber
1 2 1
2 3 1
4 3 2
4 3 2
5 6 3
5 6 3

Q. (DB) 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 advance of insecute code on a system connected to the Internet. by passing the firewall. SQL injection attacks are used to steal information from the database from which the data would normally not be avaiable and 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's we inject SQL which can be relatively dangerous for the database. Exammple:

SELECT Email, passwd, login_id, Full_name
FROM Member
WHERE email = 'X'

Now some body does not put "x" as the input but put "x; DROP TABLE members;". So the actual SQL will execute is:

SELECT Email, passwd, login_id, Full_name
FROM Member
WHERE email = 'X' ; DROP TABLE members