Are there differences between these two scripts? Or would all of the extra tokens/attributes (ie: NONCLUSTERED
, WITH...
, etc...) for the 1st script be defaults in SQL Server 2008 for the 2nd script?
1st 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]
2nd 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