Non Clustered Index

Hi ,
When I creare a non clustered index , I get error as show below ,
Server: Msg 1904, Level 16, State 1, Line 1
Cannot specify more than 16 column names for statistics or index key list.
21 specified.
SQL Server only support up to 16 key values ?
Travis Tan
First off, this question should be posted in
Microsoft.public.sqlserver.programming. Clustering is a technology which
allows individual computers to share the same data and back each other up to
prevent system failures. Clients connect to a virtual server, whose
resources float between nodes which form the cluster.
Clustered indexes are when the data is clustered or grouped in a predefined
format or order for rapid retrieval of ranges of data.
16 columns makes your index very large and I suspect inefficient. You may be
able to use an indexed view to group your data in different orders for your
particular usage. And yes, clustered indexes only support a maximum of 16
columns or keys in SQL 200x.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan
|||Yes. Why are you trying to create an index with 21 columns in it? That is
quite a bit beyond overkill.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan
|||Hi
Wrong newsgroup, crossposted to microsoft.public.sqlserver.programming
Why would you want to create a compound index of 16 or more columns? Your
query has to be very specific to be able to use it and the overhead
maintaining it will be high too.
If you have a table, with columns A-Z and you build and index on A, B, C, D
(in that column sequence), a where clause on A, B, C could use the index, a
query on column B can't, neither can a query on C, D. Column A always has to
be involved as it is the 1st sort sequence for the column.
Maybe the DB design is not optimal if you need to go to such extremes.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan