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)
BEGIN
   SET
@attempt = @attempt + 1
  
SET @value1a = NEWID()
  
SET @value2a = NEWID()
  
SET @value1b = NEWID()
  
SET @value2b = NEWID()
  
IF @attempt%100000 = 0 PRINT @attempt
END

SELECT
@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.

Friday, December 21, 2012

Converting from Varchar with Nonbreaking Spaces to Datetime

Converting varchar values to datetime with nonbreaking spaces
I was tasked with importing a csv from into SQL Server. The CSV included 40 date fields which included values that could not be converted to datetime (eg. 12-Dec, 2011, and 23 Jan 2012). I’ll explain why 23 Jan 2012 could not be converted later on. I wanted to do this quickly so I used the SSMS import wizard, included headers and imported the date values into a varchar column.
After converting the values I started looking into which values could not be converted using the following query:

SELECT *

FROM myTable
WHERE ISDATE(myDateField1) = 0
 
I repeated this for all of the fields until I resolved all issues. The first issue I ran into was:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
I isolated this down to values that included 23 Jan 2012. I could not see at first why this was causing a conversion failure. After further research I found that the first space (between 23 and Jan) was actually a nonbreaking space which used ascii code 160 instead of an ASCII space which is ascii code 32. To resolve this I used the following replace command before converting:
SELECT CONVERT(datetime,REPLACE(myTable.myDateField1,char(160),' ')) as myDateField1
FROM myTable
WHERE ISDATE(myDateField1) = 0
 
The other issues were resolved by applying a case statement to myTable.myDateFields, evaluating the length of the value then appending the appropriate values to make the value submitted a valid date. Eg. if the length was <5 it would apply len(myDateField1) < 5 then '1-1-'+myDateField1.

Next I needed to apply the changes to all of the date fields. Since all of the date fields had the same issues as the date field I evaluated I simply apply the same convert/replace/case satement to every date field.

I created a quick recording of the key commands I needed to repeat and ran the following macro 40 times. After that I tested the script and was done!

Here is the Macro I used:

In the macro edit screen, the Prompt User “Times to Repeat” asks me how many times I want to repeat/playback a recording durring runtime and loads it into a variable named NumRepeats. I set the playback to run at 1500% the actual speed of the recording.

Thursday, August 11, 2011

Search Columns, Stored Procedures, or Tables using Dynamic SQL

I wanted to share the script I created to help Analysts and DBA's easily identify where a particular column, table, or keyword is used within a particular instance.


DECLARE @Dynamic_SQL VARCHAR(MAX)
DECLARE @SSQL VARCHAR(MAX)
DECLARE @DB_NAME VARCHAR(256)
DECLARE @SearchParam VARCHAR(256)
SET @SearchParam = 'ENTER KEYWORD HERE'

--REMOVE COMMENT FROM ONE OBJECT YOU WANT TO SEARCH
--Table Name Search
--set @Dynamic_SQL = 'select ''[dbname]'' database_name, name, null [col] from [dbname].dbo.sysobjects where xtype=''u'' and name like ''%'+@SearchParam+'%'''

--Stored Procedure Dependency Search
--set @Dynamic_SQL = 'use [dbname];select ''[dbname]'' database_name, Name, null [col] FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'+@SearchParam+'%'''

--Column Name Search
--set @Dynamic_SQL = 'select ''[dbname]'' database_name, so.name, sc.name [col] from [dbname].dbo.sysobjects so inner join [dbname].dbo.syscolumns sc on so.id = sc.id where so.xtype=''u'' and so.name like ''%%'' and sc.name like ''%'+@SearchParam+'%'''

CREATE TABLE #Results (dbname VARCHAR(256), [name] VARCHAR(MAX), col VARCHAR(MAX))

--GET DB LIST
SELECT [name]
INTO #DBNAME
FROM MASTER.dbo.sysdatabases
WHERE [name] NOT IN('master','tempdb','model','msdb')

DECLARE Cur_DB CURSOR FOR SELECT [name] FROM #DBNAME
OPEN Cur_DB
FETCH Next FROM Cur_DB INTO @DB_NAME

WHILE @@FETCH_STATUS = 0 BEGIN
   SET
@SSQL = REPLACE(@Dynamic_SQL,'dbname',@DB_Name)
  
--print @SSQL
  
BEGIN TRY
      
INSERT INTO #Results (dbname, [name], [col])
      
EXEC(@SSQL)
  
END TRY
  
BEGIN CATCH
      
IF @@TRANCOUNT > 0
  
ROLLBACK
   END
CATCH

  
FETCH Next FROM Cur_DB INTO @DB_NAME

END
CLOSE
Cur_DB
DEALLOCATE Cur_DB
DROP TABLE #DBNAME

SELECT dbname, [name], col FROM #Results
ORDER BY dbname, [name], col

DROP TABLE #Results

Thursday, July 7, 2011

Creating a simple T-SQL query

For people starting to learn how to write T-SQL queries the first thing you need to know is how to perform a simple query. A query is a way to get information back from a table in a database.

A table should represent a single entity, for example you may have a person table or a product table. We will focus on the person table for this example. A person may have multiple attributes (first name, last name, birth date, sex). These attributes are defined as columns/fields in a table (similar to a row header in a spreadsheet).



The first part of the query is the select statement. You will use the select statement to define what information you want to see pulled from the table. Each field needs to be separated by a comma. If we are only interested in seeing the first name and last name we would write the following.

select first_name, last_name


The statement above will fail to execute because the query is not complete. We have not defined where we wanted to get this information from. For this example table is named "person". In order to declare where we want to get the data from, we use the "from" statement after the list of columns as follows:

select first_name, last_name
from person


That is pretty much the simplest a query will get.