CODEWITHSUNDEEP
sql-serverindexingdeterministicnon-deterministic
Shahid Thaika
Shahid Thaika

Reputation: 2305

Unable to make computed column indexable

CREATE FUNCTION dbo.FN_GET_YEAR(@ID int) RETURNS int WITH SCHEMABINDING AS
BEGIN
    RETURN (SELECT YEAR(begin_date) FROM dbo.tableName WHERE id = @ID);
END

GO

CREATE TABLE test_table (
    id int,
    fk_id int,
    test AS dbo.FN_GET_YEAR(id)
);

SELECT COLUMNPROPERTY(OBJECT_ID('test_table'), 'test', 'IsIndexable') AS IsIndexableColumn;

The above is a very simplified extract of my actual problem. I have a function that returns the year of a given date and then that function is used as a computed column. I want the computed column to be indexable.

However, despite trying all the suggestions online, I am not sure what I am missing. I used the 'WITH SCHEMABINDING' key word and also tried with and without CONVERT/CAST. The problem is that the column is not deterministic, but documentation also says that YEAR() is deterministic.

If I simply return a static declared value in the function, then the column becomes indexable. YEAR() seems to break it.

EDIT:

I didn't want to post my original queries to keep things simple, but perhaps I made it too simple. I updated the function query closer to actual.

Upvotes: 1

Views: 85

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

"I need to restrict the foreign key and year combo to once a year. So it will be a UNIQUE index."

I'm not sure what you're attempting is a good idea, but it certainly can't be enforced with an index on a computed column, as a change in either table needs to be prevented.

You can do this sort of thing with an indexed view. EG:

drop view if exists v_test_table_year
drop table if exists test_table
drop table if exists tablename
go
create table tableName(id int, begin_date datetime)

go

CREATE TABLE test_table (
    id int,
    fk_id int--,
   -- test AS dbo.FN_GET_YEAR(id)
);

go
create or alter view v_test_table_year
with schemabinding
as
select year(tn.begin_date) year
from dbo.test_table t
join dbo.tableName tn
  on t.fk_id = tn.id

 go
 create unique clustered index ci_v_test_table_year
 on v_test_table_year(year)

 go

 insert into tableName(id,begin_date) values (1,'2017-01-01')
 insert into tableName(id,begin_date) values (2,'2017-02-01')

 insert into test_table(id,fk_id) values (1,1) --ok
 insert into test_table(id,fk_id) values (2,2) --fails

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

I have a function that returns the year of a given date and then that function is used as a computed column

That's not what you posted. You posted a function that returns the current year, which is obviously not deterministic.

You can write a function that returns the year of a given date and use that in an indexed computed column:

CREATE FUNCTION dbo.FN_GET_YEAR(@d datetime) RETURNS int WITH SCHEMABINDING AS
BEGIN
    RETURN YEAR(@d);
END

GO

CREATE TABLE test_table (
    id int,
    dateCol datetime,
    test AS dbo.FN_GET_YEAR(dateCol)
);

SELECT COLUMNPROPERTY(OBJECT_ID('test_table'), 'test', 'IsIndexable') AS IsIndexableColumn;

create index ix_test_table_test on test_table(test)

Upvotes: 2

Related Questions