SQL Server 2005 Tidbit 031

  • Article

SQL Server 2005 Tidbit 031

Happy SQL Express User

We got 31 flavors of tidbits for ya…

“I’d like some more information on the requirements for online defragmentation and indexing.”

Well, first of all… The new online index operations are only available in the Enterprise edition of SQL Server 2005. 

The SQL Server 2005 Books Online has a great section on the requirments for performing Online Index Operations. 

From the manual…(And if you have the books online already installed, clicking on this link will bring you right to the correct topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d82942e0-4a86-4b34-a65f-9f143ebe85ce.htm)

—

When you perform online index operations, the following guidelines apply:

  • Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
  • Nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.
  • Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.

The following table shows the index operations that can be performed online and the indexes that are excluded from these online operations. Additional restrictions are also included….

—

See the books online for the table, plus some additional considerations of Disk Space, Performance, and Transaction Log loads.

—

Got an IT question?  Give me a comment, or contact me.