Normally refers to a variable length string data type.

learn more… | top users | synonyms

8
votes
5answers
14k 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 ...
3
votes
2answers
55 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 ...
0
votes
1answer
44 views

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

Tip: To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible ...
2
votes
2answers
231 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 ...
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 ...
12
votes
3answers
9k 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 ...
1
vote
1answer
103 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 ...
9
votes
1answer
433 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 ...
-1
votes
1answer
398 views

How To Declare Fixed Length String Value In PL/SQL

In PL/SQL strings are declared in two different ways. These ways are fixed-length strings declaration, variable-length strings declaration. If we need to declare a variable-length string, we must ...
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
279 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 ...
17
votes
2answers
19k 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 ...
22
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 ...
0
votes
0answers
388 views

varchar() vs TEXT with utf8 encoding character length limit

I'm building a huge dashboard for a big website. And I'm now in the phase of creating the database and it is very very big with a lot of relations. So I want to build something that doesn't consume ...
2
votes
1answer
712 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, ...
0
votes
1answer
102 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' ...
11
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 ...
1
vote
1answer
89 views

Null in Varchar datatype

I am using a SQL Server database. If NULL value is inserted to a VARCHAR datatype column, what will be the memory size used for the column?
8
votes
2answers
52k 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 ...
0
votes
1answer
49 views
2
votes
2answers
534 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
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 ...
5
votes
4answers
547 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. ...
1
vote
1answer
71 views

Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?

Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?
0
votes
0answers
44 views

making everything varchar on purpose, though some fields should be int

I'm writing a PHP website for some clients which parses excel, stores the value in a database and then retrieves them. Some fields are only integers, but the clients can't make up their minds, they ...
2
votes
1answer
5k 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: ...
0
votes
1answer
242 views

How to Store text of 15 to 20 KB file data in blob or as text? [duplicate]

How to Store text of 15 to 20 KB file data in blob or as varchar in sql Server?
1
vote
2answers
4k 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, ...
2
votes
2answers
357 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 ...
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
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 ...
1
vote
1answer
201 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) ...
0
votes
1answer
81 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 ...
0
votes
1answer
84 views

In a banking organization, why would account balance be declared as varchar and not decimal?

I worked in two organizations where I found money related columns are defined as VARCHAR data type. I didn't get correct explanation from anyone. As the database team is totally isolated with our ...
0
votes
1answer
510 views

MySQL 'copying to tmp table' while enlarging VARCHAR

One user called today because he has tried to enlarge a VARCHAR column (from 20 to 100) in a slightly large table resulting in a write-locked table. I watched PROCESSLIST and saw that ALTER TABLE ...
0
votes
1answer
369 views

Filtering the results of a SELECT query to avoid certain words in the value of a column?

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the ...
6
votes
1answer
377 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 ...
2
votes
1answer
76 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 ...
0
votes
1answer
907 views

Get complete value of varchar(max) field - SQL Server 2005

I have searched for a couple of hours but cannot find the exact solution for this. In my SQL Server 2005 database table (tableA) there is a varchar(max) column. I want to get complete value of that ...
0
votes
1answer
51 views

Varchar indexing

I am very rusty with MySQL and I'd like some advice. I have several software tools that are deployed throughout the company and I am writing a DLL to keep track of the usage, errors and stuff of some ...
2
votes
1answer
442 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 ...
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 ...
0
votes
1answer
114 views

Why is this query slow, it seems simple

I have a very typical table of user information for my application, with an average set of columns (ints, varchars, etc). In this table are two varchar(100) fields: emailAddress and emailAddress2. ...
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 ...
1
vote
1answer
439 views

What is a storage need for MySQL index on Varchar(100) in InnoDB?

I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example ...
2
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 ...
1
vote
2answers
603 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, ...
1
vote
1answer
396 views

char vs varchar in mysql

I was reading the book MySQL high performance, it has mentioned that : CHAR is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to ...
0
votes
1answer
148 views

Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications

We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts ...
0
votes
1answer
252 views

Do certain string patterns affect query performance on a VARCHAR column?

I am a beginner on MySQL Administration and performance problems. Now this is confusing me. I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, ...