varchar vs. long varchar

[MCMartin]

Any disadvantage, if a long varchar is used instead of a varchar with explizit length?

[Breck_Carter · Accepted solution]

The short answer is no, a string is a string.

It is the actual length of each value, not whether it is declared as binary, character and/or long, that determines how the data is stored in the database.

The long answer is, there are many differences in the way strings are treated in SQL code, and in many applications, depending on binary versus character versus long versus NVARCHAR, which collation / character set, and so on.

Also, the declared maximum length of a colum does have some performance implications for indexing, function and subquery caching and column statistics used by the query optimizer. And LONG VARCHAR does have a declared maximum length, it just happens to be implicitly declared as 2147483648.

My book goes on for two pages on the picky details, and they've gotten more interesting since Version 9, but that's not what you asked for.

Here's how I choose the data type for an ordinary (text) string column: If there is an absolutely well known maximum length, I specify it; e.g., CHAR ( 200 ) for employee_name. If the column's going to be important for performance, such as a primary key column in a 20 million row table, I think long and hard about the maximum length. Otherwise, if there is ANY DOUBT about how long the string will grow, I use LONG VARCHAR; e.g., picture_description, message_text, geographic_location.

So... I have a lot of LONG VARCHAR columns in my databases AND variables in my SQL code, and they have caused zero problems.

Caveat: I write very little application code, where LONG VARCHAR can wreak havoc with the client-server interface.

And I do not have to deal with fascist database designers who insist that phone_number cannot be VARCHAR ( 200 ) but must be broken down into the constituent subfields like area_code VARCHAR ( 3 ), or that zip_code must be VARCHAR ( 5 ), or that first_name and last_name are used to validate a credit card... crap like that is what makes me abandon so many shopping carts (how do I enter 'BRECK CARTER RISINGROAD INC'?)

[MCMartin · Accepted solution]

Breck, in you book you say, that the width of a column will determine if a compressed or a hash B-tree index is used. So what will be the case for a long varchar and is it better or not?

[Breck_Carter · Accepted solution]

AFAIK LONG VARCHAR is not a good choice for an important index. FWIW the book was written for SQL Anywhere Version 9.0.0, and it was checked by Sybase for technical accuracy for that version.

[MCMartin · Accepted solution]

@Breck: can I accept more than one answer?

[Breck_Carter · Accepted solution]

@Martin: I'm not sure, but it's moot anyway... SQLA 2.0 is coming soon and we'll have to learn the rules there.

[graeme_perrow · Accepted solution]

You can only accept one answer. This will be the same in SQLA 2.0.

[Former Member]

I want to add a point to Breck's answer. Breck is largely correct in that, in the main, the SQL Anywhere server doesn't differentiate between VARCHAR and LONG VARCHAR types in a semantic way. However, there are circumstances where having a LONG type does matter. For example, the server memoizes prior executions of sub-selects so that the sub-select does not have to be executed on each invocation. However, to avoid expensive copying of BLOB (or CLOB) values at execution time, memoization will not occur if the expression's declared type is LONG.

The best answer, in my opinion, is to use the most appropriate type for every domain. In particular, in my view it is a mistake to use LONG types unnecessarily.

[Breck_Carter]

Do any of these bad things happen for, say, the declared type VARCHAR ( 32767 )? (which is the point past which you must use LONG VARCHAR)

[Breck_Carter]

Phrases like "the most appropriate domain" and "unnecessarily" are not helpful in the real world. Folks who don't develop actual business databases, but DO SEE existing databases that perform badly, tend to promote so-called "best practices" that are nanny-like in nature. My favorite example is "Never Use Select Star" which identifies the speaker as someone who does not write sophisticated business applications for a living, but sees a lot of badly-written ones.

[Former Member]

I am unaware of any "bad things" that occur with VARCHAR(32K) or less.

[Former Member]

With LONG types, the primary issue in my view is not performance, but support for fetching/retrieval/update of LONG types by the various application APIs that are out there (JDBC, ADO.NET, Hibernate, Entity Framework, etc).

[Breck_Carter]

Ah, yes, it is a fact that many (most? almost all?) client interfaces don't handle long strings very well. FWIW your answer talks only of performance.

[VolkerBarth]

@Breck: From an application developer's point of view, I would not use long varchar for, say, names of persons or streets and the like. It may be hard to set a limit but usually that comes by interactions with different systems (that usually have limits) or the fact that most apps just won't handle such very long names correctly (just as a report won't print names with 100 k chars...). And so I prefer a hardcoded limit in the database over the hidden assumption in the according apps (or the dev's minds) that the theoretical maximum length won't ever be used.

[MCMartin]

@Karim: you should change your comment to become an answer, because it has really important information and comments might be overlooked.

[Breck_Carter]

@Volker: Where do you get the idea that I advocate LONG VARCHAR for names? See the examples in my reply.

[VolkerBarth]

@Breck: Don't know, must be a mis-interpretation of my behalf. Sorry! - Surely the questions, answers and comments here require a LONG VARCHAR type:)

[Former Member]

(As per Martin's request, I am turning my comment into an answer.)

I want to add that using long varchar/nvarchar/binary when it is not necessary will have a SERIOUS impact on the client side if your client is a JDBC application using the iAnywhere/SQL Anywhere JDBC driver. The iAnywhere/SA JDBC driver will do its very best to perform wide fetches (i.e. fetch multiple rows at a time) when retrieving result sets from the server. BUT, if any of the columns in the result set are declared long varchar/nvarchar/binary, then the JDBC driver will have no choice but to drop down into single fetch mode due to the fact that the long columns will need to be getdata'd.

By the same token, the Remote Data Access layer within SA will also attempt to perform wide fetches from the remote server over ODBC; but once again, if any of the columns are declared long, the Remote Data Access layer will also drop into single fetch mode in order to perform getdata's on the long columns.

Hence, from a JDBC client or Remote Data Access standpoint, declaring a column long when it really does not need to be will have fairly significant performance implications.