Problem creating index with included columns (SQL Server Express)

Question

I am using the Database explorer/designer of Visual C# 2008 Express Edition with SQL Server Express 2008.

I created a table and want to add an index. But the index would exceed 900 bytes. One workaround is to use included columns in an index. Unfortunately, the "Included Columns" property is disabled in the Index designer and I haven't found a way to enable it.

Can someone tell me how to enable the property? Is there another way to add included columns to an index using the designer? Is this feature at all supported by SQL Server Express?

Answer 1

You need to create the index by expanding the table in the explorer, right-clicking the Indexes folder and selecting "Add index...". Make sure the table designer is closed when you do this

Answer 2

It's certainly supported against express. After a while, you'll find limitations in most of the GUI tools, it's worth learning the actual syntax, and entering commands directly. The syntax for CREATE INDEX is easily found on MSDN, and they'll normally describe any edition limitations in the remarks section.

So, for instance, I just ran this against my Express instance, without issues:

create table T1 (
    ID int not null,
    Val1 varchar(10) not null
)
go
create unique nonclustered index IX_T1 on T1 (ID) INCLUDE (Val1)
go

Edit also, as others have mentioned, this is an unusual design. Also, one limitation (and possibly why it was disabled in the GUI), is that you can only INCLUDE columns in nonclustered indexes. A little thought about what they are should reveal why.