Create a script in MMSE Express - question about certain part of script.

Question

When I create a script with the Management Studio, it includes this part:

WITH 
(
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON, 
    FILLFACTOR = 70
)

What are those options and are they required?

Thanks :)

Answer 1

None of them are required. You can read about all the options at Books Online: http://msdn.microsoft.com/en-us/library/ms188783.aspx

Answer 2

All these options are settings that the Server or the Database has defaults set for, so you really don't need to specify them.

The only time I do is if I am specifically trying to fine tune something.

For example, if I know that I am going to have a lot of values interspersed/edited/deleted for a clustered index (for which table pages might get rearranged or broken) then I specify a low fill factor. If it is in a warehouse where I'm not bringing in old data then I don't need a lot of space left over in my index - I may take that as close to 100% as I feel comfortable.