Friday, June 29th, 2007
Massive In-Place Delete
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, [...]
No Comments » - Posted in SQL Server, SQL Tricks by Shane
Thursday, June 28th, 2007
BULK UPDATE
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 a 2hr [...]
No Comments » - Posted in SQL Server, SQL Tricks by Shane
Monday, February 12th, 2007
Checksum Code Generator
Also on the subject of checksum testing, this query will generate a checksum query for a given table. Use this query to generate the checksum for each table, then customize the sum() clauses if you did any transformations on your data.
select case a.xtype
when 167 then ‘SUM(LEN(’+a.name+’)) as sum_len_’+a.name+’,’
when 175 then ‘SUM(LEN(’+a.name+’)) as sum_len_’+a.name+’,’
when 231 then [...]
No Comments » - Posted in Other by Shane
Wednesday, January 31st, 2007
Extracting Metadata From SQL Server
select upper(b.name) as table_name, upper(a.name) as column_name,
a.colid, a.length, a.xprec, a.xscale, a.xtype,(CASE WHEN a.xtype=167 then ‘varchar’
WHEN a.xtype=175 then ‘char’
WHEN a.xtype=61 then ‘datetime’
WHEN a.xtype=106 then ‘decimal’
END) as col_type
from syscolumns a,
[...]
1 Comment » - Posted in Other by Shane
Wednesday, January 31st, 2007
SQL Server Failed Jobs
Here is a useful query for getting a list of failed jobs from SQL
Server. If you are responsible for tracking and logging job failures, this administrative work can be overwhelming in a busy week. This query will list all jobs that
failed during a given time period. It uses a function that I created,
convert_run_datetime.
<code>select a.name, [...]