How to determine if an index contains a column of type varchar (max)?

Question

I am working on defragmenting an MSSQL script index. Some types of indexes can be rebuilt on the Internet, while other types cannot.

For clustered indexes, it’s simple enough to see if the table contains any LOB columns, but for a non-clustered index, I need to know specifically if there are any LOB columns covered by this particular index.

I could do this before by looking at alloc_unit_type_desc in dm_db_index_physical_stats, but this does not work for columns like varchar (max) and xml.

This is not for my database, so I do not want to discuss whether this index is suitable, just admit that it exists, and that I would like the script to be able to cope with this situation.

Does anyone know which SQL I can write to test this? Suppose I have all the relevant object identifiers and object names in scalar variables.

Answer 1

If you have a char or nvarchar with the maximum length, it will have an entry in the sys.columns table with the corresponding system type identifier for the field with -1 as the maximum length.

So, if you want to find all the identifiers of all indexes that have varchar (system type id 167), you should do this:

select distinct
    si.*
from
    sys.indexes as si
        inner join sys.index_columns as ic on
            ic.object_id = si.object_id and
            ic.index_id = si.index_id
            inner join sys.columns as sc on
                sc.object_id = ic.object_id and
                sc.column_id = ic.column_id
where
    sc.system_type_id = 167 and
    sc.max_length = -1

Answer 2

, "max" feild sys.columns -1. , , .

Answer 3

, . Clustered Index - , LOB. , , .

. , (IndexID = 1) . Non Clustered Index (IndexID = 2), LOB INCLUDE, Non Clustered Index, LOB INCLUDE. ...

--========================================================================
--      Test Setup
--========================================================================
--===== If the test table already exists, 
     -- drop it to make reruns in SSMS easier.
     IF OBJECT_ID('dbo.IndexTest','U') IS NOT NULL
        DROP TABLE dbo.IndexTest
;
GO
--===== Create the test table
 CREATE TABLE dbo.IndexTest
        (
         SomeID     INT IDENTITY(1,1)
        ,SomeInt    INT
        ,SomeLOB1   VARCHAR(MAX)
        ,CONSTRAINT PK_IndexTest_Has_LOB
         PRIMARY KEY CLUSTERED (SomeID)
        )
;
--===== Add an index that has no INCLUDE of a LOB
 CREATE INDEX IX_Has_No_LOB 
     ON dbo.IndexTest (SomeInt)
;
--===== Add an index that has INCLUDEs a LOB
 CREATE INDEX IX_Includes_A_LOB 
     ON dbo.IndexTest (SomeInt) INCLUDE (SomeLOB1)
;

, sys.index_columns , LOB. system_type_id WHERE, ...

--========================================================================
--      Test for LOBs using sys.index_columns.
--========================================================================
select distinct
    si.*
from
    sys.indexes as si
        inner join sys.index_columns as ic on
            ic.object_id = si.object_id and
            ic.index_id = si.index_id
            inner join sys.columns as sc on
                sc.object_id = ic.object_id and
                sc.column_id = ic.column_id
where
    --sc.system_type_id = 167 and
    sc.max_length = -1
;

...

object_id   name              index_id    type type_desc    ...
----------- ----------------- ----------- ---- ------------ ...
163204448   IX_Includes_A_LOB 3           2    NONCLUSTERED ...

, Clustered Index LOB, LOB . Clustered Index .

  ALTER INDEX PK_IndexTest_Has_LOB 
     ON dbo.IndexTest REBUILD WITH (ONLINE = ON)
;

Msg 2725, 16, 2, 1 - 'PK_IndexTest_Has_LOB', "SomeLOB1" , ntext, image, varchar (max), nvarchar (max), varbinary (max) xml. , . drop_existing . .

( )...

... we can try something a little different. Each index (clustered, non-clustered, or HEAP) is displayed as a distribution unit, and also identifies data in a row, data outside a row, and large objects. The following code finds ALL indexes that have the LOB associated with them ... even the Clustered Index.

--===== Find all indexes that contain any type of LOB
 SELECT  SchemaName = OBJECT_SCHEMA_NAME(p.object_id)
        ,ObjectName = OBJECT_NAME(p.object_id)
        ,IndexName  = si.name
        ,p.object_id
        ,p.index_id
        ,au.type_desc
   FROM sys.system_internals_allocation_units au --Has allocation type
   JOIN sys.system_internals_partitions p        --Has an Index_ID
     ON au.container_id = p.partition_id
   JOIN sys.indexes si                           --For the name of the index
     ON si.object_id    = p.object_id
    AND si.index_id     = p.index_id
  WHERE p.object_id     = OBJECT_ID('IndexTest')
    AND au.type_desc    = 'LOB_DATA'
;

This gives the following result for this particular test. Note that he typed object_id and index_id in the clustered index, where he did not do code based on sys.index_columns.

SchemaName ObjectName IndexName            object_id index_id type_desc
---------- ---------- -------------------- --------- -------- ---------
dbo        IndexTest  PK_IndexTest_Has_LOB 163204448 1        LOB_DATA
dbo        IndexTest  IX_Includes_A_LOB    163204448 3        LOB_DATA