Posted by: R Manimaran | July 4, 2008

New in SQL SERVER 2005 -Part1

Except Operator

EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table.

Already we are familiar with the NOT IN operator. The difference between both these is, “NOT IN” will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result.

In Oracle, the equivalent of the EXCEPT operator is MINUS

Example

Disable the Intermediate informative message

Set NoCount On

–Declare the Table for Testing

Declare @Table1 table(id int,name varchar(50))

Declare @Table2 table(id int,name varchar(50))

–Inserting Values to the first Test Table

Insert into @Table1 values(1,‘APPLE’)

Insert into @Table1 values(2,‘MANGO’)

Insert into @Table1 values(2,‘MANGO’)

Insert into @Table1 values(3,‘GRAPES’)

insert into @Table1 values(3,‘GRAPES’)

insert into @Table1 values(4,‘JackFruit’)

–Inserting samples to Second test Table

Insert into @Table2 values(1,‘APPLE’)

insert into @Table2 values(4,‘JackFruit’)

–Quering using the Except statement

Select id,name from @Table1 except Select id,name from @Table2

–Output

Except operator Result

Except operator Result

Same thing if we use the Not in Operator.

–Using NOT IN Opearator

select id,name from @Table1 where id not in(Select id from @Table2)

–Output

NOT IN Result

NOT IN Result

INTERSECT

INTERSECT returns data value which is common in both queries. In Oracle, the equivalent of INTERSECT is same as INTERSECT.

–Intersect Query

Select id,name from @Table1 intersect select id,name from @Table2

–Output

INTERSECT Result

INTERSECT Result

TOP PERCENT

The TOP keyword allows you to return the first n number of rows from a query based on the number of rows or percentage of rows that you define.

Select Top @noofrows from Table

Example

–Top

Select Top 2 * From @Table1

–Output

TOP results

TOP results

— TOP Percentage

Select Top 50 Percent * from @Table1

TOP Percentage result

TOP Percentage result

Here @Table1 contains 6 rows. 50% of the @Table1 is 3 rows.

Row Number

ROW_NUMBER () : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Syntax for using the ROW_NUMBER ()

–Query

Select Row_Number()over(Order by id) as sno ,id,name from @Table1

–Output

Row_Number() results

Row_Number() results

Note: The incremental row number sequence is based on the Order by Clause. Row Number function can be used with User defined function and table valued functions.

Rank

The rank of a row is one plus the number of ranks that come before the row in question.

Rank function is similar to the ROW Number function, the key difference between them been that, rows with tied value will receive the same Rank Value.

–Rank Query

Select Rank() over(order by id) as [rank],id,name from @Table1

–Output

Rank Results

Rank Results

Dense_Rank

This is another Ranking Function which too is similar the Rank() functions.

The difference between RANK and DENSE_RANK been that it returns results without gaps in the rank values.

–Query

Select DENSE_RANK() over(Order by id) as [Dense_Rank],id ,name from @Table1

–Output

Dense_Rank Result

Dense_Rank Result

NTILE:

NTILE divides the result set into a specified number of groups based on the ordering and optional partition.

The syntax is very similar to the other ranking functions, only it also includes an integer expression.

–QUERY

SELECT NTILE(3) over(Order By id) as Sno ,id,name from @Table1

–Output

NTILE Result

NTILE Result

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: