SQL Server学习笔记：从SSMS到生产环境排查实战

简介面向数据库管理初学者的一份 SQL Server 学习笔记系统梳理了关系型数据库的核心概念与常见操作可作为课堂学习、备考或日常查询的速查手册。内容覆盖数据库的创建、删除与修改Create/Drop/Alter表、索引、视图等对象的建立以及 C/S 体系、编程接口、Web 分析与数据仓库支持等特性同时整理了系统数据库Master、Model、Tempdb、Msdb的作用、数据文件与日志文件的存储方式触发器与存储过程的参数限制以及主键、外键、默认值、Check、Unique 等约束的使用要点。还涉及关系模型、候选码与主码、数据库授权和 SQL 建表语句等细节知识点较为连贯。压缩包内共 1 个 doc 文档大小 499KB已有 440 人浏览学习适合需要快速回顾 SQL Server 基础知识的入门者和数据库管理人员。1. sql server 学习笔记从会用 SSMS 到能排查生产问题的关键一步我把 SQL Server 学习笔记从“抄语法”改成“记现象和排查顺序”之后才觉得自己真的入了门。很多人装好 SSMS跑通几条 SELECT 就以为会 SQL Server 了等到线上报“已成功与服务器建立连接但是在登录前发生错误”或者日志磁盘每秒几 MB 往上涨时才知道笔记里全是语法没有一条能指下一步看哪。这篇笔记按从业者实际会碰到的路径重写一遍先装对版本再从 sys 目录视图入手学 T-SQL接着用存储过程练事务边界最后把登录失败、密码过期、恢复停在 RESTORING 这类高频坑的排查顺序记下来。适合刚装好数据库、准备系统学一遍的人也适合被生产问题赶着补课的应用开发。2. 学习环境要先装对版本选型、安装顺序和最小连接验证网上搜“sql server 下载”“sql server 2019 安装教程”回来的版本列表对新人非常劝退有 SQL Server 2008 R2 的旧包有 Developer、Express、Standard 一堆名字还有从 Visual Studio Installer 里勾出来的组件。我的经验是学习环境装错版本带来的坑比语法错误更隐蔽你会花大量时间在“为什么我的行为和生产不一样”上。这一章把版本选型、安装里最常点错的两个选项以及装完怎么用命令行验证服务在线一次说清楚。2.1 版本选型Developer、Express 与 Standard学习期最怕哪种限制先看对比版本许可主要限制学习用途Developer官方免费开发版不能用于生产环境首选功能与 Enterprise 对齐Express免费单库大小上限 10GB内存受限做“小数据量怪现象”对照实验Standard付费缺少 Enterprise 高级功能生产常见行为与 Developer 接近对于写 SQL Server 学习笔记来说首选 Developer。它是免费下载功能上不阉割在线索引重建这类性能手段学完的东西拿到 Standard 生产环境基本通用。Express 最大的坑不是单库 10GB而是内存限制导致大表排序或查询慢很多容易让你误判成 SQL 写法问题实际上换到生产环境同样的语句快得离谱。把 Express 装来对照是值得的。我见过不少人在 Express 上练索引调优发现加不加索引没差别其实是数据量小到全表扫描都比索引快。所以我建议主力环境装 Developer顺手再装一个 Express。笔记里已经有固定的一句话“数据量上不去很多性能结论都是错的”这条就是被 Express 教育出来的。2.2 安装界面里两个容易顺手点错的选项实例名与服务账户安装 SQL Server 引擎时第一个容易顺手点错的是实例名。默认实例名是 MSSQLSERVER之后连接字符串写 localhost 或服务器 IP 就行如果装了命名实例比如 Express 默认叫 SQLEXPRESS连接时就得写 localhost\sqlexpress。一台物理机装多个版本做对照实验一定要用命名实例否则后装的会要求升级或卸载旧实例。这个“默认实例 vs 命名实例”的选择直接决定你后面所有 sqlcmd、连接串、SSMS 登录的写法。第二个容易坑人的选项是服务账户。安装向导默认给数据库引擎用虚拟账户 NT Service\MSSQLSERVER这个账户没有密码不受系统密码过期策略影响官方默认推荐。很多老教程让你改成 Local System 或指定域账户结果某天那个账户密码被运维轮换SQL Server 服务就起不来了。我装学习环境从不碰这个选项保持默认。还有一处是身份验证模式学习建议选“混合模式”因为后面要练 SQL 账号登录、密码策略这些只用 Windows 认证很多事情没法模拟。sa 密码一栏先设一个满足复杂度的强密码装完再关策略安装阶段绕不过去。我遇到过一个更绕的安装顺序坑先装引擎再通过 Visual Studio Installer 勾选 SQL Server 相关组件看起来是补工具结果它把 LocalDB 或一个命名实例装了进来原来写 localhost 的代码全部连错库。顺序上我习惯引擎 → SSMS → 再碰 VS 里的组件每装一个就跑一回后面那节的最小连接验证绝不让下一步建立在未知状态上。2.3 最小连接验证绕过 SSMS 用 sqlcmd 确认服务真的在线装好之后先别急着打开 SQL Server Management Studio。图形客户端能连上说明图形客户端自己那套配置是通的不代表命令行工具或应用服务器能连。我见过 Navicat for SQL Server 连不上、SSMS 却正常的例子反过来也见过所以我的最小验证永远走命令行。sqlcmd -S localhost -E -Q SELECT VERSION如果实例是命名实例连接串要带上实例名sqlcmd -S localhost\sqlexpress -E -Q SELECT VERSION-S指定服务器和实例名-E表示 Windows 身份验证-Q表示执行完语句立即退出。返回版本号说明引擎、服务、身份验证三层都是通的。如果连接失败先别猜直接查服务状态Get-Service -Name MSSQL* | Select-Object Name, Status从 SQL Server 2000 时代 Desktop Engine 的命令行工具一路到现在的 sqlcmd“先服务后命令”这个排查顺序就没变过。看服务名是 MSSQLSERVER 还是 MSSQL$SQLEXPRESS能直接判断机器上装了什么实例。服务状态是 Runningsqlcmd 还连不上再去 SQL Server 配置管理器里看 TCP/IP 协议有没有启用以及客户端协议是不是被改成了只看 Named Pipes。提示报错“已成功与服务器建立连接但是在登录前发生错误”不属于这个阶段它意味着 TCP 握手成功但在登录安全层挂了这个放到第 5 章专门说别在服务没起来这条路上反复查。3. 笔记从哪记起先学 sys 目录视图和示例库再多语法都是排错工具有人把 SQL Server 学习笔记记成一本“语法大全”从 SELECT 讲到 MERGE结果是真到查问题时一页都用不上。我更建议反过来先学会怎么“问”数据库查它自己存了哪些表、哪些索引、哪些过程引用了某张表语法用到时再查。这一章给三个能直接抄进笔记的起点sys 目录视图、AdventureWorks 示例库、带注释头的 SQL 片段库。3.1 先学会查 sys 目录视图而不是背语法打开 SSMS左侧“数据库 → 系统数据库 → master → 视图 → 系统视图”看到的 sys 开头的视图就是目录视图。新人第一反应是翻业务表里的数据但生产环境里出问题时的第一反应应该是翻这些元数据视图。下面这段“体检”脚本我在每台服务器上都会跑一遍找出当前库里所有没有主键的表SELECT t.name AS TableName FROM sys.tables t LEFT JOIN sys.indexes i ON i.object_id t.object_id AND i.is_primary_key 1 WHERE i.object_id IS NULL;LEFT JOIN 条件里带is_primary_key 1是关键。如果把这个条件直接写进 WHEREJOIN 不到主键索引的表就会被整体过滤掉查不出任何问题。跑出来如果有十几张表没主键说明这个库基本靠运维人肉兜底。改表结构前我常查“有哪些存储过程或视图引用了某张表”SELECT s.name AS SchemaName, o.name AS ObjectName, o.type_desc FROM sys.sql_modules m JOIN sys.objects o ON m.object_id o.object_id JOIN sys.schemas s ON o.schema_id s.schema_id WHERE m.definition LIKE %Orders% ORDER BY s.name, o.name;sys.sql_modules 存的是每个模块的定义文本LIKE %Orders%是把引用 Orders 这张表的对象全部捞出来。这个查询的坑是定义文本里的注释也可能被捞到真正动手前我会人工再核一遍但作为影响面扫描完全够用。这类目录视图查询的收益在初期不明显等你要改一个被十几处存储过程引用的字段时才知道它比任何语法书都值钱。3.2 用 AdventureWorks 示例库练窗口函数顺手记执行顺序AdventureWorks 是官方示例库表结构做关联练习很合适我第一次认真学窗口函数就靠它。窗口函数的难点不是语法而是 SQL 的执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。窗口函数在 SELECT 阶段计算所以 WHERE 里不能用窗口函数的结果但 ORDER BY 里可以用。这个顺序不记牢写复杂报表时很容易陷入“为什么这个条件不能写在这里”的困惑。下面这段是“每个产品分类里销售额前三的产品”SELECT pc.Name AS CategoryName, p.Name AS ProductName, SUM(so.LineTotal) AS SalesAmount, ROW_NUMBER() OVER ( PARTITION BY pc.Name ORDER BY SUM(so.LineTotal) DESC ) AS Rn FROM Sales.SalesOrderDetail so JOIN Production.Product p ON so.ProductID p.ProductID JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID psc.ProductSubcategoryID JOIN Production.ProductCategory pc ON psc.ProductCategoryID pc.ProductCategoryID GROUP BY pc.Name, p.Name ORDER BY CategoryName, Rn;PARTITION BY pc.Name意思是按产品大类分组编号ORDER BY SUM(so.LineTotal) DESC让销售额大的排前。写了 GROUP BY 之后窗口函数里用的 SUM 就是分组后的聚合值不是明细行的值。最容易翻车的点是漏掉 GROUP BY 里某个列或者想在 WHERE 里对 Rn 过滤。这两条我都写在笔记的片段开头提醒自己窗口函数只能在 SELECT 阶段出现想过滤排名结果就得包一层子查询或 CTE。3.3 把笔记沉淀成带注释头的 SQL 片段库笔记记到后期我发现最有价值的不是长篇教程而是可以直接粘贴的 SQL 片段。每个片段开头用注释把用途、适用场景、坑写清楚。下面这段是我磁盘告警时第一个跑的命令——查当前库占用空间最大的前 10 张表/* 用途找出当前库中占用页数最多的前 10 张表 * 适用磁盘快满或某表查询明显变慢时 * 坑不含日志文件删除大量数据后这个统计会虚高先重建索引再对比 */ SELECT TOP 10 t.name AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSizeKB, SUM(a.used_pages) * 8 AS UsedSizeKB FROM sys.tables t JOIN sys.indexes i ON t.object_id i.object_id JOIN sys.partitions p ON i.object_id p.object_id AND i.index_id p.index_id JOIN sys.allocation_units a ON p.partition_id a.container_id WHERE t.is_ms_shipped 0 GROUP BY t.name, p.rows ORDER BY TotalSizeKB DESC;页是 SQL Server 存储的最小单位每页 8KB所以total_pages * 8得到 KB。这段脚本里容易写错的是关联条件漏掉i.index_id p.index_id一旦漏掉分区统计会对同一张表重复计算。把这种带注释头的片段按“备份恢复”“性能排查”“日常体检”分文件夹存就是最实用的 SQL Server 学习笔记。三个月后翻回来注释头比正文还能说明当时为什么写它。4. 存储过程与事务边界学习笔记里必须记录的执行顺序和错误处理学习 SQL Server 到中段很多人会绕开存储过程觉得那是老古董应用代码里写 SQL 就够了。但存储过程强制你把参数、返回值、错误处理放在同一个代码包里这是练习事务边界最便宜的方式。这章讲怎么用 TRY...CATCH 包事务NOLOCK 提示什么时候能省以及游标怎么写才不给自己埋坑。4.1 为什么入门要写存储过程参数化、返回码与 TRY...CATCH存储过程不是一个高深对象它就是一个可复用的 T-SQL 代码包。写的过程会逼你做三个决定输入参数有哪些、调用方怎么知道成功失败、出错了数据会变成什么样。这三件事直接写 SQL 时很容易含糊放到存储过程里含糊不了。最经典的练习是转账CREATE PROCEDURE dbo.usp_TransferMoney FromAccountId INT, ToAccountId INT, Amount DECIMAL(18,2) AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; UPDATE dbo.Accounts SET Balance Balance - Amount WHERE AccountId FromAccountId; UPDATE dbo.Accounts SET Balance Balance Amount WHERE AccountId ToAccountId; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; END CATCH END;BEGIN TRY 和 BEGIN CATCH 是 SQL Server 2005 之后的两段式错误处理。这里最容易翻车的是 COMMIT 的位置事务一旦进入 CATCH说明数据变更还没定型必须回滚COMMIT 只能放在 TRY 块的正常路径里。THROW 把原始错误原样抛给调用方比返回一个错误码再翻文档好排查得多。参数用 DECIMAL(18,2) 而不是 FLOAT金额计算不能用浮点数这条我写了整段笔记。没有事务的存储过程也能写但前几个练习过程建议都带事务养成边界意识。4.2 事务里最容易翻车的隔离级别NOLOCK 什么时候能省网上抄来的笔记里NOLOCK 几乎被写成“查询不卡顿的万能药”但没人写代价。SQL Server 默认隔离级别是 READ COMMITTED读和写会互相阻塞。NOLOCK 对应的是 READ UNCOMMITTED读不加共享锁不阻塞别人写同时也会读到未提交的数据甚至因为页拆分读到重复行。-- 报表查询加 NOLOCK 的典型写法笔记里必须注明代价 SELECT COUNT(*), SUM(Amount) FROM dbo.Orders WITH (NOLOCK) WHERE OrderDate 2024-01-01;这段在“能容忍读到一半数据”的汇总场景里能用比如大致看单量。但金额汇总如果对精度有要求就不能加。正确姿势是开快照隔离数据库层面打开 ALLOW_SNAPSHOT_ISOLATION连接里设置SET TRANSACTION ISOLATION LEVEL SNAPSHOT读不会阻塞写也不会读到未提交的数据代价是 tempdb 压力变大。我笔记里记成一句口诀NOLOCK 不是不能用是你要说得清“这笔汇总差多少数据可以接受”。生产环境里用 NOLOCK 至少要在代码注释里写清楚为什么容忍脏读。4.3 游标的三种写法最小游标也要记得释放游标在 SQL Server 里名声不好但做索引碎片整理、批量归档、遍历库里的表做体检不用游标就是和自己过不去。我总结的最小游标写法是 LOCAL FAST_FORWARD READ_ONLYLOCAL 让游标只在当前批处理可见FAST_FORWARD 是单向只进READ_ONLY 不能通过游标改数据。这三个组合能避开大部分游标性能事故。DECLARE TableName NVARCHAR(128); DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.tables WHERE is_ms_shipped 0; OPEN cur; FETCH NEXT FROM cur INTO TableName; WHILE FETCH_STATUS 0 BEGIN PRINT TableName; FETCH NEXT FROM cur INTO TableName; END; CLOSE cur; DEALLOCATE cur;WHILE FETCH_STATUS 0 表示还有行可取循环体里取下一行要放最后否则会跳过一行。这段代码里最容易翻车的不是语法而是不写 CLOSE 和 DEALLOCATE。第一次跑完游标没释放第二次跑直接报“游标已存在”。CLOSE 关掉游标DEALLOCATE 释放游标占用的资源两个都得写。我在生产环境见过只 CLOSE 不 DEALLOCATE 的写法时间长了就是游标资源泄漏。学习笔记里我给游标的定位是能不用就不用但管理脚本里用游标比递归 CTE 更容易读懂关键是边界要封闭。5. 高频问题排查登录握手失败、密码到期等 4 条血泪记录学习笔记里最值钱的部分是“现象 → 原因 → 解决”的排查记录。这一章写 4 个常见的 SQL Server 问题全部按真实排查顺序来先确认现象再找原因最后给命令。这 4 个问题覆盖了“连接建立了但登录失败”“密码过期”“还原卡住”“密码策略关不掉”四类学习期和生产期都会碰到。5.1 已与服务器建立连接但在登录前报错多数是加密协议不匹配现象用 SSMS 或应用连接 SQL Server提示“已成功与服务器建立连接但是在登录过程中发生错误”后面常跟着 SSL Provider 相关字样。很多人看到“已成功建立连接”就以为网络是好的于是反复重装客户端方向一开始就错了。原因这个报错的本质是 TCP 层面握手成功但在登录安全协商阶段两侧协议不一致。最典型的是老版本 SQL Server比如 SQL Server 2008 R2、SQL Server 2012跑在较新的 Windows 上实例只开 TLS 1.0而新系统的 Schannel 默认禁用了 TLS 1.0或者客户端驱动太老只支持旧协议服务器只允许新协议。解决先看 Windows 事件日志里 SQL Server 有没有记录加密协商失败。测试环境最快的是连接字符串加TrustServerCertificateTrue跳过自签名证书校验再试。加完还是失败大概率就是 TLS 版本问题给 SQL Server 实例打上支持 TLS 1.2 的补丁或在服务器注册表里启用对应协议后重启实例。客户端这边把老驱动换成新版 ODBC Driver比在注册表里折腾更省事。这个报错不代表服务离线不要先重启实例先看协议配置。5.2 SQL Server 2012 密码到期导致应用连环报错现象应用日志里连续出现“用户登录失败原因是密码过期”“Login failed”状态码 18488。数据库服务正常资源占用也正常就是一个登录报错把应用拖垮了。原因SQL Server 登录默认继承 Windows 密码策略包括密码过期时间。装完数据库没人管90 天后就到期。这里容易漏一点不只是 sa任何用 SQL 身份验证的账号都可能到期。应用连接池里的旧连接不会自动感知新密码表现就是某天早晨所有新请求一起失败。解决用管理员账号连进去把应用账号的过期和策略检查关掉ALTER LOGIN [app_user] WITH CHECK_EXPIRATION OFF; ALTER LOGIN [app_user] WITH CHECK_POLICY OFF;CHECK_EXPIRATION 控制是否强制密码过期CHECK_POLICY 控制是否应用 Windows 密码复杂度策略。学习环境两个都关掉最省心。生产环境建议只关过期不关复杂度密码定期改。如果你连 sa 都到期了必须先能连上实例才能改名那就用 Windows 认证登录再执行。这个案例里最惨的学习是永远不要在应用里用 sa 做连接账号sa 出事影响的是整个实例不是单个库。5.3 恢复备份后库一直停在“正在恢复”日志还不涨现象RESTORE DATABASE 命令执行完SSMS 里库名后面跟着“(正在恢复)”状态是 RESTORING刷新半天不动。错误日志也没报错数据文件、日志文件都在。原因最常见的是还原语句带了 WITH NORECOVERY。NORECOVERY 本来就是为“继续追加后续备份”设计的没加后续备份或最后没收尾库就永远留在还原状态。另一种情况是还原日志备份时备份链断了比如中间日志被截断过SQL Serve 不允许直接变 ONLINE。解决如果只是还原单次全量备份确认不再追加日志后执行RESTORE DATABASE [MyDB] WITH RECOVERY;这一句把数据库拉回在线状态。如果是完整还原链记住每一步都用 NORECOVERY最后一步换 RECOVERY。在 RESTORING 状态下不要直接删库重来先试上面这条很多时候一秒钟就解决。备份恢复相关的笔记务必记录备份链的起点和时间否则等你从一大堆文件里找日志备份时才是真麻烦。5.4 SQL Server 2022 Express 装完关掉密码策略两个开关别漏现象装 SQL Server 2022 Express 时安装向导要求 sa 密码必须带大小写、数字、符号设简单的不让过。装完再用 SQL 账号登录过一段时间又提示密码过期。原因SQL Server 安装界面没有直接的“关闭密码策略”开关安装阶段绕不过强密码。装完要手动改登录属性同时注意 Express 的实例名往往是 MSSQL$SQLEXPRESS连接串写错成 localhost 就白排查一场。解决安装阶段先设临时强密码装完立即执行 5.2 的两条 ALTER LOGIN。GUI 安装就手动处理命令行部署的话常见做法是安装完成后统一执行策略调整。顺手把 sa 禁掉新建一个自己的登录以后所有连接验证都用这个账号。sa 在实例里是超级管理员上来就用 sa 写应用连接串等于把整个数据库的钥匙挂在门口。6. 进阶用法把学习笔记改造成五分钟诊断脚本集笔记积累到一定量下一步值得做的是把常用片段串成几个固定脚本当前正在跑的请求、最近最长查询、库健康速览。出问题时按脚本跑一遍基本能在五分钟内定位“是慢查询还是资源问题还是磁盘问题”。我用得最多的是第一板斧当前正在跑的请求SELECT r.session_id, r.status, t.text, r.start_time, r.wait_type, r.blocking_session_id FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id SPID;wait_type 出现 PAGEIOLATCH_SH 或 PAGEIOLATCH_EX基本指向磁盘读慢出现 WRITELOG说明日志写入卡住通常要查日志文件所在盘的 IO。这个查询把正在执行的 SQL 文本直接列出来比活动监视器更直观。我习惯再加一个输出去重的小版本只显示重复次数最多的前 5 条 SQL用来找“谁在疯狂请求同一个查询”。笔记变成诊断脚本集的诀窍不是命令多而是每条后面都带“看到什么现象 → 下一步查哪里”的注释。我有一次栽在死锁上笔记里记录了死锁的语法却没写死锁后先查哪个视图、怎么拿死锁图现场只能翻官方文档。那次之后我要求每条笔记最后都补一个“落地检查”建完索引后怎么看碎片加完 NOLOCK 后怎么确认结果可接受。现在翻笔记的感觉像翻自己的排错手册遇到新问题顺着脚本集走大部分都能落到一个具体等待类型或一条具体 SQL 上。希望这个路径对你有用也帮到你。本文还有配套的精品资源点击获取