• This does indeed happen on both SQL Server 2008 R2 (RTM) and SQL Server 2008 SP2:

    use tempdb;

    go

    create table t1 (

    id int identity primary key,

    val varchar(10)

    );

    create table t2 (

    id int identity primary key,

    val varchar(10)

    );

    go

    create unique nonclustered index ix_t1 on t1 (id);

    go

    alter table t2

    add constraint fk_t2_t1 foreign key (id) references t1 (id);

    go

    select

    f.name as ConstraintName,

    i.name as IndexName,

    object_name(i.object_id) as TableName,

    i.is_unique,

    i.is_primary_key,

    i.type_desc,

    f.key_index_id

    from

    sys.foreign_keys f

    inner join

    sys.indexes i on

    i.object_id = f.referenced_object_id and

    i.index_id = f.key_index_id

    go

    drop table t2;

    drop table t1;

    Actually, Kimberly Tripp has even a Post on it. As far as I can see, this is a very nice feature optimizing the performance of foreign key constraint validation, and not even close to a bug.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]