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 a language_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.
SELECT resource_id, language_id, name
FROM dbo.resourceTVF(@resource_id, @language_id) r
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 to def
(default
is a SQL Server keyword).
-- Create the Table-Valued Function
CREATE FUNCTION dbo.resourceTVF (@resource_id INT, @user_language_id INT)
RETURNS TABLE
AS
RETURN
SELECT @resource_id AS resource_id,
@user_language_id AS language_id,
ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource res
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id
AND exact.language_id = @user_language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
AND def.language_id = res.default_language_id
WHERE res.resource_id = @resource_id
GO
View
You could rename the resource
table (e.g., to resource_base
) and then create a resource
view in order to provide the following API:
SELECT resource_id, language_id, name
FROM dbo.resource
WHERE resource_id = @resource_id
AND language_id = @language_id
The primary downside is that the view definition needs to CROSS JOIN
all resources and languages before using applying the LEFT 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.
CREATE VIEW dbo.resource WITH SCHEMABINDING AS
SELECT res.resource_id,
lang.language_id,
ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource_base res
CROSS JOIN dbo.languages lang
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id
AND exact.language_id = lang.language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
AND def.language_id = res.default_language_id
GO
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.
It depends on how you define the computed column. A PERSISTED
computed column will be calculated and then stored as data inside the table. If you do not define the column as PERSISTED
, it will be calculated when your query is run.
Please see Aaron's answer for a great explanation and proof.
Pinal Dave also describes this in detail and shows proof of storage in his series:
SQL SERVER – Computed Column – PERSISTED and Storage
Best Answer
Just concat your columns together rather than trying to convert them into xml and then a hash.
Note: The coalesce and the pipe characters were just for examples. The Upper is optional if you either want hash to be case sensitive or not.