SQL – Temp Table: Storing all columns in temp table versus only Primary key

I would need to create a temp table for paging purposes. I would be selecting all records into a temp table and then do further processing with it.

I am wondering which of the following is a better approach:

1) Select all the columns of my Primary Table into the Temp Table and then being able to select the rows I would need

2) Select only the primary key of the Primary Table into the Temp Table and then joining with the Primary Table later on?

Is there any size consideration when working with approach 1 versus approach 2?

[EDIT]

I am asking because I would have done the first approach but looking at PROCEDURE [dbo].[aspnet_Membership_FindUsersByName], that was included with ASP.NET Membership, they are doing Approach 2

[EDIT2]

With people without access to the Stored procedure:

  -- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
    SELECT u.UserId
    FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
    ORDER BY u.UserName


SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
        m.CreateDate,
        m.LastLoginDate,
        u.LastActivityDate,
        m.LastPasswordChangedDate,
        u.UserId, m.IsLockedOut,
        m.LastLockoutDate
FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
       p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY u.UserName

Answer

If you have a non-trivial amount of rows (more than 100) than a table variable’s performance is generally going to be worse than a temp table equivalent. But test it to make sure.

Option 2 would use less resources, because there is less data duplication.

Tony’s points about this being a dirty read are really something you should be considering.

Advertisement

Answer