Problem
Can the use of bit data types in SQL Server improve query efficiency? Is the bit data type efficient? What are the performance implications for a bit, varchar or integer data type?
Solution
In a previous tip, I presented the T-SQL Bitwise Operators. One of the comments was a request to examine if query efficiency can be improved when using bit data types. In this tip we will examine the performance of different data types in the WHERE clause of a query.
For this tip, I created a table using the T-SQL below.
--====================================================== --Create our example table --====================================================== create table tblPerformanceTest ( pKey integer identity(1,1) Primary Key, dollarAmount1 decimal(5,2), dollarAmount2 decimal(5,2), dollarAmount3 decimal(5,2), charFlag varchar(1), intFlag integer, bitFlag bit, lastUpdated datetime, UpdatedBy varchar(128) )
Next, the table was populated using the T-SQL below.
declare @i integer=1
begin transaction
while @i<=5000000
begin
insert into tblPerformanceTest --1
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --2
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --3
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --4
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --5
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --6
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --7
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --8
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --9
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'Y', 1, 'TRUE', getdate(), suser_sname())
insert into tblPerformanceTest --10
values (dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(), dbo.myRandomNumberFunction(),
'N', 0, 'FALSE', getdate(), suser_sname())
set @i=@i+1
if (@i%1000=0)
begin
commit
begin transaction
end
end
commit
For each iteration of the loop, the code below will insert 9 true records and 1 false record. The terminating value for the loop was first set at 1 million and then increased in increments of 1 million up to 5 million. After each execution of the above code, the queries below were executed 10 times with the elapsed time for each query recorded. Each query counted 10 percent of the records in the table. The first query uses the integer column in the WHERE clause. The second query uses the varchar column in the WHERE clause and the third query uses the bit column.
Please note, the code for myRandomNumberFunction() can be found at this tip: A More Versatile SQL Server Random Number Function.
select count(*) as intCount from dbo.tblPerformanceTest where intFlag=0 select count(*) as charCount from dbo.tblPerformanceTest where charFlag='N' select count(*) as bitCount from dbo.tblPerformanceTest where bitFlag=0
The average elapsed times for each query for each volume of records are displayed below. The integer data type in the WHERE clause provides the best performance, with the bit data type next and the varchar data type last.
The elapsed times are visualized in the chart below. Notice the linear relationship for each data type.
Next Steps
The above queries used no indexing on the column used in the WHERE clause, so there is room for improvement.
Check out these other tips and tutorials on T-SQL on MSSQLTips.com.
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida. He received his Ph.D. in Integrated Computing and M.S. in Instrumental Sciences from the University of Arkansas at Little Rock. He received a B.A. in Physics from Hendrix College. Before joining UWF, he worked as a data warehouse developer for Northrop Grumman Information Systems and prior to that as a database application developer for Acxiom and Euronet. Dr. Snider’s teaching and research interests include data mining, data warehousing, information visualization, and software development.
