Query Sysindexes

i hav a table, Test1, with a clustered index and two non clustered index defined on it.

When i use sp_help Test1, the CI and non clustered index are listed along with the column names.

when i query the sysindex table (for id = object_id(Test1))

i can see entries like _WA_Sys_<<ColumnName>>_3D5EEB29.

what are these entries? are they indexes? if yes, how these entries are created and what is the significance of these entries.

Pl discuss.

Thanks.Hi

Given the info - why would you not expect to see this entry? Did you explicitly name your indexes? If not the SQL Server generates names for you. I admit I can't remember the conventions it uses (I name my indexes).

Also, SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason.|||Hello.

Thanks for the reply.

i hav named my indexes. the entries i mentioned are displayed in addition to the indexes i created.

"SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason." -- could u please elaborate on this or let me know where i can find more info.

Thanks|||Entries like _WA_Sys... are for Statics.|||Entries like _WA_Sys... are for Statics.Thanks mihirclarion. http://www.google.com/custom?cx=013269018370076798483:gg7jrrhpsy4&cof=GFNT%3A%23666666%3BGALT%3A%23666666%3BLH%3A100 %3BCX%3ABlackle%3BVLC%3A%23999999%3BDIV%3A%2300000 0%3BFORID%3A1%3BT%3A%23999999%3BALC%3A%23cccccc%3B LC%3A%23cccccc%3BS%3Ahttp%3A%2F%2Fwww%2Eblackle%2E com%2F%3BL%3Ahttp%3A%2F%2Fwww%2Eheapmedia%2Ecom%2F blackle%2Flogo%2Ejpg%3BGIMP%3A%23666666%3BLP%3A1%3 BBGC%3A%23000000%3BAH%3Aleft&q=_WA_Sys&hl=en&client=pub-8993703457585266

Coo - that's a long URL.


"SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason." -- could u please elaborate on this or let me know where i can find more info.
BoL is the source of all knowledge:
http://msdn2.microsoft.com/en-us/library/ms177420.aspx|||Thank you :)|||_WA_Sys is auto generated statistics. If you execute a query and one (or more) of the columns in the WHERE clause does not have an index or an appropriate statistics, the SQL Server will generate statistics on the column for you. The reason it is named _WA_Sys is because it is system generated and programmed by the SQL Server developers sitting in Washington.

Example:

create table test(id int)
insert into test values(1)

-- SQL Server 2005: This query will now return 0 rows because no
-- statistics has been created for any column in the table
select s.name,o.name
from sys.stats s
inner join sys.objects o on s.object_id=o.object_id
where s.name like '_WA%' and o.name='test'

select id from test where id=1

-- This time this query will return one row because a statistics
-- was auto generated on the id column.
select s.name,o.name
from sys.stats s
inner join sys.objects o on s.object_id=o.object_id
where s.name like '_WA%' and o.name='test'

If you're using SQL Server 2000, use this statistics query instead:

select s.name,o.name from sysindexes s
inner join sysobjects o on s.id=o.id
where s.name like '_WA%' and o.name='test'