Tuesday, September 10, 2013

CheckSum is pretty damn accurate

I am at a SSIS Masters course with Jorge Segarra and Brian Knight (Pragmatic Works). An attendee brought up an issue about checksum not being 100% accurate, which we all acknowledge; however, another attendee said it is only 99% accurate when checking for differences. I know it's not even close to 1% inaccurate so I wrote a quick script to attempt find an inaccurate checksum value via brute force. I ran the script below for 15 minutes, checking over a quarter of a billion random values in which I found 0 invalid matches. If someone wants to run this longer and change the bigint to a numeric, let me know how many attempts until you find a record.

Summary: checksum is pretty damn accurate (not 100%, but pretty close).

DECLARE @value1a VARCHAR(36)
DECLARE @value2a VARCHAR(36)
DECLARE @value1b VARCHAR(36)
DECLARE @value2b VARCHAR(36)

DECLARE @attempt bigint
SET @attempt = 0

SET @value1a = NEWID()
SET @value2a = NEWID()
SET @value1b = NEWID()
SET @value2b = NEWID()

WHILE checksum(@value1a, @value2a) <> checksum(@value1b, @value2b)
@attempt = @attempt + 1
SET @value1a = NEWID()
SET @value2a = NEWID()
SET @value1b = NEWID()
SET @value2b = NEWID()
IF @attempt%100000 = 0 PRINT @attempt

SELECT @value1a value1a, @value2a value2a, checksum(@value1a, @value2a) chk_a
SELECT @value1b value2a, @value2b value2b, checksum(@value1b, @value2b) chk_b

Thursday, January 17, 2013

Make a SQL Server Shortcut to Change Database Connections

In SSMS, to change databases most people do one of the following:
1. Click the Change Connection toolbar button
2. Right click anywhere in the query pane -> Connection -> Change Connection
3. Go to the query menu -> Connection -> Change Connection (Alt+Q, C, H)

The change connection menu button actually does have a shortcut key predefined (Alt+H); however this action has a lower priority than the (Alt+H) shortcut for the Help Menu. Thanks Microsoft! I'll show you how to enable this the Change Connection action using (Alt+G).

The work around is actually quite simple, but it is not very intuitive.
  • Right click anywhere on the toolbar and select Customize
  • Ignore (but don't close this yet) the Customize window that pops up
  • Right Click on the Change Connection toolbar button and select "Image & Text"
    • This enables the hotkey to be use inside the query window but you can not activate it yet because Help Menu shortcut (Alt + H) still has precedence.
  • Right Click on the Change Connection toolbar button again
  • Change the Name field from "C&hange Connection..." to "Chan&ge Connection..."
  • Close the Customize window that popped up earlier.
Now you can use the Alt + G keyboard shortcut to change connections.