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 ...
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 (...
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
...
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 ...
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 ...
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 ...
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(
...
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 ...
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 ...
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 [...
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 ...
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, ...
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 ...
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 (...
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 ...
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 ...
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....
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);
...
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) ...
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
