Interview question

I was asked in an interview what was the fastest way to count the rows in a
table?
I assume that: Select count(*) from Tablename - is or is not the fastest way
.
Is there another way that is faster or did I miss something.
thanksYour query would result in a full table scan. You can
also do this:
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.masterado.net
"brian" <brian@.discussions.microsoft.com> wrote in message
news:585DEB83-4414-4FD6-BDEA-2CF49C47FAB3@.microsoft.com...
>I was asked in an interview what was the fastest way to count the rows in a
> table?
> I assume that: Select count(*) from Tablename - is or is not the fastest
> way.
> Is there another way that is faster or did I miss something.
> thanks|||The count returned by this method can be used as an approximation but might
not be accurate. The SELECT COUNT(*) method should be used in normal
production code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:e1WlYqQ2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Your query would result in a full table scan. You can
> also do this:
> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <
> 2
>
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> http://www.masterado.net
>
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:585DEB83-4414-4FD6-BDEA-2CF49C47FAB3@.microsoft.com...
>|||> Your query would result in a full table scan.
Not necessarily. SELECT COUNT(*) FROM tblname can use (scan) any index on th
e table. Preferably
there is an index on a narrow column, and SQL Server can scan that index (le
ss pages to read).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:e1WlYqQ2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Your query would result in a full table scan. You can
> also do this:
> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <
2
>
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> http://www.masterado.net
>
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:585DEB83-4414-4FD6-BDEA-2CF49C47FAB3@.microsoft.com...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uT63vfT2FHA.892@.TK2MSFTNGP12.phx.gbl

> Not necessarily. SELECT COUNT(*) FROM tblname can use (scan) any
> index on the table. Preferably there is an index on a narrow column,
> and SQL Server can scan that index (less pages to read).
Wouldn't a clustered index be the fastest possible, so that the rows are lin
ed
up for a quickie?
I doubt, therefore I might be.|||> Wouldn't a clustered index be the fastest possible, so that the rows are
> lined up for a quickie?
Not sure what you mean by 'lined up' but the issue with the clustered index
is that the clustered index leaf nodes are the data rows. Consequently, the
scan of a 10GB table with 100M rows will require scanning 10GB of data. A
scan of a non-clustered index on an int column of that table will only scan
about 400MB data (plus overhead) in the non-clustered leaf nodes. This is
why the optimizer chooses the narrowest available index for the COUNT(*)
operation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:uDKVnIW2FHA.1148@.tk2msftngp13.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:uT63vfT2FHA.892@.TK2MSFTNGP12.phx.gbl
>
> Wouldn't a clustered index be the fastest possible, so that the rows are
> lined up for a quickie?
> --
> I doubt, therefore I might be.|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Ooe7gxW2FHA.2216@.TK2MSFTNGP15.phx.gbl

> in the non-clustered leaf nodes. This is why the optimizer chooses
> the narrowest available index for the COUNT(*) operation.
Thanks for the clarification!
I doubt, therefore I might be.|||"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:e1WlYqQ2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Your query would result in a full table scan. You can
> also do this:
> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <
> 2
>
You don't have to do a table scan. A scan of a secondary index will work
too. Moreover the value in sysindexes is not guaranteed to be correct. So
"Select count(*)" is correct, although the "right" answer probably touches
on all these issues.
David|||No, this will not always need to do a full table scan. Since every
nonclustered index has a pointer to every row, a leaf scan of any nc index
will also give an accurate result.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:e1WlYqQ2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Your query would result in a full table scan. You can
> also do this:
> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <
> 2
>
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> http://www.masterado.net
>
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:585DEB83-4414-4FD6-BDEA-2CF49C47FAB3@.microsoft.com...
>
>