How rigid is the max size of a VARCHAR in SQL Server?

Question

i.e. if I create a VARCHAR(50) field, what happens if I try to assign it a value that's 100 characters long?

Will SQL Server let me do this? Is it somehow less efficient?

Answer 1

It will throw an error saying that the value would be truncated. It won't write more than the 50 characters you've specified, otherwise you wouldn't be able to prevent the row size from growing out of control

Answer 2

Will SQL Server let me do this? Is it somehow less efficient?

It will emit an error, saying that the string or binary data would be truncated.

Answer 3

If you try forcing a large string into a smaller-size variable, the incoming string will be simply chopped off at the appropriate size.

declare @Variable varchar (50)
set @Variable = replace (SPACE (100), ' ' , '.')
print @Variable 

the output is 50 characters long

..................................................

If you try to force a large string into a small sized Field in a Table, an Error will be raised

declare @MyTable Table
(
    TestVariable varchar (50)
)
insert into @MyTable (TestVariable) select replace (SPACE (100), ' ' , '.')
select * from @MyTable 

the output is

Msg 8152, Level 16, State 14, Line 6
String or binary data would be truncated.
The statement has been terminated.
TestVariable
------------------------------------------------