Function on Column used in Index - need optimization advice

Question

First, is this the proper forum for MS SQL tsql questions? Why am I seeing only 114 questions?

OK, so we have a table ods_DDAHIS which has a TransDate column that is numeric in format YYYYMMDD. There is an index on ods_DDAHIS.Transdate. Then view v_ods_DDAHIS does a dbo.ConvertDate(TransDate) as TransDate. We now have a client that has 400mil rows and when they query v_ods_DDAHIS WHERE TransDate = anything, it runs forever.

What are some options to speed up the querying of v_ods_DDAHIS by TransDate?

1. run a onetime conversion on ods_ddahis to format all TransDate to type Date, remove the dbo.ConvertDAte function from v_ods_DDAIHS, add the dbo.ConvertDate function to the ETL for all new rows inserted into ods_ddahis.
2. Would adding an index to the v_ods_ddahis on TRansaction_Date help?
3. Any other ideas

The ConvertDate function is very clunky. We do have to support from SQL 2012 to 2019

CREATE FUNCTION [ConvertDate]
      (@Date decimal(12,0))
     RETURNS date
AS
BEGIN
     IF (ISDATE(@date) = 1)
        BEGIN
	     RETURN CAST(CAST(@Date AS varchar(8)) AS date)	
		END
     ELSE
        RETURN NULL
END

Answer 1

First, is this the proper forum for MS SQL tsql questions? Why am I seeing only 114 questions?

There is also a Transact-SQL tag. But it seems that many that have T-SQL questions post them to the regular SQL Server tag.

Of the alternatives you list, I would definitely recommend the first. Having dates in the wrong data type (numeric, strings), only leads to grief.

If that for some reason is not feasible, for instance, because some smartie are storing values 20250200 to cover the entire month, you could add a computed column to the table:

ConvertedDate AS try_cast(cast(TransDate AS char(8)) AS date)

and add an index on that column. Change the view to use the new column.

try_cast was introduced in SQL 2012, so that should not be an issue for you.