Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [varchar]

Normally refers to a variable length string data type.

Normally refers to a variable length string data type.

Note a discrepancy in TSQL (does it do this in Oracle and MySQL as well?) when defined without a length: @somevar VARCHAR it is a ONE CHARACTER LONG STRING. If it is defined with a length: @somevar VARCHAR(10) like so, then it has that length (in this case 10 characters). This can cause truncation when unexpected.

161 questions
98
votes
4 answers

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 index lookup be noticeably faster with char vs…
Daniel Serodio
  • 1,325
  • 3
  • 12
  • 13
67
votes
3 answers

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 trouble of migrating the database to convert it to a…
Jason Baker
  • 771
  • 1
  • 5
  • 6
65
votes
5 answers

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 writes to varchar columns vs. nvarchar columns? We…
Chris L
  • 941
  • 1
  • 7
  • 10
61
votes
6 answers

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 you don't run out of room?
BenV
  • 4,923
  • 7
  • 40
  • 38
59
votes
3 answers

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 5-10 strings in the list): SELECT myField FROM…
Mark Tower
  • 693
  • 1
  • 6
  • 6
58
votes
1 answer

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 character(n), char(n) and text?
Fredy De La Cruz
  • 683
  • 1
  • 5
  • 4
46
votes
1 answer

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 is no possible hope of changing this approach,…
Bohemian
  • 723
  • 1
  • 8
  • 13
41
votes
3 answers

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 a_table WHERE a_field LIKE '%а_pattern%' until I…
Alexander Prokofyev
  • 511
  • 1
  • 4
  • 6
34
votes
3 answers

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 the internet, however they apply to Oracle and…
user129291
31
votes
2 answers

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 off. I have verified that all the data is…
webnoob
  • 605
  • 2
  • 6
  • 18
30
votes
4 answers

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 a CHAR and a VARCHAR when doing index…
LetMeSOThat4U
  • 513
  • 1
  • 7
  • 14
26
votes
2 answers

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 yielding very poor plans because there are…
Geoff Patterson
  • 8,447
  • 2
  • 28
  • 53
25
votes
1 answer

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 includes the space padding in the case of…
keypress
  • 353
  • 1
  • 3
  • 5
23
votes
2 answers

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 to the point where the query plans are taking up…
Mark Sinkinson
  • 10,657
  • 4
  • 47
  • 54
23
votes
4 answers

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 I see anyone doing varchar(100) I should tell them…
Aleksandr Levchuk
  • 1,227
  • 1
  • 10
  • 11
1
2 3
…
10 11