More Computed Column Indexes

Question

More Computed Column Indexes

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
October 15, 2019 at 12:00 am

#3679586

Comments posted to this topic are about the item More Computed Column Indexes

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

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

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 1

The error message for that isn't quite what I got, and with a little tweaking I could add the index

drop table if exists dbo.MonthlySales;
create table dbo.MonthlySales (
      id int identity(1, 1) primary key clustered
   ,  invoiceLine     bigint
   ,  SalePrice   float)


ALTER TABLE dbo.MonthlySales ADD EstSalePrice AS ROUND(SalePrice, 0) persisted

CREATE INDEX MonthlySales_EstSalePrice ON dbo.MonthlySales (EstSalePrice)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

dammit - I pretty much only use persisted computed columns (we write once and read many)… so my version worked - I took out the persisted and it errored

MVDBA

Toreador SSChampion Points: 11365 More actions · Answer 3

None of the answers is correct. You can index a computed column based on a float as long as it is persisted.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

Toreador wrote:

None of the answers is correct. You can index a computed column based on a float as long as it is persisted.

I made that same mistake - steve didn't put persisted in his code - sneaky.... but i learned from it

MVDBA

Stewart "Arturius" Campbell SSC Guru Points: 72625 More actions · Answer 5

Seldom use float, so have never had to perform this kind of task, so learned something.

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

There are two answers that are close but none of them are actually correct. Great question topic though.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

Without persisted, this doesn't work. That was what the question tested, not if you can find a way to index a float column.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Steve Jones - SSC Editor wrote:

Without persisted, this doesn't work. That was what the question tested, not if you can find a way to index a float column.

Right, but the "correct" answer states "An error is returned because you cannot index a float field used in a computed column". That is not true. You can index a float field in a computed column, but it must be persisted. I looked at the question and knew right away it would need to be persisted but none of the answers really dealt with that. If it had added "unless it is persisted" to the end of the answer it would be have been more clear.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

Steve Jones - SSC Editor

SSC Guru

Points: 736212

October 15, 2019 at 7:33 pm

#3689019

I can see the answer as misleading. Edited it.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Steve Jones - SSC Editor wrote:

I can see the answer as misleading. Edited it.

I wouldn't say it is misleading but it certainly had me scratching my head. The fix leaves it ambiguous enough to not give it away but also clears it up nicely. Well done as always Steve.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 11

I'm with steve on this - the question wasn't "what do you do to fix this" - it was "what error will occur" - and even though I never use float and if I do a computed column I persist it, steve's question highlighted something that might trip me up... so kudos for the question

MVDBA