databasejournal.com databasejournal.com

Scheduled Task Reporting on SQL Server

Introduction If you have a server with a large number of scheduled tasks, you will know that keeping track of them all can be a bit difficult at times - checking and double-checking that your database backups and log backups are in synch, that maintenance tasks go off in the correct order, and that tasks do not interfere with one another by their timings, can be a chore. In Enterprise Manager, it is pretty hard to keep track of a...

databasejournal.com databasejournal.com

Linked Servers - Page 4

Loopback Linked Servers You can set up a linked server to point to itself, but why would you want to? One reason Microsoft cites is that you may only have one server to develop on, but need to test linked servers for a live environment with more than one server (See, you can try this at home!) Loopback linked servers do come with a couple of drawbacks though--you cannot use them for Distributed Partitioned Views, and you cannot use...

databasejournal.com databasejournal.com

'For Each Table' Stored Procedure

Microsoft provides a very handy system stored procedure called sp_msForEachTable with SQL Server. This SP is designed to easily allow the database administrator to perform simple tasks on every table in a database with a single command. While sp_msForEachTable works just fine in most cases, there is one problem with it--it attempts to cater to multiple object owners in a database by using the [owner].[object] syntax....

databasejournal.com databasejournal.com

Dynamic SQL Beginner's Guide

Dynamic SQL allows you to write SQL that will then write and execute more SQL for you. This can be a great time saver because you can: Automate repetitive tasks. Write code that will work in any database or server. Write code that dynamically adjusts itself to changing conditions Most dynamic SQL is driven off the system tables, and all the examples I use here will use system tables, but if you have suitable data in user tables, you...

databasejournal.com databasejournal.com

Tuning 'max async IO'

Tune 'max async IO' and get the best out of your disk subsystem The "max async IO" configuration setting for SQL Server enables you to tune disk IO for increased performance. Unfortunately, though, this setting is often overlooked. One of the main bottlenecks for any database system is disk IO. Reading data from a hard drive disk is many, many times slower than reading data from RAM. This is why database systems like SQL...

databasejournal.com databasejournal.com

Case Statement Tricks

The CASE statement is a very flexible tool. Here are just a few of the tricks you can work with it. Item translation One of the simplest things you can do is to derive a columns contents based on it's contents, or the contents of another data item. Click here for code example 1. Titling subtotals You can use CUBE and ROLLUP to produce totals and subtotals within a result set, but because SQL Server places NULLS for non-totaled...

databasejournal.com databasejournal.com

Using Index Intersection

Introduction First introduced in SQL Server version 7, Index Intersection gives you new options for creating indexes on tables to maximize performance To start with I am going to pick the authors table from the Pubs database and explain the indexes that exist on that table, then look at how we would use indexes on that table for queries prior to Index Intersection, then we will look at the options that Index Intersection gives us. A...

databasejournal.com databasejournal.com

Complex Updates Using the Case Statement

Introduction One of the keys to database performance if keeping your transactions as short as possible. In this article we will look at a couple of tricks using the CASE statement to perform multiple updates on a table in a single operation. Multiple updates to a single column This example uses the pubs database to adjust book prices for a sale by different amounts according to different criteria. In the example I am going to knock...

databasejournal.com databasejournal.com

Saving Space to Increase Performance

Introduction It's easy to become complacent about saving disk space when hard disk sizes keep growing and disk prices keep on dropping, but saving a few bytes here and there can help improve SQL Server performance considerably. If you have ever looked at an Execution Plan for a SQL Server query (and if you haven't, you should!) you will see that SQL Server produces an estimated "cost" for executing queries. This cost is not in money...

databasejournal.com databasejournal.com

Removing Duplicate Records

Introduction "How do I remove duplicate rows from a table" is a common question in the newsgroups, and here we will look at a few different techniques for achieving this. Databases can easily be designed to disallow duplicates (which I will discuss at the bottom of the article) but the issue can still arise. For example, duplicate data might arrive at your database via an interface to another system. The code examples in this...

databasejournal.com databasejournal.com

SQL Server 2000 Trigger Enhancements

Introduction SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger. If you are already familiar with programming triggers then read on, but if you are new to the subject then it would be worth reading about triggers in Books Online, or taking a look at the Triggers section on my own SQL Server page,...