November 20, 2007 at 5:06 am
Hi,
I try build an index on a function column of a Table bau gt an error.
This is the Column
-----------------
[IDconsel] AS ([dbo].[fnGetIdRowConsel]([Prezzo],[haveTasso0]))
This the function
------------------
FUNCTION [dbo].[fnGetIdRowConsel](
@Prezzo int,
@tasso int
)
RETURNS bigint
with schemabinding
AS
BEGIN
DECLARE @ID bigint
SELECT TOP 1 @ID = IDconsel FROM sqltest.ePrice2K5.dbo.Consel WHERE imp_fin <= @Prezzo AND tasso0 = @tasso ORDER BY imp_fin DESC
Return @ID
END
This is the error
---------------
Column 'IDconsel' in table 'dbo.TMP_Consel' cannot be used in an index or statistics or as a partition key because it is non-deterministic
The complete structure of the table
-----------------
CREATE TABLE [dbo].[TMP_Consel] (
[IDListino] int NOT NULL,
[IDsito] int NOT NULL,
[isB2B] bit NULL,
[Nome] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sku] bigint NOT NULL,
[IDricarico] int NOT NULL,
[Prezzo] bigint NULL,
[haveTasso0] int NOT NULL,
[DataDal] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataAl] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Testo] varchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MinTasso0] int NULL,
[MinNoTasso0] int NULL,
[IDconsel] AS ([dbo].[fnGetIdRowConsel]([Prezzo],[haveTasso0]))
)
ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CINDX] ON [dbo].[TMP_Consel]
([IDListino], [IDsito], [sku], [IDricarico])
WITH (
PAD_INDEX = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
Thanks Alen, Italy
November 20, 2007 at 5:29 am
You can define indexes on computed columns as long as the following requirements are met:
1. Ownership requirements
2. Determinism requirements (which in your case is violated by using TOP)
3. Precision requirements
4. Data type requirements
5. SET option requirements
--Ramesh
November 20, 2007 at 5:53 am
Funny, I was just reading about this, you can't use the TOP without an ORDER BY in that situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 20, 2007 at 6:14 am
So...there isn't other way for elude TOP in the query?
November 20, 2007 at 6:44 am
Alen cappelletti (11/20/2007)
I use this soluction for the function...SELECT
MAX(IDconsel)
FROM sqltest.ePrice2K5.dbo.Consel
WHERE imp_fin <= @Prezzo AND tasso0 = @Tasso0
I don't think this is going to work.
Initially, I said the function is violating point(2) by using TOP clause, but in fact its not because of TOP, rather because of user or system data access.
My apologies for pointing you in wrong direction.;)
--Ramesh
November 20, 2007 at 6:56 am
Yes don't run...
Can U explain me better please as I can index that column?
November 20, 2007 at 7:08 am
Oh heck, I misread. You do have an ORDER BY. Oops. Sorry for the bad info.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 20, 2007 at 7:46 am
Alen,
Without changing the design, i don't think we can find ways to index that computed column.
You can try the alternative approaches such as using triggers or joins.
Here is the one using joins.....
SELECTT.*, MAX( C.IDconsel ) AS [IDconsel]
FROM[TMP_Consel] T
INNER JOIN sqltest.ePrice2K5.dbo.Consel C ON C.imp_fin <= T.[Prezzo] AND C.tasso0 = T.[haveTasso0]
--Ramesh
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply