3

What is the Datatype for a Persisted Computed Column?

(a) Does SQL Server automatically infer it?

(b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it is?

create table dbo.CustomerTransaction
(
   CustomerTransactionId int primary key identity(1,1),
   CustomerId int,
   Price decimal(10,2),
   Quantity int,
   TotalAmount as CustomerId * Price PERSISTED  -- should I cast as cast(CustomerId* Price as decimal(10,2) ??
)
  • 2
    Sort of like using SELECT INTO, SQL Server infers it based on data type precedence. If you need it to be something specific, you should CONVERT it to that specific type. – Erik Darling Oct 23 '18 at 16:57
  • I would vote for explicitly converting the type to avoid issues. And maybe persist the column? Disk is cheap. – Jonathan Fite Oct 23 '18 at 19:30
  • Agree with all the above comments. This answer is relevant to your question. – SqlWorldWide Oct 24 '18 at 12:03
0

Information turned into a Community Wiki answer from this question on Stack Overflow: What is the data type of a computed column?

It's determined by the value with the highest datatype precedence.

You can use sql_variant_property to determine what datatype a literal expression is as per my answer here. e.g. 2147483648 is interpreted as numeric(10,0) rather than bigint.

In addition, if you want to force a specific data type instead of relying on data type precedence, you can use CONVERT or CAST in the computation to force it (assuming all potential outcomes are compatible with the type you choose). This can be very useful in cases where, by default, you end up with a wider data type than you intended; the most common use case I've seen is when you end up with an INT instead of, say, a BIT:

Active1 AS CASE WHEN something THEN 1 ELSE 0 END,
Active2 AS CONVERT(BIT, CASE WHEN something THEN 1 ELSE 0 END)

In this case Active1 is an INT (4 bytes) while Active2 is a BIT (1 byte - or less, potentially, if it is adjacent to other BIT columns).

Your Answer

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