38
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 ...
31
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
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
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 ...
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 ...
answered Oct 28 '13 at 0:39
19
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 ...
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 ...
17
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 ...
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(
...
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
);
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 ...
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
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
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 ...
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
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
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
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
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
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
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 ...
8
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 (...
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
7
Wait for 5.7 where generated fields are supported
mysql> CREATE TABLE sales (
-> name VARCHAR(20),
-> price_eur DOUBLE,
-> amount INT,
-> total_eur DOUBLE AS (price_eur * amount),
-> total_usd DOUBLE AS (total_eur * xrate),
-> xrate DOUBLE);
Query OK, 0 rows affected (0,16 sec)
http://mysqlserverteam.com/...
7
First, let me explain why your code doesn't work when A or B is NULL by formatting your code to show that you have 2 CASE expressions, one inside the other:
CF1 = (case
when [A]>[B] then (CF1=3)
else case
when [A]<[B] then (CF1=0)
else (CF1=1)
end
end)
If A >...
7
You don't want a calculated column, instead just use a datetime (non-nullable) column, with a default constraint using getdate().
The syntax for the column in the table definition SQL is as follows:
[autodate] DATETIME NOT NULL DEFAULT(GETDATE())
A Computed Column in SQL Server is calculated when it is queried (unless it is PERSISTED, then it has a value ...
7
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);
...
7
Is it possible to have such a visible column in SQL Server filled with automatic values, same as the hidden uniquifier column?
No there is no built-in feature to do this.
Or do I have to manually calculate such a column when I insert rows?
You could consider using INSTEAD OF or AFTER triggers to maintain the correct uniqueifier-like behaviour for the ...
answered May 3 '18 at 13:57
Only top voted, non community-wiki answers of a minimum length are eligible
