Rob Farley

This is an oldie, but a goodie. I was talking to Dave Gardiner about a question he had recently, the question of how to dynamically sort the results of a T-SQL query.

He was thinking of something like this:

--Not so useful - only works in a stored procedure
IF (@order = 'Column1')
    SELECT * from Table1 ORDER BY Column1
ELSE IF (@order = 'Column2')
    SELECT * from Table1 ORDER BY Column2

And one of the suggestions in his comments was to use the CASE statement:

--Not bad, but only works if the types are compatible
SELECT *
FROM Table1
ORDER BY
   CASE WHEN @order = 'Column1' THEN Column1 ELSE Column2 END

This isn't bad, but if Column1 and Column2 are different types (well, incompatible types), you get an error when the ELSE clause is used.

A better solution is to remember that CASE gives NULL if there nothing is satisfied:

SELECT *
FROM Table1
ORDER BY
   CASE WHEN @order = 'Column1' THEN Column1 END,
   CASE WHEN @order = 'Column2' THEN Column2 END

This way, it's either ordered by "Column1, NULL" or by "NULL, Column2". Both times, it's the order we want, and we're doing it in a single query.

SQL Server gives you Fuzzy Lookups and Fuzzy Grouping, but only in SQL Server Integration Services. It's not even on the list of SSIS enhancements for SQL Server 2008.

This week at the MVP Summit, I was having a discussion about this with Jamie Thomson, and we had a chat with one of the T-SQL guys to suggest it. The response came back with "Log in on connect", which I did just now. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=338664

Please check it out and provide comments, votes, validations. All this will help persuade Microsoft to implement this useful feature. And tell your friends too! The more it receives, the more likely it will be implemented.

I'm at the MVP Summit this week - an event at which Microsoft communicates (talking and listening) with MVPs. I'm having a great time. I'm seeing people I haven't seen in ages - Aussies I rarely see (like Nick Randolph), former Aussies (like Chuck Sterling), people I talk to regularly on IM (like Simon Sabin), and people I see almost never (like Dejan Sarka). I'm also meeting a lot of people I've never met before, like Allen White, Tony Rogerson and Desmond Tutu and the Dalai Lama.

Oh, alright, I didn't really meet the Dalai Lama (he walked past, but we didn't talk), but I did meet Archbishop Desmond Tutu. He was in the hotel lift this morning as I was on my way down to breakfast. He said he's an Arsenal fan and was very sad that we lost to Liverpool recently. I got a photo on my phone, and the quality isn't great - but I guess it's a nice memento.

From a technology perspective, the excitement is building around various new products. Summit is always good that way, but I can't write more for NDA reasons.

It's nice to give Microsoft feedback and get the product changed!

I first mentioned this in a post last month and logged a connect.microsoft.com entry. Microsoft checked it out, invesigtaed, and have now put a new property into SQL Server 2008 to resolve it! They write:

Thanks a lot for your feedback. We are taking a look at this issue.
Posted by Microsoft on 2/27/2008 at 8:54 AM

Greetings Rob,

First, let me thank you for your feedback on SQL Server; we really appreciate your willingness to spend time and write up the issue as you have, and to send it to us.

As you've described, the Defaults query that is run when you expand this property is just looking for defaults you've created via CREATE DEFAULT, not the more typical (imo) default constraints on table columns. It does by enumerating the SQL Server Management Object's (SMO) Database.Defaults collection, which in turn uses SQL as you've seen through the profiler. This however does not allow you to choose individual column’s DEFAULT constraints to be copied. In fact, the current implementation of the Transfer Objects Task doesn’t allow many kinds of column constraints to be copied.

For SQL Server 2008, we’ve just added a new property to the task, CopyAllDRIObjects, which corresponds to SMO’s “DriAll” property. This will cause all data referential integrity objects, including constraints, on objects you’ve selected, to be included when copying. So for example if you’ve selected three tables to be copied and they each have columns with DEFAULT (or other types) of constraints, and you enable CopyAllDRIObjects, this will cause their constraints to be copied as well to the destination. DRI objects for objects not selected, i.e. constraints on columns in tables you have not selected, will not be copied.

Thanks again for your post to connect; we look forward to hearing from you in the future. Take care!

SSIS Team

Posted by Microsoft on 3/6/2008 at 5:47 PM

I think the key words here are "on objects you've selected" - so that you're not trying to transfer things you don't want.

The fact that they took any kind of action is great, and definitely shows that it's worth jumping onto the connect.microsoft.com/sql site and putting in feedback about SQL Server. If you identify something that should be changed (and it's easy enough!), then you could have a very quick fix!

A little while back someone asked me how to make a PivotTable in Excel, showing the Min, Max and Avg of a field.

So I recorded a movie. Now, thanks to the wonder of Silverlight, it should be here for your viewing pleasure. No sound, so you can keep listening to whatever's in your ear already.

The morning after the Adelaide launch of the 2008 products, I got to enjoy breakfast with quite a handful other Adelaide community leaders, and few of the Microsoft DPE team. Conversation was remarkably varied, and one of the things that came up was Community Credit and the Second Life .Net User Group.  I've never done the Second Life thing, and I'm not sure I want to. It's an interesting concept though - the idea of attending a user group within a game. I guess it's a cross between an online community and a face-to-face one. Perhaps it's onlineface-to-onlineface? It's not my thing, but it might suit people who can't do face-to-face for one reason or another. Is there a Second Life SQL User Group?

This is really quite cool, especially since it's a tool feature, and one that will help SQL Server 2005 installs.

Suppose you have many servers, all registered in Management Studio, in the Registered Servers window. Now you can right-click on the Local Server Group, and pick the "New Query" option. Write a query, and it gives you a result set including the results from all the servers, as if you'd done a UNION ALL using linked servers.

So now if you want to do something like

select name, database_id from master.sys.databases;

on a query against multiple servers, you get three columns. Server Name, plus the columns from the query.

Sick of how slow backups can run, even though you're using compression from some tool like Hyperbac?

Greg Linwood's had a good idea to help. A large amount of the data in any database system is the data stored in indexes (copies of the data in tables, just ordered differently) to help your queries run faster, so if you can tell the system to backup the database without that data, then it should work a whole lot faster.

Of course, when you restore a smaller backup, it should a lot quicker - but you won't have the indexes in place yet. But you still have the option of letting them rebuild, so you ought to be able to get your system back up quicker (although potentially doing table-scans for a while).

If you like this idea, let Microsoft know, at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331220

Installing SQL Server 2008 gives you SQLPS.exe, an application you can run which is a PowerShell including the PSDrives for SQL Server (and compatible with SQL Server 2005 as well!).

But how do you make this work with your current copy of PowerShell? Well, I just worked this out. Darren Gosbell was lamenting with me about the fact that it's not a SnapIn for PowerShell, but it turns out it is. We ran:

Get-PSSnapin -reg

...and got the following results:

Name        : SqlServerCmdletSnapin
PSVersion   : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

Name        : SqlServerProviderSnapin
PSVersion   : 1.0
Description : SQL Server Provider

So then I ran:

Add-PSSnapin SqlServerProviderSnapin
Add-PSSnapin SqlServerCmdletSnapin

And now I have the full SQLPS functionality in my usual PowerShell window. Lovely. And by putting this into a PowerShell console file, I can have it load up automagically for me. SqlServerProviderSnapin gives me the SQL: and SQLPolicy: drives, whereas SqlServerCmdletSnapin gives me Invoke-Sqlcmd for running queries.

<?xml version="1.0" encoding="utf-8"?>
<PSConsoleFile ConsoleSchemaVersion="1.0">
  <PSVersion>1.0</PSVersion>
  <PSSnapIns>
    <PSSnapIn Name="SqlServerProviderSnapin" />
    <PSSnapIn Name="SqlServerCmdletSnapin" />
  </PSSnapIns>
</PSConsoleFile>

I'm still not convinced about the merits of using PowerShell to manage SQL Server, but as more Windows Admins adopt PowerShell as their preferred scripting language, I think it's worth having it as an option.

So I was trying to get the ScriptingOptions happening, and this is the only way I've found so far. It's ugly, and if you have a better way of doing this, please post a comment for me.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$so = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.DriAllConstraints = $true
(get-item Sales.Store).Script($so)

And this gives me the Sales.Store table with all the default constraints thrown in. Much better. Ugly way of doing it, but at least it works.

So I'm at the Heroes launch in Melbourne today. I'm going to hang out at the Experts booth, so if you're here and have a question about SQL Server 2008, why not come over?

How often do you wonder what something's actually doing under the hood (in the way that it talks to SQL Server)? It's a trick I've written about before - when working out how SSRS did multivalue parameters. This time, it's Integration Services' turn.

SSIS has this Transfer SQL Server Object task, designed to be able to transfer objects like tables from one database to another. But Ali got in touch saying that it wasn't transferring the default constraints for his table - that thing which means that the current date, 0 or 'dbo' gets populated in the field when you don't give it another value to use.

I've never had much cause to try out this task, so I started playing. Unfortunately, I'm still not that keen. I'm looking at the SQL 2005 version, not the 2008. Hopefully it's better - if I have the time I'll try it and post about it later.

First I pulled up the Edit window, to see what I could do. I went straight to the DefaultsList property, to see what I could move across explicitly. Nothing in there for the AdventureWorks database. Odd - sys.objects tells me there are default constraints. Perhaps you need to have the parent object selected for transfer first - still no luck. SQL Profiler to the rescue, it turns out that the query that SSIS is using to list the Defaults is:

SELECT
obj.name AS [Name],
SCHEMA_NAME(obj.schema_id) AS [Schema]
FROM
sys.objects AS obj
WHERE
(obj.type=N'D' and not(obj.parent_object_id > 0))
ORDER BY
[Schema] ASC,[Name] ASC

It's looking in the right place for the defaults, but the thing that's upsetting is that it's not listing the defaults that are created as part of a column definition. To make a default which is listed here, you must create the default first and then bind it to a table's column afterwards - not the way that most people do their default constraints.

I could reproduce Ali's problem easily enough - when I created a table with a default, and then transferred it from one database to another, the default constraint was missing. I think it must call the SMO Script() method without the ScriptingOptions set - when I did this (using PowerShell) I found that without parameters, the script didn't include default constraints.

What I found even more frustrating is that when I tried to transfer an object that wasn't in the default schema (I tried the Sales.Store table), SSIS simply couldn't find it, and gave me an error. Profiler showed that sure enough, it was only for objects in the default schema, despite the fact that I had selected the object from SSIS' list in the first place! Go figure. I do like SSIS, but I think this is one task that may be worth avoiding. Hopefully they'll fix it up later.

You can easily roll your own of course... by querying the system views yourself, like I did for indexes a while back. Use FOR XML PATH('') to do your string concatenation.