CODEWITHSUNDEEP
sql-serversql-server-2008indexingssms
IamIC
IamIC

Reputation: 18269

Include not available in covering indexes in SQL Server 2008 Express

In MS SQL Server Manager Studio for 2008 Express, the "Included Columns" field is always grayed out in the "Indexes/Keys" window in the Database Diagram designer.

Per the help, this should be available so long as I'm not creating a clustered index.

Further, if I run a query to create the index (which runs fine), the created query doesn't list for the table it was added against.

I don't see anywhere where MS says this feature is unavailable in the Express version.

Any ideas?

Further data:

This is the script that creates the table:

CREATE UNIQUE INDEX IX_SocialTypes_Cover ON ClientSocialTypes(ClientID, SocialTypeID, [Source]) INCLUDE (URLID)

Here is the table gen script (the index is missing):

CREATE TABLE [dbo].[ClientSocialTypes](
    [SocialTypeID] [int] IDENTITY(1,1) NOT NULL,
    [ClientID] [int] NOT NULL,
    [SocialTypeClassID] [tinyint] NOT NULL,
    [Source] [nvarchar](50) NOT NULL,
    [TagCount] [int] NOT NULL,
    [URLID] [int] NULL,
 CONSTRAINT [PK_ClientSources] PRIMARY KEY CLUSTERED 
(
    [SocialTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]    
GO

ALTER TABLE [dbo].[ClientSocialTypes]  WITH CHECK ADD  CONSTRAINT [FK_ClientSocialTypes_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([ClientID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ClientSocialTypes] CHECK CONSTRAINT [FK_ClientSocialTypes_Clients]
GO

ALTER TABLE [dbo].[ClientSocialTypes]  WITH CHECK ADD  CONSTRAINT [FK_ClientSocialTypes_SocialTypeClasses] FOREIGN KEY([SocialTypeClassID])
REFERENCES [dbo].[SocialTypeClasses] ([SocialTypeClassID])
GO

ALTER TABLE [dbo].[ClientSocialTypes] CHECK CONSTRAINT [FK_ClientSocialTypes_SocialTypeClasses]
GO

ALTER TABLE [dbo].[ClientSocialTypes] ADD  CONSTRAINT [DF_ClientSocialTypes_SocialTypeClassID]  DEFAULT ((1)) FOR [SocialTypeClassID]
GO

ALTER TABLE [dbo].[ClientSocialTypes] ADD  CONSTRAINT [DF_ClientSocialTypes_TagCount]  DEFAULT ((0)) FOR [TagCount]
GO

ALTER TABLE [dbo].[ClientSocialTypes] ADD  CONSTRAINT [DF_ClientSocialTypes_HasTrackedURL]  DEFAULT ((0)) FOR [URLID]
GO

Upvotes: 7

Views: 2136

Answers (3)

IamIC
IamIC

Reputation: 18269

It turns out this is grayed out in the full version of SQL Server too. In SSMS, use the Object Explorer (not the Designer) to navigate to {database_name} > Tables > {table_name} > Indexes to manage indexes that have includes.

Upvotes: 7

Simon_Weaver
Simon_Weaver

Reputation: 146188

There's TWO different index dialogs. An ancient horrible awful one, and a new (only just discovered it) one that actually lets you change these things.

OLD HORRIBLE ONE

  • Right click on a table in your main tables list
  • Click 'Design'
  • Right click on the list of columns and select 'Indexes/Keys'

This doesn't let you change included columns.

NEW NICE ONE

  • Expand the table in your main tables list to show the 'Columns', 'Keys', 'Constraints', 'Triggers' etc folders
  • Expand the Indexes folder
  • Right click Indexes folder for New Index
  • Right click existing index and click Properties to edit an existing index

This newer dialog allows you to do a lot more and I'm kind of disappointed in Microsoft for keeping the old one alive and for how long it's taken me to discover it.

Upvotes: 11

gbn
gbn

Reputation: 432641

The index may actually be a unique constraint (using CREATE/ALTER TABLE) rather than an index created using CREATE INDEX. Unique constraints don't allow INCLUDEs.

It's quite confusing... generate a script for the index/key entry or table and you'll be able to confirm.

Edit:

  • When you create the index separately you have to refresh Object Explorer

  • Do you have 2 SocialType tables in different schemas? (eg dbo.SocialType and [domain\myuser].SocialType). This can happen if you don't specify the schema in DDL statements.

Upvotes: 1

Related Questions