Using the CASE expression instead of dynamic SQL in SQL Server
Learn how to use the SQL CASE statement to make dynamic logic decisions for Selects, Updates, Deletes, Order By and more.
SQL Server, T-SQL, Development, DBA and Career Resources
Human Created Content by Industry Experts for Developers, DBAs and Analytics Professionals
Built in Time Dimension and Time Intelligence in SQL Server Analysis
We are building an Analysis Services cube and trying to understand how to create a time dimension and take advantage of the built-in time intelligence features that provide many useful calculations such as period-to-date, period-over-period growth, etc.
Solid State Drives and SQL Server
Learn about SSD storage benefits when used with SQL Server.
SSRS Configuration Tool
Installing SQL Server Reporting Services is just the first step in building the foundation for a solid Reporting Services instance. The second step in the process is configuring Reporting Services to conform to the particulars of your environment. These
SQL Server Index Checklist
Indexing a SQL Server database in some respects is considered both an art and a science. Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production? Are these the same types of steps o
Build a SQL Server 2005 Updatable Subscription Replication Topology
SQL Server 2005 has introduced a new replication method called Updateable Subscription Replication which allows data modification at both the publisher and the subscriber(s). An option is also available to queue the data from the subscriber so the subs
Custom SQL Server Index Defrag and Rebuild Procedures
This tip covers custom SQL Server index defrag and rebuild procedures to handle fragmentation.
Avoid Index Redundancy in SQL Server Tables
In this article, we look at how to create a SQL Server index that can satisfy multiple queries versus having to create multiple indexes.
Execute PLSQL Code in an Oracle Package from SSIS
We have a number of Oracle stored procedures that we need to execute from an SSIS package. The PL/SQL code is deployed in various Oracle packages. Many of these stored procedures require parameters and we're struggling to find the best way to do this gi
Join SQL Server tables where columns include NULL values
Dealing with NULL values especially when joining tables can become a challenge. Let's take a look at this issue and how this can be resolved.
SSIS Handling Early Arriving Facts
We typically use the Lookup Data Flow Transform to retrieve the surrogate key for a dimension row based on the natural key in a fact table. While this works well, we occasionally have a situation where a fact table row has a natural key that is not in ou
SQL Server Fragmentation Hands On Experience (Part 9 of 9)
In our final tip of the fragmentation series we walk through a complete script that will cover almost everything we discussed in the series.
Adding Reporting Services to an existing SQL Server 2005 installation
With Microsoft SQL Server 2005 going on three years many of us are in the process of acquiring new hardware and getting new instances up and running. In our company this means a shuffle of some of our Database Services, Reporting Services, and Analysis S
SSIS script task to dynamically build package variables
One of the advantages of using SSIS is the ability to dynamically create tasks and variables that can take different elements and manipulate them in code instead of having to hard code the package to do only one task. In a previous tip we looked at how to
Handle Slowly Changing Dimensions in SSIS
We have experimented with the Slowly Changing Dimension (SCD) Data Flow Transformation that is available in the SSIS designer and have found a few issues with it. Our major concern is the use of the OLE DB Command Data Flow Transformation for all updates
Correct the SQL Server Authentication Mode in the Windows Registry
I have a SQL Server 2005 instance with Mixed Security Mode, BUILTIN Administrators rights revoked and I have not set up a Dedicated Administrator Connection (DAC) in the SQL Server 2005 Surface Area Configuration tool. I have tasked my Junior DBA with set
SQL Server Fragmentation How to address it (Part 8 of 9)
In this article we look at how to address fragmentation with rebuilds and reogranization of indexes.
SQL Server Fragmentation Custom Detection Procedure, Page Linkage Part 7
This tip inlcudes a script to help you detect and deal with SQL Server fragmentation.
SQL Server Fragmentation How to detect it (Part 6 of 9)
This article covers how to detect SQL Server fragmentation.
Script to determine permissions in SQL Server 2005
At times I run into application issues when I am unable to perform particular functions in the application that some of my peers are able to complete. I need to be able to quickly and easily understand my permissions in SQL Server. Do you know of an eas
SQL Server Reporting with Microsoft Excel
Our business users are very savvy with Microsoft Excel. The issue we struggle with is getting the SQL Server data to them in a streamlined manner. We are familiar with some of your earlier tips related to integrating Microsoft Excel and SQL Server. We
SQL Server 2005 Database Mail Setup and Configuration Scripts
One of the most welcome SQL Server 2005 enhancements over its predecessors is Database Mail. Database Mail is built on the Simple Mail Transfer Protocol (SMTP) as compared to SQL Server 2000 Mail which used a buggy and temperamental MAPI client causing s
Dynamically build connection objects for MS Access databases in SSIS
In this article learn how to build a dynamic connection to a Microsoft Access database from a SQL Server Integration Services package.
Upgrading SQL Server databases and changing compatibility levels
To take advantage of new features in SQL Server make sure you update the database compatibility level after upgrading or migrating a database.