Indexing Computed Columns

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737630

  More actions

  Follow

  November 8, 2018 at 10:42 pm

  #416476

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

• Stewart "Arturius" Campbell

  SSC Guru

  Points: 72697

  More actions

  November 8, 2018 at 10:53 pm

  #2012929

  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: 22737

  More actions

  November 9, 2018 at 1:54 am

  #2012933

  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: 1004577

  More actions

  November 10, 2018 at 8:00 am

  #2013066

  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

  November 14, 2018 at 5:57 am

  #2013420

  nice question steve, it had me stumped

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

• Carlo Romagnano

  SSC-Insane

  Points: 22737

  More actions

  November 14, 2018 at 6:43 am

  #2013442

  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

  November 15, 2018 at 4:53 am

  #2013622

  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