Remove Bookmark Lookup - SQL in Sixty Seconds 089

[@diegotomiello]

Great video Pinal, I really appreciate your work. Congrats.

[@PinalDaveSQLAuthority]

My pleasure!

[@PatrickWard4]

Excellent video Pinal!

[@PinalDaveSQLAuthority]

Thanks!

[@thiagosa84]

Awesome idea, this kind of short, quick and effective video. Thank you!

[@PinalDaveSQLAuthority]

Thank you for your kind note.

[@shovon100]

Thanks for sharing it. If i have key look up on Image field, will it improve if I include the column on the index that query is using?

[@PinalDaveSQLAuthority]

Image field may not improve the performance

[@positivejostyle3169]

Very helpful ,Pls make video on reading an entire execution plan and entire field in plan

[@PinalDaveSQLAuthority]

Sure I will

[@kalaivanan.s5042]

How to perform few insert and update in large transaction live table what are the method we need follow when we working with live table

[@PinalDaveSQLAuthority]

Would you please explain your example with more detail?

[@kalaivanan.s5042]

@@PinalDaveSQLAuthority Hi i have around 10 gb data in live transaction table but i need to update 1 lak record in that table without affecting live transaction. In that table already indexed one so dml operation taking too much time in this case dead lock also chance to happen. How to overcome this problem

[@PinalDaveSQLAuthority]

@@kalaivanan.s5042 In that case you should consider the Table Partition Switch option. Search for it, partitioning Switching is a good option.

[@valQC]

Nice , Thank you the only I did not figure out
SELECT TOP 1 lead(price, 7) OVER (ORDER BY [oDateTimeLocal] DESC)
FROM Price
WHERE [month]

[@PinalDaveSQLAuthority]

In that case you can still put that in the include part.

[@valQC]

@@PinalDaveSQLAuthority Thank you

[@kalaivanan.s5042]

What situation we need to use covering index

[@PinalDaveSQLAuthority]

When there is keylookup reducing the performance.

[@databaseknowledge465]

Very simple and efficient, great video

[@PinalDaveSQLAuthority]

Glad you liked it!

[@savananjaria9169]

Credulous and yet effective

[@PinalDaveSQLAuthority]

Comments from you made my day. Thanks so much

[@achilleskocaeli]

Thanks for sharing..

[@PinalDaveSQLAuthority]

My pleasure

[@ArindamGangulyKolkata]

Thanks for sharing.

[@PinalDaveSQLAuthority]

You bet

[@Nijamization]

Thank you

[@PinalDaveSQLAuthority]

You're welcome

[@zberteoc]

The problem with this method is that is not recommended to have a specific index bases on each queries you actually use or an index that will include all the columns not part of the index. It should be used with precaution.

[@PinalDaveSQLAuthority]

Everything in SQL Server need to be used with precaution. Proper testing on dev first with staging and production. A unit testing is indeed a must.

[@zberteoc]

@@PinalDaveSQLAuthority I mentioned this only for whom may come across this video and feel excited about what they learn here and then go ahead and apply it every time they have the chance for every query. SQL Server optimizer actually quite often does suggest indexes to be applied with included columns when running the execution plan, be it estimated or actual. As a general idea the index columns should be the ones used in the JOIN clause firstly and in the WHERE clause secondly and in the INCLUDE clause should have the columns needed in the SELECT list. However, you have to make the best in choosing the right index columns and the right included columns for most of the cases and not creating an index for every single query.

[@PinalDaveSQLAuthority]

@@zberteoc Absolutely, this is indeed a huge subject.

[@ManDaily]

Best

[@PinalDaveSQLAuthority]

Thanks

[@keep_digging]

Awesome

[@PinalDaveSQLAuthority]

Thanks!