SQL Server: Create Nonclustered Index without giving a name to it

Maddy picture Maddy · Oct 24, 2014 · Viewed 9.7k times · Source

I use SQL Server 2008.

I am trying to create a nonclustered index on my table. I want to check if there exists a way to create this without giving a name to the index.

For e.g.

CREATE TABLE #mytable (Date_ datetime NOT NULL, ID_ varchar(10) NOT NULL, Value_)

When I add a PK to this table, I do not specify the name of that key. For e.g.

ALTER TABLE #mytable ADD PRIMARY KEY CLUSTERED (Date_ ASC, ID_ ASC)

Is it possible to do something similar to create a nonclustered index without specifying a name?

For e.g.

ALTER TABLE #mytable ADD NONCLUSTERED INDEX (Date_, Value_)   -- FAILS!!!

The only command I know is

CREATE NONCLUSTERED INDEX *keyname* ON #mytable (Date_, Value_)  

Answer

Stanley Constant picture Stanley Constant · Apr 5, 2019

After create temp table execute dynamic sequel with guid as index name

DECLARE @NewId VARCHAR(64) = REPLACE(NEWID(),'-','');

EXEC('CREATE INDEX IX_'+@NewId+' ON #Table (ColA,ColB) INCLUDE (ColZ)');