1

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.

  • 1
    I'm not sure about clustered views, but when creating a clustered index, you're defining how to organize the whole data on a table. If a clustered view on an index does the same it's creating an structure with all columns that compose the view onganized by EventID. If that's correct you could be getting the error because of another column of the view. Are there other columns on that view? If possible, provide the view definition and the definition of the tables involved, they might be helpful to determine the error cause. – Ronaldo Apr 16 at 18:09
  • Thanks for responding. I edited the post to contain more of the ddl. – user1664043 Apr 16 at 18:23
  • 1
    What version of sql server are you using? Did you notice that SHA1 is deprecated. And did you notice the restriction about the input size to HASHBYTES? – SMor Apr 16 at 19:02
  • Stuck on Sql Server 2008 at the moment. Thanks, I hadn't noticed the input length limit on HASHBYTES. But this problem is before I get that far. – user1664043 Apr 16 at 19:18
  • I was able to run your DDL without the error, so the problem is either related to your data on that table or to your version of SQL Server (I'm running a SQL Server 2019 instance). SMor pointed out something that is really relevant on the error you're getting. The doc says For SQL Server 2014 (12.x) and earlier, allowed input values are limited to 8000 bytes.. Can you query for a value bigger than that on your [varchar](max) columns? – Ronaldo Apr 16 at 20:19
0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.