Hi folks,
I have a large table which has approximately 150 million records. I am
running a SELECT COUNT(*) query to find out the exact number of rows
present in that table. It is running for more than an hour, yet to
complete. Estimated execution plan shows that it uses clusted index
scan. But it does seem it is using the index. What is the best way to
find out the exact number of records present in that table quickly.
Thanks in advance.
--
*** Sent via Developersdex http://www.examnotes.net ***COUNT(*) is the best way but it sounds like you are being blocked. What
does sp_who2 say? And by the way SQL Server will choose the best index with
a count(*) and it does not have to be the clustered index.
Andrew J. Kelly SQL MVP
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:evTgGq$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||This might help.
http://toponewithties.blogspot.com/...count-them.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:evTgGq$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||If you don't ned to be up to the second, you can check sysindexes. To be
more accurate, you can issue DBCC UPDATEUSAGE against the table first, but
this will take time just like the scan does...
What's probably happening is that there is activity against the table. So,
another way to make the count return quicker (though, again, it may be out
of date by the time your brain processes it) is to use SELECT COUNT(*) FROM
table WITH (NOLOCK);
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:evTgGq$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Ideally, you would have an non-clustered index on a narrow column so you wou
ld fit many rows on such
an index page. SQL Server would now scan that index instead of scanning the
data pages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:evTgGq$zFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||If you don't require an exact answer, it isn't necessary use a
SELECT count(*) query on the rows in a table to get the row
count. SQL Server keeps the row count in sysindexes and it
can be retrieved there. The key is to select the correct
record from sysindexes.
Sysindexes is a system table that exists in every database.
SQL Server maintains at least one row in sysindexes for every
user table. A few of the most important columns are:
Column Data Type Description
-- -- ---
id int ID of the table referred to by this row
indid int See the text that follows...
rowcnt bigint Number of rows in the index
The indid column tells us what part of the table structure this
row of sysindexes is referring to:
indid value Description
-- ----
0 Table data when there is no clustered index
1 Refers to the clustered index
2 - 254 Non-clustered indexes
255 Text or Image data pages
A table will only have an entry in sysindexes with an indid value
of for 0 or 1, never both. That's the entry that we're
interested in because its rowcnt field gives is the number of
rows in the table. There's a query that shows the table, index
and indid from the pubs database:
/-- Copy From Below this line --USE pubs
GO
SELECT so.[name] as [table name]
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]
\-- Stop copying above this line --/
(Results)
table name Index Name indid
-- -- --
authors UPKCL_auidind 1
discounts NULL 0
employee employee_ind 1
jobs PK__jobs__117F9D94 1
pub_info UPKCL_pubinfo 1
publishers UPKCL_pubind 1
roysched NULL 0
sales UPKCL_sales 1
stores UPK_storeid 1
titleauthor UPKCL_taind 1
titles UPKCL_titleidind 1
As you can see from the results, most of the indexes are
clustered (indid=1) but a few tables such as discounts
don't have a clustered index (indid=0).
I started this newsletter with "If you don't need an exact
answer..." That's because there are times when rowcnt is
not the exact number of records in the table. This can
be corrected by updating statistics on the table with:
dbcc updateusage
go
Here's the CREATE FUNCTION script for udf_Tbl_RowCOUNT:
/-- Copy From Below this line --
CREATE FUNCTION dbo.udf_Tbl_RowCOUNT (
@.sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
/*
* Returns the row count for a table by examining sysindexes.
* This function must be run in the same database as the table.
*
* Common Usage:
SELECT dbo.udf_Tbl_RowCOUNT ('')
* Test
PRINT 'Test 1 Bad table ' + CASE WHEN SELECT
dbo.udf_Tbl_RowCOUNT ('foobar') is NULL
THEN 'Worked' ELSE 'Error' END
* ) Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
****************************************
***********************/
AS BEGIN
DECLARE @.nRowCount INT -- the rows
DECLARE @.nObjectID int -- Object ID
SET @.nObjectID = OBJECT_ID(@.sTableName)
-- Object might not be found
IF @.nObjectID is null RETURN NULL
SELECT TOP 1 @.nRowCount = rows
FROM sysindexes
WHERE id = @.nObjectID AND indid < 2
RETURN @.nRowCount
END
GO
GRANT EXECUTE ON [dbo].[udf_Tbl_RowCOUNT] TO PUBLIC
GO
\-- Stop copying above this line --/
Let's use it:
/-- Copy From Below this line --
use pubs -- assuming the UDF was created in pubs
go
SELECT [name]
, dbo.udf_Tbl_RowCOUNT ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [name]
GO
\-- Stop copying above this line --/
(Results)
name Row Count
-- --
authors 24
discounts 3
employee 43
jobs 14
pub_info 8
publishers 8
roysched 86
sales 21
stores 6
titleauthor 25
titles 18
That's all there is to it.
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Venkat" wrote:
> Hi folks,
> I have a large table which has approximately 150 million records. I am
> running a SELECT COUNT(*) query to find out the exact number of rows
> present in that table. It is running for more than an hour, yet to
> complete. Estimated execution plan shows that it uses clusted index
> scan. But it does seem it is using the index. What is the best way to
> find out the exact number of records present in that table quickly.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***
>
