EXEC dbo.LongTermMemory__Archive

Those of you who read my blog regularly may have noticed the previous post and maybe more important the comments on this post.
Somehow I think we were not the only ones having this questions and remarks.  As a follow-up please read the following blog posts as they are very relevant to the discussion.

First of all there is the Self-Service hotfix which is explained here.  This allows you to download CU's without formally contacting Microsoft Support.

Second of all there is a nice explanation about the new Service Pack approach here.  Basically is sums up the ISM model again but the news is that they will include Service Packs into this model too, which means they will also be released on schedule and more often!

Great news and again excellent proof that Microsoft is willing to listen to it's customers.

*NOTE*
Self-service: I had to change my country to United States in order to get the download link on top.

The guys @ Microsoft have been busy improving the quality of SQL Server even more.

First of all CU7 was released (Build 3239) here.
As usual this means CU8 has been announced here.

I know there has been a lot of requests from the customers to release Service Pack 3 for SQL Server 2005.  Although I see the benefits in the Incremental Servicing Model many people like the "certainty" of a good old service pack.  Because Microsoft listens to its customers they have decided to release Service Pack 3 this year.  More information can be found here.

There are many myths surrounding table variables and one of the most common is probably the 'in memory' story.  There are however a couple of other interesting facts about temp variables which you should also know about.  The guys from the Storage Engine have an excellent post about table variables so you definitely have to read it. 

One of the things that caught my eye in the post was the fact that queries do not go parallel when table variables are involved.  This was actually something I had never come across so I decided to put it to the test.  I borrowed a query from Craig Freedman who has an excellent series on parallelism.  Here we go:

CREATE TABLE T (A INT, B INT IDENTITY, C INT, D INT)
CREATE CLUSTERED INDEX TA ON T(A)

SELECT COUNT(*) FROM T OPTION (MAXDOP 0)

UPDATE STATISTICS T WITH ROWCOUNT = 1000000, PAGECOUNT = 100000

SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

DECLARE @t AS TABLE
(NumberOfRows int)

INSERT INTO @t
SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

 
For those of you who want to try it with a temp table:

CREATE TABLE #t
(NumberOfRows int)

INSERT INTO #t
SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

DROP TABLE #t