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?
 
Gustavo Perez BuenrostroConnect With a Mentor Commented:
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
 
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
jboydCommented:
I think that they are statistics rather than indexes. They are created automatically when you have auto update statistics enabled.
0
 
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
All Courses

From novice to tech pro — start learning today.