CODEWITHSUNDEEP
sqlsql-server-2008
joelmdev
joelmdev

Reputation: 11773

SQL Server view design

I have a database that I'm running large queries against that I want to simplify with a view. Though there's more of them, the tables basically look like this (pseudo code):

TABLE document (
Id int PRIMARY KEY,
    /*more metadata columns*/
)

TABLE name (
    Id int PRIMARY KEY,
    documentId int FOREIGN KEY REFERENCES document.Id,
    date DATETIME,
    text varchar(MAX)
)

TABLE description (
    Id int PRIMARY KEY,
    documentId int FOREIGN KEY REFERENCES document.Id,
    date DATETIME,
    text varchar(MAX)
)

So the idea is that the 'document' table contains the basic information about a document and the Id that ties the rest of the tables to the document. All other tables are for individual attributes of the document that are updateable. Each update gets its own row with a timestamp. What I want the view to pull is one row per document with the most up to date versions of each attribute contained in the other tables (if this needs further elaboration or an example, please let me know and I will provide). What is the least convoluted way I can pull this off? Using SQL Server 2008.

Upvotes: 1

Views: 947

Answers (3)

gbn
gbn

Reputation: 432200

A view won't increase efficiency. A view is just a macro that expands.

There is no magic in a view: but can suffer if you join onto this view because the expanded queries can get massive.

You can index a view, but these work best with Enterprise Edition unless you want to use the NOEXPAND hint all over.

That said, the query is quite easy: unless you want to index the view when you have limitations.

One approach is the CTE as per Stuart Ainsworth's approach. Another is the "Max one per group" approach I described here on dba.se. Neither of these are safe for indexed views.

Upvotes: 4

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

You could use a CTE for each attribute inside the view to return the latest attribute values for the documentid, like so:

; WITH cName AS
(SELECT *
 FROM (SELECT ID, documentID, 
       date, text, 
       ranking = ROW_NUMBER () OVER (PARTITION BY documentID ORDER BY date DESC)
      FROM name) x
WHERE ranking = 1),
.... [more CTE's here]
SELECT columnlist
FROM document d JOIN cName cn ON d.id=cn.documentid

Upvotes: 2

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

Sql server 2008 supports computed column in the index. So you could set a column - "is_latest" as 1 for the row with latest time for that document_id. Now while querying you could use the is_latest column and it would be much faster. Refer - http://msdn.microsoft.com/en-us/library/ms189292.aspx

Upvotes: 0

Related Questions