Sql-server – SQL Server – Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A simple SELECT from the view yields the following execution plan:

That seems like a complex plan for a view that has less than a thousand rows that may see a row or two change every few months. But it gets worse with the following other observances:

1. Nested views are non-deterministic, so we cannot index them
2. Each view references multiple UDFs to build the strings
3. Each UDF contains nested UDFs to get the ISO codes for localized languages
4. Views in the stack are using additional string builders returned from UDFs as JOIN predicates
5. Each view stack is treated as a table, meaning that there are INSERT / UPDATE / DELETE triggers on each to write to the underlying tables
6. These triggers on the views use CURSORS that EXEC stored procedures which reference more of these string building UDFs.

This seems pretty rotten to me, but I only have a few years experience with TSQL. It gets better, too!

It appears the developer who decided that this was a great idea, did all this so that the few hundred strings that are stored can have a translation based on a string returned from a UDF that is schema-specific.

Here's one of the views in the stack, but they are all equally bad:

CREATE VIEW [UserWKStringI18N]
AS
SELECT b.WKType, b.WKIndex
    , CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.I18NString
       ELSE il.I18nString
       END AS WKString
    ,CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.IETFLangCode
       ELSE il.IETFLangCode
       END AS IETFLangCode
    ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
    ,dbo.UserI18N_Session_Locale_Key()  AS IETFSessionLangCode
    ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
FROM   UserWKStringBASE b
LEFT OUTER JOIN User3StringI18N il
ON    (
il.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')
AND il.IETFLangCode = dbo.UserI18N_Session_Locale_Key()
)
LEFT OUTER JOIN User3StringI18N id
ON    (
id.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex,N'WKS')
AND id.IETFLangCode = dbo.UserI18N_Database_Locale_Key()
)
GO

Here is why UDFs are being used as JOIN predicates. The I18NID column is formed by concatenating: STRING + [ + ID + | + ID + ]

During testing of these, a simple SELECT from the view returns ~309 rows, and takes 900-1400ms to execute. If I dump the strings into another table and slap an index on it, the same select returns in 20-75ms.

So, long story short (and I hope you appreciated some of this sillyness) I want to be a good Samaritan and re-design and re-write this for the 99% of clients running this product who do not use any localization at all–end users are expected to use the [en-US] locale even when English is a 2nd/3rd language.

Since this is an unofficial hack, I am thinking of the following:

1. Create a new String table populated with a cleanly joined set of data from the original base tables
2. Index the table.
3. Create a replacement set of top-level views in the stack that include NVARCHAR and INT columns for the WKType and WKIndex columns.
4. Modify a handful of UDFs that reference these views to avoid type conversions in some join predicates (our largest audit table is 500-2,000M rows and stores an INT in a NVARCHAR(4000) column which is used to join against the WKIndex column (INT).)
5. Schemabind the views
6. Add a few indexes to the views
7. Rebuild the triggers on the views using set logic instead of cursors

Now, my actual questions:

1. Is there a best practice method to handle localized strings via a view?
2. Which alternatives exist for using a UDF as a stub? (I can write a specific VIEW for each schema owner and hard-code the language instead of relying on a variety of UDF stubs.)
3. Can these views be simply made deterministic by fully qualifying the nested UDFs and then schemabinding the view stacks?