Sql-server – Truncation error creating clustered index on schema-bound view

sql serversql-server-2008

I was just creating a schema-bound view that I wanted to put an index on (to try out some computed column variations).

I created the view WITH SCHEMABINDING, then I had to create a clustered index before I could create my other non-clustered ones.

The underlying table has an INT primary key column, so I was going to make the view's clustered index based on that. So I ran

Create UNIQUE CLUSTERED INDEX [cix_viewEvents_EventID] ON [dbo].[viewEvents] (EventID)

Much to my surprise, after 4-5 minutes I got a 

Msg 8152, string or binary data would be truncated

error.

The only column in the index is an int; what could be getting truncated?

I couldn't find anything relevant on google.

EDIT:

The original table is like this,

CREATE TABLE [dbo].[Events](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EventTitle] [varchar](max) NOT NULL,
[EventContent] [varchar](max) NULL,
[EventDate] [datetime] NOT NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED 
(
[EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

while the view I created looked like this

CREATE VIEW dbo.viewEvents
WITH SCHEMABINDING
AS
SELECT EventID, EventTitle, 
EventContent, EventDate, HASHBYTES('SHA1', EventContent) as ContentHash, HASHBYTES('SHA1', 
EventTitle) as TitleHash
from dbo.Events;

I was hoping to speed up comparisons on the title and content by having hashes to compare against instead of big blobs of text.

Rather than add the computed columns straight to the table, I thought I'd try the schema bound view first, but I ran into the "you need a clustered index on the view before you can create non-clustered indexes on it". So I tried to create a clustered index, as noted above.

We're talking an int in both places, but creating the clustered index on the view bombs with a truncation error, which doesn't seem to make sense.

Best Answer

The underlying table has an INT primary key column, so I was going to make the view's clustered index based on that.

Here's what Microsoft says about Create Indexed Views:

Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.

Since a clustered index is composed by data rows themselves your assumption that "the only column in the index is an int" is incorrect for, even though only one column was listed on the DDL, the clustered index view shall contain all columns under the hood. If it was a Nonclustered index, then only the listed columns would compose the index.

With the DDL you provided I was able to run some lab tests and the following chart from SSMS shall ilustrate my point:

SQL Server chart

As you can see, the view has pretty much the same size of the table. And before the index was created the view wouldn't even show on the chart. If it was formed only by the int column as you thought, it would have a much smaller size.

That info along with the contribution of SMor about the limitation of HASHBYTES Arguments for SQL Server 2008:

For SQL Server 2014 (12.x) and earlier, allowed input values are limited to 8000 bytes.

In my lab I generated some data that was greater than 8000 bytes, but since I'm running a SQL Server 2019 instance, the DDL executed without any error. You can verify if you have any values that would exceed that limit with the following query:

SELECT COUNT(1)
FROM [dbo].[Events]
WHERE DATALENGTH([EventTitle]) > 8000
    OR DATALENGTH([EventContent]) > 8000;
Related Question