SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

August 2010 - Posts

Kent SqlServer UserGroup

I am now in the process of arranging the 2nd meeting of the kent sqlserver user group.  This will be on the 22nd September at the Style and Winch in Maidstone.

 If you wish to give a short "nugget" (a 5-10 minute presentation)  of a feature of the sqlserver product stack , then let me know and I'll schedule you a slot.

Please register at http://sqlsocialkent20100922.eventbrite.com/ and dont forget about our Redgate Sql Source control competition

See you there.

Kent SQL Server User Group

Well , that was an experience. 

It was my first time arranging anything like that and it seemed to me that everything came together in the end ok.  Things didn't quite go according to plan when I was delayed setting up by the filming of a Jack the Ripper drama in the pub, additionally Neil Hambley (blog|twitter) was running late and had had hard disk corruption resulting in a change on presentation. 

Thankfully, my major worries of A) A complete absence of attendees and B) AV Equipment Failure were unfounded as we had a 25+ turnout and my trusty home projector was pushing out a bright and clear image.

With Neil delayed and the agenda already in taters, Allan Mitchell (blog|twitter) stepped up to the mark and got going with his presentation on StreamInsight. 

kentug20100819

If ,like me, some of this left you mentally floundering, then check out Allan’s cuppa corner items for a refresh at http://www.sqlserverfaq.com/.  Thankfully, Allan, famous for overrunning, was delighted to know that the food had also been a victim of Jack the Ripper (Not known for his dislike of a vol-au-vent normally) and had been set back a bit too.

The food and drinks were provided by our sponsor for the evening Influential software.  Big big thanks to them for helping me to ‘keep it local’ and being supportive through this whole process. 

iss-logo

After a bit of socialising, always good to meet tweeps and catch-up with old friends and colleagues, Neil presented his plan B option of computed columns & indexed views.  Having used both of these features ‘in the wild’, he is very versed in the pros and cons of both and was able to give helpful advice on the how, whys and whens. 

So, would I do this again , you bet I will.  I need to recover a little bit first and then ill be getting straight onto it.   For this event I have a SQL Source Control licence to give away courtesy of RedGate.  So if you fancy this, simply download a free trial copy, post a review of the product on your blog, send me a link and the best will receive the license. Even if you dont win I have 10 runners up RedGate TShirts.  This should be a warts-n-all review , if you disliked the product then that counts to.  The only caveat is that you will have to of registered for the inaugural or attend the next meeting.  Good luck and see you at the next meetup.

Inconsistent NullIF Behaviour ?

NullIf can be a very handy function sometimes.  For those who have not used it, if the result of the two parameters are equal then null is returned.  So if we execute :

Code Snippet
  1. Select nullif(10,10) as Result1
  2. Select nullif(10,9)  as Result2

 

We get the result of

image

For some test data, I required a column that was randomly null.  Using ABS(CHECKSUM(NEWID())) to generate the random value and then modulus 10, to get a random value between 0 and 9 , then passing that into NULLIF as one parameter and 10 as the other, I should of got random values between 1 and 9 with a selection of NULL values.  Here’s the code :

Code Snippet
  1. select top(1000) ROW_NUMBER() over (order by (select null)) as ID,
  2.        nullif(ABS(checksum(newid()))%10,0)  as RandomNonZero
  3. from sys.columns a

And here's the result…

image

Interesting , does that mean that NULLIF is not working ?  Plainly it is sometimes, after all there are some NULL values. 

Much like in my previous post detailing the differences between ISNULL and COALESCE,  the NULLIF function is expanded by the engine into a case statement as shown below.

 

image

So,  its working but the value outputted is not the value tested.