Indexing the PO

Question

Indexing the PO

Steve Jones - SSC Editor

SSC Guru

Points: 734444
More actions
August 29, 2018 at 10:34 pm

#77353

Comments posted to this topic are about the item Indexing the PO

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72498 More actions · Answer 1

Nice question, thanks Steve
a slight twist - If the OrderKey column is of type INT, the ALTER TABLE script will fail with error "cannot convert varchar to int"

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 2

got it wrong but learned something in doing so
cheers steve

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 3

Stewart "Arturius" Campbell - Wednesday, August 29, 2018 11:01 PM
Nice question, thanks Steve
a slight twist - If the OrderKey column is of type INT, the ALTER TABLE script will fail with error "cannot convert varchar to int"

I still had the "old" table in my playground database from a prior QOTD.
For a brief moment, I thought that was the catch that would make "The index is not created because of another error" the correct answer.

webrunner SSC-Dedicated Points: 31717 More actions · Answer 4

Thanks, I learned something.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Heh... everytime I'm reminded of this, I end up asking myself... who would index a non-persisted column?

--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)

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

Jeff Moden - Thursday, August 30, 2018 11:39 AM
Heh... everytime I'm reminded of this, I end up asking myself... who would index a non-persisted column?

Well, if it is a covering index, then the data doesn't need to be stored in the main table, just the index. If there is an index on the computed column it would seem valid that the data in the computed column is persisted in the index.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Lynn Pettis - Thursday, August 30, 2018 12:12 PM
Jeff Moden - Thursday, August 30, 2018 11:39 AM
Heh... everytime I'm reminded of this, I end up asking myself... who would index a non-persisted column?
Well, if it is a covering index, then the data doesn't need to be stored in the main table, just the index. If there is an index on the computed column it would seem valid that the data in the computed column is persisted in the index.

Hmmmm.... good point and very interesting, Lynn. That could make for a pretty decent disk space savings on longer tables not to mention a reduction in the actual width of the CI. Seems like it would also result in a memory savings to not persist the column. Got some testing to do. Thanks.

--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)

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 8

I saw something interesting like this with one of Brent's demos.

Parameter from app is nvarchar()

Table is

create table Sometable (
somepk int
, someval varchar
)

Can't change the schema, but add

alter table sometable add somevalnvarchar nvarchar()

then index the new column. Can be used by the optimizer, not persisted,