4

I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors.

if object_id('my_proc') > 0 drop procedure my_proc
go
create procedure dbo.my_proc as Print 'This is not a dummy';

and the other which preserves permissions

if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
ALTER PROCEDURE dbo.my_proc as Print 'This is not a dummy';
    

I guess when the procedure exists and its hash code is the same as that of the new version, than there would be no need to drop and recreate or alter the procedure.

My problem is that HashBytes is limited to a maximum of 8000 bytes and I can't use it generally like

if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
if object_Id('my_proc') > 0
and
(
Select HashBytes('MD5',definition) MD5
from sys.sql_modules m
join sysobjects o on o.id = m.object_id
where o.name = 'my_proc'
) <> 0x9028A1B9D93AC7592EC939CCABF9D3DE
begin
    print 'definition has changed';
    EXEC ('ALTER PROCEDURE dbo.my_proc as Print ''This is not a dummy''');
end 

For procedures whose definition are longer than 4000 Characters. Any proposals to handle these cases in a similar way?

Edit:

It is not only that I want to avoid flushing of cached plans.

I also have to cope with different customers having different versions of a stored procedure where I only want to replace one of these variants by a newer version.

4
  • What do you mean by the last edit? This is unrelated to your question, unless this is what you really wanted to ask: "how to version procs at client site".
    – gbn
    Oct 31, 2011 at 13:58
  • I have to replace procedures with *= syntax by left join syntax and it is not granted that all customers have the same version due to customizing. I want to apply the fix to only those who have the version I modified
    – bernd_k
    Oct 31, 2011 at 14:06
  • 1
    This sounds messy, but why not hash the proc definition in groups of 8000 bytes, concatenate the results as a string, and then hash that? Oct 31, 2011 at 14:47
  • @Nick make it an answer
    – bernd_k
    Oct 31, 2011 at 14:52

3 Answers 3

3

We recently had this discussion at my workplace.

First, I want to commend you for doing the "right thing" by using HASHBYTES() over CHECKSUM() to detect changes. Microsoft specifically cautions against using CHECKSUM(@input) for this purpose as its collision rate is very high compared to that of HASHBYTES('SHA1', @input). One advantage CHECKSUM() does have, though, is that there is no (obvious) restriction on the size of its input.

Second, if you use HASHBYTES() I recommend using SHA1 as your hash algorithm. Of the available options SHA1 has the lowest collision rate, and speed is not a concern for your use case.

Finally, To use HASHBYTES() against inputs larger than 8000 bytes you'll have to:

  1. Split your input into 8000 byte chunks.
  2. Hash each chunk.
  3. Somehow combine the resulting hashes and hash them to get your final output.

    You can do this in one of two ways:

    1. Convert your hash outputs into strings, concatenate them, and hash the result.
    2. Stick all your hash outputs into a memory table and take their aggregate checksum using CHECKSUM_AGG().
  4. Encapsulate this work as a function that takes NVARCHAR(MAX) as its input.

All that said, it is all-around simpler to just compare the proc definitions directly using OBJECT_DEFINITION() as gbn suggested, or to simply push all definitions out everywhere as often as you like, as Mike suggested.

I wonder what kind of environment would significantly benefit from a process that deployed only changed procedures and used hashes to avoid copying around and comparing full definitions. You'd need to have a lot of procedures to keep in sync.

2
  • This can be done nicely using DDL triggers that can save the sp in chunks in a table and compute a hash on them (and even a hash on all hashes :-) ). But it's a bit of a "brute force attack" compared to GBN's "object_definition" solution. His solution just creates a new procedure all the time and either deletes the old one or the new one after comparing the content. A bit simpler to implement, I think.
    – Marian
    Oct 31, 2011 at 16:06
  • @Marian - Yeah, I agree just a direct comparison is simpler to implement, understand, and maintain. As I commented on Mike's answer, I wonder what it would take for this hashing approach to be worth the effort. Oct 31, 2011 at 16:26
6

Permissions shouldn't be assigned to the stored procedure

Your stored procedure should sit in a schema, permissions are on the schema (granted to roles). This way, new objects in that schema have permissions automatically. No single object should have permissions

So either way would be OK.

See these other DBA questions:

FYI, object_id is signed int so use "does not equal"

if object_id('my_proc') <> 0 drop procedure my_proc

Edit, nvarchar(max) is comparable directly

if object_Id('my_proc') <> 0
    drop proc ThenewProc
GO
CREATE PROC ThenewProc
...
GO

IF object_definition(object_id('my_proc')) <> 
         object_definition(object_id('ThenewProc')) <> 0
BEGIN
    drop procedure my_proc
    drop proc ThenewProc
END
GO
CREATE PROC my_proc
...
GO
4
  • I agree with that argument. But please see my last edit.
    – bernd_k
    Oct 31, 2011 at 13:55
  • @bernd_k : I answered what you asked. Versioning of procs is unrelated as such
    – gbn
    Oct 31, 2011 at 13:58
  • This makes sense, though I sometimes still do assign permissions at the role level (never to users if I can control that) rather than the schema. I like this answer as well. Saying the same thing - either approach works. Use a schema for perms or rebuild perms each time, no risk or harm in either.
    – Mike Walsh
    Oct 31, 2011 at 14:03
  • This is definitely better and more elegant than splitting in 8k char chunks and checking the hash of each chunk. Big thumb up! The other solution is just too "brute force attack" for this problem :-).
    – Marian
    Oct 31, 2011 at 16:01
2

I would say the best answer here is to just drop/recreate as often as you like - Only script out the permissions and run that. You can run the permissions script as many times as you like, it won't error.

Trying to come up with an effective, safe and reliable hashing methodology to only modify the affected procedures isn't really necessary when you jut drop and recreate and run your permission script.

You'll find a lot of shops that do deployments that way.

4
  • 1
    @Martin Smith: One hopes this is finalised before running once on production (out of hours after a backup with a tested rollback script) so your point should be moot...
    – gbn
    Oct 31, 2011 at 13:50
  • Agree with @gbn here. I assume you aren't attempting to deploy often throughout the middle of a workday and many other things we do after hours or during deploy windows can cause cached plans or data to be flushed.
    – Mike Walsh
    Oct 31, 2011 at 14:02
  • 1
    Honestly, this is the most straightforward way to do things. I wonder how large and/or complex your environment would have to be for proc versioning to pay off. Oct 31, 2011 at 15:39
  • 2
    When I worked at MySpace (about the biggest SQL shop around at the time) we just replaced the stored procedures without checking if they had changed. The hit to the plan cache was minimal enough to not worry about it.
    – mrdenny
    Oct 31, 2011 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.