EXEC dbo.LongTermMemory__Archive

Today we found a table that was perfect for testing the newsequentialid() default. My colleague killspid thought it might be useful to talk about it and said "Blog it!".

This new feature has been added to SQL Server 2005 although it was available in SQL Server 2000 with the addition of an extended stored procedure written by Gert Drapers.

The Books Online state the following for the newsequentialid function:
"Creates a GUID that is greater than any GUID previously generated by this function on a specified computer." I can hardly say I ever saw a statement more simple but yet so complete.

Anyway, we decided to play around a bit with the sequential GUID too determine if this statement said it all. One of the most important limitations is that it can only be used as a default for a column with the uniqueidentifier type, doing otherwise will result in the following error:
"The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.".

It's a default so can we override the contents? Well it turns out you can but there is nothing sequential about that is there?

But why would you want a GUID to be sequential? Because it makes quite a good clustering key candidate. It's unique, static, relatively narrow and sequential. All these factors make it a nice alternative to an identity for some tables. The good news is that it helped us going from a 2 seconds process to a 500 msecs process and all we had to do is change the newid() default to newsequentialid() and of course change the fillfactor since it would be a waste to keep it low with a sequential key. Should you run off and change all your defaults? No, because as always it all depends!

Here is a little test script that proves a few of these points:

SET NOCOUNT ON
GO

CREATE TABLE myGuidTest
(myGuid uniqueidentifier DEFAULT(NewSequentialID()))
GO

CREATE TABLE myGuidTest2
(myGuid uniqueidentifier DEFAULT(NewSequentialID()))
GO

--Let's see if it really is "greater than any GUID previously generated by this function on a specified computer"
INSERT INTO myGuidTest VALUES (default)
GO 5

INSERT INTO myGuidTest2 VALUES (default)
GO 5

INSERT INTO myGuidTest VALUES (default)
GO 5

SELECT * FROM myGuidTest ORDER BY 1
GO
SELECT * FROM myGuidTest2 ORDER BY 1
GO

--Can we insert our own GUID?
DECLARE @newID uniqueidentifier

SET @newID = NewID()
SELECT @newID

INSERT INTO myGuidTest VALUES (@newID)
GO

INSERT INTO myGuidTest VALUES (default)
GO 5

SELECT * FROM myGuidTest ORDER BY 1

--Cleanup
DROP TABLE myGuidTest
DROP TABLE myGuidTest2

Fire up those virtual servers because the latest CTP is out!

Get it here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=3BF4C5CA-B905-4EBC-8901-1D4C1D1DA884&displaylang=en

What's new:
https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5470

I already posted some information about this some time ago and now I finally have some confirmation from a real guru about this often asked question.

<quote>
Q4) In a maintenance plan, is it a good idea to do an index rebuild followed by an update statistics?

A4) No! An index rebuild will do the equivalent of an update stats with a full scan. A manual update stats will use whichever sampling rate was set for that particular set of statistics. So - not only does doing an update stats after an index rebuild waste resources, you may actually end of with a worse set of stats if the manualy update stats only does a sampled scan
</quote>

So to wrap it up, a reindex updates the statistics with a full scan because he has to read through the entire index anyway.  It does however not update non-index statistics so therefore you might want to trigger an update anyway.  The nice thing about SQL Server 2005 is that he will only update the statistics if it is necessary if you use sp_updatestats.

A little test script to demonstrate some of these points.

SET NOCOUNT ON
GO

CREATE TABLE [dbo].[TestStats](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SomeText] [char](8000) NULL,
CONSTRAINT PK_TestStats PRIMARY KEY CLUSTERED ([ID])
)
GO

--Add some data
WITH RepeatCTE (RowNumber, SomeText)
AS
( SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) as RowNumber, NEWID()
  UNION ALL
  SELECT RowNumber + 1 as RowNumber, NEWID() FROM RepeatCTE
)
INSERT INTO TestStats
SELECT TOP 8096 SomeText FROM RepeatCTE OPTION (MAXRECURSION 0);
GO

CREATE STATISTICS [ST_SomeText] ON [dbo].[TestStats]([SomeText])
GO

sp_spaceused 'TestStats'
GO

DBCC SHOW_STATISTICS ('dbo.TestStats', PK_TestStats) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS ('dbo.TestStats', ST_SomeText) WITH STAT_HEADER;
GO

--Rebuild the index
ALTER INDEX PK_TestStats ON dbo.TestStats
REBUILD;
GO

WAITFOR DELAY '00:01:10' --wait a little to see the difference in the Updated column of SHOW_STATISTICS
GO

--Look at the Rows and Rows Sampled column = fullscan right?
DBCC SHOW_STATISTICS ('dbo.TestStats', PK_TestStats) WITH STAT_HEADER;
GO

--But these stats are not affected right?
DBCC SHOW_STATISTICS ('dbo.TestStats', ST_SomeText) WITH STAT_HEADER;
GO

WAITFOR DELAY '00:01:10' --wait a little to see the difference in the Updated column of SHOW_STATISTICS
GO

sp_updatestats
GO

--Did he update?
DBCC SHOW_STATISTICS ('dbo.TestStats', PK_TestStats) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS ('dbo.TestStats', ST_SomeText) WITH STAT_HEADER;
GO
--Nope, he didn't think he should

--If you really want him to update however he will
UPDATE STATISTICS dbo.TestStats ST_SomeText
GO

DBCC SHOW_STATISTICS ('dbo.TestStats', ST_SomeText) WITH STAT_HEADER;
GO

DROP TABLE TestStats
GO