Normally refers to a variable length string data type.

learn more… | top users | synonyms

32
votes
3answers
37k 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 ...
26
votes
6answers
17k 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 ...
23
votes
2answers
1k 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 ...
20
votes
4answers
3k 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 ...
18
votes
2answers
20k 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 ...
16
votes
2answers
405 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 ...
16
votes
1answer
31k views

Varchar(Max) field cutting off data after 8000 characters SQL Server 2008

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 ...
14
votes
3answers
10k 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 Would ...
14
votes
3answers
2k 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 ...
12
votes
1answer
1k 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 ...
11
votes
2answers
4k 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 ...
9
votes
1answer
449 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 ...
9
votes
2answers
3k 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 ...
8
votes
5answers
16k 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 ...
8
votes
2answers
56k 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 ...
8
votes
2answers
250 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 ...
7
votes
7answers
2k 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, ...
6
votes
2answers
15k 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 ...
6
votes
3answers
824 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 ...
6
votes
1answer
414 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 ...
5
votes
4answers
552 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. ...
5
votes
3answers
3k 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. ...
5
votes
2answers
965 views

varchar performance impact

This question has been asked before ( speed impact of using varchar in MSSQL 2008 ), but the answers are not detailed enough to my satisfaction. Admitedly this is not a current issue and mainly for ...
5
votes
1answer
428 views

Unexpected Unique Constraint Violation

Given the following CREATE TABLE statement: CREATE TABLE [dbo].[Unit] ( [id] SMALLINT NOT NULL IDENTITY(1,1), [name] VARCHAR(100) NOT NULL, CONSTRAINT ...
5
votes
3answers
1k views

MySQL varchar(255) limitation & Anomaly

I have defined a column in my MySQL database as : ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL However, when I enter text ...
4
votes
1answer
6k 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 @...
4
votes
3answers
8k 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 ...
3
votes
2answers
1k 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 ...
3
votes
2answers
78 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 ...
3
votes
1answer
591 views

MySQL text field without quote

I've just run into something strange. The base_id is a varchar(255). When I do a SELECT without quote, it scans the whole table: mysql> EXPLAIN SELECT sid FROM rf_fo.scald_atoms WHERE base_id = ...
3
votes
1answer
876 views

Will one nvarchar column affect query performance?

I have one column that I recently converted to nvarchar(100) to store Asian characters. The other columns in this table, and the other tables in the same database, use varchar, int, bit and datetime. ...
3
votes
1answer
1k views

Rich text: VARCHAR, TEXT or BLOB in MySQL

Question: In my web application (php/MySQL) I will be saving rich text. This may be anything from a couple words to several pages length essay. I’m sure I could get away with using VARCHAR, TEXT or ...
2
votes
1answer
287 views

varchar storage and comparisons in SQL Server 2008

I have a large table with a structure similar to this: Id bigint primary key Sequence int Parent int foreign key Data1 varchar(20) Data2 varchar(20) Data3 varchar(20) Data4 ...
2
votes
1answer
2k views

Oracle SQL count occurances of character in VARCHAR

Is is possible to count the number of occurrences of a character in a VARCHAR in oracle? Something that would allow me to determine '11001110' has 5 ones?
2
votes
2answers
245 views

What is more efficient: text (765) or varchar (765) to store a freeform searchable description?

EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back? A little background on the question. Let's say we have sales_orders table and we want to be able to keep a ...
2
votes
1answer
450 views

Getting Visual Studio to deploy a UDF to Return NVARCHAR(MAX)

I have a SQLCLR stored procedure with the following signature in Visual Studio 2013: [SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)] public static ...
2
votes
1answer
1k views

Oracle Performance - Varchar2 (x char) x Varchar2 (x byte)

I know the definition diference between Varchar2 (x char) x Varchar2 (x byte). varchar2(20 char) means you can store 20 characters -- whereas varchar2(20) means you can store 20 bytes. What ...
2
votes
2answers
397 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 ...
2
votes
1answer
830 views

Storage size for varchar length in Redshift

So, a lot of databases will store the length prefix for a varchar field in 1 byte if the length is less than 255, and 2 bytes if it is more. For example, the MySQL docs say: In contrast to CHAR, ...
2
votes
2answers
577 views

Should I used varchar(max) or varchar(4000) SPARSE?

I have a "comment"-type column that is rarely used -- around 6% non-null in a population of 3 million records. The average length (when used) is 6 characters, and the max so far is around 3KB. A ...
2
votes
2answers
129 views

Storing NULL Values in large VARCHAR fields

I have a table that represents a "note". This note has things like a title and various other meta data. However, I'm trying to figure out the best practice for storing the contents of the note. ...
2
votes
1answer
39 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 ...
2
votes
1answer
82 views

Shrinking VARCHAR with MODIFY: data alignment and potential for data loss

I'm using MODIFY to change (shrink) a number of VARCHAR fields of various lengths in various tables. In each case, the field is called ip and is a textual representation of an IPv4 or IPv6 address. To ...
2
votes
1answer
6k views

What does it mean? ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Q 1: Whenever an Oracle SP wants to return error code/message, it fails returning below exeption: OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: ...
1
vote
2answers
2k 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 ...
1
vote
1answer
134 views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (http://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 ...
1
vote
1answer
1k views

Remove single quotes from db2 column name

I have following query which i am trying to execute against db2 database server SELECT REPLACE('\'abc\'','\'','') FROM TN_DAY_PROCESS I am expecting the output like abc without single quotes from ...
1
vote
2answers
1k views

VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed?

For a log table, I have a custom message column. It will never be updated. It will be searched with LIKE conditions. It will be capped at 1KB. TEXT fields are slower than VARCHAR fields for ...
1
vote
1answer
218 views

Is it worth indexing a varchar(32) column of UUIDS where half the UUIDS are v1 and half are v4?

I have a MySQL InnoDB table with tens of millions of rows. Each row has uuid column. They are stored in the standard VARBINARY(16). I was recently asked to add a column to this table of VARCHAR(32) ...
1
vote
2answers
630 views

Indexing varchar column to search for first letter

I'm trying to optimize a legacy web-app where users can ask for a list of all records whose name starts with a given character. The query to optimize is: SELECT oggetto.id_oggetto, titolo, anno, ...