Home Dashboard Directory Help
Search

Internal query processor for query to script an index by Erland Sommarskog


Status: 

Closed
 as Duplicate Help for as Duplicate


1
0
Sign in
to vote
Type: Bug
ID: 777049
Opened: 1/21/2013 5:08:13 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

The query in the repro section fails with an error that a plan could not be generated. The culprit appears to be the OUTER APPLY for the included columns. if I change it to CROSS APPLY, the query runs, but the result is not correct.

The purpose of the query is to script the better part of an index.

The simple workaround is to tack on the included columns later.

The error happens on all versions from SQL 2005 and up.
Details
Sign in to post a comment.
Posted by Robert Heinig II on 3/21/2013 at 2:35 AM
Sorry connect kills line break formatting, you'll have to re-guess them.
(1) result lacks a closing parenthesis anyway <grin/>
(2) pre-converting the schemaless XML column of the ic2 part to nvarchar works around the hitch (at least in 10.5.4000)
(3) I'm not surprised...




IF OBJECT_ID(N'dbo.aba_onoff',N'FN') IS NULL
    EXEC(N'CREATE FUNCTION dbo.aba_onoff(@bit bit) RETURNS varchar(3) AS BEGIN RETURN (CASE @bit WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' END) END');

declare @ixcmd nvarchar(MAX), @ixname sysname, @tblname sysname, @object_id int;
SELECT TOP 1
    @ixname = i.name
    , @tblname = t.name
    , @object_id = t.object_id
FROM sys.tables t
INNER JOIN sys.indexes i
    ON i.object_id = t.object_id;

SELECT @ixcmd = 'CREATE '
    + CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END
    + CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END
    + 'INDEX ' + quotename(@ixname)
    + ' ON ' + quotename(@tblname)
    + '(' + substring(ic1.collist.value('.', 'nvarchar(MAX)'), 1, len(ic1.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
    + CASE WHEN ic2.incllist IS NOT NULL
        THEN ' INCLUDE(' + substring(ic2.incllist, 1, len(ic2.incllist) - 1) + ')'
        ELSE '' END
    + CASE WHEN i.filter_definition IS NOT NULL
        THEN 'WHERE ' + i.filter_definition
        ELSE '' END
    + 'WITH (IGNORE_DUP_KEY=' + dbo.aba_onoff(i.ignore_dup_key) + ')'
FROM sys.indexes i
CROSS APPLY (
    SELECT quotename(c.name)
        + CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END
        + ','
    FROM sys.index_columns ic
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal > 0
    ORDER BY ic.key_ordinal
    FOR XML PATH(''), TYPE
    ) AS ic1(collist)
OUTER APPLY (
    SELECT
        ic2a.incllist.value('.', 'nvarchar(MAX)')
    FROM (
    SELECT quotename(c.name) + ','
    FROM sys.index_columns ic
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
    ORDER BY ic.index_column_id
    FOR XML PATH(''), TYPE
    ) ic2a(incllist)
    ) AS ic2(incllist)
WHERE object_id = @object_id AND name = @ixname;

PRINT @ixcmd;
Posted by Erland Sommarskog on 1/25/2013 at 12:44 PM
Oops! I forgot to include the function aba_onoff. Here it is:

CREATE FUNCTION dbo.aba_onoff(@bit bit) RETURNS varchar(3) AS
BEGIN
RETURN (CASE @bit WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END)
END
Sign in to post a workaround.