1

I have a query with a hard coded value which is also hard coded into my application (which has a comment saying that the value should be the same as the value hard coded into the query).

What would be considered the best way to make that value discoverable outside of the single query it is being used in?

A UDF immediately jumps to mind, but prior to 2019 it kills performance (don’t have 2019 so I can’t verify it, but it seems inlined UDFs should fix the performance problem).

A view or TFV might be a good alternative, but both seem overly complex and a misleading.

I don’t have a table/view of constants/lookup values.

Is there a good solution for this situation or just stick with comments in the relevant code?

marked as duplicate by Paul White sql-server Apr 20 at 16:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

Scalar UDFs don't have to kill performance when they return a constant value. You can save off the function value to a local variable or parameter and use that variable in your query

CREATE FUNCTION dbo.RETURN_SPECIAL_VALUE() RETURNS INT
AS
BEGIN
    RETURN 1;
END;

GO

DECLARE @special_value INT = dbo.RETURN_SPECIAL_VALUE();

-- example query that uses special value
SELECT TOP (5) @special_value, number
FROM master..spt_values;

On the other hand, if the constant is used in the WHERE clause the estimates may be better using the literal. Unless an OPTION (RECOMPILE) is used, which may be excessive.

Not the answer you're looking for? Browse other questions tagged or ask your own question.