SQL Server Maximum Key Length
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…).
Divide by Zero
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
end as value
select coalesce( numerator / nullif( divisor, 0 ) , 0)
SQL Server Date Conversions from Excel
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.
cast([column name] as float)
-2 as datetime
) from [table]
Getting paid for the work you do is a wonderful thing!
It feels good to pay off debts!!
One More Shot
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.