efficient count > 30
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.
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.
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
DECLARE @Temp TABLE (
table_name sysname ,
EXEC sp_msforeachtable 'sp_spaceused ''?'''
(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