the rarer options on CREATE INDEX commands;

Question

the rarer options on CREATE INDEX commands;

Lowell

SSC Guru

Points: 323518
More actions
December 23, 2011 at 8:03 am

#391192

I'm playing with scripting some of the little known options when you do CREATE INDEX.
before you send me to lmgtfy.com, i did read Books Online CREATE INDEX already, and looked indetail at the INDEXPROPERTY function (there is no INDEXPROPERTYEX) and the columns available in sys.indexes.
Here's a modified pseudocode from BOL's optional definitions:
the items that i cannot find are highlighted in red;
if i was scripting an existing index, can any of those items be defined/discovered, or are they always decided on when you are creating the index? Where can you pry this information for SQLif it was executed on the index previously?
SORT_IN_TEMPDB,
STATISTICS_NORECOMPUTE,
DROP_EXISTING,
ONLINE,
MAXDOP,
DATA_COMPRESSION
i'd think the compression data would have to be scripted someplace, but I don't know where it is.
has anyone bothered digging for this data before? where is it hidden? the non-red columns are easily discovered in sys.indexes, they are not the problem.
CREATE UNIQUE CLUSTERED INDEX [PK__PPIBPRA__7D5A4004]
ON [dbo].[PPIBPRA] (IBPRATBLKEY ASC)
WITH PAD_INDEX = { ON | OFF },
FILLFACTOR = fillfactor,
SORT_IN_TEMPDB = { ON | OFF },
IGNORE_DUP_KEY = { ON | OFF },
STATISTICS_NORECOMPUTE = { ON | OFF },
DROP_EXISTING = { ON | OFF },
ONLINE = { ON | OFF },
ALLOW_ROW_LOCKS = { ON | OFF },
ALLOW_PAGE_LOCKS = { ON | OFF },
MAXDOP = max_degree_of_parallelism,
DATA_COMPRESSION = { NONE | ROW | PAGE},
--[ ON PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) ]
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Sort in tempdb, online, maxdop and drop_existing are just options for how that index rebuild will be done, they don't affect any future rebuilds or have any effect on the index once created. They're not persisted anywhere.

Compression is in sys.partitions, join to sys.indexes on object_id and index_id.

Statistics norecompute is in sys.stats. Join on object_id and index_id = stats_id

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

perfect Gail, that was exactly the push i needed.

Thank you so much!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

okbangas SSChampion Points: 11773 More actions · Answer 3

Oh, I just love that MAXDOP. I have a customer, with a hosting partner who insists on disabling parallelism on the servers, only to avoid CXPACKET wait. That queries takes longer, resuling in more blocking is obviously not of any interrest. With MAXDOP I can at lest override this, and use all the cores if I want.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]