slow count on text column Table t1 with 500k rows, 2TB in size, one text column. SQL Server 2008 R2 SP3.

CREATE TABLE [dbo].[t1](
    [TId] [int] IDENTITY(1,1) NOT NULL,
    [Weight] [int] NULL,
    [TDate] [datetime] NULL,
    [text_column] [text] NULL,
    [CreatedDate] [datetime] NULL,
    [UpdatedDate] [datetime] NULL,
    [LastUpdatedBy] [varchar](255) NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
    [TId] ASC
)


This is running forever: 

SELECT COUNT(*) FROM t1 WHERE text_column IS NOT NULL 


Unable to change the table, can only fix this with new indexes. But can't create an index on the text column. No experience with full text indexes.

What should I do to improve it, please ?
I suggest a filtered index:

CREATE INDEX ix_valid_text_column 
  ON dbo.t1(Tid) 
  WHERE text_column IS NOT NULL;


Now your query should be able to use that index instead of the clustered index, and it should be much more efficient.

Even better, it enables you to look at the metadata views, so you read one row (or a number of rows equal to the number of partitions) instead of doing any sort of manual count:

SELECT SUM(p.rows) 
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i
  ON o.[object_id] = i.[object_id]
  AND o.[index_id] = i.[index_id]
INNER JOIN sys.tables AS t
  ON t.[object_id] = t.[object_id]
WHERE t.name = N't1'
  AND t.[schema_id] = 1
  AND i.name = N'ix_valid_text_column';


The query looks uglier but it will be far more efficient (in spite of what the execution plan may tell you), especially if the number of non-NULL values is small, and especially if the table is high-volume (your current query can block and can be blocked).