In SQL Server we use varchar, varbinary to save disk space in our tables. But its not saving data always as it sounds. When the data stores it takes few extra bytes to describe the data length. Because of that there is no gain using varchar(2). If you use varchar(2) it will take 4 or 3 bytes to store data. varchar(8000) or below should have 2Byte offset to store the variability of character count. If you are using varchar(max) is different. It should have 4 Bytes offset. But I’m still looking for that to confirm it takes 4 Bytes. And it is stored in Variable Column Offset Array.
Yesterday I wrote a post with same title. In that post I mentioned using char(1) is also same like using one bit column in table. But it is from the perspective of storage space. But using numbers is less burden to Database engine. It means using 1 and 0 is better than using ‘Y’ and ‘N’. Also in comparing data or in sorting data it is easier.
If you are storing numbers as a text its not a good thing although you don’t do any mathematical operation with that text. First thing it takes more space. It takes 1 byte for every character, while integers take very less. And second thing if you store them as string it will have extra cost to manipulate, compare, sort, whatever you are going to do with them.
If you have Yes or No data to store in DB, what will you select as the data type? Most of the times you will select “Bit”. Because it only takes 1 bit to store data. And it is the best data type for this situation. But it takes more than bit in SQL Server. If you have only one bit column in your table it will allocate 7 more extra bits to store your bit data.
And if you have two bit typed fields in your table it will waste 6 more bytes. But if you are using 8 bit typed fields in your table, it will not waste any space. And if you have only one such a binary values column in your table. Otherwise it will take space same as char(1) or tinyint. Although its name is bit it is not small like bit always.
I wanted to learn about data types. Then I went to technet some how most of the important things were there in technet documentations. But I found another way to get figures from SQL Server. We can have details of data types from “sphelp”. You just have to give your data type as a parameter like below.
EXEC sp_help int
Then I executed this to every data type except few system data types. And I listed figures on categories which was in technet.
Date and Time
Unicode Character Strings
Other Data Types
If I haven’t specified any attribute of those data types usually they have their default values. One special thing was there were only one data type which was not nullable. It is “timestamp”. And I was unable to take data of cursor and table from this method.