|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to count rows ?Hello,
i'm (sql programming newbie) goin' crazy with that: I have several tables with a lot (10000 and more) of rows on a SQL2000 server. Before i do a select or anything else on the tables i wanna return the number of rows via stored procedure or a function. How can i simply count the number of rows of a table WITHOUT doing a select or whatever before i can count the rows. In online-help i read abaout a propert called ROWS, but how to use that ? Any idea ? Regards Ralf What about SELECT COUNT(*) FROM myTable in the stored procedure you mention?
-- Show quoteJack Vamvas ___________________________________ Receive free SQL tips - www.ciquery.com/sqlserver.htm "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> wrote in message news:#uyZmzNVGHA.5148@TK2MSFTNGP12.phx.gbl... > Hello, > i'm (sql programming newbie) goin' crazy with that: > I have several tables with a lot (10000 and more) of rows on a SQL2000 > server. Before i do a select or anything else on the tables i wanna return > the number of rows via stored procedure or a function. > How can i simply count the number of rows of a table WITHOUT doing a select > or whatever before i can count the rows. In online-help i read abaout a > propert called ROWS, but how to use that ? > Any idea ? > > Regards Ralf > > Hello,
thanks for the answer. I want create a function i can use for more than one table. But i dont know a way to give this function the tablename as parameter. I tried that: create function [dbo].[getRowCountOf] (@tableName nvarchar(20) returns integer AS return select count(*) from @tableName Show quote "Jack Vamvas" <delete_this_bit_jack@ciquery.com_delete> schrieb im Newsbeitrag news:KoidnbNxSocEyLDZRVnysw@bt.com... > What about SELECT COUNT(*) FROM myTable in the stored procedure you > mention? > > -- > Jack Vamvas > ___________________________________ > Receive free SQL tips - www.ciquery.com/sqlserver.htm > > "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> wrote in message > news:#uyZmzNVGHA.5148@TK2MSFTNGP12.phx.gbl... >> Hello, >> i'm (sql programming newbie) goin' crazy with that: >> I have several tables with a lot (10000 and more) of rows on a SQL2000 >> server. Before i do a select or anything else on the tables i wanna >> return >> the number of rows via stored procedure or a function. >> How can i simply count the number of rows of a table WITHOUT doing a > select >> or whatever before i can count the rows. In online-help i read abaout a >> propert called ROWS, but how to use that ? >> Any idea ? >> >> Regards Ralf >> >> > > sorry, i send to fast....
sql means, that i have to declare @tableName. Is there a way to make the tablename variable in a function ? Regards Ralf Show quote "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> schrieb im Newsbeitrag news:udBe4lOVGHA.5004@TK2MSFTNGP11.phx.gbl... > Hello, > thanks for the answer. I want create a function i can use for more than > one table. But i dont know a way to give this function the tablename as > parameter. I tried that: > > create function [dbo].[getRowCountOf] (@tableName nvarchar(20) > returns integer > AS > return select count(*) from @tableName > > "Jack Vamvas" <delete_this_bit_jack@ciquery.com_delete> schrieb im > Newsbeitrag news:KoidnbNxSocEyLDZRVnysw@bt.com... >> What about SELECT COUNT(*) FROM myTable in the stored procedure you >> mention? >> >> -- >> Jack Vamvas >> ___________________________________ >> Receive free SQL tips - www.ciquery.com/sqlserver.htm >> >> "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> wrote in message >> news:#uyZmzNVGHA.5148@TK2MSFTNGP12.phx.gbl... >>> Hello, >>> i'm (sql programming newbie) goin' crazy with that: >>> I have several tables with a lot (10000 and more) of rows on a SQL2000 >>> server. Before i do a select or anything else on the tables i wanna >>> return >>> the number of rows via stored procedure or a function. >>> How can i simply count the number of rows of a table WITHOUT doing a >> select >>> or whatever before i can count the rows. In online-help i read abaout a >>> propert called ROWS, but how to use that ? >>> Any idea ? >>> >>> Regards Ralf >>> >>> >> >> > > DECLARE @tablename SYSNAME;
SET @tablename = N'<enter table here>'; SELECT RowCnt FROM sysindexes WHERE id = OBJECT_ID(@tablename) AND indid IN (0,1); Otherwise, see http://www.sommarskog.se/dynamic_sql.html Show quote "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> wrote in message news:eD6O8nOVGHA.1868@TK2MSFTNGP09.phx.gbl... > sorry, i send to fast.... > sql means, that i have to declare @tableName. > Is there a way to make the tablename variable in a function ? > > Regards > Ralf > > "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> schrieb im Newsbeitrag > news:udBe4lOVGHA.5004@TK2MSFTNGP11.phx.gbl... >> Hello, >> thanks for the answer. I want create a function i can use for more than >> one table. But i dont know a way to give this function the tablename as >> parameter. I tried that: >> >> create function [dbo].[getRowCountOf] (@tableName nvarchar(20) >> returns integer >> AS >> return select count(*) from @tableName >> >> "Jack Vamvas" <delete_this_bit_jack@ciquery.com_delete> schrieb im >> Newsbeitrag news:KoidnbNxSocEyLDZRVnysw@bt.com... >>> What about SELECT COUNT(*) FROM myTable in the stored procedure you >>> mention? >>> >>> -- >>> Jack Vamvas >>> ___________________________________ >>> Receive free SQL tips - www.ciquery.com/sqlserver.htm >>> >>> "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> wrote in message >>> news:#uyZmzNVGHA.5148@TK2MSFTNGP12.phx.gbl... >>>> Hello, >>>> i'm (sql programming newbie) goin' crazy with that: >>>> I have several tables with a lot (10000 and more) of rows on a SQL2000 >>>> server. Before i do a select or anything else on the tables i wanna >>>> return >>>> the number of rows via stored procedure or a function. >>>> How can i simply count the number of rows of a table WITHOUT doing a >>> select >>>> or whatever before i can count the rows. In online-help i read abaout a >>>> propert called ROWS, but how to use that ? >>>> Any idea ? >>>> >>>> Regards Ralf >>>> >>>> >>> >>> >> >> > > I have seen in the past that there is a way to get the full table count
out of the master database, thus being much quicker than actually counting the whole table. However not only is that not a great idea for a newbie to be trying, but also I can't remember how to do it. Out of interest, why do you need to count your tablerows before you do anything with them? Hello
thanks for the quick answer. The reason to read out the rowcount is that i write a .NET-Application where i read parts of datarows into the application, work with it and update it in the database. This progress will take a while so i wanna add a progressbar. The only way (i know) to display a progressbar is to know the last record number to set the min=0 and the max=rowcount. Regards Ralf Show quote "Will" <william_p***@yahoo.co.uk> schrieb im Newsbeitrag news:1143822887.413725.285180@e56g2000cwe.googlegroups.com... >I have seen in the past that there is a way to get the full table count > out of the master database, thus being much quicker than actually > counting the whole table. However not only is that not a great idea for > a newbie to be trying, but also I can't remember how to do it. > > Out of interest, why do you need to count your tablerows before you do > anything with them? > This will get you a quick estimate of row count of a table in your db from
the sysindex table. It will be less of a performance hit than a count(*), and appears to be what you are asking for. Maybe some of the experts here can tell us of the downfalls of using this, other than it's obvious limitation of only working on full table queries. SELECT [RowCount] = MAX(si.rows) FROM sysobjects so, sysindexes si WHERE so.name = @TableName AND si.id = OBJECT_ID(so.name) Show quote "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> wrote in message news:O%23tiHhOVGHA.4772@TK2MSFTNGP14.phx.gbl... > Hello > thanks for the quick answer. The reason to read out the rowcount is that i > write a .NET-Application where i read parts of datarows into the > application, work with it and update it in the database. This progress > will take a while so i wanna add a progressbar. The only way (i know) to > display a progressbar is to know the last record number to set the min=0 > and the max=rowcount. > > Regards > Ralf > > "Will" <william_p***@yahoo.co.uk> schrieb im Newsbeitrag > news:1143822887.413725.285180@e56g2000cwe.googlegroups.com... >>I have seen in the past that there is a way to get the full table count >> out of the master database, thus being much quicker than actually >> counting the whole table. However not only is that not a great idea for >> a newbie to be trying, but also I can't remember how to do it. >> >> Out of interest, why do you need to count your tablerows before you do >> anything with them? >> > > > How can i simply count the number of rows of a table WITHOUT doing a You will need to perform a select of some kind.> select You can try select rowcnt from sysindexes where id = OBJECT_ID('tablename') and indid in (0,1) However, this will depend on current sysindexes data (e.g. by running DBCC UPDATEUSAGE). If you don't update usage first, then hopefully you can rely on potentially inaccurate numbers. Otherwise, SELECT COUNT(*) FROM tablename is the only accurate way. A Hello,
thanks for answer. I tried this: create function [dbo].[getRowCountOf] (@tableName nvarchar(20) returns integer as return select count(*) from @tableName But it does'nt work because sql means i have to declare @tableName. Do You know how i can make the tablename variable ? Otherwise i will use your hint: create function [dbo].[getRowCountOf] (@tableName nvarchar(20) returns table as return select rowcnt from sysindexes where id = OBJECT_ID('(@tableName') and indid in (0,1) I've tried it and it works :-) Regards Ralf Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schrieb im Newsbeitrag news:%23b5lwGOVGHA.2704@tk2msftngp13.phx.gbl... >> How can i simply count the number of rows of a table WITHOUT doing a >> select > > You will need to perform a select of some kind. > > You can try > > select rowcnt from sysindexes where id = OBJECT_ID('tablename') and indid > in (0,1) > > However, this will depend on current sysindexes data (e.g. by running DBCC > UPDATEUSAGE). If you don't update usage first, then hopefully you can > rely on potentially inaccurate numbers. Otherwise, SELECT COUNT(*) FROM > tablename is the only accurate way. > > A > By the way, without quotas arount @tableName
Show quote "Ralf Pelzl" <ralf.pe***@inf.hs-anhalt.de> schrieb im Newsbeitrag news:elwIwxOVGHA.1572@tk2msftngp13.phx.gbl... > Hello, > thanks for answer. I tried this: > > create function [dbo].[getRowCountOf] (@tableName nvarchar(20) > returns integer > as > return select count(*) from @tableName > > But it does'nt work because sql means i have to declare @tableName. Do You > know how i can make the tablename variable ? Otherwise i will use your > hint: > > create function [dbo].[getRowCountOf] (@tableName nvarchar(20) > returns table > as > return select rowcnt from sysindexes where id = OBJECT_ID('(@tableName') > and indid in (0,1) > > I've tried it and it works :-) > > Regards > Ralf > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schrieb im > Newsbeitrag news:%23b5lwGOVGHA.2704@tk2msftngp13.phx.gbl... >>> How can i simply count the number of rows of a table WITHOUT doing a >>> select >> >> You will need to perform a select of some kind. >> >> You can try >> >> select rowcnt from sysindexes where id = OBJECT_ID('tablename') and indid >> in (0,1) >> >> However, this will depend on current sysindexes data (e.g. by running >> DBCC UPDATEUSAGE). If you don't update usage first, then hopefully you >> can rely on potentially inaccurate numbers. Otherwise, SELECT COUNT(*) >> FROM tablename is the only accurate way. >> >> A >> > > > > create function [dbo].[getRowCountOf] (@tableName nvarchar(20) If you are using the above function, then the AUTO UPDATE STATISTICS option > > returns table > > as > > return select rowcnt from sysindexes where id = OBJECT_ID('(@tableName') > > and indid in (0,1) > > of the database should be on. If not you might find it erronous.  |
|||||||||||||||||||||||
Other interesting topics