Windows Server 2008 RTM

This time I am not just trying to draw your attention to this post, it has really RTM'ed!
More information here.

Together with the announcement of Windows Server 2008 they have also announced Windows Vista SP1 here.

They will probably be available somewhere next month so just a little more patience.

SQLCAT Site

The nice people from the SQL Server Customer Advisory Team have started a new website called SQLCAT.com.
It is a great source of information for large SQL Server implementations so make sure to check it out!

Remembering Ken Henderson

Some sad news on this blog. Ken Henderson was one of the SQL Server gurus who wrote wonderful books and had a real passion for SQL Server. We will sure miss him in the community. My condolences to his family, friends and colleagues. We will see you in the next life khen1234 :'(

Update: Ken's sisters have asked for stories about Ken you would like to share. Please check out this post for more information.

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 :-)

SQL Server 2005 Best Practices Analyzer (January 2008)

In case you have not noticed yet, the SQLCat team has released a new version of the Best Practices Analyzer. Among other things it contains 60+ new rules and the Analysis Services rules have been updated so they match the Best Practices warnings in BIDS 2008.

More information can be found here and the download can be found here.

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.

SQL Server 2005 SP2 Cumulative Hotfix Package 6 - Announced

Because CU5 is released here is the next announcement.
The question is of course how many of you have already implemented CU5?

SQL Server 2005 SP2 Cumulative Hotfix Package 5 - Released

It is out there! The build number is 3215.

http://support.microsoft.com/kb/943656/

EXEC AT

With every release there are really some features that you seem to miss, maybe because they are small or maybe because you just do not use that functionality that often.

That is how I recently stumbled upon a nice feature that has been added in SQL Server 2005 but I had never seen before.

EXEC AT allows you to execute queries on a linked server just like OPENQUERY and OPENROWSET but with a little less limitations. OPENQUERY and OPENROWSET for example do not accept variables for their parameters and they act like a table and thus limit you from executing certain statements like DDL.

EXEC AT on the other hand can take parameters:

EXEC sp_addlinkedserver [MyLinkedServer], 'SQL Server';

EXEC
(
'SELECT [LanguageID], [Description]
FROM myDB.dbo.Translations
WHERE TranslationID = ?;', 1000
) AT [MyLinkedServer]

It can do DDL:

EXEC
(
'USE myDB;
CREATE TABLE myTable
(myId int NOT NULL PRIMARY KEY,
myVarChar varchar(100) NULL
)'
) AT [MyLinkedServer]

It can take a username:

EXEC
(
'SELECT [LanguageID], [Description]
FROM myDB.dbo.Translations
WHERE TranslationID = ?;', 1000
) AS USER = 'WesleyB' AT [MyLinkedServer]

And last but not least it can take a variable:
DECLARE @SQLStmt nvarchar(max)
SET @SQLStmt = 'SELECT [LanguageID], [Description] FROM myDB.dbo.Translations'

EXEC(
@SQLStmt
) AT [MyLinkedServer]

It may not be the most funky feature in SQL Server 2005 but it really has potential when you are working with linked servers.

SQL Server 2005 Books Online (September 2007)

Because I have a day off my colleague said I should stop working and blog something.
Since inspiration comes mostly at work I will keep this one simple.

The updated Books Online for SQL Server 2005 are available here.

Sequential GUIDs

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

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

Update statistics before or after my index rebuild?

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.


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


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.

TechEd Barcelona 2007 - The Return

Yesterday was sadly the last day of our visit to Barcelona. What is there to say? I saw many interesting sessions and has a nice chat with Bob Beauchemin and Michael Rys who were both at the Belgian party. And not to forget, I also had a nice evening with the Microsoft Belgium folks.

A couple of interesting things I remembered and will probably be blogging about once the SQL Server 2008 CTP5 bits are available.

• CTP 5 should be released by the end of this month (more information should be available this Monday).
• Filtered indexes are cool and allow you to mimic an Oracle behavior as a side effect. You can now create a unique index but allow more than one NULL.
• Sparse columns and column sets are really cool (especially for data warehousing).
• Change tracking, which is used for sync services, can also be used for other purposes by yourself (and maybe one day for Merge Replication?).
• Filestream will be a valuable addition but should be carefully considered. One of the biggest drawbacks in my opinion is the lack of database mirroring support.
• There are actually names for a lot of things we have implemented.
• Data mining can be used for purposes you might not think of at first as was displayed by Rafal Lukawiecki.
• Many people still do not understand that a database engine is not a psychic, if you have no way of logically telling it which records you need it will not use the book of Nostradamus to guess. Oracle and Delphi were actually psychics by the way.
• Belgian fries still rule and yet they are called French fries. If you want to find out why check out Wikipedia.

I hope to get back to you soon because that would mean CTP5 was released ;-)

TechEd Barcelona 2007

Greetings from Barcelona.

We have seen some fine sessions already on SQL Server and VSDBPro.

I will post some more information after the event to wrap up the interesting stuff we have seen.

SQL Server 2005 SP2 Cumulative Hotfix Package 5 - Announced

We are in the process of gradually upgrading our servers to CU4 now (build 3200) and here is the next announcement.

I know many people think they are messing about with SQL Server 2005 and that it has more bugs than other products but this is not the case.
Releasing this many hotfixes is all part of a new strategy of the "incremental servicing model" which is explained here.

So do not panic, updates like this will be released every 2 months.

PowerShell

Many UNIX people will probably say "boring" when they see PowerShell. UNIX has always supported great flexibility in scripting, of course they did not have a great GUI like Windows does. But as server management is getting more and more complex because of the large environments and many flavors of server software it was time to start thinking without the GUI because it does tend to work a bit slower as compared to a script enabled server and some things are just impossible using only a GUI. With scripts you can check for certain conditions and take actions automatically and appropriately based on these conditions.

The answer to this is PowerShell, a command line shell and scripting environment which will enable you to manage almost everything. One of its most powerful features is the ability to extend it with .NET components as well as being able to use the out of the box .NET Framework objects.

For Exchange 2007 they have first written the PowerShell layer and based the GUI entirely on these scripts. The GUI will also support a Script This Action (sound like we were ahead in SQL Server) which will create the PowerShell script for you.

At the customer we have something we call DCS (Data Conversion Scripts), basically it means going from one version of the database to the next version. We had a great .NET console application combined with some batch files which does this for us. Although this worked great we felt this was getting too complex to manage because we do parallel development and support different version at the same time. A new system was required using a simpler concept which my colleague killspid had proven was possible.

We started out with a batch file which used sql scripts with SQLCMD to perform the DCS task. Although this worked fine the lead DBA had a new requirement which needed more logic and was hard to do in a batch file or T-SQL. We decided to rewrite the DCS in PowerShell and I am very glad we did. Using a PowerShell script, SQLCMD and an XML config file we now support all the requirements and we have much more control than we did in the previous versions. The support for variables in SQLCMD has also proven itself very useful so do not underestimate the power of SQLCMD either.

I must admit the syntax and way of working is a bit peculiar at first because you have to lose the .NET mindset and get into the scripting mindset. If you are used to creating administrative scripts you will probably have less of a problem.

Tip: if you want to get rid of the output SQLCMD gives you in PowerShell add >$null behind it.
eg. sqlcmd -SmyServer -dmyDatabase -E -b > $null

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

SQL Server 2005 SP2 Cumulative Hotfix Package 4 - Released

It is out there! We are at the nice round build 3200.
Same as with CU3 there is also no download available.

http://support.microsoft.com/default.aspx/kb/941450