I have a scenario in a very old and very large application, where I have a table representing a type of resource:
CREATE TABLE resource (resource_id INT, name NVARCHAR(4000))
This table is selected from in hundreds of different places, including stored procedures and dynamic SQL in the application code.
A team recently updated this resource's name to be localized, and their approach is pretty straight forward. There is a new table containing the localized names, and a 'default' language ID on the resource
table, for when the name isn't localized for the requested language:
-- Foreign keys omitted
ALTER TABLE resource ADD default_language_id INT
CREATE TABLE resource_local (resource_id INT, language_id INT, name NVARCHAR(4000))
Most procs have an @user_language_id
parameter, so the logic for choosing the name to return is simple: take resource_local.name
matching language_id = @user_language_id
if it exists, otherwise resource_local.name
matching language_id = resource.default_language_id
if it exists, otherwise take resource.name
.
Unfortunately, this turns the logic to select the correct name into something like this:
SELECT ISNULL(ISNULL(exact.name, default.name), res.name)
FROM resource res
LEFT JOIN resource_local exact ON exact.resource_id = res.resource_id
AND exact.language_id = @user_language_id
LEFT JOIN resource_local default ON default.resource_id = res.resource_id
AND default.language_id = res.default_language_id
WHERE res.resource_id = @resource_id
All of the hundreds of places that try to select resource.name
are having to be updated with this logic, which has turned this project into a massive effort across the entire organization, as each team needs to update their SQL to use this logic. This also causes maintainability issues, as any new developers dealing with this table need to know that they can't just use the name
column.
It's too late now, but I'm curious: is there any better way to approach this, so that selecting the name
column from resource
will just 'do the right thing' based on the @user_language_id
variable (if it exists)?
Best Answer
I'm not sure if it's possible to do this so that none of the references to the
resource
table need to change. It seems like the fact that alanguage_id
is needed is a fundamental change that all calling code will need to be aware of.However, it is possible to design this in a way that the resource can be queried in either of the following simple ways. One of these options might have been an easier change to make and maintain across so many different places.
Table-valued function
Using an Inline Table-Valued Function, we can provide the following syntax.
Here is an example of how to create the function. It's essentially the same query from your question, but with the alias
default
changed todef
(default
is a SQL Server keyword).View
You could rename the
resource
table (e.g., toresource_base
) and then create aresource
view in order to provide the following API:The primary downside is that the view definition needs to
CROSS JOIN
all resources and languages before using applying theLEFT JOIN
to the local and default resources. Even so, this is going to be a fairly efficient plan with 4 singleton seeks assuming that you have the proper indexes.Full script
Here is a full script where I implemented both of these proposals, loaded a small amount of fake data, and ran a few test cases. At least for these test cases, both approaches yield the desired results and use a loop-seek based plan.
I think that the inline table-valued function is probably the approach the I'd try first. Note that you can use
CROSS APPLY
to "join" to the table-valued function if you need more than one resource at a time.