I was generating scripts in the Management Studio for all objects. Everything looked great; I created my new database with same structure.
Before I let the developer know database was good I decided to run it through ApexSQL Diff to see if there were any issues and sure enough it found issues with few tables.
Looking at it ApexSQL Diff found that three indexes did not get generated properly; in generating the indexes following options were ignored and never scripted out:
- MAXDOP
- ALLOW_ROW_LOCKS
- ALLOW_PAGE_LOCKS
- STATISTICS_NORECOMPUTE
Done on SQL Server 2005 SP1 (9.00.2198)
I have tried to do this via the Script Wizard and each Object with same result; I confirmed the database compatibility mode was 2005.
So I though okay maybe because I am running SQL Server 2005 SSMS SP1 thats where the issue is taking place.
So I decided to try it on SQL Server 2005 SSMS SP2 CU9, it came back with more information this time only option that was ignored was:
- MAXDOP
Done on SQL Server 2005 SP2 CU9 (9.00.3182) and SQL Server 2005 SP2 CU9+Hotfixes (8.00.3310).
I have not used this setting on any index thus far; but if it being used be cautious when transfering objects from one database to another. Run it through tools like ApexSQL Diff or Red Gate Compare or the Microsoft Visual Studio Team System 2008 (Database Edition).
SQL Canada (SQLCAN) 