ColumnStore Indexes in sql server | sql server interview | sql server tutorial | part 5

[@sonuchouhan9271]

Awesome explanation.. Thanks for sharing

[@ssunitech6890]

Thanks dear share to others..

[@deepak7s1]

Great Video for performance tuning..

[@ssunitech6890]

Thanks Deepak

[@ShivamKashyap-il7jl]

This is Very Good tutorial 👍 Thanks

[@ssunitech6890]

Thanks Shivam for your continuous watching and appreciation...

[@akhileshkumarakhileshkumar8644]

very helpful video..

[@ssunitech6890]

Thanks dear 😉

[@sunnyverma419]

great explanation

[@ssunitech6890]

Thanks Sunny..

[@anantshukla2201]

Nice video sir

[@ssunitech6890]

Thanks dear.

[@jaideepmalik1028]

Sir till now your sparse column video did’t came...... waiting for that video

[@ssunitech6890]

Hi Jaideep,
I will record video on sparse column for sure.
Now days busy with SSIS after that I will do.
Thanks

[@maheshkumar-vn6sn]

Nice video

[@ssunitech6890]

Thanks dear 🙏

[@munnaharun2431]

Sir... I have a doubt in this video...
Can u please clarify me the difference between clustered column stored index and non clustered column stored index

[@ssunitech6890]

Hi Munna,
In case of clustered Index- It include all the columns and it doesn't support large datatype previous 2016 version,and it also compress data upto 10%.
In case of Non Clustered Index- here we can specify column which we wish to use in index. and we can exclude large datatypes.It doesn't save any storage, but it add approx 10% space into new index.
Do subscribe and share to others.
Thanks-SS UNITECH

[@munnaharun2431]

@@ssunitech6890 yeah. Thank yu so much sir.
But i hv small confusion, if clustered colmn strd index includes all columns means then what is the diff bw clustered index and clustered column strd index...? Please clarify me sir

[@ssunitech6890]

Hi Munna,
These two are storing data different way, in normal clustered index- It stores data with all the columns in all the pages,
But in clustered columnstore index stores data columnwise each column data into different page ..
Thanks

[@munnaharun2431]

Hi @@ssunitech6890,
Thanks a lot sir

[@ssunitech6890]

Keep watching and share to others..

[@dharmvirkashyap8981]

Still helpful in 2024

[@ssunitech6890]

Yes

[@prakashsharma91]

I have doubt. Why would non cluster composite index improve the performance of select all rows by 50% ?

[@ssunitech6890]

Thanks for your comment Prakash!!
The performance is reduced because we have created non clustered index on Id and name columns and fetching only these 2 columns ...
For more detail about non clustered indexes.watch below video
th-cam.com/video/R4wbiGoDjIE/w-d-xo.html
Thanks-SS Unitech

[@prakashsharma91]

@@ssunitech6890 This video link provided show performance for query that has where clause. I have doubt regarding the query used in this video. select * from ColumnStoreIndex.

[@ssunitech6890]

This is because of columnstroed index. As I explained data will be stored with compressed and it doesn't have any space in SQL pages..and each column will use a separate page.. That's why performance is very good and we can't create columnstroed index in OLTP.

[@prakashsharma91]

@@ssunitech6890 Dude, can't you read English properly. I never asked about columnstore index. Asking about non clustered index on id and name.

[@ssunitech6890]

Non clustered index are stored separate from table. And we created it on id and name columns, and in select statement we are fetching only these 2 columns and data will return from index directly no need to lookup to table. That's why performance is good

[@sohailsyed5243]

Sparse column?

[@ssunitech6890]

I'll record a video on the same