I have several SQL Server tables which are dropped and reloaded through text files on a regular basis. The result is dozens of tables which all have the data type NVARCHAR(255). In the past this has worked fine since I can cast the data type to whatever I want later without having to worry about type conversion issue on the daily job. This practice has now created an issue since I would like to create an index on a datetime derived field that is stored as a multiple text fields. Is it possible to create an index on a value that is derived from the table? In my case I would like something similar to:
CREATE NONCLUSTERED INDEX NDX_prks_Position_Dta_01
([Position Number - Position Dta],
DATEADD(Minute, cast([Effective Date Sequence # - Position Dta] as int), [Effective Date - Position Dta]),
Date - Position Dta] as date))
Obviously the above syntax does not work but hopefully that helps explain what I am looking for. Essentially the query I have in the example is one of dozens of subqueries with the same issue and the result is queries that take 4 hours to run. I suppose I would create a temp table with the correct data types and index the temp table but I was really hoping for a solution that would not cause me to have to update a lot of functioning code (even though it is super slow it works). Additionally I am not the only person who accesses these tables so I am really trying to avoid modifying the source tables.
Attached is a copy of a typical example where I am trying to create a more effective index. I am open to any ideas that will increase performance and the less modification to the existing code the better! I appreciate the help experts!