How many characters can a SQL Server 2008 database field contain when the data type is VARCHAR(MAX)?

How many characters in varchar(max)?

Is it possible to change a column type in a SQL Server 2008 database from `varchar(255)` to `varchar(MAX)` without having to drop the table and recreate?

SQL Server Management Studio throws me an error every time I try to do it using that - but to save myself a headache would be nice to know if I can change the type without having to DROP and CREATE.

Thanks 

Varchar(255) to Varchar(MAX)

I have to get last 5 numbers using mysql.

My values are like `YOT-A78514`,`LOP-C4521` ...

I have to get only last five char . How can I do this in query?

Getting last 5 char of string with mysql query

I have variable length character data and want to store in SQL Server (2005) database. I want to learn some best practices about how to choose TEXT SQL type or choose VARCHAR SQL type, pros and cons in performance/footprint/function.

SQL Server Text type vs. varchar data type

I have this date format: 2011-09-28 18:01:00 (in varchar), and I want to convert it to datetime changing to this format 28-09-2011 18:01:00. How can I do it?

SQL Server Convert Varchar to Datetime

I&#39;m working on a database for a small web app at my school using `SQL Server 2005`.  
I see a couple of schools of thought on the issue of `varchar` vs `nvarchar`:

 1. Use `varchar` unless you deal with a lot of internationalized data, then use `nvarchar`.
 2. Just use `nvarchar` for everything.

I&#39;m beginning to see the merits of view 2.  I know that nvarchar does take up twice as much space, but that isn&#39;t necessarily a huge deal since this is only going to store data for a few hundred students.  To me it seems like it would be easiest not to worry about it and just allow everything to use nvarchar.  Or is there something I&#39;m missing?

What are the main performance differences between varchar and nvarchar SQL Server data types?

I&#39;m trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I&#39;ve dropped and re-created tables before but I&#39;ve never been exposed to the alter statement which is what I believe I need to use to do this. I found the documentation here: [ALTER TABLE (Transfact-SQL)][1] however I can&#39;t make heads or tails of it.

I have the following so far (essentially nothing unfortunately):

    alter table [progennet_dev].PROGEN.LE
  	alter column UR_VALUE_3

How do I approach this? Is there better documentation for this statement out there (I did some searches for an example statement but came up empty)?


  [1]: http://msdn.microsoft.com/en-us/library/ms190273.aspx

Changing the maximum length of a varchar column?

I just read that the `VARCHAR(MAX)` datatype (which can store close to 2GB of char data) is the recommended replacement for the `TEXT` datatype in SQL Server 2005 and Next SQL SERVER versions. 

If I want to search inside a column for any string, which operation is quicker?

1. Using a the `LIKE` clause against a `VARCHAR(MAX)` column?

     `WHERE COL1 LIKE &#39;%search string%&#39;`

2. Using the `TEXT` column and put a _Full Text Index_/_Catalog_ on this column, and then search using the `CONTAINS` clause?
      
      `WHERE CONTAINS (Col1, &#39;MyToken&#39;)`

Using varchar(MAX) vs TEXT on SQL Server

What is the equivalent of varchar(max) in MySQL?

Equivalent of varchar(max) in MySQL?

I&#39;ve got a messages table in MySQL which records messages between users. Apart from the typical ids and message types (all integer types) I need to save the actual message text as either VARCHAR or TEXT. I&#39;m setting a front-end limit of 3000 characters which means the messages would never be inserted into the db as longer than this.

Is there a rationale for going with either VARCHAR(3000) or TEXT? There&#39;s something about just writing VARCHAR(3000) that feels somewhat counter-intuitive. I&#39;ve been through other similar posts on Stack Overflow but would be good to get views specific to this type of common message storing.


MySQL: Large VARCHAR vs. TEXT?

I have a question about the `ALTER TABLE` command on a really large table (almost 30 millions rows).
One of its columns is a `varchar(255)` and I would like to resize it to a `varchar(40)`.
Basically, I would like to change my column by running the following command:

```sql
ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(40);
```

I have no problem if the process is very long but it seems my table is no more readable during the `ALTER TABLE` command.
Is there a smarter way? Maybe add a new column, copy values from the old column, drop the old column and finally rename the new one?

**Note:** I use PostgreSQL 9.0.

Postgresql - change the size of a varchar column to lower length

In multiple courses, books, and jobs, I have seen text fields defined as VARCHAR(255) as kind of the default for &quot;shortish&quot; text.  Is there any good reason that a length of 255 is chosen so often, other than being &lt;a href=&quot;https://stackoverflow.com/questions/164432/what-real-life-bad-habits-has-programming-given-you/164556#164556&quot;&gt;a nice round number&lt;/a&gt;?  Is it a holdout from some time in the past when there was a good reason (whether or not it applies today)?

I realize, of course, that a tighter limit would be more ideal, if you somehow know the maximum length of the string.  But if you are using VARCHAR(255) that probably indicates that you don&#39;t know the max length, only that it is a &quot;shortish&quot; string.

-----

Note: I found this question (&lt;a href=&quot;https://stackoverflow.com/questions/143933/varchar255-v-tinyblob-v-tinytext&quot;&gt;varchar(255) v tinyblob v tinytext&lt;/a&gt;), which says that VARCHAR(*n*) requires *n*+1 bytes of storage for *n*&amp;lt;=255, *n*+2 bytes of storage for *n*&amp;gt;255.  Is this the only reason?  That seems kind of arbitrary, since you would only be saving two bytes compared to VARCHAR(256), and you could just as easily save another two bytes by declaring it VARCHAR(253).

Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)?

What&#39;s the difference between the `text` data type and the `character varying` (`varchar`) data types?

According to [the documentation][1]

&gt; If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

and

&gt; In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

So what&#39;s the difference?

  [1]: http://www.postgresql.org/docs/8.0/interactive/datatype-character.html

Difference between text and varchar (character varying)

In Oracle, what is the difference between :

    CREATE TABLE CLIENT
    (
     NAME VARCHAR2(11 BYTE),
     ID_CLIENT NUMBER
    )

and

    CREATE TABLE CLIENT
    (
     NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11)
     ID_CLIENT NUMBER
    )



Difference between BYTE and CHAR in column datatypes

What is meant by `nvarchar`?

What is the difference between `char`, `nchar`, `varchar`, and `nvarchar` in SQL Server?



What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

[John][1] uses `CHARACTER VARYING` in the places where I use `VARCHAR`.
I am a beginner, while he is an expert.
This suggests me that there is something which I do not know.

**What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL?**


  [1]: https://stackoverflow.com/questions/1196873/to-prevent-the-use-of-duplicate-tags-in-a-database/1197192#1197192

What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL?

When I executed the following command:

    ALTER TABLE `mytable` ADD UNIQUE (
    `column1` ,
    `column2`
    );

I got this error message:

    #1071 - Specified key was too long; max key length is 767 bytes

Information about column1 and column2:

    column1 varchar(20) utf8_general_ci
    column2  varchar(500) utf8_general_ci

I think `varchar(20)` only requires 21 bytes while `varchar(500)` only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message?

    #1071 - Specified key was too long; max key length is 767 bytes

MySQL Error #1071 - Specified key was too long; max key length is 767 bytes

In the [documentation about the LIKE operator][1], nothing is told about the case-sensitivity of it. Is it? How to enable/disable it?

I am querying `varchar(n)` columns, on an Microsoft SQL Server 2005 installation, if that matters.

  [1]: http://msdn.microsoft.com/en-us/library/ms179859.aspx


Is the LIKE operator case-sensitive with SQL Server?

I recognize that an email address can basically be indefinitely long so any size I impose on my varchar email address field is going to be arbitrary. However, I was wondering what the &quot;standard&quot; is? How long do you guys make it? (same question for Name field...)

**update:** Apparently the max length for an email address is 320 (&lt;=64 name part, &lt;= 255 domain). Do you use this?

How long should SQL email fields be?

Here i am trying to create view as shown below in example:
  

Example:  

     create view view1
     as 
     select table1.col1,table2.col1,table3.col3
     from table1 
     inner join
     table2 
     inner join 
     table3
     on 
     table1.col4 = table2.col5 
     /* Here col4 of table1 is of &quot;integer&quot; type and col5 of table2 is of type &quot;varchar&quot; */
     /* ERROR: operator does not exist: integer = character varying */
     ....;


Note: The same query executed in sql server but getting the above error in postgreSQL.

PostgreSQL: ERROR: operator does not exist: integer = character varying

In MySQL, if I create a new `VARCHAR(32)` field in a UTF-8 table does it means I can store 32 bytes of data in that field or 32 chars (multi-byte)?

MySQL VARCHAR Lengths and UTF-8

Suppose that I have a SQL table that has a varchar[1000] field called &quot;Remarks&quot;.

I would like to craft a single SQL statement, which when executed, will return 1000, or whatever the size of the varchar field might be changed to in the future.

Something like `SELECT size(Remarks) FROM mytable`.

How do I do this?


How to get the size of a varchar[n] field in one SQL statement?

I&#39;m using SQL Server 2008 and I need to make a VARCHAR field bigger, from (200 to 1200) on a table with about 500k rows.  What I need to know is if there are any issues I have not considered. 

I will be using this TSQL statement:

    ALTER TABLE MyTable
    ALTER COLUMN [MyColumn] VARCHAR(1200)

I&#39;ve already tried it on a copy of the data and this statement had no ill effects that I could see. 

So are there any possible problems from doing this that I may not have considered? 

By the way, the column is not indexed. 

When increasing the size of VARCHAR column on a large table could there be any problems?

Is there any problem with making all your Sql Server 2008 string columns varchar(max)? My allowable string sizes are managed by the application.  The database should just persist what I give it. Will I take a performance hit by declaring all string columns to be of type varchar(max) in Sql Server 2008, no matter what the size of the data that actually goes into them?

varchar(max) everywhere?

I want to create a varchar column in SQL that should contain `N&#39;guid&#39;` while `guid` is a generated GUID by .NET ([Guid.NewGuid][1]) - class System.Guid.

What is the length of the `varchar` I should expect from a GUID?
Is it a static length?

Should I use `nvarchar` (will GUID ever use Unicode characters)?

    varchar(Guid.Length)

PS. I don&#39;t want to use a SQL row guid data-type. I am just asking what is `Guid.MaxLength`.

  [1]: https://docs.microsoft.com/en-us/dotnet/api/system.guid.newguid


What is the string length of a GUID?

I screwed up and created a column as a varchar(255) where that is no longer sufficient. I&#39;ve read that varchar has no performance benefits over text on Postgres, and so would like to convert the varchar to a text column in a safe way that preserves the data.

What&#39;s the best way for me to do this?

Postgres: Convert varchar to text

When we create a table in MySQL with a `VARCHAR` column, we have to set the length for it. But for `TEXT` type we don&#39;t have to provide the length. 

What are the differences between `VARCHAR` and `TEXT`?

Difference between VARCHAR and TEXT in MySQL

I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a `VARCHAR`.

My rationale is that, each coupon will have a unique code, and the only commands I will be running are `SELECT ... FROM ... WHERE coupon_code=&#39;..&#39;`

I won&#39;t be doing any joins or indexing, and I don&#39;t see there ever being more than a few hundred entries in this table.

It seems to me that this will be OK, but I don&#39;t know if there is anything I&#39;m missing/not thinking about.

Can I use VARCHAR as the PRIMARY KEY?

How does MySQL store a varchar field? Can I assume that the following pattern represents sensible storage sizes :

1,2,4,8,16,32,64,128,255 (max)




A clarification via example. Lets say I have a varchar field of 20 characters. Does MySQL when creating this field, basically reserve space for 32 bytes(not sure if they are bytes or not) but only allow 20 to be entered?

I guess I am worried about optimising disk space for a massive table. 

What are the optimum varchar sizes for MySQL?

I&#39;m trying to populate a SQL table with a list of words. The table itself it pretty simple:


    CREATE TABLE WORDS(
      ID BIGINT AUTO_INCREMENT, 
      WORD VARCHAR(128) NOT NULL UNIQUE, 
      PRIMARY KEY(ID)
    );

The problem I&#39;m running into is this: when I do the following inserts back to back

    INSERT INTO WORDS(WORD) VALUES(&#39;Seth&#39;);
    INSERT INTO WORDS(WORD) VALUES(&#39;seth&#39;);

The second insert fails with a constraint violation (&quot;Duplicate entry &#39;seth&#39; for key &#39;WORD&#39;&quot;).

How can I get the `UNIQUE` constraint on `WORD` to be case sensitive?

SQL unique varchar case sensitivity question

Is it just that `nvarchar` supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using `varchars`?


What is the difference between varchar and nvarchar?

Why do we always need to specify `VARCHAR(length)` instead of just `VARCHAR`? It is dynamic anyway.

UPD: I&#39;m puzzled specifically by the fact that it is mandatory (e.g. in MySQL).

Why does VARCHAR need length specification?

If I have a VARCHAR of 200 characters and that I put a string of 100 characters, will it use 200 bytes or it will just use the actual size of the string?

MySQL VARCHAR size?

I’m in need of some clarification of the maximum length of a varchar field in MySQL.

I’ve always thought the max length was 255 (255 what? Characters I’ve assumed, but this might be a source of my confusion). Taking a look at the tables of a database set up by an external company we’re working with, I see a field set-up as varchar(20000), holding chunks of xml longer than 255 characters. Why does this work? Is 20000 a valid value?

A bit of googling has revealed that in mysql varchar has a limit of 65,535 bytes, and I see varchar(65535) &lt;a href=&quot;https://stackoverflow.com/questions/332798/equivalent-of-varcharmax-in-mysql&quot;&gt;in use&lt;/a&gt;, so how does the 255 limit relate to this?


Is a VARCHAR(20000) valid in MySQL?

I would like to know what the max size is for a MySQL VARCHAR type. 

I read that the max size is limited by the row size which is about 65k. I tried setting the field to `varchar(20000)` but it says that that&#39;s too large. 

I could set it to `varchar(10000)`.  What is the exact max I can set it to?

What is the MySQL VARCHAR max size?

Is it more efficient to use a `varchar` field sized as a power of two vs. another number? I&#39;m thinking no, because for SQL Server the default is 50.

However, I&#39;ve heard (but never confirmed) that sizing fields as a power of 2 is more efficient because they equate to even bytes, and computers process in bits &amp; bytes.

So, does a field declared as `varchar(32)` or `varchar(64)` have any real benefit over `varchar(50)`?

varchar Fields - Is a Power of Two More Efficient?

I need to update a field in my production db from INT to VARCHAR. 

The information in the current INT field is sensitive and I want to ensure that I don&#39;t alter it inadvertently or destroy it while changing the table type. For example, a user may have INT 123456 stored in this column and I want to ensure that is accurately stored once converted to VARCHAR.

What is the recommended process for accomplishing this? 

Is there anything I need to worry about when using something like this?

    ALTER TABLE table_sample CHANGE col_sample col_sample VARCHAR;


Thanks in advance for any help. 

All Varchar Solutions on Gang of Coders

Total of 37 Varchar Solutions