T-SQL String Veri Tipleri ve SQL Server 2008 R2 Üzerinde Performans Kıyaslaması

Character Strings

• char Definition: Fixed-Length character string.
  Declaration: char(n).
  n = the number of characters.
  
  Space: n number of bytes
  
  Valid lengths for a char datatype are 1 through 8,000.
• varchar Definition: Variable-Length character string.
  Declaration: varchar(n | max).
  n = the number of characters.
  
  Space: characters actually used in datatype (1 byte per character) + 2 additional bytes
  
  Valid lengths for a varchar datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 characters).
• text Definition: Variable-Length character string in the code page of the server.
  
  Maximum length is 2,147,483,647 characters.
  Note: will be removed in future versions. Use varchar(max) instead.

Unicode Character Strings

• nchar Definition: Fixed-Length Unicode character string.
  Declaration: nchar(n).
  n = the number of characters.
  
  Space: n * 2 number of bytes
  
  Valid lengths for a char datatype are 1 through 4,000.
• nvarchar Definition: Variable-Length Unicode character string.
  Declaration: nvarchar(n | max).
  n = the number of characters.
  
  Space: characters actually used in datatype (2 bytes per character) + 2 additional bytes
  
  Valid lengths for a varchar datatype are 1 through 4,000. Alternatively MAX indicates that the maximum storage size is much larger (2^31-1 bytes - 2,147,483,647 characters).
• ntext Definition: Variable-Length character string in the code page of the server.
  
  Maximum length is 1,073,741,823 characters.
  Note: will be removed in future versions. Use nvarchar(max) instead.

I thought I would try to do a few quick tests to compare the performance of assigning values and comparing values using some of the different string data types that you have available in SQL Server. It turns out that there is a significant difference in performance for these operations depending on the data type of the variable that you use. Using varchar(MAX) and char(8000) are much slower than using varchar(8000) or char(3).
Of course if you are writing T-SQL that is doing millions of loops, you are probably “thinking like a developer” instead of thinking like a good DBA. In my opinion, you really should not be writing T-SQL code that has lots of complicated business logic or that needs to do high numbers of loops. One exception to this would be if you are doing some sort of operation where you need to delete or update millions of rows in a table, but you can’t afford to take the concurrency hit that a single, set-based delete or update would cause.
It is much more important to match the data types that you use for input parameters and variables to the data type that you have for the corresponding column in your table or view, so you can avoid very expensive implicit data type conversions.

Code Snippet

1. — Note: These scripts only work on SQL Server 2008 and 2008 R2
2. — since I am doing things like declaring and assigning a value
3. — to a variable in a single line and using the += operator
5. — My timings were on an Intel 2.83GHz Core2 Quad Q9550
7. — Assignment Tests *******************************************************
9. — Assign a value to char(3)
10. DECLARE @StringValue char(3);
11. DECLARE @IterationCount int = 0;
12. DECLARE @StartTime datetime = GETDATE();
14. WHILE @IterationCount < 1000000
15.     BEGIN
16.       SET @StringValue = ‘abc’;
17.       SET @IterationCount += 1;
18.     END
20. – 966ms
21. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [char(3) Elapsed Time (ms)];
22. GO
25. – Assign a value to varchar(8000)
26. DECLARE @StringValue varchar(8000);
27. DECLARE @IterationCount int = 0;
28. DECLARE @StartTime datetime = GETDATE();
30. WHILE @IterationCount < 1000000
31.     BEGIN
32.       SET @StringValue = ‘abc’;
33.       SET @IterationCount += 1;
34.     END
36. – 966ms
37. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(8000) Elapsed Time (ms)];
38. GO
40. – Assign a value to nvarchar(4000)
41. DECLARE @StringValue nvarchar(4000);
42. DECLARE @IterationCount int = 0;
43. DECLARE @StartTime datetime = GETDATE();
45. WHILE @IterationCount < 1000000
46.     BEGIN
47.       SET @StringValue = N’abc’;
48.       SET @IterationCount += 1;
49.     END
51. – 970ms
52. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [nvarchar(4000) Elapsed Time (ms)];
53. GO
56. – Assign a value to char(8000)
57. DECLARE @StringValue char(8000);
58. DECLARE @IterationCount int = 0;
59. DECLARE @StartTime datetime = GETDATE();
61. WHILE @IterationCount < 1000000
62.     BEGIN
63.       SET @StringValue = ‘abc’;
64.       SET @IterationCount += 1;
65.     END
67. – 1670ms
68. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [char(8000) Elapsed Time (ms)];
69. GO
71. – Assign a value to varchar(MAX)
72. DECLARE @StringValue varchar(MAX);
73. DECLARE @IterationCount int = 0;
74. DECLARE @StartTime datetime = GETDATE();
76. WHILE @IterationCount < 1000000
77.     BEGIN
78.       SET @StringValue = ‘abc’;
79.       SET @IterationCount += 1;
80.     END
82. – 2380ms
83. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(MAX) Elapsed Time (ms)];
84. GO
85. – End of Assignment Tests
87. — Comparision Tests **************************************
89. — Char (3)
90. DECLARE @Result bit = 0;
91. DECLARE @StringValue char(3) = ‘abc’;
92. DECLARE @IterationCount int = 0;
93. DECLARE @StartTime datetime = GETDATE();
96. WHILE @IterationCount < 1000000
97.     BEGIN
98.       SET @Result = CASE
99.                         WHEN @StringValue = ‘abc’ THEN 1
100.                         ELSE 0
101.                     END;
102.       SET @IterationCount += 1;
103.     END
105. – 1170ms
106. SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [char(3) Elapsed Time (ms)];
107. GO
109. – Varchar (8000)
110. DECLARE @Result bit = 0;
111. DECLARE @StringValue varchar(8000) = ‘abc’;
112. DECLARE @IterationCount int = 0;
113. DECLARE @StartTime datetime = GETDATE();
116. WHILE @IterationCount < 1000000
117.     BEGIN
118.       SET @Result = CASE
119.                         WHEN @StringValue = ‘abc’ THEN 1
120.                         ELSE 0
121.                     END;
122.       SET @IterationCount += 1;
123.     END
125. – 1200ms
126. SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [varchar(8000) Elapsed Time (ms)];
127. GO
130. – varchar(MAX)
131. DECLARE @Result bit = 0;
132. DECLARE @StringValue varchar(MAX) = ‘abc’;
133. DECLARE @IterationCount int = 0;
134. DECLARE @StartTime datetime = GETDATE();
137. WHILE @IterationCount < 1000000
138.     BEGIN
139.       SET @Result = CASE
140.                         WHEN @StringValue = ‘abc’ THEN 1
141.                         ELSE 0
142.                     END;
143.       SET @IterationCount += 1;
144.     END
146. – 2106ms
147. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(MAX) Elapsed Time (ms)];
148. GO
150. – Caution: This will take a long time!
151. — Char (8000)
152. DECLARE @Result bit = 0;
153. DECLARE @StringValue char(8000) = ‘abc’;
154. DECLARE @IterationCount int = 0;
155. DECLARE @StartTime datetime = GETDATE();
158. WHILE @IterationCount < 1000000
159.     BEGIN
160.       SET @Result = CASE
161.                         WHEN @StringValue = ‘abc’ THEN 1
162.                         ELSE 0
163.                     END;
164.       SET @IterationCount += 1;
165.     END
167. – 317333ms
168. SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [char(8000) Elapsed Time (ms)];
169. GO
170. – End of Comparision Tests