Blog Post

Comparing the Performance of Different String Data Types in SQL Server 2008 R2

,

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. -- Some Silly Optimization Tests
  2. -- Glenn Berry
  3. -- June 2010
  4. -- http://glennberrysqlperformance.spaces.live.com/
  5. -- Twitter: GlennAlanBerry
  6. -- Note: These scripts only work on SQL Server 2008 and 2008 R2
  7. -- since I am doing things like declaring and assigning a value
  8. -- to a variable in a single line and using the += operator
  9. -- My timings were on an Intel 2.83GHz Core2 Quad Q9550
  10. -- Assignment Tests *******************************************************
  11. -- Assign a value to char(3)
  12. DECLARE @StringValue char(3);
  13. DECLARE @IterationCount int = 0;
  14. DECLARE @StartTime datetime = GETDATE();
  15. WHILE @IterationCount < 1000000
  16.     BEGIN
  17.       SET @StringValue = 'abc';
  18.       SET @IterationCount += 1;
  19.     END
  20. -- 966ms
  21. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [char(3) Elapsed Time (ms)];
  22. GO
  23. -- Assign a value to varchar(8000)
  24. DECLARE @StringValue varchar(8000);
  25. DECLARE @IterationCount int = 0;
  26. DECLARE @StartTime datetime = GETDATE();
  27. WHILE @IterationCount < 1000000
  28.     BEGIN
  29.       SET @StringValue = 'abc';
  30.       SET @IterationCount += 1;
  31.     END
  32. -- 966ms
  33. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(8000) Elapsed Time (ms)];
  34. GO
  35. -- Assign a value to nvarchar(4000)
  36. DECLARE @StringValue nvarchar(4000);
  37. DECLARE @IterationCount int = 0;
  38. DECLARE @StartTime datetime = GETDATE();
  39. WHILE @IterationCount < 1000000
  40.     BEGIN
  41.       SET @StringValue = N'abc';
  42.       SET @IterationCount += 1;
  43.     END
  44. -- 970ms
  45. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [nvarchar(4000) Elapsed Time (ms)];
  46. GO
  47. -- Assign a value to char(8000)
  48. DECLARE @StringValue char(8000);
  49. DECLARE @IterationCount int = 0;
  50. DECLARE @StartTime datetime = GETDATE();
  51. WHILE @IterationCount < 1000000
  52.     BEGIN
  53.       SET @StringValue = 'abc';
  54.       SET @IterationCount += 1;
  55.     END
  56. -- 1670ms
  57. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [char(8000) Elapsed Time (ms)];
  58. GO
  59. -- Assign a value to varchar(MAX)
  60. DECLARE @StringValue varchar(MAX);
  61. DECLARE @IterationCount int = 0;
  62. DECLARE @StartTime datetime = GETDATE();
  63. WHILE @IterationCount < 1000000
  64.     BEGIN
  65.       SET @StringValue = 'abc';
  66.       SET @IterationCount += 1;
  67.     END
  68. -- 2380ms
  69. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(MAX) Elapsed Time (ms)];
  70. GO
  71. -- End of Assignment Tests
  72. -- Comparision Tests **************************************
  73. -- Char (3)
  74. DECLARE @Result bit = 0;
  75. DECLARE @StringValue char(3) = 'abc';
  76. DECLARE @IterationCount int = 0;
  77. DECLARE @StartTime datetime = GETDATE();
  78. WHILE @IterationCount < 1000000
  79.     BEGIN
  80.       SET @Result = CASE
  81.                         WHEN @StringValue = 'abc' THEN 1
  82.                         ELSE 0
  83.                     END;
  84.       SET @IterationCount += 1;
  85.     END
  86. -- 1170ms
  87. SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [char(3) Elapsed Time (ms)];
  88. GO
  89. -- Varchar (8000)
  90. DECLARE @Result bit = 0;
  91. DECLARE @StringValue varchar(8000) = 'abc';
  92. DECLARE @IterationCount int = 0;
  93. DECLARE @StartTime datetime = GETDATE();
  94. WHILE @IterationCount < 1000000
  95.     BEGIN
  96.       SET @Result = CASE
  97.                         WHEN @StringValue = 'abc' THEN 1
  98.                         ELSE 0
  99.                     END;
  100.       SET @IterationCount += 1;
  101.     END
  102. -- 1200ms
  103. SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [varchar(8000) Elapsed Time (ms)];
  104. GO
  105. -- varchar(MAX)
  106. DECLARE @Result bit = 0;
  107. DECLARE @StringValue varchar(MAX) = 'abc';
  108. DECLARE @IterationCount int = 0;
  109. DECLARE @StartTime datetime = GETDATE();
  110. WHILE @IterationCount < 1000000
  111.     BEGIN
  112.       SET @Result = CASE
  113.                         WHEN @StringValue = 'abc' THEN 1
  114.                         ELSE 0
  115.                     END;
  116.       SET @IterationCount += 1;
  117.     END
  118. -- 2106ms
  119. SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(MAX) Elapsed Time (ms)];
  120. GO
  121. -- Caution: This will take a long time!
  122. -- Char (8000)
  123. DECLARE @Result bit = 0;
  124. DECLARE @StringValue char(8000) = 'abc';
  125. DECLARE @IterationCount int = 0;
  126. DECLARE @StartTime datetime = GETDATE();
  127. WHILE @IterationCount < 1000000
  128.     BEGIN
  129.       SET @Result = CASE
  130.                         WHEN @StringValue = 'abc' THEN 1
  131.                         ELSE 0
  132.                     END;
  133.       SET @IterationCount += 1;
  134.     END
  135. -- 317333ms
  136. SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [char(8000) Elapsed Time (ms)];
  137. GO
  138. -- End of Comparision Tests

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating