| Author |
Topic |
|
Jim
Starting Member
12 Posts |
 Posted - 2002-08-19 : 13:16:57
|
| It says in MSDN that "SQL Server automatically creates indexes for certain types of constraints (for example, PRIMARY KEY and UNIQUE constraints). You can further customize the table definitions by creating indexes that are independent of constraints." But when I run sp_helpindex it is listing indexes that SQL is creating on its own for fields without unique constraints. Any ideas about what is going on? |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-19 : 14:13:23
|
| Your specific DDL is key here, otherwise all that can be offered to you are guesses.If you could, script out both the table and the index(es) involved.Jonathan Boott, MCDBA{0} |
 |
|
|
Jim
Starting Member
12 Posts |
Posted - 2002-08-19 : 14:47:09
|
| I have found out a few more things about this since my first post.SQL automatically creates these "indexes" with the name format _wa_sys_<column_name>_<xxxx> when the AUTO_CREATE_STATISTICS database option is set ON. It creates these entries in the index table when a column is used in a "WHERE" or "ORDER" clause. My question is are these index columns actually functioning as indexes or are they just there for statistcs purposes? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-19 : 16:14:23
|
| SQL Server does need to store statistics on indexed columns to determine the selectivity of a given index. The statistics themselves are not indexes, though, but rather histogram data.Are you using a third party modeling tool such as Erwin to construct your schema?Jonathan Boott, MCDBA{0} |
|
|
|
Jim
Starting Member
12 Posts |
Posted - 2002-08-19 : 17:03:46
|
| No, a third party schema modeling program is not being used. In this case, I ran a create table SQL statement. It specified one key, a primary key on an auto increment column. The script then imported data from another table. At that point sp_help in the query analyzer showed just one index - one corresponding to the primary key. Then an ASP page that exercised the new table by running a number of select statements against unindexed columns was executed. sp_help now revealed a number of new "index" fields created by SQL with the name format "_wa_sys_<column_name>_<xxxx>". sp_help describes these new indexes under "index_description" as "nonclustered, statistics, auto create located on PRIMARY". |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-19 : 17:24:40
|
| Right, that's normal. As you said earlier, with the AUTO_CREATE_STATISTICS option set SQL Server will create statistics as it sees fit. Are you coming from an Access background? (I note you use the term 'auto-increment' instead of IDENTITY).Jonathan Boott, MCDBA{0} |
|
|
|
Jim
Starting Member
12 Posts |
Posted - 2002-08-19 : 17:47:34
|
| If a programmer does not specifically create indexes for these columns referenced by the "nonclustered, statistics, auto create indexes" will SQL do a full table scan when selecting these columns? Does anyone know if these "statistics indexes" perform the same functions as the regular indexes? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-19 : 18:44:02
|
| The issue of execution plans is a complex one. The optimizer uses many factors in determining an optimal plan. BOL covers the basics, and there are many good books available that discuss indexing in the larger context of performance tuning.Again, you haven't posted details, so I can't provide specific answers, but generally a good place to start is to examine the execution plan for a given query to see what the optimizer chose, and experiment with different indexing choices to see how they affect the generation of the plan.Jonathan Boott, MCDBA{0} |
|
|
|
|
sp_helpindex