November 26, 2013 at 6:57 pm
So, I have a query which uses dynamic-SQL. I noticed some unexpected behavior. I will try to simplify it. If, for example, I run the following (large) code:
DECLARE @SQL_String AS NVARCHAR (MAX)
SET @SQL_String =
N'
--Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters.
--Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters.
--Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters.
--Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters.
SELECT
sqIF.[type] AS object_type
,DB_NAME () AS database_name
,SCHEMA_NAME (sqIF.[schema_id]) AS [schema_name]
,sqIF.[object_name]
,sqIF.index_name
,sqIF.index_key
,sqIF.include_key
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, sqIF.avg_fragmentation_in_percent), 1)), 1, 23)) AS fragmentation
,sqIF.type_desc AS index_type
,(CASE sqIF.is_primary_key
WHEN 0 THEN ''No''
WHEN 1 THEN ''Yes''
ELSE ''N/A''
END) AS is_pk
,(CASE sqIF.is_unique
WHEN 0 THEN ''No''
WHEN 1 THEN ''Yes''
ELSE ''N/A''
END) AS is_unique
,caREC.recommendation
,N''USE [''
+ DB_NAME ()
+ N'']; ALTER INDEX [''
+ sqIF.index_name
+ N''] ON [''
+ SCHEMA_NAME (sqIF.[schema_id])
+ N''].[''
+ sqIF.[object_name]
+ N''] ''
+ caREC.recommendation
+ (CASE caREC.recommendation
WHEN ''REBUILD'' THEN N'' WITH (MAXDOP = 1)''
ELSE N''''
END)
+ N'';'' AS alter_index_statement
FROM
(
SELECT
O.[type]
,O.[schema_id]
,O.name AS [object_name]
,I.name AS index_name
,STUFF (CONVERT (NVARCHAR (MAX), (caIIF.index_key)), 1, 2, N'''') AS index_key
,ISNULL (STUFF (CONVERT (NVARCHAR (MAX), (caIIF.include_key)), 1, 2, N''''), N'''') AS include_key
,DDIPS.avg_fragmentation_in_percent
,I.type_desc
,I.is_primary_key
,I.is_unique
,ROW_NUMBER () OVER
(
PARTITION BY
I.name
ORDER BY
DDIPS.avg_fragmentation_in_percent DESC
) AS row_number_id
FROM
sys.dm_db_index_physical_stats (1, NULL, NULL, NULL, N''LIMITED'') DDIPS
INNER JOIN sys.objects O ON O.[object_id] = DDIPS.[object_id]
AND O.[type] IN (''U'', ''V'')
AND O.is_ms_shipped = 0
AND NOT
(
SCHEMA_NAME (O.[schema_id]) = N''dbo''
AND O.name = N''sysdiagrams''
AND O.[type] = ''U''
)
INNER JOIN sys.indexes I ON I.[object_id] = DDIPS.[object_id]
AND I.index_id = DDIPS.index_id
AND I.is_disabled <> 1
AND I.is_hypothetical <> 1
CROSS APPLY
(
SELECT
(
SELECT
N'', ''
+ C.name AS [text()]
FROM
sys.index_columns IC
INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
AND C.column_id = IC.column_id
WHERE
IC.is_included_column = 0
AND IC.[object_id] = I.[object_id]
AND IC.index_id = I.index_id
ORDER BY
IC.key_ordinal
FOR
XML PATH ('''')
,TYPE
) AS index_key
,(
SELECT
N'', ''
+ C.name AS [text()]
FROM
sys.index_columns IC
INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
AND C.column_id = IC.column_id
WHERE
IC.is_included_column = 1
AND IC.[object_id] = I.[object_id]
AND IC.index_id = I.index_id
ORDER BY
IC.key_ordinal
FOR
XML PATH ('''')
,TYPE
) AS include_key
) caIIF
WHERE
DDIPS.index_id <> 0
AND DDIPS.avg_fragmentation_in_percent > 5
) sqIF
CROSS APPLY
(
SELECT
(CASE
WHEN sqIF.avg_fragmentation_in_percent <= 30.0 THEN ''REORGANIZE''
ELSE ''REBUILD''
END) AS recommendation
) caREC
WHERE
sqIF.row_number_id = 1
'
EXECUTE (@SQL_String)
SELECT LEN (@SQL_String)
Everything runs as expected. If I try to concatenate something to the code I get an error. For example, replace the following:
--Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters.
SELECT
sqIF.[type] AS object_type
,DB_NAME () AS database_name
with:
--Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters.
'+'
SELECT
sqIF.[type] AS object_type
I've essentially concatenated nothing new / additional to the code, but it errors out. When I break it down, what is happening is the concatenation seems to be limiting / truncating to NVARCHAR (4000). What I don't understand is why. It seems that when it is all one string it can go to NVARCHAR (MAX), but if you concatenate it goes down to NVARCHAR (4000).
Forgive my ignorance, just trying to understand why it works this way. Especially since if I update the changed code to:
--Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters. Filler to exceed 4000 characters.
'+CONVERT (NVARCHAR (MAX), N'')+'
SELECT
sqIF.[type] AS object_type
...everything works fine again.
I am thinking it has something to do with implicit conversions, but I was expecting it to implicitly convert to NVARCHAR (MAX)... Not, seemingly, NVARCHAR (4000).
Again, sorry for my ignorance and somewhat simplified (yet long) example.
Thanks all.
November 26, 2013 at 10:08 pm
Never mind, figured it out. It is checking each portion of text that is being concatenated. Since no one portion needs to be NVARCHAR (MAX) on its own, the highest level of implicit conversion it considers using is NVARCHAR (4000). It concatenates all the stings, concatenated to NVARCHAR (4000), and subsequently truncates the string.
Not sure if this is a bug, but not what I would have expected.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply