rownumbering w tsql lost to ADO loop -

Hello
Thanks to all who have been helping me to fight a battel
with tsql for rownumbering of my tables. I humbly admit
defeat for today. I had to throw the towel in and whipped
up an ADO loop to number some rows in a table. That took
me all of 3 minutes, if that. I wrestled with tsql all
day long. Well, here is one more shot at tsql.
create table t (rownum int, area char(1), yearnum int,
monthnum int, areaNum int, productNum int)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 3)
this yields
rownum area yearnum monum areanum prodnum
NULL A 2003 1 1 1
NULL A 2003 1 1 2
NULL A 2003 1 1 3
NULL B 2003 1 2 1
NULL B 2003 1 2 2
NULL B 2003 1 2 3
NULL C 2003 1 3 1
NULL C 2003 1 3 2
NULL C 2003 1 3 3
NULL A 2003 2 1 1
NULL A 2003 2 1 2
NULL A 2003 2 1 3
NULL B 2003 2 2 1
NULL B 2003 2 2 2
NULL B 2003 2 2 3
NULL C 2003 2 3 1
NULL C 2003 2 3 2
NULL C 2003 2 3 3
I need to number these rows in the order of
yearNum, MonthNum, areaNum, productNum
select * from t order by yearnum, monthnum, areanum,
productnum
how can I number the rownum column starting at 1 using
tsql?
Thanks in advance,
RonDo:
UPDATE t
SET rownum = ( SELECT COUNT(*)
FROM t t1
WHERE CAST( t1.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t1.monthnum AS VARCHAR), 2 ) +
CAST( t1.areaNum AS VARCHAR) +
CAST( t1.productNum AS VARCHAR) <=
CAST( t.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t.monthnum AS VARCHAR), 2 ) +
CAST( t.areaNum AS VARCHAR) +
CAST( t.productNum AS VARCHAR) )
WHERE rownum IS NULL ;
Btw, do you really need a reason to have such a column in the table.
Generally, it is a good idea to do such derivations while extracting the
data from the table or to use a view with the ranking expression.
Anith|||thanks for your reply. This worked perfectly.
And to answer your question if I really need this
column? Truthfully, no. I don't really need it. Well,
for my purposes, this column facilitates reporting and ad-
hoc queries for data analysts in my dept at the
workplace. My real problem was that I did not know how
to achieve this with t-sql. I was aware of using the
self join, but I did not know that you had to cast int
values as varchars to accomplish this numerating.
I believe that my exercise for today will be very
valuable in the future.
Thanks for your help,
Ron

>--Original Message--
>Do:
>UPDATE t
> SET rownum = ( SELECT COUNT(*)
> FROM t t1
> WHERE CAST( t1.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t1.monthnum
AS VARCHAR), 2 ) +
> CAST( t1.areaNum AS VARCHAR) +
> CAST( t1.productNum AS VARCHAR)
<=
> CAST( t.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t.monthnum
AS VARCHAR), 2 ) +
> CAST( t.areaNum AS VARCHAR) +
> CAST( t.productNum AS VARCHAR) )
> WHERE rownum IS NULL ;
>Btw, do you really need a reason to have such a column
in the table.
>Generally, it is a good idea to do such derivations
while extracting the
>data from the table or to use a view with the ranking
expression.
>--
>Anith
>
>.
>|||One more question if I may. I hope this isn't too
ignorant sounding, but...
I noticed a semi colon at the end of your code. Does
Enterprise Manager have a wizard that can generate the t-
sql for something like this? Man, I sure hope so. It
would sure save me a lot of heartache.

>AS VARCHAR), 2 ) +
VARCHAR)
><=
>AS VARCHAR), 2 ) +
VARCHAR) )
>in the table.
>while extracting the
>expression.
>.
>|||>> Does Enterprise Manager have a wizard that can generate the t-sql for
No, they are optional. You need not worry about it.
Btw, for the problem you posted, instead of concatenating the columns you
could do a series of OR-ed correlations like:
WHERE t1.yearnum <= t.yearnum
OR ( t1.yearnum = t.yearnum
AND t1.t1.monthnum <= t.t1.monthnum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum <= t.areaNum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum = t.areaNum
AND t1.productNum <= t.productNum )
Anith|||Thanks again for this explanation. I guess I will just
have to keep experimenting. This other method did work.

>--Original Message--
generate the t-sql for
>No, they are optional. You need not worry about it.
>Btw, for the problem you posted, instead of
concatenating the columns you
>could do a series of OR-ed correlations like:
>WHERE t1.yearnum <= t.yearnum
> OR ( t1.yearnum = t.yearnum
> AND t1.t1.monthnum <= t.t1.monthnum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum <= t.areaNum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum = t.areaNum
> AND t1.productNum <= t.productNum )
>--
>Anith
>
>.
>

Rowcount in tables

Can someone throw light on how to get the rowcount of a table that is stored in any system tables? I want to get tablename and rowcount for all user tables in a database in a query. Is there anyway other than count(*)?
Thanks
VinnieDBCC UPDATEUSAGE (0) WITH COUNT_ROWS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_24rp.asp)

SELECT rows, Object_name(id)
FROM dbo.sysindexes
WHERE indid IN (0, 1)If you skip the DBCC, you'll get a SWAG guess, but none too accurate in a busy server.

-PatP|||sysindexes has a rowcount, see BOL for more details on this.

EDIT: sniped again!|||Thanks guys.|||Check for sp_spaceused. It may help you.|||Here's my little addtion to the pile: (built on MSSQL2k)

DECLARE @.MinSize dec(28,2), @.LikeName varchar(45), @.SizeSort bit, @.IncludeLogs bit
--DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

SET @.MinSize = .00
SET @.LikeName = '' --Company%'
SET @.SizeSort = 1
SET @.IncludeLogs = 0

/*
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)

** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
IF @.LikeName = '' SET @.LikeName = '%'

CREATE TABLE #space
(
name varchar(30),
id int,
type char(1),
rows int NULL,
reserved dec(28,2) NULL,
data dec(28,2) NULL,
datapages dec(28,2) NULL,
blob dec(28,2) NULL,
indexp dec(28,2) NULL,
unused dec(28,2) NULL,
rowsize dec(28,2) NULL
)

SET NOCOUNT ON

/************************************************** ***********
** Generate a list of all User and System tables.
** AND
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
INSERT INTO #space (name, id, type, reserved)
SELECT LEFT(obj.name,30), obj.id, obj.type, SUM(reserved)
FROM sysindexes idx, sysobjects obj
WHERE idx.id = obj.id
AND idx.indid IN (0, 1, 255)
AND obj.type in ('S','U')
AND obj.name != 'syslogs'
AND obj.name LIKE @.LikeName
AND (@.IncludeLogs = 1 OR obj.name NOT LIKE '%Log')
GROUP BY obj.name, obj.id, obj.type

/************************************************** ***********
** Initialize these to zero.
*/
UPDATE #space
SET rows = 0, data = 0, blob = 0, indexp = 0, unused = 0

/************************************************** ***********
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
UPDATE #space
SET data = data + ISNULL((
SELECT SUM(idx.dpages)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid < 2), 0)
FROM #space spc

UPDATE #space
SET blob = blob + ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid = 255), 0)
FROM #space spc

/************************************************** ***********
** index: sum(used) where indid in (0, 1, 255) - data space
*/
UPDATE #space
SET indexp = ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid IN (0, 1, 255)), 0) - data - blob
FROM #space spc

/************************************************** ***********
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
UPDATE #space
SET unused = reserved - ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid IN (0, 1, 255)), 0)
FROM #space spc

/************************************************** ***********
** rows: rows where indid < 2
*/
UPDATE #space
SET rows = idx.rows
FROM #space spc, sysindexes idx
WHERE spc.id = idx.id
AND idx.indid < 2

/************************************************** ***********
** Page Size: for Windows NT
*/
DECLARE @.PageSize int, @.UsablePage int
SELECT @.PageSize = low
FROM master.dbo.spt_values
WHERE number = 1 AND type = 'E'

set @.UsablePage = @.PageSize - 132

/************************************************** ***********
** Compute the results
*/
UPDATE #space SET
reserved = reserved * @.PageSize,
datapages = data,
data = data * @.PageSize,
blob = blob * @.PageSize,
indexp = indexp * @.PageSize,
unused = unused * @.PageSize

UPDATE #space SET
rowsize =
CASE
WHEN rows < 50 THEN -1
ELSE ((data) / rows)
END

UPDATE #space SET
reserved = reserved / 1024.0 / 1024.0,
data = data / 1024.0 / 1024.0,
blob = blob / 1024.0 / 1024.0,
indexp = indexp / 1024.0 / 1024.0,
unused = unused / 1024.0 / 1024.0

/************************************************** ***********
** Finally: output the report header

*/

PRINT GETDATE()
PRINT ''

IF @.MinSize != 0
PRINT 'Tables with a Total space used of ' + LTRIM(STR(@.MinSize, 8,1)) + ' MB or greater in the ' + DB_NAME() + ' database.'
ELSE
PRINT 'All tables in the ' + DB_NAME() + ' database.'

PRINT ''

/************************************************** ***********
** Finally: output the totals
*/

DECLARE @.DataTotal dec(28,2), @.BlobTotal dec(28,2), @.IndexTotal dec(28,2), @.Format varchar(15)

SELECT @.DataTotal = SUM(data),
@.BlobTotal = SUM(blob),
@.IndexTotal = SUM(indexp)
FROM #space

SET @.Format = CONVERT(varchar(15),convert(money,@.DataTotal+@.Blob Total+@.IndexTotal),1)
PRINT 'Space used : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.DataTotal),1)
PRINT 'Space used by table data : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.BlobTotal),1)
PRINT 'Space used by text/image data : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.IndexTotal),1)
PRINT 'Space used by table indexes : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),@.PageSize)
PRINT 'Page size : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' Bytes'
SET @.Format = CONVERT(varchar(15),@.UsablePage)
PRINT 'Usable Page size : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' Bytes'
PRINT ''

/************************************************** ***********

** Finally: output the detail
*/

update #space
set rowsize = CASE
WHEN rowsize < 0 THEN 0
WHEN (data) < .1 THEN 0
ELSE rowsize
END

SELECT
TableName = name,
Rows = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,rows),1)),
Total = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,data+blob+indexp),1)),
Data = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,data),1)),
Blob = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,blob),1)),
Indexes = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,indexp),1)),
RowBytes = convert(varchar(11),
CASE
WHEN rowsize = 0 THEN 'n/a'
ELSE CONVERT(varchar(15),convert(money,rowsize),1)
END),
RowsPage = convert(varchar(11),
CASE
WHEN rows = 0 THEN 'n/a'
ELSE CONVERT(varchar(15),convert(money,rows/datapages),1)
END),
Pages = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,datapages),1)),
TotalSize = data+indexp+blob
INTO #report
FROM #space
WHERE data+indexp >= @.MinSize AND type = 'U' and name != 'dtproperties'

UPDATE #report
SET Rows = REPLACE(REPLACE(Rows,'.00',''),'.0',''),
Pages = REPLACE(REPLACE(Pages,'.00',''),'.0','')

UPDATE #report
SET Rows = REPLICATE(' ',11-DATALENGTH(Rows)) + Rows,
Total = REPLICATE(' ',11-DATALENGTH(Total)) + Total,
Data = REPLICATE(' ',11-DATALENGTH(Data)) + Data,
Blob = REPLICATE(' ',11-DATALENGTH(Blob)) + Blob,
Indexes = REPLICATE(' ',11-DATALENGTH(Indexes)) + Indexes,
RowsPage = REPLICATE(' ',11-DATALENGTH(RowsPage)) + RowsPage,
Pages = REPLICATE(' ',11-DATALENGTH(Pages)) + Pages,
RowBytes = REPLICATE(' ',11-DATALENGTH(RowBytes)) + RowBytes

PRINT ' --- Average --- ----- Table Space In MB -----'

IF @.SizeSort = 1
SELECT
TableName,
' Row Size' = RowBytes,
' Rows/Page' = RowsPage,
' Data Pages' = Pages,
' Rows' = Rows,
' Total' = Total,
' Data' = Data,
' Text/Img' = Blob,
' Index' = Indexes
FROM #report
ORDER BY TotalSize DESC
ELSE
SELECT
TableName,
' Row Size' = RowBytes,
' Rows/Page' = RowsPage,
' Data Pages' = Pages,
' Rows' = Rows,
' Total' = Total,
' Data' = Data,
' Text/Img' = Blob,
' Index' = Indexes
FROM #report
ORDER BY TableName

PRINT ''

DROP TABLE #space
DROP TABLE #report|||Here's my little addtion to the pile: (built on MSSQL2k)Great zot there, bubba! I'd consider that a pile all its own, not an addition to an existing pile! Whew, I can't wait to see a major contribution!

-PatP|||I got the following errors when I ran this in Query Analyzer:

Server: Msg 137, Level 15, State 2, Line 172
Must declare the variable '@.Blob'.
Server: Msg 170, Level 15, State 1, Line 201
Line 201: Incorrect syntax near 'ney'.|||i think you might have a simple wrapping issue...@.blob isnt a variable in this script but @.blobtotal is.

testing it...that is exactly what happened...

any tips on posting this without having the format chang on me?|||try the attachment...

for some reason pasting it FROM this forum adds some freaky yeaky spaces.

they dont appear as spaces in the forum window, but they sure as hell get added if you copy/paste it out of here...

...sorry...forum noooob issue.|||Cool that worked. Check this script out that I've been using.sql