Blog ala Vidar

It’s been way too long since my last blog post, so I figured it was about time to give a little SQL update on things I’ve learned the last months.

After attending NDC, watching Peter Myers’ sessions about SQL Server Analysis Services and data mining, I’ve played A LOT with SSAS. It’s way different compared to T-SQL, but it’s a lot of fun and a nice challenge. Soon got a cube working as I want it, to show off to the department managers at the company I work at. Hopefully I’ve done a good enough job to encourage them to use it, and maybe even recommend to customers. We haven’t used SSAS too much yet, but that might be about to change.

I’ve also updated my scripts library to include “FIND – Space Usage By Table”. You can use this if you’ve done big clean-ups in your databases, and what to “shrink” some tables. “RECREATE – Foreign Keys” – I wrote this one to make automatic data-transfer easier. You run the first part to store the foreign keys in a temporary table, then remove them. Then you can do all your data-transfer-stuff without any issues. Then just run the rest of the script to create the foreign keys again.

NB!!! These are of course scripts without any warranties. I’m sharing them because you might need something similar, but always look over scripts you find on the internet. Not just mine, but everyone else’s too!

Last but not least, I’ve played some with SQL Server 2011 CTP3, codenamed Denali. The first thing you notice after installing it, if you haven’t already tested CTP1 (CTP2 was not public), is the new integration with Visual Studio 2010. Pretty! Not a big difference from Management Studio, but everything is now in VS; BI tools and Management Studio. I’ve already written about some new exciting features in Denali, but here’s some I didn’t mention, or didn’t know of at the time:

• A new edition/version called Express Local DB Runtime. Zero-configuration but full support for all programmability features.
• Books online has changed. It was awful in CTP1, but I think I like the version in CTP3.
• In-Memory Column-store indexes aka Apollo – Read more about it here.
• New restore options. Now you got a timeline to choose from!
• A couple of new functions, Analytic, Conversion, THROW. Full list of new functions.
• You’re now able to format the result set from EXECUTE. For example returning XML.

Googling “SQL Server Denali CTP 3 new features” will give you a lot more features.

Almost a month ago, Microsoft released the first CTP (Community Technology Preview) version of their upcoming SQL Server 2011, code-named Denali. I immediately downloaded and installed it on all my machines, and I’ve been using it ever since. It works perfectly with both 2005 and 2008, and of course with 2011.

Now, what’s new in this release? The biggest difference is that Management Studio is built into Visual Studio. Or, more correct, Management Studio has got a Visual Studio 2010-shining. I think this is a good call. For instance, I HATE the intellisense in 2008. It’s the first thing I remove when I’ve installed it, but in 2011, I haven’t turned it off yet, because it’s not in my way. It’s actually helping me write queries! The other big new UI related feature is snippets. Now you can, finally, have them inside Management Studio. I’m going to create a library of my scripts and make a snippet folder to share with all that’s interested. I’ll post it here on my blog when it’s ready.

When it comes to T-SQL enhancements, there’s actually a couple of new features that looks very interesting:

ORDER BY has some new features. With the following query, you get 10 rows. Not the first 10, but from row 10 (OFFSET 10) to 19 (FETCH NEXT 10). I think this will be easier to use for paging than using ROW_NUMBER() in 2008.

SELECT *
	FROM sys.sysobjects
	ORDER BY name
	OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Notice that in books online they specify that FETCH FIRST and FETCH NEXT are synonyms and that they’re there to provide ANSI compatibility.

CREATE SEQUENCE is a completely new T-SQL feature. The following query makes a sequence that you can poll.

CREATE SEQUENCE test
	AS INT
	START WITH 1
	INCREMENT BY 1

As you see, the script is not refering to any tables, which means you can use this sequence anywhere by using NEXT VALUE FOR MySequence. For instance:

INSERT INTO MyTable
  (Field1, Field2)
VALUES
  (NEXT VALUE FOR MySequence, 'Value2')

You can also use this feature as a default constraint. This could be helpful when identity-columns isn’t good enough.