13:01

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Notes about data types

CHAR and VARCHAR page 3

Let's consider the following code.

DECLARE @ch AS VARCHAR(2)='1';
DECLARE @t TABLE(ch VARCHAR(2));
INSERT INTO @t VALUES('123');
SELECT @ch=CONCAT(@ch,'2','3');
SELECT @ch "var",(SELECT ch FROM @t) "col";

var	col
12	NULL

Here we define the simple variable of VARCHAR(2) data type and the table (table variable) with the single column of the same data type - VARCHAR(2).

Running this code results in truncation of variable value up to defined size, whereas the error arises, when inserting the row in the table, due to the column undersize:

String or binary data would be truncated.

This explains the result obtained - NULL in the col column, i.e. data does not be inserted into the table.

Regarding character data, it should be made a remark about its available sizes.

Console

Execute

SELECT LEN(REPLICATE('a',100000)) "varchar"
,LEN(REPLICATE(CAST('a' AS NVARCHAR),100000)) "nvarchar"
,LEN(REPLICATE(CAST('c' AS VARCHAR(MAX)),100000)) "varchar_max";

varchar	nvarchar	varchar_max
8000	4000	100000

This example shows us that simple character data types are truncated up to SQL Server data page - 8000 bytes (NVARCHAR type uses 2 bytes per symbol). VARCHAR(MAX) data type allows to store strings up to 2Gb.

It should be taken into account particularly when concatenating strings with beforehand unknown size of resulting string, which could result in wrong analysis:

Console

Execute

SELECT LEN(CONCAT(REPLICATE('a', 7000), REPLICATE('b', 7000))) ch;

ch
8000

Above is concatenation of two strings of 7000 symbols each. As a result we get string of 8000 symbols in size, the exceed symbols are truncated without error message.

Pages

varchar CHAR CONCAT

Previous [Notes about data types]

[Float(n)] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

Notes about data types

CHAR and VARCHAR page 3