Is a BIT field faster than an int field in SQL Server?

Question

I have a table with some fields whose value will be 1 0. These tables will be extremely large overtime. Is it good to use a data bit type or is it better to use a different type for performance? Of course, all fields must be indexed.

Answer 1

Officially, the bit will be the fastest, especially if you did not allow null. In practice, this can make a difference even with big customs. But if the value is only 0 or 1, why not use a bit? This seems like the best way to make sure the value is not filled with invalid data, like 2 or -1.

Answer 2

I cannot give you any performance statistics, however you should always use the type that best reflects your data. If all you need is 1-0, then you should use the bit field.

The more information you can provide to your database, the more likely it is to "guess" correctly.

Answer 3

As I understand it, you still need a byte to store the bit column (but you can store 8-bit columns in one byte). Thus, the presence of a large number (how many?) Of these columns of bits can save a little on storage. As Yishai said that this probably will not make much difference in performance (although the bit will be more logical in the application logic code).

If you can say with 100% certainty that the two parameters for this column will NEVER change, use a bit anyway. But if you can see the third meaning that appears in the future, it can make life easier when this day starts using tinyint.

Just a thought, but I'm not sure how good a index will make you in this column, unless you see that the vast majority of rows go in one direction or another. In a distribution of approximately 50/50, you can get more hits by keeping the index up to date than you can get when querying a table.

Answer 4

It depends.

If you want to maximize the selection speed, use int (tinyint to save space), because the bit in the where clause is slower than int (not radically, but every millisecond counts). Also make the column non-null, which also speeds up the process. Below is a link to a real performance test, which I would recommend running in my own database, and also expand it using non-zero, indexes and using multiple columns at the same time. At home, I even tried to compare using multiple bit columns versus several tinyint columns, and tinyint columns were faster ( select count(*) where A=0 and B=0 and C=0 ). I thought SQL Server (2014) would be optimized by performing only one comparison using a bitmask, so it should be three times faster, but that is not the case. If you use indexes, you will need more than 5,000,000 rows (as in the test) to notice the difference (which I didn’t have enough patience for, since filling out a table with several million rows would take a lot of time on my machine).

https://www.mssqltips.com/sqlservertip/4137/sql-server-performance-test-for-bit-data-type-in-a-where-clause/

If you want to save space, use a bit, since 8 of them can occupy one byte, while 8 tiny ones will occupy 8 bytes. Which is about 7 megabytes for every million lines.

The differences between the two cases are mostly minor, and since using a bit takes precedence over a signal that the column is just a flag, I would recommend using a bit.