What Does Varchar(-1) Mean

What does varchar(-1) mean?

It's how to represent varchar(max) in .net SQLDBType (not explicitly stated, but length is -1)

I can't try it in SQL language in SQL Server right now for interest's sake: but I'm sure it's only for client code because varchar is normally strongly defined between 1 and 8000, but for max type it's not.

Sql Server 2008 NVARCHAR length -1

As JNK pointed out in the comments, it means MAX:

Name NVARCHAR(MAX)

From MSDN:

-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.

Is There ANY Sense in SQL Data Type VARCHAR(1)?

Yes there is sense to it.

• Easier for it to be definable in the language. It is consistent and easier to define varchar to allow 1-8000 than to say it needs to be 2+ or 3+ to 8000.

• The VARying CHARacter aspect of VARCHAR(1) is exactly that. It may not be optimal for storage but conveys a specific meaning, that the data is either 1 char (classroom code) or blank (outside activity) instead of NULL (unknown/not-yet-classified).

Storage plays very little part in this - looking at a database schema for CHAR(1), you would almost expect that it must always have a 1 char value, such as credit cards must have 16 digits. That is simply not the case with some data where it can be one or optionally none.

There are also differences to using VARCHAR(1) vs CHAR(1)+NULL combination for those who say tri-state [ 1-char | 0-char | NULL ] is completely useless. It allows for SQL statements like:

select activity + '-' + classroom
from  ...

which would otherwise be more difficult if you use char(1)+NULL, which can convey the same information but has subtle differences.

performance between varchar(1) vs char(1)

The difference will be negligible in most cases. Concentrate your performance oriented design efforts in places where it will make a real difference, like table composition and index design.

It helps to divide the design effort into two layers: logical design and physical design.

Most of the performance oriented effort is in the second stage, physical design.

In logical design, flexibility trumps performance. Except when it doesn't.

What does varchar(-1) mean?

It's how to represent varchar(max) in .net SQLDBType (not explicitly stated, but length is -1)

I can't try it in SQL language in SQL Server right now for interest's sake: but I'm sure it's only for client code because varchar is normally strongly defined between 1 and 8000, but for max type it's not.

VARCHAR Length is -1 in dbeaver. Why?

MAX datalengths are defined as -1 in the system objects. Some applications therefore will report the length as -1, instead of MAX.

For example:

USE tempdb;
GO

CREATE TABLE dbo.TestTable (varcharColMAX varchar(MAX),
                            nvarcharColMAX nvarchar(MAX),
                            varbinaryColMAX varbinary(MAX),
                            varcharCol10 varchar(10),
                            nvarcharCol10 nvarchar(10),
                            varbinaryCol10 varbinary(10));
SELECT c.name,
       c.max_length
FROM sys.tables t
     JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.[name] = 'TestTable';

DROP TABLE dbo.TestTable;

Which outputs:

name             max_length
---------------- ----------
varcharColMAX    -1
nvarcharColMAX   -1
varbinaryColMAX  -1
varcharCol10     10
nvarcharCol10    20
varbinaryCol10   10

Note that nvarcharCol10 has a length of 20 as max_length reports the size in bytes, and a single nvarchar character is 2 bytes in length (2 * 10 = 20).

Difference between VARCHAR and TEXT in MySQL

TL;DR

TEXT

• fixed max size of 65535 characters (you cannot limit the max size)
• takes 2 + c bytes of disk space, where c is the length of the stored string.
• cannot be (fully) part of an index. One would need to specify a prefix length.

VARCHAR(M)

• variable max size of M characters
• M needs to be between 1 and 65535
• takes 1 + c bytes (for M ≤ 255) or 2 + c (for 256 ≤ M ≤ 65535) bytes of disk space where c is the length of the stored string
• can be part of an index

More Details

TEXT has a fixed max size of 2¹⁶-1 = 65535 characters.

VARCHAR has a variable max size M up to M = 2¹⁶-1.

So you cannot choose the size of TEXT but you can for a VARCHAR.

The other difference is, that you cannot put an index (except for a fulltext index) on a TEXT column.

So if you want to have an index on the column, you have to use VARCHAR. But notice that the length of an index is also limited, so if your VARCHAR column is too long you have to use only the first few characters of the VARCHAR column in your index (See the documentation for CREATE INDEX).

But you also want to use VARCHAR, if you know that the maximum length of the possible input string is only M, e.g. a phone number or a name or something like this. Then you can use VARCHAR(30) instead of TINYTEXT or TEXT and if someone tries to save the text of all three "Lord of the Ring" books in your phone number column you only store the first 30 characters :)

Edit: If the text you want to store in the database is longer than 65535 characters, you have to choose MEDIUMTEXT or LONGTEXT, but be careful: MEDIUMTEXT stores strings up to 16 MB, LONGTEXT up to 4 GB. If you use LONGTEXT and get the data via PHP (at least if you use mysqli without store_result), you maybe get a memory allocation error, because PHP tries to allocate 4 GB of memory to be sure the whole string can be buffered. This maybe also happens in other languages than PHP.

However, you should always check the input (Is it too long? Does it contain strange code?) before storing it in the database.

Notice: For both types, the required disk space depends only on the length of the stored string and not on the maximum length.

E.g. if you use the charset latin1 and store the text "Test" in VARCHAR(30), VARCHAR(100) and TINYTEXT, it always requires 5 bytes (1 byte to store the length of the string and 1 byte for each character). If you store the same text in a VARCHAR(2000) or a TEXT column, it would also require the same space, but, in this case, it would be 6 bytes (2 bytes to store the string length and 1 byte for each character).

For more information have a look at the documentation.

Finally, I want to add a notice, that both, TEXT and VARCHAR are variable length data types, and so they most likely minimize the space you need to store the data. But this comes with a trade-off for performance. If you need better performance, you have to use a fixed length type like CHAR. You can read more about this here.

What is the difference between varchar and nvarchar?

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benefits of full Unicode storage.

---

Related Topics