Data handling in sysindexes table

Hi,
I require your help in understanding the way data is maintained in
sysindexes table, I have two doubts,
1) While creating a foreign key constraint for a table, new entry is made in
this table, after dropping this constraint, the entry added with indid equal
to 2 is not deleted from sysindexes table.
2) When we transfer the data from one file group to another, groupid field
value for
for indid equal to 2 still shows the old file group value.
Please execute the following sql statements to simulate the scenario I am
talking about.
create table Department
(DeptId int not null,
DeptName varchar(100))
go
insert into Department
values(1, 'One')
insert into Department
values(2, 'two')
insert into Department
values(3, 'three')
select * from sysindexes
where id = object_id('Department')
-- you will see one entry with indid equal to 0
alter table Department
add constraint PK_DeptId PRIMARY KEY (DeptId)
go
select * from sysindexes
where id = object_id('Department')
-- you will see one entry with indid equal to 1
create table Employee
(EmpId int not null,
EmpName varchar(100),
DeptId int not null)
go
select * from sysindexes
where id = object_id('Employee')
insert into employee
values(1, 'Emp1', 1)
insert into employee
values(2, 'Emp2', 2)
insert into employee
values(3, 'Emp3', 3)
-- you will see one entry with indid equal to 0
alter table Employee
add constraint PK_EmpId PRIMARY KEY (EmpId)
go
alter table Employee
add constraint FK_Emp_DeptId FOREIGN KEY (DeptId)
REFERENCES Department(DeptId)
go
select * from sysindexes
where id = object_id('Employee')
-- you will see two entries with indid values 1 and 2
alter table Employee
drop constraint FK_Emp_DeptId
go
DBCC dbreindex ('Employee', '', 50)
DBCC dbreindex ('Department', '', 50)
select * from sysindexes
where id = object_id('Employee')
-- still you will see those two entries, and indid 2 is not removed from
sysindexes table.
To change the file group, I follow the steps given below, after completing
these steps
groupid value with '2' in sysindexs table for employee object is not getting
updated to
the new file group id value.
1) We drop the employee table's clustered index and recreate clustered index
on the new file group, after this group id field in sysindexes table is
changed
to the new file group in employee table for the row where indid is 1.
2) We drop the foreign key constraint in employee table.
3) We drop the department table's clustered index and recreate clustered
index
on the new file group, after this group id field in sysindexes table is
changed
to the new file group in department table.
4) Foreign key constraint is created again on the employee table by
referring department
table.
5) Execute this statement, select * from sysindexes where id =
object_id('Employee')
6) Groupid field in sysindexes table for employee table against indid value
2 still
shows the old file group id.
This behaviour in sysindexes table is bit confusing, please help me out,
thank you.
Regards,
DevaI think the changes will get reflected when the Server feels that the
statisctics are out of date.
Try
EXEC sp_updatestats
before you check for the change. Its just a guess.
Let me know if this worked.|||Hi,
Data in sysindexes table is not updated even after running
EXEC sp_updatestats
statement, thank you.
Regards,
Deva|||Can you try
DBCC UPDATEUSAGE ('<DBNAME>','<TABLENAME>')
--
"Deva" wrote:

> Hi,
> Data in sysindexes table is not updated even after running
> EXEC sp_updatestats
> statement, thank you.
> Regards,
> Deva
>|||Hi,
Bad luck, even updateusage command does not refresh the data in sysindexes
table, thank you.
Regards,
Deva
"Omnibuzz" wrote:
> Can you try
> DBCC UPDATEUSAGE ('<DBNAME>','<TABLENAME>')
> --
>
>
> "Deva" wrote:
>|||Deva
sp_helpindex 'Employee'
sp_helpconstraint 'Employee'
Yep, it seems to be fixed in SQL Server 2005
select * from sys.indexes
where object_id= object_id('Employee')
Shows only one row
"Deva" <Deva@.discussions.microsoft.com> wrote in message
news:E25567E2-F79B-4516-AD31-6CDC58567F32@.microsoft.com...
> Hi,
> Bad luck, even updateusage command does not refresh the data in sysindexes
> table, thank you.
> Regards,
> Deva
>
> "Omnibuzz" wrote:
>|||1) This is most likely statistics created. What is the name of that "index"?
2) Since statistics doesn't store any pages, the file group is irrelevant. M
ost probably Ms decided
not to change this (dummy) value in case you move the physical table, since
the value should really
be NULL in the first place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Deva" <Deva@.discussions.microsoft.com> wrote in message
news:E422EDEF-5DA1-4135-97EF-DCA8637E7B63@.microsoft.com...
> Hi,
> I require your help in understanding the way data is maintained in
> sysindexes table, I have two doubts,
> 1) While creating a foreign key constraint for a table, new entry is made
in
> this table, after dropping this constraint, the entry added with indid equ
al
> to 2 is not deleted from sysindexes table.
> 2) When we transfer the data from one file group to another, groupid field
> value for
> for indid equal to 2 still shows the old file group value.
> Please execute the following sql statements to simulate the scenario I am
> talking about.
> create table Department
> (DeptId int not null,
> DeptName varchar(100))
> go
> insert into Department
> values(1, 'One')
> insert into Department
> values(2, 'two')
> insert into Department
> values(3, 'three')
> select * from sysindexes
> where id = object_id('Department')
> -- you will see one entry with indid equal to 0
> alter table Department
> add constraint PK_DeptId PRIMARY KEY (DeptId)
> go
> select * from sysindexes
> where id = object_id('Department')
> -- you will see one entry with indid equal to 1
> create table Employee
> (EmpId int not null,
> EmpName varchar(100),
> DeptId int not null)
> go
> select * from sysindexes
> where id = object_id('Employee')
> insert into employee
> values(1, 'Emp1', 1)
> insert into employee
> values(2, 'Emp2', 2)
> insert into employee
> values(3, 'Emp3', 3)
> -- you will see one entry with indid equal to 0
> alter table Employee
> add constraint PK_EmpId PRIMARY KEY (EmpId)
> go
> alter table Employee
> add constraint FK_Emp_DeptId FOREIGN KEY (DeptId)
> REFERENCES Department(DeptId)
> go
> select * from sysindexes
> where id = object_id('Employee')
> -- you will see two entries with indid values 1 and 2
> alter table Employee
> drop constraint FK_Emp_DeptId
> go
> DBCC dbreindex ('Employee', '', 50)
> DBCC dbreindex ('Department', '', 50)
> select * from sysindexes
> where id = object_id('Employee')
> -- still you will see those two entries, and indid 2 is not removed from
> sysindexes table.
> To change the file group, I follow the steps given below, after completing
> these steps
> groupid value with '2' in sysindexs table for employee object is not getti
ng
> updated to
> the new file group id value.
> 1) We drop the employee table's clustered index and recreate clustered ind
ex
> on the new file group, after this group id field in sysindexes table is
> changed
> to the new file group in employee table for the row where indid is 1.
> 2) We drop the foreign key constraint in employee table.
> 3) We drop the department table's clustered index and recreate clustered
> index
> on the new file group, after this group id field in sysindexes table is
> changed
> to the new file group in department table.
> 4) Foreign key constraint is created again on the employee table by
> referring department
> table.
> 5) Execute this statement, select * from sysindexes where id =
> object_id('Employee')
> 6) Groupid field in sysindexes table for employee table against indid valu
e
> 2 still
> shows the old file group id.
> This behaviour in sysindexes table is bit confusing, please help me out,
> thank you.
> Regards,
> Deva|||On Tue, 9 May 2006 22:26:01 -0700, Deva wrote:

>Hi,
>I require your help in understanding the way data is maintained in
>sysindexes table, I have two doubts,
>1) While creating a foreign key constraint for a table, new entry is made i
n
>this table, after dropping this constraint, the entry added with indid equa
l
>to 2 is not deleted from sysindexes table.
>2) When we transfer the data from one file group to another, groupid field
>value for
>for indid equal to 2 still shows the old file group value.
(snip)
Hi Deva,
The extra row in sysindexes that you see is not for an index, but for
column statistics. If you look at the name column in the output from
SELECT * FROM sysindexes WHERE ...
you'll see that the name start with "_WA_Sys_". For experienced SQL
Server user, this is a clear indication that this is not a real index,
but an entry for column statistics.
I don't think it's documented anywhere, but a more reliable way to
seperate statistics entries from real indexes in sysindexes is to use
the status column:
SELECT *
FROM sysindexes
WHERE id = OBJECT_ID('Employee')
AND status & 64 = 0
Or, even better, don't query the system tables at all. Use the supported
stored procedures instead:
EXEC sp_helpindex 'Employee'
Hugo Kornelis, SQL Server MVP|||Hugo,

> AND status & 64 = 0
How about using INDEXPROPERTY() instead? ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:9ga362h8at3nk05gmhtj2hnb5hqfq0sop4@.
4ax.com...
> On Tue, 9 May 2006 22:26:01 -0700, Deva wrote:
>
> (snip)
> Hi Deva,
> The extra row in sysindexes that you see is not for an index, but for
> column statistics. If you look at the name column in the output from
> SELECT * FROM sysindexes WHERE ...
> you'll see that the name start with "_WA_Sys_". For experienced SQL
> Server user, this is a clear indication that this is not a real index,
> but an entry for column statistics.
> I don't think it's documented anywhere, but a more reliable way to
> seperate statistics entries from real indexes in sysindexes is to use
> the status column:
> SELECT *
> FROM sysindexes
> WHERE id = OBJECT_ID('Employee')
> AND status & 64 = 0
> Or, even better, don't query the system tables at all. Use the supported
> stored procedures instead:
> EXEC sp_helpindex 'Employee'
> --
> Hugo Kornelis, SQL Server MVP|||On Wed, 10 May 2006 18:58:09 +0200, Tibor Karaszi wrote:

>Hugo,
>
>How about using INDEXPROPERTY() instead? ;-)
Hi Tibor,
Of course! Much better. (sound of hand slapping forehead)
That's what I get for giving up on BOL in frustration and simply
checking sp_helptext sp_helpindex...
Hugo Kornelis, SQL Server MVP