SQL Server Performance

Optimizing Select Count(*)

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by subratac999@gmail.com, Feb 5, 2011.

  1. subratac999@gmail.com New Member

    Assume a case when I have count the no of available rows in a table, should we use "Select Count(*) From ", is it an optimized query.Note: I am not aware of the table name and its structure, actually it came from some parameter values. And I want to count its rows through .net code.Please let me know, what should be the most optimized query in this regards.SubrataC999@gmail.com
    subratac999@gmail.com, Feb 5, 2011
    #2

  2. FrankKalis Moderator

    Welcome to the forum!
    This thread might be interesting for you: http://sql-server-performance.com/Community/forums/p/32434/167409.aspx
    FrankKalis, Feb 6, 2011
    #3

  3. ismailadar New Member

    Hi,
    you can create a proc with table name as an input parametre.CREATE PROCEDURE Usp_GetTableRows
    @tblName
    varchar(50)AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;select
    rows from sys.partitions where object_id=object_id(@tblName)END
    GO
    ismailadar, Feb 6, 2011
    #4

  4. Madhivanan Moderator

    or
    select rows from sysindexes where indid<2 and name=@table_name
    Madhivanan, Feb 7, 2011
    #5

  5. FrankKalis Moderator

    [quote user="Madhivanan"]
    or
    select rows from sysindexes where indid<2 and name=@table_name
    [/quote]
    ...where @table_name really is @index_name... [:)]
    FrankKalis, Feb 7, 2011
    #6

  6. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"] <P>[quote user="Madhivanan"]</P><P>or</P><P>select rows from sysindexes where&nbsp;indid&lt;2 and name=@table_name</P><P>[/quote]&nbsp;</P><P>...where @table_name really is @index_name... <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"><BR></P><P>[/quote]</P><P>Yes. Actually I wanted to write this</P><P>select rows from sysindexes where&nbsp;indid&lt;2 and object_name(id)=@table_name</P>
    Madhivanan, Feb 8, 2011
    #7

  7. FrankKalis Moderator

    <p>[quote user="Madhivanan"]</p><p>[quote user="FrankKalis"] </p><p>[quote user="Madhivanan"]</p><p>or</p><p>select rows from sysindexes where&nbsp;indid&lt;2 and name=@table_name</p><p>[/quote]&nbsp;</p><p>...where @table_name really is @index_name... <img src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif" alt="Smile"><br></p><p>[/quote]</p><p>Yes. Actually I wanted to write this</p><p>select rows from sysindexes where&nbsp;indid&lt;2 and object_name(id)=@table_name</p><p>[/quote]&nbsp;</p><p>...this is sooo SQL Server 2000.... [;)] <br></p>
    FrankKalis, Feb 8, 2011
    #8

  8. satya Moderator

    .... and adding sys. before the system catalog should work.
    satya, Feb 8, 2011
    #9

Share This Page