Question about ALTER INDEX... REBUILD

[pandatime]

Hi,

I am wanting to rebuild some indexes in my database. I have a script that will generate my ALTER INDEX... REBUILD statements for me in the following format, e.g.)

ALTER INDEX ALL ON myTable REBUILD

My question is, when SQL Server generates the drop and create statements internally when this run, will it specify the indexes be created in the SAME exact locations on disk?

I am assuming it would, but just want to be sure. Can't see to find any documentation on it.

Thanks!

 

[PRPhx]

I think it depends. I found the following statement:

Rebuilding Indexes
Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. FOREIGN KEY constraints do not have to be dropped in advance. When indexes with 128 extents or more are rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. For more information, see Dropping and Rebuilding Large Objects.

Take a look at this site, if you haven't already:

http://msdn.microsoft.com/en-us/library/ms188388.aspx

 

[pandatime]

Thanks. Nevermind.

This statement from BOL clarifies:

ALTER INDEX cannot be used to repartition an index or move it to a different filegroup.