Is it possible to create nonclustered index on columns with space and ignore the space so that it does not do a table scan?

Question

Is it possible to create nonclustered index on columns with space and ignore the space so that it does not do a table scan?

skhan-680833

SSChasing Mays

Points: 622
More actions
December 18, 2009 at 10:02 am

#218866

Is it possible to create nonclustered index on columns with space and ignore the space so that it does not do a table scan?
Thank You
SD Khan

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The table scan occurs if the Engine determines that a table scan will be more efficient. If you have null values or empty strings in your column, they will be included in your non-clustered index if you create it.

You should test the creation of your non-clustered index to determine if the new index will help improve performance. One of the factors to consider is column selectivity and uniqueness of the data in that column. The query that is causing the table-scan may also need to be revised.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What precisely do you mean by "columns with space"?

Columns with lots of nulls?

Columns with lots of empty strings?

Columns that have varchar/nvarchar with leading/trailing spaces?

Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

skhan-680833 SSChasing Mays Points: 622 More actions · Answer 3

I am not sure what the developer is actually trying to achive so i told him to redo his query.....but is it possible to do teh create index in a way so that it ignores the blank spaces?

I think the problem is there has been new fields added and new data is coming in in the table.

Thank you

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Who designed the tables, the developer? If you post the table DDL, index definitions, sample data and your query, we can help more. Please reference this link to learn how to format your sample content:

http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

skhan-680833 (12/18/2009)
but is it possible to do teh create index in a way so that it ignores the blank spaces?

What do you mean? What blank spaces where?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

GilaMonster (12/18/2009)
skhan-680833 (12/18/2009)
but is it possible to do teh create index in a way so that it ignores the blank spaces?
What do you mean? What blank spaces where?

Almost sounds like a filtered index. Unfortunately, not available in SQL Server 2005.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

More information would be needed to best answer the question. As it is, we are only guessing at how best to help/answer your question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

skhan-680833 SSChasing Mays Points: 622 More actions · Answer 8

Thanks everyone. I have asked the developer to redo the query. I do not think filtered index is an option and as it is a vendor apps they build these indexes thourgh the vendor designer tools. Also I checked a few things and I think he should not put indexes on those columns and do the query in a different way. Thank you for all of your help and suggestions.

SD Khan

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

Never mind... wrong rev...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)