Professional Web Applications Themes

How many indexes can I use in this table? - Microsoft SQL / MS SQL Server

I have this structure table (sorry I omitted column names for brevity) ======================= int 4 uniqueidentifier 16 int 4 binary 15 binary 125 binary 250 binary 125 1 binary 125 1 varchar 10 1 numeric 9 1 bit 1 1 bit 1 1 int 4 1 nvarchar 70 1 nvarchar 60 1 nvarchar 10 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 5 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 nvarchar 50 1 ...

  1. #1

    Default How many indexes can I use in this table?

    I have this structure table (sorry I omitted column names for brevity)


    =======================
    int 4
    uniqueidentifier 16
    int 4
    binary 15
    binary 125
    binary 250
    binary 125 1
    binary 125 1
    varchar 10 1
    numeric 9 1
    bit 1 1
    bit 1 1
    int 4 1
    nvarchar 70 1
    nvarchar 60 1
    nvarchar 10 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 5 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 5 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 50 1
    nvarchar 5 1
    nvarchar 35 1
    nvarchar 35 1
    nvarchar 35 1
    nvarchar 100 1
    bit 1 1
    nvarchar 100 1
    bit 1 1
    nvarchar 100 1
    bit 1 1
    nvarchar 6 1
    nvarchar 2 1
    nvarchar 50 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1
    bit 1 1

    =======================


    I have 220.000 rows in this table.
    Update activity isn't so frequent.

    I have 1 clustered index and 6 non-clustered indexes.

    Do you think it is good? Bad?
    Can I put other indexes?

    Any help appreciated.

    regards


    --
    fabrizio


    fabriZio Guest

  2. #2

    Default Re: How many indexes can I use in this table?

    fabriZio,

    Without supplying the queries you want to perform against the table,
    and your current index in DDL format you will not get much help.

    As you say updates are infrequent there is nothing (well maybe disk space)
    to prevent you going wild and adding covering indexes for every query you
    will ever run (up to 249).

    Looking at the execution plan ( Query yser ) for your queries will show
    you where you need more indexes, or you could use the index tuning wizard.

    Supplying DDL - ie "Create Table ..." code by scripting your table
    ( <right click> <all tasks > ... from Enterprise Manager ),
    including indexes may get you a better answer.

    Regards
    AJ

    "fabriZio" <it> wrote in message news:phx.gbl... 


    Andrew Guest

  3. #3

    Default Re: How many indexes can I use in this table?

    It is very important to know that Indexes play a vital role in performance
    .... But the scenario's where indexes get used depends on the way you access
    this table in real-time ... Hence from the definition and description given
    by you we wouldnot be able to speculate the usage pattern ...
    Provide us with more information on the usage then maybe we can give it a
    shot ... You can also use the ITW (Index Tuning Wizard) for seeing if your
    table needs indexes ... Take the recommendations given by ITW also ...

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    www.extremeexperts.com


    "fabriZio" <it> wrote in message
    news:phx.gbl... 


    Vinodk Guest

Similar Threads

  1. Using two indexes on the same table
    By J.F. Groff in forum MySQL
    Replies: 10
    Last Post: April 20th, 10:53 PM
  2. Use of indexes with table inheritance
    By Alvaro in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: February 7th, 11:08 PM
  3. N to M relationship table and indexes
    By Luc in forum Oracle Server
    Replies: 4
    Last Post: October 20th, 11:36 PM
  4. Replies: 2
    Last Post: August 12th, 07:55 AM
  5. How many indexes can 9i use on one table in a single query?
    By Roger Redford in forum Oracle Server
    Replies: 3
    Last Post: January 7th, 12:16 AM

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