sql server数据库索引的查看和创建

 

创建数据库[BCVote].[dbo].[m_vote_record] 的索引 名称为m_vote_recordIPIndex

CREATE INDEX m_vote_recordIPIndex
ON [BCVote].[dbo].[m_vote_record] (ip)

查询数据库中的所有索引

SELECT CASE
WHEN t.[type] = 'U' THEN '表'
WHEN t.[type] = 'V' THEN '视图'
END AS '类型',
SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',
i.[name] AS 索引名称,
SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',
CASE
WHEN i.[type] = 1 THEN
'聚集索引'
WHEN i.[type] = 2 THEN
'非聚集索引'
WHEN i.[type] = 3 THEN
'XML索引'
WHEN i.[type] = 4 THEN
'空间索引'
WHEN i.[type] = 5 THEN
'聚簇列存储索引'
WHEN i.[type] = 6 THEN
'非聚集列存储索引'
WHEN i.[type] = 7 THEN
'非聚集哈希索引'
END AS '索引类型',
CASE
WHEN i.is_unique = 1 THEN
'唯一'
ELSE
'不唯一'
END AS '索引是否唯一'
FROM sys.objects t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
CROSS APPLY
(
SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER BY col.column_id
FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1 AND index_id > 0ORDER BY i.[name];

 

查询后的结果