Indexing Computed Columns

Question

Post reply

Indexing Computed Columns

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
November 8, 2018 at 10:42 pm

#416476

Comments posted to this topic are about the item Indexing Computed Columns

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72485 More actions · Answer 1

Really interesting question, thanks Steve

forgot that the results of this datetime function is non-deterministic....

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 2

Stewart "Arturius" Campbell - Thursday, November 8, 2018 10:53 PM
Really interesting question, thanks Steve
forgot that the results of this datetime function is non-deterministic....

DATEPART not always is nondeterministic.
e.g. datepart(day,'20180131') is deterministic because it returns always 31
e.g. datepart(dw,'20180131') is NONdeterministic, because it depends from localization.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

I believe you meant "not" rather than "now".

However, your first example is also non-deterministic. Here's the proof.

Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME);
GO
ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(day,'20180131') PERSISTED;

Msg 4936, Level 16, State 1, Line 1
Computed column 'WhichDay' in table 'SalesOrderHeader' cannot be persisted because the column is non-deterministic.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 4

nice question steve, it had me stumped

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 5

Jeff Moden - Saturday, November 10, 2018 8:00 AM
I believe you meant "not" rather than "now".
However, your first example is also non-deterministic. Here's the proof.
Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME);
GO
ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(day,'20180131') PERSISTED;
Msg 4936, Level 16, State 1, Line 1
Computed column 'WhichDay' in table 'SalesOrderHeader' cannot be persisted because the column is non-deterministic.

Here an example where it is deterministic:
DROP table dbo.SalesOrderHeader Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME NOT NULL); ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(d,SomeDate) PERSISTED; create index idx_SalesOrderHeader ON SalesOrderHeader(WhichDay)
Commands completed successfully.

The difference is the second input parameter:
with string is non-deterministic
with a valid datetime is deterministic

pbelter SSC Rookie Points: 28 More actions · Answer 6

Carlo Romagnano - Wednesday, November 14, 2018 6:43 AM
Jeff Moden - Saturday, November 10, 2018 8:00 AM
I believe you meant "not" rather than "now".
However, your first example is also non-deterministic. Here's the proof.
Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME);
GO
ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(day,'20180131') PERSISTED;
Msg 4936, Level 16, State 1, Line 1
Computed column 'WhichDay' in table 'SalesOrderHeader' cannot be persisted because the column is non-deterministic.
Here an example where it is deterministic:
DROP table dbo.SalesOrderHeader Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME NOT NULL); ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(d,SomeDate) PERSISTED; create index idx_SalesOrderHeader ON SalesOrderHeader(WhichDay)
Commands completed successfully.
The difference is the second input parameter:
with string is non-deterministic
with a valid datetime is deterministic

Wow, that just gave me a headache.LOL But hey I forgot the deterministic rule. OLD AGE is A bummer.
Thanks for the schooling!
Paul