Browsing the blog archives for October, 2009
I’ve been a SQL Practitioner since 1996 when 6.5 was the hot item. Along the way I’ve learned a bunch of stuff about SQL Server. Without a doubt, the more I learn, the more I know what I don’t know. Being humbled on a regular basis is probably a good thing, but sometimes it’s just [...]
Have you ever noticed how long it takes to start up SQL Server Management Studio (SSMS)? I always assumed it was because it is the biggest and most powerful tool in my toy box and it deserves the load time. You can SIGNIFICANTLY reduce the load time by avoiding Microsoft’s marketing message at startup, the [...]
Today’s post will demonstrate how to partition an existing table without disturbing the existing data in the table by taking advantage of it’s clustered index. SQL Server Books Online (BOL) is laced with interesting tidbits of information but sometimes falls short of delivering on the specifics. Let’s face it, there’s no way to demonstrate every [...]
This is a real quickie but it cost me dearly this weekend. My task was to call 7-zip from within SSIS and extract the contents. Here’s the basic example that’s all over the Internet: 7z e archive.zip -oC:\Doc Where: 7z is the executable e is the argument to perform an extract archive.zip is the file [...]
I recently had a situation where I needed to compare table columns between the production table and a test/development table. Chances are if I needed to do it someone else will to. I like using the EXCEPT SET OPERATOR. Some people might prefer to use an OUTER JOIN to determine the column differences. With the [...]
In several of my production databases I have to deal with tables that have hundreds of millions of records. Querying these tables are challenging; doing updates to them can be nightmarish. I recently had to add a column to a table that had 65 million records. The table is an aggregate table so the new [...]
There are some tasks, no matter how seemingly simple, I just would not want to have to do again. Today’s post is about one of them… At work we use a data cleansing tool that purchased from the USPS (Post Office) called NCOA (National Change of Address). All of the big list brokers use the [...]
This is the third and final part to this thread on how to capture the number of rows being processed in SSIS to a flat file. In Part I we learned how not to do it and why it didn’t work. In Part II we showed how to use the ROW COUNT transformation to populate [...]
In yesterday’s post we pulled a Thomas Edison and showed one of 10,000 ways how not to write the number of records being processed using the ROW COUNT transformation. We thought the process was straight forward, but it didn’t work. Our approach was: 1. Create a query that processed some records. 2. Send the result [...]
Yesterday I had a simple enough task at hand. An existing EXTRACT that loads a result set to a DATA file needed to have a corresponding COUNT flat file that contained the number of rows in the extract’s data file. Not being the most savvy at SSIS I did what anyone else would do get [...]
My wife of 24 years recently asked me “Why blog? Are you looking for a new job?” At first I didn’t have a good answer for her other than “If I were looking for a new job it would be too late for blogging”. After all, nobody blogs their way into a job overnight. If [...]
What does one do on a Friday night after going out with some friends and having a couple of mugs of suds? How about doing a T-SQL puzzle? Jacob Sebastian and a team of MVPs and high-powered database gurus are running a series of challenges. It’s the closest thing to getting an assignment in college. [...]
Today’s post is going to be short and sweet. About a year ago I put together a procedure that allows me to see the run times of all of the SQL Agent jobs on a database server. Some might argue that I got a little carried away when I added the standard deviation and variance [...]