sqlserver入门篇

基本操作

登入与登出

:: 通过 sqlcmd 登录实例，登录之后每一步加上 go 显式执行
sqlcmd -S localhost -E
sqlcmd -S 127.0.0.1 -U dba_admin -P Admin_147
sqlcmd -S "." -U "dba_admin" -P "Admin_147"

:: 通过 sqlcmd 执行外部脚本
sqlcmd -S 10.135.63.101 -U dba_admin -P Admin_147 -d db001 -i C:\script.sql

实例的管理

--.查看SQL版本信息
SELECT 
  @@SERVERNAME AS InstanceName
 ,CONVERT(sysname, SERVERPROPERTY('MachineName')) AS ServerName
 ,CONVERT(sysname, SERVERPROPERTY('ServerName')) AS ServerName
 ,HOST_NAME() AS ClientServerName
 ,CONVERT(nvarchar(256), SERVERPROPERTY('Edition')) AS Edition
 ,CONVERT(nvarchar(256), SERVERPROPERTY('Collation')) AS Collation
 ,CONVERT(nvarchar(256), SERVERPROPERTY('ProductVersion')) AS ProductVersion
 ,CONVERT(nvarchar(256), SERVERPROPERTY('ProductLevel')) AS ProductLevel
 ,CONVERT(nvarchar(256), SERVERPROPERTY('ResourceVersion')) AS ResourceVersion
 ,CONVERT(datetime, SERVERPROPERTY('ResourceLastUpdateDateTime')) AS ResourceLastUpdateDateTime
 ,@@VERSION AS Version
 ,@@LANGUAGE AS Language
 ,cpu_count AS Logical_CPU_Count
 ,hyperthread_ratio AS Hyperthread_Ratio
 ,cpu_count / hyperthread_ratio AS Physical_CPU_Count
 ,physical_memory_kb      --.物理内存大小
 ,sqlserver_start_time    --.启动时间(SQL2K8及以上)
 ,sql_memory_model_desc   --.LOCK_PAGES=锁定内存中的页
 ,MAX_DOP = (SELECT CONVERT(int, VALUE_IN_USE) FROM SYS.CONFIGURATIONS WHERE NAME='MAX DEGREE OF PARALLELISM') 
 ,MAX_SERVER_MEMORY = (SELECT CONVERT(int, VALUE_IN_USE) FROM SYS.CONFIGURATIONS WHERE NAME='MAX SERVER MEMORY (MB)') 
FROM sys.dm_os_sys_info

--.查看版本,只取大版本数字号,比如sqlserver2014sp3为12.0.6024.0只返回12,方便脚本处理
DECLARE @version SYSNAME 
SET @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(40)) 
SET @version = SUBSTRING(@version,1,CHARINDEX('.',@version)-1) 
SELECT @version

--.更改数据文件和日志文件默认存放路径(重启生效)
execute xp_create_subdir 'd:\sqldata'
execute xp_create_subdir 'd:\sqllog'
execute master..xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='Software\Microsoft\MSSQLServer\MSSQLServer', @value_name='DefaultData', @type=REG_SZ, @value='d:\sqldata'
execute master..xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='Software\Microsoft\MSSQLServer\MSSQLServer', @value_name='DefaultLog',  @type=REG_SZ, @value='d:\sqllog'

--.性能计数器
select * from sys.dm_os_performance_counters

查看硬件配置：

--.机器型号
exec sys.sp_readerrorlog 0, 1, 'Manufacturer'

--查看CPU型号
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';

--.操作系统版本
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows NT\CurrentVersion','ProductName'

--.查看系统信息(CPU,RAM等)
exec xp_msver
exec xp_msver 'ProductVersion'
exec xp_msver 'PhysicalMemory'

--.取IP地址
SELECT TOP 1 local_net_address 
FROM SYS.DM_EXEC_CONNECTIONS 
WHERE local_net_address IS NOT NULL AND net_transport = 'TCP' AND local_net_address not in('127.0.0.1','::1') AND len(local_net_address)<16

--.查看各磁盘可用空间
EXEC master..xp_fixeddrives

--.查看各磁盘的使用率
SELECT DISTINCT 
   vs.volume_mount_point 
  ,vs.file_system_type 
  ,vs.logical_volume_name 
  ,CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total_Size_GB]
  ,CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available_Size_GB]
  ,CAST(CAST(vs.available_bytes AS FLOAT) / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [SpaceFree_%]
FROM sys.master_files AS mf WITH(NOLOCK) 
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs 
ORDER BY vs.volume_mount_point OPTION (RECOMPILE)

--.创建目录
exec xp_create_subdir 'C:\MSSQL\Backup'

--.读取目录清单
exec xp_dirtree 'C:\Program Files',1,1

--.清空目录
-- exec master.dbo.xp_delete_file 0, 'D:\SQLDATA\MSSQL\Backup'

服务的启停（在 cmd 命令提示符下执行）：

:: 更改SQL服务的启动账号(内含服务重启命令,注意顺序)
net user sqladmin kT6f7m_wPKdW4037 /add /Y /expires:never
net localgroup administrators sqladmin /add
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
sc config MSSQLSERVER obj= ".\sqladmin" password= "kT6f7m_wPKdW4037"
sc config SQLSERVERAGENT obj= ".\sqladmin" password= "kT6f7m_wPKdW4037"
sc config MSSQLSERVER start= auto
sc config SQLSERVERAGENT start= auto
NET START MSSQLSERVER
NET START SQLSERVERAGENT

Linux系统：微软官方发布的一款针对 SQL Server 新命令行的交互工具 mssql-cli

#.安装mssql-cli
yum install -y -q epel-release libunwind libicu python-pip
pip install mssql-cli

#.语法
# mssql-cli -S <server name> -U <user name> -d <database name>

#.使用mssql-cli登录
shell> mssql-cli -U sa
master>SELECT @@version

数据库的管理

关于数据库的基本用法：参考 ALTER DATABASE SET

--.创建新库-可重复执行
USE master
IF DB_ID('xxx') IS NULL
CREATE DATABASE xxx COLLATE Chinese_PRC_CI_AS

--.创建新库
CREATE DATABASE xxx
ON PRIMARY ( NAME = xxx, FILENAME = 'e:\sqldata\xxx.mdf', SIZE = 100MB, MAXSIZE = UNLIMITED, FILEGROWTH = 100MB ) 
LOG ON ( NAME = xxx_log, FILENAME = 'f:\sqllog\xxx_log.ldf', SIZE = 1024MB, MAXSIZE = 2048GB, FILEGROWTH = 1000MB ) 

--.调整自动增量
CREATE DATABASE xxx 
ALTER DATABASE xxx MODIFY FILE ( NAME = 'xxx', FILEGROWTH = 1024MB )
ALTER DATABASE xxx MODIFY FILE ( NAME = 'xxx_log', FILEGROWTH = 2048MB )

--.更改当前数据库的所有者,示例改为sa
use xxx; exec sp_changedbowner 'sa'

--.设置兼容性级别
ALTER DATABASE xxx SET COMPATIBILITY_LEVEL = 160

--.设置字符集
ALTER DATABASE xxx COLLATE Chinese_PRC_CI_AS

--.设置单用户/多用户模式
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE xxx SET MULTI_USER

--设置/取消只读模式
ALTER DATABASE xxx SET READ_ONLY WITH ROLLBACK IMMEDIATE
ALTER DATABASE xxx SET READ_WRITE WITH ROLLBACK IMMEDIATE

--设置脱机/在线
ALTER DATABASE xxx SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE xxx SET ONLINE WITH ROLLBACK IMMEDIATE

--设置简单/完整模式
ALTER DATABASE xxx SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE xxx SET RECOVERY FULL WITH NO_WAIT

--.查看当前上下文
select db_id() as CurrentDB_ID, db_name() as CurrentDB_Name, @@SPID as CurrentSPID

--.查看指定db的状态,只读/单用户均返回 ONLINE,其他如RESTORING/RECOVERING/SUSPECT/EMERGENCY等
select DATABASEPROPERTYEX ('xxx','status')

--.查看所有库的配置信息
exec sp_helpdb

--.查看当前数据库文件的配置
exec sp_helpfile

--.查看所有数据库文件的配置
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; exec sp_helpfile'

--.查看数据/日志/备份/安装目录的配置
declare @Data nvarchar(512),@Log nvarchar(512),@Backup nvarchar(512),@LoginMode int,@Root nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @Data OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @Log OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @Backup OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @LoginMode OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @Root OUTPUT
select @Data AS 'DefaultData',@Log AS 'DefaultLog',@Backup AS 'BackupDirectory',@LoginMode AS 'LoginMode',@Root AS 'DefaultSetup'

--.修改数据库名称
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE
exec sp_renamedb 'xxx','xxx222'
ALTER DATABASE xxx222 SET MULTI_USER

--.修改逻辑文件名
ALTER DATABASE xxx222 MODIFY FILE (NAME = N'xxx', NEWNAME = 'xxx222')
ALTER DATABASE xxx222 MODIFY FILE (NAME = N'xxx_log', NEWNAME = 'xxx222_log')
ALTER DATABASE xxx222 MODIFY FILE (NAME = N'xxx222', FILEGROWTH = 10240KB, SIZE = 102400KB)

--.删除数据库之前建议先设置单一用户模式
ALTER DATABASE xxx222 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE xxx222

--.删除DB报错：数据库主体在该数据库中拥有架构，无法删除。错误代码：15138
USE xxx 
GO 
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo 
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [db_owner]

关于数据库的批量操作

关于数据库的批量操作：

--.更改所有数据库的所有者为sa 
-- SELECT name, suser_sname(owner_sid) FROM sys.databases WHERE database_id > 4 AND state = 0 
SELECT 'USE '+QUOTENAME(name)+';'+char(13)+ 'EXEC sp_changedbowner ''sa'';'+char(13) 
FROM master.sys.databases WITH(NOLOCK) 
WHERE database_id > 4 AND state = 0 AND suser_sname(owner_sid) <> 'sa' 

--.更改兼容级别<参考master库>
-- SELECT name,compatibility_level FROM master.sys.databases WHERE database_id > 4 AND state = 0 
DECLARE @compatibility_level VARCHAR(10) 
SELECT @compatibility_level = compatibility_level FROM master.sys.databases WHERE database_id = 1 
SELECT 'ALTER DATABASE '+QUOTENAME(name)+' SET COMPATIBILITY_LEVEL = '+@compatibility_level+ '' 
FROM master.sys.databases WITH(NOLOCK) 
WHERE database_id > 4 AND state = 0 AND compatibility_level <> @compatibility_level 

--.更改字符集<参考实例字符集>
-- SELECT name,collation_name FROM master.sys.databases WHERE collation_name <> SERVERPROPERTY ('Collation')
DECLARE @collation VARCHAR(40) 
SELECT @collation = CAST(SERVERPROPERTY ('Collation') AS VARCHAR(40)) 
SELECT 'ALTER DATABASE '+QUOTENAME(name)+' COLLATE '+@collation+ '' 
FROM master.sys.databases WITH(NOLOCK) 
WHERE database_id > 4 AND state = 0 AND collation_name <> SERVERPROPERTY ('Collation') 

--.更改所有<完整模式>为简单恢复模式
-- SELECT name,recovery_model_desc FROM master.sys.databases WHERE database_id > 4 AND state = 0 
SELECT 'ALTER DATABASE '+QUOTENAME(name)+' SET RECOVERY SIMPLE WITH NO_WAIT' 
FROM master.sys.databases WITH(NOLOCK) 
WHERE database_id > 4 AND state = 0 AND recovery_model = 1 

--.收缩所有<简单模式>数据库的日志文件 
SELECT 'USE '+QUOTENAME(name)+';'+char(13)+ 'DBCC SHRINKFILE (2, 0, TRUNCATEONLY);'+char(13) 
FROM master.sys.databases WITH(NOLOCK) 
WHERE database_id > 4 AND state = 0 AND recovery_model = 3 

--.收缩所有数据库
SELECT 'USE '+QUOTENAME(name)+';'+char(13)+ 'DBCC SHRINKDATABASE('''+name+''') WITH NO_INFOMSGS;'
FROM master.sys.databases WITH(NOLOCK) 
WHERE database_id > 4 AND state = 0 
ORDER BY name

关于数据库的高级设置

关于数据库的高级设置：

--.添加数据文件
USE master; ALTER DATABASE xxx ADD FILE ( NAME = xxx_2, FILENAME = 'c:\xxx_2.ndf', SIZE = 5MB )

--.删除数据文件
USE xxx; DBCC SHRINKFILE (xxx_2, EMPTYFILE)
USE xxx; ALTER DATABASE xxx REMOVE FILE xxx_2

--.分离数据库
use master
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE
exec sp_detach_db @dbname = 'xxx'

--.附加数据库
use master
exec sp_attach_db @dbname = N'xxx', @filename1 = N'C:\opt\MSSQL\Data\xxx.mdf', @filename2 = N'C:\opt\MSSQL\Log\xxx_Log.ldf'

快照读：开启之后，数据库中的读操作（SELECT）将不再被写操作（INSERT/UPDATE/DELETE）阻塞，写操作也不会被读操作阻塞，可显著提升并发性能，并避免脏读，风险就是大幅增加 tempdb 的负载和tempdb的空间占用

--.查看当前设置
SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = 'xxx'

--.启用快照读
ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

延迟事务持久性：启用之后可减少日志I/O征用，提升磁盘io性能，以丢数据的代价提高数据导入性能，针对报表服务器的效果明显

--.查看当前设置
SELECT name, delayed_durability, delayed_durability_desc FROM sys.databases WHERE name = 'xxx'

--.允许模式：ALTER DATABASE xxx SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
--.强制模式：ALTER DATABASE xxx SET DELAYED_DURABILITY = FORCED WITH NO_WAIT

--.恢复默认设置，不管如何保持完全持久性
ALTER DATABASE xxx SET DELAYED_DURABILITY = DISABLED WITH NO_WAIT

数据表的管理

--.切换上下文
USE xxx;

--.有表删表-可重复执行
if object_id('tab001') is not null drop table tab001

--.有表删表-针对临时表
IF (OBJECT_ID(N'tempdb.dbo.#sys_objects') IS NOT NULL) 
DROP TABLE #sys_objects

--.创建新表-可重复执行
IF OBJECT_ID('tb_user') IS NOT NULL
DROP TABLE tb_user
GO
CREATE TABLE [dbo].[tb_user](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [user_id] [bigint] NOT NULL,
    [user_name] [nvarchar](10) NOT NULL,
    [create_time] [datetime] NOT NULL DEFAULT (getdate()),
    [update_time] [datetime] NOT NULL DEFAULT (getdate()),
    [is_deleted] [bit] NOT NULL DEFAULT ((0)),
    PRIMARY KEY CLUSTERED ([id] ASC))
ON [PRIMARY]
/*# Owner: 王伟; Manager: 王大朋 #*/
GO

--.添加表的注释
EXEC sys.sp_addextendedproperty 
  @name = N'MS_Description', 
  @value = N'用户表', 
  @level0type = N'SCHEMA', @level0name = 'dbo', 
  @level1type = N'TABLE',  @level1name = 'tb_user' 

--.添加字段的注释
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'用户id', 
  @level0type=N'SCHEMA', @level0name=N'dbo', 
  @level1type=N'TABLE' , @level1name=N'tb_user', 
  @level2type=N'COLUMN', @level2name=N'user_id' 

--.显示某个schema的所有表的注释
DECLARE @schema1 varchar(10) = 'dbo'
SELECT * FROM fn_listextendedproperty (NULL, 'schema', @schema1, 'table', default, NULL, NULL) 

--.显示某个表的所有字段的注释
DECLARE @schema2 varchar(10) = 'dbo'
DECLARE @table varchar(10) = 'tb_user'
SELECT * FROM fn_listextendedproperty (NULL, 'schema', @schema2, 'table', @table, 'column', default) 

--.查看当前库的所有表注释
SELECT 
   ROW_NUMBER() OVER (ORDER BY tb.name) AS id 
  ,tb.name AS table_name 
  ,CONVERT(nvarchar(100),ISNULL(ep.[value],'-')) AS table_desc 
FROM sys.tables tb 
LEFT JOIN sys.extended_properties ep ON tb.object_id = ep.major_id AND ep.minor_id = 0 

--.返回某表所有字段
DECLARE @table sysname
SET @table = 'tb_user'
SELECT STUFF((SELECT ',' + c.name FROM sys.tables t LEFT JOIN sys.columns c ON t.object_id=c.object_id AND t.name=@table ORDER BY c.column_id FOR XML PATH('')),1,1,'')

--.查看表的行数和大小
exec sp_spaceused 'tb_user'

--.查看表的索引信息
exec sp_helpindex 'tb_user'

--.修改表名
exec sp_rename 'tb_user','tb_user222'

--.互换表名
BEGIN TRAN
  exec sp_rename 'pata_var','pata_var_20180604'
  exec sp_rename 'pata_var_new','pata_var'
--COMMIT

--.其他
exec sp_help 'tb_user'                            --.查看表的详细信息
exec sp_depends 'tb_user'                        --.查看表的关联性
exec sp_changeobjectowner 'dbo.tb_user','dbo'   --.更改表的owner

与表有关的 dmv：

select * from sys.schemas
select * from sys.objects
select * from sys.tables
select * from sys.indexes
select * from sys.index_columns
select * from sys.columns
select * from sys.syscolumns
select * from sys.types
select * from sys.partitions

关于数据表的批量操作

关于数据表的批量操作：

--.批量清空特定库下的所有表
SELECT 'SELECT ''truncate table '+QUOTENAME(name)+'..''+QUOTENAME(name)+'''' FROM '+name+'.sys.tables ORDER BY name'
FROM sys.databases 
WHERE database_id > 4 and name not like 'xxx%'
ORDER BY name

字段的管理

--.新增字段,可以同时新增多个字段
alter table 表名 add 字段名 字段类型
alter table tb_user add col222 int, col333 varchar(6)

--.修改字段类型,不可以同时修改多个字段
ALTER TABLE 表名 ALTER COLUMN 字段名称 varchar(长度) COLLATE Chinese_PRC_CS_AI 是否为空
alter table tb_user alter column col222 bigint
alter table tb_user alter column col222 bigint, col333 varchar(8)  --.会报错

--.修改字段名称
exec sp_rename '表名.字段名称','新的字段名称','column'

--删除单个/多个字段,若字段有约束则先删约束
alter table 表名 drop CONSTRAINT 约束名
alter table tb_user drop column col222,col333

关于 identity 自增

--.测试表
use xxx;
if object_id('tab001') is not null drop table tab001
create table tab001(id int identity(1,1),name nvarchar(10))
insert into tab001(name) values ('刘备'),('关羽')

--.验证或重设table的自动增长初始值
DBCC CHECKIDENT ('tab001',NORESEED)       --.验证当前的最大值
DBCC CHECKIDENT ('tab001', RESEED, 1000)  --.重设自动增长初始值,下个值为 1000+1
insert into tab001(name) values ('曹操')
select * from tab001

--.查看某个自增表当前的最大值
SELECT IDENT_CURRENT('xxx.dbo.tab001')

--.设置 SET IDENTITY_INSERT 为 ON 则可以向目的表的自增字段赋值
SET IDENTITY_INSERT tab001 ON 
INSERT INTO tab001(id,name) VALUES(3,'张飞') 
SET IDENTITY_INSERT tab001 OFF 

--.利用 ROW_NUMBER 函数模拟id自增
SELECT name, type_desc FROM sys.sql_logins
SELECT id = ROW_NUMBER() OVER(ORDER BY (SELECT 1)), name, type_desc FROM sys.sql_logins

--.利用 @@IDENTITY 取模
if object_id('tab001') is not null drop table tab001
create table tab001(id int identity(1,1),isactive bit)
INSERT INTO tab001(isactive) VALUES(1) 
SELECT @@IDENTITY, @@IDENTITY % 128 + 1;

增删改查

--.利用 [] 包裹变量
SELECT QUOTENAME('abc'), '[abc]'

--.生成8位随机数
SELECT id = RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8)

--.YYYYMMDD
select convert(char(8),convert(datetime,'20120801')+7,112)

--.身份证最后1位X
if object_id('tab001') is not null drop table tab001
create table tab001(id int,name nvarchar(10),idcard char(18))
insert into tab001(id,name,idcard) values(1,'刘备','31011020260831401X')
select * from tab001 with(nolock) where len(idcard)=18 and SUBSTRING(idcard,18,1)='x' COLLATE Chinese_PRC_CI_AS  --.能搜到,CI不区分大小写
select * from tab001 with(nolock) where len(idcard)=18 and SUBSTRING(idcard,18,1)='x' COLLATE Chinese_PRC_CS_AS  --.搜不到,CS区分大小写

--.按照多个字段查看有重复的记录及重复次数
select distinct age,addr,count(*) as cnt 
from friends 
group by age,addr 
having count(*) > 0 
order by cnt desc 

--.删掉 leads_ 开头的备份表
if object_id('leads_20260831') is null create table leads_20260831(id int)
SELECT name, 'DROP TABLE '+QUOTENAME(name) AS drop_command 
FROM sys.tables 
WHERE name LIKE 'leads\_%20%' ESCAPE '\'
ORDER BY name 

--.insert可以全部默认
if object_id('tab001') is not null drop table tab001
create table tab001(id int identity, name nvarchar(10) default 'xxx')
insert into tab001 values (default)

--.创建表并批量插入
if object_id('tab001') is not null drop table tab001
create table tab001(id int,name nvarchar(10),sex nvarchar(2))
insert into tab001(id,name,sex) values(1,'刘备','M'),(2,'西施','F')

--.更新 case when
update tab001 set sex=(case sex when 'M' then '男' when 'F' then '女' else '' end) WHERE id < 10

--.更新 output
update tab001 set name = '关羽' 
output deleted.name,inserted.name 
where id = 1

利用 cte 删除重复数据

--.删除b和c完全重复(保留a最大)的数据
if object_id('tab001') is not null drop table tab001
create table tab001(a int, b int, c int, d int)
insert into tab001 values 
(1, 2, 3, 4),  --.要删除这行
(2, 2, 3, 5),
(3, 6, 7, 8),
(4, 6, 8, 8)
go
with cte as ( select *, row_number() over(partition by b,c order by a desc) rn from tab001 )
delete cte where rn > 1
go
select * from tab001

备份与还原

批量备份与还原数据库，迁移过程中建议先停掉自动备份任务

-- 创建账号 net user tongbu mgLg0Z_JHaqTwyf /add /Y /expires:never
-- 创建目录 mkdir c:\share\sqlbackup
-- 创建共享 net share sqlbackup=c:\share\sqlbackup /grant:tongbu,change /remark:"just for sqlserver bacup"
-- 验证共享 exec master..xp_cmdshell 'net use \\192.168.31.104\sqlbackup "mgLg0Z_JHaqTwyf" /user:192.168.31.104\tongbu'
-- 取消验证 exec master..xp_cmdshell 'net use * /d /y'
SELECT name 
-- SELECT 'BACKUP DATABASE '+QUOTENAME(name)+' TO DISK=''\\192.168.31.104\sqlbackup\'+name+'_'+convert(varchar(101),getdate(),112)+'.bak'' WITH STATS=1,COMPRESSION' AS sql 
-- SELECT 'BACKUP LOG '+QUOTENAME(name)+' TO DISK=''\\192.168.31.104\sqlbackup\'+name+'_'+convert(varchar(101),getdate(),112)+'.trn'' WITH STATS=1,COMPRESSION' AS sql
-- SELECT 'RESTORE DATABASE '+QUOTENAME(name)+' FROM DISK=''\\192.168.31.104\sqlbackup\'+name+'_'+convert(varchar(101),getdate(),112)+'.bak'' WITH STATS=1,NORECOVERY' AS sql 
-- SELECT 'RESTORE LOG '+QUOTENAME(name)+' FROM DISK=''\\192.168.31.104\sqlbackup\'+name+'_'+convert(varchar(101),getdate(),112)+'.trn'' WITH STATS=1,NORECOVERY' AS sql 
FROM master.sys.databases WITH(NOLOCK) WHERE state = 0 and database_id > 4 ORDER BY name

数据库的备份操作：

--.启用备份压缩特性
EXEC sys.sp_configure N'backup compression default', N'1'
RECONFIGURE WITH OVERRIDE

--.常见的备份参数
backup database xxx to disk = 'c:\opt\xxx.bak'                                            --.完整备份
backup database xxx to disk = 'c:\opt\xxx.bak' with differential                          --.差异备份
backup database xxx to disk = 'c:\opt\xxx.bak' with compression                           --.压缩备份
backup database xxx to disk = 'c:\opt\xxx.bak' with stats = 5                             --.报告进度
backup database xxx to disk = 'c:\opt\xxx.bak' with description = 'hello'                 --.添加描述
backup database xxx to disk = 'c:\opt\xxx.bak' with init,EXPIREDATE = '2026-12-31 23:59'  --.设定过期时间

--.删除3天前的备份文件
DECLARE @bakpath nvarchar(60) = 'c:\opt' 
DECLARE @olddate DATETIME = GETDATE()-3
EXECUTE master.dbo.xp_delete_file 0,@bakpath,N'bak',@olddate
EXECUTE master.dbo.xp_delete_file 0,@bakpath,N'trn',@olddate

--.平均分割备份文件，适用单块盘不够大但多块盘加一起够大
backup database xxx to disk='D:\xxx.1.bak', disk='E:\xxx.2.bak'
restore database xxx from disk='D:\xxx.1.bak', disk='E:\xxx.2.bak'

--.备份的日志直接丢弃,适用于空间严重不足
BACKUP LOG xxx TO DISK = N'NUL'

--.网络备份,适用于空间严重不足
exec master..xp_cmdshell 'net use \\192.168.31.104\sqlbackup "mgLg0Z_JHaqTwyf" /user:192.168.31.104\tongbu'
backup database xxx to disk = '\\192.168.31.104\sqlbackup\xxx.bak' with stats=1,compression

数据库的还原操作：

--.查看还原历史
select * from msdb..restorehistory order by restore_date desc  

--.验证备份集是否完整可用
restore verifyonly from disk = 'c:\xxx.bak'

--.返回由备份集内包含的数据库和日志文件列表组成的结果集
RESTORE FILELISTONLY FROM DISK = 'c:\xxx.bak'

--.返回备份集的头信息
RESTORE HEADERONLY FROM DISK= 'c:\xxx.bak' 

--.还原数据库,利用stopat精确恢复到指定时间点
restore database xxx from disk='c:\xxx.bak' with move 'xxx' to 'c:\xxx.mdf',move 'xxx_log' to 'c:\xxx_log.ldf',norecovery,replace
restore log xxx from disk='c:\xxx.trn' with recovery, stopat='2026-09-01 15:48:59.000'

--.应对由于备份损坏导致的还原错误
--.如果不设置continue_after_error，SQLServer只要遇到一个页面有问题，整个恢复动作都会停止，而数据丢失量取决于遇到的错误
restore database xxx2 from disk='c:\xxx.bak' with continue_after_error

查看正在备份与还原的进度百分比：

--.查看正在备份与还原的进度百分比
SELECT 
   DB_NAME(database_id) AS Exec_DB 
  ,[Individual Query] = 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) 
  ,percent_complete 
FROM SYS.DM_EXEC_REQUESTS qs (nolock) 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
WHERE command LIKE 'BACKUP%' OR command LIKE 'RESTORE%' 
ORDER BY 2 DESC

查看备份历史：

--.查看备份历史
select top 10 
  bs.backup_set_id,bs.database_name
 ,CHOOSE(CHARINDEX(bs.type,'DILPF'),'Database','Differential','Log','Partial','File') AS type
 ,bs.compressed_backup_size,bs.backup_size,bs.backup_start_date,bs.backup_finish_date 
 ,cast(bs.compressed_backup_size/1024/1024 as decimal(16,2)) as bak_file_size
 ,bf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bf on bs.media_set_id=bf.media_set_id
order by backup_set_id desc

--.查看备份历史
SELECT 
  sd.name AS database_name 
 ,sd.recovery_model_desc 
 ,ISNULL(CONVERT(varchar, bs.bdate, 120), '') AS last_backup_date 
 ,backup_type = CASE bs.type 
    WHEN 'D' THEN 'DataBase' 
    WHEN 'I' THEN 'Differential' 
    WHEN 'L' THEN 'Log' 
    WHEN 'F' THEN 'File or filegroup' 
    WHEN 'G' THEN 'Differential file' 
    WHEN 'P' THEN 'Partial' 
    WHEN 'Q' THEN 'Differential partial' 
    ELSE '' END 
FROM sys.databases AS sd 
LEFT JOIN (SELECT database_name,MAX(backup_finish_date) AS bdate,type 
  FROM msdb.dbo.backupset GROUP BY database_name,type) bs ON sd.name=bs.database_name 
WHERE sd.name <> 'tempdb' 
ORDER BY sd.name ASC, bs.bdate DESC

账号的管理

--.查看用户账号
SELECT name,type,type_desc,create_date,is_disabled FROM sys.server_principals WHERE type = 'S'
SELECT name,type,type_desc,create_date,is_disabled,is_policy_checked,is_expiration_checked FROM sys.sql_logins

--.创建用户账号
USE master; CREATE LOGIN user_pay_gateway WITH PASSWORD = 'H69f40S3_QGd7FPV',CHECK_POLICY=ON
USE xxx_pay_gateway; CREATE USER user_pay_gateway FOR LOGIN user_pay_gateway
USE xxx_pay_gateway; EXEC sp_addrolemember N'db_datareader', N'user_pay_gateway'
USE xxx_pay_gateway; EXEC sp_addrolemember N'db_datawriter', N'user_pay_gateway'

--.创建备用管理员账号
USE master; CREATE LOGIN dba_admin WITH PASSWORD = 'Admin_147'
USE master; ALTER SERVER ROLE sysadmin ADD MEMBER dba_admin

--.修改用户的密码及策略
ALTER LOGIN dba_admin WITH PASSWORD = 'Admin_147',CHECK_POLICY=ON,CHECK_EXPIRATION =ON

--.查看拥有sa权限+已启用的账号
SELECT sa.name,sa.status,sa.createdate,sa.sysadmin,sb.is_disabled,sb.default_database_name,sb.is_policy_checked,sb.is_expiration_checked
FROM sys.syslogins sa INNER JOIN sys.sql_logins sb ON sa.name = sb.name
WHERE sa.sysadmin = 1 AND sb.is_disabled = 0 

--.查看某个账号的上次密码修改时间
SELECT LOGINPROPERTY('sa', 'PasswordLastSetTime')

--.返回某个账号的hash密码
SELECT CAST(LOGINPROPERTY('sa','PasswordHash') AS varbinary(512)) 
SELECT password_hash FROM sys.sql_logins where name = 'sa'

--.返回md5值
print HashBytes('MD5','123456') 
print substring(sys.fn_sqlvarbasetostr(HashBytes('MD5','123456')),11,16)
print substring(sys.fn_sqlvarbasetostr(HashBytes('MD5','123456')),3,32)

--.查看某个账号的登录情况
exec sp_who 'dba_admin'

--.将sa禁用/启用/改名
ALTER LOGIN sa DISABLE 
ALTER LOGIN sa ENABLE 
ALTER LOGIN sa WITH NAME=[sa2]

--.删除 LOGIN
USE master; 
DROP LOGIN Sh_Login

--.删除 LOGIN 报错：The database principal owns a schema in the database, and cannot be dropped
USE [HJOneCoreCCB01]
GO
--.1.找到要删除的user拥有什么schema
DECLARE @user varchar(40) = 'HJOneCoreUser'
SELECT name FROM sys.schemas WHERE principal_id = USER_ID(@user)
--.2.将这个schema授权给dbo，再删掉user
ALTER AUTHORIZATION ON SCHEMA::SignalR TO dbo 
GO
DROP USER HJOneCoreUser
GO

针对 windows 账号的操作：

--.创建windows账号
CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

--.添加.\administrator账号并添加sa权限
USE master
DECLARE @loginname sysname = @@SERVERNAME + '\Administrator'
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = @loginname)
EXECUTE ('CREATE LOGIN ['+@loginname+'] FROM WINDOWS WITH DEFAULT_DATABASE = [master]') 
EXECUTE ('ALTER SERVER ROLE [sysadmin] ADD MEMBER ['+@loginname+']') 

--.删除Windows Administrator账号
DECLARE @servername VARCHAR(20) = @@SERVERNAME
DECLARE @loginname VARCHAR(200) 
SET @loginname = @servername + '\Administrator'
IF EXISTS (SELECT name FROM sys.server_principals WHERE name = @loginname)
EXECUTE ('DROP LOGIN ['+@loginname+']')

GRANT 与 REVOKE：

--.授予对表的DDL操作
GRANT CREATE TABLE TO Sh_User

--.授予对表的DML操作
GRANT SELECT,INSERT,UPDATE,DELETE TO Sh_User            --.所有表：ALL=SELECT,INSERT,UPDATE,DELETE
GRANT SELECT ON dbo.tb_user TO Sh_User                    --.指定表
GRANT SELECT ON dbo.tb_user(email) TO Sh_User            --.指定表的指定字段
GRANT ALL ON dbo.tb_user TO Sh_User WITH GRANT OPTION    --.可授权

--.对Store Procedure的DDL操作
GRANT EXECUTE ON dbo.usp_Update_Employee TO Sh_Login

--.授权对数据库的操作
GRANT CREATE DATABASE,CREATE TABLE TO Mary,John

--.拒绝建表权限
DENY CREATE TABLE TO Sh_User

--.拒绝修改Table、SP结构：alter优先级高于create,若deny alter则无法create table.
DENY ALTER TO [Sh_User] 

--.回收授权
REVOKE SELECT ON OBJECT::dbo.tb_user FROM Sh_Login

--.针对特定表授予只读权限(只给SELECT,不给EXECUTE)
USE xxx
GO
IF NOT EXISTS(SELECT name FROM sys.sysusers WHERE name = 'test_bigdata') 
BEGIN 
  CREATE USER test_bigdata FOR LOGIN test_bigdata 
END 
GRANT SELECT ON dbo.DroolsBu TO test_bigdata

创建并配置 Profiler 账号的权限

--.新增用户并开放Profiler权限
USE master
GO

--.若要运行Profiler必须拥有ALTER TRACE权限
--.拥有SHOWPLAN、ALTER TRACE或VIEW SERVER STATE权限的用户可以对显示计划输出中捕获的查询进行查看，可能包含敏感信息，例如密码
CREATE LOGIN user_app_profiler WITH PASSWORD='XfIQMVLN_pURcF9t'
GRANT ALTER TRACE TO user_app_profiler
GRANT VIEW SERVER STATE TO user_app_profiler
GO

--.在目标数据库创建Trace用户帐户并授予其SHOWPLAN的权限.
USE xxx
GO
CREATE USER user_app_profiler FROM LOGIN user_app_profiler
GRANT SHOWPLAN TO user_app_profiler
GO

针对 role 角色的操作：

--.1.创建Login
USE master
CREATE LOGIN Sh_Login WITH PASSWORD = 'Admin_147',CHECK_POLICY=OFF

--.2.创建User和加权限
USE xxx; 
GO 
CREATE USER Sh_User FOR LOGIN Sh_Login
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.tb_user TO Sh_User

--.3.创建Role角色
USE xxx;
GO 
CREATE ROLE sh_rw_role 
GRANT SELECT,UPDATE,INSERT,DELETE,EXECUTE TO sh_rw_role 
CREATE ROLE sh_reader_role 
GRANT SELECT,EXECUTE TO sh_reader_role 
CREATE ROLE sh_readonly_role 
GRANT SELECT TO sh_readonly_role 

--.4.创建User和Schema
USE xxx; 
GO 
EXEC sp_addrolemember @rolename = 'sh_rw_role', @membername = 'Sh_User' 
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'Sh_User'
GO 
CREATE SCHEMA Sh_Schema AUTHORIZATION Sh_User
ALTER USER Sh_User WITH DEFAULT_SCHEMA = Sh_Schema
GO 

--.5.清除架构/用户/登录名等
USE xxx; 
DROP SCHEMA Sh_Schema
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [db_owner] 
DROP USER Sh_User
DROP ROLE sh_rw_role 
DROP ROLE sh_reader_role 
DROP ROLE sh_readonly_role 
GO 
USE master; 
DROP LOGIN Sh_Login
GO

关于账号的批量操作：

--.批量禁用/启用
SELECT name,'ALTER LOGIN ['+name+'] DISABLE' as disable_command,'ALTER LOGIN ['+name+'] ENABLE' as enable_command 
FROM sys.sql_logins WHERE is_disabled = 0 and name <> 'sa'

--.所有帐号启用密码策略
SELECT name,'ALTER LOGIN '+name+' WITH CHECK_EXPIRATION = ON, CHECK_POLICY = ON' AS command 
FROM master.sys.sql_logins WITH(NOLOCK) 
WHERE is_disabled = 0 
AND (is_policy_checked = 0 OR is_expiration_checked = 0)   --强制实施密码策略+强制密码过期
ORDER BY name

关于账号的高级用法

利用 PWDCOMPARE 碰撞检测是否存在弱口令

SELECT name,type_desc,is_disabled,create_date,is_policy_checked,is_expiration_checked,password_hash
FROM sys.sql_logins sa
WHERE PWDCOMPARE('', password_hash) = 1        --.查询密码空的账号
OR PWDCOMPARE('password', password_hash) = 1   --.查询密码为password的账号
OR PWDCOMPARE('123456', password_hash) = 1     --.查询密码为123456的账号
OR PWDCOMPARE('Aa123456', password_hash) = 1   --.查询密码为Aa123456的账号

创建用户之后，如何批量给多个db添加只读权限

--.方式1.批量添加只读权限并排除DB
DECLARE @readonly varchar(100) = 'dba_bigdata_readonly'
SELECT  
  [create_user] = 'USE ['+name+']; CREATE USER ['+@readonly+'] FOR LOGIN ['+@readonly+']',
  [add_role] = 'USE ['+name+']; EXEC sp_addrolemember N''db_datareader'', N'''+@readonly+''''
FROM sys.databases 
WHERE database_id> 4 
ORDER BY name 

--.方式2.批量添加权限并排除DB
USE master
GO
CREATE LOGIN dba_bigdata_readonly WITH PASSWORD = 'Admin_147' ,CHECK_POLICY = OFF
GO
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') begin USE [?]; CREATE USER [dba_bigdata_readonly] FOR LOGIN [dba_bigdata_readonly] end'
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') begin USE [?]; EXEC sp_addrolemember N''db_datareader'', N''dba_bigdata_readonly'' end'

通过 EXECUTE AS LOGIN 借以某个用户的身份去执行某些操作，直到 REVERT 操作才切回 caller 的身份

--.通过 EXECUTE AS LOGIN 借以某个用户的身份去执行某些操作，直到 REVERT 操作才切回 caller 的身份
EXECUTE AS LOGIN = 'dba_admin';
SELECT SUSER_NAME(), USER_NAME();
REVERT;
SELECT SUSER_NAME(), USER_NAME();

会话与连接数

--.查看SQL进程
exec sp_who2

--.当前连接使用哪个用户登陆 
select SUSER_SNAME()

--.统计.总的连接数
SELECT COUNT(1) FROM sys.dm_exec_connections WITH(NOLOCK) WHERE session_id>50

--.统计.每个账号的连接次数
SELECT login_name, COUNT(*) ct
FROM sys.dm_exec_sessions WITH(NOLOCK) WHERE session_id>50
GROUP BY login_name
ORDER BY ct DESC

--.统计.每个账号、每个ip的连接次数
SELECT s.login_name, c.client_net_address, s.host_name, COUNT(1) cnt
FROM sys.dm_exec_requests r WITH(NOLOCK)
RIGHT OUTER JOIN sys.dm_exec_sessions s WITH(NOLOCK) ON r.session_id = s.session_id
RIGHT OUTER JOIN sys.dm_exec_connections c WITH(NOLOCK) ON s.session_id = c.session_id
WHERE s.session_id > 50
--and login_name='AppUser_MobileScore'
GROUP BY login_name, client_net_address, host_name
ORDER BY cnt DESC

--.统计.每个db、每个账号的连接次数
SELECT DB_NAME(dbid) AS database_name, loginame AS login_name, COUNT(dbid) AS conn_cnt 
FROM sys.sysprocesses WITH(NOLOCK) 
WHERE dbid > 0 
GROUP BY dbid,loginame 
ORDER BY DB_NAME(dbid)

--.杀掉僵尸连接
select 'kill ' + CAST(s.spid AS NVARCHAR(100)) AS kill_spid, s.spid, s.kpid, s.hostprocess, 
  s.status AS s_status , es.status AS es_status, s.blocked, s.open_tran, es.transaction_isolation_level AS L,
  s.waittype, s.waitresource , s.waittime, 
  DB_NAME(s.dbid) as dbname, s.loginame, s.hostname, s.program_name, 
  s.login_time AS s_login_time, es.login_time AS es_login_time, s.last_batch, 
  es.last_request_start_time, es.last_request_end_time,
  s.cmd, es.row_count, qt.text ,
  es.memory_usage, es.cpu_time AS es_cpu_time, s.cpu AS sum_cpu, es.logical_reads, s.physical_io sum_physical_io,s.lastwaittype,
  es.last_successful_logon, es.last_unsuccessful_logon, es.unsuccessful_logons
from sys.sysprocesses as s
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
right outer join sys.dm_exec_sessions es on s.spid=es.session_id
where spid>50
--and es.status='sleeping'
order by s.login_time desc

--.杀某个IP的所有连接
SELECT client_net_address, 'KILL ' + CONVERT(VARCHAR, session_id) AS killcmd 
FROM SYS.DM_EXEC_CONNECTIONS WITH(NOLOCK) 
WHERE client_net_address ='172.17.2.59' 
ORDER BY session_id 

--.杀某个DB的所有连接
SELECT 'KILL ' + CONVERT(VARCHAR, SPID) AS SPID,DB_NAME(dbid) as dbname, hostname, status, program_name 
FROM master..sysprocesses 
WHERE spid > 50 AND spid <> @@SPID AND dbid <> 0 
AND DB_NAME(dbid) = 'xxx' 
ORDER BY dbid 

--.批量杀掉指定SPID进程
-- KILL 58; KILL 59;

事务

--.如果操作失误,利用事务可快速回滚
begin tran
  update BankAccounts set BankCityId = 261, RealName = '马云辉' where userid = 58464
--commit
--rollback

存储过程与函数

以下是创建存储过程的一个示例：

use xxx
go

--.判断是否存在
IF OBJECT_ID('usp_demo','P') IS NOT NULL 
DROP PROC usp_demo 
GO 

--.创建存储过程
create proc usp_demo
as
select name from sys.databases where database_id < 5

关于存储过程与函数的常用操作：

--.查看当前db的所有存储过程
select db_name() as database_name,name,type,type_Desc,create_date,modify_date from sys.objects where type='P' order by name

--.执行存储过程
use xxx; exec usp_demo

--.生成脚本
sp_helptext 'usp_demo'
SELECT object_name(object_id) as name, definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('usp_demo')

--.查看当前db各个存储过程的性能统计信息
SELECT 
  db_name(sd.database_id) as database_name,so.name,so.type,sd.cached_time,sd.last_execution_time
 ,sd.execution_count,sd.total_worker_time,sd.total_logical_reads,sd.total_logical_writes
FROM sys.dm_exec_procedure_stats sd 
INNER JOIN sys.objects so ON sd.object_id = so.object_id 
WHERE db_name(sd.database_id) = DB_NAME()

--.建议添加描述,示例如下
/*****************************************************************************************************
Funtion : 对碎片比超过30%的按碎片大小及使用次数倒序依次联机重建
Input   : @end_time hh:mm:ss     比如 上午6点请用06:00:00, 不要用 6:00:00
Author  : yuxiukai  2009/07/16   新增 编写草稿.
Modify  : yuxiukai  2016/12/12   新增 schema_name,避免非dbo表报错.
          yuxiukai  2016/12/13   新增 10W行限制,只对大表做索引重建.
Sample : exec usp_defragment_indexes @end_time='06:00:00'
*****************************************************************************************************/

索引与调优

索引的创建与删除

--.创建聚集索引
CREATE CLUSTERED INDEX 索引名 ON 表名(字段名) WITH(ONLINE=ON,FILLFACTOR=90)

--.创建非聚集索引
CREATE NONCLUSTERED INDEX 索引名 ON 表名(字段名) WITH(ONLINE=ON,FILLFACTOR=90)

--.组合索引
CREATE INDEX 索引名 ON 表名(字段1,字段2) WITH(ONLINE=ON)

--.覆盖索引
CREATE INDEX idx_users_userid@name ON users(userid) INCLUDE(name) WITH(ONLINE=ON)

--.条件索引
CREATE INDEX IX_Trade_UpdateTime ON Trade(UpdateTime) WHERE UpdateTime >= '2019-03-01 00:00:00' WITH(ONLINE=ON)

--.重建索引
ALTER INDEX 索引名 ON 表名 REBUILD WITH(ONLINE=ON,MAXDOP=4);

--.删除索引：建议先disable，再drop，以避免直接删除而造成block；聚集索引支持with(onlie=on),而非聚集不支持onlie
ALTER INDEX 索引名 ON 表名 DISABLE 
DROP INDEX 聚集索引 ON 表名 WITH(ONLINE=ON)
DROP INDEX 非聚集索引 ON 表名

关于索引的批量管理：

--.查询当前库的所有索引
SELECT 
 db_name() AS database_name, so.name AS table_name,ss.name AS index_name,ss.id as index_id,so.type_desc,so.create_date 
FROM sys.sysindexes ss
LEFT JOIN sys.objects so ON ss.id=so.object_id
WHERE ss.indid>=1 AND so.type='U'

--.找出聚集索引所在列
DECLARE @Table VARCHAR(20)
SET @table='IOSInfo'
SELECT so.name AS TableName,ss.name AS IndexName,ss.id,so.type_desc,so.create_date,si.column_id,sc.name
FROM sys.objects so
INNER JOIN sys.sysindexes ss ON ss.id=so.object_id
INNER JOIN sys.index_columns si ON si.object_id=ss.id AND si.index_id=ss.indid
INNER JOIN syscolumns sc ON sc.colid=si.column_id AND sc.id=ss.id
WHERE so.type='U' AND ss.indid=1 AND so.name=@table

主键约束与普通约束

关于主键约束与普通约束：

--.约束的创建与删除
ALTER TABLE 表名 ADD CONSTRAINT 主键名称 PRIMARY KEY CLUSTERED (主键字段) WITH(ONLINE = ON)
ALTER TABLE 表名 DROP CONSTRAINT 主键名称

--.唯一索引约束
CREATE UNIQUE NONCLUSTERED INDEX 唯一索引约束名称 ON 表名(字段) WITH(ONLINE = ON,FILLFACTOR=90)
ALTER TABLE 表名 ADD CONSTRAINT 唯一索引约束名称 UNIQUE nonclustered(email) 

--.普通约束
if object_id('tab001') is not null drop table tab001
create table tab001(id int,name nvarchar(20),sex varchar(1)) 
ALTER TABLE tab001 ADD CONSTRAINT ck_sex check(sex='M' or sex='F' or sex='')
insert into tab001(id,name,sex) values(1,'刘备','M'),(2,'西施','F')   --.插入成功
insert into tab001(id,name,sex) values(3,'张飞','X')                  --.插入失败
ALTER TABLE tab001 DROP CONSTRAINT ck_sex

--.默认约束
alter table tab001 ADD CONSTRAINT DF_tab001_sex DEFAULT 'M' FOR sex

--.添加字段的时候,隐式/显式指定约束名称
alter table tab001 add col5 varchar(6) not null default('b')
alter table tab001 add col6 varchar(6) not null CONSTRAINT DF_tab001_col6 DEFAULT 'c'

--.查看表的约束信息
exec sp_helpconstraint 'tab001'

--.查找当前db下所有的约束
SELECT tbl.name AS table_name,cstr.name AS default_name,clmns.name AS column_name,cstr.definition AS default_value
FROM sys.tables AS tbl 
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id 
INNER JOIN sys.default_constraints AS cstr ON cstr.object_id=clmns.default_object_id 
ORDER BY tbl.name ASC

统计计息与索引碎片

查看当前db超过n天没有更新的统计信息

--.查看当前db超过n天没有更新的统计信息 
SELECT 
   db_name() AS database_name 
  ,obj.name AS table_name 
  ,sts.name AS ststs_name 
  ,sts.stats_id 
  ,sts.auto_created 
  ,sts.user_created 
  ,STATS_DATE(sts.object_id, sts.stats_id) AS statistics_update_date 
  ,'UPDATE STATISTICS '+QUOTENAME(sch .name)+'.'+QUOTENAME(obj.name)+' '+QUOTENAME(sts.name) AS stats_name 
FROM sys.objects obj with(nolock) 
INNER JOIN sys.schemas sch with(nolock) ON obj.schema_id = sch .schema_id 
INNER JOIN sys.stats sts with(nolock) ON obj.object_id = sts.object_id 
WHERE obj.type = 'U' 
--AND obj.name = 'tab001' 
AND STATS_DATE(sts.object_id, sts.stats_id) < GETDATE()-3
ORDER BY obj.name

更新统计信息：

--.更新表的所有/指定索引的统计信息(更新索引比重建索引快多了)
UPDATE STATISTICS dbo.tab001
UPDATE STATISTICS dbo.tab001 WITH ALL
UPDATE STATISTICS dbo.tab001(idx_tab001_name)

--.通过使用 20% 抽样更新某表某列的统计信息(默认根据需要采用抽样)
--.对于基于默认抽样的查询计划并非最佳的特殊情况,SAMPLE非常有用
UPDATE STATISTICS dbo.tab001 WITH SAMPLE 20 PERCENT
UPDATE STATISTICS dbo.tab001(idx_tab001_name) WITH SAMPLE 20 PERCENT

--.更新表中某字段的统计信息，强制对表中的所有行进行完全扫描，并关闭统计信息的自动统计信息功能
UPDATE STATISTICS dbo.tab001(name) WITH FULLSCAN, NORECOMPUTE

--.对当前数据库中所有用户定义表和内部表运行 UPDATE STATISTICS
sp_updatestats

重建索引：

--.所有DB的所有索引的碎片信息,这个执行特别慢...
SELECT * FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL , 'LIMITED');

--.重建索引
--.实测：1亿行、数据289G+索引49G,先重建PK(碎片83%,未指定maxdop)耗时01:32:45,日志增长203G;再重建非聚集索引(类型varchar(255),碎片99%)耗时03:36,日志无增长
ALTER INDEX IX_ip2location_city ON ip2location REBUILD WITH (ONLINE = ON,MAXDOP = 4) 

--.SQL2014新功能, 可以只重建特定的分区
ALTER INDEX MyIndex ON MyTable
REBUILD PARTITION = 3 WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5, ABORT_AFTER_WAIT = BLOCKERS)))

外键

普通外键：

USE xxx
GO

--.DROP TABLE: 先删外键表(TableB)，再删主键表(TableA)
if object_id('TableA') is not null drop table TableA   --.Step 2
if object_id('TableB') is not null drop table TableB   --.Step 1
CREATE TABLE TableA (AID INT NOT NULL, AName NVARCHAR(20))
CREATE TABLE TableB (BID INT NOT NULL, BName NVARCHAR(20), AID INT NOT NULL)

--.ADD KEY: 先新增主键约束，再新增外键约束
ALTER TABLE TableA WITH NOCHECK ADD CONSTRAINT PK_TableA_AID PRIMARY KEY CLUSTERED (AID)      --.Step 1
ALTER TABLE TableB WITH NOCHECK ADD CONSTRAINT PK_TableB_ID PRIMARY KEY CLUSTERED (BID, AID)  --.Step 1
ALTER TABLE TableB ADD CONSTRAINT FK_TableB_TableA FOREIGN KEY (AID) REFERENCES TableA (AID)  --.Step 2

--.INSERT DATA: 先插入主键表(TableA)资料，再插入外键表(TableB)资料
INSERT INTO TableA(AID,AName) VALUES(1,'A')          --.Step 1
INSERT INTO TableA(AID,AName) VALUES(2,'B')          --.Step 1
INSERT INTO TableB(BID,BName,AID) VALUES(21,'AA',1)  --.Step 2
INSERT INTO TableB(BID,BName,AID) VALUES(22,'BB',2)  --.Step 2

--.DELETE DATA: 先删除外键表(TableB)资料，再删除主键表(TableA)资料
DELETE FROM TableA WHERE AID=1  --.Step 2
DELETE FROM TableB WHERE AID=1  --.Step 1

--.CHECK DATA: 两张表都只剩下 AID=2
SELECT * FROM TableA
SELECT * FROM TableB

--.DELETE KEY: 先删除外键约束，再删除主键约束
ALTER TABLE TableA DROP CONSTRAINT PK_TableA_AID WITH (ONLINE = ON)  --.Step 2
ALTER TABLE TableB DROP CONSTRAINT PK_TableB_ID WITH (ONLINE = ON)   --.Step 2
ALTER TABLE TableB DROP CONSTRAINT FK_TableB_TableA                  --.Step 1

级联外键：ON DELETE CASCADE

USE xxx
GO

--.DROP TABLE: 先删外键表(TableB)，再删主键表(TableA)
if object_id('TableA') is not null drop table TableA   --.Step 2
if object_id('TableB') is not null drop table TableB   --.Step 1
CREATE TABLE TableA (AID INT NOT NULL, AName NVARCHAR(20))
CREATE TABLE TableB (BID INT NOT NULL, BName NVARCHAR(20), AID INT NOT NULL)

--.ADD KEY: 先新增主键约束，再新增外键约束
ALTER TABLE TableA WITH NOCHECK ADD CONSTRAINT PK_TableA_AID PRIMARY KEY CLUSTERED (AID)  --.Step 1
ALTER TABLE TableB WITH NOCHECK ADD CONSTRAINT PK_TableB_ID PRIMARY KEY CLUSTERED (BID, AID)  --.Step 1
ALTER TABLE TableB ADD CONSTRAINT FK_TableB_TableA FOREIGN KEY (AID) REFERENCES TableA (AID) ON DELETE CASCADE  --.Step 2

--.INSERT DATA: 先插入主键表(TableA)资料，再插入外键表资料
INSERT INTO TableA(AID,AName) VALUES(1,'A')  --.Step 1
INSERT INTO TableA(AID,AName) VALUES(2,'B')  --.Step 1
INSERT INTO TableB(BID,BName,AID) VALUES(21,'AA',1)  --.Step 2
INSERT INTO TableB(BID,BName,AID) VALUES(22,'BB',2)  --.Step 2

--.DELETE DATA: 直接删除主键表(TableA)资料,外键表(TableB)会一起删除
DELETE FROM TableA WHERE AID=1

--.DELETE DATA: 如果删除外键表(TableB)资料,则主键表(TableA)资料会保留
DELETE FROM TableB WHERE AID=2

--.CHECK DATA: 只有 主键表(TableA) 只剩下 AID=2
SELECT * FROM TableA
SELECT * FROM TableB

--.DELETE KEY: 先删除外键约束，再删除主键约束
ALTER TABLE TableA DROP CONSTRAINT PK_TableA_AID WITH (ONLINE = ON)  --.Step 2
ALTER TABLE TableB DROP CONSTRAINT PK_TableB_ID WITH (ONLINE = ON)   --.Step 2
ALTER TABLE TableB DROP CONSTRAINT FK_TableB_TableA                  --.Step 1

其他

Agent 作业

--.查看job是否包含特定内容
SELECT b.name as job_name,case when b.enabled = 1 then 'Enable' else 'Disable' end as job_is_enable,b.description,a.step_name,a.command
FROM msdb.dbo.sysjobsteps a INNER JOIN msdb.dbo.sysjobs b ON a.job_id = b.job_id 
WHERE a.command LIKE '%sp_who2%' 

--.查看所有job和每步具体执行的命令
select a.name,a.enabled,a.description,b.step_id,b.step_name,b.command,b.database_name 
from msdb..sysjobs a 
left join msdb..sysjobsteps b on a.job_id=b.job_id 
where a.enabled = 1
order by a.name

--.查看所有已启用的job
select name,job_id,enabled,owner_sid,date_created from msdb.dbo.sysjobs where enabled = 1 order by name

--.更改job所有者为sa
DECLARE @jobname nvarchar(100) = 'job_demo'
EXEC msdb.dbo.sp_update_job @job_name = @jobname, @owner_login_name = N'SA'

--.禁用/启用job
EXEC msdb..sp_update_job @job_name = N'job_demo', @enabled = 0
EXEC msdb..sp_update_job @job_name = N'job_demo', @enabled = 1

--.执行job
EXEC msdb..sp_start_job @job_name ='job_demo', @step_name='step_demo'

--.查看最近7日已启用作业的执行记录
select 
   a.name 
  ,a.enabled 
  ,b.run_date 
  ,STUFF(STUFF(RIGHT(REPLICATE('0',6) + CAST(b.run_time as varchar(6)),6),3,0,':'),6,0,':') as run_time 
  ,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0',8) + CAST(b.run_duration as varchar(8)),8),3,0,':'),6,0,':'),9,0,':') as run_duration 
  ,b.step_name 
  ,b.message 
  ,case b.run_status when 0 then '失败' when 1 then '成功' when 2 then '重试' when 3 then '已取消' when 4 then '正在进行' end as run_status 
from msdb.dbo.sysjobs a 
left join msdb.dbo.sysjobhistory b on a.job_id=b.job_id 
where b.run_date>convert(char(8),getdate()-7,112) and b.step_id=0 and a.enabled=1 
order by a.name asc,b.step_id asc,b.run_date desc 

--.设置JOB历史记录最大行数
execute msdb.dbo.sp_set_sqlagent_properties 
  @jobhistory_max_rows=10000       --.限制全局作业最多保留的历史记录行数,默认1000行
 ,@jobhistory_max_rows_per_job=20  --.限制单个作业最多保留的历史记录行数,默认100行

--.设置JOB历史记录最大行数(查看是否生效)
exec msdb.dbo.sp_get_sqlagent_properties

--.删除早于指定日期的备份还原记录,注意仅限备份还原任务
EXEC msdb..sp_delete_backuphistory '2010-09-02 10:46:39'

--.删除指定作业、指定时间以前的历史执行记录
EXEC msdb.dbo.sp_purge_jobhistory @job_name='job_demo', @oldest_date = '2026-09-01 10:08:00'

--.删除所有作业、指定时间以前的历史执行记录
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = '2026-09-01 10:10:00'

--.删除job
-- EXEC msdb.dbo.sp_delete_job @job_name = N'job_demo', @delete_unused_schedule=1

关于 job 的批量操作：

--.批量启用/禁用/删除作业
SELECT 'EXEC msdb.dbo.sp_update_job @job_name=N'''+name+''', @enabled=1'     --启用
-- SELECT 'EXEC msdb.dbo.sp_update_job @job_name=N'''+name+''', @enabled=0'  --禁用
-- SELECT 'EXEC msdb.dbo.sp_delete_job @job_name=N'''+name+''', @delete_unused_schedule=1'  --.删除
FROM msdb.dbo.sysjobs
WHERE name <> 'syspolicy_purge_history'

读取日志

特性	sp_readerrorlog	xp_readerrorlog
本质	系统存储过程，是 xp_readerrorlog 的一个便捷封装	扩展存储过程，是实现读取日志功能的核心底层程序
参数数量	4个，对应 xp_readerrorlog 的前四个参数	7个，比 sp_readerrorlog 多了时间范围和排序功能
参数功能	1. 日志文件编号 2. 日志类型 (1: SQLServer, 2: SQLServer Agent) 3. 搜索字符串1 4. 搜索字符串2	在 sp_readerrorlog 的4个参数基础上，增加了： 5. LogDate开始时间 6. LogDate结束时间 7. 结果排序 (按LogDate排序ASC/DESC)
推荐度	语法简单，日常使用更推荐	高级场景使用，比如按时间筛选或自定义排序结果

--.查看最近x天的Error日志
DECLARE @start_time datetime 
SET @start_time = GETDATE()-30 
EXEC sys.xp_readerrorlog 0,1,N'Error',Null,@start_time,NULL,'DESC'

--.查看最近x天的登录失败信息
DECLARE @start_time datetime 
SET @start_time = GETDATE()-30
DECLARE @errorlog TABLE(logdate datetime,info varchar(20),text nvarchar(max))
INSERT INTO @errorlog EXEC sys.xp_readerrorlog 0,1,Null,Null,@start_time,NULL,'DESC'
SELECT * FROM @errorlog WHERE info = 'Logon' AND text LIKE '%failed%' ORDER BY logdate DESC

--.通过 xp_readerrorlog 查看从 2026-8-30 到 2026-8-31 之间、包含关键词 BACKUP 的日志，并按时间降序排列
EXEC master.sys.xp_readerrorlog 0, 1, N'BACKUP', null, '20260830', '20260831', 'DESC';

--.通过 sp_readerrorlog 查看最近包含 BACKUP 的记录
EXEC sp_readerrorlog 0, 1, 'BACKUP';

--.通过 sp_readerrorlog 查看哪些信息
exec sys.sp_readerrorlog 0, 1, 'Manufacturer'    --.机器型号
exec sys.sp_readerrorlog 0, 1, 'RAM'             --.内存大小

--.查看SQL Server Log路径
DECLARE @param nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @param OUTPUT
SELECT @param+'\Log'

--.删除最老的ErrorLog,并循环错误日志扩展编号
-- exec master.sys.sp_cycle_errorlog

针对最近30天的登录失败的分析

DECLARE @start_time datetime 
SET @start_time = GETDATE()-30
DECLARE @errorlog TABLE(logdate datetime,info varchar(20),text nvarchar(max))
INSERT INTO @errorlog EXEC sys.xp_readerrorlog 0,1,Null,Null,@start_time,NULL,'DESC'
SELECT * FROM @errorlog WHERE info = 'Logon' AND text LIKE '%failed%' ORDER BY logdate DESC

SELECT 
   SUBSTRING(text,charindex('user',text)+6,charindex('原因',text)-charindex('user',text)-9) as login_name
  ,SUBSTRING(text,charindex('数据库',text)+4,charindex('客户端',text)-charindex('数据库',text)-8) as database_name
  ,SUBSTRING(text,charindex('客户端',text)+5,charindex(']',text)-charindex('客户端',text)-5) as ip_address
  ,count(1) as times 
FROM @errorlog 
WHERE info = 'Logon' AND text LIKE '%failed%' 
GROUP BY 
   SUBSTRING(text,charindex('user',text)+6,charindex('原因',text)-charindex('user',text)-9) 
  ,SUBSTRING(text,charindex('数据库',text)+4,charindex('客户端',text)-charindex('数据库',text)-8) 
  ,SUBSTRING(text,charindex('客户端',text)+5,charindex(']',text)-charindex('客户端',text)-5) 
ORDER BY times DESC

利用 RAISERROR 将特定的错误号、严重度和状态（参考select * from sys.messages）写入 SQL Server Log 或返回到应用程序中

--.举例1
DECLARE @ErrorLog NVARCHAR(1024) 
SET @ErrorLog = 'Failed: [提示] 最近7天bak库无备份记录,请核实. [当前时间] '+CONVERT(VARCHAR(30),GETDATE(),121) 
RAISERROR (@ErrorLog,1,1) WITH LOG; 
RETURN --建议直接返回,不再执行下面的

--.举例2
IF OBJECT_ID('CommandLog') IS NULL AND OBJECT_ID('PK_CommandLog') IS NULL
BEGIN 
  DECLARE @ErrorMessage VARCHAR(100) 
  SET @ErrorMessage = 'The table does not supported.' 
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT 
END

@@ 全局变量

--.常用的@@变量
SELECT 
  @@SPID AS CurrentSPID
 ,@@LOCK_TIMEOUT AS CurrentLockTimeout
 ,@@LANGUAGE AS CurrentLanguage
 ,SUSER_SNAME()   -- 连接的时候使用的哪个SQL登陆用户
 ,System_User
 ,HOST_NAME()     -- 客户端的机器名
 ,APP_NAME()      -- 是从查询分析器，还是应用程序来执行的
 ,DB_NAME()
 ,USER

--.更多全局变量
SELECT 
  @@CONNECTIONS         --.返回自上次启动以来连接或试图连接的次数
 ,@@CURSOR_ROWS      --.返回连接上最后打开的游标中当前存在的合格行的数量(返回被打开的游标中还未被读取的有效数据行的行数)
 ,@@DATEFIRST          --.返回每周第一天的数字
 ,@@ERROR              --.返回最后执行的SQL 语句的错误代码
 ,@@FETCH_STATUS      --.返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态
 ,@@IDENTITY          --.返回最后插入的标识值
 ,@@LANGID              --.返回当前所使用语言的本地语言标识符(ID)
 ,@@LANGUAGE          --.返回当前使用的语言名
 ,@@LOCK_TIMEOUT      --.返回当前会话的当前锁超时设置，单位为毫秒
 ,@@PROCID              --.返回当前过程的存储过程标识符 (ID) 
 ,@@ROWCOUNT          --.返回受上一语句影响的行数
 ,@@SERVERNAME          --.返回运行 的本地服务器名称
 ,@@SPID              --.返回当前用户进程的服务器进程标识符 (ID)
 ,@@TRANCOUNT          --.返回当前连接的活动事务数
 ,@@VERSION          --.返回当前安装的日期、版本和处理器类型
 ,@@CPU_BUSY         --.返回自SQL Server 最近一次启动以来CPU 的工作时间其单位为毫秒
 ,@@DATEFIRST         --.返回使用SET DATEFIRST 命令而被赋值的DATAFIRST 参数值SET DATEFIRST，命令用来指定每周的第一天是星期几
 ,@@DBTS             --.返回当前数据库的时间戳值必须保证数据库中时间戳的值是惟一的
 ,@@ERROR             --.返回执行Transact-SQL 语句的错误代码
 ,@@FETCH_STATUS     --.返回上一次FETCH 语句的状态值
 ,@@IDLE             --.返回自SQL Server 最近一次启动以来CPU 处于空闭状态的时间长短单位为毫秒
 ,@@IO_BUSY             --.返回自SQL Server 最近一次启动以来CPU 执行输入输出操作所花费的时间其单位为毫秒
 ,@@LANGID             --.返回当前所使用的语言ID 值
 ,@@LANGUAGE         --.返回当前使用的语言名称
 ,@@LOCK_TIMEOUT     --.返回当前会话等待锁的时间长短其单位为毫秒
 ,@@MAX_CONNECTIONS  --.返回允许连接到SQL Server 的最大连接数目
 ,@@MAX_PRECISION     --.返回decimal 和numeric 数据类型的精确度
 ,@@NESTLEVEL         --.返回当前执行的存储过程的嵌套级数初始值为0
 ,@@OPTIONS             --.返回当前SET 选项的信息
 ,@@PACK_RECEIVED     --.返回SQL Server 通过网络读取的输入包的数目
 ,@@PACK_SENT         --.返回SQL Server 写给网络的输出包的数目
 ,@@PACKET_ERRORS     --.返回网络包的错误数目
 ,@@PROCID             --.返回当前存储过程的ID 值
 ,@@REMSERVER         --.返回远程SQL Server 数据库服务器的名称
 ,@@SERVICENAME         --.返回SQL Server 正运行于哪种服务状态之下如MSSQLServer MSDTC SQLServerAgent
 ,@@TEXTSIZE         --.返回SET 语句的TEXTSIZE 选项值SET 语句定义了SELECT 语句中text 或image数据类型的最大长度基本单位为字节
 ,@@TIMETICKS         --.返回每一时钟的微秒数
 ,@@TOTAL_ERRORS     --.返回磁盘读写错误数目
 ,@@TOTAL_READ         --.返回磁盘读操作的数目
 ,@@TOTAL_WRITE         --.返回磁盘写操作的数目
 ,@@TRANCOUNT         --.返回当前连接中处于激活状态的事务数目

DBCC

--.帮助类命令
DBCC HELP('?')         --.查询所有的DBCC命令
DBCC HELP('checkdb')   --.查询指定的DBCC命令的语法说明
DBCC USEROPTIONS       --.返回当前连接的SET选项

--.查看当前DB已打开的活动事务
if object_id('tab001') is not null drop table tab001
CREATE TABLE tab001(id int, name varchar(3))
BEGIN TRAN 
INSERT INTO tab001 VALUES (101, 'abc')
DBCC OPENTRAN
ROLLBACK TRAN

--.所有DB的LOG大小
DBCC SQLPERF(LOGSPACE)

--.查询当前db的事务日志状态,只有status=0(free状态)的虚拟日志文件才会被收缩
use xxx; dbcc loginfo

--.显示从客户端发送到SQLServer的最后一个语句,须指定会话ID
SELECT @@spid
DBCC INPUTBUFFER (会话ID)

--.显示指定表或视图的数据和索引的碎片信息
DBCC SHOWCONTIG ('tb_user')

--.验证或重设table的自动增长初始值
DBCC CHECKIDENT ('tb_user',NORESEED)
DBCC CHECKIDENT ('tb_user', RESEED, 1000)  --.下个自增值为 1000+1

--.检查及修复逻辑和物理完整性
use xxx; 
DBCC CHECKDB ('xxx') WITH PHYSICAL_ONLY                           --.先对整个库快速联机检查
DBCC CHECKTABLE ('dbo.tb_user') WITH PHYSICAL_ONLY                --.再以较低的开销检查指定表的完整性
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE        --.再设置为单用户模式
ALTER DATABASE xxx SET EMERGENCY                                  --.再设置为紧急模式
DBCC CHECKDB ('xxx') WITH TABLOCK, PHYSICAL_ONLY, ALL_ERRORMSGS   --.先快速检查数据库的一致性
DBCC CHECKDB ('xxx') WITH TABLOCK, ALL_ERRORMSGS                  --.再执行全面检查
DBCC CHECKDB ('xxx', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS   --.最后修复db,可能丢失数据
ALTER DATABASE xxx SET MULTI_USER                                 --.先改回多用户模式
ALTER DATABASE xxx SET ONLINE                                     --.再取消紧急模式

--.收缩日志文件
USE xxx; DBCC SHRINKFILE (xxx_log, 100)              --.收缩日志文件到100MB
USE xxx; DBCC SHRINKFILE (2, 0, TRUNCATEONLY)        --.收缩日志文件到最小

--.收缩数据文件
USE xxx; DBCC SHRINKFILE (xxx,180) WITH NO_INFOMSGS  --.收缩数据文件到180MB

xp_cmdshell

若未启用 xp_cmdshell（默认禁用）则会报错 SQL Server 阻止了对组件“xp_cmdshell”的过程“sys.xp_cmdshell”的访问，因为此组件已作为此服务器安全配置的一部分而被关闭，启用脚本如下：

--.启用 xp_cmdshell
USE master; 
exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE 
exec sp_configure 'xp_cmdshell', 1
exec sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

sqlserver入门篇

sqlserver入门篇

基本操作

登入与登出

实例的管理

数据库的管理

关于数据库的批量操作

关于数据库的高级设置

数据表的管理

关于数据表的批量操作

字段的管理

关于 identity 自增

增删改查

备份与还原

账号的管理

关于账号的高级用法

会话与连接数

事务

存储过程与函数

索引与调优

索引的创建与删除

主键约束与普通约束

统计计息与索引碎片

外键

其他

Agent 作业

读取日志

@@ 全局变量

DBCC

xp_cmdshell

results matching ""

No results matching ""