| Author |
Topic |
|
Blastrix
Posting Yak Master
208 Posts |
 Posted - 2004-03-02 : 16:46:07
|
| Is a query speed on a varchar(16) column the same as a query on a uniqueidentifier field which is also 16 bytes? Has anyone done any tests, or is it well known that one of the above will perform better?Thanks,Steve |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-02 : 18:59:50
|
| It would depend on several factors. :) Remember that a varchar field only holds the size actually residing in the table. Your uniqueidentifier field is always unique. Given that the varchar field is filled to capacity on every field and every field is unique, they should be the same.Having said that, the above will rarely if ever happen. You will often for example have a name varchar field with Smith several hundred times. Searching off Smith, before going to the second column of your name index which would be first name, would be infinitely faster than searching a column like uniqueidentifier. Finally, I'm curious as to why you wold ask this. Why would you ever want to compare the two? They are totally different datatypes and I can't imagine an instance where you would want to actually compare performance of the two for a decision process.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2004-03-03 : 00:27:36
|
| The data in question to be stored would be a set length. The theory was that if I could determine query times would be similar for the two, then I could make up my own unique ID system that was a bit more friendly than dishing out GUIDs to user's. After all, it's easier on users to input 16 characters rather than 36. So it's not for directly comparing the two data types, but rather which one I can best use for my purposes. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-03-03 : 10:06:45
|
quote:
The data in question to be stored would be a set length.
then you should use a char() and not a varchar()- Jeff |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-03 : 11:08:51
|
| Queries on the character field would have to use the codepages so there is a bit more work to do unless you use a binary collation.It is more important to give the users what they want though as any slight performance improvement here will be far outwaid by any design deficiencies. Spend time on the overall design rather than worrying about odd datatypes.I tend to stick to varchar(x), int, datetime unless there is a good reason to use something else as it saves time looking things up and I can spend that time worrying about more important things.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2004-03-03 : 14:35:02
|
quote:
Originally posted by jsmith8858then you should use a char() and not a varchar()
I thought I read somewhere that char should only be used for data with shorter lengths such as 2-3 characters, and that anything more should just use a varchar. Is that not the case? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 14:40:23
|
quote:
Originally posted by Blastrix
I thought I read somewhere that char should only be used for data with shorter lengths such as 2-3 characters, and that anything more should just use a varchar. Is that not the case?
If your data is fixed characters, then you should use CHAR(x). VARCHAR(x) has more overhead and should be used for non-fixed character values.Tara |
|
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-03 : 14:40:55
|
| I've been taught that in general if all records are to be the same data length use char, if they are variable length use varchar. Varchar carries a 1-byte overhead so dont use it when all values have a fixed length (even if its longer than 3 characters).Where this can be different is the shorter length in chosing char or varchar, but it depends on what you want to do with the data.If you have a field that can be 1-3 characters long, then a varchar(3) will take up to 4 bytes based on the data length. Only those that are 3 characters long will take up 4 bytes, the rest will be 3 or less. This is not much of an overhead. Char(3) in this case can lead to issues as selections against the data for concatination etc would have to trim excess space. I think you need to weigh the individual needs of the field rather than make a blanket statement. Chris |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-03 : 15:28:11
|
| Take a look at the original post..he said it would be a fixed length, if you know the length, and it is always going to be the same, use CHAR. Why incur the overhead if you don't need to? |
|
|
|
|
Datatype Query Speed?