| Downloads |
|---|
| 96137.zip |
| Subscribe to SQL Server Magazine UPDATE, a free weekly email newsletter, with exclusive commentary by Brian Moran. Sign up now at http://www.sqlmag.com/email. |
Here's the solution to the June Reader Challenge. (To read the full Challenge, "Optimizing Queries for Fact Tables," go to InstantDoc ID 95960.)
Solution:
Mark can't create an index on each of the measure column s in the fact table since such a construction would contain values from all the rows. In addition, since the data in the fact table is sparse, creating such indexes would be inefficient. Instead, he can use indexed views to create smaller indexes and filter the rows based on the non-null value of a particular measurement. For example, he can create an indexed view per measurement that contains only non-null values such as
WITH schemabinding
AS
SELECT InstrId, Msr1, CreateTime
FROM dbo.InstrStats
WHERE Msr1 IS NOT NULL
GO
Mark can create a unique clustered index on the view that contains the instrument identification and creation time of the fact table. The following CREATE INDEX statement creates such an index on the InstrStats_Msr1 view:
InstrStats_Msr1_Time on
dbo.InstrStats_Msr1(InstrId,
CreateTime desc);
go
The scripts in Web Listing 1 show how to create such indexed views on other measurement columns.
Now the queries from the reports can use the indexed views more efficiently. And if the SQL Server software is Enterprise Edition, the query rewrite happens transparently. You can observe the use of the indexed views for the sample report queries by looking at the show plan output. Figure 1 shows sample output.
JULY CHALLENGE:
Test your SQL Server savvy in this month's Reader Challenge. Submit your solution in an email message to challenge@sqlmag.com by July 19, 2007. A SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Problem:
Tony is a T-SQL programmer in a company that sells sports products online. He is currently designing the product catalog functionality of a Web-based order placement application. The database is hosted on Microsoft SQL Server 2005. As part of the product catalog display, he wants to localize the description of various products based on the language or regional settings of the customer.
For the purpose of this problem, assume that the product catalog tables are similar to the tables in the sample AdventureWorks database for SQL Server 2005. The tables that can be used for solving the problem are \[Production\].\[Culture\], \[Production\].\[ProductDescription\], \[Production\].\[ProductModel\], \[Production\].\[ProductModelProductDescriptionCulture\] and \[Production\].\[Product\].
The \[Production\].\[ProductModelProductDescriptionCulture\] table contains the mapping between each product model, description, and culture or language stored in the database. For example, it might contain descriptions for certain models for the English language or for the English and Spanish languages.
Using the AdventureWorks database tables I just described, help Tony create an appropriate database object that he can use on the client or server and that returns the following details when a specific culture is chosen:
- Product attributes
- Product model name
- Localized product description (i.e., the description for the specified culture or the description in English when a culture isn't specified)
In addition, Tony needs the flexibility to filter the results based on various product attributes or join with other tables, views, or functions.
