Taking advantage of the cue, DATALENGTH
has varied behaviors, depending on the type of data you use, example:
DECLARE @exemploChar char(30)
SET @exemploChar = 'stack'
SELECT DATALENGTH(@exemploChar)
In the above example, the column, exemplified by the variable @exemploChar
, will receive the total size determined in the column, because the data type char
has fixed size, that is, it will occupy 30 bytes (1 byte per character), even if the column content, occupy, as in our example, only 5 characters.
DECLARE @exemploVarchar varchar(30)
SET @exemploVarchar = 'stack'
SELECT DATALENGTH(@exemploVarchar)
In the above example, we create the column, exemplified by the variable @exemploVarchar
, with the type of data varchar
. As this is a variable data type, the size used was only 5 bytes (5 characters) and not the 30 (1 byte per character) predetermined in the creation.
Out of curiosity, I’ll do two more examples, but using Unicode.
DECLARE @exemploNchar nchar(30)
SET @exemploNchar = 'stack'
SELECT DATALENGTH(@exemploNchar)
For this, we can notice that the column size has doubled to 60 bytes consumed. This is 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).
In case you’re wondering, when to use one or the other:
char
is good for writing, because the column size is already determined, 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 predetermined size
unicode
It is good to be used when you are importing data from another database or need to treat special characters in our language. In this case, the Unicode does not try to make the conversion, keeping the original data, in addition to gaining speed.
Remember that if you want to see the properties of a table, and in it see the size determined for each column, you can use the commands below:
Select the object and with the keyboard, press ALT + F1
which database you refer to?
– wesley luan
MS SQL Server 2008
– Laerte
Its interpretation of the return is wrong. The function returns the size in BYTES of the column. In this case a value of type INT occupies four bytes in Mssql. The size of numeric fields is not measured in "characters" but in bytes.
– jean