loading..
Русский    English
15:23
go

CHAR and VARCHAR page 3

Let's consider the following code.

  1. DECLARE @ch AS VARCHAR(2)='1';
  2. DECLARE @t TABLE(ch VARCHAR(2));
  3. INSERT INTO @t VALUES('123');
  4. SELECT @ch=CONCAT(@ch,'2','3');
  5. 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
  1. SELECT LEN(REPLICATE('a',100000)) "varchar"
  2. ,LEN(REPLICATE(CAST('a' AS NVARCHAR),100000)) "nvarchar"
  3. ,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
  1. 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 1 2 3
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.