March 10, 2014 at 12:59 am
Which one is faster in data reterival float or int
Difference in float (4 to 8 byte)vs int (4 byte). i am not going to store more than 2.1 billion of records length.
i am not going to store decimal value.so which to choose if float why?
If 10 million records are there whcih one i should consider, since this column will be in "where".
March 10, 2014 at 1:01 am
If you are not going to store decimal values (and also not in the future), I see no reason to choose float.
Go with int.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2014 at 1:10 am
Koen Verbeeck (3/10/2014)
If you are not going to store decimal values (and also not in the future), I see no reason to choose float.Go with int.
Yes for sure i am not going to store decimal value.
Will int be performing faster than float.
any links are there for it
March 10, 2014 at 1:29 am
Int is always 4 bytes, so the data retrieval will not be slower.
Regarding calculations: no idea, but I think the effect will be negligible.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2014 at 1:30 am
when you say performance... what exactly you are referring to? do you want this column to be primary key as well ?
March 10, 2014 at 1:36 am
twin.devil (3/10/2014)
when you say performance... what exactly you are referring to? do you want this column to be primary key as well ?
No. I dont wnat this column to be primary key.
CREATE CLUSTERED INDEX [IX_Name] ON [dbo].[Table_name]([Column_name]) WITH FILLFACTOR = 80 ON [PRIMARY]
March 10, 2014 at 1:52 am
yuvipoy (3/10/2014)
twin.devil (3/10/2014)
when you say performance... what exactly you are referring to? do you want this column to be primary key as well ?No. I dont wnat this column to be primary key.
CREATE CLUSTERED INDEX [IX_Name] ON [dbo].[Table_name]([Column_name]) WITH FILLFACTOR = 80 ON [PRIMARY]
I think twin.devil implied that you might wanted a clustered index (which is the default for a primary key in SQL Server).
Since you are building a clustered index, choose the data type with the smallest storage footprint (and hence minimize the IO), which is the INT data type. If you only have a subset of ints you are going to use, you might even be able to pick smallint or tinyint.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2014 at 1:58 am
Koen Verbeeck (3/10/2014)
yuvipoy (3/10/2014)
twin.devil (3/10/2014)
when you say performance... what exactly you are referring to? do you want this column to be primary key as well ?No. I dont wnat this column to be primary key.
CREATE CLUSTERED INDEX [IX_Name] ON [dbo].[Table_name]([Column_name]) WITH FILLFACTOR = 80 ON [PRIMARY]
I think twin.devil implied that you might wanted a clustered index (which is the default for a primary key in SQL Server).
Since you are building a clustered index, choose the data type with the smallest storage footprint (and hence minimize the IO), which is the INT data type. If you only have a subset of ints you are going to use, you might even be able to pick smallint or tinyint.
+1, 🙂
March 10, 2014 at 3:38 am
If you are moving into very large sets of data, you might want to consider using bigint out of the box. That will be wider on your indexes, but the sizes we're talking about here, 4-8 bytes, is just not that big. SQL Server largely handles these more or less the same. You'll just get fewer total values on the leaf levels of the pages, so scans could take longer with the larger data size.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2014 at 3:48 am
So if i move to Big int data type then will there be performance improvement?
here i am mainly concern about data reading(Select).
March 11, 2014 at 3:54 am
Do you have a performance problem at the moment?
If not, then design your database according to good data design principals (including picking the correct data type for the expected values which it needs to store)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2014 at 4:14 am
yuvipoy (3/11/2014)
So if i move to Big int data type then will there be performance improvement?
Grant Fritchey (3/10/2014)
..., so scans could take longer with the larger data size.
Longer <> performance improvement
However, don't stare blindly at performance as Gail mentioned.
It might even be neglible. Who cares about a few milliseconds? (unless you read the data a few thousands times per second)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2014 at 4:56 am
yuvipoy (3/11/2014)
So if i move to Big int data type then will there be performance improvement?here i am mainly concern about data reading(Select).
Performance improvement over what? If you need to store a large number of integers, you go with BIGINT and you don't sweat performance of that storage. It's a price you pay to store that kind of data.
Gail is right. Don't start trying to tune the query and index at this level.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2014 at 7:05 am
GilaMonster (3/11/2014)
Do you have a performance problem at the moment?If not, then design your database according to good data design principals (including picking the correct data type for the expected values which it needs to store)
I am just asking will there be inprovement if there the data type is Int
If i am going to design new table how can i go ahead with float or int.
(Only going to store int value no decimal values.)
in data selection which one is good.since going to have this column in where condition.
March 11, 2014 at 7:25 am
yuvipoy (3/11/2014)
I am just asking will there be inprovement if there the data type is Int
Improvement over what?
If i am going to design new table how can i go ahead with float or int.
(Only going to store int value no decimal values.)
If you're only going to store integer values, then the logical data type is integer. Don't make weird, non-standard design decisions based on an unfounded expectation of some unknown performance improvement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply