前言：使用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
赞 9
打赏