Should I index a view that is converting data types?

Question

Should I index a view that is converting data types?

traughber

SSC-Addicted

Points: 419
More actions
December 7, 2010 at 2:30 pm

#388388

I have two tables of raw data that were imported into SQL Server and every data type was set to varchar(255). The data won't stay that way forever, but in the mean time, some business users need to query the data through MS Access. I have created a view where I am unioning the two tables together and renaming some fields to match some legacy data they are used to. I am also doing some CASTs in order to change numbers to numbers and dates to dates.
My question is whether or not I should index the view. There are no joins and no aggregations, but I didn't know whether converting data types can benefit from indexing in a view.
Thanks.
Kris

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

It really depends on the queries being run against it. As it currently stands, any filter against the view will result in table scans. Actually, as it's configured, any query against it will result in table scans. So, are the Access queries simply moving the data down to Access, in which case it's just going to be a scan anyway, or are they filtering the data? If the latter, you should create indexes.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

traughber SSC-Addicted Points: 419 More actions · Answer 2

I will be linking to the view in MS Access and users will be writing queries against the view. They'll be doing some filtering and aggregating.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

traughberk (12/8/2010)
I will be linking to the view in MS Access and users will be writing queries against the view. They'll be doing some filtering and aggregating.

Then you're going to want to put indexes on the underlying tables. The first thing I'd look for is the place on each table to put the clustered index. It should be the column or columns that define the most selective data AND the most frequently filtered. After that, you might need to put some non-clustered indexes in, but it's hard to know without seeing the queries being run and the data within the tables.

All that said, varchar(256) is less than optimal for indexing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

traughber SSC-Addicted Points: 419 More actions · Answer 4

That's why I was wondering if I should index the view instead of the tables, because the data types in the tables aren't optimal for indexing. So, should I index the view instead?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

I can't say, absolutey yes, or absolutely no. It's probably worth the experiment. But, remember, as far as indexing the view goes, you can only create that cluster. If you need any other indexes, you can't create them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

traughber SSC-Addicted Points: 419 More actions · Answer 6

I just looked at the data to find some columns to create the clustered index and there aren't any combination of columns that are consistently unique in the entire table. I even checked with a business user who knows the data better. This is data we received from an outside source so we don't have any control over its quality. There are various fields with amounts as well as other fields that make it so you can tell one row from another, but those aren't consistently unique.

So I guess my only option is to create some non-clustered indexes on the underlying tables, unless you have any other suggestions.

Thanks for all your help.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

traughberk (12/8/2010)
I just looked at the data to find some columns to create the clustered index and there aren't any combination of columns that are consistently unique in the entire table. I even checked with a business user who knows the data better.

A common confusion. The clustered index doesn't need to be unique, though it can help with the size on the non-clustered because of the lack of a need for an internal RID.

A Primary Key or Unique constraint, that does need to be unique, but I use non-clustered Primary Keys all the time.

You want the clustered index to be your primary data access method.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

traughber SSC-Addicted Points: 419 More actions · Answer 8

I tried to create a clustered index on the view as seen below.

But I received the following error.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Well I'll be damned. I had to test it myself to be sure (you left the unique check flagged above, fyi) but you're right.

Alright, I take back what I said. That's true for tables, apparently not for schemabound indexed views.

Sorry about that.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

traughber SSC-Addicted Points: 419 More actions · Answer 10

No problem. Yep, I did accidentally leave the Unique flag checked. I've decided I'm going to create an identity column to help with indexing.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 11

traughberk (12/8/2010)
No problem. Yep, I did accidentally leave the Unique flag checked. I've decided I'm going to create an identity column to help with indexing.

You don't necessarily want to create an identity column. They don't "help" with indexing, a monotonically increasing integer value just indexes really well. But, if that value isn't used in the selection process, it doesn't matter how beautiful an index it is.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

traughber SSC-Addicted Points: 419 More actions · Answer 12

Any suggestions then on how I can increase the performance on this data? Should I just create a few single non-clustered indexes on often-used columns?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 13

I'd start with clustered indexes on the most select and most used column or columns. Then add non-clustered indexes from there. Remember if the key in a cluster covers the JOIN and/or WHERE conditions in a query, you're done. If the key in a non-clustered does the same, you still need to do an RID lookup on heap tables to get the data back.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

traughber SSC-Addicted Points: 419 More actions · Answer 14

That's what I was struggling with. How unique does something need to be in order to be considered "selective"? I have SSN, dates, and some other data, but none of it makes a record truly unique.