一、标量函数：一个输入，一个输出

CREATE FUNCTION fn_level(@s INT)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @r VARCHAR(10)
    IF @s >= 90 SET @r = '优秀'
    ELSE IF @s >= 60 SET @r = '及格'
    ELSE SET @r = '不及格'
    RETURN @r
END

三段结构：参数、返回类型、函数体。 RETURNS 后面写返回类型， 函数体里用 RETURN 返回值——跟编程语言的普通函数一样。 但 T-SQL 的标量函数不能改表。 不能 INSERT、UPDATE、DELETE， 只能算——这是它跟存储过程最大的区别。 函数里也不能用 TRY...CATCH、不能开事务、不能调存储过程。限制不少， 为的是「函数可以被优化器当成表达式处理」——一旦能改数据，就没法优化了。 建好之后用 dbo.fn_level(score) 调用，前面的 dbo 是架构名。不写 dbo 也能调， 但如果当前用户的默认架构不是 dbo，就会找不到——所以生产代码里建议写全。

二、表值函数：返回的是一张表

CREATE FUNCTION fn_scores(@min INT)
RETURNS TABLE
AS
RETURN (SELECT sno, score FROM tb_score WHERE score >= @min)

返回的不是一个值，是一个结果集——所以它能出现在 FROM 后面， 跟查一张表一模一样：SELECT * FROM fn_scores(80)。 表值函数分两种，性能差很远：

内联的好，是因为优化器能把它「展开」进外层查询。 你写 SELECT * FROM fn_scores(80) WHERE sno = 'S01'， 优化器会把它变成一条完整的 SELECT——两个条件一起用索引。 多语句的就做不到。 它先把结果装进一个表变量， 外层再筛——中间那张表没有统计信息，优化器只能猜。 数据量一大，性能就崩。 所以能写成内联的就别写多语句。 这一条在性能优化里非常实用—— 很多「查询突然变慢」，追到最后是一个多语句表值函数。

三、标量函数在 WHERE 里的代价

这是本节最该记住的性能点。 标量函数放在 WHERE 里，会逐行调用：

SELECT * FROM tb_score WHERE dbo.fn_level(score) = '优秀'

一百万行就调一百万次函数。 而且 SQL Server 2019 之前， 标量函数对优化器是黑盒——它没法估算代价，只能给个固定值。 结果就是「明明有索引却不用」。 因为 fn_level(score) 这个表达式上没有索引， 优化器也不知道它和 score 什么关系——只能全表扫，逐行算。 解法有两条： 一是改成 WHERE score >= 90（把逻辑展开，不用函数）；二是用内联表值函数代替。 前者最快，后者最灵活。 SQL Server 2019 做了「标量函数内联」优化，能把简单的标量函数自动展开。 但它有条件限制——复杂的、带多语句的还是展开不了。

四、建、改、删

改函数只能 ALTER，没有 CREATE OR REPLACE。后者是 Oracle 的写法， SQL Server 不认——这是从 Oracle 转过来的人最常踩的坑。 所以脚本里改函数，常见写法是「先删再建」：

IF OBJECT_ID('fn_level', 'FN') IS NOT NULL
    DROP FUNCTION fn_level
GO
CREATE FUNCTION fn_level(@s INT) ...

那个 GO 不能少。 因为 CREATE FUNCTION 必须是批里的第一句—— 这个规则在 6.6 讲 GO 的时候说过，这里正好用上。 SQL Server 2016 起支持 DROP FUNCTION IF EXISTS，比 IF OBJECT_ID(...) IS NOT NULL 短。 但老版本不认，写脚本时要注意目标版本。 改函数还有一个坑：依赖它的视图、过程会「记住」旧的返回类型。改了函数的返回类型， 那些依赖对象要重建——否则报「架构绑定无效」。

五、动手 Demo：用 create_function 真跑自定义函数

这一段贴了标量函数和表值函数的 T-SQL 原码（本沙箱不执行）， 然后用 SQLite 的 create_function 把同名函数注册进去真跑一遍—— 这是本课少数「自定义函数能真跑」的地方。

import sqlite3

def fn_level(s):
    if s is None:
        return None
    if s >= 90:
        return u"优秀"
    if s >= 60:
        return u"及格"
    return u"不及格"

c = sqlite3.connect(u":memory:")
c.create_function(u"fn_level", 1, fn_level)
c.execute(u"CREATE TABLE tb (sno TEXT, score INT)")
c.executemany(u"INSERT INTO tb VALUES (?,?)",
              [('S01', 92), ('S02', 78), ('S03', 55)])
for r in c.execute(u"SELECT sno, score, fn_level(score) FROM tb"):
    print(r)

实跑输出：

--------------------------------------------------------------------
自定义函数：标量与表值
--------------------------------------------------------------------


  ① 标量函数：一个输入，一个输出
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE FUNCTION fn_level(@s INT)
  | RETURNS VARCHAR(10)
  | AS
  | BEGIN
  |     DECLARE @r VARCHAR(10)
  |     IF @s >= 90 SET @r = '优秀'
  |     ELSE IF @s >= 60 SET @r = '及格'
  |     ELSE SET @r = '不及格'
  |     RETURN @r
  | END

  标量函数就是「传一个值，返回一个值」，跟编程语言的普通函数一样。
  注意 RETURNS 后面写返回类型，函数体里用 RETURN 返回。
  但 T-SQL 的标量函数不能改表——不能 INSERT、UPDATE、DELETE，只能算。
  用 SQLite 的 create_function 注册同名函数：
  sno  score  等级
  S01  92     优秀
  S02  78     及格
  S03  55     不及格
  这是真跑出来的——SQLite 的 create_function 能把 Python 函数注册进 SQL 里当函数用。
  所以「自定义函数怎么调」这件事，演示能演到位；
  差别只在「函数体是用 T-SQL 写的还是 Python 写的」。

  ② 表值函数：返回的是一张表
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE FUNCTION fn_scores(@min INT)
  | RETURNS TABLE
  | AS
  | RETURN (SELECT sno, score FROM tb_score WHERE score >= @min)

  表值函数返回的不是一个值，是一个结果集——所以它能出现在 FROM 后面。
  调用写法：SELECT * FROM fn_scores(80)，跟查一张表一模一样。
  表值函数分两种：内联（RETURNS TABLE，只有一条 SELECT）和多语句（RETURNS @t TABLE）。
  内联的性能好得多，因为优化器能把它展开进外层查询；多语句的常常是性能黑洞。
  用 Python 复刻「表值函数」——传 80，返回分数 >= 80 的行：
  sno  score
  S01  92
  这里复刻的是「返回一个结果集」这个行为，不是 T-SQL 的语法。
  SQLite 没有表值函数，所以只能这样演；真实 SQL Server 里它就是一句 SELECT * FROM fn_scores(80)。

  ③ 标量函数和表值函数怎么选
  --------------------------------------------------------------------
| 需求 | 用哪种 | 怎么用 |
|---|---|---|
| 返回一个值 | 标量函数 | 用在 SELECT 列表、WHERE 里 |
| 返回一张表 | 表值函数 | 用在 FROM 后面 |
| 性能 | 标量慢 | 内联表值快，多语句慢 |

  第三行是重点：标量函数在 WHERE 里用，会导致「逐行调用」——一百万行就调一百万次。
  SQL Server 2019 之前，标量函数还被当成黑盒，优化器没法估算它的代价。
  所以「能用表值函数就用表值」——这句话在性能优化里非常实用。

  ④ 建、改、删三个动作
  --------------------------------------------------------------------
| 动作 | 怎么写 | 注意 |
|---|---|---|
| 建 | CREATE FUNCTION 名(...) | 已存在会报错 |
| 改 | ALTER FUNCTION 名(...) | 不存在会报错 |
| 删 | DROP FUNCTION 名 | 不存在会报错 |

  改函数只能 ALTER，没有 CREATE OR REPLACE（那是 Oracle 的写法）。
  所以脚本里改函数，常见写法是「先 DROP IF EXISTS 再 CREATE」。
  SQL Server 2016 起支持 DROP FUNCTION IF EXISTS，之前的版本得先查存在性再删。

这段是真跑的。 create_function 把 Python 的 fn_level 注册成 SQL 函数， 之后 SELECT fn_level(score) 就能直接用——跟 T-SQL 建函数的调用方式一模一样。 所以「自定义函数怎么建、怎么调」这件事，演示是到位的。差别只在函数体： T-SQL 版用 IF...ELSE 加 RETURN，Python 版就是普通函数。 注意 if s is None: return None 那一行。 T-SQL 版没写这句—— 因为 T-SQL 里 NULL 参与比较会返回未知，IF @s >= 90 直接就是假， 走到最后 ELSE 返回「不及格」。两边对 NULL 的处理不一样，这是个真实的差异。 表值函数那段只能贴原码。 SQLite 没有表值函数， 演示里用 Python 的列表推导复刻——复刻的是「返回一个结果集」这个行为，不是语法。

30 秒实验

1. 写一个标量函数，传日期返回「星期几」，然后在 WHERE 里用它筛「周末的订单」。 跑一次执行计划，看它是不是全表扫——这就是「标量函数在 WHERE 里」的代价。
2. 把同一个逻辑写成内联表值函数，再跑一次计划。 这次它应该能展开，用上索引——两者的差别在计划里看得清清楚楚。
3. 想一想：为什么函数不能改表？ 因为它要能被优化器当成表达式处理。 一旦能改数据，就没法保证「同样输入同样输出」， 也就没法优化了。

常见误区

1. 「自定义函数跟存储过程差不多」。差很多。 函数不能改表、不能开事务、不能调过程，而且能出现在 SELECT 里。 要改数据就得用存储过程。
2. 「标量函数写在 WHERE 里很方便」。方便但慢。 逐行调用，一百万行调一百万次，而且索引用不上。 能展开成普通条件的，就别用函数。
3. 「改函数用 CREATE OR REPLACE」。那是 Oracle 的写法， SQL Server 只认 ALTER。脚本里要通用，就写「先 DROP 再 CREATE」，中间别忘了 GO。

第 7 章到此结束。下一篇进第 8 章 8.1a，开始讲查询：SELECT...FROM 是 SQL 里用得最多的一条， 而 INTO 子句能把查询结果直接存成一张新表——这是做数据备份最快的方式。

• ☐ （本篇完，下一篇 8.1a SELECT...FROM 与 INTO）

相关笔记

• 系统函数
• 数学函数
• 48 课 MySQL从入门到精通 · 索引
• 🪟 SQL Server从入门到精通 · 索引