SQL Server 2008 Performance

SQL Server 2008 has not yet been released but nevertheless there are already some test results from the CTP versions. Do note that the performance will probably improve even more when the final product arrives but the results today are already quite amazing. They can be found on https://www.microsoft.com/sqlserver/2008/en/us/benchmarks.aspx.

Apart from the performance improvements you get by installing SQL Server 2008 there has also been a close collaboration with the Windows Server 2008 team giving even better performance. Check out this post which discusses the performance improvement in replication because of the SQL Server 2008 / Windows Server 2008 combination.

This confirms that building on the strengths of SQL Server 2005 was certainly the right decision. For those of you who did not have the time to test Windows Server 2008, grab a server and install it! It offers some great enhancements on many different areas like security, high availability and more. As an extra benefit it has amazing performance. The I/O improvements together with more intelligent scheduling of threads and a rewritten TCP/IP stack make it blazing fast and the best choice for your new SQL Server 2005 and 2008 installations.

SQL Server 2008 February CTP (CTP6)

In case you have not noticed, the February CTP is out there!
Go get it!

SQL Server 2008 RTM

I got your attention didn't I?

Unfortunately the news in this post is not as good. The guys from the Data Platform Insider blog posted a 'Roadmap Clarification' for SQL Server 2008 and I am afraid it has a sad statement: "Microsoft is excited to deliver a feature complete CTP during the Heroes Happen Here launch wave and a release candidate (RC) in Q2 calendar year 2008, with final Release to manufacturing (RTM) of SQL Server 2008 expected in Q3. Our goal is to deliver the highest quality product possible and we simply want to use the time to meet the high bar that you, our customers, expect."

Is it really a sad statement? I think we can all agree that we need the highest quality product for our projects. Apart from this I think many of us still want to implement a lot of SQL Server 2005 features where we have not gotten around too. The leap from SQL Server 2000 to SQL Server 2005 was quite a large one and many projects are still running SQL Server 2000 or did 'simple' migrations to SQL Server 2005. Because SQL Server 2008 builds on the SQL Server 2005 strength the move will be less complicated so maybe we should start implementing those very needed SQL Server 2005 features and have some patience for SQL Server 2008 to come out.

Nevertheless I want to play with data compression so get the next CTP ready please :-)

A question about and answer to the MERGE Statement and the CLR version in SQL Server 2008

I was giving a presentation on the new programmability features in SQL Server 2008 when the following question popped up:"Does the MERGE statement support multiple WHEN MATCHED clauses?"

As you know it is possible to say "WHEN MATCHED AND myField = 0 THEN...", so it would make sense to have multiple WHEN MATCHED statements because depending on the criteria you could have different actions. Since you see this blog post you might have guessed that it is not the obvious answer. Trying to add a second WHEN MATCHED statement will result in the following pretty clear error: "An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement".

A second popular question was: "Which version of the .NET framework is included in SQL Server 2008?". This question has been answered by Bob Beauchemin on his blog with a very detailed explanation. Basically it loads the latest CLR version but there is a little catch which is explained by Bob so I recommend you read his post.

Some new features in SQL Server 2008 - November CTP

First of all I am very glad that 2 of my requests seem to have made it to SQL Server 2008:

• Cumulative waitstats info https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=275212


• A central registered server list https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=136596

Of course there is a lot more in this CTP 5 and the first thing you will notice is the new installer which is a lot better than in previous versions. A great addition is the ability to configure the location of tempdb at install time.





But let us look at the nifty stuff that has been added to SQL Server:

• Backup compression
• Intellisense
• Filestream support
• Many improvements on partitioning
• Plan freezing
• Resource Governor
• Change tracking
• Transparent Data Encryption
• Spatial data support
• Lock escalation can be defined on table level
• ...

Obviously there are many other new features available, as well in the Database Engine as in the other services like Reporting Services, Analysis Server and Integration Services. I suggest that you download the new Books Online to read about all the exciting new features because this time there are too many to discuss them in one post.

A quick note, when installing on Windows Server 2008 RC0 I had to start setup.exe because the autorun splash screen did not work and returned several javascript errors.

SQL Server 2008 November CTP (CTP5)

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

Table Valued Parameters

Before SQL Server 2008 the most common way to pass data from one procedure to another would be by creating a temporary table, fill it with the required data and then call another procedure that uses that temporary table. Although this is not a disaster it does have some flaws like not being strongly typed. The weak typing of temp tables in this scenario make it a perfect spot for issues. There are other solutions using XML but these tend to be more complex than necessary for the 'simple' thing you are trying to do... pass structured data around.

SQL Server 2008 now supports something called Table Valued Parameters (or TVP) which can help you in these situations. TVP's make it possible to use a "table" as a parameter for a procedure. A couple of limitations apply, TVP's can only be READONLY in the procedure that define them as a parameter and they can only be used as an input parameter. Apart from this the same rules apply to TVP's as to table variables for example no DDL can be executed against a TVP and no statistics are kept for TVP's.

A little example will make it very clear.

--Create test table
CREATE TABLE myUsers
(ID int, UserName varchar(50), UserRole tinyint);
GO

--Create the required type
CREATE TYPE UserRoleType AS TABLE
( UserRole tinyint );
GO

--Create procedure that takes the type as a parameter (READONLY is required)
CREATE PROCEDURE GetUsersInRole
@UserRoleType UserRoleType READONLY
AS
SELECT UserName
FROM myUsers u
INNER JOIN @UserRoleType ut ON u.UserRole = ut.UserRole
GO

--Insert some test data (multiple inserts in one go, another new feature)
INSERT INTO myUsers
VALUES (1, 'Wesley', 1),
(2, 'Tom', 2),
(3, 'Patrick', 2),
(4, 'Jan', 3),
(5, 'Bregt', 3)

--Throw in a new dmv to look at the type and check dependencies if you like
--SELECT * FROM sys.table_types
--SELECT * FROM sys.dm_sql_referenced_entities ('dbo.GetUsersInRole', 'OBJECT')
GO

--Lookup action
DECLARE @UserRoleType
AS UserRoleType;

--Lets use another new features (initialize var on declare!)
DECLARE @Admin tinyint = 1
DECLARE @PowerUser tinyint = 2
DECLARE @User tinyint = 3

--Add parameter values to the table valued parameter
--INSERT INTO @UserRoleType VALUES (1), (2)
INSERT INTO @UserRoleType VALUES (@Admin), (@PowerUser)

--Call stored procedure with specific type (remember the post is about table valued parameters)
EXEC GetUsersInRole @UserRoleType;
GO

--Clean up
DROP PROCEDURE GetUsersInRole
DROP TYPE UserRoleType
DROP TABLE myUsers

Some new features in SQL Server 2008 - July CTP

Date and time

As many of you already know we finally have a separate date and time datatype in SQL Server 2008. This is not the only new option for date and time however, there a couple of other new datatypes like datetime2 and datetimeoffset.

• Date
  The data datatype takes 3 bytes and has a range from 0001-01-01 through 9999-12-31
• Time
  The time datatype takes 3 to 5 bytes depending on the fractional second precision. Time has a range from 00:00:00.0000000 through 23:59:59.9999999. The default precision is 7 meaning hh:mm:ss.nnnnnnn and depending on the precision you specify - with time(x) where x is a number from 0 to 7 - the storage size differs . From 0 to 2 time will take 3 bytes, from 3 to 4 it will take 4 bytes and up to 7 it will take 5 bytes.
• Datetime2
  Although this makes me shiver as it makes me think of varchar2 it is a great datatype. Datetime2 takes from 6 to 8 bytes and has a range of 0001-01-01 through 9999-12-31. It also implements the same fractional second precision option as the time datatype. From 0 to 2 datetime2 will take 6 bytes, from 3 to 4 it will take 7 bytes and up to 7 it will take 8 bytes.
• Datetimeoffset
  The datetimeoffset datatype takes from 8 to 10 bytes and has the same properties as datetime2 but the difference with the other datatypes is that datetimeoffset is timezone aware. The fractional second precision again defines the storage size, from 0 to 2 datetimeoffset will take 8 bytes, from 3 to 4 it will take 9 bytes and up to 7 it will take 10 bytes. There are also a couple of datetimeoffset related functions like SWITCHOFFSET and TODATETIMEOFFSET.

HierarchyID

Another new datatype is the hierarchyid datatype which represents the position in a tree hierarchy. I did not have the time to play with it yet but from what I have read it implements 2 special indexing methods, depth-first and breadth-first. It also has a couple of related function like GetAncestor, GetDescendant, IsDescendant, ...

Object Dependencies

The new management views sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, and sys.dm_sql_referenced_entities gives you a view of dependencies that is always up-to-date. It even allows you to see cross-server and cross-database dependencies.

Page corruption

The new management view sys.dm_db_mirroring_auto_page_repair contains information about automatic repair attempts on mirrored databases (page restores from the mirror database).

SQL Server Extended Events

Built in support for the ETW (Event Tracing for Windows) engine to help you trace, log and debug problems. For more information about using ETW with SQL Server check out this post.

I recommend you to play with SQL Server 2008 yourself since there is already a very long list of new things to learn in this CTP! There are still a couple of other features in the July CTP which I have not seen yet so I hope I will find some time to experiment with these too.

SQL Server 2008 - July CTP

It's play time :-)
A lot of people will be very happy... date and time is available as a seperate datatype!

More information here and download here.

SQL Server 2008 Launch

SQL Server 2008, together with Visual Studio 2008 and Windows Server 2008, will be launched on the 27th of February. More information can be found here.

Greg Low once launched a nice suggestion for deferred constraints but unfortunately it did not make it to SQL Server 2008. But not to worry there are lot of other exciting features ;-)

Also some nice features in Windows 2008, for the I/O lovers: there is an internal limit of 64k per I/O request in earlier version of Windows, this limit has been removed in the new Windows core.

For Visual Studio 2008 we will see the integration of WF, WCF and WPF and of course LINQ.

Seeing the end of February date reminded me of a really stupid joke someone sent me.
So for the geeks among us:

It is March 1st and the first day of DBMS school
The teacher starts off with a role call.. Teacher: Oracle?
"Present sir"
Teacher: DB2?
"Present sir"
Teacher: SQL Server?
"Present sir"
Teacher: MySQL?
[Silence]
Teacher: MySQL?
[Silence]
Teacher: Where the hell is MySQL
[In rushes MySQL, unshaved, hair a mess]
Teacher: Where have you been MySQL
"Sorry sir I thought it was February 31st"

Fun with the new MERGE statement

I found some time to play with CTP3 and checked out the MERGE statement. What you achieve with it may not be rocket science and was perfectly possible with a couple of IF statements but using MERGE gives you much cleaner code. It looks like Microsoft is investing a lot in 'cleaner' coding in SQL Server 2008, which I can only welcome with great enthusiasm.

A simple example will show you exactly what the MERGE statement does.

CREATE TABLE StagedData
(ID int, UserName varchar(20))
GO

CREATE TABLE RealData
(ID int, UserName varchar(20))
GO

INSERT INTO StagedData VALUES (1, 'Slava') , (2, 'Paul') , (3, 'Wesley')
GO

MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

UPDATE StagedData SET UserName = 'Kimberley' WHERE ID = 3
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

DELETE FROM StagedData WHERE ID = 3
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

DROP TABLE StagedData
GO
DROP TABLE RealData
GO