Monday, December 16, 2013

Case-Sensitive Updates in a Case-Insensitive World

Assuming you're in a case-insensitive ("CI" in the collation name) column, this UPDATE statement:

UPDATE tablefoo
set foo = 'Z'
where foo = 'z'

will just update all values where foo = 'z' or 'Z'. SQL Server is not by default case-sensitive, be it at the server, database, or column level. Each of those levels can have their own collation set differently, and there are rules for determining how they are enforced.

For example, in the US, this is the default collation: SQL_Latin1_General_CP1_CI_AS, a case-insensitive, accent-sensitive collation.

So, given this data:
tablename: tablefoo
foo
z
Z
Z
z
Z
z
How can we update just the three records where foo = 'z' but not the 3 records where foo = 'Z'? Without ALTER'ing any objects to change their collation, obviously...

UPDATE tablefoo
set foo = 'Z'
where foo COLLATE SQL_Latin1_General_CP1_CS_AS = 'z'

And then, to make our UPDATE statement non-repeatable for safety, we should add one more line:
UPDATE tablefoo
set foo = 'Z'
where foo COLLATE SQL_Latin1_General_CP1_CS_AS = 'z'
and foo COLLATE SQL_Latin1_General_CP1_CS_AS <> 'Z'
More reading on collations:
http://technet.microsoft.com/en-us/library/ms180175.aspx


Thursday, December 12, 2013

It's Dangerous to ISNUMERIC, Take This Try_Convert

There's an issue with some characters in T-SQL when validating numeric values out of raw varchar fields, and it can be easily solved with a function new to SQL Server 2012.

Consider this sample setup, where we are stuck importing values from a varchar data source into an integer data destination, a common task in heterogeneous ETL activities:
create table tablefoo
(id int identity(1,1) not null primary key,
foo varchar(20) not null)
go
--add some valid integers
insert into tablefoo (foo) values
(123),
(456)

--add some dollar values
insert into tablefoo (foo) values
('$123'),
('$456')

--add some alphanumeric strings
insert into tablefoo (foo) values
('abc123'),
('def456')

--add scientific notation
insert into tablefoo (foo) values
('1e234')
go
select * from tablefoo 

/*
id foo
1 123
2 456
3 $123
4 $456
5 abc123
6 def456
7 1e234
*/
Let's try to validate only proper integer values in tablefoo.foo for insertion into a column with a data type bigint.
--this fails! why?
select 
      CASE   WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END
from tablefoo c
The error message is
Msg 8114, Level 16, State 5, Line 1 Error CONVERTing data type varchar to bigint.
Let's dig into the data case-by-case...
--this succeeds as designed, returning NULL for the alphanumeric value.
select 
      CASE   WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END
from tablefoo c
where foo = 'abc123'

--This fails because the dollar sign passes the ISNUMERIC check 
-- BUT is not valid for an int field 
select 
      CASE   WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END
from tablefoo c
where foo = '$123'

--This also fails because 1e234 is valid scientific notation 
-- BUT is not valid for an int field.
select 
      CASE   WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END
from tablefoo c
where foo = '1e234'
Our culprit was the curious behavior of certain characters (in this case, "$" and "e") which is valid for the ISNUMERIC built-in function, commonly used to detect numeric strings.

How to clean this up?
--this returns only integer values properly for the whole table
-- and is much prettier.
select 
 c.foo
, ISNUMERIC(c.foo) 
, TRY_CONVERT(bigint, c.foo )
from tablefoo c
Note the above values of ISNUMERIC and the error-less conversion of the TRY_CONVERT function. So not only is TRY_CONVERT downright sexy with its efficiency and compactness, it can help you avoid errors commonly encountered while using ISNUMERIC with characters like the dollar sign ($), decimal point (.), scientific notation (e) and comma (,).


Tuesday, December 03, 2013

Upcoming Special Events for the Baton Rouge SQL Server User Groups

I'm happy to announce a big slate of upcoming events for Baton Rouge User Groups and especially the SQL Server User Group in the next three months:

December 11: Holiday Party at On the Border - free for user group attendees, door prizes from sponsors! http://www.brssug.org/group-news/december13batonrougeusergroupsholidayparty


January 8 '14: Two speakers from Microsoft will present to the User Groups on all the new things happening with Azure. This is going to be big! http://www.brssug.org/group-news/january14batonrougeusergroupsmeeting


February 12 '14: The day of the user groups meeting, we'll have two national Business Intelligence speakers presenting a paid all-day training at the Louisiana Tech Park, then hanging around to present at the SQL Server User Group meeting that night!


Tim Mitchell’s Real World SSIS: A Survival Guide:https://www.eventbrite.com/e/real-world-ssis-a-survival-guide-tickets-9555728441


Bill Pearson's Practical Self-Service BI with PowerPivot for Excel:http://www.eventbrite.com/e/practical-self-service-bi-with-powerpivot-for-excel-tickets-9450327183


Please visit our website for complete details: www.brssug.org
Info on the Tech Park Location is here:www.brssug.org/where