Professional Web Applications Themes

Efficient Select Statement - Microsoft SQL / MS SQL Server

Hi, Consider the following scenario. Items table is having 5 columns with 1 million records. * ItemId (PK) * ItemName * ReorderLevel * ManufId * CreatedDate If i want to query the Item table for knowing the ReorderLevel of all items, it can done in following 2 ways: SELECT * FROM Items SELECT ItemId, ReorderLevel FROM Items Which one of the above 2 queries will be efficient and why? Will the Query written using SELECT * takes more time than the other one? Need your valuable comments on this....

  1. #1

    Default Efficient Select Statement

    Hi,

    Consider the following scenario.

    Items table is having 5 columns with 1 million records.

    * ItemId (PK)
    * ItemName
    * ReorderLevel
    * ManufId
    * CreatedDate

    If i want to query the Item table for knowing the
    ReorderLevel of all items, it can done in following 2 ways:

    SELECT * FROM Items

    SELECT ItemId, ReorderLevel FROM Items

    Which one of the above 2 queries will be efficient and
    why? Will the Query written using SELECT * takes more time
    than the other one?

    Need your valuable comments on this.
    sweetsell Guest

  2. #2

    Default Re: Efficient Select Statement

    Hi,
     
    THIS ONE!
    (Especially I expect Covered queries.)
    -------
    SHINICHI YONEDA ne.jp
    Microsoft Most Valuable Professional
    MVP for SQL Server 2002-2003

    "sweetsell" <com> wrote in message
    news:0b6101c36192$017fad50$gbl... 

    Shinichi Guest

  3. #3

    Default Re: Efficient Select Statement

    > If i want to query the Item table for knowing the 

    Yes, almost certainly.

    (a) one axiom I live by is to never use SELECT * in production code. It
    makes the behavior of your queries, and the success of your application(s),
    very dependent on the underlying structure of the table. For example, if
    someone were to go into Enterprise Manager and insert a column into the
    second slot, your SELECT * query comes back quite differently... and if you
    are using getRows or ordinal positions to retrieve data from your query, the
    data will suddenly look wrong or cause an error.

    (b) if you only want ItemId and ReorderLevel back from the database, why
    send the CreatedDate, ManufId and ItemName across the wire? You're sending
    unnecessary data across the network and to the client application, and it's
    a waste because it's never used...


    Aaron Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. Need help with select statement
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 04:53 AM
  3. possible to run a select with an if statement in it?
    By Jim in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 17th, 04:48 PM
  4. Need some help on a SELECT statement
    By Sydney in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 16th, 01:42 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139