Indexes with datatype char

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Design Ideas and Questions » Indexes with datatype char

Indexes with datatype char

Rate Topic

Display Mode

Topic Options

Author

Message

Oliveira, Douglas

Posted Monday, August 20, 2012 8:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 05, 2013 10:28 AM
Points: 9, Visits: 167

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

Steve Jones - SSC Editor

Posted Monday, August 20, 2012 8:42 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 9:03 PM
Points: 31,406, Visits: 13,723

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

Grant Fritchey

Posted Monday, August 20, 2012 10:39 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144

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

Oliveira, Douglas

Posted Monday, August 20, 2012 10:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 05, 2013 10:28 AM
Points: 9, Visits: 167

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

Grant Fritchey

Posted Monday, August 20, 2012 11:18 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144

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

Steve Jones - SSC Editor

Posted Monday, August 20, 2012 1:30 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 9:03 PM
Points: 31,406, Visits: 13,723

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 »

Permissions