Power of SQL: Update Replace()

Sometimes you realize after the fact that there's an issue with the way your data is being stored. In this case, we had a field that was storing names. Originally intended to be able to show an admin / show on reports the full person's name without concatenating data together, here is what was being built.
"firstname middlename lastname"
Unfortunately, when the middlename was not filled out, it contained two spaces between the first and last names. In addition to fixing the way that the the data is being updated, there's an easy fix for existing data.

update users
set fullname = replace(fullname,'  ',' ')
where fullname like '%  %'

For clarity, this finds and replaces two spaces with one space. Its easy to adapt to your specific situation and yes, its really that easy.

Power of SQL: Update Join Tables

This is one of the most common update queries I do when dealing with multiple tables that needs data updated.

update o
set o.status = 'complete'
from orders o
join order_detail od
on o.id = od.order_id
where ... (criteria here)

I often see code that will do a select and then update the subsequent table where id in (list_of_values). Remember that (almost?) anytime you see an IN clause, it can be replaced with a more efficient join statement.

Power of SQL: Find and Delete Duplicates

Most likely we have all had the situation, or will most likely encounter it, where we have duplicate data that needs to be removed from a system. For me this was experienced when moving data from a flat file / legacy system into SQL. This is the solution I came up with after getting it into SQL. Perhaps you have a more elegant way in dealing with this, if so, feel free to comment. The execution of this entire process against ~200,000 records took less than 15 seconds on my particular database. The creation of the index seems to be helpful if you are going to run several queries against the data while preparing the statement.

First, the background:
I have a table of notes. Those zero or more of those notes are related to a foreign key. If there is more than one note that is identical, for the same foreign key, it needs to be removed.

It is extremely difficult to compare a text field to another text field. With up to 2GB in length, it can be a daunting task. For my situation, most of the notes were less than a length of 8000, only a small number were longer. If they were longer, we could safely assume if they were not different for the first 8000 chars, they wouldn't be. You may choose to add additional constraints for your situation.

Basic Table layout:

noteId (identity field)
foreignId (integer / foreign key to other data)
noteText (text field)
... other unimportant data

ALTER Statements Before Starting: This adds a checksum and length based on the note. FYI - I did find duplicate checksums with different lengths. If you need to be precise, I suggest using the binary checksum function which is more sensitive, but understand, a checksum CAN have a collision. That means you can have the same checksum for different data.

ALTER TABLE notes ADD len_notes AS len(CAST(notetext AS Varchar(8000)))
ALTER TABLE notes ADD cs_notes AS checksum(CAST(notetext AS Varchar(8000)))
CREATE INDEX temp_index ON notes (foreignId,cs_notes,len_notes)

Now we have just added a column that represents the length of a note and the checksum of a note. If your data is changing, new records will contain NULL values and updated records will NOT recalculate the checksum, so keep that in mind. You may want to put criteria on your select statement that will only update the non-null values if your data changes, or the data that has a length <>

Select and Review part of your data that will be deleted:

SELECT top 100 n2.noteId, n2.cs_notes, n2.len_notes, n2.notetext
FROM notes n2
WHERE noteId in (
SELECT n2.noteId
FROM notes n1
JOIN notes n2
ON (n1.foreignId = n2.foreignId
AND n1.cs_notes = n2.cs_notes
AND n1.len_notes = n2.len_notes)
GROUP BY n2.noteId
HAVING n2.noteId > min(n1.noteId)
) ORDER BY n2.noteId

Delete your duplicates: To be clear here, this will preserve the lowest noteId and delete those with higher ids where the checksum AND length AND foreignId match.

DELETE from notes where note_id in
(
SELECT n2.noteId
FROM notes n1
JOIN notes n2
ON (n1.foreignId = n2.foreignId
AND n1.cs_notes = n2.cs_notes
AND n1.len_notes = n2.len_notes)
GROUP BY n2.noteId
HAVING n2.noteId > min(n1.noteId)
)

Put the table back the way it was:

DROP INDEX notes.temp_index
ALTER TABLE notes DROP column [len_notes]
ALTER TABLE notes DROP column [cs_notes]

I hope this helped.

Power of SQL: Indexing and sorting

Coldfusion Muse recently made a couple of posts about clustered indexes on SQL Server. I was commenting off the grid to the Muse that the timely post helped me in solving a data import issue.

To protect the innocent, and make things less prone to narcoleptic episodes, I had the following scenario:
- An Access Database with Categories and Products.
- The First product listed in a category was a "special" product. Problem: There wasn't any information that would indicate that product was the first one.

Example Data:

category_id	product_id	product_name	...
1	2	Red Jumbo Ball	...
1	1	Green Jumbo Ball	...
1	3	Blue Jumbo Ball	...
2	4	Purple People Eater	...
2	6	Yellow Submarine	...
2	5	White Christmas	...

Once imported, I knew the data was at the whim of the database which meant that I needed to "preserve" the order prior to importing the data. I added another column "autonumber" on my products table in Access.

Once imported, I could set the clustered index to the "autoNum" column in SQL and know the sort order was correct! Of course, it never hurts to add an order by to your select statement. There are many factors that can affect sorting, clustered indexes are just one factor.

Power of SQL: Insert from Select

Continuing the discussion of the Power of SQL. I came a situation where I wanted to move data from one table to another (without using DTS). How do you get the data moved over with just ONE SQL statement and no temp tables or complicated programming?

Consider the following example:

INSERT into foo2
(col, col2,col3)
select col,col2,col3
from foo1
where destination='foo2'

I hope that helps!

Power of SQL

Update: This is a part of a series now called "Power of SQL".

As a former developer at an elite government organization, I had the opportunity to leverage some of this SQL functionality. I had the opportunity to work on a project where they had a text file dump of Driver's license information. They also had a file dump (Select * from all_employees_of_all_employers_in_the_state). These statistics could show that a certain age, sex, ethnicity or even height of a person could help determine the wage of a person for a certain industry. If there was a similar correlation that short people get paid less than tall people, in a similar way that women get paid less than men, that could be a huge discovery to the HGH Industry - but I digress.

This program was to tie some "statistical" driver's license information collected by the state DMV to the wage information already collected under existing labor laws by NWD-DOL. After the program ties this information together OIT was then supposed to make sure the program would remove the "identifying" information (the coveted SSN), and then pass it along to the appropriate department (LMI). The program was inefficient and needed to have it optimized.

I opened the code up and like many programs I worked on, I was not terribly shocked by what I saw. Here is the pseudo-code:

Select * from wage_info Into wages
Select * from dmv_info into dmv
BEGIN LOOP dmv
-- The dmv clearly shows that SSNs are not unique and are recycled.
IF dmv.deceased = 0
BEGIN LOOP wages
IF wages.ssn = dmv.ssn
insert into dmv_wage_match
/IF
END LOOP
/IF
END LOOP
Select * from dmv_wage_match into obfuscate
Set id = 0
BEGIN LOOP obfuscate
set id = id+1
update dmv_wage_match
set ssn = #id#
where ssn = #obfuscate.ssn#
END LOOP

So let me break this down for you. If there are 1,200,000 DMV records and 900,000 wage records, with a 750,000 of them that match ... well, you get the idea, the program was bloated.

Here's what I updated the code to:

INSERT INTO dmv_wage_match
select d.sex, d.height, d.weight, d.eye, d.hair, w.amount, w.industry_code ...
from dmv_info d
join wage_info w
on d.ssn = w.ssn
where d.deceased = 0

In this case, dmv_wage_match had an identity field that served to obfuscate the actual SSN. This also elegantly avoids the tedious insert / update that was being done in the previous version. This version ran in a few seconds as compared to the previous version that took almost an hour! Now that's an improvement.

This is a VERY simple example, but I continue to be amazed by the power of SQL. The key in my opinion though is knowing what SQL can do and then asking the right question, for example, "How can SQL do some of the heavy lifting for me?".

I hope this helps give some insight into those who are still using a spreadsheet or *tisk tisk* text files and then trying to do something meaningful with them. Yes, they hold data, but so does the phonebook. If you're not convinced yet, tell me, by glancing through the phonebook, what are all of your neighbor's names and phone numbers who share your street name? Please post back how long that took you, I'm sure the readers would be interested in knowing.