April 7, 2009 at 5:01 pm
I have to create a new column and its index. This column can be any data type. The length of its value should be about 20-23.
I heard that numbers is a better candidate than letters when it comes to index. I can see this is true for tinyint/int/bigint versus char/nchar/varchar/nvarchar.... How about float or real?
Since the length of the field is not fixed, should I use varchar or char for better index?
Thank you in advance.
P.S. If there are other forums discussing about this, please direct me to it. I've searched for them, but no luck.
April 9, 2009 at 1:33 am
ur question is not enogh to give u a solution.
1. first decide datatype of new column. r u gng to store only a number or char (fixed or variable lenght) else UNICODE chars?
2. then decide whether u need index on the new column
* will that column be used frequently in WHERE/JOINs
* will new column have unique values
* is that table having more number of records or frequently joined?
consider factors like this and proceed further
April 9, 2009 at 2:05 am
jungnaja (4/7/2009)
I have to create a new column and its index. This column can be any data type. The length of its value should be about 20-23.
Maybe I'm misunderstanding something here, but how can you say that the column can be any data type? What's it going to be storing?
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
April 9, 2009 at 7:40 am
Thank you for responses.
Data will be all numbers and it will be vary from 15 to 30 digits. So it's not fixed-length and it will join to other tables. The table that this new field will reside will contains rows in millions eventually.
This field will hold concatenating values, and one of them has padding 0s. Since it's a new field, we can rearrange it whatever we want. However we prefer padding 0 in the front if char/varchar index is the same or better than float index.
About char/varchar, I was told that although SQL Server has to work an extra step for variable length data type to calculate the ending, char is faster than varchar. So I'm not sure whether or not wasting 15 bytes (for some rows not all) will gain better performance.
Thanks
April 9, 2009 at 7:43 am
BTW, the lenght will be 15-30, not 20-23 in my first post.
April 9, 2009 at 10:42 am
If the column will be containing 15-30 character and leading 0's are important, then you should make it a varchar(30) column.
Varchar has a 2 byte overhead for the storage of the length. My general rule-of-thumb is that unless all the values in the column will be within 5 characters or the length is under around 6, make the column varchar.
As for the char/varchar, have you tested to see what the performance difference is? Same regarding float vs varchar.
Picking a data type based solely upon an unverified performance statement is not usually the best idea.
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
April 9, 2009 at 1:54 pm
> Picking a data type based solely upon an
> unverified performance statement is not
> usually the best idea.
I agree and I plan to test that myself, but I thought I would start off with experts knowledge about index on float versus varchar/char.
Thank you for your response.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply