SQL Server - add NONCLUSTERED to CREATE INDEX command

Toad Data Modeler
1

Hi,

Is there a way to tell Toad to include NONCLUSTERED in the CREATE INDEX command for index that are not CLUSTERED.

Thank you

Steve

2

This is what I see when working with a non-clustered table. It appears that the 'noncluster’ing happens after the indexes are created. See the last line of this script.

I did not set anything special in the tool to generate this.

– Create indexes for table dbo.EMP_SMALL

CREATE INDEX [IDX_EMP_SMALL_DEPT] ON [dbo].[EMP_SMALL] ([EMP_DEPT])
ON [PRIMARY]
go

CREATE INDEX [IDX_EMP_SMALL_GRADE] ON [dbo].[EMP_SMALL] ([EMP_GRADE])
ON [PRIMARY]
go

CREATE INDEX [IDX_EMP_SMALL_NAME] ON [dbo].[EMP_SMALL] ([EMP_NAME])
ON [PRIMARY]
go

CREATE INDEX [IDX_EMP_SMALL_SALARY] ON [dbo].[EMP_SMALL] ([EMP_SALARY])
ON [PRIMARY]
go

CREATE UNIQUE INDEX [IDX_EMP_SMALL_ID_NAME] ON [dbo].[EMP_SMALL] ([EMP_ID],[EMP_NAME])
ON [PRIMARY]
go

– Add keys for table dbo.EMP_SMALL

ALTER TABLE [dbo].[EMP_SMALL] ADD CONSTRAINT [PK__EMP_SMAL__16EBFA27D9F5F528] PRIMARY KEY NONCLUSTERED ([EMP_ID])
ON [PRIMARY]

3

Hi Mark,

Thanks for the response. A bit of a misunderstanding, the last line you mention is for the primary key index which is it adding as a constraint. no problem however what I am looking for :

CREATE NONCLUSTERED INDEX [IDX_EMP_SMALL_DEPT] ON [dbo].[EMP_SMALL] ([EMP_DEPT])
ON [PRIMARY]
go

Note the NONCLUSTERED explicitly being included in the CREATE INDEX command, It is just a best practice and again it is being picked up as a Warning in my analysis tool

Cheers,

Steve

4

Hi Steve,

NONCLUSTERED is default, therefore only CLUSTERED is included. But we can change it… I create issue TDM-3943, you can track it in release notes in next versions.

By the way … what version of SQL Server do you use?

Regards,

Mario

5

Hi Mario,

Thanks for that. I am using SQL 2008R2 > 2016 but most recently I am working in 2014

Cheers,

Steve