www.webdeveloper.com
Results 1 to 3 of 3

Thread: efficient count > 30

  1. #1
    Join Date
    Dec 2003
    Location
    England, UK
    Posts
    432

    efficient count > 30

    Hi All

    What's the most efficient way to see if a table contains more than 30 rows?

    Obviously "select count(*) > 30" will work but will this count every row before doing the comparison? I suspect it does and was wondering if there was a better way. I'm using SQL Server 2008.

    Thanks

    Russell

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,226
    MySQL optimizes SELECT COUNT(*), so it's probably about as efficient as you'll get (doesn't really do a table scan in that case). Not sure about any other DBMS, though.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Sep 2011
    Posts
    59
    select count(*) in SQL 2008 did not count actual record it is read header record it is read data from system table, you can use script below to get your statistics from database
    Code:
    DECLARE @Temp TABLE (
    table_name sysname ,
    row_count bigINT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50))
    
    INSERT @Temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    
    
    SELECT a.*,
    (select COUNT(*) from information_schema.columns b
    	where a.table_name collate database_default
    	= b.table_name collate database_default) AS col_count
    FROM @Temp a
    ORDER BY row_count desc,CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles