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.
- -- Some Silly Optimization Tests
- -- Glenn Berry
- -- June 2010
- -- http://glennberrysqlperformance.spaces.live.com/
- -- Twitter: GlennAlanBerry
- -- Note: These scripts only work on SQL Server 2008 and 2008 R2
- -- since I am doing things like declaring and assigning a value
- -- to a variable in a single line and using the += operator
- -- My timings were on an Intel 2.83GHz Core2 Quad Q9550
- -- Assignment Tests *******************************************************
- -- Assign a value to char(3)
- DECLARE @StringValue char(3);
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @StringValue = 'abc';
- SET @IterationCount += 1;
- END
- -- 966ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [char(3) Elapsed Time (ms)];
- GO
- -- Assign a value to varchar(8000)
- DECLARE @StringValue varchar(8000);
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @StringValue = 'abc';
- SET @IterationCount += 1;
- END
- -- 966ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(8000) Elapsed Time (ms)];
- GO
- -- Assign a value to nvarchar(4000)
- DECLARE @StringValue nvarchar(4000);
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @StringValue = N'abc';
- SET @IterationCount += 1;
- END
- -- 970ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [nvarchar(4000) Elapsed Time (ms)];
- GO
- -- Assign a value to char(8000)
- DECLARE @StringValue char(8000);
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @StringValue = 'abc';
- SET @IterationCount += 1;
- END
- -- 1670ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [char(8000) Elapsed Time (ms)];
- GO
- -- Assign a value to varchar(MAX)
- DECLARE @StringValue varchar(MAX);
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @StringValue = 'abc';
- SET @IterationCount += 1;
- END
- -- 2380ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(MAX) Elapsed Time (ms)];
- GO
- -- End of Assignment Tests
- -- Comparision Tests **************************************
- -- Char (3)
- DECLARE @Result bit = 0;
- DECLARE @StringValue char(3) = 'abc';
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @Result = CASE
- WHEN @StringValue = 'abc' THEN 1
- ELSE 0
- END;
- SET @IterationCount += 1;
- END
- -- 1170ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [char(3) Elapsed Time (ms)];
- GO
- -- Varchar (8000)
- DECLARE @Result bit = 0;
- DECLARE @StringValue varchar(8000) = 'abc';
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @Result = CASE
- WHEN @StringValue = 'abc' THEN 1
- ELSE 0
- END;
- SET @IterationCount += 1;
- END
- -- 1200ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [varchar(8000) Elapsed Time (ms)];
- GO
- -- varchar(MAX)
- DECLARE @Result bit = 0;
- DECLARE @StringValue varchar(MAX) = 'abc';
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @Result = CASE
- WHEN @StringValue = 'abc' THEN 1
- ELSE 0
- END;
- SET @IterationCount += 1;
- END
- -- 2106ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [varchar(MAX) Elapsed Time (ms)];
- GO
- -- Caution: This will take a long time!
- -- Char (8000)
- DECLARE @Result bit = 0;
- DECLARE @StringValue char(8000) = 'abc';
- DECLARE @IterationCount int = 0;
- DECLARE @StartTime datetime = GETDATE();
- WHILE @IterationCount < 1000000
- BEGIN
- SET @Result = CASE
- WHEN @StringValue = 'abc' THEN 1
- ELSE 0
- END;
- SET @IterationCount += 1;
- END
- -- 317333ms
- SELECT DATEDIFF(ms, @StartTime, GETDATE())AS [char(8000) Elapsed Time (ms)];
- GO
- -- End of Comparision Tests
