Better Understanding

/*APPLY*/

/*Today I want to tell the one very good new feature of SQL 2005 -- APPLY (CROSS APPLY/OUTER APPLY)*/


/*We can use APPLY operator with table valued function. Before going to APPLY. I ll brief types of Functions*/

/*Two Main types of functions*/
--1.System Function
--2.User defined function

--1.System Functions

/*These Functions already defined in SQL server for various operations*/

/*We can split system functions to two types*/
--1.Scalar Functions
--2.Aggregate Functions

--1.Scalar Functions

/*It works only for single value/variable and returns single value*/
/*Ex:*/

SELECT ABS(-10) /*It will return absolute value*/

DECLARE @Name VARCHAR(10)
SET @Name = 'moov'
select UPPER(@Name) /*It will convert Lower case to Upper Case*/

/*Some more Scalar functions are : LTRIM(),RTRIM(),ROUND(),CONVERT(),....*/

--2.Aggregate Functions
/*It will accept multible rows of values and return one result*/

if object_id('tempdb..#temp') is not null
DROP table #temp
GO
create table #temp
(
sno int,
e_name varchar(100),
dept  varchar(100)
)
go
insert into #temp
select 1,'vicky','sw' union select 2,'moov','bill' union select 3,'anand','acc' union select 5,'AS','sw'

select max(sno) from #temp /*It will return maximum value from 3 rows*/

SELECT COUNT(sno) from #temp /*It will return count of table*/

/*Some more Aggregate functions are : MIN(),AVG(),....*/

--2.User defined function
/*This functions are defined by SQL developers*/

/*We can split User defined functions to two types*/

--1.Scalar Functions
--2.Table valued Functions

--1.Scalar Functions
/*Again it accepts single values return single value*/


IF OBJECT_ID('dbo.FN_ADD_NAME') IS NOT NULL
DROP FUNCTION dbo.FN_ADD_NAME
GO

CREATE FUNCTION FN_ADD_NAME
(
@Name1 VARCHAR(100), /*Inputs*/
@Name2 VARCHAR(100)
)
RETURNS VARCHAR(500)
AS
BEGIN
RETURN (@Name1 + ' From ' + @Name2 + ' Team.')
END

/*We use the above function in select list of table*/
GO
SELECT sno,e_name,dept,dbo.FN_ADD_NAME(e_name,dept) as msg from #temp

/*Or we can use simple select list*/

SELECT dbo.FN_ADD_NAME('BS','Claims')

--2.Table valued Functions
/*It will return table values*/

IF OBJECT_ID('dbo.TEMP_SALARY') IS NOT NULL
DROP table dbo.TEMP_SALARY

go
CREATE TABLE TEMP_SALARY
(
SNO INT,
SALARY MONEY
)

INSERT INTO TEMP_SALARY
SELECT 1,'1000' UNION SELECT 2,'2000' UNION SELECT 3,'1500' UNION SELECT 4,'3000'


IF OBJECT_ID('dbo.FN_RETURN_SALARY') IS NOT NULL
DROP FUNCTION dbo.FN_RETURN_SALARY
go

CREATE FUNCTION FN_RETURN_SALARY
(
@sno INT, /*inputs*/
@AllValue INT
)
RETURNS @Salary TABLE
(
SNO INT,
SALARY MONEY
)
AS
BEGIN

INSERT INTO @Salary
SELECT * FROM TEMP_SALARY WHERE SNO =@sno OR 1=@AllValue /*Simple Trick to restrict output rows*/

RETURN
END

GO
/*We use the above function in simple select*/

SELECT * FROM FN_RETURN_SALARY(1,1) /*All values - 1  -- Return Sno 1*/

SELECT * FROM FN_RETURN_SALARY(4,2) /*Specific Value -- Return Sno 4*/

/*Now I want to get Salar with each name from these two tables using table function*/

SELECT * FROM #TEMP
SELECT * FROM TEMP_SALARY

/*You cant use inner join here. But you can use APPLY here- This is the new feature in SQL 2005*/

SELECT * FROM #TEMP
CROSS APPLY FN_RETURN_SALARY(SNO,2) /*2 means return specific value*/


SELECT * FROM #TEMP
OUTER APPLY FN_RETURN_SALARY(SNO,2) /*2 means return specific value*/

/*It will return NULL if function reurn nothing*/


/*So You can use APPLY operator with table valued function effectively. We can use this feature extensivly in our project*/

/*
Note:

/*BAS Team : You cant use this feature in our SQL 2005. Because this feature requires db compatability level should be 90. Our level is 80
So I created DB "KSCOPE" with db compatability 90 in dtdbtest server. You can use this feature in "KSCOPE" db. If you want to know about db compatability level
Please contact me.

/*If you want to see the error try to run this in dtdbtest..*/
*/

/*
DenRX Team : You can simply run this in your environment ;-)
*/

Thank You!
*/

Filter Date of birth for same age and particular month

create table ##age (id int,dob datetime)
insert into ##age values(1,'1990-02-25 09:18:58.740')
insert into ##age values(2,'1990-07-01 09:18:58.740')
insert into ##age values(3,'1998-05-25 09:18:58.740')
insert into ##age values(4,'1990-08-25 09:18:58.740')

create table #temp (id int,dob datetime,age int,month int)

insert into #temp (id,dob,age,month)
select id,dob,DATEDIFF(yy, dob, GETDATE()) - CASE WHEN
(MONTH(dob) > MONTH(GETDATE())) OR (MONTH(dob) = MONTH(GETDATE()) AND DAY(dob) > DAY(GETDATE())) THEN 1 ELSE 0 END as age,month(dob) as [month] from ##age

select * from #temp
select * from #temp where age =26 and [month] in (1,2,3,4,5,6,7,8)

How to calculate accurate age from date of birth in sql

DECLARE @date datetime, @tmpdate datetime, @age int
SELECT @date = '02/25/91'
SELECT @tmpdate = @date
SELECT @age = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @age

What to choose for performance - SubQueries or Joins in sql

In General Joins work faster than subqueries .

According to MSDN, in sql server, in most cases, there is usually no performance difference between queries that uses sub-queries and equivalent queries using joins. For example, on my machine I have
400,000 records in tblProducts table
600,000 records in tblProductSales tables 

The following query, returns, the list of products that we have sold atleast once.This query is formed using sub-queries. When I execute this query I get 306,199 rows in 6 seconds
Select Id, Name, Description
from tblProducts
where ID IN
(
Select ProductId from tblProductSales
) 

At this stage please clean the query and execution plan cache using the following T-SQL command. 
CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; -- Clears query cache
Go
DBCC FREEPROCCACHE; -- Clears execution plan cache
GO

Now, run the query that is formed using joins. Notice that I get the exact same 306,199 rows in 6 seconds. 

Select distinct tblProducts.Id, Name, Description
from tblProducts
inner join tblProductSales
on tblProducts.Id = tblProductSales.ProductId

Please Note: I have used automated sql script to insert huge amounts of this random data. Please watch Part 61 of SQL Server tutorial, in which we have discussed about this automated script.

According to MSDN, in some cases where existence must be checked, a join produces better performance. Otherwise, the nested query must be processed for each result of the outer query. In such cases, a join approach would yield better results.

The following query returns the products that we have not sold at least once. This query is formed using sub-queries. When I execute this query I get 93,801 rows in 3 seconds

Select Id, Name, [Description]
from tblProducts
where Not Exists(Select * from tblProductSales where ProductId = tblProducts.Id)

When I execute the below equivalent query, that uses joins, I get the exact same 93,801 rows in 3 seconds.

Select tblProducts.Id, Name, [Description]
from tblProducts
left join tblProductSales
on tblProducts.Id = tblProductSales.ProductId
where tblProductSales.ProductId IS NULL 

In general joins work faster than sub-queries, but in reality it all depends on the execution plan that is generated by SQL Server. It does not matter how we have written the query, SQL Server will always transform it on an execution plan. If sql server generates the same plan from both queries, we will get the same result.

I would say, rather than going by theory, turn on client statistics and execution plan to see the performance of each option, and then make a decision. 

SQL Interview Q&A

SQL Interview Q&A