Unable to make computed column indexable

Question

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.

`GETDATE()` is nondeterministic. Refer to https://learn.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql?view=sql-server-2017 . So `YEAR(GETDATE())` is also nondeterministic — Squirrel, Oct 04 '18 at 11:49
You can something be deterministic when it could change every second? — Salman A, Oct 04 '18 at 12:44
What would you expect to happen to the data in the index when the table `dbo.tableName` is updated? — David Browne - Microsoft, Oct 04 '18 at 16:17
@DavidBrowne-Microsoft I need to restrict the foreign key and year combo to once a year. So it will be a UNIQUE index. In addition, the year column will be used for some other queries — Shahid Thaika, Oct 04 '18 at 16:22

score 2 · Answer 1 · answered Oct 04 '18 at 12:37

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)

score 0 · Answer 2 · answered Oct 04 '18 at 17:08

"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

Unable to make computed column indexable

2 Answers2