Timeline for Truncation error creating clustered index on schema-bound view
Current License: CC BY-SA 4.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 17, 2020 at 15:04 | comment | added | user1664043 | Thanks guys. I have reposted the additional detail in a new question: dba.stackexchange.com/questions/265277/… | |
| Apr 17, 2020 at 14:13 | comment | added | Ronaldo | I see. If you open a new question about how to improve that comparison, maybe a new solution will be achieved. I'd like to help with that, but I don't think discussing this other subject on the comments for this question is appropriate for the sake of organization :) | |
| Apr 17, 2020 at 13:19 | comment | added | user1664043 | There's this query that's looking for "duplicate" rows within the last 7 days, and it's taking a very long time comparing the title and the content. I was interested in seeing if comparing the hashes would improve the performance. At this point it's just a theory I want to run tests on. There's replication set up on the table so I didn't want to actually change the underlying table's make-up to run the test. I figured the indexed view would allow me to test in a way that I can just drop after without any impact on running processes. | |
| Apr 17, 2020 at 13:15 | vote | accept | user1664043 | ||
| Apr 17, 2020 at 12:30 | comment | added | SMor | I suspect that's what happening with your attempt to index the view, the data must be materialized. You could try casting your max columns to the smaller size to see if that works. But I just don't see the point of this view and how it "improves" anything. | |
| Apr 17, 2020 at 1:04 | answer | added | Ronaldo | timeline score: 0 | |
| Apr 16, 2020 at 21:25 | comment | added | user1664043 | Ah! Sorry if I'm slow on the uptake, but is what you and SMor are saying is that to build the clustered index, sql server is invoking the view (hence dragging along the two HASHBYTES() columns - which would be susceptible to the HASHBYTES size limit)? Okay, that makes some sense. I was hung up on "how can an int get truncated?" But if the index is being built by sifting over the results the view returns (even though those columns wouldn't be used), then I can see how the truncation error would happen. | |
| Apr 16, 2020 at 20:19 | comment | added | Ronaldo |
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?
|
|
| Apr 16, 2020 at 19:21 | history | edited | LowlyDBA - John M |
Version tag
|
|
| Apr 16, 2020 at 19:18 | comment | added | user1664043 | 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. | |
| Apr 16, 2020 at 19:02 | comment | added | SMor | 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? | |
| Apr 16, 2020 at 18:23 | comment | added | user1664043 | Thanks for responding. I edited the post to contain more of the ddl. | |
| Apr 16, 2020 at 18:22 | history | edited | user1664043 | CC BY-SA 4.0 |
added 1351 characters in body
|
| Apr 16, 2020 at 18:09 | comment | added | Ronaldo | 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. | |
| Apr 16, 2020 at 17:52 | history | edited | user1664043 | CC BY-SA 4.0 |
edited title
|
| Apr 16, 2020 at 17:45 | history | asked | user1664043 | CC BY-SA 4.0 |