Rebuild indexes online with SQL Server 2005
Rebuild indexes online with SQL Server 2005
http://blogs.techrepublic.com.com/datacenter/?p=249
Online index rebuild
SQL Server 2005 introduces the ability to rebuild your indexes in an
online fashion so that other processes are able to access the table
while the rebuild is occurring. Because you can access the indexes
during the rebuild, you are not limited to only rebuilding indexes
during off-peak hours.
To accomplish this, the database engine takes some special actions
to rebuild the index and to allow access to the index at the same time.
The original index will remain available to users for reading data and
data modification. Row versioning is used to allow for transactional
consistency. During the rebuild, a new index is created that mimics the
old index. Any data modifications that alter the original index will
also be applied to this index by SQL Server during the rebuild. This
new index is not read from at all — it is write-only. It is essential
that you have enough available disk space to accommodate the data for
the two concurrent indexes during the online rebuild. While the rebuild
is taking place, SQL Server uses a mapping index to determine records
to modify in the new index when modifications occur in the original
index. Once the rebuild process has finished, any queries or data
modifications occur to the new index, and the original index is dropped.
Example
The process to rebuild an index online is not much different than
the typical rebuild process; however, there are a few ways to
accomplish the rebuild. One way is to simply drop the index using a
DROP INDEX statement followed by a CREATE INDEX statement. Rebuilding
indexes in this fashion leaves the table without an index until the
index is completely created. For this reason (and a number of other
reasons), dropping the index and recreating it is not recommended.
The CREATE INDEX statement can still be used to rebuild an index if
the DROP_EXISTING option is u
Ïà¹ØÎĵµ£º
/*
±êÌ⣺һ¸öÏîÄ¿Éæ¼°µ½µÄ50¸öSQLÓï¾ä(ÕûÀí°æ)
×÷Õߣº°®Ð¾õÂÞ.ع»ª
ʱ¼ä£º2010-05-10
˵Ã÷£ºÒÔÏÂÎåÊ®¸öÓï¾ä¶¼°´ÕÕ²âÊÔÊý¾Ý½øÐйý²âÊÔ£¬×îºÃÿ´ÎÖ»µ¥¶ÀÔËÐÐÒ»¸öÓï¾ä¡£
ÎÊÌâ¼°ÃèÊö£º
--1.ѧÉú±í
Student(S#,Sname,Sage,Ssex) ......
ÔÚsqlÓï¾äÖÐÌí¼Ó±äÁ¿¡£
declare @local_variable data_type
ÉùÃ÷ʱÐèÒªÖ¸¶¨±äÁ¿µÄÀàÐÍ£¬
¿ÉÒÔʹÓÃsetºÍselect¶Ô±äÁ¿½øÐи³Öµ£¬
ÔÚsqlÓï¾äÖоͿÉÒÔʹÓÃ@local_variableÀ´µ÷ÓñäÁ¿
ÉùÃ÷ÖпÉÒÔÌṩֵ£¬·ñÔòÉùÃ÷Ö®ºóËùÓбäÁ¿½«³õʼ»¯ÎªNULL¡£
ÀýÈ磺declare @id int
&nb ......
ÔÚSQLÓïÑÔÖУ¬Ö»ÌṩÁËÒ»¸ö¶¯´ÊSELECTÓÃÀ´½øÐÐÊý¾Ý²éѯ²Ù×÷£¬µ«Õâ¸ö¶¯´ÊµÄ²ÎÊýÊ®·Ö¸´ÔÓ£¬ÇÒÄÜǶÌ×ʹÓã¬ÆäͨÓøñʽÈçÏ£º
SELECT [All|Distinct]<Ä¿±êÁбí´ïʽ>[£¬<Ä¿±êÁбí´ïʽ>]...
from <±íÃû»òÊÓͼÃû>[£¬<±íÃû»òÊÓͼÃû>]...
[WHERE<Ìõ¼þ±í´ïʽ>]
[GROUP BY<ÁÐÃû1>[HAVING<Ì ......
MS SQL Server²éѯÓÅ»¯·½·¨
×÷Õߣºxmllover 2007-11-29
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
4¡¢ÄÚ´æ ......