Michael J. Swart

March 7, 2012

SQL Server 2012 Launches Today

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 1:08 am

Question: What just arrived today, has been months in the making and makes me super-excited today?
Answer: It’s my new nephew! I’m an uncle again as of early early this morning!
Question: Okay, what else?
Answer: What? That’s not enough? okay… (Original article follows)

Today is March 7th, 2012 and SQL Server 2012 launches today. Woohoo!

Here’s the scoop:

  • We’ve had the Community Technology Previews (CTPs) to mess around with for a while now.
  • We’ve had a Release Candidate (RC0) for a little bit.
  • Today the product launches.
  • April 1st, (no kidding) the product is released to general availability.
  • But don’t wait until then. Today you can download the RTM evaluation copy.


  • SQL Server Data Tools is now RTW! More on that below.

The Virtual Launch

... it's just an illustration of one

Microsoft is marking the event with a virtual launch here. An online event that lets you watch them demo what they’ve been working on since the last release (And unless you’re a BI professional, that means since 2008)

Personally What I’m Most Excited About: SSDT

The code I work with gets deployed to a large number of systems which run on multiple versions of SQL Server. In order to avoid maintaining multiple code-lines, I can’t make use of the new features until we de-support all the older versions.

For example, I can’t use the IIF function (new in 2012) if I’m worried that the code could be deployed to a 2008 instance somewhere. So there’s going to be some waiting for me and probably some of you …

Not so fast, there are still a few things we can use today. And I want to focus on one of them. It’s called SQL Server Data Tools (SSDT) and it was Released To Web (RTW) today.

What’s SSDT do? It’s a free development environment. It lets you develop SQL for any version of SQL Server (including SQL Azure) whether you’re connected or not. It supports Intellisense (and as far as I can tell, even against 2005). It does schema comparisons and data comparisons.

The clincher for me is that the table designer is the first one I’ve ever preferred over a plain old text editor.  It’s that slick. Check it out.


January 25, 2012

Rebuild Your Indexes Online (When You Can)

So I was recently reading SQL Server’s blog, specifically the article Customers using SQL Server 2012 today! and some brave businesses are already using and enjoying some of SQL Server 2012 features that help their databases stay available. I’m excited about the new features too. For example, index rebuilds have been improved. We can now rebuild indexes online that include “blob” columns (like nvarchar(max), image, etc…). This means that (almost) every index can be rebuilt without requiring a table lock for the entire operation. And that’s good news for availability!

This wasn’t the case in earlier versions. In earlier versions, you couldn’t rebuild an index online if it included columns that were large strings. If you tried, you would get this message:

An online operation cannot be performed for index 'ix_t1_1' because the 
index contains column '<column name>' of data type text, ntext, image, varchar(max), 
nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered 
index, the column could be an include column of the index. For a clustered 
index, the column could be any column of the table. If DROP_EXISTING is 
used, the column could be part of a new or old index. The operation must be 
performed offline. 

Logic to determine when ONLINE=ON is supported

So that means there’s one more thing to check when finding out whether you can rebuild an index on-line. The logic now goes like this. You can rebuild an index online if:

  • You’re using Enterprise Edition or higher
  • And you’re using SQL Server 2012 or later
  • Or you’re using SQL Server 2008 or earlier
  • And your index is clustered and the table contains no blob columns
  • Or your index is non-clustered and the index includes no blob columns

Hmm… You can’t really put parentheses in a bullet point list. Here try this flow chart:

Clear as mud

Hmm… that really doesn’t clear things up too well either. And I haven’t even mentioned partitioned indexes.

Just Try It

Maybe you’re like me, you may have to deal with multiple versions, multiple editions, and multiple tables and their indexes. Instead of wading through that logic above, just TRY it and let SQL Server figure it out. Here’s a sproc which takes a table name and index name and tries to rebuild the index online. If it can’t, it builds it offline.

create procedure s_TryRebuildOnlineOtherwiseOffline
	@schema sysname = 'dbo',
	@tablename sysname,
	@indexname sysname
	set @schema = QUOTENAME(@schema);
	set @tablename = QUOTENAME(@tablename);
	set @indexname = QUOTENAME(@indexname);
	declare @sqlRebuild nvarchar(max)
	set @sqlRebuild = N'ALTER INDEX ' + @indexname + ' ON ' + @schema + '.' + @tablename + ' REBUILD';
	declare @sqlRebuildOnline nvarchar(max)
	set @sqlRebuildOnline = @sqlRebuild + ' WITH (ONLINE=ON)';
	begin try
		EXEC sp_executesql @sqlRebuildOnline;
		print @sqlRebuildOnline;
	end try
	begin catch
		EXEC sp_executesql @sqlRebuild;
		print @sqlRebuild;
	end catch

Alternatively, you can adjust the script to REORGANIZE an index when it can’t rebuild it online.

By the way, if you use Ola Hallengren’s maintenance scripts, he’s already taken all this logic into account! If you write your own maintenance scripts, feel free to incorporate what I have here.

Powered by WordPress