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_)
No, it is not possible to create a non-clustered index without a name, the syntax is quite clear:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
index_name Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.
The database object name is referred to as its identifier. Everything in Microsoft SQL Server can have an identifier. Servers, databases, and database objects, such as tables, views, columns, indexes, triggers, procedures, constraints, and rules, can have identifiers. Identifiers are required for most objects, but are optional for some objects such as constraints.