SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexing Computed Columns


Indexing Computed Columns

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)SSC Guru (611K reputation)

Group: Administrators
Points: 611848 Visits: 21182
Comments posted to this topic are about the item Indexing Computed Columns

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62441 Visits: 8171
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
Carlo Romagnano
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19320 Visits: 3729
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
Jeff Moden
SSC Guru
SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)SSC Guru (884K reputation)

Group: General Forum Members
Points: 884766 Visits: 47952


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Shayn Thomas
Shayn Thomas
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4009 Visits: 781
nice question steve, it had me stumped

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
Carlo Romagnano
Carlo Romagnano
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19320 Visits: 3729
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
pbelter
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 12
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search