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
The only column in the index is an int; what could be getting truncated?
I couldn't find anything relevant on google.
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.