The impact of temporary files generated by SQL queries on database queries

When a query is made using an SQL statement, the result of the query is stored in a temporary file with a suffix named tmp. When the query, the file holds the results of the query, when the query is closed, the temporary file will be automatically deleted, so in our query, it is not feel the existence of the file.

Where does the TMP file exist

For Windows systems, there is a system environment variable that can be passed

Right-click My Computer--Properties--advanced--environment variables can be viewed.

The TMP temporary file exists under the Temp folder.

Second, TMP file format

For the TMP file, where the query results are stored, the storage of the query results in the TMP file is based on the

A format for storing, stored in a format that is:

For each column, it is divided into two parts:

The first part: the length of the column is marked, and when the length of the character is greater than 255, 5 bytes are used to store it.

Part Two: The data for this column. For character data, it is converted to Uniocode for storage.

Select 1,cast (1 as bigint), ' AB ', GETDATE () The TMP file generated by the query is (16 binary) C2 7a 7b CB 08, at the same of the same (at the same-) of which 04 01 00 00 00 is the first 1 08 01 00 00 00 00 00 00 00 for the second bigint type 1 04 61 00 62 00 for ' AB ' C2 7a 7b CB 08 for GETDATE ()

For example: For a table like this:

The TMP file format that is formed using SELECT * from TB is:

id column data length

< Span lang= "en-US" >id column data

dtcol column data length

column data

charcol data length

charcol data

maxcol column data length

maxcol The data

Where Charcol and Maxcol are converted to Unicode for storage.

For the view of the TMP file, you can view it by Notepad, but only the characters, the numbers, the date is garbled, you can use the UE to view the binary data of the TMP file.

Three, insert 100W data into the table

declare int @dt @i font> select @i=0, @dt = '!--? xml:namespace PR Efix = st1/--> ' begin     Insert into TestData (dtcol,charcol,maxcol)    values (@dt +@i,replicate char (rand () *26+65), Replicate (newid (), 100))    set @i=@i+1 end

The effect of TMP file on queries

After you know the format of the TMP file, the size of the TMP file is generally estimated, and the above

For example, the size of a row in the TMP file is: 1+8+1+8+1+200+5+7200=7424b,100w data is approximately 7424*100wb,tmp file size 7,250,000kb or so.

1, when the table of more data, especially the character type of the majority of data, you need to pay attention to this TMP file. If the Temp folder does not have a rich disk space, then TMP takes up the remaining disk space and is not enough, then the system prompts for insufficient space and terminates the query.

2, the disk of the Temp folder is the best disk format for NTFS, because the FAT32 format is the largest file size of 4G, when the size of the TMP file is more than 4G, then will not produce a new TMP file, then the system will not be prompted enough, and terminate this query. (Not enough space is not enough disk space, but because the TMP file has reached the maximum capacity of 4G)

To sum up: the Temp folder should be placed on a partition that has sufficient disk space and is formatted in NTFS format.