Queries in Inquiries

Menu

Tag Archives: Data Types

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.

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.