February 4, 2015 at 5:36 am
I'm trying to create an indexed view. In my table there are 5 dates, all integers in their native form e.g. 20150204. I want to out put this in the UK date format '2015-02-04'.
Firstly, I tried joining onto a calendar table but as there are 5 dates, there are 5 joins to the same table and therefore I can't create the index on the view. the message is 'The view contains a self join on "DW.dim.Calendar".'
Secondly I created a function to convert the integer to a date using the calendar dimension, but I couldn't create the index because the function 'performs user or system data access'.
Then I tried converting the integer in the query code, casting the integer first as a varchar(8) and then to a date e.g. cast(convert(varchar(8),im.RecordLastChangedDateKey) as date), but I couldn't create the index because 'The view contains a convert that is imprecise or non-deterministic'. Even converting the integer to a varchar and using left, right & substring, then casting as date gives the same response.
So now I'm stuck. Any ideas anyone?
EDIT:
I suppose what I'm asking is How do I explicitly convert an integer to a date? I think that if I can do that I'll be ok.
February 4, 2015 at 5:55 am
theboyholty (2/4/2015)
I'm trying to create an indexed view. In my table there are 5 dates, all integers in their native form e.g. 20150204. I want to out put this in the UK date format '2015-02-04'.Firstly, I tried joining onto a calendar table but as there are 5 dates, there are 5 joins to the same table and therefore I can't create the index on the view. the message is 'The view contains a self join on "DW.dim.Calendar".'
Secondly I created a function to convert the integer to a date using the calendar dimension, but I couldn't create the index because the function 'performs user or system data access'.
Then I tried converting the integer in the query code, casting the integer first as a varchar(8) and then to a date e.g. cast(convert(varchar(8),im.RecordLastChangedDateKey) as date), but I couldn't create the index because 'The view contains a convert that is imprecise or non-deterministic'. Even converting the integer to a varchar and using left, right & substring, then casting as date gives the same response.
So now I'm stuck. Any ideas anyone?
SELECT
RecordLastChangedDateKey,
CAST(CAST(RecordLastChangedDateKey AS CHAR(8))AS DATE)
FROM (SELECT RecordLastChangedDateKey = CAST(20150204 AS INT)) im
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 4, 2015 at 6:01 am
ChrisM@Work (2/4/2015)
SELECTRecordLastChangedDateKey,
CAST(CAST(RecordLastChangedDateKey AS CHAR(8))AS DATE)
FROM (SELECT RecordLastChangedDateKey = CAST(20150204 AS INT)) im
Thanks but its still giving me the message "The view contains a convert that is imprecise or non-deterministic."
February 4, 2015 at 6:04 am
theboyholty (2/4/2015)
ChrisM@Work (2/4/2015)
SELECTRecordLastChangedDateKey,
CAST(CAST(RecordLastChangedDateKey AS CHAR(8))AS DATE)
FROM (SELECT RecordLastChangedDateKey = CAST(20150204 AS INT)) im
Thanks but its still giving me the message "The view contains a convert that is imprecise or non-deterministic."
http://www.sqlservercentral.com/Forums/FindPost1478762.aspx
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 4, 2015 at 7:03 am
Try replacing CAST(... AS Date) with CONVERT(Date, ..., 112). Here is my hypothesis. Casting from a string to date is not deterministic because a change in the system culture can change how the cast statement is interpreted. However, by using Convert(Date, ..., 112) you constrain the string to be in format yyyymmdd.
Russel Loski, MCSE Business Intelligence, Data Platform
February 4, 2015 at 6:00 pm
Quite interesting.
This illustrates the problem.
CREATE VIEW dbo.Test
WITH SCHEMABINDING
AS
SELECT
RecordLastChangedDateKey=20150204,
x=CAST('20150204' AS DATE) ;
GO
CREATE UNIQUE CLUSTERED INDEX IDX_t1 ON dbo.Test (RecordLastChangedDateKey, x);
GO
SELECT *
FROM dbo.Test;
GO
DROP VIEW dbo.Test;
Perhaps this solves it?
CREATE VIEW dbo.Test
WITH SCHEMABINDING
AS
SELECT
RecordLastChangedDateKey=20150204,
x=DATEADD(day, 20150204%100 - 1, DATEADD(month, (20150204/100)%100-1, DATEADD(year, (20150204/10000)-1900, 0)));
GO
CREATE UNIQUE CLUSTERED INDEX IDX_t1 ON dbo.Test (RecordLastChangedDateKey, x);
GO
SELECT *
FROM dbo.Test;
GO
DROP VIEW dbo.Test;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 5, 2015 at 1:50 am
dwain.c (2/4/2015)
Quite interesting.This illustrates the problem.
CREATE VIEW dbo.Test
WITH SCHEMABINDING
AS
SELECT
RecordLastChangedDateKey=20150204,
x=CAST('20150204' AS DATE) ;
GO
CREATE UNIQUE CLUSTERED INDEX IDX_t1 ON dbo.Test (RecordLastChangedDateKey, x);
GO
SELECT *
FROM dbo.Test;
GO
DROP VIEW dbo.Test;
Perhaps this solves it?
CREATE VIEW dbo.Test
WITH SCHEMABINDING
AS
SELECT
RecordLastChangedDateKey=20150204,
x=DATEADD(day, 20150204%100 - 1, DATEADD(month, (20150204/100)%100-1, DATEADD(year, (20150204/10000)-1900, 0)));
GO
CREATE UNIQUE CLUSTERED INDEX IDX_t1 ON dbo.Test (RecordLastChangedDateKey, x);
GO
SELECT *
FROM dbo.Test;
GO
DROP VIEW dbo.Test;
Sweet!
In case OP didn't see it the first time, here it is again:
CREATE TABLE test (
RecordLastChangedDateKey INT,
x AS
DATEADD(day, RecordLastChangedDateKey%100 - 1,
DATEADD(month, (RecordLastChangedDateKey/100)%100-1,
DATEADD(year, (RecordLastChangedDateKey/10000)-1900, 0)))
PERSISTED
);
INSERT INTO test (RecordLastChangedDateKey) VALUES (20150204);
GO
CREATE VIEW dbo.vTest
WITH SCHEMABINDING
AS
SELECT RecordLastChangedDateKey, x FROM dbo.test;
GO
CREATE UNIQUE CLUSTERED INDEX IDX_t1 ON dbo.Test (RecordLastChangedDateKey, x);
GO
SELECT * FROM dbo.vTest;
GO
DROP VIEW dbo.vTest;
DROP TABLE test;
Edit: Here's an excellent reference - https://www.simple-talk.com/sql/learn-sql-server/sql-server-indexed-views-the-basics/[/url]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 5, 2015 at 2:01 am
ChrisM (@Home or @Work, whatever) - Your formatting certainly highlighted my calculational gymnastics better than mine did because of the limitation of the SQL code frame/window. Well done!
Interesting alternative solution for the OP to ponder on too.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply