Author : Shaun Tinline-Jones Reviewers : Mike Ruthruff, Sanjay Mishra, Alexei Khalyako Not too long ago an ISV that developed solutions using SQL Server as the RDBMS, asked me how they could query a database as at a point in time. This was a relatively...

Author : Shaun Tinline-Jones Technical Reviewers : Thomas Kejser, Steve Howard, Jaime Alva Bravo, Kun Cheng, Jimmy May Note: Validation for this post was performed in the SQL CAT Customer Lab on an HP Proliant DL580 G7 , Intel Xeon Nehalem E7...

Authors: Kevin Cox & Michael Thomassy Contributors: Lubor Kollar Technical Reviewers: Shaun Tinline-Jones, Chuck Heinzelman, Steve Howard, Kun Cheng, Jimmy May Overview Connecting directly to a SQL Azure database from a reporting tool...

Author: Kun Cheng Reviewers: Wanda He, Kevin Cox, Michael Thomassy, Peter Scharlock In a recent customer engagement, I ran into an interesting situation, which I’d like to share, especially with SQL Server users using Microsoft JDBC drivers. It started...

Implementing a hierarchy structure in a relational data base normally takes a bit of work. The new SQL Server data type for hierarchyID gives a good shortcut to the old methods, makes it faster to get a solution in place and makes it much easier to maintain...

In data warehouses, it is quite common that fact records arrive with a source system key that has not yet been loaded in the dimension tables. This phenomena is known as “late arriving dimensions” or “early arriving facts” in Kimball terminology. When...

At first I was going to write a detailed white paper about the conversion effort. But someone has already done it. There is a very good guide at: http://nationalcom.com/home/download/Conversion-VFP-SQLServer.pdf Since FoxPro is nearing its end of...

The database system in a company that is growing internationally must support multilingual characters in tables with Unicode data types. Existing d atabases that support only non-Unicode information must be migrate d from non-Unicode data type to Unicode...

Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance...

For those of you whom want to know a little more about precision considerations for Analysis Services users, please check out this recently released whitepaper at http://www.microsoft.com/downloads/details.aspx?familyid=bae8beec-9892-4ecd-a9db-292254895f9c&displaylang...

Scale-Out Querying with Analysis Services Using SAN Snapshots

Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services There are a variety of things that you can do to improve the performance of an individual MDX query. To begin, you must identify the source(s) of the performance...

Author: Denny Lee Contributors: John Desch, Kevin Cox Reviewers: Robert Zare DRILLTHROUGH MDX Overview DRILLTHROUGH MDX is a very powerful way to drillthrough to your original data source of detail data that make up the aggregations...

Goal This document describes how to build Create Cache commands. Create Cache for Analysis Services (AS) was introduced in SP2 of SQL Server 2005. It can be used to make one or more queries run faster by populating the OLAP storage engine cache first...

Summary: SSAS uses partitions to contain cube data. Small cubes might use only one, but for non trivial cubes, cube designers will create partitions based on ease of managing data and to split groups of data. This document discusses how the server uses...

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx Get the real-world guidelines, expert tips, and rock-solid guidance to take your SQL Server implementation to the next level. Drawing on the extensive experience and expertise...

Overview SQL Server’s 2005 Analysis Services has introduced several changes to the MDX queries syntax that can lead to better performance than the equivalent AS 2000 queries. Here is a list of tips and tricks as well as best practices on how...

Many applications need to generate sequentially incremental number as unique/primary key of records. This blog describes additional options of generating sequence numbers beyond the SQL Server's support of identity column....

SQLCAT Blog: Managing Schema Changes (Part 2) Question: What are best practices for managing schema changes in SQL Server 2005? What are the fastest, least intrusive techniques for high availability? Answer: In Part 1, we outlined the behavior...

Another pattern that can cause too many round trips to the database is when each object is treated as a black box and is responsible for retrieving and saving its own data. This by itself is not the problem; it’s when the object hierarchy reaches three...

Question: What are best practices for managing schema changes in SQL Server 2005? What are the fastest, least intrusive techniques for high availability? Answer: A common requirement as business requirements evolve over time is managing schema changes...

I have shown how you can find out if SQL Server eliminates partitions in your queries. Most of the time SQL Server is doing great job in partition elimination and it is scanning or seeking only partitions that could potentially yield rows satisfying the WHERE, IN or other row eliminating clauses. In spite of that you should still check the query plans of your queries against partitioned tables if you suspect the performance may be or is an issue. You should be aware that SQL Server is employing both the static partition elimination when the query is optimized and dynamic partition elimination when the choice of scanned and skipped partitions is made at the query execution time. ...

As promised way back in August, here is the first installment of a series of blogs on database performance from the application point of view. Complex screens can cause database performance problems when each control (grid, list box, etc) on the screen...

In workloads that involve expensive long-running queries and reports, or complex batch operations, you frequently want to see the SQL statements and associated query plans that are active on the system or that consume the most resources. SQL Server...

At least two customers that I’ve worked with in the past month have been building Data Warehouses that contain Social Security Numbers (SSN) – in one case it is even being used as a foreign key for a very large fact table. These customers were interested...