Monday, 19 November 2012

how to copy data from one table to another in sql?

--how to copy data from one table to another in sql
insert into TableName
select * from AnotherTABLE
--here Tablename is already created
--another way of copying data but here we should create temp table
select * into NEWTABLE
from OLDTABLE

Tuesday, 13 November 2012

Functions in sql server
function is like a stored procedure it include a return statement
to return a result as a single value or as a table
it is a block of code we can reuse it
it takes only input parameters

Types of functions
1.scalar
2.inline table values
3.Multitable valued

--get salery of an employee using his Empno
create function Multiply(@a int,@b int)
RETURNS int
as
begin

return @a*@b

End

select dbo.Multiply (10,2000000) as yjreddyincomeperyear
--inline table valued fn
CREATE FUNCTION Getdetailsbyempno
( @EmpNo int )
RETURNS table
AS
RETURN (
SELECT Empname, salery
FROM Employeerecord
WHERE EmpNo=@EmpNo
)
select * from Getdetailsbyempno(7900)

User-Defined Functions

There are three Types of UDFS in Sql Server:
1. Scalar
2. Inline Table-Valued
3. Multi-statement Table-Valued
Let us go through each of these with examples:
1. Scalar User-Defined Function
A Scalar UDF can accept 0 to many input parameter and will return a single value. A Scalar user-defined
function returns one of the scalar (int, char, varchar etc) data types. Text, ntext, image and timestamp data types
are not supported. These are the type of user-defined functions that most developers are used to in other
programming languages.
Example 1: Here we are creating a Scalar UDF AddTwoNumbers which accepts two input parameters @a and
@b and returns output as the sum of the two input parameters.
CREATE FUNCTION AddTwoNumbers
(
@a int,
@b int
)
RETURNS int
AS
BEGIN
10/ 9/ 12 User Def ined Funct ion « BeginSQ L
beginsql. wor dpr ess. com/ cat egor y/ sql- ser ver / user - def ined- f unct ion/ 2/ 6
BEGIN
RETURN @a + @b
END
Once the above function is created we can use this function as below:
PRINT dbo.AddTwoNumbers(10,20)
OR
SELECT dbo.AddTwoNumbers(30,20)
Note: For Scalar UDFS we need to use Two Part Naming Convention i.e. in the above two statements we are
using dbo.AddTwoNumbers.
Whether Below statement is correct? No, because it is not using two-part naming convention. Try executing the
below statement it will error out…
PRINT AddTwoNumbers(10,20)
2. Inline Table-Valued User-Defined Function
An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT
statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed
in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column
definitions for the table variable) because the structure of the returned value is generated from the columns that
compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT,
no duplicate column names are allowed and all derived columns must have an associated alias.
Example: In this example we are creating a Inline table-valued function GetAuthorsByState which accepts
state as the input parameter and returns firstname and lastname of all the authors belonging to the input state.
USE PUBS
GO
CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS table
AS
RETURN (
SELECT au_fname, au_lname
FROM Authors
WHERE state=@state
)
GO
We can use the below statement to get all the authors in the state CA.
10/ 9/ 12 User Def ined Funct ion « BeginSQ L
beginsql. wor dpr ess. com/ cat egor y/ sql- ser ver / user - def ined- f unct ion/ 3/ 6
SELECT * FROM GetAuthorsByState(‘CA’)
3. Multi-statement Table-Valued User-Defined Function
A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one TSql
statement. Within the create function command you must define the table structure that is being returned.
After creating this type of user-defined function, we can use it in the FROM clause of a T-SQL command unlike
the behavior found when using a stored procedure which can also return record sets.
Example: In this example we are creating a Multi-Statement Table-Valued function GetAuthorsByState which
accepts state as the input parameter and returns author id and firstname of all the authors belonging to the
input state. If for the input state there are no authors then this UDF will return a record with no au_id column
value and firstname as ‘No Authors Found’.
USE PUBS
GO
CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS
@AuthorsByState table (
au_id Varchar(11),
au_fname Varchar(20)
)
AS
BEGIN
INSERT INTO @AuthorsByState
SELECT au_id,
au_fname
FROM Authors
WHERE state = @state
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @AuthorsByState
VALUES (”,’No Authors Found’)
END
RETURN
END
GO
We can use the below statements to get all the authors in the given input state:
SELECT * FROM GetAuthorsByState(‘CA’)
SELECT * FROM GetAuthorsByState(‘XY’)

Monday, 12 November 2012

How to copy particular records into another table

How to copy particular records into another table ?
select * into temptabbb from
(
select ROW_NUMBER() over(order by salery) as rownum,EmpName,EmpNo from EmployeeRecord) tempderivedtable
where rownum>5

How to get specific records from a table in sql?

--how to get specific records from a table
SELECT EmpNo, EmpName, Emppost
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmpNo ASC) AS Row,
EmpNo, EmpName, Emppost
FROM EmployeeRecord) tmp
WHERE Row >= 5 AND Row <= 15

Friday, 9 November 2012

Rankin functions in sql server

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

I have not written about this subject for long time, as I strongly believe that Book On Line explains this concept very well. SQL Server 2005 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.

All the above definition and syntax are taken from BOL. It is difficult to explain above function anything else than what they are explained in BOL. Following example is excellent example from BOL again. This function explains usage of all the four function together in one query.

USE AdventureWorks;GOSELECT c.FirstName, c.LastName,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number',RANK() OVER (ORDER BY a.PostalCode) AS 'Rank',DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank',NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile',s.SalesYTD, a.PostalCodeFROM Sales.SalesPerson sINNER JOIN Person.Contact cON s.SalesPersonID = c.ContactIDINNER JOIN Person.Address aON a.AddressID = c.ContactIDWHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;

Resultset:

Most of the content of this article is taken from BOL.

How to get the record of highest saleried Employee in sql?

--How to get the record of highest saleried Employee
SELECT * FROM
(
SELECT DENSE_RANK() OVER(ORDER BY [Salery] DESC)AS RowId, *
FROM EmployeeRecord

) AS e1
WHERE e1.RowId = 4

Thursday, 8 November 2012

Common Table expression in Sql server

/*
A cte can be thought as a Temporary results set and
that can be defined within select,insert,update,delete
and views.
Note: the scope of cte is works for within that query only
it can be referenced multiple times in the query
*/
--CTE EXAMPLE
WITH Simplecte(EmpName)
as
(
select EmpName from EmployeeRecord
)
select * from simplecte

uses of CTE:
*it is used to write recursive query
*substitute for a view
*improved readability in case of complex queries

How to find the nth highest salary with out using top clause or subquery?

SELECT *
FROM EmployeeRecord e1
WHERE (2-1) = (
SELECT COUNT(DISTINCT(e2.Salery))
FROM EmployeeRecord e2
WHERE e2.Salery > e1.Salery)

How to get the table creation date?

SELECT create_date
FROM sys.tables
WHERE name='Employee'

How to find the top 6 salary from emp table?

--how to find the top 6 salary from emp table
select top 6 salery from EmployeeRecord order by Salery desc

Wednesday, 7 November 2012

How to find a transaction has effeced?

--How to find a transaction has effeced
create procedure FundsTransfer_Rowcount
(@Giver int,
@Reciever int,
@AmtBal money

)
as
begin
declare @count1 int,@count2 int
begin tran
update bank set Balance=Balance-@AmtBal where B_id=@Giver
set @count1=@@ROWCOUNT
update bank set Balance=Balance+@AmtBal where B_id=@Reciever
set @count2=@@ROWCOUNT
IF @count1=@count2
begin
commit
print 'Transaction succeded'
end
else
begin
rollback
print 'transaction rolledback'
end
end

exec FundsTransfer_Rowcount 104,105,2000

How to transfer money from one account to another using stored procs wihin same table?

sp for send the amount from one customer to another in same table

create Table bank
(
B_id int,
Balance Money,
Address varchar(50)
)
alter table bank
add Name varchar(50)
select * from bank
insert into bank values(106,38000,'Gnt','Ankireddy')
--stored procedure for trnasferring amount
create procedure Funds_Transfer
(@srcid int,
@Destid int,
@Balamt money)
as
begin
update bank set Balance=Balance-@Balamt where B_id=@srcid
update bank set Balance=Balance+@Balamt where B_id=@Destid
ENd

Exec Funds_Transfer 110,104,2000

SQL SERVER – Stored Procedure and Transactions

« SQL SERVER – Precision of SMALLDATETIME – A 1 Minute Precision

SQLAuthority News – Training and Consultancy and Travel – Story of Last 30 Days »

SQL SERVER – Stored Procedure and Transactions

June 2, 2010 by pinaldave

I just overheard the following statement – “I do not use Transactions in SQL as I use Stored Procedure“.

I just realized that there are so many misconceptions about this subject. Transactions has nothing to do with Stored Procedures. Let me demonstrate that with a simple example.

USE tempdb
GO-- Create 3 Test TablesCREATE TABLE TABLE1 (ID INT);CREATE TABLE TABLE2 (ID INT);CREATE TABLE TABLE3 (ID INT);GO-- Create SPCREATE PROCEDURE TestSPAS
INSERT INTO TABLE1 (ID)VALUES (1)INSERT INTO TABLE2 (ID)VALUES ('a')INSERT INTO TABLE3 (ID)VALUES (3)GO-- Execute SP
-- SP will error outEXEC TestSP
GO-- Check the Values in TableSELECT *FROM TABLE1;SELECT *FROM TABLE2;SELECT *FROM TABLE3;GO

Now, the main point is: If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Let’s see the result very quickly.

It is very clear that there were entries in table1 which are not shown in the subsequent tables. If SP was transactional in terms of T-SQL Query Batches, there would be no entries in any of the tables. If you want to use Transactions with Stored Procedure, wrap the code around with BEGIN TRAN and COMMIT TRAN.

The example is as following.

CREATE PROCEDURE TestSPTranAS
BEGIN TRAN
INSERT INTO TABLE1 (ID)VALUES (11)INSERT INTO TABLE2 (ID)VALUES ('b')INSERT INTO TABLE3 (ID)VALUES (33)COMMITGO-- Execute SPEXEC TestSPTran
GO-- Check the Values in TablesSELECT *FROM TABLE1;SELECT *FROM TABLE2;SELECT *FROM TABLE3;GO-- Clean upDROP TABLE Table1DROP TABLE Table2DROP TABLE Table3
GO

In this case, there will be no entries in any part of the table. What is your opinion about this blog post? Please leave your comments about it here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Tuesday, 6 November 2012

STORED PROCEDURES

INSERTING RECORDS USING SPS

create procedure Insert_Empp(

@ID int,
@name varchar(10),
@salary int
)
As
BEGIN

INSERT INTO dbo.Empp(ID,name,salary)values
(@ID,
@name,
@salary)
END

RETRIEVING RECORDS
create procedure select_Empp(
@Id int,
@name varchar(10),
@salary int
)
As
Begin
End
select * from Empp

Getting details by ID

create procedure Showdetails

(

@ID int

)

Begin

select name,salary from Empp

where ID=@ID

End

EXEC Showdetails 3

procedure with output parameter
create procedure pro5(@AB int,@BC int,@CD int,@DE int)
as
begin
declare @abc int
set @abc=@DE+@AB+@BC+@CD
print 'RESULT AS '+cast(@abc as varchar)
end
EXEC pro5 200,300,500,600

SIMPLE EXAMPLES

--simple stored procedure
create procedure simmple_proc
as
begin
print'hi yjreddy doing first procedure'
End

Exec simmple_proc

--second one
create procedure Secondonewithparameters(@x int,@y int)
as
begin
declare @z int
set @z=@x+@y
print 'The sum of two nos is'+cast(@z as varchar)
End

EXEC Secondonewithparameters 10,20
EXEC Secondonewithparameters 50,100

create procedure third(@a int,@b int,@c int)
AS
BEGIN
DECLARE @d int
set @d=@a+@b+@c
print 'result is '+cast(@d as varchar)
END

create procedure proc4(@e int,@f int,@g int)
as
begin
declare @v int
set @v=@e+@f+@g
print 'result as '+cast(@v as varchar)
end
EXEC proc4 100,200,300
--procedure with output parameter
create procedure pro5(@AB int,@BC int,@CD int,@DE int)
as
begin
declare @abc int
set @abc=@DE+@AB+@BC+@CD
print 'RESULT AS '+cast(@abc as varchar)
end
EXEC pro5 200,300,500,600

DELETE RECORD USING STOREDPROCEDURE

--Delete recorb by id
create procedure Delete_procedure(@ID int,@title nvarchar(10),@averageSalary int)
as
begin
delete from EMPJOB where ID=@ID
end

--updating records by sps

create procedure update_emp11(@ID int,@title nvarchar(10),@averageSalary int)
as
begin
update EMPJOB set title=@title , Id=@id where ID=@ID
end
EXEC update_emp11 107,'yjreddy',50000

--create procedure for inserting checking null values
create procedure condition_nulls(@Id int,
@title nvarchar(10),
@averageSalary int
)
as
begin
if @Id is null
Begin
print'dont enter null it wont accept'
Return
end

insert into EMPJOB(ID,title,averageSalary) values(
@Id,
@title,
@averageSalary
)

end

--create procedure for inserting checking null values and restricting redundant values

create procedure condition_nullsanduniquevalues(@Id int,

@title nvarchar(10),

@averageSalary int

)

begin

if @Id is null

Begin

print'dont enter null it wont accept'

Return

end

if EXISTS(select * from EMPJOB where ID=@Id)

begin

print 'emp id should be unique '

return

end

insert into EMPJOB(ID,title,averageSalary) values(

@Id,

@title,

@averageSalary

)

end

--create procedure for inserting checking null values and restricting redundant values
--and salary should be more than 25000
Create procedure condition_nullsanduniquevalues(@Id int,
@title nvarchar(10),
@averageSalary int
)
as
begin
if @Id is null
Begin
print'dont enter null it wont accept'
Return
end
if EXISTS(select * from EMPJOB where ID=@Id)
begin
print 'emp id should be unique '
return
end
if @averageSalary>25000
begin
print'salary should be high'
Return
end

insert into EMPJOB(ID,title,averageSalary) values(
@Id,
@title,
@averageSalary
)

end

sp for send the amount from one customer to another in same table

create Table bank
(
B_id int,
Balance Money,
Address varchar(50)
)
alter table bank
add Name varchar(50)
select * from bank
insert into bank values(106,38000,'Gnt','Ankireddy')
--stored procedure for trnasferring amount
create procedure Funds_Transfer
(@srcid int,
@Destid int,
@Balamt money)
as
begin
update bank set Balance=Balance-@Balamt where B_id=@srcid
update bank set Balance=Balance+@Balamt where B_id=@Destid
ENd

Exec Funds_Transfer 110,104,2000

---a procedure can cause error
create procedure proc123(@X int,@y int)
as
begin
declare @z int
set @z=@X/@y
print 'the output is '+cast(@z as varchar)
End
EXEC proc123 200,0

-- procedure with try catch block
create procedure trycatch(@x int,@y int)
as
begin
Begin try
Declare @z int
set @z=0
set @z=@x/@y
print 'the output is'+cast(@z as varchar)
end try
begin catch
print Error_message()
end catch
end

EXEC trycatch 100,20
EXEC trycatch 500,50

Monday, 5 November 2012

Cursors in sql server

--First example
select * from Employee
DECLARE samplecursor CURSOR for select emp_no from Employee
DECLARE @emp_no int
open samplecursor
FETCH NEXT FROM samplecursor into @emp_no
while @@FETCH_STATUS=0
BEGIN
print @emp_no
fetch next from samplecursor into @emp_no
END
CLOSE samplecursor
--second example
DECLARE SECONDCURSOR123 CURSOR FOR select emp_fname from Employee
DECLARE @emp_fname varchar(50)
open SECONDCURSOR123
FETCH NEXT FROM SECONDCURSOR123 INTO @emp_fname
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @emp_fname
FETCH NEXT FROM SECONDCURSOR123 into @emp_fname
END
CLOSE SECONDCURSOR123
select * from EmployeeDummy sys.all_objects where type='u'
--cursor Examples
DECLARE CURSOR_EXAMPLE CURSOR FOR select emp_no from EmployeeDummy
DECLARE @Employeeno int
open CURSOR_EXAMPLE
FETCH NEXT FROM CURSOR_EXAMPLE into @Employeeno
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @Employeeno
FETCH NEXT FROM CURSOR_EXAMPLE
END
CLOSE CURSOR_EXAMPLE

select * from dbo.EMPJOB
DECLARE AVUSAL_CURSOR CURSOR FOR SELECT averagesalary from EMPJOB
DECLARE @Avgsalary int
open AVUSAL_CURSOR
FETCH NEXT FROM AVUSAL_CURSOR
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @Avgsalary
FETCH NEXT FROM AVUSAL_CURSOR
END
CLOSE AVUSAL_CURSOR

select * from Employee

--ksn
DECLARE CURSOR_EMPLOYEE CURSOR FOR SELECT emp_no,emp_fname from Employee
DECLARE @empno int,@emp_name varchar(50)
open CURSOR_EMPLOYEE
FETCH NEXT FROM CURSOR_EMPLOYEE INTO @empno,@emp_name
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'empno'+@empno+'empname'+@emp_name
FETCH NEXT FROM CURSOR_EMPLOYEE
END
CLOSE CURSOR_EMPLOYEE

Cursors in sql server?

--Tsql Programming
DECLARE @MONTH int
SET @MONTH=DATEPART(MM,GETDATE())
if @MONTH=1
print 'jan'
if @MONTH=2
print 'feb'
if @MONTH=3
print 'march'
else
print 'year almost in ending'

--CASE STATEMENT
--simple case
DECLARE @CASEEXAMPLE int
SET @CASEEXAMPLE=MONTH(GETDATE())
select CASE @CASEEXAMPLE
when 1 then 'jan'
when 2 then 'feb'
when 10 then 'march'
when 11 then 'november'
END
--Search case
DECLARE @SECONDCASE DATETIME
SET @SECONDCASE=GETDATE()
select case
when @SECONDCASE>'10/05/2012' then 'year is in second quareter'
end
select DATEPART(ww,getdate())
-- while loop
DECLARE @X int
SET @X=0
WHILE @X<10
BEGIN
SET @X=@X+1
print 'x is incrementing'
END
--

DECLARE @y int
SET @y=0
while @y<10
begin
set @y=@y+1
if @y>6 continue
print @y

END

Indexes in sql server

INDEXES

select * from EmployeeDummy
--clustered index
create unique clustered index sample_cluster on Employeenotnull(ID desc)
create unique clustered index sample123_clust on dbo.EmployeeDummy(emp_no)
/*note:if a table has duplicate records then we can't create
clustered index on that*/
--Nonclustered index
create index Noncluster on dbo.classificationdummy(classif_ID)
--can a table have both clusterd and nonclustered index?
--yes,but not on same columns
create unique clustered index sample123_clust on dbo.EmployeeDummy(emp_no)
create index Noncluster345 on dbo.EmployeeDummy(emp_fname)
create index Noncluster345 on dbo.EmployeeDummy(emp_no)
--indexed view
--first create view
select * from dbo.Empdummt
create view sampleindexview
WITH SCHEMABINDING
as
select EmpNo,EmpName,Salery from dbo.Empdummt
select * from sampleindexview
--indexed view
create unique clustered index indexviewsample on sampleindexview(EmpNo Desc)

Sunday, 4 November 2012

Rownumber in sql server

How to use ROW_NUMBER() to enumerate and partition records in SQL Server

By Dave C Andrews, 31 Dec 2011

I had a situation recently where I had a table full of people records, where the people were divided into families. The business logic that needed to be followed was that I had to assign a “Twin Code” to each record. This meant that for each family in the database, if two or more members were born on the same day they should be treated as twins. The twins should be assigned a number enumerating them in order of birth. If the member was not a twin, they should just receive the twin code of 1.
Here’s an example table:

PersonID	FamilyID	FirstName	LastName	DateOfBirth
1	1	Joe	Johnson	2000-10-23 13:00:00
2	1	Jim	Johnson	2001-12-15 05:45:00
3	2	Karly	Matthews	2000-05-20 04:00:00
4	2	Kacy	Matthews	2000-05-20 04:02:00
5	2	Tom	Matthews	2001-09-15 11:52:00

There are lots of ways to achieve the desired result, but the simplest is to just use a simple SELECT statement combined with the ROW_NUMBER() function with a couple parameters as to how to number the rows!
ROW_NUMBER() provides you with the number of a row in a given recordset, where you provide details on how to number the records. For example, if I just had to number the records above based solely upon the date of birth (ignoring families) then I would use this query:

Collapse | Copy Code

SELECT
     [PersonID]
    ,[FamilyID]
    ,[FirstName]
    ,[LastName]
    ,[DateOfBirth]
    ,ROW_NUMBER() over (ORDER BY DateOfBirth) AS Number
FROM
 People
ORDER BY
 PersonID

This just tells the ROW_NUMBER() function to order its numbering ascending by DateOfBirth. Notice that I apply an order myself later on in the query, which is different than the row_number() order. I would get these results:

PersonID	FamilyID	FirstName	LastName	DateOfBirth	Number
1	1	Joe	Johnson	2000-10-23 13:00:00	3
2	1	Jim	Johnson	2001-12-15 05:45:00	5
3	2	Karly	Matthews	2000-05-20 04:00:00	1
4	2	Kacy	Matthews	2000-05-20 04:02:00	2
5	2	Tom	Matthews	2001-09-15 11:52:00	4

The number field that is assigned to each record is in the order of DateOfBirth.
Ordering my numbering by DateOfBirth is just half of the picture. I also need to “group” the records by the FamilyID. This is where a clause in T-, SQL that you might not be very familiar with comes into play: “PARTITION BY”. The PARTITION BY clause allows us to group the results within the call to ROW_NUMBER()without grouping them ourselves via a GROUP BY. It just tells the ROW_NUMBERR what groupings to use when it does its counting.
Here is our final SQL statement, which achieves the business logic we wanted to implement.

Collapse | Copy Code

SELECT
       [PersonID]
     [FamilyID]
      ,[FirstName]
      ,[LastName]
      ,[DateOfBirth]
      ,ROW_NUMBER() over(PARTITION BY FamilyID,
                         CONVERT(NVARCHAR(25), DateOfBirth, 111)
                         ORDER BY DateOfBirth ASC) TwinCode

  FROM [People]
ORDER BY PersonID

IIn the ROW_NUMBER function above, I am doing several things. I’m grouping on FamilyID, and also grouping on a converted DateOfBirth. I convert the DateOfBirth to an nvarchar using the 111 conversion code, because that gets results like ‘2009/10/11′ and ‘2009/10/12′ which can easily be grouped by to achieve distinct dates.
Grouping on the Family, DateOfBirth, and then sorting by DateOfBirth ascending achieves the desired result for the ROW_NUMBER. Here are the results of the query:

PersonID	FamilyID	FirstName	LastName	DateOfBirth	TwinCode
1	1	Joe	Johnson	2000-10-23 13:00:00	1
2	1	Jim	Johnson	2001-12-15 05:45:00	1
3	2	Karly	Matthews	2000-05-20 04:00:00	1
4	2	Kacy	Matthews	2000-05-20 04:02:00	2
5	2	Tom	Matthews	2001-09-15 11:52:00	1

As you can see, the two people who qualify as twins above (Karly and Kacy) are enumerated correctly, with Karly receiving a 1 and Kacy receiving a 2. All the records that are not twins properly receive a 1.

Using ROW_NUMBER() to paginate your data with SQL Server 2005 and ASP.NET

By Jason Witty, 20 Nov 2005

Download source files - 667 Kb

View Demo

Introduction

One of the great weaknesses of SQL Server 2000 was its inability to handle data pagination well. The solution to the problem was always to select all of your results and programmatically hide the results you did not want to show. As the user clicked Next or Previous, you would again select all of the rows and only display what the user asked for. Though the desired interface is achieved through this method, it is terribly inefficient. Why couldn't we select only the data that we wanted? If you have ever had to write a search that used "like" against thousands of records, you know how terribly slow SQL Server 2000 could perform.
During my SQL Server 2000 days, I would search endlessly for a solution to this problem. I tried implementations of using a select top where my last row was greater than a parameter. This works only in some cases, like when ordering by a primary key or by date. Otherwise, this failed because of the existence of duplicate data. I also tried building stored procedures that used crazy for loops to try and accomplish this. In every instance, I would always hit a brick wall. The client would request a feature that I could not support with my method and I would always default back to the poor performance of selecting all of the rows (or many of them) and handling the paging scheme programmatically.
Throughout this process, I often theorized of a SQL Server function that could add a sequential row number to my result set and allow me to use a where clause against that row the only selects what rows I needed. After a bit of research, I found out that this function did in fact exist. The only problem was, it existed only in Oracle! I was enraged, how could something so useful be simply left out of SQL Server 2000?
A few years pass by and Microsoft releases .NET which offers a partial solution to the problem. ASP.NET offers you the ability to output-cache the results of your web control. So essentially, you can select all of the rows once and as you page through the results, pull each subsequent set from the cached results. This seems to partially solve the performance problem though you are still faced with making the initial selection. But what if you want to view live changing data? As you decrease your cache time, your performance gets worse, as you increase it, your data gets old. Eventually, you fall back on your tired old method again.
With the release of SQL Server 2005, Microsoft introduces the long overdue ROW_NUMBER() function to solve this problem. In this article, we will walk through a C# implementation of pagination using the ROW_NUMBER()method.

The Code

The first step is writing your stored procedure. The SQL code for using ROW_NUMBER() is not as intuitive as you might think. When I originally attempted to do this, I tried to simply use the ROW_NUMBER() function like I wouldnewid(). I quickly found out, that was not going to work. After some research, I came up with the stored procedure below. Though I would have rather seen a more intuitive syntax than what is below when you think about it, it does make sense. I suppose they did not want to hide logic from the programmer and ask him to accept that something magical simply happens. In the following project, I will use a database of all zip codes in the United States.

Collapse | Copy Code

CREATE PROCEDURE [dbo].[sp_getzipcodes] 
     -- Add the parameters for the stored procedure here
     @start int = 0

AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     Set NOCOUNT ON
     SELECT TOP 20 * FROM 
     (
          SELECT zip,city,state,latitude,longitude,timezone,dst,
          ROW_NUMBER() OVER (ORDER BY zip) AS num
          FROM dbo.zipcode
     ) AS a
     WHERE num > @start
END