July 29, 2013 at 2:17 pm
So I am attempting to create a clustered index on a view. I am trying to create the index off of the following fields (order_date (datetime), offer_code varchar(3), and demandtype varchar(1)).
When I try and created the clustered index I receive the following error:
Cannot create index or statistics 'IX_vw_sale_demand' on view 'vw_sale_demand' because key column 'INDATE' is imprecise, computed and not persisted. Consider removing reference to column in view index or statistics key or changing column to be precise. If column is computed in base table consider marking it PERSISTED there.
Now the order_date is stored as a decimal(10,0) in the base table and I am converting it to a datetime in my view. From what I have read - the error happens mostly when using float values. Any ideas on how to fix this?
Thanks for the help in advance - will continue to search for an answer and will post if I find one......
July 29, 2013 at 2:39 pm
smrobin (7/29/2013)
So I am attempting to create a clustered index on a view. I am trying to create the index off of the following fields (order_date (datetime), offer_code varchar(3), and demandtype varchar(1)).When I try and created the clustered index I receive the following error:
Cannot create index or statistics 'IX_vw_sale_demand' on view 'vw_sale_demand' because key column 'INDATE' is imprecise, computed and not persisted. Consider removing reference to column in view index or statistics key or changing column to be precise. If column is computed in base table consider marking it PERSISTED there.
Now the order_date is stored as a decimal(10,0) in the base table and I am converting it to a datetime in my view. From what I have read - the error happens mostly when using float values. Any ideas on how to fix this?
Thanks for the help in advance - will continue to search for an answer and will post if I find one......
Can you change your base table so that is has datetime instead of decimal(10,0)?
Without more details it is difficult to help much. If you could at least provide ddl for the base table and the view definition you are trying.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2013 at 3:48 pm
decimal(10,0) and datetime are not really the same thing...
The error message is clear: it says that you need to add a computed and persisted column to the base table which holds the conversion. That is:
ALTER TABLE tbl ADD INDATE AS (...) PERSISTED
As for how to convert that decimal(10,0) value to datetime, that depends on what is in that column.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply