Index with names _WA_Sys_

I have seen some indexes begining
with "_WA_Sys_". I tried to drop them, and I got the error "index is not present".

What are these indexes?

Thanks
Srikumar
srikumar_pAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jon_RaymondCommented:
They could be constraints, not indexes.
0
nico5038Commented:
Everything with _Sys_ is usually hidden and in use by the databaseprogram itself(SQL).

Under MS access you don't see these system objects, but you can 'unhide' them to check their contents. (If you have Access just check the System Objects in the Tools/Options panel (Under the View tab). You will see the MSys... tables.
0
jboydCommented:
I think that they are statistics rather than indexes. They are created automatically when you have auto update statistics enabled.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

srikumar_pAuthor Commented:
When I am selecting all the indexes, this too is getting selected. What I can I do to avoid these (bogus) indexes from getting selected?
0
nico5038Commented:
Within Access the only way to exclude them (when selecting each item of the table collection) is the explicit test for found items starting with the "MSys" characters and ignore them for further processing.
 
0
Gustavo Perez BuenrostroCommented:
srikumar_p,

SQL Server automatically creates indexes to optimise queries. These indexes can be identified checking the internal system-status information contained in status column of sysindexes table (8388608 decimal value).

To retrieve all auto create indexes:

select cast(object_name(id) as varchar) as 'table'
      ,status
      ,name
 from sysindexes
where status&8388608<>0


This sample shows the process:

1.- Create a sample table and populate it with data.

create table YourTable (Column1 int,Column2 int)
insert into YourTable values (1,4)
insert into YourTable values (1,4)
insert into YourTable values (2,5)
insert into YourTable values (2,5)
insert into YourTable values (2,5)
insert into YourTable values (2,5)

2. - Check if there are auto-create indexes.

select name
  from sysindexes
 where id=object_id('YourTable')
   and status&8388608<>0

Here is the result set (no indexes):

name
------------------------


3. - Run a query.

  select *
    from YourTable
group by Column1,Column2
order by Column1,Column2


4. - Check if there are auto-create indexes.

select name
  from sysindexes
 where id=object_id('YourTable')
   and status&8388608<>0


Here is the result set (indexes created):

name
------------------------
_WA_Sys_Column1_1F63A897
_WA_Sys_Column2_1F63A897

Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.