CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-designtypes
Nate
Nate

Reputation: 30656

What SQL Server DataType should I use for a Text field which will be JOINED, "LIKE'd" and Queried against a lot?

The title really says it all, a bit more info though for those who bothered to click.

I'm wondering if I should use nvarchar(10) or char(10) or varchar(10)? I don't really know what the advantages or disadvantages of the above are for a case like mine. I appreciate any input you may have.

Upvotes: 4

Views: 1348

Answers (3)

James Curran
James Curran

Reputation: 103555

varchar vs. nvarchar: Are you certain to be limited to the US-english printable character set? Or might you need foreign characters?

'char' vs 'varchar': If 10 is a solid, hard limit, then you might be better off with char. The main problem is that the field will always be padded to 10 character, so you'll have to always be Trim()ing them. On some database servers, you have to account for the extra spacing in where clauses (i.e., WHERE col1 = 'abcd ') but I thing MS Sql Server does the sensible thing.

Upvotes: 0

Faisal Feroz
Faisal Feroz

Reputation: 12785

nvarchar is used if you want to store unicode characters. char(10) would be a waste of storage space.

Use nvarchar if you are supporting languages other than english otherwise varchar should do the trick. comparisons

Don't forget to have a index on this field as this will speed up your joins and comparisons.

Upvotes: 0

marc_s
marc_s

Reputation: 755297

CHAR(10) will be padded to the defined length with spaces, e.g. if you have CHAR(10) and store "Stack" in it, the contents will really be "Stack.....". Works great for things like state abbreviation (always 2 chars). But the padding does make the querying a bit more cumbersome at time.

VARCHAR(10) will store as many chars as needed - more efficient, but more so for larger strings.

NVARCHAR(10) will be the same - variable length - but with 2 bytes for each character (and NCHAR(10) is the same as CHAR(10) - only 2 bytes per character). Great for when you need to regularly support e.g. Asian, Cyrillic, or Arabic characters or other "non-Latin" alphabets. If you have Western European languages only (English, French, German, Spanish etc.), it's a waste of space.

My gut feeling tells me that CHAR/NCHAR might be a tad quicker when joining, but I don't think it'll really be a significant difference. Otherwise, if you have to reserve 10 characters for each entry, and most of them are only 2 to 5 chars, you are wasting some space, so that's a downside for the CHAR/NCHAR types.

So if I had to decide, I'd probably use VARCHAR(10) (without knowing all your detailed requirements, that is).

Upvotes: 7

Related Questions