Solved

MSSQL 2005 filtered index non-unique column return unique result set vs indexed view

Posted on 2010-11-15
5
768 Views
Last Modified: 2012-05-10
I have an MSSQL 2005 table that has transaction-related data, which may have multiple entries per significant column.  It is unique only on its sequential ID key (primary key).

I need to return, if possible, the data filtered down to the most recent entry for each key field value and was wondering if it's possible to do that with a filtered key.

Columns are:

id, order_num, order_ref_num, ts

Where id is an auto-incrementing sequential unique ID, order_num and order_ref_num are text fields and ts is a date/time field (timestamp.)  There are occasions where multiple order_num+order_ref_num sets have the same timestamp value, because the format was changed from logging date only to logging time and date, so the uniqueness and filtering of most-recent order/ref pairs can't be gained by using timestamp.

The order_num field would relate to an order table.  There may be multiple order_ref_num values associated with a particular order_num, and there may be multiple records with the same order_num/order_ref_num set but different timestamps.

Can the most-recent unique record be returned using a filtered index, or does it have to be filtered down in the query?  If it can be done with a filtered index, what would be your recommendation for setting up that index.  The key field would be order_ref_num, and would need to return unique order_ref_num and order_num combinations.

If not, can it be accomplished using an indexed view, and if so, how?  Are complex SELECT statements available for creating an unique-valued indexed view per the requirements stated earlier?  What would be the potential performance impact of an indexed view vs a filtered index?

By the way, the key fields of the order table are order_num and transaction_id, which set also is non-unique and would also have to be filtered down to the most-recent records of each key set to make them unique as well.  It also has a primary key of a sequential ID.

I was looking on technet and don't see whether it's possible to do an equivalent of select distinct or similar to create a filtered index, and it doesn't really say if indexed views can use anything like select distinct either.  TOP is mentioned, but isn't likely to be of any use for this scenario.

Thanks!

0
Comment
Question by:ShineOn
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34138878
I suspect you will find that Filtered Indexes are not supported in SQL Server 2005.  They were introduced in SQL Server 2008:
http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34140056
>> Can the most-recent unique record be returned using a filtered index? <<

How, specifically, should we determine the "most-recent unique record"?

If order_num & order_ref_num & ts are all identical, do you want the last id value?  The first id?  Something else??



0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34140067
Do you want unique per combination of all 3 or just two or what?

Sorry, I'm not clear on exactly what results you want to see.

Some brief sample data with desired results would greatly help me understand :-) .
0
 
LVL 35

Author Comment

by:ShineOn
ID: 34140134
Scottpletcher, I will create a new Question that is specifically about the view I would want to create, that should have those questions answered.

I am going to close this, accepting acperkin's comment, because I made a mistake - I can't do a filtered index in SQL2005, and that is, unfortunately, my answer to this question.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question