SQL NVARCHAR and VARCHAR Limits

Question

All  I have a large  unavoidable  dynamic SQL query  Due to the number of fields in the selection criteria the string containing the dynamic SQL is growing over 4000 chars  Now  I understand that there is a 4000 max set for NVARCHAR MAX   but looking at the executed SQL in Server Profiler for the statement   DELARE  SQL NVARCHAR MAX   SET  SQL    SomeMassiveString  gt  4000 chars      EXEC  SQL   GO   Seems to work      for another query that is also large it throws an error which is associated with this 4000 limit      it basically trims all of the SQL after this 4000 limit and leaves me with a syntax error  Despite this in the profiler  it is showing this dynamic SQL query in full        What exactly is happening here and should I just be converting this  SQL variable to VARCHAR and get on with it   Thanks for your time   Ps  It would also be nice to be able to print out more than 4000 chars to look at these big queries  The following are limited to 4000   SELECT CONVERT XML   SQL   PRINT  SQL     is there any other cool way

User · Answer

declare  p varbinary max  set  p   0x declare  local table  col text   SELECT    p    p   0x3B   CONVERT varbinary 100   Email   FROM tbCarsList  where email  lt  gt      group by email  order by email   set  p   substring  p  2  100000    insert  local values cast  p as varchar max     select DATALENGTH col  as collen  col from  local  result collen  gt  8000  length col value is more than 8000 chars

User · Answer

You mus use nvarchar text too  that s mean you have to simply had a  N  before your massive string and that s it  no limitation anymore  DELARE  SQL NVARCHAR MAX   SET  SQL   N SomeMassiveString  gt  4000 chars      EXEC  SQL   GO

User · Answer

I understand that there is a 4000 max set for NVARCHAR MAX   Your understanding is wrong  nvarchar max  can store up to  and beyond sometimes  2GB of data  1 billion double byte characters   From nchar and nvarchar in Books online the grammar is nvarchar     n   max      The   character means these are alternatives  i e  you specify either n or the literal max  If you choose to specify a specific n then this must be between 1 and 4 000 but using max defines it as a large object datatype  replacement for ntext which is deprecated   In fact in SQL Server 2008 it seems that for a variable the 2GB limit can be exceeded indefinitely subject to sufficient space in tempdb  Shown here  Regarding the other parts of your question Truncation when concatenating depends on datatype   varchar n    varchar n  will truncate at 8 000 characters  nvarchar n    nvarchar n  will truncate at 4 000 characters  varchar n    nvarchar n  will truncate at 4 000 characters  nvarchar has higher precedence so the result is nvarchar 4 000   n varchar max     n varchar max  won t truncate  for  lt  2GB   varchar max    varchar n  won t truncate  for  lt  2GB  and the result will be typed as varchar max   varchar max    nvarchar n  won t truncate  for  lt  2GB  and the result will be typed as nvarchar max   nvarchar max    varchar n  will first convert the varchar n  input to nvarchar n  and then do the concatenation  If the length of the  varchar n  string is greater than 4 000 characters the cast will be to nvarchar 4000  and truncation will occur   Datatypes of string literals If you use the N prefix and the string is  lt   4 000 characters long it will be typed as nvarchar n  where n is the length of the string  So N Foo  will be treated as nvarchar 3  for example  If the string is longer than 4 000 characters it will be treated as nvarchar max  If you don t use the N prefix and the string is  lt   8 000 characters long it will be typed as varchar n  where n is the length of the string  If longer as varchar max  For both of the above if the length of the string is zero then n is set to 1  Newer syntax elements  1  The CONCAT function doesn t help here DECLARE  A5000 VARCHAR 5000    REPLICATE  A  5000    SELECT DATALENGTH  A5000    A5000           DATALENGTH CONCAT  A5000  A5000     The above returns 8000 for both methods of concatenation  2  Be careful with    DECLARE  A VARCHAR MAX         SET  A   REPLICATE  A  5000    REPLICATE  A  5000   DECLARE  B VARCHAR MAX         SET  B    B   REPLICATE  A  5000    REPLICATE  A  5000    SELECT DATALENGTH  A           DATALENGTH  B     Returns -------------------- -------------------- 8000                 10000  Note that  A encountered truncation  How to resolve the problem you are experiencing  You are getting truncation either because you are concatenating two non max datatypes together or because you are concatenating a varchar 4001 - 8000  string to an nvarchar typed string  even nvarchar max    To avoid the second issue simply make sure that all string literals  or at least those with lengths in the 4001 - 8000 range  are prefaced with N  To avoid the first issue change the assignment from DECLARE  SQL NVARCHAR MAX   SET  SQL    Foo     Bar          To DECLARE  SQL NVARCHAR MAX         SET  SQL    SQL   N Foo    N Bar   so that an NVARCHAR MAX  is involved in the concatenation from the beginning  as the result of each concatenation will also be NVARCHAR MAX  this will propagate  Avoiding truncation when viewing Make sure you have  quot results to grid quot  mode selected then you can use select  SQL as  processing-instruction x   FOR XML PATH    The SSMS options allow you to set unlimited length for XML results  The processing-instruction bit avoids issues with characters such as  lt  showing up as  amp lt

User · Answer

Okay  so if later on down the line the issue is that you have a query that s greater than the allowable size  which may happen if it keeps growing  you re going to have to break it into chunks and execute the string values  So  let s say you have a stored procedure like the following   CREATE PROCEDURE ExecuteMyHugeQuery      SQL VARCHAR MAX  -- 2GB size limit as stated by Martin Smith AS BEGIN     -- Now  if the length is greater than some arbitrary value     -- Let s say 2000 for this example     -- Let s chunk it     -- Let s also assume we won t allow anything larger than 8000 total     DECLARE  len INT     SELECT  len   LEN  SQL       IF   len  gt  8000      BEGIN         RAISERROR   The query cannot be larger than 8000 characters total                       16                     1       END      -- Let s declare our possible chunks     DECLARE  Chunk1 VARCHAR 2000                Chunk2 VARCHAR 2000                Chunk3 VARCHAR 2000                Chunk4 VARCHAR 2000       SELECT  Chunk1                   Chunk2                   Chunk3                   Chunk4           IF   len  gt  2000      BEGIN         -- Let s set the right chunks         -- We already know we need two chunks so let s set the first         SELECT  Chunk1   SUBSTRING  SQL  1  2000           -- Let s see if we need three chunks         IF   len  gt  4000          BEGIN             SELECT  Chunk2   SUBSTRING  SQL  2001  2000               -- Let s see if we need four chunks             IF   len  gt  6000              BEGIN                 SELECT  Chunk3   SUBSTRING  SQL  4001  2000                  SELECT  Chunk4   SUBSTRING  SQL  6001    len - 6001               END               ELSE             BEGIN                 SELECT  Chunk3   SUBSTRING  SQL  4001    len - 4001               END         END           ELSE         BEGIN             SELECT  Chunk2   SUBSTRING  SQL  2001    len - 2001           END     END      -- Alright  now that we ve broken it down  let s execute it     EXEC   Chunk1    Chunk2    Chunk3    Chunk4  END

User · Answer

The accepted answer helped me but I got tripped up while doing concatenation of varchars involving case statements   I know the OP s question does not involve case statements but I thought this would be helpful to post here for others like me who ended up here while struggling to build long dynamic SQL statements involving case statements   When using case statements with string concatenation the rules mentioned in the accepted answer apply to each section of the case statement independently   declare  l sql varchar max        set  l sql    l sql   case when 1 1 then     --without this correction the result is truncated     --CONVERT VARCHAR MAX         REPLICATE  1   8000    REPLICATE  1   8000  end  print len  l sql

[sql] SQL NVARCHAR and VARCHAR Limits

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to variables

Examples related to string-concatenation