Retrieve the Maximum Length of a Varchar Column in SQL Server

Retrieve the maximum length of a VARCHAR column in SQL Server

Use the built-in functions for length and max on the description column:

SELECT MAX(LEN(DESC)) FROM table_name;

Note that if your table is very large, there can be performance issues.

Find length of varchar(max) column

Yes, the maximum length of a varchar(max) or nvarchar(max) column is not unlimited.
The storage limit is 2GB (2^31 -1 bytes).
It behaves as a regular varchar/nvarchar column when the data is less than 8kb. Then when the data for a individual row is greater than 8kb, it is stored as a large-object text/ntext value. You can think of it like a wrapper for the 2 data types.

However, the maximum character length is not 2000000000.
It is 2147483645 for a varchar(max) column and 1073741822 for a nvarchar(max) column.
This is because an nvarchar has 2 bytes per character and a varchar has 1 byte per character and 2 bytes are needed to store the data length of a variable column.

I don't think any extra metadata is subtracted from the 2GB limit but I will comment tomorrow if I find anything else.

How to get the value of max length in sql server

SELECT TOP 1 t.value
FROM table AS t
ORDER BY LEN(t.value) DESC

List all SQL columns with max length AND greatest length

Thanks for the suggestions. I have come up with the following solution. It gets me the data i need but would be interested to see if it can be made more efficient.

declare @results table
(
ID varchar(36),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
Longest varchar(250),
SQLText varchar(250)
)

INSERT INTO @results(ID,TableName,ColumnName,DataType,MaxLength,Longest,SQLText)
SELECT 
    NEWID(),
    Object_Name(c.object_id),
    c.name,
    t.Name,
    case 
        when t.Name != 'varchar' Then 'NA'
        when c.max_length = -1 then 'Max' 
        else CAST(c.max_length as varchar)
    end,
    'NA',
    'SELECT Max(Len(' + c.name + ')) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id)
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
WHERE
    c.object_id = OBJECT_ID('MyTable')    

DECLARE @id varchar(36)
DECLARE @sql varchar(200)
declare @receiver table(theCount int)

DECLARE length_cursor CURSOR
    FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @receiver (theCount)
    exec(@sql)

    UPDATE @results
    SET Longest = (SELECT theCount FROM @receiver)
    WHERE ID = @id

    DELETE FROM @receiver

    FETCH NEXT FROM length_cursor
    INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor

SELECT 
    TableName, 
    ColumnName, 
    DataType, 
    MaxLength, 
    Longest 
FROM 
    @results

Changing the maximum length of a varchar column?

You need

ALTER TABLE YourTable ALTER COLUMN YourColumn <<new_datatype>> [NULL | NOT NULL]

But remember to specify NOT NULL explicitly if desired.

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NOT NULL;

If you leave it unspecified as below...

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500);

Then the column will default to allowing nulls even if it was originally defined as NOT NULL. i.e. omitting the specification in an ALTER TABLE ... ALTER COLUMN is always treated as.

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NULL;

This behaviour is different from that used for new columns created with ALTER TABLE (or at CREATE TABLE time). There the default nullability depends on the ANSI_NULL_DFLT settings.

Maximum length of varchar in sql

In MySQL, the max size of a row is 64KB (65535).

I don't know about TSQL, so I did some research:

In SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.

To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs.

To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.

Microsoft recommend using MAX data types instead of BLOBs in SQL Server 2005. In fact, BLOBs are being deprecated in future releases of SQL Server.

http://www.teratrax.com/articles/varchar_max.html

Make column length the max length of a column in another database

You need to fetch the value first. Then, I think you'll need dynamic SQL for the alter table. So:

declare @len int;

select @len = max(len(s.Sitename))
from [database].dbo.Sites s;

declare @sql nvarchar(max);

set @sql = 'alter table client alter column ClientNameShort nvarchar(@len)';

set @sql = replace(@sql, '@len', @len);

exec sp_executesql @sql;

---

Related Topics