How can I tell if an index contains a column of type varchar(max)?

Question

I'm working on my MSSQL index defragmentation script. Certain kinds of indexes can be rebuilt online, and other kinds can't.

For clustered indexes, it's easy enough to see if the table contains any LOB columns, but for a non-clustered index I need to specifically know if there is any LOB columns covered by that specific index.

I used to be able to do this by looking at the alloc_unit_type_desc in dm_db_index_physical_stats, but this doesn't work for columns of type varchar(max) and xml.

This isn't for my database, so I don't want to get into a discussion over whether or not the index is appropriate, let's just accept that it exists and that I'd like the script to be able to handle this situation.

Does anyone know what kind of SQL I can write to check for this? Assume I have all the relevant object ids and object names in scalar variables.

Answer 1

You could also check the DMV - sys.dm_db_index_physical_stats.

It has a alloc_unit_type_desc column that would tell us if the index has LOB_DATA or not.

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count,
DDIPS.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
and DDIPS.alloc_unit_type_desc = 'LOB_DATA'

Answer 2

Be careful, folks. The Clustered Index is a different animal when it comes to LOBs. Let's do a test to see what I mean.

First, let's setup a test table. No data is needed for this test but we do have a Clustered Index (IndexID=1) as the PK. We also have a Non Clustered Index (IndexID=2) that contains no LOB columns as an INCLUDE and we also have a Non Clustered Index that does contain an LOB column as an INCLUDE. Here's the test setup code...

--========================================================================
--      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)
;

Now, let's try the code that uses sys.index_columns to find indexes that contain LOBs. I've commented out the system_type_id in the WHERE clause to open it up a bit...

--========================================================================
--      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
;

Here's the output from the run above...

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

It couldn't tell that the Clustered Index contains an LOB because the LOB is not one of the index columns. Trying to rebuild this Clustered Index will cause a failure.

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

Msg 2725, Level 16, State 2, Line 1 Online index operation cannot be performed for index 'PK_IndexTest_Has_LOB' because the index contains column 'SomeLOB1' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

With a tip of the hat to Remus Rusanu (system wouldn't let me post the link)...

... we can try something a bit different. Every index (clustered, non-clustered, or HEAP) shows up as an allocation unit and will also identify in-row data, out-of-row data, and LOBs. The following code finds ALL indexes that have an 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'
;

That produces the following output for this particular test. Notice that it did pick up on the Clustered Index by object_id and index_id where the code based on sys.index_columns didn't.

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

Answer 3

If you have a char or nvarchar with a max length, then it will have an entry in the sys.columns table with the appropriate system type id for the field, with -1 as the max length.

So, if you want to find all the ids of all the indexes that have a varchar (system type id 167), you would 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 4

I think that for "max" columns, the length or size feild in the sys.columns table should be -1. Don't have the documentation in front of me, but let me know if this works.