SQLTeam.com | Weblogs | Forums

Using VIEW instead of TABLE


#1

My tables often have some JOINed "code lookup" tables, something like this

CREATE TABLE MyDetailTable
(
	D_ID int IDENTITY(1,1) NOT NULL,
	D_GroupID int NOT NULL,
	D_MemberID int NOT NULL,
	D_Col1 int,
	D_Col2 varchar(10),
	...
	PRIMARY KEY ( D_ID )
)

CREATE TABLE MyGroup
(
	G_ID int IDENTITY(1,1) NOT NULL,
	G_Name varchar(30),
	...
	PRIMARY KEY ( G_ID )
)

CREATE TABLE MyMember
(
	M_ID int IDENTITY(1,1) NOT NULL,
	M_Name varchar(30),
	M_CountryID int,
	...
	PRIMARY KEY ( M_ID )
)

CREATE TABLE MyCountry
(
	C_ID int IDENTITY(1,1) NOT NULL,
	C_Name varchar(30),
	...
	PRIMARY KEY ( C_ID )
)

So then I can JOIN with

SELECT ...
FROM MyDetailTable AS D
	JOIN MyGroup AS G
		 ON G.G_ID = D.D_GroupID
	JOIN MyMember AS M
		 ON M.M_ID = D.D_MemberID
	JOIN MyCountry AS C
		 ON C.C_ID = M.M_CountryID

Until recently I have had a view for these:

CREATE VIEW MyDetailView
AS
SELECT	D_ID AS V_D_ID,
	G_Name AS V_D_G_Name,
	M_Name AS V_D_M_Name,
	C_Name AS V_D_C_Name
FROM MyDetailTable AS D
	JOIN MyGroup AS G
		 ON G.G_ID = D.D_GroupID
	JOIN MyMember AS M
		 ON M.M_ID = D.D_MemberID
	JOIN MyCountry AS C
		 ON C.C_ID = M.M_CountryID

so that whenever I wanted any of the lookup columns I could do

SELECT ...
FROM MyDetailTable AS D
	JOIN MyDetailView AS DV
		 ON DV.V_D_ID = D.D_ID
WHERE	D.D_ID = 1234

but it seemed to me that this was daft as it references MyDetailTable twice.

So I have changed the view to include all the columns from the parent table too:

CREATE VIEW MyDetailView
AS
SELECT	D_ID,
	D_GroupID int NOT NULL,
	D_MemberID int NOT NULL,
	D_Col1 int,
	D_Col2 varchar(10),
	...
	--
	G_Name AS V_D_G_Name,
	M_Name AS V_D_M_Name,
	C_Name AS V_D_C_Name
FROM MyDetailTable AS D
	JOIN MyGroup AS G
		 ON G.G_ID = D.D_GroupID
	JOIN MyMember AS M
		 ON M.M_ID = D.D_MemberID
	JOIN MyCountry AS C
		 ON C.C_ID = M.M_CountryID

so now I can just say

SELECT ...
FROM MyDetailView AS DV
WHERE	DV.D_ID = 1234

I still use the table name if I don't need any lookup columns, but I used the View name if I want a combination of table columns and view lookup-columns.

Does this seem like a good idea? or are there downsides?

As I see it I avoid having JOINs to all the lookup columns peppered through my code. If something changes (rare, admitedly) I can just fix it in the VIEW. However, there is less chance of my making a mistake in a JOIN as all the logic is encapsulated in the JOIN and in effect re-uses that code each time.

Another useful side effect is that if a column becomes obsolete I can either comment it out in the VIEW (which means that any code referencing it will break), or I can allow backward compatbility by making the VIEW select that column in a forward-compatible fashion.

(Where I comment out a column, to force an error in DEC, I can choose to comment it back in on PRODUCTION "just in case" it is still referenced somewhere, perhaps only in a Client's own query)


#2

I like to use this approach. I've often created systems where each table has a matching view with all the "lookup" joins already done.


#3

Thanks Graz,

Do you include all the underlying table's column in the view, and also the Lookup Columns?

Or just the Lookup Columns?

We're changing from "Just lookup columns" to "Both table's columns and lookup columns" and I am just wondering if there is any side effect that is hiding just around the corner!!


#4

We did all the columns from the base table.


#5

There's one other aspect I've included in my VIEW ... not sure if it is a good idea?

We have a generic table which stores Record Validation Errors - basically a TableName, ID (matching the PKey of a row in another table) and then an Error Number and a Message.

This table is populated by a variety of things, most notably any Data Import from alien systems (we build a lot of CRM which pull data from multiple sources, accounts, products, phone system ...) and of course there isn't an interactive way of display the errors to a user during the overnight import :slight_smile: . We provide a report / screens where users can review the errors but in practice they never bother. Beats me ... all the errors are things that they told us their other systems don't permit! such as missing State / Postcode (but only in countries where such things are "required") and most of our clients have tens-of-thousands of such errors - management tell me that they are all, indeed, critical and will be cleaned up in the source systems ... it never happens, and yet they continue to make money

So on our Enquiry Record Cards (e.g. a Customer Record Card) we display any Record Validation Errors that exist for that Record/Customer to try to encourage the CLient to sort them out but, failing that, at least the person looking at the record knows that the data quality is "lacking"!

So ... in my view I have

CREATE VIEW MyView
AS
SELECT ... all normal columns ...
       ... all lookup columns ...
	[V_xxx_rve_ErrorCount] = rve_ErrorCount,
	[V_xxx_rve_HasErrorsMessage] = CASE WHEN rve_ErrorCount = 0
				THEN NULL
				ELSE
					CONVERT(varchar(20), rve_ErrorCount)
					+ ' Error'
					+ CASE WHEN rve_ErrorCount = 1 THEN '' ELSE 's' END
				END
FROM MyTable AS T
	OUTER APPLY (
		SELECT	COUNT(*) AS [rve_ErrorCount]
		FROM	dbo.RecordValidationError
		WHERE	    rve_ID = T.xxx_ID)
			AND rve_TableName = 'MyTable' -- Actually we use a GUID for this
			AND rve_IsActve = 1
	) AS RVE

This is only used on record card screens (user can click on "This record has 999 errors" to see the details), and on some reports (we include a column for "Number of errors")

I wonder if it is "expensive" to have this sort of stuff in the view?

My understanding is that if I don't reference columns in the VIEW then SQL should optimise the query plan to exclude them, and their associated tables, but I wonder how effective it actually is. A JOIN to a table on the PKey to get a Lookup Name seems pretty "cheap", but something that is asked to perform a COUNT on a sub-table might well be a much more expensive query?


#6

I guess that really depends on how often you do SELECT * :smile:


#7

Only in EXISTS :sunglasses:


#8

Its well overdue ... I'll check some Query Plans and see if the RecordValidationErrors table is getting any hits, or not, if I don't reference the column ... Pretty sure I have a covering-index anyway, but I'll double check that too.


#9

Bad news ... all the JOINed tables appear to be referenced in the query plan, whether the columns that use them are referenced or not.

I didn't think it was supposed to be like that?

However, the Outer Join is only references if columns using it are included in the query.

(I can post sample data etc. based on the above example if anyone wants to have a fiddle)


#10

That makes sense. A join can be used to eliminate rows so it has to reference the tables.


#11

Perhaps I should change all my JOINs to APPLYs ?

"feels wrong" though ...

(As it happens my JOINS are all LEFT JOINS ... so not going to exclude any rows by them being absent ... that obviously isn't making any difference!)

I suppose the JOIN could find multiple rows, which would change the number of rows in my resultset - although I'm JOINing on a UNIQUE characteristic - but that might not be relevant to SQL when deciding whether to physically query that table, or not...