sql server的常用sql范例

约定不等于承诺〃 2022-09-02 12:46 188阅读 0赞

– 查找某个表的统计信息 相关信息：上次更新时间、修改行  
SELECT name, sp.\*  
FROM sys.stats AS stat  
CROSS APPLY sys.dm\_db\_stats\_properties(stat.object\_id, stat.stats\_id) AS sp  
WHERE stat.object\_id = object\_id(‘op\_param\_sniffing’);

– 指定sql重编译  
select \* from op\_param\_sniffing where product\_id = ‘200’ option(RECOMPILE)

– 指定使用某索引  
select \* from op\_param\_sniffing with(index(idx\_op\_param\_sniffing\_product\_id)) where product\_id = ‘100’

– 查询某表的某统计信息的密度向量  
DBCC SHOW\_STATISTICS(‘op\_param\_sniffing’, ‘op\_param\_sniffing\_all’) WITH DENSITY\_VECTOR

– 查看表的碎片情况  
dbcc showcontig(‘op\_param\_sniffing’)

– 查看表所有的统计信息  
select stats\_date(s.object\_id,s.stats\_id) \[统计信息的最新更新的日期\],  
c.name as 列名,  
\*  
from sys.stats s  
inner join sys.stats\_columns sc  
on s.object\_id = sc.object\_id and  
s.stats\_id = sc.stats\_id  
inner join sys.columns c  
on s.object\_id = c.object\_id and  
sc.column\_id = c.column\_id  
where s.object\_id = object\_id(‘op\_param\_sniffing’)

– 其它查看最近一次更新日期：若是索引对应的统计信息，则还会存在与sys.indexes里面  
SELECT name AS stats\_name, STATS\_DATE(object\_id, stats\_id) AS statistics\_update\_date FROM sys.stats WHERE object\_id = OBJECT\_ID(‘op\_param\_sniffing’);  
SELECT name AS index\_name, STATS\_DATE(object\_id, index\_id) AS statistics\_update\_date FROM sys.indexes WHERE object\_id = OBJECT\_ID(‘op\_param\_sniffing’);

– 查询某表是否有自动创建的统计信息  
SELECT OBJECT\_NAME(s.object\_id) AS object\_name,  
COL\_NAME(sc.object\_id, sc.column\_id) AS column\_name,  
s.name AS statistics\_name  
FROM sys.stats AS s  
INNER JOIN sys.stats\_columns AS sc  
ON s.stats\_id = sc.stats\_id AND s.object\_id = sc.object\_id  
WHERE s.name like ‘\_WA%’ and s.object\_id = object\_id(‘op\_article\_channel’)  
ORDER BY s.name;