Retrieving Query Result in a Tree Structure in MS SQL SERVER using Common Table Expression (CTE)

A common table expression or "CTE" is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table. A derived table exists only for the duration of the query and is not stored as an object. But the problem with derived tables is the lack of self referencing. Here is where the importance of CTE comes in. A CTE can be self-referencing and it can be referenced multiple times in a query. Here we discuss about the recursive nature of CTE i.e to create a recursive query. Returning hierarchical data is a common use of recursive queries i.e for example suppose we want to create a general ledger(Example query is given at the end of this section). For now lets have a look at the recursive CTE. The following are the components of a recursive CTE:

WITH cte_name [(col1,col2,......,coln)] (Here the column names are optional)
AS
(
cte_query (Anchor member defined here)
UNION ALL
cte_query(recursive member referencing cte_name here)

)
select statement to retrieve the result

A recursive CTE consists of three elements:

1). Invocation of the routine
This part consist of one or more cte_query definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. This query definitions are referred to as anchor elements because they form the base resultset of CTE.

2)Recursive invocation of routine
The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.

3)Termination check.
The termination check is implicit i.e recursion stops when no rows are returned from the previous invocation.

The semantics behind CTE is as follows:

a)Splitting the CTE expression into anchor and recursive members.
b) Then we run the anchor member creating the first invocation or base result, say BR0 .
c) Now we run the recursive member with BRi(where i=0 to n) as input and BRi+1 as output.
d) Repeat step c until an empty set is returned.
e)Return the resultset this is BR0 UNION ALL BRn

Example Query

WITH MyLedger(BranchID, AccParentID, AccID) AS
(
SELECT DISTINCT BranchID,AccParentID, AccID
FROM GeneralLedger
WHERE ActiveFlag='Y'
UNION ALL
SELECT e.BranchID,e.AccParentID, e.AccID
FROM GeneralLedger e
INNER JOIN MyLedger d
ON e.AccParentID = d.AccID AND E.BranchID=d.BranchID AND ActiveFlag='Y'
)

SELECT * FROM MyLedger

BranchID-denotes the branch.
AccParentID- denotes the parent group here.
AccID-denotes the account id.
The above result displays a General Ledger in a Tree Structure branch wise. Here we form our base result from the table GeneralLedger.

Happy Programming...... Enjoy!!!!!!!!!!!!!!!!!!!!!!!!!

Retrieve Query result(records) as Comma Separated Value in SQL Server

Its been a while that i've posted an article here. This time i am sharing a technique by which you can retrieve result from MS SQL Server in the form of Comma Separated Values(CSV). The idea is to display all the states from a particular country in CSV format.For this purpose we first create a table and name it "Country". Then we create two fields in the table, one for holding the country name and the second for holding the state name, say "countryname" and "state" respectively. After Creating the above table copy and paste the below code in your SQL Query Analyzer and Run it. Now you can see the magic!!. What is the idea behind this query?. Lets examine it.

DECLARE @s varchar(3500)

SET @s=''

SELECT @s=state +','+@s FROM Country WHERE countryname='INDIA'

SELECT @S

The keyword DECLARE is used to declare a variable named s and declare its datatype as varchar which can hold upto 3500 chars. Then we use the SET keyword to null string because if we dont assign it to null string the result will also be null. Now we use SELECT keyword to assign the result set into @s. Then at last we print our result using the last SELECT Keyword.

I know what i presented here is not a "Big thing", But every one knows nothing is so simple ever..........................

SQL Server Date Time Formats using Query

We can format the sql server 'datetime' field into a specific date time format. SQL Server provides this functionality through an inbuilt function called CONVERT(). For Example you can get the date format in dd/mm/yy by using the following query.

Select Convert(Varchar(10),GetDate(),3) from your_table_name.

Look at the third integer parameter you are passing(i.e. 3), which does the formating part.

Following are the values and thier corresponding formats :

1----------------- mm/dd/yy
2-----------------yy.mm.dd
3-----------------dd/mm/yy
4-----------------dd.mm.yy
(And so on. Other format numbers range from 100-114, 120,121,126,130,131) .Try it n see the results yourself.....enjoy

Bulk inserting values from one table to another table

There may be situations when you need to copy records from one table to another on a specific condition. This is can be achieved by simply using the insert and select combination in your sql query. For example the following query copies the names of employees(emp table) who are managers(Manager table).

insert into Manager(name) select name from emp where designation ='mngr' .

NB:The number of fields you are selecting must be equal with the inserting fields.

Enable and disable Trigger in SQL Server

Disable Triggers instead of dropping them.
================================

Business rules in table often expect your application to update the table one row at a time. Also some triggers generate error when the code in the trigger assigns to a local variable returned by selecting a column from the inserted virtual table. The assignment fails if you are updating multiple rows because the inserted table contains more than one row so the sub query returns more than a single value. Multiple updates need special handling in such scenario. Developers often wind up dropping a trigger before multi- row updates then creating them later.

But this scenario can be overcome by disabling trigger

Alter Table Mytable Disable Trigger mytrigger

You can enable Trigger using
Alter Table Mytable Enable Trigger mytrigger

2 Simple steps to speed up your SQL Server execution

1.Replace "Count (*) " with "exists" when checking for existence

If(Select(count(*) from orders where shipvia=3>0)

The execution Plan shows sqlserver has to read whole rows in orders table ,You can achieve same result by

If exists(select * from orders where shipvia=3>0)

You will see a major speed improvement

2. In sub queries can be replaced with Left outer join

Eg:
Select * from Customers where customer rid not in(select customer rid from order)
Replace with outer join

Select c.* from Customers c left outer join orders o On o.customerid=c.customerid where o.customerid is null .