1

What I want to achieve: To create an index with an existing date column data converted into UTC timing (Query is written below)

Issue: There's a column in table with local server date values, I need to convert them into UTC date value and then create an Index over it.

Why I need this: I have the same thing done in oracle, and I am trying to migrate stuff along with queries into Sql Server for a new client

Problem: Index doesn't take variables or user defined functions. But only takes table columns as parameters.

Only Work around: is to make a computed column on table and use it to create the index.

Steps I followed:

  1. Ran the below queries at first

    ALTER TABLE dbo.tableClient ADD tempComp1 AS DATEADD(minute, datediff(minute, GETUTCDATE(), getdate()), [svrDate])
GO
create index idx1 on dbo.tableClient([key] asc, [tempComp1] desc, [type])
GO

    It gives the below error:

    Column 'tempComp1' in table 'dbo.tableClient' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

  2. So I tried making the column as PERSISTED

    ALTER TABLE dbo.tableClient ADD tempComp1 AS DATEADD(minute, datediff(minute, GETUTCDATE(), getdate()), [svrDate]) PERSISTED

    it now giving the error:

    Computed column 'tempComp1' in table 'tableClient' cannot be persisted because the column is non-deterministic.

Now, the funny thing is, if I do

SELECT datediff(minute, GETUTCDATE(), getdate())

it gives result: 330
Now if I try the same commands with 330

ALTER TABLE dbo.tableClient ADD tempComp1 AS DATEADD(minute, 330, [svrDate]) PERSISTED
GO
create index idx1 on dbo.tableClient([key] asc, [tempComp1] desc, [type])
GO

it works absolutely fine.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Veer
  • 23
  • 2
  • 1
    `GETDATE()` isn't deterministic, it *can't* be because its value changes **every** time you use it. A deterministic expression will return the same value **every single time** you run it with the same parameters; regardless of things like language settings. This is why `330` is deterministic, because it's a literal. `SELECT 330` is never going to return anything other than the value `330`. – Thom A Jun 29 '21 at 11:49
  • 1
    Any other work around that you can suggest, I am out of options in my mind to be honest. Below is the oracle query which I am trying to change `CREATE INDEX idx1 on tableClient (key asc, sys_extract_utc(svrDate) desc ,type)` – Veer Jun 29 '21 at 12:06
  • Honestly, seems like you should be storing your data in a `datetimeoffset`. – Thom A Jun 29 '21 at 12:07
  • Since I'll be creating a new DB, so let's say even if I create **[svrDate]** column of the type _datetimeoffset_. Wouldn't I have to create a computed column to create the Index in that case also. – Veer Jun 29 '21 at 12:23
  • I don't even get the logic of how you expect the server to persist such information. When should it do it? When you query, when you insert, when you last update? – Charlieface Jun 29 '21 at 13:57
  • Persisting info.. - I Didn't get you. If you're referring to initial query which I wrote, then we're over that discussion as rightly pointed out by @Larnu above about GETDATE(). For in-depth logic: The oracle query I wrote above is a liquibase changeset. and all I am trying to write is the equivalent for sql server. To answer your question: _When should server do it_: sequentially everytime the liquibase runs So, in short, I am only looking for the equivalent query / work-around of oracle query in sql server – Veer Jun 29 '21 at 15:51
  • Sounds like what you actually need is perhaps a computed column `svrDate AT TIME ZONE 'Some Time Zone Name'`. You cannot use the server time zone dynamically because that is also non-deterministic – Charlieface Jun 29 '21 at 18:23
  • Ya, trying to figure out how even that'll rescue me from this situation, coz Index only takes columns as parameters. No sub-queries, no variables, no funtions.. Phew!! – Veer Jun 30 '21 at 06:57

0 Answers0