Candidate For Partitioning?

I have a table that looks like this:

ItemID TokenID WordCount
12345 11334 5
12345 3453 1
12345 546546 2
12345 242555 1
12345 556346 4
12345 346346 1






Code SnippetCREATE TABLE [dbo].[ItemTokensLink](
[ItemID] [int] NOT NULL,
[TokenID] [int] NOT NULL,
[WordCount] [int] NOT NULL DEFAULT ((1)),
CONSTRAINT [PK_ItemTokensLink_1] PRIMARY KEY CLUSTERED
(
[ItemID] ASC,
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]






So this table has now reached over 22 million rows. The problem is my machine only has 1GB ram....so SQl Server tries to load up the whole table and this is ~560MB....equals zero ram for ...well anything else!

Does SQL Server load up an entire table when performing an INSERT?




Code Snippet


INSERT INTO ItemTokensLink(ItemID, TokenID, WordCount)
SELECT DISTINCT @ItemID as ItemID, t.TokenID, Count(t.TokenID) as WordCount
FROM #Tokens t
Group by TokenID




@ItemID is parameter provided
#Tokens is a temp table create with a word list


Would partitioning help here?

The table is is 80% of the time added to and the ItemID will always increase. What is the best to deal with massive tables that are inserted to frequently?

How does SQL Server decide when to load a table into memory?
TIA