--找出所有非xml索引 DECLARE cur CURSOR FOR SELECT [object_name]=s.name+'.'+OBJECT_NAME(A.object_id), B.name FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,null,null,null) AS A JOIN sys.indexes AS B ON A.[object_id]=B.[object_id] AND A.[index_id]=B.[index_id] JOIN sys.objects AS o ON A.[object_id]=o.[object_id] JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id] WHERE A.[index_id]>0 AND NOT EXISTS( SELECT * FROM sys.xml_indexes WHERE A.[object_id]=[object_id] AND A.[index_id]=[index_id] );OPEN cur; DECLARE @objname varchar(128),@indname varchar(128);DECLARE @sql nvarchar(4000);FETCH NEXT FROM cur INTO @objname,@indname;--重整所有索引,在这里先不管索引的碎片程度 WHILE @@FETCH_STATUS=0 BEGIN SET @sql='ALTER INDEX '+@indname+' ON '+@objname+' REBUILD'; EXEC(@sql); FETCH NEXT FROM cur INTO @objname,@indname; END
DECLARE cur CURSOR FOR
SELECT
[object_name]=s.name+'.'+OBJECT_NAME(A.object_id),
B.name
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,null,null,null) AS A
JOIN sys.indexes AS B
ON A.[object_id]=B.[object_id]
AND A.[index_id]=B.[index_id]
JOIN sys.objects AS o
ON A.[object_id]=o.[object_id]
JOIN sys.schemas AS s
ON o.[schema_id]=s.[schema_id]
WHERE A.[index_id]>0
AND NOT EXISTS(
SELECT *
FROM sys.xml_indexes
WHERE A.[object_id]=[object_id]
AND A.[index_id]=[index_id]
);OPEN cur;
DECLARE @objname varchar(128),@indname varchar(128);DECLARE @sql nvarchar(4000);FETCH NEXT FROM cur INTO @objname,@indname;--重整所有索引,在这里先不管索引的碎片程度
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='ALTER INDEX '+@indname+' ON '+@objname+' REBUILD';
EXEC(@sql);
FETCH NEXT FROM cur INTO @objname,@indname;
END
CLOSE cur;
DEALLOCATE cur;
2:重建索引默认只会重建聚集索引,需要指定all关键字。删除再重建是个费时费力的过程。删除聚集索引时所有非聚集索引需要重建,然后重建聚集索引,所有非聚集索引再次被重建。
建议用重建索引而不是删除再重建
3:mssql中没有oracle中的tablespace概念。但有文件和文件组的概念。个人理解相当于oracle中的tablespace.
create table tab1(id int,string char(5)) on [PRIMARY]
create NONCLUSTERED INDEX [inx_tab1_id] ON [dbo].[tab1]
(
[id] ASC
) on [groupinx]
4:一般基于的是文件/文件组备份恢复。