浅析SQL查询时生成的临时文件对数据库查询的影响

浅析SQL查询时生成的临时文件

当在sql server managerment studio中进行查询使用sql语句进行查询时，查询的结果是存放在一个后缀名为tmp的一个临时文件中。当查询的时候，该文件存放查询的结果，当关闭该查询的时候，该临时文件会自动删除，所以在我们进行查询的时候，是感觉不到该文件的存在。

环境：WINXP（SP3）+MSSQL 2005（SP3）

一、tmp文件存在于什么地方

对于windows系统，都有一个系统环境变量，这个变量可以通过

右击我的电脑——属性——高级——环境变量可以查看。

Tmp临时文件就存在于temp文件夹下面。

二、tmp文件的格式

对于tmp文件，里面存放的查询结果，那么对于查询结果在tmp文件中的存储是按照

一定格式来进行存储的，存储的格式为：

对于每一列，分成两部分：

第一部分：标示该列的长度，当字符的长度大于255时，使用5个字节来存放。

第二部分：该列的数据。对于字符型数据，是转化为uniocode来进行存储的。

select 1,cast(1 as bigint),'ab',getdate()

查询生成的tmp文件为(16进制)

04 01 00 00 00 08 01 00 00 00 00 00 00 00 04 61 00 62 00 08 60 73 c2 02 7a 7b cb 08

其中04 01 00 00 00为第一个1

    08 01 00 00 00 00 00 00 00为第二个bigint类型的1

    04 61 00 62 00  为’ab’

    08 60 73 c2 02 7a 7b cb 08 为getdate（）

例如：对于这样一张表：

 

使用select * from tb 形成的tmp文件格式为

id列的数据长度

id列的数据

dtcol列的数据长度

dtcol列的数据

charcol的数据长度

charcol的数据

maxcol列的数据长度

maxcol的数据

其中charcol与maxcol都是转换为unicode来进行存储的。

对于tmp文件的查看，可以通过记事本来查看，但是只能看到字符，对于数字、日期看到的是乱码，可以使用UE来查看tmp文件的二进制数据。

三、向该表中插入100W数据

    declare @i int @dt datetime

select @i=0,@dt='1900-1-1'

while @i<1000000

begin

   insert into testdata(dtcol,charcol,maxcol)

   values(@dt+@i,replicate(char(rand()*26+65),100),replicate(newid(),100))

   set @i=@i+1

end

四、tmp文件对查询的影响

在知道了tmp文件的格式之后，那么对于tmp文件的大小一般是能估算出来的，以上

述表为例，一行在tmp文件中的大小为：1+8+1+8+1+200+5+7200=7424B，100W的数据大约是7424*100WB，tmp文件的大小为7,250,000KB左右。

1、当表中的数据比较多的话，尤其是字符类型的数据占多数的时候，就需要注意这个tmp文件了。如果temp文件夹所在的磁盘空间不富裕的话，那么tmp会占用剩余的磁盘空间，还不够的话，那么系统会提示空间不够，并且会终止本次查询。

2、temp文件夹所在的磁盘的磁盘格式最好为NTFS，因为FAT32格式最大的文件大小为4G，当tmp文件的大小超过4G时，那么是不会产生新的tmp文件的，那么系统也会提示空间不够，并且终止本次查询。（空间不够未必是磁盘空间不够，而是因为tmp文件已经达到最大容量4G）

综上所述：temp文件夹应该放在磁盘空间充足的，并且分区格式为ntfs格式的分区上。