For Nvarchar(Max) I am only getting 4000 characters in TSQL?

by

You have declared this as nvarchar(max) which allows 2GB of data so it will store 2GB.

What is happening:

  • The datatype is not yet nvarchar(max) until assignment to @sql1
  • Before that, it’s a collection of strings, each less than 4000 (constants)
  • You are concatenating short constants with short variables (short = < 4000)
  • So you have 4000 characters put into @sql1

So, you have make sure you have nvarchar(max) on the right hand side.

One idea. The 2nd line concatenates nvarchar(max) with a constant = nvarchar(max)

SET @SQL1 = ''
SET @SQL1 = @SQL1 + 'SELECT DISTINCT Venue...
   ....

It’s no different to the integer division that happens in every langauge.

declare @myvar float
set @myvar = 1/2 --gives zero because it's integer on the right

Operator precedence (infers datatype precedence) is always “assignment” last… why should unicode strings in SQL Server be any different?

More Related Contents:

  1. Get top 1 row of each group
  2. Insert results of a stored procedure into a temporary table
  3. Referring to a Column Alias in a WHERE Clause
  4. Convert Month Number to Month Name Function in SQL
  5. SET versus SELECT when assigning variables?
  6. How important is the order of columns in indexes?
  7. Generate a resultset of incrementing dates in TSQL
  8. Getting result of dynamic SQL into a variable for sql-server
  9. How to get difference between two rows for a column field?
  10. Better techniques for trimming leading zeros in SQL Server?
  11. How to print VARCHAR(MAX) using Print Statement?
  12. What’s the best way to select the minimum value from several columns?
  13. Concatenate values based on ID
  14. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  15. Splitting delimited values in a SQL column into multiple rows
  16. varbinary to string on SQL Server
  17. How do I group on continuous ranges
  18. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  19. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  20. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  21. SQL Server 2005 ROW_NUMBER() without ORDER BY
  22. How to find consecutive rows based on the value of a column?
  23. Can an INNER JOIN offer better performance than EXISTS
  24. How to escape square brackets inside square brackets for field name
  25. How do you copy a record in a SQL table but swap out the unique id of the new row?
  26. Cumulative Total in MS Sql server [duplicate]
  27. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  28. Compare dates in T-SQL, ignoring the time part
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. Memory effective way to read BLOB data in C#/SQL 2005

Leave a Comment