Join the hub to connect, find resources, and get the information you need. Check out webcasts and podcasts from industry leaders, blogs by subject matter experts, community forums and more.
-
Categories
-
Archives
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010
- October 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- February 2010
- January 2010
- December 2009
- November 2009
- October 2009
- September 2009
- August 2009
- July 2009
- June 2009
- May 2009
- April 2009
- March 2009
- February 2009
- January 2009
- December 2008
- November 2008
- October 2008
- September 2008
- August 2008
- July 2008
- June 2008
- May 2008
- April 2008
- March 2008
- February 2008
- January 2008
- December 2007
- November 2007
- October 2007
- September 2007
- August 2007
- June 2007
- May 2007
- April 2007
- December 1969
-
Blogroll
-
Meta
Join us at a TechNet Event for a free learning session designed to tackle your toughest IT challenges. You will always get the latest tips, tools, and inside information with lots of time to talk shop with your peers.
Join our virtual launch experience today and check out our website packed with useful and entertaining content.
Some Simple Code To Show The Difference Between Newid And Newsequentialid
In SQL Server 2000 we got the uniqueidentifier data type and the newid() function
Lots of people thought that newid() would be very handy to create some unique values across all databases.
Newid() is nice but it has a little side effect; it causes terrible page splits because it is a random value. SQL Server 2005 introduced newsequentialid() where each value generated by the function is always greater than the previous value.
Let's take a look
First we will create these two tables
CREATE TABLE TestGuid1 (Id UNIQUEIDENTIFIER not null DEFAULT newid(),
SomeDate DATETIME, batchNumber BIGINT)
CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER not null DEFAULT newsequentialid(),
SomeDate DATETIME, batchNumber BIGINT)
SET NOCOUNT ON
INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),1)
go 1000
SET NOCOUNT ON
INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),1)
go 1000
Create these two clustered indexes
CREATE CLUSTERED INDEX ix_id1 ON TestGuid1(id)
CREATE CLUSTERED INDEX ix_id2 ON TestGuid2(id)
Run the code below
DBCC showcontig ('TestGuid1') WITH tableresults
DBCC showcontig ('TestGuid2') WITH tableresults
You will see that AvgerageFreeBytes is the same for both tables
What about the inserts themselves? Run this code below
SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid1
GROUP BY batchNumber
SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid2
GROUP BY batchNumber
The table with the Newsequentialid did the inserts about 40% faster.
Now we will insert 1000 rows in each table again
SET NOCOUNT ON
INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),2)
go 1000
SET NOCOUNT ON
INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),2)
go 1000
No we will look again what happened
DBCC showcontig ('TestGuid1') WITH tableresults
DBCC showcontig ('TestGuid2') WITH tableresults
Wow the first table uses 21 pages while the second one uses 12.
AvgerageFreeBytes is 3524 bytes per page in the first table and only 96 bytes per page in the second table. Obviously newsequentialid is the better choice.
Running the code below you will see that newid is still slower than newsequentialid by about 16% or so for batch 2
SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid1
GROUP BY batchNumber
SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid2
GROUP BY batchNumber
Now we will insert 10000 rows and then look at freespace and duration again
SET NOCOUNT ON
INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),3)
go 10000
SET NOCOUNT ON
INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),3)
go 10000
DBCC showcontig ('TestGuid1') WITH tableresults
DBCC showcontig ('TestGuid2') WITH tableresults
The first table uses 117 pages while the second one uses 80.
AvgerageFreeBytes is 2574 bytes per page in the first table and only 21 bytes per page in the second table.
Running the code below you will see that newid is still slower than newsequentialid by about 40% or so for batch 3
SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid1
GROUP BY batchNumber
SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid2
GROUP BY batchNumber
Clean up
DROP TABLE TestGuid2,TestGuid1
So that is all, do you use newid or newsequentialid and if you do use newid did you experience fragmentation or performance problems because of it?
Supporting Microsoft’s commitment to delivering world-class enterprise capabilities and to help customers take advantage of the latest innovations through Microsoft and SAP development, today Windows Server 2008 and SQL Server 2008 are certified to fully support SAP NetWeaver 7.0 and the newly released SAP Business Suite 7.
We see immense benefits for our customers to move to our platform with products like virtualization through Hyper-V, and data and backup compression in SQL Server 2008. This level of innovation, combined with the attractive price point, provides great value to our enterprise customers.
A few of our customers, such as Kimball International Inc and SABMiller RUS, are already experiencing the benefits of migrating to Microsoft platforms with SAP. Kimball International recognized up to 50 percent more processing power in using Windows Server and SQL Server over an Oracle/Unix-based environment, seeing a savings of $450K in just one year. Also, SABMiller RUS found that Windows Server 2008 Hyper-V helped to reduce infrastructure costs by almost 50 percent and expects 234 percent ROI. Two great examples of how Microsoft’s products can help our enterprise customers save money.
Tina Couch
Windows Server Marketing