Best syntax for Fixed Row in a VIEW

Transact-SQL
1

I have VIEWs which selects a number of columns from a table, and JOIN to some lookup tables (e.g. to get a Description of a Code Column).

I want to also include a fixed value - e.g. a Configuration Table value for, for example, the "Default value" for an optional column.

Let's say I have a column for TaxCode and I allow this to be NULL to indicate "Use the configured default"

CREATE VIEW dbo.MyView
AS
SELECT T.Col1, T.Col2, T.Col3
	, T.TaxCode
	, T.Col4, T.Col5
	, [V_TaxCode_Name] = TAX.Name
	, [V_TaxCodeDefault] = CFG.Data
FROM	dbo.MyTable
	LEFT OUTER JOIN dbo.TaxLookup AS TAX
		ON TAX.TaxCode = T.TaxCode
	LEFT OUTER JOIN dbo.ConfigData AS CFG
		ON CFG.Key = 'TAX_CODE_DEFAULT'

In this example I've used LEFT OUTER JOIN but are there better choices? There will only be one row (for a given query that may match multiple rows from the primary table).

I could use OUTER/CROSS APPLY for example

Would an INNER JOIN be more efficient (assuming I could make sure that the Client has created the CONFIG Data value)

The VIEW is basically used to prevent the same JOINs being duplicated in multiple SProcs, in order to keep the code consistent.

Any other suggestions?

Thanks :slight_smile:

2

Possibilities:
Do the default lookup only if the original lookup is not found.
Have only one output column.

CREATE VIEW dbo.MyView
AS
SELECT T.Col1, T.Col2, T.Col3
	, T.TaxCode
	, T.Col4, T.Col5
	, ISNULL( [V_TaxCode_Name], [V_TaxCodeDefault]) AS TAX.Name
FROM	dbo.MyTable
	LEFT OUTER JOIN dbo.TaxLookup AS TAX
		ON TAX.TaxCode = T.TaxCode
	LEFT OUTER JOIN dbo.ConfigData AS CFG
		ON TAX.TaxCode IS NULL AND CFG.Key = 'TAX_CODE_DEFAULT'
3

I like that idea, thanks. Does this work?

CREATE VIEW dbo.MyView
AS
SELECT T.Col1, T.Col2, T.Col3
	, T.TaxCode
	, T.Col4, T.Col5
	, ISNULL( [V_TaxCode_Name]
			, (
					SELECT Data -- i.e. TaxCodeDefault
					FROM dbo.ConfigData AS CFG
					WHERE CFG.Key = 'TAX_CODE_DEFAULT'
				)
			) AS TAX.Name
FROM	dbo.MyTable
	LEFT OUTER JOIN dbo.TaxLookup AS TAX
		ON TAX.TaxCode = T.TaxCode

or is it horrid?

4

That will too. It's not horrid, but I don't think the plan will be any different than the more natural style join I showed with the added "IS NULL" condition.

5

I'll check the plans; I definitely prefer the JOIN syntax :slight_smile:

6

Here are the tests I ran. I recreated the VIEW between each test (even if not changed) which i hope will have cleared the caches etc.

TEST #1 has LookupValue = NULL (so will need the DEFAULT lookup)
TEST #2 has LookupValue = NOT NULL (depending on the test either Includes/Excldues the lookup)

There are very few rows in my CONFIG table (and will be in PRODUCTION too), so the READS is tiny, and as such I have no real feel for which of these plans is better, any advice / comment / suggestions would be appreciated (even if "For so few rows who cares" :slight_smile: )

ALTER VIEW dbo.MyView
AS
SELECT
	[V_ClustedIndexKey] = T.ClustedIndexKey
	, [V_LookupValue] = T.CodeValue
	, [V_LookupValue_DEFAULT] = CONVERT(int, CFG.cfg_Data)
FROM	dbo.MyTable  AS T
	LEFT OUTER JOIN dbo.CONFIG AS CodeValue_CFG
		 ON CFG.cfg_Kind = 'INIT'
		AND CFG.cfg_Key = 'CodeValue'
		AND CFG.cfg_Brand = 'MyBrand'
		AND CFG.cfg_ItemNo = 1

TEST #1:

V_ClustedIndexKey V_LookupValue V_LookupValue_DEFAULT 
----------------- ------------- --------------------- 
1447              NULL          9

Table 'CONFIG'. Scan count 0, logical reads 2
Table 'MyTable'. Scan count 0, logical reads 2
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT(int,[MyDB].[dbo].[CONFIG].[cfg_Data] as [CodeValue_CFG].[cfg_Data],0)))
|--Nested Loops(Left Outer Join)
    |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[MyTable].[IX_ClustedIndexKey] AS [T])
	, SEEK:([T].[ClustedIndexKey]=(1447)) ORDERED FORWARD)
    |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[CONFIG].[PK_CFG] AS [CodeValue_CFG])
	, SEEK:([CodeValue_CFG].[cfg_Kind]='INIT' 
		AND [CodeValue_CFG].[cfg_Key]='CodeValue' 
		AND [CodeValue_CFG].[cfg_Brand]='MyBrand' 
		AND [CodeValue_CFG].[cfg_ItemNo]=(1)
	) ORDERED FORWARD)

TEST #2:
Results depend on whether DeFAULT was only included if LookupValue=NULL

V_ClustedIndexKey V_LookupValue V_LookupValue_DEFAULT 
----------------- ------------- --------------------- 
881               5             NULL
or
881               5             9

(Same)

Add:
AND T.CodeValue IS NULL

TEST #1:

Table 'CONFIG'. Scan count 0, logical reads 2
Table 'MyTable'. Scan count 0, logical reads 2
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT(int,[MyDB].[dbo].[CONFIG].[cfg_Data] as [CodeValue_CFG].[cfg_Data],0)))
|--Nested Loops(Left Outer Join, WHERE:([MyDB].[dbo].[MyTable].[CodeValue] as [T].[CodeValue] IS NULL)) <<<
    |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[MyTable].[IX_ClustedIndexKey] AS [T])
	, SEEK:([T].[ClustedIndexKey]=(1447)) ORDERED FORWARD)
    |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[CONFIG].[PK_CFG] AS [CodeValue_CFG])
	, SEEK:([CodeValue_CFG].[cfg_Kind]='INIT' 
		AND [CodeValue_CFG].[cfg_Key]='CodeValue' 
		AND [CodeValue_CFG].[cfg_Brand]='MyBrand' 
		AND [CodeValue_CFG].[cfg_ItemNo]=(1)
	) ORDERED FORWARD)

TEST #2:
(same)

INNER JOIN
FROM	dbo.MyTable  AS T
	JOIN dbo.CONFIG AS CodeValue_CFG
		 ON CFG.cfg_Kind = 'INIT'
		AND CFG.cfg_Key = 'CodeValue'
		AND CFG.cfg_Brand = 'MyBrand'
		AND CFG.cfg_ItemNo = 1

TEST #1:

Table 'MyTable'. Scan count 0, logical reads 2
Table 'CONFIG'. Scan count 0, logical reads 2
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT(int,[MyDB].[dbo].[CONFIG].[cfg_Data] as [CodeValue_CFG].[cfg_Data],0)))
|    |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[CONFIG].[PK_CFG] AS [CodeValue_CFG])
	, SEEK:([CodeValue_CFG].[cfg_Kind]='INIT' 
		AND [CodeValue_CFG].[cfg_Key]='CodeValue' 
		AND [CodeValue_CFG].[cfg_Brand]='MyBrand' 
		AND [CodeValue_CFG].[cfg_ItemNo]=(1)
	) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[MyTable].[IX_ClustedIndexKey] AS [T])
	, SEEK:([T].[ClustedIndexKey]=(1447)) ORDERED FORWARD)

TEST #2:

Table 'MyTable'. Scan count 0, logical reads 2
Table 'CONFIG'. Scan count 0, logical reads 2

(same)

Add:

		AND T.CodeValue IS NULL

TEST #1:

Table 'MyTable'. Scan count 0, logical reads 2
Table 'CONFIG'. Scan count 0, logical reads 2
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT(int,[MyDB].[dbo].[CONFIG].[cfg_Data] as [CodeValue_CFG].[cfg_Data],0)))
|    |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[CONFIG].[PK_CFG] AS [CodeValue_CFG])
	, SEEK:([CodeValue_CFG].[cfg_Kind]='INIT' 
		AND [CodeValue_CFG].[cfg_Key]='CodeValue' 
		AND [CodeValue_CFG].[cfg_Brand]='MyBrand' 
		AND [CodeValue_CFG].[cfg_ItemNo]=(1)
	) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[MyTable].[IX_ClustedIndexKey] AS [T])
	, SEEK:([T].[ClustedIndexKey]=(1447))
		,  WHERE:([MyDB].[dbo].[MyTable].[CodeValue] as [T].[CodeValue] IS NULL) <<<
		ORDERED FORWARD)

TEST #2:

Table 'MyTable'. Scan count 0, logical reads 2
Table 'CONFIG'. Scan count 0, logical reads 2

(same)

Remove JOIN and change SELECT:

	, [V_LookupValue_DEFAULT] = CASE WHEN T.CodeValue IS NOT NULL THEN NULL
					ELSE
					(
						SELECT	[V_LookupValue_DEFAULT] = CONVERT(int, CFG.cfg_Data)
						FROM	dbo.CONFIG AS CodeValue_CFG
						WHERE	CFG.cfg_Kind = 'INIT'
							AND CFG.cfg_Key = 'CodeValue'
							AND CFG.cfg_Brand = 'MyBrand'
							AND CFG.cfg_ItemNo = 1
							AND T.CodeValue IS NULL
					)
					END

TEST #1:

Table 'CONFIG'. Scan count 0, logical reads 2
Table 'MyTable'. Scan count 0, logical reads 2
|--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [MyDB].[dbo].[MyTable].[CodeValue] as [T].[CodeValue] IS NOT NULL THEN NULL ELSE CONVERT(int,[MyDB].[dbo].[CONFIG].[cfg_Data] as [CodeValue_CFG].[cfg_Data],0) END))
|--Nested Loops(Left Outer Join, PASSTHRU:([MyDB].[dbo].[MyTable].[CodeValue] as [T].[CodeValue] IS NOT NULL), OUTER REFERENCES:([T].[CodeValue]))
    |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[MyTable].[IX_ClustedIndexKey] AS [T])
	, SEEK:([T].[ClustedIndexKey]=(1447)) ORDERED FORWARD)
    |--Filter(WHERE:(STARTUP EXPR([MyDB].[dbo].[MyTable].[CodeValue] as [T].[CodeValue] IS NULL)))
         |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[CONFIG].[PK_CFG] AS [CodeValue_CFG])
	, SEEK:([CodeValue_CFG].[cfg_Kind]='INIT' 
		AND [CodeValue_CFG].[cfg_Key]='CodeValue' 
		AND [CodeValue_CFG].[cfg_Brand]='MyBrand' 
		AND [CodeValue_CFG].[cfg_ItemNo]=(1)
	) ORDERED FORWARD)

TEST #2:

Table 'Worktable'. Scan count 0, logical reads 0
Table 'MyTable'. Scan count 0, logical reads 2

(same)

7

If you always need to list the default, then the first query you had is the only way.

If you only need to list/use the default if you don't find a match, then use the JOIN that specified "IS NULL".

1 Like