varchar vs. long varchar

6	Any disadvantage, if a long varchar is used instead of a varchar with explizit length? data-type asked 11 Oct '10, 16:39 Martin 8.9k●127●164●253 accept rate: 14% flat view

3 Answers:

active answers oldest answers newest answers popular answers

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'?)

permanent link

answered 12 Oct '10, 09:08

Breck Carter
32.5k●539●724●1050
accept rate: 20%

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?

(12 Oct '10, 09:52) Martin

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.

(12 Oct '10, 13:34) Breck Carter

@Breck: can I accept more than one answer?

(13 Oct '10, 07:31) Martin

@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.

(13 Oct '10, 14:19) Breck Carter

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

(13 Oct '10, 17:48) Graeme Perrow

flat view

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.

permanent link

answered 12 Oct '10, 11:20

Glenn Paulley
10.8k●5●76●106
accept rate: 43%

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)

(12 Oct '10, 13:29) 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.

(12 Oct '10, 13:38) Breck Carter

Comment Text Removed

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

(12 Oct '10, 13:43) Glenn Paulley

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).

(12 Oct '10, 13:46) Glenn Paulley

Comment Text Removed

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.

(12 Oct '10, 13:55) Breck Carter

@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.

(12 Oct '10, 13:58) Volker Barth

Comment Text Removed

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

(13 Oct '10, 07:29) Martin

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

(13 Oct '10, 14:17) Breck Carter

@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:)

(13 Oct '10, 14:35) Volker Barth

More comments hidden

showing 4 of 9 show all flat view

(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.

permanent link

answered 13 Oct '10, 13:51

Karim Khamis
5.6k●5●38●70
accept rate: 40%

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

data-type ×30

question asked: 11 Oct '10, 16:39

question was seen: 16,434 times

last updated: 13 Oct '10, 13:51

SAP SQL Anywhere

varchar vs. long varchar

Follow this question

Related questions