Does it make a difference in SQL Server whether to use a TinyInt or Bit? Both in size and query performance

Question

I have a table that has 124,387,133 rows each row has 59 columns and of those 59, 18 of the columns are TinyInt data type and all row values are either 0 or 1. Some of the TinyInt columns are used in indexes and some are not.

My question will it make a difference on query performance and table size if I change the tinyint to a bit?

Answer 1

In case you don't know, a bit uses less space to store information than a TinyInt (1 bit against 8 bits). So you would save space changing to bit, and in theory the performance should be better. Generally is hard to notice such performance improvement but with the amount of data you have, it might actually make a difference, I would test it in a backup copy.

Answer 2

Actually,it's good to use the right data type..below are the benefits i could see when you use bit data type

1.Buffer pool savings,page is read into memory from storage and less memory can be allocated

2.Index key size will be less,so more rows can fit into one page and there by less traversing

Also you can see storage space savings as immediate benefit