Today I wanted to touch on massive and wasteful views that are re-used a little too much. The one-size-fits-all view: you've seen them, you've had to use them, you may have even created them.  Why do we use them?  Convenience, laziness, you name it.

Picture a forums system where you have users.  On the user page you will see a bunch of data: demographic information, a picture, reputation score, badges, activity, last login time, questions asked, questions answered, etc.  This system probably has tables like this (please allow me to grossly simplify things here):

CREATE TABLE dbo.Users
(
  UserID            INT PRIMARY KEY,
  Username          NVARCHAR(255) NOT NULL UNIQUE,
  FirstName         NVARCHAR(32) NOT NULL,
  LastName          NVARCHAR(32) NOT NULL,
  ProfilePictureURL VARCHAR(1024),
  CreatedDate       SMALLDATETIME NOT NULL 
                    DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE dbo.Questions
(
  QuestionID INT PRIMARY KEY,
  UserID     INT NOT NULL 
             REFERENCES dbo.Users(UserID),
  Question   NVARCHAR(MAX),
  EventDate  SMALLDATETIME NOT NULL 
             DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE dbo.Answers
(
  AnswerID   INT PRIMARY KEY, 
  QuestionID INT NOT NULL 
             REFERENCES dbo.Questions(QuestionID),
  UserID     INT NOT NULL 
             REFERENCES dbo.Users(UserID),
  Answer     NVARCHAR(MAX) NOT NULL,
  EventDate  SMALLDATETIME NOT NULL 
             DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE dbo.ReputationPoints
(
  UserID     INT NOT NULL 
             REFERENCES dbo.Users(UserID),
  QuestionID INT 
             REFERENCES dbo.Questions(QuestionID),
  AnswerID   INT 
             REFERENCES dbo.Answers(AnswerID),
  PointValue INT NOT NULL 
             DEFAULT (10),
  EventDate  SMALLDATETIME NOT NULL
             DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE dbo.BadgesEarned
(
  BadgeID   INT NOT NULL 
            REFERENCES dbo.Badges(BadgeID),
  UserID    INT NOT NULL 
            REFERENCES dbo.Users(UserID),
  EventDate SMALLDATETIME NOT NULL 
            DEFAULT CURRENT_TIMESTAMP
);

There are likely a bunch of other columns and indexes geared for the different ways the application will use the data, but this core schema should be a good enough start to get my point across.  Let's start from the user page I was talking about, where you see something like this:

Now, if you were tasked to write a query that retrieved all of the data necessary to display this page, it would probably be something like this:

DECLARE @UserID INT;
 
SELECT @UserID = 1;
 
SELECT
  u.UserID,
  u.UserName,
  u.ProfilePictureURL,
  u.FirstName,
  u.LastName,
  Reputation        = COALESCE(r.Reputation, 0),
  MemberSince       = u.CreatedDate,
  LastActivity      = q.EventDate,
  QuestionsAsked    = q.QuestionCount,
  QuestionsAnswered = q.AnswerCount,
  BadgesEarned      = COUNT(b.BadgeID)
FROM
  dbo.Users AS u 
LEFT OUTER JOIN
(
  SELECT
    UserID,
    Reputation = SUM(PointValue)
  FROM dbo.ReputationPoints
  WHERE UserID = @UserID
  GROUP BY UserID
) AS r
ON u.UserID = r.UserID
LEFT OUTER JOIN
(
  SELECT
    UserID,
    EventDate     = MAX(EventDate),
    QuestionCount = MAX(QuestionCount),
    AnswerCount   = MAX(AnswerCount),
  FROM
  (
    SELECT
      UserID,
      EventDate     = MAX(EventDate),
      QuestionCount = 0,
      AnswerCount   = COUNT(*)
    FROM
      dbo.Answers
    WHERE UserID = @UserID
    GROUP BY UserID
    UNION ALL
    SELECT
      UserID,
      EventDate     = MAX(EventDate),
      QuestionCount = COUNT(*),
      AnswerCount   = 0
    FROM
      dbo.Questions
    WHERE UserID = @UserID
    GROUP BY UserID
   ) AS s
) AS q
ON u.UserID = q.UserID
LEFT OUTER JOIN dbo.BadgesEarned AS b
ON u.UserID = b.UserID
WHERE u.UserID = @UserID;

A lot of people will turn this into a view, so that when they need to get this information from a different stored procedure, they don't have to repeat a lot of the same code.  Creating the view only requires that you take out the WHERE clauses:

CREATE VIEW dbo.UserData
AS
SELECT
  u.UserID,
  u.UserName,
  u.ProfilePictureURL,
  u.FirstName,
  u.LastName,
  Reputation        = COALESCE(r.Reputation, 0),
  MemberSince       = u.CreatedDate,
  LastActivity      = q.EventDate,
  QuestionsAsked    = q.QuestionCount,
  QuestionsAnswered = q.AnswerCount,
  BadgesEarned      = COUNT(b.BadgeID)
FROM
  dbo.Users AS u 
LEFT OUTER JOIN
(
  SELECT
    UserID,
    Reputation = SUM(PointValue)
  FROM dbo.ReputationPoints
  GROUP BY UserID
) AS r
ON u.UserID = r.UserID
LEFT OUTER JOIN
(
  SELECT
    UserID,
    EventDate     = MAX(EventDate),
    QuestionCount = MAX(QuestionCount),
    AnswerCount   = MAX(AnswerCount),
  FROM
  (
    SELECT
      UserID,
      EventDate     = MAX(EventDate),
      QuestionCount = 0,
      AnswerCount   = COUNT(*)
    FROM
      dbo.Answers
    GROUP BY UserID
    UNION ALL
    SELECT
      UserID,
      EventDate     = MAX(EventDate),
      QuestionCount = COUNT(*),
      AnswerCount   = 0
    FROM
      dbo.Questions
    GROUP BY UserID
   ) AS s
) AS q
ON u.UserID = q.UserID
LEFT OUTER JOIN dbo.BadgesEarned AS b
ON u.UserID = b.UserID;

Turning this into a view on its own is not necessarily a bad thing.  If it is truly for re-use in other queries where most or all of the columns (and hence derived tables) are required, then I see nothing wrong with encapsulating these joins into a single view, instead of repeating those joins and derived tables several times across slightly different stored procedures.  The problem comes in when the view gets used in stored procedures that need a very small subset of the columns.  Another problem arises when some new interface needs a couple of those columns and then some other key piece of information… instead of writing a new query, the view above is modified to add another left join, or derived table, or correlated subquery, or UDF to calculate an additional column value.  Not only is this wasteful for the new application page (since it doesn't need everything above), it is also wasteful for the existing application pages already using the view, since they will simply ignore this extra column.  This is obviously a rabbit-hole you don't really want to find yourself stuck in a year or two down the road.

We have inherited legacy views like this that return all kinds of information about a particular type of entity, and they get called even when all that the application needs are a couple of columns from the core table (e.g. UserID and UserName, for populating a drop-down list).  Even worse is when we go after a small subset of columns in the view and only for a single user (e.g. just get reputation count) – the stored procedure uses the view presumably because the user copied part of the query from another stored procedure that users the view.  Obviously it is very expensive to materialize that view (especially those computations in the derived tables) for the entire set of users in the database, and this is quite wasteful if we're only after data that is found all by itself in the Users table.  In this case, at least in my humble opinion, it would be better to just have a stored procedure called dbo.User_GetList, which just went after the dbo.Users table directly, or to create a second view, called UserList, that looked like this (since this is all that application page requires):

CREATE VIEW dbo.UserList
AS
  SELECT
    UserID,
    UserName
  FROM
    dbo.Users;

Now you have two views to maintain, but one of them is fairly trivial, and will help prevent your server from doing a lot of extra work every time the list of users is presented to the application.  And perhaps at this point you could move the larger view code above back into a stored procedure, where it can remain parameterized so that even when all of the columns are needed, it is a narrow set of rows that are evaluated.  Again, I'm not sure that I can tell you when you will need the view for re-use, but in most cases I've seen, the page that has a smorgasbord of information about an entity only appears once in an application, and it is likely the case that smaller sets of columns are re-used more widely.

We are working on cleaning some of these up, and this work was the motivation behind this post.  And you may laugh, or wonder, "who would do such a thing?", but I've seen it often.

UPDATE

Rob has pointed out several flaws with my example, including my syntax errors, as well as the fact that the query optimizer is smart enough to not materialize every OUTER JOIN that isn't needed in the result set (and this join elimination can happen with INNER JOINs as well, FYI).  I'm not going to go back and correct those, and I'll admit that I rushed through my initial thinking of this article.  But let's move on and try a slightly more extreme example – and one which is actually much more indicative of the cases where I've seen this kind of problem.  Picture a case where you are logging all changes to a user, have normalized certain attributes (in this case a user category), and encapsulated formatting logic (in this case date/time output).  You might have something like this:

-- normalize category descriptions
 
CREATE TABLE dbo.UserCategories
(
   UserCategoryID  TINYINT PRIMARY KEY,
   [Description]   NVARCHAR(32) NOT NULL UNIQUE
);
GO
 
CREATE FUNCTION dbo.GetUserCategory
(
   @UserCategoryID TINYINT
)
RETURNS NVARCHAR(32)
AS
BEGIN
   RETURN
   (
       SELECT [Description] 
           FROM dbo.UserCategories
           WHERE UserCategoryID = @UserCategoryID
   );
END
GO
 
-- encapsulate consistent date formatting
 
CREATE FUNCTION dbo.FixDate
(
   @d SMALLDATETIME
)
RETURNS CHAR(16)
AS
BEGIN
   RETURN 
   (
       SELECT CONVERT(CHAR(10), @d, 120)
       + ' ' + CONVERT(CHAR(5), @d, 108)
   );
END
GO
 
-- standard users table
 
CREATE TABLE dbo.Users
(
   UserID          INT PRIMARY KEY,
   UserName        NVARCHAR(255) NOT NULL UNIQUE,
   UserCategoryID  TINYINT NOT NULL 
                   REFERENCES dbo.UserCategories(UserCategoryID)
   /* , other columns */
);
GO
 
-- keep a log of who changed what 
-- (including initial creation, so always >= 1 row per user) 
 
CREATE TABLE dbo.UserChangeLog
(
   UserID          INT NOT NULL
                   REFERENCES dbo.Users(UserID),
   EventDate       SMALLDATETIME NOT NULL
                   DEFAULT CURRENT_TIMESTAMP,
   Details         NVARCHAR(255) NOT NULL
);
GO
 
-- now, an uber-view intended for use across
-- several different queries
 
CREATE VIEW dbo.UserData
AS
   SELECT
       u.UserID,
       u.UserName,
       /* , other u. columns */
       Category = dbo.GetUserCategory(u.UserCategoryID),
       LastUpdate = dbo.FixDate(a.LastModified)
   FROM
       dbo.Users AS u
   INNER JOIN
   (
       SELECT
           UserID,
           LastModified = MAX(EventDate)
       FROM
           dbo.UserChangeLog
       GROUP BY
           UserID
   ) AS a
       ON u.UserID = a.UserID;
GO

In this case, you'll see that the entire view is materialized no matter how few columns you ask for in the SELECT.  Just turn on the execution plan… you'll see that while the graphical plan is not 100% identical, all of the expensive operators are (click on the exec plan to embiggen):

SELECT UserID FROM dbo.UserData;
 
SELECT *      FROM dbo.UserData;

So, unlike in the example I presented earlier, in this case (and thanks Rob for the prodding), I think I can demonstrate that there is extra work being done in the case where you are only after a narrow subset of the columns.

Now, it could be argued that there are plenty of "bad habits" in there that are just as important, or even more important, than the one I am trying to highlight in this post.  It could also be argued that folks like Rob or myself, as well as a lot of this site's readers, would never lay out a system like this and expect it to perform.  But what I try to keep in mind when I write articles like this is that they are intended for people of all levels – those that may have contributed to this design, or those that may have inherited it and don't understand their current performance issues.  And I think it is hard to argue that this type of encapsulation and/or normalization is rare out in the wild, so ultimately it could be an issue that is affecting a lot of environments.

Anyway, is the uber-view always bad?  Of course not.  My intention here is not to tell you, "you should never do x and that is always true."  I just want to point out practices and usage patterns that should be thoroughly thought out as opposed to just blindly implemented.