【20002】Sql Server 常用语法、常用语句

性能检测语句

-- 1.查询sql所消耗io性能
set statistics time on 
-- 2.查看SQL语句时间消耗
set statistics profile on 
-- 3.查看SQL语句索引消耗
set statistics io on
-- 执行语句
    select password from userTest group by password order by password
-- 关闭检测
set statistics io off
set statistics profile off
set statistics time off

临时表

临时表在SysObjects表里是找不到的

• 本地临时表

  创建临时表和创建普通表一样，只需要给名称前加一个#即可

CREATE TABLE #temp (
    [id] int NOT NULL IDENTITY(1,1) ,
    [name] varchar(255) NULL ,
    [createTime] datetime2 NULL DEFAULT getdate() ,
    PRIMARY KEY ([id])
    )

• 可见性

  一个临时表的作用时效是一个事务

• 全局临时表

  创建时在名称前加一个##即可

CREATE TABLE ##temp (
    [id] int NOT NULL IDENTITY(1,1) ,
    [name] varchar(255) NULL ,
    [createTime] datetime2 NULL DEFAULT getdate() ,
    PRIMARY KEY ([id])
    )

• 可见性

  • A事务创建##temp后，B事务是可以查到##temp的

  • A事务创建##temp后，关闭A事务，B事务查不到##temp

  • A事务创建##temp后，B事务加锁查询##temp后持续未提交，关闭A事务，C事务还可以查询到##temp

  • A事务创建##temp后，B事务加锁查询##temp后持续未提交，提交A事务，C事务还可以查询到##temp，关闭B事务后，C事务就不能查询到了

begin tran
select * from ##temp with(xlock)
waitfor delay '1:0:0'
commit tran

• 一般操作

-- 查询数据并写入临时表：
select * into #tab from table;
-- 删除临时表：
drop table #tab;

tran | waitfor delay

• begin tran开启事务
• commit tran 提交事务
• waitfor delay 延迟 后面跟'0:1:30'表示延迟1分30秒

示例

---开启事务
begin tran
-- select * from userLogin
waitfor  delay '0:1:30' --等待5秒执行下面的语句
update userLogin set name = 'text' where id = 12
commit tran

系统表sql

• 获取表名称

-- 获取表名称
select top 10000
name 表名称,create_date 创建时间,modify_date 最后修改时间
from sys.tables  order by create_date desc

• 查询哪些sql的逻辑读很高,之后进行优化

--查询哪些sql的逻辑读很高,之后进行优化
SELECT
    s2.dbid,
    s1.sql_handle,
  (SELECT TOP 1 SUBSTRING ( s2. TEXT, statement_start_offset / 2 + 1,   (( CASE WHEN statement_end_offset = - 1  THEN ( LEN( CONVERT (nvarchar(MAX), s2. TEXT)) * 2 )   ELSE statement_end_offset END ) - statement_start_offset ) / 2 + 1 )    ) AS sql_statement,
    execution_count,
    plan_generation_num,
    last_execution_time,
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes 
FROM
    sys.dm_exec_query_stats AS s1  CROSS APPLY sys.dm_exec_sql_text (sql_handle) AS s2 
WHERE
    s2.objectid IS null 
ORDER BY
    last_worker_time DESC,
    s1.sql_handle,
    s1.statement_start_offset,
    s1.statement_end_offset ;

• 查看索引使用率（很慢）

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempFragmentation
SELECT TOP 20
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC'
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
DROP TABLE #TempFragmentation

参考链接
SQL Server索引的维护 - 索引碎片、填充因子