41
Most of the information you will need is going to be in the execution plan ( and the plan XML).
Take this query:
SELECT COUNT(val) As ColA,
COUNT(val2) As ColB,
COUNT(val) + COUNT(val2) As ColC
FROM dbo.TableA;
The execution plan (opened with sentryone plan explorer) shows what steps it went through:
With the stream aggregate aggregating ...
34
This is very easy to prove on your own. We can create a table with a computed column that uses a scalar user-defined function, and then check plans and function stats before and after both an update and select, and see when an execution gets recorded.
Let's say we have this function:
CREATE FUNCTION dbo.mask(@x varchar(32))
RETURNS varchar(32) WITH ...
answered Apr 27 '16 at 14:33
Aaron Bertrand
170k2323 gold badges358358 silver badges565565 bronze badges
33
Yes if you:
are running SQL Server 2014 or later; and
are able to run the query with trace flag 176 active; and
the computed column is PERSISTED
Specifically, at least the following versions are required:
Cumulative Update 2 for SQL Server 2016 SP1
Cumulative Update 4 for SQL Server 2016 RTM
Cumulative Update 6 for SQL Server 2014 SP2
BUT to avoid a bug (...
answered Oct 1 '17 at 6:21
22
Unfortunately as of SQL Server 2014, there is no ability to create a Filtered Index where the Filter is on a Computed Column (regardless of whether or not it is persisted).
There has been a Connect Item open since 2009, so please go ahead and vote for it. Maybe Microsoft will fix this one day.
Aaron Bertrand has an article that covers a number of other ...
21
Although you cannot create a filtered index on a persisted column, there is a fairly simple workaround that you may be able to use.
As a test, I've created a simple table with an IDENTITY column, and a persisted computed column based on the identity column:
USE tempdb;
CREATE TABLE dbo.PersistedViewTest
(
PersistedViewTest_ID INT NOT NULL
...
answered Sep 28 '15 at 16:19
21
The [Ranking] field is showing as "Nullable" due to being a computed column. Yes, it is declared as NOT NULL, but as the MSDN page for Computed Columns states, the database engine can change that determination at query-time:
The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most ...
answered Sep 5 '15 at 12:17
Solomon Rutzky
61.2k66 gold badges123123 silver badges248248 bronze badges
21
Not sure if this is what you want, but attribute notation row.full_name and function notation full_name(row) are equivalent in postgresql.
That means you take a table
CREATE TABLE people (
first_name text,
last_name text
);
and a function:
CREATE FUNCTION full_name(people) RETURNS text AS $$
SELECT $1.first_name || ' ' || $1.last_name;
$$ LANGUAGE ...
20
Why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why?
Columns A, B, and C are referenced in the query plan - they are used by the seek on T2.
Also, why can the query use the index on t2, but not on t1?
The optimizer decided that scanning the clustered index ...
answered Oct 28 '13 at 0:39
19
It depends on how you define the computed column. A PERSISTED computed column will be calculated and then stored as data inside the table. If you do not define the column as PERSISTED, it will be calculated when your query is run.
Please see Aaron's answer for a great explanation and proof.
Pinal Dave also describes this in detail and shows proof of ...
13
To guarantee that the Ranking computed column expression does not return NULL in any circumstances, you must wrap it in ISNULL with a suitable default value. For example:
Ranking AS ISNULL(Id + RankingBonus, 0) PERSISTED NOT NULL
The NOT NULL constraint ensures the persisted value is not null, in the context of the table- and session-level settings in ...
answered Sep 5 '15 at 12:26
12
The problem seems to be related to the fact that [TestGeocode].[ToString]() returns a max datatype (nvarchar(max)).
I also encounter the issue with this simpler version (changing the definition of c1 to varchar(8000) or using COALESCE instead of ISNULL resolves it)
DROP TABLE dbo.Test
CREATE TABLE dbo.Test
(
c1 VARCHAR(
...
answered Jul 17 '15 at 20:10
Martin Smith
75.1k1212 gold badges217217 silver badges305305 bronze badges
12
Just add a special case for division by 0:
CREATE TABLE [dbo].[TblDivision]
(
[Numerator] int NOT NULL,
[Denominator] int NOT NULL,
[Result] AS case when Denominator=0 then 0 else (Numerator/ Denominator) end
);
12
In addition to @Paul's excellent Yes #1, there is actually a Yes #2 that:
works as far back as SQL Server 2005,
does not require setting a trace flag,
does not require that the computed column be PERSISTED, and
(due to not requiring trace flag 176), does not prevent query expression matching to persisted computed columns
The only drawbacks (as far as I can ...
answered Oct 19 '17 at 19:55
Solomon Rutzky
61.2k66 gold badges123123 silver badges248248 bronze badges
12
I'm not sure what "http://www.sql-server-helper.com/" is, but this is from the current, official documentation:
The following built-in functions from other categories are always nondeterministic.
...
PARSENAME
Side note: it looks like PARSENAME was deterministic, at least on SQL Server 2005 (thanks for that link, jpa). So perhaps that other site ...
11
When SQL Server creates the index on the computed field, the computed field is written to disk at that time - but only on the 8K pages of that index. SQL Server can compute the InvoiceStatusID as it reads through the clustered index - there's no need to write that data to the clustered index.
As you delete/update/insert rows in dbo.Invoice, the data in the ...
11
You need to use a deterministic style when converting from a string representation.
You were not using a deterministic style with the conversion from string to date.
You were unnecessarily specifying a style when converting from date to datetime2.
There is a confusing mixture of date/time data types in the question.
This works (producing a datetime ...
answered Oct 19 '18 at 11:10
10
Similar to @Phil's solution:
CREATE TABLE dbo.TblDivision
( Numerator int NOT NULL
, Denominator int NOT NULL
, Result AS Numerator / nullif(Denominator,0)
);
If Denominator is 0 it is mapped to null via nullif. Since anything divided by null is null the result becomes null in this case.
10
Try with COALESCE instead of ISNULL. With ISNULL, SQL Server doesn't seem capable of pushing a predicate against the narrower index, and therefore has to scan the clustered to find the information.
CREATE TABLE dbo.Diffs
(
Id int NOT NULL IDENTITY (1, 1),
DataA int NULL,
DataB int NULL,
DiffPersisted AS COALESCE(convert(bit, case when [...
answered Dec 16 '15 at 19:34
Aaron Bertrand
170k2323 gold badges358358 silver badges565565 bronze badges
10
Is there any need to persist these columns?
Generally, no, though there are a number of potential issues that will require validation with your particular workload and client applications.
The major requirements (including SET option requirements for all clients) are listed in the documentation at Indexes on Computed Columns. You may also, for example, be ...
answered Jan 18 '18 at 10:37
10
If computers were infinitely fast, then, No, you would never store a value that could be calculated from other columns in the database. Storing calculated values is a violation of database normalization.
Examples would include:
The extended cost on an invoice line that carries price and quantity fields.
The total cost of an invoice’s lines.
In the real ...
9
However, execution plans still show an implicit conversion warning even though the conversion happens when the table is updated, not when it is read from.
Persisting a computed column does not guarantee that the persisted value will be used. The optimizer makes a cost-based decision between using the persisted value and computing the expression afresh, ...
answered Dec 19 '15 at 6:36
9
This is certainly a bug. The fact that the col1 values happened to be the result of an expression involving random numbers clearly doesn't change what the the correct value for col2 is supposed to be. DBCC CHECKDB returns an error if this is run against a permanent table.
create table test (
Col1 INT,
Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 ...
answered Jul 7 '16 at 10:24
Martin Smith
75.1k1212 gold badges217217 silver badges305305 bronze badges
9
Still don't understand why this needs to be a column in a table, never mind a persisted one.
Why not just create a table-valued function that you cross apply when (and only when) the query actually needs it? Since the old key will never change it doesn't need to be computed or persisted anyway.
If you really want the old key to live in multiple places (...
answered Apr 20 '18 at 18:22
Aaron Bertrand
170k2323 gold badges358358 silver badges565565 bronze badges
9
I'm just curious why the plan changes this way after I index the computed column
It's not indexing the computed column that really matters; any additional non-covering index can produce the same effect (so long as the table occupies more than one page). With only a clustered index present (or a fully covering nonclustered index), the optimizer can produce a ...
answered May 7 '18 at 8:02
9
Converting a string to a date without a style number is not deterministic, also there is no reason to use a style number when converting a date or datetime to datetime2. Try:
ALTER TABLE dbo.test
ADD UTCTime AS CONVERT(datetime2,ISNULL(EventTime,
DATEADD(SECOND, PosixTime, CONVERT(datetime,'1970-01-01',120))))
PERSISTED;
Though I'm curious ...
answered Oct 19 '18 at 11:03
Aaron Bertrand
170k2323 gold badges358358 silver badges565565 bronze badges
8
The value for an indexed, non-persisted computed column is not persisted in the data pages of the table, but it is persisted in the pages of the index. It remains non-persisted in the table, regardless of whether it is persisted in 0, 1, or multiple indexes.
Just to illustrate Brent's description, taking the example you gave, let's insert a row:
INSERT dbo....
answered Feb 7 '17 at 21:29
Aaron Bertrand
170k2323 gold badges358358 silver badges565565 bronze badges
8
The attribute PERSISTED for a computed column relates to whether the values are persisted in the table (clustered index or heap) and not whether the values are persisted in the index.
The CREATE INDEX has the requirements for the limitations regarding computed columns and indexes:
Computed columns that are deterministic and either precise or imprecise can ...
8
I'm not sure why you think you need a function or a computed column to do this. You can just add a new column to the table with a default value and index it however you want.
CREATE TABLE dbo.whatever ( Id INT );
ALTER TABLE dbo.whatever
ADD YourMom UNIQUEIDENTIFIER
DEFAULT NEWSEQUENTIALID();
CREATE INDEX ix_whatever ON dbo.whatever (YourMom);
...
answered Apr 20 '18 at 16:53
Erik Darling
30.8k1313 gold badges100100 silver badges179179 bronze badges
8
You could use the CONCAT function in SQL Server 2012 or later, which automatically ignores NULL, and implicitly converts the inputs to string types if necessary.
Example:
CREATE TABLE #Demo
(
[Tag Type] char(1) NOT NULL,
[Parent Tag Type] char(1) NULL,
[Tag Area No] integer NOT NULL,
[Tag seq No] integer NOT NULL,
[Tag Suffix] char(1) ...
answered Jun 15 '18 at 5:10
8
To answer the why part of your question, the CASE expression result is nullable because the CAST function result is nullable. This can be observed with:
EXEC sp_describe_first_result_set N'SELECT CAST(1 AS bit) AS result;';
Result (abbreviated):
+-----------+----------------+--------+-------------+----------------+------------------+
| is_hidden | ...
Only top voted, non community-wiki answers of a minimum length are eligible
Related Tags
computed-column × 95sql-server × 57
mysql × 13
postgresql × 13
index × 9
t-sql × 8
sql-server-2014 × 8
sql-server-2016 × 8
oracle × 6
view × 6
sql-server-2012 × 5
functions × 5
database-design × 4
sql-server-2008-r2 × 4
performance × 4
optimization × 4
null × 4
unique-constraint × 4
query-performance × 3
oracle-12c × 3
sql-server-2017 × 3
alter-table × 3
sql-server-2008 × 2
execution-plan × 2
partitioning × 2