sqlserver存储过程中使用临时表的问题

2023年6月6日08:52:15

因为最近接触的his系统一些存储过程做数据统计，一个存储过程就要使用1-3个临时表，这些存储过程是零几年的写得，和我们这个时代的写的存储过程习惯不太一样，就好奇为什么要使用这么多的临时表

临时表的基本概念

　　在深入临时表之前，我们要了解一下会话(Session)，一个会话仅仅是一个客户端到数据引擎的连接。在SQL Server Management Studio中，每一个查询窗口都会和数据库引擎建立连接。一个应用程序可以和数据库建立一个或多个连接，除此之外，应用程序还可能建立连接后一直不释放知道应用程序结束，也可能使用完释放连接需要时建立连接。

　　临时表和Create Table语句创建的表有着相同的物理工程，但临时表与正常的表不同之处有：

　　1、临时表的名称不能超过116个字符，这是由于数据库引擎为了辨别不同会话建立不同的临时表，所以会自动在临时表的名字后附加一串。

　　2、局部临时表(以"#"开头命名的)作用域仅仅在当前的连接内，从在存储过程中建立局部临时表的角度来看，局部临时表会在下列情况下被Drop：
　　　　a、显示调用Drop Table语句
　　　　b、当局部临时表在存储过程内被创建时，存储过程结束也就意味着局部临时表被Drop。
　　　　c、当前会话结束，在会话内创建的所有局部临时表都会被Drop。

　　3、全局临时表(以"##"开头命名的)在所有的会话内可见，所以在创建全局临时表之前首先检查其是否存在，否则如果已经存在，你将会得到重复创建对象的错误。
　　　　a、全局临时表会在创建其的会话结束后被Drop，Drop后其他会话将不能对全局临时表进行引用。
　　　　b、引用是在语句级别进行，如:
　　　　　　1.新建查询窗口，运行语句：

　　CREATE TABLE ##temp(RowID int)
　　INSERT INTO ##temp VALUES(3)

　　2.再次新建一个查询窗口，每5秒引用一次全局临时表

　　While 1=1 
　　BEGIN
　　SELECT * FROM ##temp
　　WAITFOR delay '00:00:05'
　　END

　　　　　　3.回到第一个窗口，关闭窗口。
　　　　　　4.下一次第二个窗口引用时，将产生错误。

　　4、不能对临时表进行分区。

　　5、不能对临时表加外键约束。

　　6、临时表内列的数据类型不能定义成没有在TempDb中没有定义自定义数据类型(自定义数据类型是数据库级别的对象，而临时表属于TempDb)。由于TempDb在每次SQL Server重启后会被自动创建，所以你必须使用startup stored procedure来为TempDb创建自定义数据类型。你也可以通过修改Model数据库来达到这一目标。

　　7、XML列不能定义成XML集合的形式，除非这个集合已经在TempDb中定义。

　　临时表既可以通过Create Table语句创建，也可以通过"SELECT <select_list> INTO #table"语句创建。你还可以针对临时表用"INSERT INTO #table EXEC stored_procedure"这样的语句。
　　临时表可以拥有命名的约束和索引。但是，当两个用户在同一时间调用同一存储过程时，将会产生”There is already an object named <objectname> in the database”这样的错误。所以最好的做法是不用为建立的对象进行命名，而使用系统分配的在TempDb中唯一的。

优点：

临时表可以提高查询性能：使用临时表可以减少重复的子查询和表连接，从而提高查询性能。
可以使用复杂的查询语句：临时表可以存储复杂的查询语句的结果，并将其用于后续的查询，从而使查询语句更简单。
可以缓存查询结果：如果多次执行相同的查询，使用临时表可以缓存查询结果并加快查询速度。
可以实现数据隔离：临时表可以使得不同的用户或连接在同一个数据库中使用不同的数据，从而实现数据隔离。

缺点：

临时表会占用内存和磁盘空间：使用临时表时需要占用内存和磁盘空间，如果临时表中存储的数据过多，可能会导致系统性能下降。
临时表不适用于高并发环境：临时表通常只适用于当前连接或事务中使用，如果在高并发环境下使用，可能会导致性能问题。
临时表可能会引起命名冲突：如果多个用户或连接在同一个数据库中使用临时表，可能会导致命名冲突。
临时表不支持跨数据库或跨服务器使用：临时表只能在当前连接或事务中使用，不支持跨数据库或跨服务器使用。

综上所述，SQL 临时表可以提高查询性能和数据隔离，但也会占用系统资源，并且不适用于高并发环境。在使用临时表时需要根据具体情况综合考虑其优缺点。

个人感受：
1，临时表可以简化复杂一些复杂sql的编写
2，减少锁影响
3，不适合互联网服务改造，因为现在his系统大多数都是需要支持互联网化的，很容易吧内存，硬盘io，cpu跑满

引用：
https://www.cnblogs.com/kissdodog/p/3169470.html
https://learn.microsoft.com/zh-cn/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-ver16

QQ群 166427999 （新群）
博客文件如果不能下载请进群下载
如果公司项目有技术瓶颈问题，请联系QQ
如果需要定制系统开发服务，请联系QQ
技术服务QQ: 903464207