Awesome explanation of things that we think are only capable of be done in a single way, because it is the default. But there are multiple reasons and explanations for alternative ways of implementation for each scenario. Thanks, Pinal!
@@PinalDaveSQLAuthorityThanks a lot for the videos, are great...In Mexico we use a slang word for great, we say "chingón" to mean something is great..Your videos are "chingones".
Very informative video Pinal, can you please make video on how to update a table with 6 billion rows having clustered column store index on that and non clustered primary key.
Great insight. Thank you Dev. Have a question, If I don’t create clustered index and create a non clustered index on a table then what will be the base of non clustered index? As per my knowledge the leaf node of non clustered would be based on clustered index row ids. Is it not true?
Hi Sir, I am having following problem one of the stored procedure.. The stored procedure is calling through SSIS package and this package is running in SQL Server Agent job daily based on schedule time.. My problem is After completing the job.. In recent month few data points are not loading and rest of the data points I can able to see the recent month data points. . if i run same Stored procedure in SSMS i can see all the data points .. not able to figure out what is the issue .. could you please let me know what could be the reason in this scenario..looking for your valuable input..Thanks in advance.. version of the SQL Server is 2012
Sir one question, can we create noncluster columnstore index on primary key and drop cluster index on same column, and please tell me if this will help performance while table having 1.5 billion rows in it
It is very common use case in datawarehouse. Also cluster index key is repeated in nonclustered index so sometimes it is important to have them different.
@@PinalDaveSQLAuthorityFor child tables, is it a good idea to create the FK against the master table clustered and the PK non clustered? Could this work better against locks/deadlocks?
Sir Thanks for clearing the biggest lie of my life for SQL. Just wondering, why would someone use 3rd scenario from the video? How is it going to help?
Hello Sir, Thank you for the video. I learned lot from you. I have table with records of 45lakhs so can I used Clustered index or Non clustered index. When I used a non clustered index on composite key , it works very fast as compared to clustered index. please let me know your thought sir.
Strictly speaking sql adds hidden data and sorts by that. I’ve had loads of tables where the clustered index is not the primary key, but it’s always worth having a clustered index.
Awesome explanation of things that we think are only capable of be done in a single way, because it is the default. But there are multiple reasons and explanations for alternative ways of implementation for each scenario. Thanks, Pinal!
Thanks so much
You made my day 😅
Great video and demonstration.
Thanks pinal 🙂👍
My pleasure!
Thanks Pinal...this clearly clears the misconception for lot of people
Thanks for your kind words.
This was very interesting Pinal, thanks for the Demo.
Thank you so much!
Very nice, You are always suggesting good tricks..
Thanks
Love your info Pinal, thanks a bunch!
Thanks for watching
Great video Pinal Dave sir. Please create a video on columnstore clustered index
I already have one on rh8s blog. Let me create one more soon.
Thanks a lot sir👌🏻👍🏻
So nice of you
But which is the best approach? Clustered or NonClustered Primary Key for huge data volumes?
I wish I could answer that one. This one is indeed difficult to answer without doing the test.
Thanks a lot, greetings from Mexico.
Mucho Gracias
@@PinalDaveSQLAuthorityThanks a lot for the videos, are great...In Mexico we use a slang word for great, we say "chingón" to mean something is great..Your videos are "chingones".
@@israel1919 You made my day amigo!
Very informative video Pinal, can you please make video on how to update a table with 6 billion rows having clustered column store index on that and non clustered primary key.
Just drop the indexed first...
sir any video how function impact query performance in select or in where clause .
Currently blog post on sqlauthority. Just check.
Good information. Thanks you so much.
My pleasure
Great insight. Thank you Dev.
Have a question, If I don’t create clustered index and create a non clustered index on a table then what will be the base of non clustered index?
As per my knowledge the leaf node of non clustered would be based on clustered index row ids. Is it not true?
Row ID
Thanks Pinal, very useful!
Glad that you liked it. I am so happy that you watch my videos.
Thanks sir...Very informative... 🙏🙏
You are welcome
Thanks sir for this video.
i have one doubt ,can you create one video for difference between
single column index vs multicolumn index.
Sure here is one...th-cam.com/video/pak2cPE1Y-g/w-d-xo.html
Hi Pinal Dave iam reading and following your block from last 5 years if possible put detailed explanation of execution plan complete operators
Sure...I have done many videos and few more planned.
Hi Sir, I am having following problem one of the stored procedure.. The stored procedure is calling through SSIS package and this package is running in SQL Server Agent job daily based on schedule time.. My problem is After completing the job.. In recent month few data points are not loading and rest of the data points I can able to see the recent month data points. . if i run same Stored procedure in SSMS i can see all the data points .. not able to figure out what is the issue .. could you please let me know what could be the reason in this scenario..looking for your valuable input..Thanks in advance.. version of the SQL Server is 2012
This i am not sure I can answer with limited information.
Thanks Pinal for useful info
You are welcome.
Sir one question, can we create noncluster columnstore index on primary key and drop cluster index on same column, and please tell me if this will help performance while table having 1.5 billion rows in it
I prefer to have a clustered index on table as it usually helps multifold with performance.
@@PinalDaveSQLAuthority ok 👍🏻 thanks sir
What's the use of primary key with non clustered index, as we can't insert duplicate data into non clustered primary key
It is very common use case in datawarehouse.
Also cluster index key is repeated in nonclustered index so sometimes it is important to have them different.
Oho.. understand ur point... and ur vedio share greate information.. thanks..
Thanks Very easy to understand
Great to hear that.
What scenearies would be useful to have a PK non-clustered?
When you want clustered index some other columns.
@@PinalDaveSQLAuthorityFor child tables, is it a good idea to create the FK against the master table clustered and the PK non clustered? Could this work better against locks/deadlocks?
Very interesting and useful 👍
Glad that you watched it.
Sir Thanks for clearing the biggest lie of my life for SQL. Just wondering, why would someone use 3rd scenario from the video? How is it going to help?
It is very common in datawarehouse. They want clustering index to be smaller.
Hello Sir,
Thank you for the video. I learned lot from you.
I have table with records of 45lakhs so can I used Clustered index or Non clustered index. When I used a non clustered index on composite key , it works very fast as compared to clustered index. please let me know your thought sir.
It is difficult to answer without providing all the necessary details like schema, data, statistics, etc.
Sir i will share details with u.
hi , with a Primary key / Non-clustered index, does it mean Duplicate is allowed on the Primary key field ?
In PK no duplicate value - clustered or not clustered.
pk noncluster seems very useful with partitioned tables, what do you think Pinal Dave?
Very difficult to say without looking into the whole partitioning scheme. However, in general terms yes, I agree.
@@PinalDaveSQLAuthority thanks for your time reading the comments 🙌
Hi pinal what is the birth place of index / data page? (buffer or data file?)
From data file to buffer.
thanks for the demo
Glad you liked it.
Strictly speaking sql adds hidden data and sorts by that. I’ve had loads of tables where the clustered index is not the primary key, but it’s always worth having a clustered index.
Totally agree.
Good one. Thanks.
Glad you liked it!
Thank you
You are welcome
Awesome trick
Thanks
Very intresting....
Glad you liked it
That is really interesting
Gald you liked it.