SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes with datatype char


Indexes with datatype char

Author
Message
Oliveira, Douglas
Oliveira, Douglas
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 469
Hi guys!

I have an question, in my environment I have one table that the most of columns is char datatype.
But some columns store numbers and one of this columns have a index.

I need know the negative points about a index with datatype char.

Att,
Douglas.

[]'s
Douglas R. Oliveira
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62985 Visits: 19111
If the column is a char, the index is a char.

The downside is that you can't work with the values as numbers without casting them, which takes resources. You also may have issues with some functions if you are expecting numbers and have characters in there.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40170 Visits: 32654
I'm not worried one way or another about an index on a CHAR column. However, storing numbers in string columns is very concerning. You're going to hit issues when you go to manipulate or retrieve those values if you attempt to treat them as numbers. Further, if you treat what's stored as a string as a number instead, you will get index scans where you should see index seeks. These will be caused by either your explicit or SQL Server's implicit conversion. That may make the index seem useless when, in fact, it's the code.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Oliveira, Douglas
Oliveira, Douglas
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 469
Hi Steve,

Thanks for your reply.

I understand, but what I need know is in a column where store just numbers if that is more performative if it was int datatype. Also, the size store in the disk is more if I utilize char.

[]'s
Douglas R. Oliveira
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40170 Visits: 32654
The size of the index really depends on the size of the CHAR field and the size of the number field you're comparing. If the CHAR is CHAR(2) and you're only ever storing '12' in it, it'll be smaller than a BIGINT storing 12 or '12' because the CHAR(2) is 2 bytes in size but the BIGINT is 8. However, if you make a CHAR field big enough to store a BIGINT it would have to be CHAR(19) in order to hold the same size number, but the BIGINT would still only be 8.

In general practice, always store data as the data type it should be. If it's a character field, store it as one. If it's something else, such as a number, store it as a number field.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62985 Visits: 19111
I'd follow Grant's recommendations and I'd agree with his explanations.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search