Post reply

Integer left and right shift

  • February 14, 2012 at 11:33 pm

    #250749

    Comments posted to this topic are about the item Integer left and right shift

  • February 15, 2012 at 3:52 am

    #1447233

    Not sure that I'll ever have to use your functions (I hope not!) but I was intrigued by your code to verify the existence of the function before creating (in fact altering) it.

    There's one occurrence of what I would selfishly call 'standard code' (although I suspect it was left in the script by accident).

    IF OBJECT_ID('dbo.shiftRight0', 'IF') IS NOT NULL

    DROP FUNCTION [dbo].[shiftRight0]

    The other examples are of the following format.

    IF OBJECT_ID('dbo.shiftRight', 'IF') IS NULL

    EXEC('CREATE FUNCTION [dbo].[shiftRight]() RETURNS TABLE AS RETURN SELECT foo = 1')

    GO

    ALTER FUNCTION [dbo].[shiftRight] (

    I can see what's going on, but I'd like to know why. At a guess, I'd say that by altering rather than drop / creating, you conserve permissions. Or is there another reason?

    Thanks,

    David McKinney.

  • February 15, 2012 at 12:20 pm

    #1447486

    David McKinney (2/15/2012)

    Not sure that I'll ever have to use your functions (I hope not!)

    That's ok, I'm just testing "Write For Us" with this script. I can't imagine anyone needing a bit shift at the db level, but if it happens, this implementation is better than a naive POWER(x, s).

    David McKinney (2/15/2012)

    There's one occurrence of what I would selfishly call 'standard code' (although I suspect it was left in the script by accident).

    IF OBJECT_ID('dbo.shiftRight0', 'IF') IS NOT NULL

    DROP FUNCTION [dbo].[shiftRight0]

    The reason there's a DROP FUNCTION for shiftRight0 is the WITH SCHEMABINDING statement. shiftRight0 uses shiftRight in its implementation and schema binding will not allow an ALTER on shiftRight without first dropping shiftRight0.

    Schema binding can be a bit of pain sometimes, but it's useful for detecting dependencies and making sure nothing breaks when you modify something.

    David McKinney (2/15/2012)

    I can see what's going on, but I'd like to know why. At a guess, I'd say that by altering rather than drop / creating, you conserve permissions. Or is there another reason?

    Except preserving permissions and other meta-data, ALTER has the advantage that it's atomic. This makes patching a live application painless, when you need to apply small changes to the implementation of an already deployed proc/function. No locking is required and you can perform the ALTER while the proc/function is being used.

  • February 16, 2012 at 12:42 am

    #1447684

    Thanks Ioannis,

    I think you should test 'write for us' some more!

    Looking forward to a first article.

  • May 11, 2016 at 7:09 am

    #1878191

    Thanks for the script.

  • October 23, 2018 at 9:34 am

    #2010795

    Thanks for this.  Worked perfectly for some skunkworks i was working on.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply