Skip to main content

Questions tagged [varchar]

Normally refers to a variable length string data type.

Filter by
Sorted by
Tagged with
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
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
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
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
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
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
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
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
2 votes
2 answers
7k views

varchar(n) size?

When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
d4bbi's user avatar
  • 45
0 votes
2 answers
5k views

the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead?

I have many situation where I need to convert big float numbers with many decimal places to a rounded number with 2, or 1 or no decimal places at all. the way I have been doing this is demonstrated ...
Marcello Miorelli'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
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
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
3 votes
1 answer
2k views

Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for ...
David Rahrer's user avatar
-1 votes
2 answers
241 views

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

"Often, you get the right outcome without doing so." Example: select CONVERT(varchar, getdate(), 112) outputs 20240417 I saw this in quite a few places on Stack Exchange until I found a ...
questionto42'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
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
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
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
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
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
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
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.7k
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
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
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
2 votes
1 answer
393 views

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

My application has a Product and a Purchased table. The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the ...
Chris Betti's user avatar
1 vote
2 answers
249 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
Slawomir's user avatar
  • 453
1 vote
1 answer
763 views

Is there any slowdown in casting from varchar to text?

In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and ...
Evan Carroll's user avatar
  • 63.7k
0 votes
1 answer
1k views

Convert ASCII Bytes from XML to VARCHAR

I have XML data which includes strings as byte array, which I'd like to convert to VARCHAR in SQL Server 2012. Example snippet with text="2Hallo" and the code I tried: DECLARE @testxml XML = N' <...
user1952162's user avatar