"An item with the same key has already been added."

In SQL Server Reporting Services, when adding a new dataset to a report, you may see an error that looks like this:

"An item with the same key has already been added."

Based on some quick googling, it looks like there are several potential causes for the error, but here's why I received it today.

The stored procedure I had declared as the query source had the same column name declared twice.  Even if you use different data from different tables, even if you declare them differently, SSRS needs all column names in the final dataset to be unique.

select

tablea.samecolumnname

,tableb.samecolumnname

,samecolumnname = tablec.columnname

,tabled.columnname as samecolumnname

...

That error message isn't helpful, but that was the solution for me, for what was a clumsy mistake to begin with.

Using Foreign Keys to Determine Table Insertion Order

Here's a script to determine, based on your database's foreign key relationships, what the insertion order would be for, say, a testing dataset.

      with cteFK (pktable, fktable) as ( 
       select             
            pktable = s1.name + '.' + o1.name 
       ,    fktable = isnull(s2.name + '.' + o2.name, '')        
       from sys.objects o1       
       left outer join sys.sysforeignkeys fk on o1.object_id = fk.fkeyid        
       left outer join sys.objects o2 on o2.object_id = fk.rkeyid        
    left outer join sys.schemas s1 on o1.schema_id = s1.schema_id
    left outer join sys.schemas s2 on o2.schema_id = s2.schema_id
       where o1.type_desc = 'user_table'       
       and o1.name not in ('dtproperties','sysdiagrams')        
       group by s1.name + '.' + o1.name 
      , isnull(s2.name + '.' + o2.name, '')       
), cteRec (tablename, fkcount) as  ( 
       select tablename = pktable 
       ,    fkcount = 0
       from cteFK    
       
       UNION ALL       
       
    select tablename = pktable 
       , fkcount = 1
       from cteFK  
       cross apply cteRec        
       where cteFK.fktable = cteRec.tablename    
       and cteFK.pktable <> cteRec.tablename
) 
select
 TableName
, InsertOrder = dense_rank() OVER ( ORDER BY max(fkcount) asc )
from (       
       select
              tablename = fktable
       ,      fkcount = 0 
       from cteFK 
       group by fktable    
        
       UNION ALL    
 
       select tablename = tablename, fkcount = sum(ISNULL(fkcount,0))   
       from cteRec      
       group by tablename
     ) x 
where x.tablename <> ''
group by tablename 
order by InsertOrder asc, TableName asc

Use the sample script from the previous post on how to "Script Out Foreign Keys With Multiple Keys" for an example of building a complicated set of foreign key relationships to test this script out.

Here's the results from that example:

Similarly, this script would generate an order for you to unravel the data - the numbers descending would allow you to delete in the proper order,

delete from fktable11
delete from fktable10
delete from fktable9
delete from fktable8
delete from fktable6
delete from fktable4
delete from fktable2
delete from fktable7
delete from fktable5
delete from fktable3
delete from fktable1

... or drop the tables in the proper order.

drop table fktable11
drop table fktable10
drop table fktable9
drop table fktable8
drop table fktable6
drop table fktable4
drop table fktable2
drop table fktable7
drop table fktable5
drop table fktable3
drop table fktable1

UPDATED 20140507: changed old system reference objects (sysobjects) to new system reference objects (sys.objects) UPDATED 20140624: added "and cteFK.pktable <> cteRec.tablename", see comments for explanation.

Script Out Foreign Keys With Multiple Keys

It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys.  But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?

For example,

--Script 1
create table dbo.fktable1(
  id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable1 primary key (id1, id2, id3))

create table dbo.fktable2(
  id int not null identity(1,1) primary key
, id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable1_fktable2] 
  FOREIGN KEY (id1, id2, id3) 
  REFERENCES dbo.fktable1 (id1, id2, id3))

Combining those multiple records in the sys.foreign_key_columns into a concatenated string in order to get this is tricky:

--Script 2
ALTER TABLE [dbo].[fktable2]  WITH CHECK 
  ADD  CONSTRAINT [FK_fktable1_fktable2] 
  FOREIGN KEY([id1], [id2], [id3])
  REFERENCES [dbo].[fktable1] ([id1], [id2], [id3])

Here's how I recently did this.   It actually turned out to be more complicated than I thought, certainly more complicated that your standard throw-strings-together-based-on-system-tables.  This is because we need to build a recurse of the multi-key values that are both referenced and referencing in foreign keys. 

Adding the Clustered Key To Your Nonclustered Index? Part 2

Of course, a rather obvious answer presents itself the next day.  After reviewing the actual environment that my colleague was working in, it popped quickly into mind.

What if the table has a compound primary key?  See comments for a play-by-play.

drop table dbo.testclusteredinclude

go

create table dbo.testclusteredinclude

(             id1 int not null

,             id2 int not null

,             id3 int not null

,             text1 varchar(30) Not null

,             constraint pk_testclusteredinclude primary key  (id1, id2, id3)

)

go

insert into dbo.testclusteredinclude (id1, id2, id3, text1) values (1,2,3,'test1'); --put in our seed row

--filler data of 10000 rows

with cte3pk (id1, id2, id3) as

(select id1=2,id2=3,id3=4

union all

select id1+1, id2+1, id3+1

from cte3pk

where id1 <= 10000

)

insert into dbo.testclusteredinclude (id1, id2, id3, text1)

select id1, id2, id3, 'test2' from cte3pk

OPTION (MAXRECURSION 10000);

go

alter index all on dbo.testclusteredinclude  rebuild

go

--turn on show actual exec plan

--Second key of the Clustered Key can benefit, this easy to understand.

-- We're simply giving SQL a narrower set of data, via the nonclustered index.

select id2, text1 from dbo.testclusteredinclude where

id2 = 1001

go

create nonclustered index idx_nc_testclusteredinclude_id2_text1

on dbo.testclusteredinclude (id2, text1)

go

select id2, text1 from dbo.testclusteredinclude where

id2 = 1001

go

drop index idx_nc_testclusteredinclude_id2_text1 on dbo.testclusteredinclude

go

--Still, putting a subsequent key of a compound clustered key in the include column doesn't help.

-- SQL can still do an index seek on id2, even when the index doesn't contain it (idx_nc_testclusteredinclude_text1).

select id2, text1 from dbo.testclusteredinclude where

text1 = 'test2'

go

create nonclustered index idx_nc_testclusteredinclude_text1

on dbo.testclusteredinclude (text1)

go

select id2, text1 from dbo.testclusteredinclude where

text1 = 'test2'

go

create nonclustered index idx_nc_testclusteredinclude_text1_inc_id2

on dbo.testclusteredinclude (text1) include (id2)

go

select id2, text1 from dbo.testclusteredinclude where

text1 = 'test2'

go

drop index idx_nc_testclusteredinclude_text1 on dbo.testclusteredinclude

drop index idx_nc_testclusteredinclude_text1_inc_id2 on dbo.testclusteredinclude

go

One final note - none of the SELECT statements above generate any missing index suggestions in SQL 2012 SP1 or SQL 2008 SP2, even though without any nonclustered indexes they all generated Clustered Index Scans.

Adding the Clustered Key To Your Nonclustered Index?

A colleague of mine was understandably confused when using the almost-perfect missing index engine in SQL 2008. The engine recommended he include the clustered index key in a new nonclustered index. Read that again - you're right, it doesn't make a lot of sense.

From my colleague:

Why would you ever need to have your primary key or clustered index key as a key field on a non-clustered index? That don't make any sense to me. If the SQL engine needs to make a lookup on the clustered index have the primary key as an include on your non-clustered index. ...I researched it and the only difference is that my current nonclustered has the primary key as a key field and now it's suggesting making it a include column.

I've got a wild hunch that the clustered key might make sense to have as a key in the nonclustered index as long as it isn't the first key, and the query is ordering on a nonclustered key. However, I can't think of a representative situation to prove that, so I might be wrong. I'm open to suggestions for a reproducible lab sample, perhaps your starting point would be the sample script below.

That said, I don't think it would make any sense to put the clustered key in the INCLUDE of a nonclustered index.  I'm puzzled as to why the missing index feature would suggest this.

Here's a lab for proof. In this scenario, the index was 19.320312 MB without the include, and 19.320312 with the include. SQL doesn't store an extra copy of the clustered key in the nonclustered index just because you asked for it in the INCLUDE or in the key. That's because the clustered key is already a part of any nonclustered index key.

This reinforces the point that the clustered key should always been as unique (so it's helpful) and narrow (so it doesn't waste space) as possible. That's why for almost all tables, an integer IDENTITY(1,1) key is a perfect clustered index and primary key. 4 bytes per row. And that's why for all tables, a GUID is a less efficient choice for your clustered key. 16 burdensome bytes per row...which is then lugged around by all your nonclustered keys...  </GUIDrant>

use somedatabasenotinproduction 

go

create table dbo.testclusteredinclude 

(      id int identity(1,1) not null primary key 

,      text1 varchar(30) Not null) 

go

insert into dbo.testclusteredinclude (text1) values ('test1'), ('test2') 

go 

insert into dbo.testclusteredinclude (text1) 

select text1 from dbo.testclusteredinclude where text1 = 'test2' 

go 20 

--turn on show actual exec plan 

select id, text1 from dbo.testclusteredinclude where text1 = 'test1' 

go 

create nonclustered index idx_nc_testclusteredinclude_text1  

on dbo.testclusteredinclude (text1) 

go 

select id, text1 from dbo.testclusteredinclude where text1 = 'test1' 

go 

drop index idx_nc_testclusteredinclude_text1 

on dbo.testclusteredinclude 

go 

create nonclustered index idx_nc_testclusteredinclude_text1 

on dbo.testclusteredinclude (text1)  include (id) 

go 

select id, text1 from dbo.testclusteredinclude where text1 = 'test1'
 

Adding the Clustered Key To Your Nonclustered Index?  Part 2