A Computed Column in SQL Server allows you to create a read-only calculated field that performs some logic but occupies no space in the database.
We have an Access front-end with a SQL Server database & I was looking for ways to improve the user experience. I noted that the query behind one of the forms opened "OK" but scrolling thru the records was a little sluggish. The form itself is a continuous form. I suspected that an IIf() statement was the culprit.
The IIf() statement drives a control that displays "A" if the record has an approval date, like this:
=IIf(IsNull([approval_date]),"","A")
I created a computed column in the SQL table using this statement:
ALTER TABLE my_table
ADD A_display
AS
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
...and then in the application, I replaced that IIf() statement with the new column A_display; thus duplicating the Access statement. This did speed up the query quite a bit.
Now, looking back at the modified table, I can see that A_display shows as
varchar(1) NOT NULL
...which is fine for my purpose. But you can modify that behavior by adding to the statement. For example, we might want the output to be nullable - so if you set the control's background property to Transparent (instead of Normal) then you can see through it, if there's no approval date.
To make the column nullable, we could just change the original logic like this:
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE NULL
END
...but if your statement is more complex, you might use NULLIF:
NULLIF(
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
,'')
You can also control the data type and size by using CAST:
CAST(
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE
NULL
END AS nvarchar(1)
)
which gives you: nvarchar(1) NULL
