Most commonly used SQL Server Data Types

In our previous blog, we have discussed the various SQL Server Data Types categories.

In this blog, we are going to discuss the very first category i.e., Exact Numeric Data Types.

I. Exact Numeric Data Types: - are those that use integer data. An integer is a number with no fractional part; it can be positive, negative or zero.

The following table lists the Exact Numeric Data Types in SQL Server with minimum and maximum range value: -

 

Exact Numeric Data Types

Things to Remember:

1. If we convert string value ‘true’ or ‘false’ to bit data type, then ‘true’ is converted to 1 and ‘false’ is converted to 0. But, if we take string value other than ‘true’ or ‘false’ then it may result in throwing an error “Conversion failed when converting the varchar value 'abc' to data type bit.”

select CONVERT(bit,'true') as Out1, CONVERT(bit,'false') as Out2

 

Output Window

select CONVERT(bit,'abc') as Out3, CONVERT(bit,'vr') as Out4

 

Output Window

2. If we convert any number either +ve or -ve except zero to bit data type is result to 1.

select CONVERT(bit,45) as Out5, CONVERT(bit,-84) as Out6

 

Output Window

3. If we convert ‘money’ data type value to any ‘int’ data type (i.e., int, bigint, tinyint, smallint, decimal, numeric) value, then only the real part of monetary unit is considered.

declare @myaccountmoney money='546.23'

select CONVERT(numeric,@myaccountmoney) as outM1,

CONVERT(decimal,@myaccountmoney) as outM2

 

Output Window

4. Storage space or the number of digits it may hold is a very important thing. You may understand this by reading the next two lines. If you unknowingly convert a ‘decimal’ type number to ‘int’ type number then the extra digits will be truncated, which is not good. This result in heavy data lost.

5. Choosing right data type for each column reduce backup time, improve SQL Server performance and improve the execution time for queries, views, joins.

Now the question comes to our mind. How to use these Data Type?

Let’s understand and learn how to use these data types in some real situation. The Situation is something like - where we have to create a table for banks to maintain every person record who purchase an item using internet banking.

(Note: It is just an example.)

Practical Implementation:

For this, we will create a table, i.e., tbl_ExactNumericDataType. In which, values are inserted into each column in respect to their column data type. After inserting the record successfully. We’ll also learn how to return or view those inserted records using select statement. Just follow the below mentioned steps.

Step-1: Open a New Query window in the SQL Management Studio instance.

Step-2: Create a table using create statement. As you can see in the create statement - I have used all the data type just to get an idea - How and where to use each and every type?

create table tbl_ExactNumericsDataType

(

CustomerID int,

CountryCode tinyint,

AccountNumber bigint,

AccountBalance money,

InvoiceNumber numeric,

ProductCode smallint,

EncryptedPassword decimal,

AccountWallet smallmoney,

ExistStatus bit

)

Step-3: Just press Execute button or F5 key to run that creates statement query. I am hoping you have written the exact query that I have mentioned. Once clicking the execute button you will get a message “Command(s) completed successfully.”

Step-4: Now you need to insert a record based on the column type. No problem, just copy the below code that I have created for you. Before copying, I would strongly encourage you to understand each and every column value. Now, Execute the insert query. You will get the message “(1 row(s) affected).” It implies that you are doing things in a right way.

insert into tbl_ExactNumericsDataType(CustomerID,CountryCode,AccountNumber,AccountBalance,

InvoiceNumber,ProductCode,EncryptedPassword,AccountWallet,ExistStatus)

values(110256525,162,1110023656616656464,451621464.61665,25336544215335445,2124,12145121515455454,23521.36,1)

Step-5: Lastly, View the inserted record by executing the select query.

select * from tbl_ExactNumericsDataType

 

Output Window

More Explanation On Table Column: -

1. CustomerId: Is the most important column in every table. Because to maintain the uniqueness of a table, some column need to be marked as a primary key column. And a column with “int” data type is treated as the safest.

2. CountryCode: At present there are 193 countries. And our type “tinyint” ranges from 0-255.

3. AccountNumber: Account number ranges from 8-16 digit as I’m not so sure. So, for this situation, we’ll have to use “bigint” data type whose range is best to suit this situation. This type can also be used in total pageviews or website count.

4. AccountBalance: To represent currency or money value we use “money” data type. “money” data type contains real and integer value and can store up to. -2^63 to +2^63 range value.

5. InvoiceNumber: “numeric” data type where precision can be raised from 1 to 38 on both sides of the decimal.

6. ProductCode: “smallint” data type is used in this case as Product code can be in 100’s or 1000’s but not more than that.

7. EncryptedPassword: “decimal” data type is similar to “numeric” data type both with same range precision.

8. AccountWallet: Again to store money or currency value we’ll use “smallmoney” data type, but this time not in the long range.

9. ExistStatus: Status can be 0 or 1. Same value are supported by bit data type. Here 0 means account is not working where 1 means an account is working.

Lastly, I always force coders to act smart while working with different data types. It’s not a good practice to use high precision data type every time.

By this, we learn how and where to use Exact Numeric Data Type in SQL Server.

For any query, comment us below.

Previous - Most commonly used SQL Server Data Types #1

Next - Most commonly used SQL Server Data Types #3

Related Questions: -

#newtoprgm try the first program

Q-1 Does numeric and decimal data type in SQL Server can be used interchangeably?

A) Yes

B) No

C) Numeric is not a data type

D) Decimal is not a data type

Ans. Option(A).

Explanation: Because they have the fixed precision with scale number. When maximum precision is used, valid values are from the range -10^38 to +10^38.

Q-2 Write the correct storage space for following data type:

I) money

II) int

III) tinyint

IV) smallmoney

Ans. The storage space for following data types are:

I) “money” takes 8 bytes.

II) “int” takes 4 bytes.

III) “tinyint” takes 1 bytes.

IV) “smallmoney” 4 bytes.

Q-3 What happen when we convert money to decimal data type?

Declare @accountBalance money;

Set @accountBalance=564223.23;

Print Cast(@accountBalance as decimal);

Choose the correct alternative for the given query:

A) 0.23

B) 564223.23

C) 564223

D) Error

Ans. Option(C).

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS

Keep learning and sharing...

Read More »