SQL 2008 Create Index vs Rebuild Index (Alter Index) - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL 2008 Create Index vs Rebuild Index (Alter Index)

SQL 2008 Create Index vs Rebuild Index (Alter Index)# Database - 数据库

l*y2009-07-16 07:07

1 楼

This one is a slash story, mainly humor and parody, and not at all Arthurian.
http://www.glitterati.talkoncorners.net/drp/drp.html

a*t2009-07-16 07:07

2 楼

According to BOL, TechNet etc,
Alter Index Rebuild = Drop index and Create index
But after I drop an index, it takes 2 hours to create index
whereas Alter Index Rebuild take 15 mins.
Why???

j*72009-07-16 07:07

3 楼

Did you create the clustered index first?

a*t2009-07-16 07:07

4 楼

There are no clustered index on the tabled I tried.
I tested on 1 of the 20 or so non-clustered indexes.

【在 j*******7 的大作中提到】

: Did you create the clustered index first?

gy2009-07-16 07:07

5 楼

你的次序是: 先DROP + create, 然后再 Alter??

【在 a*******t 的大作中提到】

: According to BOL, TechNet etc,
: Alter Index Rebuild = Drop index and Create index
: But after I drop an index, it takes 2 hours to create index
: whereas Alter Index Rebuild take 15 mins.
: Why???

a*t2009-07-16 07:07

6 楼

yes.
I am thinking the alter index rebuild doesn't actually drop the index, it
scans and don't touch the pages if it's still good?

【在 gy 的大作中提到】

: 你的次序是: 先DROP + create, 然后再 Alter??

w*e2009-07-16 07:07

7 楼

呵呵, 你难道没看出gy问你次序的原因吗??

【在 a*******t 的大作中提到】

: yes.
: I am thinking the alter index rebuild doesn't actually drop the index, it
: scans and don't touch the pages if it's still good?

a*t2009-07-16 07:07

8 楼

day 1, drop, create, 2 hr
day 2, alter index rebuilt, 15 min
day 3, drop, create, 2 hr
no data change in between
and keywords in my original post: according to BOL and TechNet, alter index
rebuild = drop + create

【在 w*******e 的大作中提到】

: 呵呵, 你难道没看出gy问你次序的原因吗??

j*72009-07-16 07:07

9 楼

也许逻辑上等价，但rebuild实际上并行建立那些index,而drop+create是串行执行？值
得研究。
Also I find the BOL says there are log differences per recovery models and per different index operations.

gy2009-07-16 07:07

10 楼

这个,,,,看来还有其他什么解释吧?
zenny??

index

【在 a*******t 的大作中提到】

: day 1, drop, create, 2 hr
: day 2, alter index rebuilt, 15 min
: day 3, drop, create, 2 hr
: no data change in between
: and keywords in my original post: according to BOL and TechNet, alter index
: rebuild = drop + create

z*y2009-07-16 07:07

11 楼

When you alter clustered index, all non-clustered indexes will repointed to
new clustered index. Also since we only alter clustered index database
engine will not spend time inventory the existing constraints--they will be
left untouched.
However, when you drop the clustered index, all indexes(clustered and non
clustered) are gone. All constraints will be modified/removed.
Actually my first statement is not 100% correct I just realized... it is
true that non clustered indexes will not be dropped