NULL in SQL databases!!!!!!! What a pain!

I am very passionate about the whole issue surrounding NULL in databases. It is a simple yet exceedingly difficult item.

Let me pass along some ideas and one scenario that I use as guides that has help keep me out of trouble.

1) NULL is NEVER a value! It is the absence of value! Or as EF Codd originally stated: “missing information and inapplicable information”
See: http://en.wikipedia.org/wiki/Null_(SQL)
2) NULL should NEVER EVER be used as a flag in decision logic. Testing for NULL is fine with a narrow constraint that we are checking for NULL state.
3) Setting anything to NULL is not the same as “deleting information.”

Here is a scenario I use to illustrate a simple practical application of these ideas. Assume we have a simple order entry SQL application that has a shipping address form coupled to an address table. Something like:
Create table ShipAddr( CustName varchar(40) null, Street1 varchar(30) null, City varchar(30) null, State varchar(20) null, ZipCode varchar(9) null)

Here is the scenario:

1- Customer orders a product and customer service calls up the shipping address form to fill out the information.
2- The customer supplies all of the information but doesn’t have their Zip code. This field is not updated and the form is saved. The ZipCode field has the original “state” NULL.
3- The next day the customer calls back and supplies the value “35123” as their Zip code. The form is updated and saved. The ZipCode field now has the value “35123”
4- Order fulfillment department runs a check and determines that “35123” is an invalid Zip. The ship address form is updated by deleting all of the values in this field. The form is saved.
IMPORTANT NOTE: Now the ZipCode field has the value “”, or in other words a zero length string!!! Not NULL!

Why not just set it back to NULL? The most important reason is that doing this completely loses the “transitional state” of the field in this record! If you look at classic 3VL and tuple calculus set operations it negates the ability to segregate subsets of the data based on if the value in the field has ever been “touched.” If the field is set back to NULL as a “value” the segregation of records that have never had an address value is lost. How do we also determine that a field in the general sense may actually have a correct value of “”?

Ok at this point you are probably asking yourself … who cares?
Having seen this problem repeated over and over in many SQL implementations I would suggest that this issue is important for several reasons:
1)Maintainability, confusion around NULLs is costly
2) Accuracy, loss of state or ability to validate because of NULL “values” can be expensive
3) Interoperability, NULLs used as values in one table or application may not have the same meaning in another application.

Regards,

Dave W

Finding foreign key references to a table.

At some point you'll find yourself wanting to modify or change the way a table is structured. If your database uses FK (foreign key) constraints it is sometime a pain to find all the FKs referencing the table. Microsoft’s management tool will list dependant objects but drilling down to which tables and columns reference which column on the table you want to modify can take some time.

Here is a little procedure to list out FKs on a target table:
--
-- Find all foreign keys references to a particualr table
-- D Winters
--
declare @targetTab sysname

set @targetTab = 'employee'

select t.name as TableWithForeignKey, object_name(constraint_object_id) as ForeignKeyConstraint,
c.name as ForeignKeyColumn, fk.constraint_column_id as FK_PartNo ,
COL_NAME(referenced_object_id , referenced_column_id ) as References_Column
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id
and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = @targetTab)
order by TableWithForeignKey, FK_PartNo

Old error checking still in use. @@ERROR

SQL 2000 error checking with @@ERROR and @@ROWCOUNT is still in use in many applications. Even with the advent of SQL 2005's TRY-CATCH feature you may still find this in old T-SQL or it might be the best fit for some applications. BUT make sure you understand how this works and fix those older legacy TSQL constructs that may have problems.

I found this pattern in SQL Stored Procedures for error checking at a client.

Example-1 SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
select @errnum = @@error
if @errnum != 0
Begin
rollback tran
return @@error
End

Does this work the way the original author expected? NO.
The intent is rollback an open transaction and report the error number encountered to the the caller of the procedure. What will always(99%) get reported is a SUCCESS value of Zero (0).
Why?
Because @@error ONLY maintains the condition of the “Last” SQL statement executed. In this case the ROLLBACK statement. This is also a problem when you introduce constructs that have IF conditions.

Example-2 SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
if @@rowcount = 0
Begin
return @@error
End

Again the @@rowcount check resets the error condition and @@error returns 0.
Here are some rewrites of the above examples:

Example-1 REWRITE SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
select @errnum = @@error
if @errnum != 0
Begin
If @@trancount > 0 rollback tran –-a good idea
return @errnum
End

And the proper way to check for row counts and error codes. You must grab both at the same time or you’ll get the wrong value in the 2nd variable.

Example-2 REWRITE SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
Select @ErrorVar = @@error, @RowCountVar = @@rowcount
if @RowCountVar = 0
Begin
return @ErrorVar
End

In SQL 2005 and above you should consider using TRY-CATCH for all Insert-Update-Delete SQL operations. These constructs are still popular and appropriate in some cases.
Cheers, Dave Winters

SQL Azure Application locks

I'm working with a complex database and seeing how easy it is to port the application to SQL Azure. My initial impressions of the platform are positive. But complex and "interesting" SQL Server applications that leverage advanced features require some close scruity and care when operating in the Cloud world.

Microsoft makes it very clear that connections to the Azure database are not 100% continous. And this can have a subtle effect on transaction and locking features of traditional SQL 2008 applications.

Take for example the use of the SQL Server application lock facility. Look at this sequence:

----begin----
exec sp_getapplock @Resource='ALockTest',@LockMode='Exclusive',@LockOwner ='session'

SELECT APPLOCK_MODE('public', 'ALockTest', 'session');

exec sp_releaseapplock 'ALockTest', 'session'
----end----

If done "quickly" this will work as expected. BUT ... SQL Azure doesn't guarantee that the session is continous.

Try the above sequence again and put a long delay(I used 5 minutes) between the 1st step( sp_getapplock ) and the last two steps.

You'll probably get the following message from the sp_releaseapplock procedure.

Msg 1223, Level 16, State 1, Procedure xp_userlock, Line 1
Cannot release the application lock (Database Principal: 'public', Resource: 'ALockTest') because it is not currently held.


As you can guess this is not what is expected.
There are other ways of implementing application locks. More on that later.

So if you are moving to SQL Azure you must take issues like this into consideration for the Cloud environment that is Azure.

Dave W

SQL Server x64 memory performance

I have a client who sells medical software and some of their clients are complaining about performance on SQL Server x64. A little research showed that the permissions for the account running the server were incorrect.

Looking at Microsoft's support site there are two links you should check out.
The first:
http://support.microsoft.com/kb/918483
Describes the problem in detail and permission and setting changes to help.

The second is a link the to hotfix that is available.
http://support.microsoft.com/kb/970070

Don't forget about enabling trace flag 845.

Dave

The Seldon Vault Group

Group page for forums and files.
http://groups.google.com/group/theseldonvault

Stored procedure with memory

Sometimes its helpful if a stored procedure can remember the last time it was run.
This is useful for processing data that is time stamped and has been added to a table since the last time the proc was run.

Most of the time keeping track of last runs is done by using a table. This proc demonstrates keeping information in the extended properties of the stored procedure.
Orginal source code is at: http://groups.google.com/group/theseldonvault

Dave

--


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.usp_RememberWhen') IS NOT NULL
DROP PROCEDURE dbo.usp_RememberWhen
go
-- ==========================================================================================
-- Author: Dave Winters
-- Create date: November 2009
-- URL: http://theseldonvault.blogspot.com/
-- Description: Demonstrate stored procedure keeping
-- the time it was last run without using a user table.
-- This technique could also be used to keep track of past parameters.
-- Performance of this technique has not been checked.
-- ==========================================================================================
CREATE PROCEDURE dbo.usp_RememberWhen
AS
BEGIN

SET NOCOUNT ON;
DECLARE @tdate DATETIME
DECLARE @lastdate DATETIME

SET @tdate = GETDATE()

IF NOT EXISTS(SELECT * FROM ::fn_listextendedproperty('last_run', 'schema', 'dbo','Procedure', 'usp_RememberWhen', NULL, NULL) )
EXEC sys.sp_addextendedproperty @name=N'last_run', @value=@tdate ,
@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'PROCEDURE',@level1name=N'usp_RememberWhen'
ELSE
BEGIN
SELECT @lastdate=CONVERT(DATETIME,[value]) FROM ::fn_listextendedproperty('last_run', 'schema', 'dbo','Procedure', 'usp_RememberWhen', NULL, NULL)
EXEC sys.sp_updateextendedproperty @name=N'last_run', @value=@tdate,
@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'PROCEDURE',@level1name=N'usp_RememberWhen'
END
--
-- Print out the value from last run
SELECT @lastdate AS "I RAN WHEN"
END
GO

--
-- Execise the solution
--
SELECT * FROM fn_listextendedproperty('last_run', 'schema', 'dbo', 'PROCEDURE', 'usp_RememberWhen', NULL, NULL)
EXEC usp_RememberWhen
SELECT * FROM fn_listextendedproperty('last_run', 'schema', 'dbo', 'PROCEDURE', 'usp_RememberWhen', NULL, NULL)



--

Welcome to Terminus

Hey - I just picked something I could remember for a name. For those of you SciFi fans the name is obvious.

Dave