Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: 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.
    Cheers....

    baburajv

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    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
    Cheers....

    baburajv

  4. #4
    Join Date
    May 2007
    Posts
    49
    Entries like _WA_Sys.... are for Statics.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mihirclarion
    Entries like _WA_Sys.... are for Statics.
    Thanks mihirclarion. http://www.google.com/custom?cx=0132...93703457585266

    Coo - that's a long URL.

    Quote Originally Posted by baburajv
    "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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Thank you
    Cheers....

    baburajv

  7. #7
    Join Date
    Nov 2005
    Posts
    122
    _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:

    Code:
    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:

    Code:
    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'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •