Most commonly used SQL Server Data Types

In our previous blog, we have discussed the very first SQL Server Data Types category i.e., Exact Numeric Data Type.

In this blog, we are going to discuss the second category i.e., Approximate Numeric Data Type.

II. Approximate Numeric Data Types: - are those that use floating point numeric data.

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

 

Approximate Numeric Data Types

Things to Remember:

1. We use float as float(n), here n indicates the digits it can hold. float(24) hold a 4-byte field and float(53) holds a 8-byte field.

2. In float(n), default value of n is 53.

3. In real data type, the storage space is 4-byte field. It is equivalent to float(24).

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 ‘float’ type number to ‘real’ 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 a user purchases an item and based on the purchase he got some bonus points and his updated account balance.

Practical Implementation:

For this, we will create a table, i.e., tbl_ApproximateNumericDataTypes. In which, values are inserted into each column with respect to their column data type. After inserting the record successfully. We’ll also learn how to return or view those inserted records using a 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_ApproximateNumericDataTypes

(

WalletBalance float,

AccountBalance real

)

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_ApproximateNumericDataTypes(WalletBalance, AccountBalance)

values(26564612.232,15112121.2323)

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

select * from tbl_ApproximateNumericDataTypes

Output Window

 

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 Approximate Numeric Data Type in SQL Server.

For any query, comment us below.

Previous - Most commonly used SQL Server Data Types #2

Next - Most commonly used SQL Server Data Types #4

Related Questions: -

#newtoprgm try the first program

Q-1 Which Approximate data type should we use to store a variable with 6-byte storage?

A) float

B) real

Ans. Option(A).

Explanation: Float data type storage is 8-byte while real data type storage is 4-byte. Asked range is exceeding the real. Hence, we have to use float data type.

Q-2 The default value of n in float(n) is __________?

A) 24

B) 25

C) 53

D) 54

Ans. Option(C).

Q-3 What happen when we convert float to int data type?

Declare @accountBalance float;

Set @accountBalance=564223.234643;

Print Cast(@accountBalance as int);

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...