Fragmented heaps matter. The larger the heap, the harder it is to walk the data. Performance suffers.
Creating and dropping the CI will reorder the rows in the table as you are aware. If the vendor offers no solution and it is your responsibility to address the fragmentation, that's exactly what I have done in the past. I wouldn't leave the CI if it were me, I'd drop it. I would avoid adding a column since that does alter the function of the table permanently, and the same argument could be made if you leave the CI in place.
If you do this be sure that you have appropriate storage for creating the CI since it will require storage at least equal to the size of the heap, and also consider when you do it re: your service level requirements.
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.
Best Answer
Create clustered index ix1_table1 on dbo.table1 (col1) with (online = on)
This will take a little longer, the online part, but it will allow users to continues using the table while the index is being created. Every table (with very few exceptions) should have a clustered index.