Skip to main content
Share Your Experience: Take the 2024 Developer Survey
66 votes
Accepted

Size limit of character varying Postgresql

The maximum size of limited character types (e.g. varchar(n)) in Postgres is 10485760. You can check this in that way: create table test(id serial primary key, str varchar(10485761)); ERROR: length ...
klin's user avatar
  • 2,184
45 votes
Accepted

varchar(255) or varchar(256)?

Size each and every column appropriately. Do NOT use a "standard" size for each column. If you only need 30 characters, why create a column that can handle 255? I'm so glad you're not advocating ...
Hannah Vernon's user avatar
  • 70.1k
34 votes

How to add 1 milliseconds to a datetime string?

Datetime is not precise to the level of 1 millisecond. What you are asking for is not possible unless you change to a different datatype (i.e. datetime2). Documentation Important quote: Accuracy ...
Forrest's user avatar
  • 4,169
32 votes
Accepted

Understanding varchar(max) 8000 column and why I can store more than 8000 characters in it

I can see why you're misunderstanding this - it's a little tricky. These are all valid: VARCHAR(1) - one character string VARCHAR(4000) - 4,000 characters VARCHAR(8000) - 8,000 characters - and if ...
Brent Ozar's user avatar
31 votes
Accepted

Using MAX text or more specific, smaller type

Should I always use (n)varchar(max) for text columns? No. For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (...
Paul White's user avatar
  • 85.5k
25 votes

varchar(255) or varchar(256)?

Others have already pointed out that that the number of bytes required to store the length is fixed. I wanted to focus on this part in your question: Does it matter anymore at this point? You have ...
Joe Obbish's user avatar
  • 32.2k
23 votes
Accepted

What are the current best practices concerning varchar sizing in SQL Server?

Regardless of specific datatype, you need to be able to store whatever the application requests to be stored. You cannot specify something smaller than the max size of what will actually be saved. ...
Solomon Rutzky's user avatar
21 votes

Performance implications of MySQL VARCHAR sizes

Most of the answers in this thread are over five, twelve years old, written before InnoDB and utf8 were defaults. So, let me start over... When a query needs an internal temporary table it tries to ...
Rick James's user avatar
  • 78.3k
21 votes
Accepted

If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)?

Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table: USE tempdb; DROP TABLE IF ...
Joe Obbish's user avatar
  • 32.2k
18 votes
Accepted

Using column size much larger than necessary

SQL Server uses column lengths when allocating memory for query processing. So, yes, in short, you should always size columns appropriately for the data. Memory allocations are based on the number ...
Hannah Vernon's user avatar
  • 70.1k
16 votes

Using column size much larger than necessary

With 6 rows, no, there will be no observable benefit. That entire table will fit on a single page so lowering the maximum potential space you’ll use on that page while still occupying that entire page ...
Aaron Bertrand's user avatar
13 votes

How to add 1 milliseconds to a datetime string?

The DateAdd function is what you are looking for. Use millisecond as the first parameter to the function, to tell it that you are adding milliseconds. Then use 1 as the second parameter, for the ...
Doug Deden's user avatar
  • 1,782
13 votes

How to add 1 milliseconds to a datetime string?

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds ...
BlueGI's user avatar
  • 299
12 votes
Accepted

Do varchar/text/longtext columns in MySQL occupy full size in filesystem even for partially filled cells?

'abcd' in CHAR(72) CHARACTER SET ascii occupies 72 bytes on disk. 'abcd' in CHAR(72) CHARACTER SET utf8 occupies 3*72 bytes on disk. 'abcd' in CHAR(72) CHARACTER SET utf8mb4 occupies 4*72 bytes on ...
Rick James's user avatar
  • 78.3k
9 votes

Index performance for CHAR vs VARCHAR (Postgres)

I agree with everything said by a_horse_with_no_name, and I generally agree with Erwin's comment advice: No, char is inferior (and outdated). text and varchar perform (almost) the same. Metadata ...
Evan Carroll's user avatar
  • 63.5k
9 votes
Accepted

CLOB vs Varchar2 performance in this case?

Pre-12c: varchar2 is limited to 4000 bytes. If you need to store more data in a field, go for CLOB. From 12c: varchar2 is limited to 4000 bytes, or 32767 bytes after enabling Extended datatypes. If ...
Balazs Papp's user avatar
  • 40.7k
9 votes
Accepted

Storing unicode text into varchar column in SQL Server

The '?' character replaced the original value because there was no equivalent character in the collation code page and is physically stored in the column instead of the original value. Sorry to say ...
Dan Guzman's user avatar
  • 28.2k
8 votes

Using MAX text or more specific, smaller type

This is going to read like a paranoid's answer, but there aren't only storage and performance considerations. The database itself doesn't control its clients, and clients can't be assumed to always ...
Mathieu Guindon's user avatar
8 votes

varchar(255) or varchar(256)?

The declared size of the varchar rarely has a performance impact1. The data might be actually stored as a rowstore with page compression or row compression. As a Clustered Columnstore, or as a ...
David Browne - Microsoft's user avatar
8 votes
Accepted

Why should you always write "varchar" with the length in brackets behind it? Often, you get the right outcome without doing so

Why should you still always write varchar with the length in brackets behind it? Often, you get the right outcome without doing so. Often and always are two distinctly different things. Example: ...
Sean Gallardy's user avatar
7 votes
Accepted

How large space will be occupied by mysql for a varchar utf8 column?

CHAR(...) CHARACTER SET utf8 always takes 3 bytes per character -- CHAR(100) occupies 300 bytes (no length needed). [The "ROW FORMAT" impacts the details of this statement.] VARCHAR ...
Rick James's user avatar
  • 78.3k
7 votes

Understanding varchar(max) 8000 column and why I can store more than 8000 characters in it

Question 1: How does the 8000 characters come into play and where should I be aware of it? Setting n to 8000 causes 8000 characters to come into play. You need to be aware of the Precision, Scale, ...
Bill's user avatar
  • 124
7 votes
Accepted

Performance difference between Text and Varchar in Mysql

The performance difference is mainly due to the fact, that text datatype columns are always copied to temporary tables on disk, because the memory engine doesn't support those types. From the manual: ...
tombom's user avatar
  • 3,158
7 votes
Accepted

How to implement a unique key for a long VARCHAR column in MySQL

Checking directly for uniqueness of a 5000-character string is going to be pretty expensive, no matter how you implement it. I'd suggest computing a hash, storing that hash, and computing uniqueness ...
AMtwo's user avatar
  • 16.2k
6 votes
Accepted

Switch VARCHAR to DATE in MySQL

Use str_to_date() to handle the conversion. str_to_date('2016-12-4', '%Y-%m-%d');
Evan Carroll's user avatar
  • 63.5k
6 votes
Accepted

float to varchar with no trailing zeros

SQL Server 2012 and up support FORMAT, which enables .NET-like format strings, making this a rather easy task; just use FORMAT(Num, '0.#####') Working SQL Fiddle here.
Glorfindel's user avatar
  • 2,201
6 votes

Converting columns from NVARCHAR to VARCHAR

This database is only for US use and I don't think there are any foreign characters or letters in any of the columns so I wanted to look into ... Changing many of these columns over from NVARCHAR to ...
Solomon Rutzky's user avatar
6 votes

Issue with variable length string between compatibility level 80 and 100

Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server? No, not that I am aware of. This is an issue of ...
Solomon Rutzky's user avatar
6 votes
Accepted

varchar(n) size?

It's not that simple. The n in varchar(n) is just the upper limit of allowed characters (not bytes!). Only the actual string is stored, not padded to the maximum allowed size. That's opposed to the ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Find "naked" varchars

Regex is not the correct way to go about this. There will always be false positives that are impossible/extremely hard to find. For example, multiline comment blocks Instead, I recommend using the ...
Zikato's user avatar
  • 4,737

Only top scored, non community-wiki answers of a minimum length are eligible