CODEWITHSUNDEEP
sqlsql-serversql-server-2005
TimLeung
TimLeung

Reputation: 3479

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

OR

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

Upvotes: 2

Views: 3612

Answers (5)

StingyJack
StingyJack

Reputation: 19469

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.

Upvotes: 2

super9
super9

Reputation: 30111

An alternative to paging (the way my company does it) is to use CTE's.

Check out this example from http://softscenario.blogspot.com/2007/11/sql-2005-server-side-paging-using-cte.html

CREATE PROC GetPagedEmployees (@NumbersOnPage INT=25,@PageNumb INT = 1)
AS BEGIN

WITH AllEmployees AS
(SELECT ROW_NUMBER() OVER (Order by [Person].[Contact].[LastName]) AS RowID,
[FirstName],[MiddleName],[LastName],[EmailAddress] FROM [Person].[Contact])

SELECT [FirstName],[MiddleName],[LastName],[EmailAddress]
FROM AllEmployees WHERE RowID BETWEEN
((@PageNumb - 1) * @NumbersOnPage) + 1 AND @PageNumb * NumbersOnPage
ORDER BY RowID

Upvotes: 0

HLGEM
HLGEM

Reputation: 96570

Think about it this way. Suppose your query would return enough records to populate 1000 pages. How many users do you think would really look at all those pages? By returning only the ids, you aren't returning a lot of information you may or may not need to see. So it should save on network and server resources. And if they really do go through a lot of pages, it would take enough time that the data details might indeed need to be refreshed.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

This is exactly the approach I use for Paging on the server,

Create a Table Variable (why incur the overhead of transaction logging ?) With just the key values. (Create the table with an autonum Identity column Primary Key - this will be RowNum. )

Insert keys into the table based on users sort/filtering criteria.. Identity column is now a row number which can be used for paging.

Select from table variable joined to other tables with real data required, Joined on key value, 

Where RowNum Between ((PageNumber-1) * PageSize) + 1 And PageNumber * PageSize

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

With approach 1, the data in the temp table may be out of step with the real data, i.e. if other sessions make changes to the real data. This may be OK if you are just viewing a snapshot of the data taken at a certain point, but would be dangerous if you were also updating the real table based on changes made to the temporary copy.

Upvotes: 1

Related Questions