I have a table with a persisted column that is build using a function call:
This is the function:
CREATE FUNCTION os.RemoveNonAlphaNumericCharacters(
DECLARE @KeepValues AS NVARCHAR(100)
SET @KeepValues = '%[^a-z^0-9]%'
WHILE PatIndex(@KeepValues, @Temp) > 0
SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
and this is the column:
IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = N'NormalizedName' AND
OBJECT_ID = OBJECT_ID(N'dbo.Product'))
ALTER TABLE dbo.Product
-- Maximum Index is 900
ADD NormalizedName AS LEFT(os.RemoveNonAlphaNumericCharacters(Name), 500
Then I have an index on that column:
CREATE NONCLUSTERED INDEX IX_Product_AccountId_NormalizedName ON
WITH (ONLINE = ON)
All quite simple really.
When I try to execute a simple search using that column the search is very
very slow and I can see in the list of executing session a call to the os.
SELECT TOP 1 * FROM dbo.Product P WITH(NOLOCK)
WHERE P.BusinessFileId = 51678
AND P.NormalizedName = 'test' -- should use the index
This is what I see is running:
I'm stuck and I don't know how to fix this atm.
I really expected the function to be called only during the insert (or
maybe updates) and not during the search.
I could remove the calculated column, clean the value in C# before I save
it and remove the function all together but that requires a fair bit of
PS> This is running on an Azure SQL P4 DB.