前言：使用SQL的过程中，有些用得到，但并不特别常用的SQL，有时需要到网上找资料，再自行测试验证，

等到要用的时候还需要花费不少的时间。

下面对一些实用的 SQL进行分享：

--有些是基于KIS旗舰版、专业版的数据结构

【特别申明】此文章的内容是本人在使用过程中收集到的，以实用为主，不作为SQL SERVER的官方内容，

希望对读者有所帮助。

本文章有可能存在不完整或错漏，或描述不准确之处，作者不承担使用此文章带来的任何不良后果，敬请谅解。

请读者自行决定是否使用。

本文章的SQL基于SQL SERVER 2016 版本环境测试验证通过，在其他数据库环境比如ORACLE、MYSQL中可能不适用。

SQL2000或更低的SQL版本可能存在不兼容的情况。

一、已有数据库的备份文件，需要查询是哪个版本的数据库备份出来的

示例：  f:\db\D02Chw003.bak 文件，查询语法：

Restore HeaderOnly From Disk ='f:\db\D02Chw003.bak'

查询效果如下图

图1

查询出来的结果中 852 是内部版本号，以下是从网上查到的SQL版本与内部版本号的对应关系 

其中内部版本号661，也有网上文章说是SQL Server 2008的,实际上某次在SQL2008R2环境备份出来的BAK谁的，查询到的内部版本号是661，如果要恢复数据库，高版本的数据库环境可恢复低版本的备份。

另外，SQL  Server 2017因手上没有相关的环境，也没找到具体的数据，暂未列入

（如下所示：内部版本号904对应于SQL Server 2019）

SQL Server 2019   904

SQL Server 2016   852

SQL Server 2014   782

SQL Server 2012   706

SQL Server 2008 R2   665 或 661

SQL Server 2008         655

SQL Server 2005 with vardecimal enabled   612

SQL Server 2005   611

SQL Server 2000   539

SQL Server 7   515

二、 查询数据库的逻辑名

SQL示例：

use  AIS20230518075307

SELECT name,physical_name  FROM sys.database_files

图2

SQL示例：（根据备份文件查询其逻辑名）

restore filelistonly from DISK = 'F:\01\A515cd.bak'

图3

三、收缩数据库的日志文件

适用场景：日志文件不是特别重要，比如非生产数据库，恢复数据库后，日志文件特别大，可能10G或更大

收缩后，日志文件可能不超过50K

SQL示例：其中 C4Bszah111是数据库名，SCM102SP2_Log是日志文件的逻辑名

use C4Bszah111

ALTER DATABASE C4Bszah111 SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE C4Bszah111 SET RECOVERY SIMPLE --简单模式

DBCC SHRINKFILE (N'SCM102SP2_Log' , 11, TRUNCATEONLY) 

四、字段改长度并增加默认值

SQL示例：以下3行中，正常从第2行开始执行，有可能提示有XXX名的约束，则将约束名复制到第1行，

再按1-2-3行的顺序执行：

alter table T_FA_CARD_l drop constraint DF__T_FA_CARD__FNAME__5D440CDB

alter table T_FA_CARD_l alter column FNAME nvarchar(255) not null

ALTER TABLE T_FA_CARD_l add  DEFAULT (('')) for FNAME

五、用SQL恢复数据库时，指定物理文件的位置及名称：

QL示例：

RESTORE DATABASE [test23]   --为待还原库名

FROM 

  DISK = 'F:\05\AAA6.BAK'     --备份文件的位置

WITH

  MOVE 'SCM102SP2_Data' --数据文件逻辑名字

  TO 'F:\05\AAA6.MDF',   --指定数据文件路径

  MOVE 'SCM102SP2_log'  TO 'F:\05\AAA6.lDF',

STATS = 10, REPLACE

六、修复数据库逻辑错误

SQL示例：

需要数据库在独占访问的情况下使用（有可能要重启SQL SERVER服务），如果损坏不严重则可以修复。

修复成功标志：

1、下列所有SQL指令执行成功；

2、成功执行最后一行 DBCC CHECKDB 后，结果中倒数第二行的错误数量都为0。

use master

go

ALTER DATABASE AIS20200720184737 SET EMERGENCY

ALTER DATABASE AIS20200720184737 SET SINGLE_USER

dbcc checkdb(AIS20200720184737,REPAIR_ALLOW_DATA_LOSS)

dbcc checkdb(AIS20200720184737,REPAIR_REBUILD) 

ALTER DATABASE AIS20200720184737 SET  MULTI_USER

ALTER DATABASE AIS20200720184737 SET ONLINE

DBCC CHECKDB(AIS20200720184737)

七、查看耗时长的SQL

SELECT TOP 50

qs.total_worker_time/qs.execution_count as [Avg CPU Time],qt.text,

SUBSTRING(qt.text,qs.statement_start_offset/2, 

(case when qs.statement_end_offset = -1 

then len(convert(nvarchar(max), qt.text)) * 2 

else qs.statement_end_offset end -qs.statement_start_offset)/2) 

as query_text,

qt.dbid, dbname=db_name(qt.dbid),

qt.objectid 

FROM sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Avg CPU Time] DESC

八、MSDE（SQL 桌面版）的实例名 SQLEXPRESS

在安装了MSDE的服务器上，可以用简化版的SQL客户端登录，服务器名可参考以下文本，用WINDOWS身份验证

.\SQLEXPRESS

九、查询另一个台服务器的SQL

SQL示例：（需要知道另外服务器的IP、账号、密码）

select * FROM  OPENDATASOURCE('SQLOLEDB',

'Data Source=172.20.36.28;User ID=sa;Password=sa123Q8!0'

).A510SS.dbo.icstockbill

十、日期函数示例：

  --日期计算 ：天数差异 日期函数，后面的日期减去前面的日期，后面日期更早时，结果为负

 SELECT  DATEDIFF( Day,'2022-6-30','2020-11-20' ),DATEDIFF (Day,'2022-6-30','2019-09-27' ) 

  ,DATEDIFF (Day,'2022-6-30','2019-4-16' )

  --差多少天后的日期,负数为以前的日期

 SELECT DATEADD (DD,-396,'2022-6-30')

--日期计算函数 日期加三年

dateadd(year,3,e.FPRODUCEDATE)

--三年少一天

dateadd(day,-1, dateadd(year,3,e.FPRODUCEDATE))

--本月第一天

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

--下个月第一天

select CONVERT(varchar(10),DATEADD(m,1 ,dateadd(dd,-day(getdate())+1,getdate())) ,111)

select CONVERT(DATE,DATEADD(m,1 ,dateadd(dd,-day(getdate())+1,getdate())) ,111)

--下个月最后一天:

SELECT convert(varchar(10),dateadd(d,-1,dateadd(m,1,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))),111)

SELECT convert(DATE,dateadd(d,-1,dateadd(m,1,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))),111)

--所在月最后时刻到毫秒

select dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,'2022-2-19')+1,0))

十一、查数据库大小，先 USE 待查数据库，单位为MB：

use SCM

select name, convert(float,size) * (8192.0/1024.0)/1024.  MB,FILENAME from sysfiles

十二、查询SQL SERVER服务器的版本

select @@version

下图所示是SSMS（SQL SERVER查询分析器，可理解为SQL客户端）版本是SQL2008R2，

连接到了SQL2012版本的数据库服务器

图4