Serial Number Generation problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Serial Number Generation problem

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2010-08-18 : 04:26:30

hello

i am using the following query.
SELECT c.CID, c.AFName, SUBSTRING(CONVERT(varchar, d.DDate), 7, 2) + '/' + SUBSTRING(CONVERT(varchar, d.DDate), 5, 2)
+ '/' + SUBSTRING(CONVERT(varchar, d.DDate), 1, 4) AS Expr1, d.NYr, RTRIM(d.DAmt) AS damt, a.AID, a.AName, s.SName
FROM dbo.Associates AS a INNER JOIN
dbo.DepositDetails AS d ON a.ACode = d.SCode INNER JOIN dbo.CustomerDetails AS c ON d.CCode = c.CID INNER JOIN dbo.SchemeMaster AS s ON d.Scheme = s.Code
and d.ddate>='20090101' and d.ddate<='20100101'

Now i want to insert a serial no field at beginning, something like SELECT ROW_NUMBER() OVER (ORDER BY a.aid) as srno,...

But I do not want to use "Row_Number() Over.." construct here.
the reason is , I am using ReportViewer in VB2005, and the dataset where I put my query do not support this.
I would appreciate if anybody help me modifying this query.

Thanks and regards

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-18 : 07:22:35

For that you should do it in report that give an incremental number for every new row.
In SQL without ROW_NUMBER() this is not possible as per my knowledge.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-18 : 11:24:16

Why dont you do numbering when you show data in ReportBuilder?

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-08-19 : 07:04:12

Incremental number for every new row... if only the system could generate this IDENTITY for you somehow...

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 07:57:46

[code]DECLARE @Sample TABLE
(
ID INT
)

INSERT @Sample
SELECT 2 UNION ALL
SELECT 11 UNION ALL
SELECT 234 UNION ALL
SELECT 4534

-- SQL Server 2005 and later
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS Seq,
ID
FROM @Sample

-- SQL Server 2000 and earlier. Is very slow on a large dataset.
SELECT (SELECT COUNT(*) FROM @Sample AS w WHERE w.ID <= s.ID) AS Seq,
s.ID
FROM @Sample AS s[/code]

N 56°04'39.26"
E 12°55'05.63"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-08-20 : 13:12:08

Peter: Please stop giving answers that are not possible! [/sarcasm]

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-21 : 07:14:17

quote:
Originally posted by DonAtWork

Peter: Please stop giving answers that are not possible! [/sarcasm]

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

I could not understand...
Whats wrong with that solution if it works for anyone ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-21 : 08:50:56

It's a "triangular" join, which means the sum of records read required to complete the query is exponential.
For first row, the sub query has to read 1 record.
For second row, the sub query has to read 2 records.
For third row, the sub query has to read 3 records.

So, if you have to number 50,000 records, the subquery is run 50,000 times and sums up a total of 1,250,025,000 (1.25 billion records).
That's why the second alternative is so slow for a large dataset.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources