select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t left join sys.indexes i on i.object_id=t.object_id left join sys.dm_db_index_physical_stats(db_id(),object_id('event'),null,null,'limited') s on s.object_id=i.object_id and s.index_id=i.index_id order by s.avg_fragmentation_in_percent desc 这样执行我这里就没报错 SELECT @@VERSION /* Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) */
兼容级别对 ORDER BY 的影响(第 2 种情况)--官方注解 在兼容级别 90(默认级别)中,以下 SELECT... ORDER BY 语句将因 ORDER BY 子句中存在附加的表前缀而出现错误。 SELECT c1 AS x FROM SampleTable ORDER BY SampleTable.x; GO 在将该数据库的兼容级别重置为 80 之后,这一 SELECT... ORDER BY 语句将成功执行。 sp_dbcmptlevel tempdb, 80 SELECT c1 AS x FROM SampleTable ORDER BY SampleTable.x; GO 以下 SELECT... ORDER BY 语句在这两个兼容级别中均可正常执行。 sp_dbcmptlevel tempdb, 80 SELECT c1 AS x FROM SampleTable ORDER BY x; GO sp_dbcmptlevel tempdb, 90 SELECT c1 AS x FROM SampleTable ORDER BY x; GO
另外想请教下各位: alter index objective on ix_objective_customer_guid rebuild 提示报错了: 消息 1088,级别 16,状态 9,第 2 行 找不到对象 "ix_objective_customer_guid",因为它不存在或者您没有所需的权限。这是什么原因?
select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t left join sys.indexes i on i.object_id=t.object_id left join sys.dm_db_index_physical_stats(db_id(),object_id('objective'),null,null,'limited') s on s.object_id=i.object_id and s.index_id=i.index_id 我用这个SQL语句查询avg_fragmentation_in_percent 的值大于30我就需要重建索引,当小于30的重新组织索引,可是我运行以下程序之后查询avg_fragmentation_in_percent 的值还是都大于30啊。那这样岂不是没有达到重建索引,提供查询方面的效率吗? declare @table varchar(50),@index varchar(500),@avg float,@str nvarchar(300) declare R_Index cursor for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t left join sys.indexes i on i.object_id=t.object_id left join sys.dm_db_index_physical_stats(db_id(),object_id('objective'),null,null,'limited') s on s.object_id=i.object_id and s.index_id=i.index_id where s.avg_fragmentation_in_percent>29 --and i.name<>'ix_objective_customer_guid' open R_Index fetch next from R_Index into @table,@index,@avg while(@@fetch_status=0) begin if(@avg>=30) begin set @str='alter index '+rtrim(@index)+' on '+rtrim(@table)+' rebuild' end if(@avg<30) begin set @STR='alter index '+rtrim(@index)+' on '+rtrim(@table)+' reorganize' end exec sp_executesql @str fetch next from R_Index into @table,@index,@avg end close R_Index deallocate R_Index
GO
sys.tables t left join sys.indexes i on i.object_id=t.object_id
left join sys.dm_db_index_physical_stats(db_id(),object_id('event'),null,null,'limited') s
on s.object_id=i.object_id
and s.index_id=i.index_id
order by s.avg_fragmentation_in_percent desc
这样执行我这里就没报错
SELECT @@VERSION
/*
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
*/
/*tbTodayRecvLog PK_tbTodayRecvLog_CreateTime 0.141843971631206
tbTodayRecvLog PK_TBTODAYRECVLOG 0
tbTodayRecvLog PK_tbTodayRecvLog_RowID 0
....*/
sp_dbcmptlevel 存储过程只影响指定数据库的行为,而不影响整个服务器的行为。早期兼容级别和级别 90 之间的行为差异
这一小部分介绍新引进的使用兼容级别 90 时的行为。有关影响兼容级别 80 和更低兼容级别的其他行为差异,请参阅本部分后面的“保留关键字”片段。 使用兼容级别 90 时,将在行为中发生下列更改。
官方注解 有空看看吧。
在兼容级别 90(默认级别)中,以下 SELECT... ORDER BY 语句将因 ORDER BY 子句中存在附加的表前缀而出现错误。
SELECT c1 AS x
FROM SampleTable
ORDER BY SampleTable.x;
GO
在将该数据库的兼容级别重置为 80 之后,这一 SELECT... ORDER BY 语句将成功执行。
sp_dbcmptlevel tempdb, 80
SELECT c1 AS x
FROM SampleTable
ORDER BY SampleTable.x;
GO
以下 SELECT... ORDER BY 语句在这两个兼容级别中均可正常执行。
sp_dbcmptlevel tempdb, 80
SELECT c1 AS x
FROM SampleTable
ORDER BY x;
GO
sp_dbcmptlevel tempdb, 90
SELECT c1 AS x
FROM SampleTable
ORDER BY x;
GO
alter index objective on ix_objective_customer_guid rebuild
提示报错了:
消息 1088,级别 16,状态 9,第 2 行
找不到对象 "ix_objective_customer_guid",因为它不存在或者您没有所需的权限。这是什么原因?
有的话,用SA用户登录进去再执行这个SQL
sys.tables t left join sys.indexes i on i.object_id=t.object_id
left join sys.dm_db_index_physical_stats(db_id(),object_id('objective'),null,null,'limited') s
on s.object_id=i.object_id
and s.index_id=i.index_id
我用这个SQL语句查询avg_fragmentation_in_percent 的值大于30我就需要重建索引,当小于30的重新组织索引,可是我运行以下程序之后查询avg_fragmentation_in_percent 的值还是都大于30啊。那这样岂不是没有达到重建索引,提供查询方面的效率吗?
declare @table varchar(50),@index varchar(500),@avg float,@str nvarchar(300)
declare R_Index cursor
for select t.name,i.name,s.avg_fragmentation_in_percent from
sys.tables t left join sys.indexes i on i.object_id=t.object_id
left join sys.dm_db_index_physical_stats(db_id(),object_id('objective'),null,null,'limited') s
on s.object_id=i.object_id
and s.index_id=i.index_id
where s.avg_fragmentation_in_percent>29
--and i.name<>'ix_objective_customer_guid'
open R_Index
fetch next from R_Index into @table,@index,@avg
while(@@fetch_status=0)
begin
if(@avg>=30)
begin
set @str='alter index '+rtrim(@index)+' on '+rtrim(@table)+' rebuild'
end
if(@avg<30)
begin
set @STR='alter index '+rtrim(@index)+' on '+rtrim(@table)+' reorganize'
end
exec sp_executesql @str
fetch next from R_Index into @table,@index,@avg
end
close R_Index
deallocate R_Index