Max Length for Computed Column Specification?

Question

I ran into an intriguing problem while doing some testing on a very large dimension table. The table has about 370 columns which are all char/varchar. I have implemented a RowHashKey using HASHBYTES() by concatenating all of the columns and casting to bigint. This value is then used during ETL to see if the data in the staging table is different than the dimension table. This has worked fine in a view and in a trigger, and I was now going to test on a persisted computed column.

While trying to to a table create with the computed column specification, I immediately got back the following error "Computed column 'RowHashKey' in table 'EtlTest' cannot be persisted because the column is non-deterministic."  After much fiddling, I found that no matter which (or how many) columns I used in the HASHBYTES() function, I would get this error message whenever the string length for the computed column specification was longer than about 22,000 characters. 

I next tried breaking up HASHBYTES() into multiple computed columns, and found that I would receive the same error whenever the TOTAL string length of ALL computed columns in the create table statement was longer than about 22,000 characters.

So the question is - what are the limits with string length (if any) for a computed column specification. If there is no limit, then please help me understand why I am encountering these issues.

Any information, insights, corroboration, or otherwise is greatly appreciated. 

Answer 1

A computed column will have a datatype (either because you explicitly dis a CAST or CONVERT to that datatype or it will be derived from the data type precedence rules.  But computed columns can certainly be longer than 22,000 characters if the datatype allows strings longer than that.  For example

create table #foo(a varchar(max), b as replicate(a, 1000) persisted);
insert #foo(a) Values (replicate('abc', 1000))
select len(a) as LenA, len(b) As LenB from #foo
go
drop table #foo

creates a table with a varchar(max) column, then puts a value into that column that is 3,000,000 characters long.

The best way for us to help you find the cause of your error would be for you to give us the create table statement that gets this error. 

Tom

P.S - a warning about using HashBytes() to determine if data has changed.  If the HashBytes values are different then the data has changed.  But the converse is not true.  If is possible (though unlikely) for the data to change, but the HashBytes values to be the same.

Answer 2

I would get this error message whenever the string length for the computed column specification was longer than about 22,000 characters. 

What is the point of storing such large computed string?

Why don't you just generate it with a query when needed?

---

---

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014