Differences Between Two Different Create Index Commands

Question

Are there differences between these two scripts? Or would all of the extra tokens/attributes (ie: NONCLUSTERED, WITH..., etc...) for the 1^st script be defaults in SQL Server 2008 for the 2^nd script?

1^st script:

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]
(
    [VISIT_NUM] ASC
) WITH 
  (PAD_INDEX  = OFF, 
   STATISTICS_NORECOMPUTE  = OFF, 
   IGNORE_DUP_KEY  = OFF, 
   ALLOW_ROW_LOCKS = ON, 
   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

2^nd script:

CREATE UNIQUE INDEX [DEID_MAP_IDX1] ON [DEID_MAP] 
(
    [VISIT_NUM] ASC
);

FYI: there is ETL code that drops the index with this script before doing a bulk data load, and then finally applying re-creating the index with the 2nd script above.

DROP INDEX [deid_map_idx1] ON [deid_map] WITH ( ONLINE = OFF );

---

EDIT:

After applying the simple index above (2nd script), I got this:

SQL Server Management Studio > expanded table > expanded folder "Indexes" > right click index > selected "Script Index as.." > selected "CREATE TO" > selected "New Query Editor Window" > got the following.

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP] 
(
    [VISIT_NUM] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

So it appears that these are added in addition by running the simple statement:

SORT_IN_TEMPDB = OFF

DROP_EXISTING = OFF

ONLINE = OFF

Answer 1

It boils down to looking what the default values are. Lets break this down:

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]

nonclustered is specified here. The default (i.e. nothing specified) is nonclustered. So unless clustered is specified it will default to nonclustered. So that's the same in both scripts.

[dbo] is specified here explicitly. As for the second un-specified CREATE INDEX then it all depends on what the current user's default schema is. Only you can answer that at the moment, so that may or may not default to dbo.

WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY  = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

PAD_INDEX: the default is OFF, so unspecified will be the same in the second script as it is in the first.

STATISTICS_NORECOMPUTE: the default is OFF, so the second script unspecified has the same value.

IGNORE_DUP_KEY: the default is OFF, so the second CREATE INDEX is identical with this parameter.

ALLOW_ROW_LOCKS: the default is ON, so the second CREATE script has the same behavior.

ALLOW_PAGE_LOCKS: the default is ON...the second script has identical behavior.

... ON [PRIMARY]: just like the default schema one, this all depends on what your default filegroup is. If PRIMARY is the default filegroup, your second CREATE INDEX script will also create the index on PRIMARY. If PRIMARY is not the default filegroup, then it will be a different filegroup, as an unspecified filegroup will go to the default filegroup.

All of this information and default values can be found on this BOL reference here.

Answer 2

There is one big difference.

[dbo] is not necessarily the default schema for every user. The second script could be affecting an entirely different table. It is definitely a good practice to always specify a schema.

The other options, specifically in the WITH are all SQL Server defaults, besides the PRIMARY file group which is defaulted at the database level. There may be a way to change these default values, though.

As a rule, it's always a good idea to specify these options in production scripts since it makes it clear what the setting is. Not everyone knows the default for every setting and making it explicit is very clear.