Skip to main content

Questions tagged [varchar]

Normally refers to a variable length string data type.

Filter by
Sorted by
Tagged with
93 votes
4 answers
92k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column? Edit: Not a dupe of: ...
Daniel Serodio's user avatar
68 votes
3 answers
88k views

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
Jason Baker's user avatar
64 votes
5 answers
253k views

Write differences between varchar and nvarchar

Currently in our SQL Server 2012 database, we're using varchar, and we'd like to change that nvarchar. I've generated a script to do that. My question is are there any differences in how SQL Server ...
Chris L's user avatar
  • 931
60 votes
6 answers
32k views

Performance implications of MySQL VARCHAR sizes

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
BenV's user avatar
  • 4,903
58 votes
3 answers
95k views

Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this: +--------------+ | table_name | +--------------+ | myField | +--------------+ ...and I need to make a lot of queries like this (with ...
Mark Tower's user avatar
58 votes
1 answer
149k views

Size limit of character varying Postgresql

What is the size limit of various data types in postgresql? I saw somewhere that for character varying(n), varchar(n) n must be between 1 to 10485760. Is that true? What are the valid sizes for ...
Fredy De La Cruz's user avatar
46 votes
1 answer
17k views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
Bohemian's user avatar
  • 713
41 votes
3 answers
62k views

N prefix before string in Transact-SQL query

Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this SELECT * FROM ...
Alexander Prokofyev's user avatar
34 votes
3 answers
53k views

varchar(255) or varchar(256)?

Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata. Does it matter anymore at this point? I saw some posts on ...
user avatar
31 votes
2 answers
70k views

Varchar(max) field cutting off data after 8000 characters

I have a field to store some data, the field is declared as varchar(max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest ...
webnoob's user avatar
  • 605
29 votes
4 answers
34k views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
LetMeSOThat4U's user avatar
26 votes
2 answers
2k views

Why does LEN() function badly underestimate cardinality in SQL Server 2014?

I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is ...
Geoff Patterson's user avatar
25 votes
1 answer
21k views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
keypress's user avatar
  • 353
23 votes
2 answers
1k views

Using MAX text or more specific, smaller type

Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should ...
Phrancis's user avatar
  • 1,320
23 votes
4 answers
4k views

What are the consequences of setting varchar(8000)?

Since varchar takes disk space proportional to the size of the field, is there any reason why we shouldn't always define varchar as the maximum, e.g. varchar(8000) on SQL Server? On create table, if ...
Aleksandr Levchuk's user avatar
21 votes
2 answers
222k views

Converting a VARCHAR to VARBINARY

I've been keeping a log of expensive running queries, along with their query plans, in a table to allow us to monitor trends in performance and identify areas that need optimising. However, it's come ...
Mark Sinkinson's user avatar
20 votes
2 answers
7k views

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255), when VARCHAR(30) will probably do. I ...
Eric J.'s user avatar
  • 468
18 votes
2 answers
5k views

Using column size much larger than necessary

I'm creating an SQL Server database with someone else. One of the tables is small (6 rows) with data that will probably remain constant. There is a remote possibility that a new row will be added. ...
elbillaf's user avatar
  • 291
18 votes
3 answers
4k views

Is there any point in undersizing VARCHAR columns?

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not. I would like to give the question of VARCHAR size a little twist and hope ...
Martin's user avatar
  • 2,420
17 votes
5 answers
21k views

How to add 1 milliseconds to a datetime string?

Based on a select, I can return x rows like this: 1 2019-07-23 10:14:04.000 1 2019-07-23 10:14:11.000 2 2019-07-23 10:45:32.000 1 2019-07-23 10:45:33.000 We have all milliseconds with 0. Is ...
Racer SQL's user avatar
  • 7,386
16 votes
1 answer
25k views

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

I'm trying to understand the best way to decide how big varchar columns should be, both from storage and performance perspectives. Performance From my research, it seems that varchar(max) should ...
aristotle2600's user avatar
16 votes
2 answers
17k views

What effect will reducing the size of a varchar column have on the database file?

We have a number of tables in our database that have VARCHAR(MAX) columns where a VARCHAR(500) (or something much smaller than max) will suffice. Naturally I want to clean these up, and get the sizes ...
nateirvin's user avatar
  • 770
15 votes
2 answers
33k views

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

From this Microsoft doc,+ n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual ...
Peter PitLock's user avatar
15 votes
1 answer
8k views

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

I'm asking this out of curiosity, being inspired by this question. We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a ...
mustaccio's user avatar
  • 26.2k
12 votes
7 answers
4k views

MySQL Indexing VarChar

I am trying to index my blogentries database for better performance but found an issue. Here is the structure: CREATE TABLE IF NOT EXISTS `blogentries` ( `id_id` int(11) NOT NULL AUTO_INCREMENT, ...
user avatar
10 votes
2 answers
8k views

UNIQUE constraint on large VARCHARs - PostgreSQL

I have a column defined like: data_url character varying(32768) NOT NULL and UNIQUE constraint on that column: CONSTRAINT unique_data_url UNIQUE (data_url) When a large object is being inserted ...
czerny's user avatar
  • 523
10 votes
2 answers
544 views

Are "out of the row" fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored. I have the ...
gotqn's user avatar
  • 4,026
9 votes
2 answers
26k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
dayuloli's user avatar
  • 203
9 votes
2 answers
7k views

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

Do varchar/text/longtext columns in MySQL occupy full length size in filesystem even for incomplete cells? Referring to this blog article for example I have the following columns and i have put in a ...
Rakib's user avatar
  • 193
8 votes
2 answers
32k views

Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
ElHaix's user avatar
  • 191
8 votes
2 answers
51k views

Whats SQL Server NVARCHAR(max) equivalent in MySQL?

Using SQL Server you just have to give the "MAX" parameter to the length of a text data type, but in MySQL there's no such a thing. According to Ispirer: "n" is the maximum number of characters, ...
Jonathan Solorzano's user avatar
8 votes
1 answer
14k views

CLOB vs Varchar2 performance in this case?

From java application, I need to store the string which can be more than 4000 characters. I just need to store it. Then person having DB access can query and view it. That column won't be accessed ...
user3198603's user avatar
8 votes
1 answer
1k views

.WRITE clause performance optimization

I have found that you can do high performance updates on varchar(max),nvarchar(max),varbinary(max) columns using the .WRITE clause(example here and here). Unfortunately, there is nothing mentioned ...
gotqn's user avatar
  • 4,026
7 votes
1 answer
8k views

Rows with a variable number of columns?

I'm trying to design a database for a program that is storing and manipulating tweets off of Twitter. I'm using code to fragment a tweet into words, usernames, and hashtags, so I'm meeting @...
jonsca's user avatar
  • 155
6 votes
3 answers
24k views

Switch VARCHAR to DATE in MySQL

I inherited a database that has a VARCHAR column that really should be a DATE. There's no checking on the form (I need to fix that) so dates constantly get entered as 2016-12-4 instead of 2016-12-04 ...
Robusto's user avatar
  • 203
6 votes
2 answers
1k views

Store VarChar(Max) [Notes] Column on Same or Separate Table

Disclaimer I had and idea for a table design a little while ago that made sense to me at the time. In recent history I have realized I have a habit of "over engineering" or spending too much time ...
Kirk Saunders's user avatar
6 votes
2 answers
5k views

Should I replace my varchar primary key with an integer primary key?

I have a table that has a varchar primary key that's associated with "Base Product Codes". They rarely change, and if they do, it's usually just being deleted or added. I figured it would be better ...
Nathan Lutterman's user avatar
6 votes
2 answers
2k views

varchar performance impact

This question has been asked before ( Speed impact of using varchar in SQL Server 2008 ), but the answers are not detailed enough to my satisfaction. Admitedly this is not a current issue and mainly ...
M Afifi's user avatar
  • 227
6 votes
1 answer
147 views

Issue with variable length string between compatibility level 80 and 100

I was getting tons of issues related with a specific scenario on my current enterprise. We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to ...
Pablo Guzman Labra's user avatar
6 votes
1 answer
5k views

Is varchar(x) as fast as `text CHECK ( char_length(x) )`?

In an Twitter exchange, Simon West asks to Brandur, Out of interest, why do use email TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before ...
Evan Carroll's user avatar
  • 63.6k
6 votes
3 answers
1k views

Why is Row Overflow not working on SQL Server 2005

I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an ...
Rkaede's user avatar
  • 63
5 votes
4 answers
664 views

How much will using a varchar(15) PK affect my tables performance?

I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. ...
Alex Ciarlillo's user avatar
5 votes
4 answers
60k views

Converting columns from NVARCHAR to VARCHAR

I have several large tables with many NVARCHAR(MAX) columns. 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 ...
equipe9's user avatar
  • 147
5 votes
2 answers
2k views

How to change an incorrect datatype for a birth date field from VARCHAR to DATE

I made a mistake when creating my users table with a birthdate field and instead of putting the DATE datatype, I put a VARCHAR! So now my users table looks like this: CREATE TABLE IF NOT EXISTS users ...
Lion Smith's user avatar
5 votes
4 answers
5k views

Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time. Increasing just the size of a column in a table is a rather simple task. ...
bernd_k's user avatar
  • 12.3k
5 votes
1 answer
16k views

Performance difference between Text and Varchar in Mysql

I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below CREATE TABLE small_table ( pk int(11) NOT NULL AUTO_INCREMENT, varc varchar(255) DEFAULT NULL, txt ...
Dinesh Kumar's user avatar
5 votes
3 answers
13k views

Does an Oracle nvarchar2 take twice as much space as varchar2?

Regarding point 4 of this question (which is in reference to MS SQL Server): VARCHAR does not store Unicode characters. NVARCHAR does store Unicode characters. Today's applications should ...
Lazer's user avatar
  • 3,361
5 votes
1 answer
1k views

What happens when you update a VARCHAR cell with a longer string?

Say I have a column of type VARCHAR(32) in a mySQL database. On Spetmeber 1st I decide to store the string "tea", but on September 2nd I decide to update it to "coffee". Clearly, if all the records ...
Mahkoe's user avatar
  • 153
5 votes
1 answer
2k views

Why does COALESCE prevent the use of an index on a varchar, but not on a text column?

Imagine a view which contains a COALESCE of two varchar columns from two different tables. The underlying varchars are indexed in both tables. In Postgres 11.6, filtering this view on the result of ...
LondonRob's user avatar
  • 233
5 votes
1 answer
6k views

Varchar index - will hashing value make it faster?

I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
Peter Kelly's user avatar