非聚集索引查询
来源:互联网 发布:网络运营推广新存活 编辑:程序博客网 时间:2024/03/29 04:34
下面查询数据库的非聚集索引的所有记录数目,保留和使用空间:
USE DatabaseNameHere;GO-- Drop temporary table if existsIF OBJECT_ID('tempDB.dbo.#IndexInfo') IS NOT NULL DROP TABLE #IndexInfo ; -- Create temporary tableCREATE TABLE #IndexInfo( ObjectName VARCHAR(250), IndexName VARCHAR(250), IndexID INT, PartitionNumber INT, [#Records] INT, [Reserved(MB)] INT, [Used(MB)] INT);-- Collect index infoINSERT INTO #IndexInfoSELECT o.name AS ObjectName, i.name AS IndexName, i.index_id AS IndexID, p.partition_number AS PartitionID, p.[rows] AS [#Records], a.total_pages * 8 / 1024 AS [Reserved(MB)], a.used_pages * 8 / 1024 AS [Used(MB)]FROM sys.indexes AS i INNER JOIN sys.partitions AS p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id INNER JOIN sys.sysobjects o ON i.[object_id] = o.idWHERE i.name NOT LIKE 'sys%' AND o.name NOT LIKE 'sys%' AND i.[type] <> 1ORDER BY a.total_pages DESC;-- Return index info with TOTAL SELECT ObjectName, IndexName, IndexID, PartitionNumber, [#Records], [Reserved(MB)], [Used(MB)]FROM #IndexInfoUNION ALLSELECT 'TOTAL', NULL, NULL, NULL, NULL, SUM(a.total_pages * 8 / 1024) AS [Reserved(mb)], SUM(a.used_pages * 8 / 1024) AS [Used(mb)]FROM sys.indexes AS i INNER JOIN sys.partitions AS p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id INNER JOIN sys.sysobjects o ON i.[object_id] = o.idWHERE o.name NOT LIKE 'sys%' AND i.[type] <> 1;GO
- 非聚集索引查询
- 非聚集索引中的查询
- 非聚集索引,聚集索引
- 聚集索引,非聚集索引
- 聚集索引,非聚集索引
- 聚集索引,非聚集索引
- 聚集索引非聚集索引
- 聚集索引&非聚集索引
- 聚集索引,非聚集索引
- 聚集与非聚集索引
- 聚集与非聚集索引
- 聚集和非聚集索引
- 非聚集索引
- 非聚集索引结构
- 创建非聚集索引
- 聚集索引和非聚集索引
- 聚集索引和非聚集索引
- 聚集索引和非聚集索引
- HGE引擎学习笔记1 : 如何模拟物理碰撞
- 【第一章】C#的进化史——C#1.0,2.0,3.0区别比较(二)——C#2.0,3.0 简化排序的特性
- lnmp开启nginx和php-cgi多个进程
- 最高效的工具:笔和纸
- 指针参数是如何传递内存的
- 非聚集索引查询
- 使用Eclipse的几个必须掌握的快捷方式+改默认编码 .
- Affective Loop
- dreamweaver网站 的google自定义搜索
- MySQL学习笔记
- 黑马程序员——static和final关键字
- Spring AMQP 1.0 GA发布了
- PHP安装问题总结
- Android 4.0通过新的特性统一了平板电脑与手机