We’ve been talking about how to create and perform automated database maintenance tasks for the non-DBA individual. Primarily, we have been focusing on tasks that can be performed real time, or scheduled to be done during hours when the database is less active, such as updating statistics, or defragmenting indexes. However, there are some maintenance tasks you really need to […]

If you aren’t an SQL Server guru, you are not restricted to the out of the box implementation for maintaining your database. There are certain features that make sense to do periodically instead of maintaining it real time. For example, as we talked about a couple days ago, you can configure your database to automatically maintain statistics. If you have […]

Have you ever created custom data types in SQL Server? A user defined data type is a data type you define based on an existing standard data type already included in SQL Server. User defined data types are created in a single database, and are not understood by other databases. The data must be converted back to the original SQL […]

SQL Server ships with a number of methods for importing and exporting data to and from SQL Server in bulk. The concept, as you probably know is called Bulk Copy. Bulk Copy ADO implementation Bulk_Insert TSQL command Bulk Copy Executable Utility The Bulk Copy implantation in ADO works nicely when you have data you want to import from a Dot […]

Tuning a database for performance is a never ending task. For SQL Server, you can use automated maintenance implementations, which are usually the default. If the database is configured, it will automatically create statistics when the query optimizer determines a statistic would be useful to determine the best query plan. It will also automatically update statistics whenever data is modified […]

One reader responds to yesterday’s editorial on using multiple indexes to solve a single query with a question. Michael asks, “Are you saying it ultimately better to have multiple single column indexes than to have multiple column indexes?” When a query uses multiple indexes to support a query it can be helpful. However, it will not perform as well as […]

In response to the editorial on Filtered Indexes, David shares one way he uses them. He says many of his tables have an EffectiveStatus column, and the status often sought has a value of 1. I don’t know the data domain of the other values…nor the percentage of the rows in the table having an EffectiveStatus of 1. Still, this […]

Today I want to return to a topic I tend to cover annually, because we are always getting new readers, often new to database techniques. The topic is indexing. In fact, I want to talk about a specific method of indexing found in SQL Server, where the index has a where clause, known as a filtered index. A filtered index […]

As I read the guidance of many database professionals one concept appears to be frequently espoused. When writing SQL, avoid using engine specific syntax. Use ANSI standard syntax whenever possible, as supported by your database vendor. The reasoning is this: If you use ANSI standard syntax, you can more easily host your application on a database from a different, compliant […]

Recently I was reminded of the saying, “If the only tool in your toolbox is a hammer, then all of your problems start to look like a nail.” I’m sure we can apply this maxim to many areas of our lives. I’m, of course, going to relate it to information systems, because that is what I do. Let’s start out […]