Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexes with datatype char Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 8:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:30 AM
Points: 18, Visits: 362
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
Post #1347223
Posted Monday, August 20, 2012 8:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:12 AM
Points: 33,169, Visits: 15,300
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
Post #1347236
Posted Monday, August 20, 2012 10:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 15,637, Visits: 28,021
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1347328
Posted Monday, August 20, 2012 10:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:30 AM
Points: 18, Visits: 362
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
Post #1347334
Posted Monday, August 20, 2012 11:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 15,637, Visits: 28,021
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1347352
Posted Monday, August 20, 2012 1:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:12 AM
Points: 33,169, Visits: 15,300
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
Post #1347435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse