Sql Server 数据库索引整理语句,自动整理数据库索引

在一个大型数据库中，数据的更改是非常频繁的。

而建立在这些数据上的索引也是需要经常去维护的。

否则这这些数据索引就起不到起应起的作用。甚至会成为数据库本身的负担。

我们就要定期的对数据库的索引进行维护 我在MSDN上发现了这个脚本不过其中有些小问题我已经修正 大家可以使用这个脚本对数据库的索引进行日常维护

复制代码 代码如下:

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname sysname;

DECLARE @objectname sysname;

DECLARE @indexname sysname;

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command varchar(8000);

DECLARE @dbId int;

-- ensure the temporary table does not exist

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')

DROP TABLE work_to_do;

-- conditionally select from the function, converting object and index IDs to names.

set @dbId=DB_ID();

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO work_to_do FROM sys.dm_db_index_physical_stats (@dbId, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0

BEGIN;

SELECT @objectname = o.name, @schemaname = s.name

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = name

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding

IF @frag < 30.0

BEGIN;

SELECT @command = 'ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE';

IF @partitioncount > 1

SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

EXEC (@command);

END;

IF @frag >= 30.0

BEGIN;

SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD';

IF @partitioncount > 1

SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

EXEC (@command);

END;

PRINT 'Executed ' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;

END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- drop the temporary table

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')

DROP TABLE work_to_do;

GO

这个脚本在运行时 会建立一个表 work_to_do 整理完毕后会自动删除这个表。如果大家不喜欢这样的话也可以用 一个 临时表解决 .

【Sql Server 数据库索引整理语句,自动整理数据库索引】相关文章：

★ 如何在SQL Server 2005数据库中导入SQL Server 2008的数据

★ SQL Server 2008 R2数据库镜像部署图文教程

★ SQL Server 2005 还原数据库错误解决方法

★ SQL Server 数据库自动执行管理任务

★ SQL Server 2008 数据库镜像部署实例之一 数据库准备

★ SQL Server数据备份处理过程探讨

★ SQL Server 2005“备份集中的数据库备份与现有的数据库不同”解决方法

★ SQL Server数据库的修复SQL语句

★ SQL Server 数据库备份和还原认识和总结

★ SQL Server2008 数据库误删除数据的恢复方法分享

1/准备测试数据

---------------------------------------------------------------------------------

create table t1(

c1 integer,

c2 integer,

c3 integer

);

insert into t1 values(1,2,3)

insert into t1 values(1,8,4)

insert into t1 values(1,4,4)

insert into t1 values(1,4,5)

insert into t1 values(1,5,5)

insert into t1 values(2,2,3)

insert into t1 values(2,8,4)

insert into t1 values(2,4,4)

insert into t1 values(2,4,5)

insert into t1 values(2,5,5)

2/查看排名

---------------------------------------------------------------------------------

A/单记录排名

select c1,c3,

(select count( c3)+1 from t1 a where a.c3>b.c3

and a.c1=b.c1 and a.c1 =1

) order_num

from t1 b

where c1 =1

order by c1,c3

c1 c3 order_num

----------- ----------- ----------------------

1 3 5

1 4 3

1 4 3

1 5 1

1 5 1

B/多记录排名

select c1,c2,c3,

(select count( c3)+1 from t1 a where a.c3>b.c3

and a.c1=b.c1

) order_num

from t1 b

order by c1,c3

c1 c2 c3 order_num

----------- ----------- ----------- ----------------------

1 2 3 5

1 8 4 3

1 4 4 3

1 4 5 1

1 5 5 1

2 2 3 5

2 8 4 3

2 4 4 3

2 4 5 1

2 5 5 1

【查询数据排名情况SQL】相关文章：

★ SQL Server 数据库管理常用的SQL和T-SQL语句

★ SQL语句查询重复字段并按数量排序

★ sqlserver数据库迁移的几种方式

★ 恢复.mdf 数据库步骤

★ 模糊查询

★ MSSQL2005数据库备份导入MSSQL2000

★ sql2005 数据同步方法

★ SQL Server数据库Suspect解决

★ 查找sqlserver数据库中某一字段在 哪

★ SQL查询日志 查看数据库历史查询记录的方法

（编辑：南平站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!