简介《SQL Server学习笔记》面向数据库初学者和需要快速梳理SQL Server知识框架的从业者以一份doc文档归纳核心概念与常用语法适合课程复习、入门自学及日常查阅。笔记从创建、删除、修改数据库对象切入串联C/S体系、表/列/索引/触发器/存储过程等对象规模限制并补充数据库大小、用户连接数与分布式管理框架等参数展开关系模型中的实体、属性、码、域、元组等基础概念以及建库建表、数据插入、主键/外键/默认值/check/unique约束、系统数据库和用户授权等语句要点。文中还涉及数据文件、次要文件、日志文件与文件组的存储划分便于对照理解数据库物理组织。压缩包共1个doc文件约499KB已有440人学习内容偏重概念梳理与操作要点归纳可作为搭建SQL Server基础、定位易混知识点的轻量学习资料。1. 一份笔记能不能直接抄SQL Server 从建库到授权的顺序问题有人拿着一份 SQL Server 学习笔记来问照着敲create table中文进去变问号想删一个用不上的列报「对象依赖于该列」视图建好了update又被拒绝。语句本身没错错在笔记是按知识点罗列的没标出执行顺序和前置条件。这份笔记覆盖建库、建表、五种约束、视图、索引、存储过程、游标、范式、权限条目密、例子多适合刚接手一个 SQL Server 实例、需要把零散概念串成一条链的人也适合维护老库时当对照清单。下面按「先落文件、再建表、后上对象、最后验权限」的顺序拆开把能抄的部分写全把抄了会翻车的地方单独拎出来。2. 建库先落文件CREATE DATABASE 三个子句与 .mdf/.ndf/.ldf 的对应关系2.1 数据库在磁盘上到底是几个文件逻辑上看到的是表、视图、索引落到操作系统只有三类文件。笔记里这段写得很干脆主数据文件带启动信息并存数据次要数据文件用来把数据摊到多块磁盘日志文件保证事务和可恢复性。文件组则是把多个文件打包成一个分配单元方便按盘做管理。文件类型常见扩展名数量约束存什么主数据文件.mdf每个库有且只有一个数据库启动信息 数据页次要数据文件.ndf可选可多个主文件放不下或需要分摊 IO 的数据事务日志文件.ldf至少一个可多个事务日志记录用于恢复和回滚文件组的意义在于建表时可以指定ON 文件组名把大表和它的索引分到不同物理盘上减少 IO 争抢。默认文件组叫 PRIMARY不写就落在主文件里。很多老库所有东西都堆在 C 盘一个 mdf 上数据量一涨备份慢、恢复更慢这就是当初建库时没分文件组留下的账。需要提醒一句笔记里记录的「数据库大小 1MB—1TB」「用户连接 32767」「每库 20 亿个表」这类上限是特定版本口径下的数字迁移到新环境前用系统视图或官方文档核对一遍别直接拿来做容量规划。2.2 CREATE DATABASE 的每个参数都能改但别一次写满建库语句的结构是三段CREATE DATABASE 库名、ON定义数据文件、LOG ON定义日志文件。下面这段是常见的完整写法两个数据文件分目录、日志单独放。CREATE DATABASE ShopDB ON PRIMARY ( NAME NShopDB_data, -- 逻辑名备份还原脚本里引用它 FILENAME ND:\SQLData\ShopDB_data.mdf, -- 物理路径目录必须已存在 SIZE 50MB, -- 初始大小 MAXSIZE 2GB, -- 上限写 UNLIMITED 表示不设限 FILEGROWTH 10MB -- 每次增长量建议按固定 MB ), ( NAME NShopDB_data2, FILENAME ND:\SQLData\ShopDB_data2.ndf, SIZE 50MB, MAXSIZE 2GB, FILEGROWTH 10MB ) LOG ON ( NAME NShopDB_log, FILENAME ND:\SQLLog\ShopDB_log.ldf, SIZE 50MB, MAXSIZE 4GB, FILEGROWTH 10MB );NAME是逻辑名不是文件名后面做备份、还原、脱机时用的都是它FILENAME必须写绝对路径且 SQL Server 服务账户对该目录要有写权限否则执行时直接报「操作系统错误 5拒绝访问」这种报错跟语法一点关系都没有纯权限问题。SIZE不要给得太小库启动时要复制 model 库的结构给 50MB 以上比较稳。FILEGROWTH用百分比是血泪经验里最容易踩的一个按 10% 增长库越大每次增长越多增长瞬间的阻塞越明显改成固定 10MB 或 64MB 更可控。MAXSIZE建议显式给上限尤其是日志文件不设限就是等着某天把盘吃满。笔记里还有一句值得抄下来一般用户建库时不指定方括号里的可选项关键字大小写不影响语义逻辑名可以是中文。实操上我一般先用最简语句建一个默认配置的库再用ALTER DATABASE调文件和增长参数比一次性写十几个参数再排错快得多。2.3 系统数据库各自的职责以及不要碰的那几个库名作用操作建议master登录账号、其他数据库和实例的全部配置信息只备份不手工改系统表model新建数据库的模板含每个库都要有的系统表改动会传染给之后所有新库tempdb临时表、排序和哈希的中间结果实例重启自动重建别在里面建业务表msdb作业、警报、操作员等调度信息跟业务库一起纳入备份pubs / northwind早期版本自带的示例库学习可以生产实例不装model 这一段值得单独说有人在 model 里建了一张表做「公共配置」结果之后每建一个新库都莫名其妙多出这张表查半天查不到来源。tempdb 则是另一个极端把中间结果表建在 tempdb 里图快实例一重启数据全没还以为是丢数据故障。3. 建表与约束char 与 varchar 的边界、五种约束的生效顺序3.1 定长与变长char(n) 的 n 到底算字节还是字符笔记里列的类型够用char(n)定长、varchar(n)变长、int/bigint/smallint/tinyint整数族、numeric(p,s)精确数值、money货币、datetime日期时间、table表值返回。真正容易出事的是字符类型。char(n)的 n 介于 1 到 8000实际存储长度就是 n不足补空格超长直接截断且不报错varchar(n)按输入的实际长度存最多 n。关键在于这两个都是非 Unicode 类型中文占几个字节取决于排序规则多数中文排序规则下是 2 字节。也就是说char(4)塞「电子产品」四个汉字很可能只剩两个汉字剩下的被截掉而语句一路绿灯直到业务方发现数据不对。存中文的列我一般直接上nvarchar(n)n 按字符算一个汉字算一个。已经在用的老表改类型要谨慎ALTER COLUMN改字符类型时如果现有数据超长会直接失败先查MAX(LEN(列))再改。money类型自带 4 位小数且不用加引号但做金额计算时四舍五入行为和numeric(18,2)不完全一样财务口径要求严格的场景优先numeric。3.2 五种约束写在一张建表语句里CREATE TABLE goods ( goods_id VARCHAR(10) NOT NULL, goods_name NVARCHAR(50) NOT NULL, goods_type_id VARCHAR(10) NOT NULL, goods_number INT NOT NULL DEFAULT 0, goods_price MONEY NULL, bid_date DATETIME NULL, CONSTRAINT pk_goods PRIMARY KEY (goods_id), CONSTRAINT fk_goods_type FOREIGN KEY (goods_type_id) REFERENCES goods_type (goods_type_id), CONSTRAINT uq_goods_name UNIQUE (goods_name), CONSTRAINT ck_goods_num CHECK (goods_number 0) );主键约束保证唯一且非空一张表只能有一个作为主键的列不能为 NULL这是实体完整性的落地方式。外键约束把两张表连起来插入时必须先有父表记录、删除时必须先删子表记录否则报冲突它维护的是参照完整性。默认值约束只在 INSERT 语句完全没提这一列时生效显式写NULL不会触发默认值这点很多人搞混。CHECK 约束在插入和更新时校验表达式但表达式结果为 UNKNOWN涉及 NULL时是放行的所以CHECK (goods_price 0)挡不住 NULL。UNIQUE 约束保证列值不重复与主键的区别是允许一个 NULL 值且一张表可以有多个。候选码和主码的关系也在这份笔记里能唯一标识一行的属性组叫候选码可以是一列也可以是多列组合从候选码里选一个当主码主码里的属性叫主属性其余叫非主属性。这段概念在后面讲范式时会反复用到。3.3 ALTER TABLE 的增删改顺序错一步就报错-- 加列允许为空历史行自动填 NULL ALTER TABLE goods ADD goods_remark NVARCHAR(200) NULL; -- 加约束对已有数据默认也会校验 ALTER TABLE goods ADD CONSTRAINT ck_price CHECK (goods_price 0); -- 删列之前必须先清掉依赖它的约束 ALTER TABLE goods DROP CONSTRAINT ck_goods_num; ALTER TABLE goods DROP COLUMN goods_number; -- 临时关闭 / 恢复约束校验 ALTER TABLE goods NOCHECK CONSTRAINT ALL; ALTER TABLE goods CHECK CONSTRAINT ALL;笔记里专门点了这段被复制的列、用在索引里的列、被 CHECK / FOREIGN KEY / UNIQUE / PRIMARY KEY 引用的列、绑定了默认值对象或规则的列都不能直接删。所以删列的标准动作是「先查依赖、再删约束、最后删列」顺序反了就是那句「对象依赖于该列」。NOCHECK CONSTRAINT ALL只影响之后的新数据历史数据不会自动校验要让已有数据也过一遍得先WITH NOCHECK加约束再CHECK CONSTRAINT或者直接WITH CHECK ADD CONSTRAINT。改列类型用ALTER COLUMN改之前确认现有数据能转过去varchar转int只要有一行非数字就整体失败。3.4 范式判定拆表到底在拆什么笔记里用商品表的组合列举例goods_type_1, goods_type_2, goods_type_3这种设计按某个类别查商品编号时结果为空原因就是存在组合列。这引出第一范式——每个分量必须是不可再分的数据项一列里不能塞集合或序列。转换方法有两种把非原子列拆成多列或者拆成多行。范式要消除的依赖典型症状处理方式1NF组合列、集合值一列里存「生活用品,电子产品」拆列或拆行或独立出类型表2NF非主属性对码的部分依赖联合主键 (用户编号, 商品编号) 的表里存了「商品名称」把只依赖商品编号的列拆到商品表3NF非主属性对码的传递依赖表里存了「部门编号→部门名称」部门名称拆到部门表BCNF主属性之间的依赖多个候选码互相决定进一步分解让每个决定因素都是候选码第二范式的坑最隐蔽(用户编号, 商品编号)做联合主键商品名称只跟商品编号有关跟用户无关这就是部分依赖。它带来的直接后果是重复存储和更新异常而且联合主键里的列不能为空一旦某个商品编号缺失就插不进去。第三范式看的是传递依赖a 决定 b、b 决定 c 且 b 不能反推 a那 c 就不该待在这张表里。BCNF 是在 3NF 基础上再要求所有决定因素都是候选码对应到笔记里那句「修正的第三范式」。实际建表不必教条。OLTP 业务表按 3NF 走减少冗余和更新异常报表宽表和数据集市为了少 join 会主动反范式这是权衡不是犯错。4. 视图、索引、存储过程三个高频对象的写法与不可更新边界4.1 视图的三个选项选错就是给自己埋雷CREATE VIEW goods_view_electronics WITH SCHEMABINDING AS SELECT goods_id, goods_name, goods_type_id FROM dbo.goods WHERE goods_type_id E01;WITH CHECK OPTION的作用是通过这个视图做 INSERT 或 UPDATE 时结果行必须满足视图定义里的 WHERE 条件不满足就拒绝。笔记里的例子是先筛出电子产品再建视图加上这个选项后往视图里插一条非电子产品会被挡回去没加就能插进去然后查不出来这是最常见的「数据凭空消失」现场。WITH ENCRYPTION把视图定义文本加密之后用sp_helptext看不到定义上线前一定把源码留档否则后面接手的人只能看到一坨黑匣子。WITH SCHEMABINDING把视图绑到基表结构上好处是基表不能随便改列类型或删列坏处也是这个——改表结构前要先删视图。用了它之后 SELECT 列表不能写*表和视图都要带架构名。视图可更新的条件笔记里列得很清楚FROM 子句至少引用一个表选择列表里没有派生列、没有聚合函数也不包含 TOP、GROUP BY、UNION、DISTINCT。多表连接的视图一般只能更新其中一张基表的列跨表的列更新会被拒绝。给视图列改名也简单在CREATE VIEW 视图名 (列1, 列2)里写别名即可视图对外就暴露这套列名。4.2 聚簇索引与非聚簇索引一个管物理顺序一个管查找路径聚簇索引的索引顺序和表的物理存储顺序一致所以一张表只能有一个通常落在主键或最常用于范围查询的列上。非聚簇索引是独立结构叶子节点存的是行定位信息先查到位置再回表取数据一张表可以有多个。笔记里给的上限是「1 个簇类、249 个非簇类」同样是特定版本口径实际能建多少受列宽和页大小限制但设计上没人会去逼近这个数。-- 聚簇索引一表一个按主键 CREATE UNIQUE CLUSTERED INDEX ix_goods_id ON goods (goods_id); -- 复合非聚簇索引前导列是查询条件第二列带降序 CREATE INDEX ix_goods_type_date ON goods (goods_type_id, bid_date DESC);复合索引的列顺序按「最左前缀」来配查询条件里只用到第二列是走不了这个索引的。ASC/DESC在建索引时声明能匹配对应的 ORDER BY省一次排序。该建索引的场景笔记里给了四条经常搜索的列、经常排序的列、主键和外键、经常出现在 WHERE 里的列。反面清单也得记住更新非常频繁的列、选择性极低的列比如只有男女两种值的性别列建了收益有限还拖慢写入。判断索引有没有被用上别靠猜。开SET STATISTICS IO ON看逻辑读次数或者直接看执行计划里是 Index Seek、Index Scan 还是 Table Scan。Seek 是走了索引定位Scan 是全扫一字之差性能差一个量级。4.3 存储过程与游标参数传错和方法用错是两回事CREATE PROCEDURE modify_process bid_user VARCHAR(10), bid_date DATETIME, bid_number INT, bid_goods_id VARCHAR(10), goods_price MONEY AS BEGIN SET NOCOUNT ON; -- 少发一行「受影响行数」的网络包 UPDATE goods SET goods_number goods_number - bid_number WHERE goods_id bid_goods_id; INSERT INTO user_history (user_id, amount, bid_date) VALUES (bid_user, goods_price * bid_number, bid_date); END; GO -- 调用 EXEC modify_process A001, 2024-05-01, 2, G001, 99.50;存储过程把一组 SQL 封装成一个调用单元笔记里记录的上限是一个过程最多 1024 个参数、32 层嵌套。参数前加调用时按位置或按名字传都行。多个语句放进一个过程默认不是原子的出错要回滚得显式写事务这点笔记没展开但实际项目里必须补上。一个必须说的坑笔记里调用存储过程的示例是用字符串拼接参数再 EXEC 的这种写法遇到单引号就断还带注入风险。正确做法是参数化调用或者用sp_executesql带参数声明。游标那段笔记写的是标准四步DECLARE、OPEN、FETCH、CLOSE DEALLOCATE。DECLARE goods_name NVARCHAR(50), goods_number INT; DECLARE shop_info CURSOR LOCAL FAST_FORWARD FOR SELECT goods_name, goods_number FROM goods WHERE goods_name LIKE S% ORDER BY goods_name; OPEN shop_info; FETCH NEXT FROM shop_info INTO goods_name, goods_number; WHILE FETCH_STATUS 0 BEGIN PRINT goods_name : CAST(goods_number AS VARCHAR(20)); FETCH NEXT FROM shop_info INTO goods_name, goods_number; -- 循环里必须再取一次 END CLOSE shop_info; DEALLOCATE cur_info;LOCAL让游标只在当前会话可见FAST_FORWARD声明只进只读能减少开销。循环体里漏掉第二次 FETCH 就是死循环这个错误在代码评审里出现的频率高得离谱。游标能用集合操作替代就别用它是逐行处理数据量一大性能就塌。5. 照着笔记抄会翻车的五个点从删列报错到登录用户分家5.1 建库建表阶段的两个坑现象一CREATE DATABASE语句检查了十几遍没毛病执行就报「操作系统错误 5拒绝访问」。原因FILENAME指向的目录不存在或者 SQL Server 服务账户对该目录没有写权限语句在创建物理文件这一步失败跟语法无关。解决先在资源管理器里把目录建好给服务账户 NTFS 修改权限再执行建库语句路径里不要用中文和空格避免二次排查。现象二char(4)的列插入「电子产品」查出来只剩两个汉字或者变成问号。原因char/varchar是非 Unicode 类型中文按字节算长度不够就被静默截断不报错。解决存中文的列统一用nvarchar已经建好的表用ALTER TABLE ... ALTER COLUMN改类型改之前先SELECT MAX(LEN(列))确认数据不会超长。连接串和排序规则不一致也会出问号排查时两边一起看。5.2 对象阶段的两个坑现象三ALTER TABLE goods DROP COLUMN goods_number报「对象依赖于列」。原因这一列上有 CHECK 约束、默认值约束、索引或统计信息删列前必须先清依赖。解决先查依赖再按顺序删。-- 查出表上所有约束和索引确认哪些挂在目标列上 SELECT name, type_desc FROM sys.objects WHERE parent_object_id OBJECT_ID(dbo.goods) AND type IN (C, F, PK, UQ, D); -- 先删约束再删列 ALTER TABLE goods DROP CONSTRAINT ck_goods_num; ALTER TABLE goods DROP COLUMN goods_number;现象四视图CREATE VIEW成功UPDATE时报「视图或函数不可更新因为包含聚合或 DISTINCT」。原因视图定义里有聚合函数、TOP、GROUP BY、UNION、DISTINCT 或派生列这些都会让视图变成只读。解决把聚合下沉到子查询或改成对基表更新确实需要更新多表视图时一次只更新其中一张基表的列。建视图时就该想清楚用途只读报表视图和可更新视图分开建别指望一个视图两头兼顾。5.3 部署与权限阶段的一个坑现象五新建了登录账号能连上实例一USE业务库就报拒绝访问。原因登录是实例级对象只解决「能不能连上服务器」要访问某个库还得在这个库里建对应用户并授权。解决按「登录→用户→角色」三级走。较新的版本用CREATE LOGIN/CREATE USER/CREATE ROLE笔记里记的sp_addlogin/sp_adduser/sp_addrole是早期写法逻辑一样先建实例级登录再在目标库里建用户映射到该登录最后把用户加进角色或直接授 SELECT/INSERT 权限。只建登录不建用户就是能进大门进不了房间。顺带提一个部署期的坑CREATE PROCEDURE里引用了不存在的表创建时不一定报错因为名称解析是延迟的要等第一次执行才暴露。所以每个存储过程建完立刻EXEC一次做冒烟测试别等上线后由业务方触发。6. 上线前跑一遍验证链约束、索引命中与权限隔离怎么自查笔记看完、脚本写完最后一件事是验证它真的按预期在拦、在用、在隔离。我习惯在一个测试库里跑一条固定链路三步十分钟内能出结果。验证项关键命令预期结果约束是否生效插入一条违反 CHECK 的记录报约束冲突事务回滚索引是否命中SET STATISTICS IO ON 条件查询逻辑读明显低于全表扫描执行计划为 Seek权限是否隔离EXECUTE AS USER切换身份查询未授权时报表或对象不存在-- 1. 约束验证goods_number 上有 CHECK (goods_number 0) INSERT INTO goods (goods_id, goods_name, goods_type_id, goods_number) VALUES (G999, N测试商品, E01, -1); -- 预期CHECK 约束冲突插入失败 -- 2. 索引验证ix_goods_type_date 的前导列是 goods_type_id SET STATISTICS IO ON; SELECT goods_id, goods_name FROM goods WHERE goods_type_id E01; SET STATISTICS IO OFF; -- 3. 权限验证模拟应用账号只授了 SELECT EXECUTE AS USER app_user; SELECT COUNT(*) FROM dbo.goods; REVERT; -- 4. 顺手确认对象依赖没断 SELECT name, type_desc FROM sys.objects WHERE parent_object_id OBJECT_ID(dbo.goods);第一步的插入必须真的失败如果插进去了说明约束加的时候用了WITH NOCHECK或者根本没加上历史数据也没校验。第二步看逻辑读全表扫一个几万行的表逻辑读可能是几百上千走 Seek 通常只有个位数。第三步EXECUTE AS之后如果还能查说明权限给宽了生产上应用账号只给必要的 SELECT/INSERT/UPDATE绝不给 db_owner。第四步查依赖是为了确认删列、改类型之后没有留下悬空对象。这套链路我现在是强制走的新库建完跑一遍改完表结构再跑一遍权限调整后跑第三段。有一次省了第一步结果上线三天后发现一批负数库存回头查是 CHECK 约束在迁移脚本里被NOCHECK掉之后忘了恢复补数据补到半夜。从那以后我每次改完约束都会把CHECK CONSTRAINT那句单独拎出来执行一次再跑一遍验证链才敢交付。希望帮到你。本文还有配套的精品资源点击获取