SQL database control and deployment

If you develop SQL databases, you know how difficult it can be to promote from the development environment to production with any level of confidence that the databases are the same.

When you have upgrades, it’s difficult to identify changes as well, especially when there are multiple developers making changes to multiple databases. The problem is compounded if your target environment is a client’s system over which you have no control. It’s staggering to think about what can happen if there are 600-plus client systems that have various versions of your database.

Finding Duplicate Indexes in Large SQL Server Databases

One important component of tuning a large, heavily used database, is to ensure that the tables are indexed optimally: enough indexing, but not too much indexing for the application you are running. There are rules of thumb about index tuning, but the entire issue is so complex that there's no "silver bullet" solution that will work for every case. However, in tuning indexes we can generally say that it's not a good idea to maintain duplicate indexes on the same data. SQL Server does not provide checks to prevent duplicate indexes from being created, as long as the names are different

Are UDFs Harmful to SQL Server Performance?

SQL Server 2000 introduced user-defined functions (UDFs), and they were immediately hailed as a great tool for encapsulating repetitive code, as well as allowing you to perform more complicated processing directly in an SQL expression. On its face, that claim is valid. You can certainly improve readability and maintainability with UDFs. But cleaner code will be cold comfort if your queries bog down and lock up your server.

How much backup is enough?

How much backup data should you keep around and what are the implications? Part 2 of a series looking at backups and their implications.

Executing the result set

SQL Server Database administrators often generate SQL Statements and execute the generated SQL statement in order to simplify certain tasks. It has always been a twin operation. This article illustrates how to use un-documented stored procedures to execute the generated SQL Statements directly.

Tableau set for BI feast

Newly surfaced Tableau Software aims to revolutionize the acquisition and deployment process with its product suite, Tableau Professional and Standard Editions. To a significant degree, it has succeeded...

Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and

We will use SWITCH() to enact conditional formatting in a couple of different ways. First, we will use it to drive formatting of a value to meet requirements that are based upon the magnitude of the value itself. As a part of our practice with SWITCH() to achieve our ends, we will delve into a popular formatting convention for negative (and other "outlier") numbers.

SQL Server 2005 - SQL Server Integration Services - Part 6

In this article, we are continuing coverage of Foreach Loop container functionality, by discussing different types of loop enumerators. So far, we have presented methods for working with files in a folder as well as records in a recordset (using Foreach File and Foreach ADO enumerators, respectively). Our next enumerator is based on the SQL Server Management Objects technology (hence it is referred to as the SMO enumerator), which is the SQL Server 2005, .NET-based management framework providing replacement for COM-based DMO (Distributed Management Objects) that served an equivalent role in earlier versions of SQL Server.

Business Intelligence with SQL Server Reporting Services - Part 3

Reporting Services exposes web methods via XML web services to facilitate cross platform reports management and delivery. It's a simple SOAP based API makes it very easy to add full functionality of reporting services in your application. It provides a set of rendering and viewing functions as well as complete set of management routines. Reporting services web service is the one stop shop for subscription, management, publishing, scheduling and rendering reports.

Business Intelligence with SQL Server Reporting Services - Part 2

A look at Parameterized reports, stored procedures to retrieve data and the Report Viewer.