The Grouping Sets feature was added to the T-SQL GROUP BY clause with the release of SQL Server 2008. Despite its usefulness, I've not come across many (or any) of them in the wild at any of the clients I've worked at in the past few years. Here is a primer on how to use them, using the AdventureWorks OLTP database.
SQL Server 2005 Style In SQL Server 2005, we could use the WITH ROLLUP syntax to add an aggregated total row:
I was asked today if it was possible to use a case-sensitive LIKE statement in a WHERE clause. The default SQL Server collation is case-insensitive, but the collation used can be specified in a query. To return the collation of the current database:
SELECT DATABASEPROPERTYEX(DB_NAME(),'Collation'); In my case, this returned Latin1_General_CI_AS. The case-sensitive version of this is Latin1_General_CS_AS. To use this collation for a column, specify it after the column using the COLLATE statement.
In a recent data migration project, I had a task to re-number some primary key columns for certain tables, so that there was no key overlap when a number of systems were combined. Part of this process involved identifying and dropping all the foreign keys that referenced the primary key on the tables that were being changed, then adding them again after the updates were completed. To save a bit of time, I knocked up this little script to generate the DROP and ALTER statements for the referencing foreign keys for a given table.
It's been a while since I've posted any SQL Server content, so in a bid to get back into the swing of things, here are a couple of very simple, but useful, T-SQL commands. I've used these recently in a large project I'm working on.
Identify all tables with no Foreign Key constraints:
select distinct<br /> t.name<br /> from sys.tables t<br /> left outer join sys.foreign_keys fk on t.object_id = fk.
I've been a Posterous user for over 3 years, having used them to host all my sparsely updated blogs. It came as a bit of a surprise this week when their founder Sachin Agarwal announced that the company had been bought by Twitter. From the press releases, the acquisition appears to be more about buying the people rather than the product. All good for the Posterous staff I think, I'm pleased for them if it works out.
Since I stopped using Windows as an OS for personal use, almost four years ago, I also gave up using IM chat programs. Back then, I was a fairly big user of MSN Messenger, but when I found the Mac version at that point to be flaky to say the least, I ditched it completely. And I didn't miss it at all. Apart from using Skype for remote work purposes, I've shied away from almost all other chat programs.
So Amazon announced their new Kindle range this week, which included the new Android-based tablet, the Kindle Fire. I really like the current Kindle device, but I’m not sure how I feel about the new products yet. It seems a touch half-baked to me, and there are some design decisions I really don’t understand.
iPad Competitor In the non-techie media there has been much talk of how the Fire is a competitor to the iPad.
Since last weeks post on automated DB and ETL deployment builds with Powershell, I’ve made a couple of small but useful changes to the Powershell script.
The distinct parts of the script are now encapsulated in parameterised functions. The end goal of this is to be able to turn the main script into a Powershell Module that could be installed and used when required. So now there are three functions, GetHeadFromSVN, SyncDB and DeployPackages.
You know sometimes you keep hearing about something, about how great it is, yet for some reason, you decide to ignore it and not bother checking it out? Well that’s pretty much what I had been doing with Markdown till this week. Almost every text editor I looked at on the Mac and iOS app stores mentioned support for Markdown, tech guys I respect either wrote about it or talked about it in podcasts.
This week I’ve been working on a new branch of a data warehouse project. To make deployment to development environments easier I decided I’d attempt to get some sort of continuous integration/automated builds to our dev server up and running. Doing this sort of thing has never been particularly straightforward for database projects, but with the fantastic SQL Compare command line tools and SQL Source Control from Red Gate, this task is made so much easier.