Shane on June 29th, 2007

My latest debacle loading this massive table has to do with removing duplicates before I try adding a primary key to the table. I had to restart the load process a couple times before I finally settled into a method that worked well. (Running multilpe instances of DTExec on different subsets of the source data, [...]

Continue reading about Massive In-Place Delete

Shane on June 28th, 2007

Dear Shane, Next time trust me. SELECT INTO is always faster than UPDATE when you’re talking about updating the entire table. Even if the table has three NVARCHAR(MAX) fields and you’re only updating one 4 byte INT column. Rather than run an update for 1hr 6min before you decide to cancel said query and trigger [...]

Continue reading about BULK UPDATE

Shane on November 1st, 2006

So I need to import data from Excel into SQL Server. I whip up a new table, knock out a quick package, run the package, and look at the data. Looks fine, okay. Email the customer so they can look at the table. Two days later I get the email from the customer saying, “How [...]

Continue reading about What to do when Excel guesses wrong

Shane on April 12th, 2006

I always seem to get this wrong, so please excuse this note to myself. SELECT gpa, DENSE_RANK() OVER (PARTITION BY strm, class_nbr ORDER BY gpa DESC) class_rank

Continue reading about Analytic Functions

Shane on April 12th, 2006

Since the PeopleSoft table naming convention is to use as many underscores as possible, I’ve run into many snafus since the underscore is a special character in SQL. Clark (via Linda) showed us some ways around this. select count(*) from dba_tables where table_name like ‘%\_UM’ escape ‘\’ COUNT(*) ———- 113 1 row selected This query, [...]

Continue reading about Special SQL Characters

Shane on April 12th, 2006

Some food for thought when working with NULL values. select count(*) from ps_dwsa_prog_dtl COUNT(*) ———- 657662 1 row selected select count(*) from ps_dwsa_prog_dtl where acad_sub_plan =’13′ COUNT(*) ———- 13 1 row selected select count(*) from ps_dwsa_prog_dtl where acad_sub_plan ’13′ COUNT(*) ———- 148559 1 row selected select count(*) from ps_dwsa_prog_dtl where acad_sub_plan is null COUNT(*) ———- [...]

Continue reading about NULL values and NOT EQUALS operator

Shane on September 21st, 2005

If you work with SQL — especially with Oracle — and you don’t read Ask Tom, you are missing a goldmine of tips and tuning advice. In the words of my former colleague Andy, “That Tom guy’s the shit.” The best part of it is that Oracle pays him just to put his tips on [...]

Continue reading about SQL Tuning