Which is faster varchar or int

Grant Fritchey. It really depends on how the optimizer is resolving those queries. It's also dependent on how you're writing the queries. AND, it's dependent on the indexes you have on the columns. Assuming up to date statistics, well written queries, and the values are the same, probably, you won't see any real difference between the two at all. But that's a bunch of assumptions. Theodore Roosevelt. One could bring a server to its knees with implicit conversions. Combining int with string, if doing joins on those values, or a parameter thinking int instead of string and it could slow down pretty significantly.

I thought the search by int column was faster, so meanwhile I also performed some tests on a table with a million records. An index seek was performed for each one of the options and sometimes the search by the string was faster a few milisecs.

I suppose it will only be slower when index on colString does not exists or turns too fragmented, but the same would occur for the search by int column I think. So is. Related Questions. How can i use System. SqlDataSourceEnumerator class to know available sql datasources?

What does a transaction around a single statement do? Convert integer to hex and hex to integer. Feel free to Contact Us. Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community.

It only takes a minute to sign up. Connect and share knowledge within a single location that is structured and easy to search. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is searching for very specific attributes on a model. I am currently deciding whether to use an integer type or simply use a typical string type e.

These columns are enums. They have a fixed size for the amount of possible values they can have. Most enum lengths do not exceed 5, meaning the index would be more or less fixed throughout the lifetime of the application ; thus, the integer and string indexes would be identical in the number of nodes. However, the string that would be indexed could be around 20 characters long, which in memory is roughly 5x that of the integer if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per character, then this holds.

I don't know how database engines do index look-ups, but if it needs to "scan" the string until it matches exactly , then in essence that means that the string lookup would be 5x slower than an integer lookup; the "scan" until match for the integer lookup would be 4 bytes instead of This is what I'm imagining:.

FOUND getting records I hope that that makes sense. Basically, because the integer takes up less space, it can be "matched on" faster than its string counterpart. Perhaps this is a completely wrong guess, but I'm no expert, so that's why I'm asking you guys!

I suppose that this answer I just found seems to support my hypothesis, but I want to be sure. The number of possible values in the column wouldn't change in using either one, so the index itself would not change unless I added a new value to the enum.

In this case, would there be a performance difference in using integer or varchar , or does using an integer type make more sense? The reason I am asking is that Rails' enum type maps integers to string keys, but they aren't meant to be user-facing columns. Essentially, you can't do verification that the enum value is a valid one, because an invalid value will cause an ArgumentError before any validations can be run. Using a string type would allow validations, but if there's a performance cost I'd rather just hack away around the validation problem.

Short answer: integer is faster than varchar or text in every aspect. The difference grows with the length of the keys and the number of rows.

Detailed assessment:. Index size is probably responsible for the lion share of performance difference in most cases. Consider the overhead per index tuple basically the same as for a table : 4 bytes for the item identifier and 8 bytes for the index tuple header.