CODEWITHSUNDEEP
sqlsql-servert-sql
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33964

Add Computed Column and Put Index on it

I have a table which include,

CREATE TABLE [dbo].[ProductsAttributesValues](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [AttributeId] [int] NOT NULL,
    [Value] [nvarchar](3000) NOT NULL
)

this table include huge amount of rows. The problem is that lot of time the [Value] column include float or int values but sometimes it include text as well. Now, I am thinking to add an Index on float or int values so that the index will use <, >, <=, >= and BETWEEN operators efficiently. So, how can I create a computed column which will cast the value to float and then add index to this computed column? Is this possible?

Upvotes: 1

Views: 99

Answers (1)

Donal
Donal

Reputation: 32713

A float expression is considered imprecise and cannot be a key of an index; a float expression can be used in an indexed view but not as a key. This is true also for computed columns. Any function, expression, or user-defined function is considered imprecise if it contains any float expressions. This includes logical ones (comparisons).

However, you You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. Taken from here.

-- This will add your float computed column:
ALTER TABLE ProductsAttributesValues ADD Value_Float AS 
(
    CASE WHEN ISNUMERIC([Value]) = 1 THEN
        CONVERT(float, [Value]) 
    ELSE 
        NULL 
    END 
) PERSISTED;

-- This will create the index:
CREATE INDEX IX_ProductsAttributesValues_Value_Float 
ON ProductsAttributesValues (Value_Float);

Upvotes: 1

Related Questions