合 MSSQL常用SQL语句
Tags： MSSQLSQL Server常用SQL
👉 本文共约8466个字，系统预计阅读时间或需32分钟。
目录
- SQL Server对象查询（只查询当前数据库）
- 检查数据库完整性
- 数据库重命名、修改恢复模式、修改用户模式
- 数据库重命名
- 设置数据库为完整恢复模式
- 只允许一个用户访问数据库
- 只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库
- 多用户模式
- 移动文件
- 扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称
- 添加文件组
- 添加数据文件
- 添加日志文件
- 修改数据文件的大小,增长大小,最大大小
- 修改数据文件或日志文件的逻辑名称
- 设置默认文件组、只读文件组
- 收缩数据库、收缩文件
- 重新生成索引
- 查看实例名
- 查看数据库属性
- 查看数据库恢复模式
- 查看日志空间
- 系统函数
- 查看数据文件
- 查询缓存中具体的执行计划，及对应的SQL
- 查询缓存的各类执行计划，及分别占了多少内存
- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
- 查询SqlServer总体的内存使用情况
- 查询SQLSERVER内存使用情况
- 看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据
- 查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）–全局
- 查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
- 查询会话中有多少个worker在等待
- 查看当前数据库用户连接
- 查看数据库大小
- 查看数据库可用大小，已使用大小
- SQL优化相关、执行时间
- 查看索引碎片
- 查看指定表的索引信息
- 重新生成或重新组织索引
- 查看索引使用情况
- 统计信息
- 自动索引和统计信息管理
- 查看缺少的索引
- ALTER DATABASE 语法
- A. 向数据库中添加文件
- B. 向数据库中添加由两个文件组成的文件组
- C. 向数据库中添加两个日志文件
- D. 从数据库中删除文件
- E. 修改文件
- F. 将文件移至新位置
- G. 将 tempdb 移至新位置
- H. 使文件组成为默认文件组
- I. 使用 ALTER DATABASE 添加文件组
- J. 更改文件组，以便当文件组中的某个文件达到自动增长阈值时，文件组中的所有文件都会增长
- 参考
SQL Server对象查询（只查询当前数据库）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | select case when xtype='U' then '表' when xtype='P' then '存储过程' when xtype='FN' then '标量值函数' when xtype='IF' then '表值函数' when xtype='V' then '视图' when xtype='TR' then '触发器' when xtype='SN' then '同义词' end as xtype, count(*) cnt from sysobjects where xtype in('P','FN','IF','TR','TR','U','V') and uid=1 and category=0 group by xtype order by xtype; |
检查数据库完整性
1 | dbcc checkdb(lhrdb) |
通过加tablock提高速度
1 | dbcc checkdb(test) with tablock |
数据库重命名、修改恢复模式、修改用户模式
数据库重命名
1 | ALTER DATABASE WC MODIFY NAME = test |
设置数据库为完整恢复模式
1 | alter database test set recovery full |
只允许一个用户访问数据库
1 2 3 | alter database test set single_user with rollback after 10 seconds --指定多少秒后回滚事务 |
只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库
1 2 3 | alter database wc set restricted_user with rollback immediate --立即回滚事务 |
多用户模式
1 2 3 | alter database wc set multi_user with no_wait --不等待立即改变，如不能立即完成，那么会导致执行错误 |
移动文件
–由于在SQL Server中文件组、文件不能离线
–所以必须把整个数据库设置为离线
1 2 3 4 5 6 | checkpoint go ALTER DATABASE WC SET OFFLINE go |
–修改文件名称
1 2 3 4 5 6 7 | ALTER DATABASE WC MODIFY FILE ( NAME = WC_fg8, FILENAME = 'D:\WC\WC_FG8.NDF' ) go |
–把原来的文件复制到新的位置：‘D:\WC\WC_FG8.NDF’
–设置数据库在线
1 2 | ALTER DATABASE WC SET ONLINE |
扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称
添加文件组
1 2 | ALTER DATABASE test ADD FILEGROUP WC_FG8 |
添加数据文件
1 2 3 4 5 6 7 8 9 10 | ALTER DATABASE test ADD FILE ( NAME = WC_FG8, FILENAME = 'D:\WC_FG8.ndf', SIZE = 1mb, MAXSIZE = 10mb, FILEGROWTH = 1mb ) TO FILEGROUP WC_FG8 |
添加日志文件
1 2 3 4 5 6 7 8 9 | ALTER DATABASE test ADD LOG FILE ( NAME = WC_LOG3, FILENAME = 'D:\WC_FG3.LDF', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 100KB ) |
修改数据文件的大小,增长大小,最大大小
1 2 3 4 5 6 7 8 | ALTER DATABASE test MODIFY FILE ( NAME = 'WC_FG8', SIZE = 2MB, --必须大于之前的大小,否则报错 MAXSIZE= 8MB, FILEGROWTH = 10% ) |
修改数据文件或日志文件的逻辑名称
1 2 3 4 5 6 | ALTER DATABASE test MODIFY FILE ( NAME = WC_LOG3, NEWNAME = WC_FG33 ) |
设置默认文件组、只读文件组
–设置默认文件组
1 2 | ALTER DATABASE WC MODIFY FILEGROUP WC_FG8 DEFAULT |
–设为只读文件组
–如果文件已经是某个属性，不能再次设置相同属性
1 2 | ALTER DATABASE WC MODIFY FILEGROUP WC_FG8 READ_WRITE |
收缩数据库、收缩文件
–收缩数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DBCC SHRINKDATABASE('test', --要收缩的数据库名称或数据库ID 10 --收缩后，数据库文件中空间空间占用的百分比 ) DBCC SHRINKDATABASE('test', --要收缩的数据库名称或数据库ID 10, --收缩后，数据库文件中空闲空间占用的百分比 NOTRUNCATE --在收缩时，通过数据移动来腾出自由空间 ) DBCC SHRINKDATABASE('test', --要收缩的数据库名称或数据库ID 10, --收缩后，数据库文件中空间空间占用的百分比 TRUNCATEONLY --在收缩时，只是把文件尾部的空闲空间释放 ) |
–收缩文件
1 2 3 4 5 6 7 8 | DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称 7 --要收缩的目标大小，以MB为单位 ) DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称 EMPTYFILE --清空文件，清空文件后，才可以删除文件 ) |
重新生成索引
1 2 3 4 5 6 7 8 9 | ALTER INDEX [idx_temp_lock_id] ON [dbo].[temp_lock] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF ) |
查看实例名
1 2 3 4 | SELECT @@SERVICENAME AS InstantName; SELECT ISNULL(SERVERPROPERTY('InstanceName'),'MSSQLSERVER') AS InstanceName; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
