How to get the total character size of an SQL column?

Make an appointment in the following tables:

• sys.types
• sys.all_columns

Follow the query

SELECT cNmColuna = C.name
    ,cTpColuna = UPPER(TYPE_NAME(C.user_type_id))
    ,iMaxDigit = CASE
                    WHEN T.precision = 0
                    THEN C.max_length
                    ELSE T.precision
                 END
FROM sys.all_columns C WITH(NOLOCK)
    INNER JOIN sys.types T WITH(NOLOCK) ON T.user_type_id = C.user_type_id
    WHERE C.object_id = Object_Id('Nome da Tabela')

According to the article:

  

link

int supports 10 characters:

Máximo: 2.147.483.647
Mínimo: -2.147.483.648

Dynamically speaking, SQL Server can only count character characters ( varchar , nvarchar , text , etc.). From numeric fields, it's worth following the intervals table of the link.

Taking advantage of the cue, DATALENGTH has a variety of behaviors, depending on the type of data you use, eg:

DECLARE @exemploChar char(30)
SET @exemploChar = 'stack'

SELECT DATALENGTH(@exemploChar)

In the above example, the column, represented by the variable @exemploChar , will receive the total size determined in the column, because the data type char has a fixed size, that is, it will occupy 30 bytes (1 byte per character) , even if the contents of the column, occupy, as in our example, only 5 characters.

DECLARE @exemploVarchar varchar(30)
SET @exemploVarchar = 'stack'

SELECT DATALENGTH(@exemploVarchar)

In the example above, we created the column, exemplified by the variable @exemploVarchar , with data type varchar . Because it is a variable data type, the size used was only 5 bytes (5 characters), not the 30 (1 byte per character) predetermined at creation.

Out of curiosity, I'll make two more examples, but using unicode.

DECLARE @exemploNchar nchar(30)
SET @exemploNchar = 'stack'

SELECT DATALENGTH(@exemploNchar)

For this, we can notice that the size of the column has doubled to 60 bytes consumed. This happens because the unicode data type consumes two bytes per character and not one.

DECLARE @exemploNvarchar nvarchar(30)
SET @exemploNvarchar = 'stack'

SELECT DATALENGTH(@exemploNvarchar)

Finally, in this example the consumption will be 10 bytes (2 bytes per character).

If you're wondering when to use one or the other:

• char is good for writing, since the size of the column is already determined, and there is no need for extra processing for this task.
• varchar is good for reading, because in 90% of cases, the column size will be smaller than the default size
• unicode is good to use when importing data from another database or need to handle special characters in our language. In this case, unicode does not attempt to convert, keeping the original data, in addition to gaining speed.

It is worth remembering that if you want to see the properties of a table and see the size determined for each column, you can use the commands below:

• sp_help [nome_objeto] *

  * object must be in the same schema used by login

• select * from INFORMATION_SCHEMA.COLUMNS

Select the object and with the keyboard, press ALT + F1

IF ((SELECT character_maximum_length FROM information_schema.columns WHERE (table_name = 'SUC_CONFIG') AND (COLUMN_NAME = 'SUC_CHAVE')) < 100)
    BEGIN
        ALTER TABLE SUC_CONFIG ALTER COLUMN [SUC_CHAVE] VARCHAR(100) NULL
    END
GO  

So I understood the solution is to refer to the metadata table and see the definition of the type and size of the field.

These tables, of course, change according to the Database. For more details, see the documentation.