Posts

Showing posts from October, 2010

Apply Clause in SQL

Have you were felt need for passing outer query field value to inner or Sub Query as a where clause or join and have sub query field in outer Select. With conventional SQL it’s not possible. This can be done using SQL SERVER 2005 and 2008 new feature called as Outer Apply. It works like left outer join with outer Query field in sub Query. Sample: Note: In this sample I am trying to get pervious total invoice value for that customer as on bill date Selec t Srno,Amount ,DocDT, PreAmount From tranSales sl Outer Apply ( Select SUM(Amount) PreAmount From tranSales PrSl where COID = 1 and PrSl.DocDt sl.DocDt and PrSl.CustomerID = sl.CustomerID Group by CustomerID ) PreSl where COID = 1 and sl.CustomerID = 1 Order by SRNO I am sure this will help lot of peoples. Switin Kotian

Temp Table Vs Temp Variable

Temp table is valid for a session (Connection). Defination : Create table #temp (Name as Varchar(100)) Insert into #temp (Name) Value ('Switin'); It can be used in Into Statement Select * Into #Temp From SomeTable Go 1.If you try to create one more Temp Table with Same Name with same Connection Exceptation will be raised 2.Best Part you can create Primary Key, Indexes, Constraints. As we do for normal tables 3.As soon as connection is dead the Temp Table is Dropped automatically by SQL SERVER . Cool Part Temp Variable are Tables but for the Scope. They are Scope driven Defination : Declare @temp table(Name Varchar(100)) Insert into @temp (Name) Value ('Switin'); Go Declare @temp table(Name Varchar(100)) Insert into @temp (Name) Value ('Switin'); Above mentioned syntax will not generate any error as after first creation of table scope of variable end when Go is Called. Only Weak point in Table Variable is SELECT INTO statment cannot be used Mostly used in SP, Tr...

Database Connection Strings Diffcult to Remember

Hi, To All Database Connection string is one thing i find diffcult to remember for different datasource. I found one web site which provides ready to use any datasource Connection strings. http://www.connectionstrings.com/ I thought this may help you to. Switin Kotian