For some time now I have been participating in usability sessions with Red Gate Software for their product SQL Response. This has been a great chance to shape a commercial application to do what I wanted it to do. I've seen ideas of mine appear in their latest version (ok,ok - other people MAY have had similar thoughts too...)
The team behind the application are now looking to create a new version, and are looking for input from real users, with real monitoring and performance issues. They are going to build the next version of their product to do exactly what users want, so get along to their website at The Future of Monitoring, have a look at what they've done so far and contribute YOUR ideas.
They are even giving away free copies of their new monitoring tool (when it's finished) - all you have to do is submit a design (doesn't have to be anything fancy, they just want ideas), and you'll be entered into the draw.
Tuesday 27 October 2009
Tuesday 20 October 2009
COUNT(DISTINCT) OVER() aggregate window function
Recently have been using some of the new partition and aggregate window functions in SQL Server 2005.(see here for the BOL entry).
However when I tried to change a count(distinct ...) query, I ran into an error!
create table TableA(
id int,
category varchar(10),
subcategory varchar(10),
sometext varchar(100)
)
go
insert into TableA (id, category, subcategory, sometext)
select 1,'Category A','fruit','apple'
union select 2,'Category A','fruit','banana'
union select 3,'Category A','fruit','cherry'
union select 4,'Category A','animal','cat'
union select 5,'Category A','animal','dog'
union select 6,'Category A','animal','lion'
union select 7,'Category B','vegetable','carrot'
union select 8,'Category B','vegetable','potato'
union select 9,'Category B','vegetable','onion'
union select 10,'Category B','name','alice'
union select 11,'Category B','name','bob'
union select 12,'Category B','place','london'
union select 13,'Category B','place','manchester'
go
from this sample data we can easily count the number of subcategories per category using
select category, count(distinct subcategory)
from TableA
group by category
which gives
category CountSubcategory
---------- ----------------
Category A 2
Category B 3
(2 row(s) affected)
but if we try to use the OVER() clause
select category, count(distinct subcategory) over(partition by category)
from tableA
we get
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.
Check BOL, and there's nothing that says that the DISTINCT argument cannot be used in the COUNT function when used in an aggregate window function. How confusing. Then I discover I'm not the only one to have the same thought, as this item on Connect shows.
So if you have the same issue and want this resolved, please log on to Connect and vote this up.
However when I tried to change a count(distinct ...) query, I ran into an error!
create table TableA(
id int,
category varchar(10),
subcategory varchar(10),
sometext varchar(100)
)
go
insert into TableA (id, category, subcategory, sometext)
select 1,'Category A','fruit','apple'
union select 2,'Category A','fruit','banana'
union select 3,'Category A','fruit','cherry'
union select 4,'Category A','animal','cat'
union select 5,'Category A','animal','dog'
union select 6,'Category A','animal','lion'
union select 7,'Category B','vegetable','carrot'
union select 8,'Category B','vegetable','potato'
union select 9,'Category B','vegetable','onion'
union select 10,'Category B','name','alice'
union select 11,'Category B','name','bob'
union select 12,'Category B','place','london'
union select 13,'Category B','place','manchester'
go
from this sample data we can easily count the number of subcategories per category using
select category, count(distinct subcategory)
from TableA
group by category
which gives
category CountSubcategory
---------- ----------------
Category A 2
Category B 3
(2 row(s) affected)
but if we try to use the OVER() clause
select category, count(distinct subcategory) over(partition by category)
from tableA
we get
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.
Check BOL, and there's nothing that says that the DISTINCT argument cannot be used in the COUNT function when used in an aggregate window function. How confusing. Then I discover I'm not the only one to have the same thought, as this item on Connect shows.
So if you have the same issue and want this resolved, please log on to Connect and vote this up.
Monday 12 October 2009
MSc Business Intelligence at Dundee University
Whilst I don't normally like just repeating what others blog about, once in a while you come across something that you just have to share!
Tony Rogerson has blogged about a new MSc in Business Intelligence that Dundee University are hoping to start in January. I say 'hoping' as if no-one registers then it wont be run! Although having spoken to Mark Whitehorn, the organiser, if the amount of interest generated so far is anything to go by, it is a certainity.
Tony doesn't mention it, but the course will run over 2 years for those wanting to complete it part-time via distance learning, and you will be required to attend in person for 2 weeks per year. The content seems to be product-agnostic, teaching the concepts of BI, although there will be some discussion of various technologies and techniques, and the final module will use MDX as the implementation language.
The cost of the course (if you enrol this year, - no guarantees this price will stay) is £1,700 per year, which when compared to the cost of commercially available courses that come in at about £2,000 for 5 days training, is a very good price. I also see the value of an MSc far outweighing the value of any product-specific certification (mentioning no names), but that's just a personal view.
I'm hoping to secure some support from my current employer, and get myself on the course ASAP. Haven't been in formal education for over 15 years, so it might be a bit of a shock to the system.
Anyway don't take my word for it, check out Tony's blog for some more info and get in touch with Mark (email address on Tony's blog)
Tony Rogerson has blogged about a new MSc in Business Intelligence that Dundee University are hoping to start in January. I say 'hoping' as if no-one registers then it wont be run! Although having spoken to Mark Whitehorn, the organiser, if the amount of interest generated so far is anything to go by, it is a certainity.
Tony doesn't mention it, but the course will run over 2 years for those wanting to complete it part-time via distance learning, and you will be required to attend in person for 2 weeks per year. The content seems to be product-agnostic, teaching the concepts of BI, although there will be some discussion of various technologies and techniques, and the final module will use MDX as the implementation language.
The cost of the course (if you enrol this year, - no guarantees this price will stay) is £1,700 per year, which when compared to the cost of commercially available courses that come in at about £2,000 for 5 days training, is a very good price. I also see the value of an MSc far outweighing the value of any product-specific certification (mentioning no names), but that's just a personal view.
I'm hoping to secure some support from my current employer, and get myself on the course ASAP. Haven't been in formal education for over 15 years, so it might be a bit of a shock to the system.
Anyway don't take my word for it, check out Tony's blog for some more info and get in touch with Mark (email address on Tony's blog)
Friday 9 October 2009
Manchester SQL Server User Group
Attended my first EVER user group meeting a couple of weeks back. I had been meaning to get to one of these ever since they started in Manchester earlier this year, but always something else had conspired against me.
Sure I've done SQL Bits, and various MSDN/TechNet type stuff, but this was different, more personal and certainly made me feel more involved. Looking forward to many more of these, and maybe next time I will network properly - I talked to many people but didn't get their details!
If you've never attended one of these, or perhaps are not sure they are for you, don't worry just get along to one. You'll meet people working in SQL at all levels, and even if you have nothing in common with anyone else, then you'll be able to share your 'uniqueness' with them. There's no pressure to network, speak or contribute, but after just one I bet you'll be itching to get more involved!
They are organised by the UK SQL Server User Group , the Manchester one by Chris Testa-O'Neill ( blog ), who shows great enthusiasm for the community.
Next Manchester one is planned for 15 Oct 2009, more details here, but check out the UK SSUG website for other events near you. They do Leeds, Dundee, Reading, Bracknell, Cardiff and the obligatory London.
Sure I've done SQL Bits, and various MSDN/TechNet type stuff, but this was different, more personal and certainly made me feel more involved. Looking forward to many more of these, and maybe next time I will network properly - I talked to many people but didn't get their details!
If you've never attended one of these, or perhaps are not sure they are for you, don't worry just get along to one. You'll meet people working in SQL at all levels, and even if you have nothing in common with anyone else, then you'll be able to share your 'uniqueness' with them. There's no pressure to network, speak or contribute, but after just one I bet you'll be itching to get more involved!
They are organised by the UK SQL Server User Group , the Manchester one by Chris Testa-O'Neill ( blog ), who shows great enthusiasm for the community.
Next Manchester one is planned for 15 Oct 2009, more details here, but check out the UK SSUG website for other events near you. They do Leeds, Dundee, Reading, Bracknell, Cardiff and the obligatory London.
Subscribe to:
Posts (Atom)