How varchar data is stored?

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.

062911_1840_Themysteryo1

Picture: http://www.sqlservercentral.com/blogs/aschenbrenner/2011/06/29/the-mystery-of-the-null-bitmap-mask/

Are you going to save space using Bit Data Type? – Part 2

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.

Are you going to save space using Bit Data Type?

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.

Data Type details from sphelp

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.

Exact Numerics

Type Name Length Precision Scale Nullable
bigint 8 19 0 Y
bit 1 1 Null Y
decimal 17 38 38 Y
int 4 10 0 Y
money 8 19 4 Y
numeric 17 38 38 Y
smallint 2 5 0 Y
smallmoney 4 10 4 Y
tinyint 1 3 0 Y

Approximate Numerics

Type Name Length Precision Scale Nullable
float 8 53 Null Y
real 4 24 Null Y

Date and Time

Type Name Length Precision Scale Nullable
date 3 10 0 Y
datetime2 8 27 7 Y
datetime 8 23 3 Y
datetimeoffset 10 34 7 Y
smalldatetime 4 16 0 Y
time 5 16 7 Y
Binary Strings
 
Type Name Length Precision Scale Nullable
binary 8000 8000 Null Y
image 16 2147483647 Null Y
varbinary 8000 8000 Null Y

Character Strings

Type Name Length Precision
char 8000 8000
varchar 8000 8000
text 16 2147483647

Unicode Character Strings

Type Name Length Precision
nchar 8000 4000
nvarchar 8000 4000
ntext 16 1073741823

Other Data Types

Type Name Length Precision Nullable
hierarchyid 892 892 Y
sql_variant 8016 0 Y
timestamp 8 8 N
uniqueidentifier 16 16 Y
xml -1 -1 Y

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.