Discovering Full Text Search

And so Code Camp 2011 comes to a close for another year. I can honestly say I had a great time and enjoyed presenting my topic: Discovering Full Text Search

Apologies for not being able to get through all the demos, I'm a bit disappointed about that as there were a couple I think were of great value. One in particular around the thesaurus as there were a couple of questions I think it would have answered quite well.

However, this then becomes a great opportunity to present the topic again at the Auckland SQL Users Group. There were quite a few people from Auckland who admitted to having not attended the users group before - it would be good to see some of you. We have free pizza and beer!

The goodness

Until then, you're welcome to view/download the slides to the presentation.
And grab the code for the demos - even the ones I skipped. You will need the AdventureWorks2008R2 database which you can download from http://msftdbprodsamples.codeplex.com/

If you did attend, thanks for coming and I hope you enjoyed it and managed to get something out of it.

Feedback

I'd love some feedback. I'm still fairly new to presenting so have a long way to go. What did you like, what didn't you like? Could you hear me OK, did I mumble? etc. Feel free to leave a comment or perhaps more ideally drop me a email (see my contact page above).

Shameless Plug

We've got SQL Saturday coming up. There's going to be fireworks! Literally. Its on the 5th of November. Put it in your diaries. More info will be available on the Auckland SQL website (http://www.aucklandsql.com/SQL-Saturday.aspx) when it becomes available.

If you did not hang around for Leo Millers excellent security presentation you missed a couple of things:

• a very good presentation with lots to think about
• and my little nugget (found the probable original): http://stuff.creativityhurts.net/post/9468362554/best-sql-injection-out-there-translation-for

Until next year...

We dont need no education!

Way back in 1979 Pink Floyd argued "we don't need no education!". But that was over 30 years ago (sheesh...just how old am I?!) and education is fast becoming a scarce commodity. In just under two weeks TechEd NZ 2011 kicks off.

But I'm not here to tell you about that. In just over two weeks a far more cost accessible set of training is available: Code Camp.

I've been to a couple of code camps and the first one I attended, I think, was 2008 at Whitireia in Porirua. And I'm going to be brutally honest: it was better than TechEd. I kid you not. For a SQL Server professional this was a seriously good event. The quality of presentations and knowledge was amazing I met some great people and best of all it was free!

"You! Yes you laddie!"

On the 28th of August we have Code Camp 2011 in Auckland.

Personally I'm interested in the SQL Server track, but we also have:

• Development Track - you know...all things .Net
• SharePoint Track - Im guessing...but I think this focuses on SharePoint
• Infastructure Track - shared with SQL track to cover AD, SSO, NLB and other awesome TLAs
• Business Track - a new one covering topics that don't sit comfortably numb elsewhere.

Check out the full agenda for all the sessions here: http://www.dot.net.nz/codecamp/Pages/AllSessions.aspx

But lets be honest with ourselves...we're only interested in the SQL Server track so what are we covering here?  Since the SQL track and the Infrastructure track are shared, the first two sessions are for Infrastructure topics, then we get into it:

• 11:30 - Full Text Search
• 12:30 - Lunch. Quite possibly the most important session.  Seriously it is - this is your chance to quiz everyone!
• 13:00 - Monitoring multiple SQL instances for $0.00.  I'm personally looking forward to this one
• 14:00 - Minimal Logging and Data Manoeuvring on Very Large Tables
• 15:00 - Afternoon Tea - Yummy!
• 15:30 - SQL Server: How Secure are you?
• 16:30 - Close and Prizes - Yet more "free stuff" (we do love free at Code Camp)

"If you dont eat your meat, you cant have any pudding. How can you have any pudding if you dont eat your meat?"

Going to TechEd NZ 2011? Then have your pudding too and come to Code Camp!
Not going to TechEd NZ 2011? I'll let you in on a secret: you can still have your pudding. It is free after all!

Come along to put another brick in your (education) wall. With a price like that, just why wouldn't you go?

Want some more info? Your two best sources are the Code Camp website (http://www.codecamp.co.nz/) and the #ccnz hash tag on twitter.

Teacher. Leave them kids alone.

And yes, I'm speaking! Please come say "Hello".

Heading towards an identity crisis?

My client recently struck an issue with an identity column value. Basically they ran out! This brought a very visible (read customer facing) interruption to one of their systems.

I'm sure many of you are in the situation where you have defined an identity column for your primary key. There's nothing particularly wrong with that but do you keep an eye on whether you're near to running out of values or not?

Simply run this script to find out:

with cteIdentity as (
    -- Basic identity info
    select 
    o.object_id                                  as TableID
    ,c.column_id                                 as ColumnID
    ,s.name + '.' + o.name                       as TableName
    ,ident_current(s.name + '.' + o.name)        as CurrentIdentity
    ,power(2.0, ((8 * c.max_length) - 1)) - 1    as MaxIdentity
    from
        sys.objects as o
        inner join sys.columns as c
            on o.object_id = c.object_id
        inner join sys.schemas as s
            on o.schema_id = s.schema_id
        where
            c.is_identity = 1    -- Table has an identity column
        and o.type = 'U'         -- User tables only
)
-- Calc the percentage used
select
    TableName
    ,CurrentIdentity
    ,(CurrentIdentity / MaxIdentity) * 100       as PercentageOfMax
from
    cteIdentity;

It would be very easy to wrap some alerts around the results if the "PercentageOfMax" value is above a predefined threshold level (e.g: 95%)

Naturally this does not fix the issue you might be screaming towards, but it gives you that important "heads up" that you need to start planning for a change soon.

Consider that to increase the data type to bigint from an int (which is what you would typically have to do) could involve:

• An outage to perform the table change
• The need to re-factor several (many?) procedures that reference the table
• Since its likely the identity is a primary key you're going to have to amend any tables that reference this one
• You'll need to release an application update to cater for the increase in data-type size

Trust me; its far easier to address this before it happens than during an identity crisis.

I like chocolate eggs...when is it Easter again?

A little while back I was working on a new data warehouse and was building the date dimension. The date dimension needed to cater for some seasonality adjustments and as such needed to know when specific holidays occurred. Most holidays are pretty easy to work out:

• Christmas is usually on the 25th of Dec
• New Year is round about the 1st of Jan

Some holidays are not so clear; Easter moves all over the place!  That makes populating a date dimension a little awkward, but at least once you've done it you don't have to worry about it again.

I found a couple of sites that listed when Easter had or would occur (date dimensions need to cater for dates in the past and potentially in the future).  If you Google for Easter dates you'll get them too.  But I really wanted a programmatic way to find the date for a given year.

With a little bit of hunting I found this: http://www.bbc.co.uk/dna/h2g2/A653267

Good on ya BBC!

A bit of jiggery-pokery later I give you (I hope they don't mind me regurgitating it):

create function [dbo].[CalcEaster](@Year int)
returns datetime2
with schemabinding
as
begin
 /*
  * This function originally came from http://www.bbc.co.uk/dna/h2g2/A653267 which read:
  *
  * Given the Year as a four digit number in the range 1700 - 2299
  * Calculate Day and Month of Easter Sunday
  *  Note 1: the algorithm has not been tested outside this range.
  *  Note 2: the \ operator performs integer division without remainder.
  *  Note 3: the date returned is the Gregorian Calendar date
  *          (the one we use now), even for dates in the 18th Century.
  * 
  * a = Year mod 19;
  * b = Year \ 100;
  * c = Year mod 100;
  * d = b \ 4;
  * e = b mod 4;
  * f = c \ 4;
  * g = c mod 4;
  * 
  * h = (b + 8)\25;
  * i = (b - h + 1)\3;
  * j = (19*a + b - d - i + 15) mod 30;
  * k = (32 + 2*e + 2*f - j - g) mod 7;
  * m = (a + 11*j + 22*k) \ 451;
  * n = j + k - 7*m + 114;
  * 
  * Month = n\31;
  * Day = (n mod 31) + 1;
  */
 declare @a  int
 declare @b  int
 declare @c  int
 declare @d  int
 declare @e  int
 declare @f  int
 declare @g  int
 declare @h  int
 declare @i  int
 declare @j  int
 declare @k  int
 declare @l  int
 declare @m  int
 declare @n  int

 select @a = (@Year % 19)
 select @b = (@Year / 100)
 select @c = (@Year % 100)
 select @d = ((@Year / 100) / 4)
 select @e = ((@Year / 100) % 4)
 select @f = ((@Year % 100) / 4)
 select @g = ((@Year % 100) % 4)

 select @h = (@b + 8) / 25
 select @i = (@b - @h +1 ) / 3
 select @j = ((19 * @a) + @b - @d - @i + 15) % 30
 select @k = (32 + (2 * @e) + (2 * @f) - @j - @g) % 7
 select @m = (@a + (11 * @j) + (22 * @k)) / 451
 select @n = (@j + @k) - (7 * @m) + 114

 /*
  * Return the values as a datetime
  */
 return convert(datetime2, convert(char(4), @Year) + '-' + right('0' + convert(varchar(2), (@n / 31)), 2) + '-' + right('0' + convert(varchar(2), (@n % 31) + 1), 2))
end

So when is Easter 2012?

Enjoy.