Search results for 'app:weblogs' matching tag 'sql'

Recording of my SQLBits Session on Visual Studio 2008

Anonymous — Mon, 31 Aug 2009 05:00:00 GMT

A webcast recording of SQLBits IV session ‘Making the SQL developer one of the family with Visual Studio Team System’ is now available on the SQLBits site. This discusses the features of the VS2008 Database GDR Edition.

Unfortunately I will not be proposing a session for this years SQLBits community event on the 21st of November 2009 at Celtic Manor in Newport, as I will be travelling back from the Microsoft PDC in LA

More SQL Conferences coming up, including SQL Bits and SQL Down Under

robfarley — Thu, 27 Aug 2009 05:00:00 GMT

I know I won’t be there, as I’m a million miles away in Australia, but being from the UK myself, I always have an interest in the UK SQL community and in particular, events like SQL Bits.

This is the fifth SQL Bits conference, and they keep getting larger and larger. I’ve heard it’s now the largest SQL-focussed event in Europe. It’s going to be in South Wales (that’s OLD South Wales, not New South Wales), in November. I’m sure the area is lovely, good beaches ‘n all that… but considering it’s late November in Wales, I think you’ll be going for the SQL content, not the scenery.

Of course, if you are in New South Wales, then you ought to be thinking slightly earlier, in particular, the second weekend in October. The third SQL Code Camp is being held in Wagga, with many regular speakers (like myself) and quite a few new ones too.

These two events are clearly the significant SQL events in the last quarter of the year. I’m sure no-one cares about SQL PASS, after all. (I do wish I was going to this one, but I won’t be. I plan to go one year, but I was in the US that week last year, and I don’t plan to be away from home for two birthdays in a row. Maybe next year. It is the biggest SQL event in the world, with great speakers from everywhere, including many good friends of mine.)

No matter where you are in the world, there are SQL events that you should be going to. Professional development is really important for your career, and you shouldn’t neglect it. That being said, make sure you find me at TechEd Australia.

SSRS: Removing the Navigation link using an Expression

robfarley — Wed, 05 Aug 2009 05:00:00 GMT

There are times when you want to have a Navigation property of a textbox (typically providing a link to a URL or other report), but you don’t always want the link to be there. Sometimes you just want it to be an ordinary textbox.

This particularly applies when you’re using a Matrix, and you don’t want the Subtotal rows to have the navigation links. Previously I’ve blogged about using InScope to control various properties. But the thing that I hadn’t noticed was how to make the link actually disappear. I could make it point at somewhere less useful (like the current report), but I didn’t want the cursor to change.

And then my friend (and fellow MVP) Jason Strate told me that if you make the Expression give the result of “Nothing”, then this does the trick.

So try something like: =iif(inscope("matrix1_SalesPersonID"), “http://someurl.com”, Nothing)

Thanks, Jason!

Still learning… foreign keys don’t need to reference a primary key

robfarley — Wed, 05 Aug 2009 05:00:00 GMT

…but you should still have a primary key on every table of course.

It’s just that I only recently discovered that you can have a foreign key that references something else, so long as it’s known to be unique through a unique index / constraint.

The scripts here demonstrate this in SQL Server 2005 and beyond.

create table testunique (id int identity(1,1) primary key, otherid int);
go
create unique index ixOther on testunique(otherid);
go
create table testFK (id int identity(1,1) primary key, someid int)
go
alter table testFK add constraint fkTest foreign key (someid) references testunique(otherid)
go

And then if I try to drop the ixOther index, I get an error saying:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index 'testunique.ixOther'. It is being used for FOREIGN KEY constraint enforcement.

So I guess this is another reason not to blindly remove indexes that aren’t mentioned in sys.dm_db_index_usage_stats

Counting consecutive instances with DENSE_RANK

robfarley — Sat, 25 Jul 2009 05:00:00 GMT

It’s hard to think of a practical use for DENSE_RANK(), but here’s one.

It’s one of the ranking functions that was introduced in SQL Server 2005, along with RANK(), NTILE(n) and the incredibly useful ROW_NUMBER(). But apart from producing reports, it’s quite hard to find genuine uses for some of them.

Yesterday I answered a question at StackOverflow (first time – I often answer questions at Experts Exchange and on the MSDN Forums, but never at StackOverflow before). It’s at http://stackoverflow.com/questions/1176011/sql-to-determine-minimum-sequential-days-of-access/1176255#1176255

The problem is trying to find runs of consecutive days of activity. I solved it using ROW_NUMBER() and the integer value of the day, grouping by the offset between them. When you partition the ROW_NUMBER() by the users, you have a complete solution. It works nicely.

But if people can have multiple entries in a day, then ROW_NUMBER() doesn’t cut it – the offset would change whenever someone didn’t have exactly one record per day. Of course, there might be logic in place to make sure this never happens, and I hope there would be – but if there isn’t the problem just becomes one for DENSE_RANK().

You see, DENSE_RANK() won’t go up for ties. If you have two entries on the same day, they’ll be given the same rank. And then the next day will be as if there had only been one the previous day. If you have ten 107ths, the next DENSE_RANK() is 108 (RANK() would give the next value as 117).

So then my query of:

with numberedrows as
(
select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
from tablename
)
select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID
from numberedrows
group by UserID, TheOffset;

…becomes:

with numberedrows as
(
select dense_rank() over (partition by UserID order by cast(CreationDate-0.5 as int)) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
from tablename
)
select min(CreationDate), max(CreationDate), datediff(day, min(CreationDate), max(CreationDate)) + 1 as NumConsecutiveDays, count(*) as NumEntries, UserID
from numberedrows
group by UserID, TheOffset;

Bear in mind that I’m having to truncate the CreationDate here. If the values were stored as an date with no time component, I’d be able to use CreationDate without modification, which lends itself much better to indexing strategies. For this scenario, I’d prefer to have an index on a computed column which was the truncated date.

So there you go – a practical use for DENSE_RANK().

Counting consecutive instances with DENSE_RANK

robfarley — Sat, 25 Jul 2009 05:00:00 GMT

It’s hard to think of a practical use for DENSE_RANK(), but here’s one.

So then my query of:

with numberedrows as
(
select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
from tablename
)
select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID
from numberedrows
group by UserID, TheOffset;

…becomes:

with numberedrows as
(
select dense_rank() over (partition by UserID order by cast(CreationDate-0.5 as int)) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
from tablename
)
select min(CreationDate), max(CreationDate), datediff(day, min(CreationDate), max(CreationDate)) + 1 as NumConsecutiveDays, count(*) as NumEntries, UserID
from numberedrows
group by UserID, TheOffset;

So there you go – a practical use for DENSE_RANK().

The new brand is Microsoft SQL Azure

robfarley — Thu, 09 Jul 2009 05:00:00 GMT

It used to be SQL Server Data Services. Then SQL Data Services. Then we started hearing about SQL Services, and now (I hope finally), we have Microsoft SQL Azure instead of SQL Services, and the Microsoft SQL Azure Database instead of SQL Data Services.

http://blogs.technet.com/dataplatforminsider/archive/2009/07/08/microsoft-sql-services-is-now-microsoft-sql-azure.aspx

I think this is a sensible move. For a while now, whenever refer to SQL Services, I have to make it clear that it’s not the Windows Services that run SQL on your server, but that it’s the “in the cloud” offering that Microsoft are bringing out. Now, I can talk about SQL Azure, and it’s very clear what it means. And SQL Azure Database is clear too, as opposed to whatever will come, like (and I’m predicting here, haven’t heard anything) SQL Azure Analytics or SQL Azure Reporting.

This is definitely a positive move, and should hopefully help people realise that this is not the same as SQL Server, help them approach their SQL Azure platform slightly differently to their On-Premise equivalent.

For example… At the moment, the SQL Azure Database platform is limited in size. I hope it will soon be unlimited, but for the time being there is a limit which will affect development decisions. If your database suddenly needs to store a lot more data than you expected, you may need to hurriedly work out how you’re going to handle this. But I think having a name which reflects the Azure nature will prompt developers into making this decision earlier.

I’m looking forward to getting my hands on SQL Azure, and seeing how my job will change because of it.

Fun with a SQLExpress 2005 upgrade 2008

Anonymous — Mon, 06 Jul 2009 05:00:00 GMT

On my development PC I had a 2005 instance of SQLExpress that was installed as part of the VS2008 setup. I thought I had upgraded it when I put on the SQL 2008 Management tools and/or VS2010 beta, but it seems I didn’t. I thought I would try the new Microsoft Web Platform Installer, but this also thought I had done the upgrade to 2008, I suspect due to the fact I had the 2008 management tools.

Note: If you are using the Microsoft Web Platform Installer 2.0 RC remember you can’t just click on it to run from the web if you are running as a non-administrator user on your PC (as you should be, running least privilege). You need to download it and ‘run it is administrator’ or open it in a browser running as administrator to get it t even load.

So I needed to download the SQLExpress 2008 media to do a manual upgrade, as I remembered I could not use the developer edition media I had to hand to upgrade and Express instance. This download in itself proved problematic. I did a downloaded from MSDN, but the file I got gave a ‘not a valid win32’ error when I tried to run it. Also I noticed each time I tried to download it in IE8 it was a different size – not a good sign! Once I swapped to Firefox it downloaded without issue.

Anyway in the end I got the right media and access rights and the upgraded went smoothly. However then I tried to attach a 2008 DB (the reason I needed the upgrade in the first place) I got the error

Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)

I had yet again forgotten to run SQL Management Studio as an administrative user. This error dialog is SQL Management Studio was of saying you don’t have the rights!

Excel dates counted differently and a reliable way of working out the day of the week

robfarley — Sun, 05 Jul 2009 05:00:00 GMT

Following my recent post about 40,000 days, I got a couple of emails telling me that Excel disagrees about when the 40,000th day is. And this is true – Excel counts Day 40000 as July 6th 2009, not July 7th.

Unfortunately for Excel users, they’re wrong. And it’s down to the fact that Excel thinks that 1900 was a leap-year, when we all know it wasn’t. You see, leap-years only fall on the century year if the year is divisible by 400. So 2000 was a leap-year, but 1900 wasn’t, and 2100 won’t be either.

What’s more, this has been a problem for ages, and it was a conscious decision to introduce the bug. Essentially, Lotus 1-2-3 thought 1900 was a leap-year, and to be consistent with the market leader of the day, Microsoft treated 1900 as a leap-year too. And nowadays, we have a situation where backwards compatibility is more important. Today, if you have an application which knows that day 40000 is July 7th, then you’d better not treat your date that same way in Excel (or vice-versa).

The thing that I find really amazing with this is that Excel tells me that Feb 29, 1900 was a Wednesday. But it didn’t exist – so what happened that week? Actually (and you can check other calendars for this, including Windows’ one), Feb 28th was a Wednesday, and Excel gets the day of the week wrong for the first 59 days of its calendar.

I know you don’t care, but perhaps you should – in case you ever write an application that needs to know what day of the week it is.

Working out the day of the week is really trivial. For instance, in SQL Server, you can generally ask for the DATEPART(dw,…) of the date in question, and get a number back, telling you what day of the week it is. It’ll tell you 1 for the 1st day of the week, 2 for the 2nd, and so on.

Which is great, until you find that someone in your organisation says that Sunday is the first of the week, but someone else insists that it’s Monday. In the movie industry, I think Thursday is the first day of the week. So then, when is the 5th day? In SQL we have @@DATEFIRST, which helps a lot, but a method I like to use is to count the number of days since a known Sunday (or whatever), and take the “mod 7”. If that’s zero, I’m an exact number of weeks since that known Sunday. It works nicely, and it’s simple enough for everyone to understand (and it works regardless of location or other changeable settings).

But if you had picked your “known Sunday” in early 1900 using Excel, you’d’ve got it wrong, and your data might not work if you push your system out to SQL later (so pick something later – like 1901). I recently dealt with a date dimension that someone had put together in Excel and imported into SQL – if this data had gone back to 1900, then there would’ve certainly been errors in it (for a start, the import wouldn’t’ve worked because SQL would’ve complained that Feb 29, 1900 wasn’t a valid date).

My preference with date dimensions is to use a lot of computed columns, and only ever populate a single field. It works nicely, and it’s almost no effort to extend the table to include extra dates when required.

40,000 days since the beginning of ‘time’

robfarley — Wed, 01 Jul 2009 05:00:00 GMT

Next Tuesday feels like a significant day. Not only is it 7/7, and the fourth anniversary of the London bombings (in 2005), but it’s 40,000 days since the beginning of time. Well, not proper time, but since the start of the 1900s.

SELECT DATEDIFF(day,'19000101','20090707')+1 AS DaysOf1900s

So if Day 1 is Jan 1, 1900 (as many systems tend to use – SQL actually considers it Day 0), Day 40000 is 7/7/2009.

I actually rate the start of the twentieth century as a very significant event in the human race. In 1900 the world was changing faster than it had ever done before, and no-one can deny the significance of the twentieth century in history. I’m sure change will continue to become faster, but I found it interesting recently when I noticed that we were approaching this ‘milestone’ of sorts.