Can you create an index that has an expression ?

Question

Post reply

Can you create an index that has an expression ?

mw_sql_developer

SSCoach

Points: 19445
More actions
March 8, 2016 at 4:18 pm

#305474

Select top 100 CONVERT( CHAR(8), LAB_SCR_DT, 112 ) , * FROM LAB_IN
This will not work..( below )
Create INDEX IDX_334455 on LAB_IN ( CONVERT( CHAR(8), LAB_SCR_DT, 112 ) );
I thought it was possible to create an index that has a expression ? Yes/No ?
Please share your opinion please ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

You can add a computed column to the table and then index it.

_____________
Code for TallyGenerator

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

You can also create an indexed view.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Sergiy (3/8/2016)
You can add a computed column to the table and then index it.

Agreed. I haven't done that since earlier today.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

mw112009 (3/8/2016)
Select top 100 CONVERT( CHAR(8), LAB_SCR_DT, 112 ) , * FROM LAB_IN
This will not work..( below )
Create INDEX IDX_334455 on LAB_IN ( CONVERT( CHAR(8), LAB_SCR_DT, 112 ) );
I thought it was possible to create an index that has a expression ? Yes/No ?
Please share your opinion please ?

Is this just a random sample to explain the problem domain or is it quite close to an issue you have with filtering/joining on datetime columns? If it's the latter, then post up a sample or two of affected queries. Datetime arithmetic isn't always intuitive and many folks take a few tries to figure it out. You'll get fixed queries with explanations.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

Anyone,

I like the idea of a computed column.

Can you share the syntax for that please ?

ALTER TABLE a

ADD dd_date CHAR(8) and then ??????

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

Thx to

Sergiy

create table a ( x datetime );

alter table a

add date_char as convert(char(10), x, 112 );

😛

insert into a ( x ) VALUES ( '05/12/2005' );

select * FROM a;

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

I'm glad to see you figured it out. Here's some information on your next topic: https://msdn.microsoft.com/en-us/library/ms189292%28v=sql.110%29.aspx

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions