Handling Long Dynamic Queries

Dynamic queries always have problems. It have lot of drawbacks but I am not going to discuss here about its drawbacks. I am going to discuss how to handle those situations where you have to execute large dynamic sql. We generally put the query in varchar or nvarchar type of variable and then execute the query & and it is always a point of discussion. I have seen on many forums, people have these issues and generally answers are related to the capacity of the types (varchar/nvarchar).

Many answers are related to length/capacity of the varchar/nvarchar types. If we go through the MSDN for varchar and nvarchar defination then we have the below defination     VARCHAR varchar [ ( n | max ) ]  Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying. NVARCHAR nvarchar [ ( n | max ) ]  Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.  If we analyse the above to definitions for the types then " We can set the length of the varchar type to 8000 and nvarchar to 4000 but if we specify the length as max then it is capable of storing 2GB of data, so think how many string you can store in the varchar or nvarchar types. So issue is not of the storage, there is something else which is going behind. Lets discuss that. So as per MSDN The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max). So size is not limited we can execute dynamic sql with size less than 2 GB. If you are getting any error then please check your query or if you are getting any error related to length truncate then check your code , you might be appending some variable with fixed length in the query. Remove all the nvarchar(n) with nvarchar(max) used in storing the dynamic sql. You will not get any error while executing the query unless and until there is error in your query itself. The size of the varchar(max) and nvarchar(max) can be verified practically. Simply follow the below steps and you will come to know that varchar can store more than 8000 chars and nvarchar can store more than 4000 chars. Declare two variables one is varchar(max) and another nvarchar(max) as hown below DECLARE @var1 VARCHAR(MAX) DECLARE @var2 NVARCHAR(MAX) Now set value of these variables to more than 50000 chars as shown below SET @var1='abcdefg.......up to 50000+ chars' SET @var2='abcdefg.......up to 50000+ chars' Now print these variables value using select statement as below: SELECT @var1 SELECT @var2 OR SELECT LEN(@var1), @var1 SELECT LEN(@var2), @var2 Note : If you will use print then the max data for varchar variable will be truncated to 8000 and 4000 for nvarchar variable. Its limitation of the print statement*, Try Select statement to verify the data length. *Will discuss on print and select statement in details in next posts. keep visiting. You can try this for dynamic query also by simple select statement like: SET @var1 = 'SELECT ''abcdef........ up to 50000+ chars'' AS Data' EXEC (@var1) Using Varchar and NVarchar in dynamic SQL Now the question is where to use varchar and where nvarchar in dynamic sql ?  We have two way of executing dynamic queries. One is to simply use EXEC <dynamic sql(varchar or nvarchar)> And another is to use special stored procedure i.e. SP_EXECUTESQL <dynamic sql(only nvarchar)>,<other params> So for executing simple dynamic sql we can use exec which accepts varchar and nvarchar both types, but if you are using dynamic queries with some paramaters then you must have to use sp_executesql stored procedure which accepts only nvarchar type. I will post about exec and sp_executesql in my next blog post. Thanks for visiting the blog.