January 7, 2010
Twitter #sqlhelp WORKS!
I had a problem, in which our corporate data folks wanted a table of ours to have certain criteria. We sell marine electronics, and some of it is serialized, some not. There is a program in our packing department that records in our SQL Server the products that get packed, and what type of boxes they’re packed in. Corporate is working on a project to take the information in that table, and use it to do automatic Completions in our JDE One World ERP.
So, I am the SQL DBA/Developer at our location, and I was told Monday that we had duplicate Serial Numbers in our data, which were found during dts package testing. Now, our unserialized products get things written into the serial number column such as ’4 Pack with NO SN’ while our serialized products get an 11 digit serial number. Corporate has decided that their DTS package will ignore the ’4 Pack with NO SN’, bu the serial numbers cannot be duplicated.
I fought with this thing. I wrote a function, which I attached to a CHECK Constraint, and that didn’t work at all. I spent a couple hours today trying to get it to work, and finally at lunch time I put out a #sqlhelp call on Twitter. I described my problem as best I could, even posted my function, 140 characters at a time. I was stressed, I missed a conference call in the middle of it, but you know what, people absolutely rallied to help me!
The first response was from @sqlrunner, who, in the end solved the problem, but while we were talking it out, I got input, help and even calming words from @markvsql, (who had a great line that went, “with enough chimpanzees, anything is possible. ” at which point I knew I was in good company ), @mrdenny, who introduced me to the concept of unique filtered indexes, which, while it really didn’t apply to the problem at hand, will be tucked away in my SQL toolbox for future use, @MladenPrajdic, who told me the database design was wrong, which I agree with (not my table, can’t help it , the C# people made it!), and also calmed me down when he saw I was falling apart with the stress of trying to keep up with several things at once. There were even people who actually wrote and/or posted blog posts *WHILE I WAS WORKING ON IT!* @onpnt pointed me to his http://www.sonetring.net/fileshare/trigg.txt, @bluedog67 pointed me to http://www.bluedog67.com/blog/post/Simple-Indexed-View-To-Constrain-Data.aspx, @markvsql gave me his http://markvsql.com/index.php/2009/05/enforcing-uniqueness-on-a-nullable-column/, and @billinkc wrote and posted http://billfellows.blogspot.com/2010/01/instead-of.html. Wild!
In the middle of it all, I emailed my stuff to @sqlrunner, who actually came back with a trigger that solved my problem. Can’t thank Josef enough. 13 emails back and forth to refine and define the problem then the solution, and just like that it was done. He told me, he has 16 yrs doing it, no problem. I think, take those years, and those of everyone else that helped, probably close to a century of experience in SQL all out there, willing to help with nothing in return but heartfelt thanks. The Twitter SQL Community is the BEST!
The solution? Like so many things, once presented, it becomes self-evident. It’s the mark of greatness that one can pull it out of their head, though, and not have to ask for it.
The table looks like thus:
CREATE TABLE [dbo].[Productiontest](
[WO] [nvarchar](50) NOT NULL,
[Assy] [nvarchar](50) NOT NULL,
[BoxNum] [nvarchar](50) NOT NULL,
[SerialNum] [nvarchar](50) NOT NULL,
[Date] [datetime] NOT NULL,
[HQ] [bit] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Productiontest] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Some of the data like thus:
843726, 407420-1M, 45Pack100107133354, 91202030199, 2010-01-07 13:33:57.000, (null), 63635
843726, 406540-1, 45Pack90817134501, 4 Pack with NO SN, 2010-01-07 13: 45:16.000, (null), 63639
The solution, in all it’s glory, looks like thus:
ALTER TRIGGER [dbo].[DisallowDupeNum] on [dbo].[Productiontest]
SET NOCOUNT ON
if exists (select 1 from inserted where serialnum like ‘%Pack with NO SN’ and isnumeric(serialnum)=0)
if not exists (select 1
where exists (select 1