Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

When I create an index on a table using SQL Server Management Studio, the index has page locks disabled by default. I'd prefer to have page locks enabled by default so neither I nor any other developer has to remember to manually modify. How can I accomplish this?

Thanks,

Pat Brickson

exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||

Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?

The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?

Thanks, Pat

|||

After testing, I find that this only affects indexes that already exist on the table. I'd like to ensure that any new indexes on this table (or any existing or new table for that matter) have page locks enabled. Unfortunately this stored procedure doesn't really help me any more than just manually altering the index's options via the index properties dialog in Management Studio.

Again, I appreciate any assistance. Any other ideas?

Thanks, Pat

|||

The default settings for new indexes are hard coded in the dialog to match the defaults in SQL Server if you don't specify any options. There is no way for end users to change these defaults in the dialog.

Thanks,
Steve

|||

Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.

I'm really not concerned with end users changing the default. I'm more interested in ensuring that an any index created via Management Studio by a developer or DBA will have page locks enabled. Since that doesn't seem to be the default when creating in management studio, is there a way for me to specify which defaults the indexes should take?

Thanks, Pat

|||

If the dialog defaults aren't matching the default behavior in T-SQL, that's not intentional. Please file a defect report for this on http://connect.microsoft.com. Defects reported by customers via the connect site carry extra weight when the development team is prioritizing future work, including service pack work.

Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.

Thanks,
Steve