March 20, 2014 at 11:27 pm
Cluster index on a floating column vs Cluster index on a numeric or decimal column vs Cluster index on a int column
column which is best on where conition.
If i use this column then i should get benifit one over the other
I am not going to store decimal point,only numeric.
What data type can be used here.
Thanks!
March 21, 2014 at 2:00 am
What is the range of values you are going to store?
This is the only thing you need to look for. Performance differences, if there are any, will most likely be negligible.
Since you are not going to store decimal values, you can rule out float.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2014 at 2:17 am
Table max size will be of around 30-50 million records.
data type will be wil be storing some max size of 1829683708 (+/-5million) less than INT(2147483648).
how internally the records are readed for INT , Double, Float.
What is the Algorithm SQL Server is using to read so.
March 21, 2014 at 2:20 am
If the maximum value is lower than the maximum int value, then choose int.
You don't need to know how SQL Server works internally just to create a table.
If you are going to store integer values, use the int data type.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2014 at 2:33 am
Koen Verbeeck (3/21/2014)
If the maximum value is lower than the maximum int value, then choose int.You don't need to know how SQL Server works internally just to create a table.
If you are going to store integer values, use the int data type.
What about the performance
how data stored for each data type and how it is readed.
Asked SQL server Algorithm to know what is used for data type.
March 21, 2014 at 2:42 am
yuvipoy (3/21/2014)
Koen Verbeeck (3/21/2014)
If the maximum value is lower than the maximum int value, then choose int.You don't need to know how SQL Server works internally just to create a table.
If you are going to store integer values, use the int data type.
What about the performance
how data stored for each data type and how it is readed.
Asked SQL server Algorithm to know what is used for data type.
Int uses the least amount of bytes, so you have the least amount of IO.
You are focusing on the wrong things here. You don't need to know the algorithms that work behind the scenes of SQL Server.
All you need to know is that int is 4 bytes. You can take a look at indexing and how SQL Server stores data inside a clustered index, but you are grossly overthinking this.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2014 at 4:11 am
Use the data type which is appropriate to the values you are storing.
If you're storing integer values, use integers. End of story.
Don't make non-standard design decisions based on performance unless you have tested and have confirmed that the standard design does not perform acceptably. I highly doubt that's the case here.
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 21, 2014 at 5:10 am
If it's integer data, use the integer data type. Done.
Numbers in general are stored basically the same way within SQL Server. The only differences really are in the size and type of number stored. There are no fundamental differences that will result in differences in performance. None. For numbers, just focus on the type of number you need.
Didn't we already have this exact conversation earlier?
"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 21, 2014 at 5:13 am
Grant Fritchey (3/21/2014)
Didn't we already have this exact conversation earlier?
Yup.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 22, 2014 at 9:45 am
Grant Fritchey (3/21/2014)
Didn't we already have this exact conversation earlier?
Indeed. Apparently all the previous answers were unacceptable, so keep asking until the desired answer materialises.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply