Hugo Kornelis (10/15/2010)
henrik staun poulsen (10/15/2010)
Hugo,<<If you want to index a computed column, you must persist it.
Are you sure?
I've just added a computed column to one of our big tables (+4 billion rows).
I did not add "PERSISTED" to the alter table statement.
Then I created an index on the column.
Looks fine.
A simple query on the field returns data from the index according to the plan.
But one caveat:
I have a query that currently crashes after 8 hours, when it is using the new index.
We're using SQL Server 2008 sp1 and some cu's.
You are completely right; I should have checked that statement before speaking. I'll edit my previous post.
(And on a simple test I just ran, I did not crash the query - so your problem might not be related to the indexed computed column)
hi one more doubt on creating the index on computed columns
-- Query 1 Creating the index for computed columns no Error
CREATE TABLE #tblTemp
(
Name1 varchar(10),
Col1 int,
PartitionKey as (Col1 % 10)
)
GO
CREATE CLUSTERED INDEX ix_Partitionkey on #tblTemp(PartitionKey)
GO
INSERT INTO #tblTemp(Name1,Col1) select 'Nam1',565
INSERT INTO #tblTemp(Name1,Col1) select 'Nam2',58
SELECT * FROM #tblTemp
DROP TABLE #tblTemp
GO
-- Query 2 Creating the index for computed columns Error Raised For Foat DataType
CREATE TABLE #Quantities (
ID INT IDENTITY,
DataSource varchar(30) NOT NULL,
QF1 FLOAT,
QF2 FLOAT,
QTot AS QF1 + QF2
)
GO
CREATE CLUSTERED INDEX IX_PF1 on #Quantities(QTot)
GO
drop table #Quantities
/*
-- Error
Msg 2799, Level 16, State 1, Line 1
Cannot create index or statistics 'IX_PF1' on table '#Quantities' because the computed column 'QTot' is imprecise and not persisted.
Consider removing column from index or statistics key or marking computed column persisted.
*/
-- Query 3 Creating the index for computed columns No Error for Decimal datattype
CREATE TABLE #Quantities2 (
ID INT IDENTITY,
DataSource varchar(30) NOT NULL,
QF1 DECIMAL(18,2),
QF2 DECIMAL(18,2),
QTot AS QF1 + QF2
)
GO
CREATE CLUSTERED INDEX IX_PF1 on #Quantities2(QTot)
GO
drop table #Quantities2
in the above Quesries second one (Query 2) thrown on error while creating the index on computed column but the Query 1 and Query 3 not thrown error i'm confused about this
Thanks & Regards
Deepak.A