Indexing in MSSQL 2000 vs MSSQL 2005
Microsoft has changed a lot of the indexing syntax between MSSQL 2000 and MSSQL 2005. In MSSQL 2000 and before, MS was using the Sybase syntax. Now Microsoft is trying to become more SQL ANSI compliant, and has announced that it will deprecate the DBCC commands.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
| MSSQL 2000 | MSSQL 2005 | |
|---|---|---|
| Create a new index [identical] | create index xzy on table(col1); | create index xyz on table(col1); |
| Drop an existing index [identical] | drop index xzy; | drop index xyz; |
| Drop an existing index to create a new one in a single command | create index xzy on tablename(col1) with drop_existing; | create index xyz on tablename(col1) with (drop_existing = on) |
| Reindex and change fill factor | dbcc reindex (xyz,'',70) with no_infomsgs; | alter index all on tablename rebuild with (fillfactor = 70, sort_in_tempdb = on, statistics_norecompute = on); |
| Defrag an index | dbcc indexdefrag (databasename, tablename, indexname); | alter index indexname on tablename reorganize'''; |
- Try to use the new ANSI compliant syntax. Eventually, Microsoft will remove the old DBCC commands.