DH Unlimited
Portfolio Blog Login

DH Unlimited is comprised solely of me, Dave. I've been doing freelance web development since 2010, mainly for the purpose of expanding my technical skill set.

Site Updated

SQL Server Maximum Key Length

Tuesday, December 11, 2012 11:18 am

Warning! The maximum key length is 900 bytes. The index ‘PK_BATCH_ORACLE_SHIPMENTS’ has maximum length of 919 bytes. For some combination of large values, the insert/update operation will fail.

This message bothered me until I found a way around it (kind of sorta).  Instead of using all 360 characters of a particular field to use as a key I chose to use only 320 (it’s a customer name).  I used a calculated column with keyword PERSISTED.

So…instead of using  

[customer] [nvarchar](360) not null

as my primary key I use part of it instead 

[customer_truncated] as left ([customer], 330) persisted

I hope this helps anyone interested in overcoming this issue (although I hope you can find better columns to build a primary key…).

Tag(s): SQL Server  Oracle  Work  Windows 

Divide by Zero

Monday, December 3, 2012 10:37 am

Instead of using a CASE statement inside a SELECT statement in SQL Server, I have found a better, cleaner method is to implement NULLIF which takes two parameters and compares them.  If they are equal then NULL is returned.   This forces the divisor to become NULL instead of zero.  And dividing by NULL makes the entire equation NULL.

For my project this morning I need to return 0 instead of NULL so I will also implement a COALESCE function which compares the output. 

So rather than use this logic below:

when isNull(divisor, 0) = 0
then 0
else numerator/divisor
end as value

I use: 

select coalesce( numerator / nullif( divisor, 0 ) , 0)

Tag(s): SQL Server 

SQL Server Date Conversions from Excel

Thursday, November 22, 2012 11:44 am

Going from Excel data to SQL Server data shouldn’t be difficult.  A common problem when doing this conversion is how to go from an Excel date like ’6/4/2011 11:34’ to a SQL Server date that is identical, but with a datetime data type.  The first thing to do in SQL Server is to convert the data from NVARCHAR to Float.  From there convert the float to datetime.  It really is almost too easy.

select cast(
	    	cast([column name] as float)
	    	-2 as datetime
  from [table]

Tag(s): Oracle  Windows  SQL Server 

Pay Day

Thursday, November 22, 2012 11:22 am

Getting paid for the work you do is a wonderful thing! 

It feels good to pay off debts!!

Tag(s): Life 

One More Shot

Friday, November 9, 2012 11:55 am

Well, that’s the title of the latest Rolling Stones’ single. Sounds like Don’t Stop, Street Fightin’ Man, and probably another 10 of their songs mixed together. Very disappointing. But what can you expect? I mean, they’re about 80 years old.

Tag(s): Music 
Back to the Top