I know I just threw a bunch of technical acronyms at you at once, but this is a topic that comes up more and more as people finally move off of SQL 2000 and onto a currently supported version. Companies making this move will have a lot of old Data Transformation Service (DTS) packages that…
Category: Microsoft SQL
HELP! My Log Drive is Full!
A few weeks back I received a call from a client who isn’t running one of our monitoring solutions. “Help! ServerX is not responding! I can’t even connect to it via RDP.” This machine happens to be a virtual machine, so I ask if he can log in to the host machine, and check for…
DATEPART(WK, DateColumn)
It’s been a busy month since my last post. Moving from contracting to consulting requires a more work that I’d thought at first. In looking over the past few months I’ve built a few new queries that I haven’t built before. This first one comes from a report built for a client. The request came…
Upgrade SQL Server
One of the most difficult projects to undertake as a DBA is upgrading your SQL Server from one version to another. The biggest reason that it’s so difficult is the lack of planning and testing before during and after the migration. With proper planning, everyone involved with the migration from the DBA, to the programmers,…
World Backup Day
This past Saturday was World Backup Day, It’s not an actual government recognized holiday, but as a DBA you should start celebrating it. Maybe celebrate isn’t the right word for what you might want to do on WBD. If you have a copy of the SQL PASS Summit 2011 DVDs, check out Grant Fritchey’s Lightning…
SQL203 — Indexes can help|hurt
Yeah, I used a pipe in my title. That’s because indexes can help some things, but hurt others. The brief of it is indexing helps reads, but comes at a cost: your inserts, updates and deletes are going to take longer. That’s because as you change your data, you’re going to have to update the…
SQL 201 – APPLY
Let’s finish up with the SQL 210 series today with the APPLY operator. After you learn how to create your own functions, you’re going to want to use them in new and interesting ways. Learning to use APPLY is like learning to use joins for functions. It let’s you run the function against multiple rows…
Loading Hierarchical Data Using a WHILE EXISTS
Last time, we discussed three methods for inserting data into a destination table that has a recursive relationship. In this article we’re going to cover how to insert data into that table without vi0lating the foreign key constraint by using a WHILE EXISTS loop. If you want to follow along with the examples, you’re going…
Loading Hierarchical Data
Earlier this week the question came up on how can we insert data into a table that has a hierarchical data structure. If you’re not familiar with a hierarchical data structures, here’s a quick explanation. Consider the staging table to the right. This table is used to load new accounts into our system. Each account…
Improve Performance by Replacing Cursors
This is the last article for my upcoming design patterns session. Today we’re going to look into speeding up your queries by replacing cursors, a row-by-row operation with a table value function. Before you start composing that hate-mail on how I’m wrong for not understanding cursors, let me say this: there is a time and…