Clustered and nonclustered indexes in sql server Part 36

Подписаться 835 тыс.

Просмотров 956 тыс.

50% 1

In this video we will learn about
1. Different types of indexes in sql server
2. What are clustered indexes
3. What are NonClustered indexes
4. Difference between clustered and non clustered indexes
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our RU-vid channel. Hope you can help.
/ @aarvikitchen5572
Text version of the video
csharp-video-tu...
Slides
csharp-video-tu...
All SQL Server Text Articles
csharp-video-tu...
All SQL Server Slides
csharp-video-tu...
All Dot Net and SQL Server Tutorials in English
www.youtube.co...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic

Опубликовано:

1 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 322

@kanyagudipati8605 9 лет назад

Hi Venkat You are gifted and talented teacher. I think people who want to learn SQL they first open your videos.

@jurianbraham3386 4 года назад

Man!... you have taught me so much and it's not just the quantity but the quality of your content is priceless!

@pradyfunvlog 9 лет назад

sir i think there is a mistake in slide on time 15:57 ,there should be ..the NON clustered index has to refer back to the table as u explained on 15:05 to 15:35

@asthatripathi9211 6 лет назад

thats what i thought

@findmangesh 4 года назад

I was about to mention the same. Thanks.

@LeonaMeyer 3 года назад

I agree. It should be changed. I thought I was going mad...

@jag7450 2 года назад

I did not catch the mistake, has that been fixed?

@Csharp-video-tutorialsBlogspot 11 лет назад

Thank you very much for taking time to give feedback. In the description of this video, I have included the link for ASP .NET, C#, and SQL Server playlists. All the videos are arranged in logical sequence in these playlists, which could be useful to you. Please share the link with your friends who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video. For email alerts, when new videos are uploaded, you may subscribe to my channel.

@abdullahmohammad5613 2 года назад

Thank You! How do you highlight an entire line at once in sql server?

@asharibahmad5267 Год назад

@@abdullahmohammad5613 ctrl+shift+End key

@TopHatdudeman 3 года назад

Once again I have been saved by random indian guy on youtube. I am on my final interview at my dream job because of you. I really appreciate the help sir!

@arunjohny 3 месяца назад

Did you get the job? Tomorrow is my interview

@priyankarora3461 8 лет назад

Hi Venkat, In the differences slide, in point 2, could you please correct the statement "clustered index has to refer back to table" to "non clustered index has to refer back to the table." Thank you!

@LeonaMeyer 3 года назад

Yes this statement is incorrect and is very misleading

@Csharp-video-tutorialsBlogspot 11 лет назад

Yes, we can think it that way. Smart thinking. Thanks for taking time to leave your thoughts.

@wenyizhang3942 5 месяцев назад

Thank you so much, your tutorial from 11 years ago still teaches me a lot!

@georgewu5512 9 лет назад

You are the best!! This is the easiest way to learn SQL that I have found. You are very clear, concise, and give real examples on how to use SQL. Thanks so much.

@zheng-guangwang5112 10 лет назад

Hi, there might be a typo in the slide that summarizes the "Difference" between Clusterred and NonClusterred. Non clustered index has to refer back to the data table if the column is not found in the index. In the slide, it reads that clustered index has to refer back... is that correct? Thanks.

@PravinKumar-qv9zd 7 лет назад

Yes, you are right...

@Csharp-video-tutorialsBlogspot 11 лет назад

Hi Kunal, the PPT's are available on my blog. You can find the link to my blog in the description of this video.

@Csharp-video-tutorialsBlogspot 12 лет назад

1. Yes, it is suggested to create a clustered index on a column that will be queries the most. 2. The column in the where clause should be included in the index. 3. SQL server uses unique index to enforce the primary key. So, if you already have a clustered index on a column other than the primary key, then a unique non-clustered index needs to be created on the primary key column to enforce the uniqueness.

@Csharp-video-tutorialsBlogspot 11 лет назад

Hi Shiva, sorry for the delayed response. You are 100% correct. Thank you very much for offering to help answer questions posted by others. May I ask you for a favour. I want these tutorials to be helpful for as many people as possible. Please feel free to share the link with your friends and family who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video.

@sukritikhatri3692 4 года назад

Can non-clustered indexes have multiple columns like the composite clustered index?

@Salty_Matter 4 года назад

Many links, videos, teachers explain these concepts in such a complicated way, maybe because they want to show off their knowledge or intelligence by making it complicated and then explaining it. But you Venkat have made it so simple to understand and Digest, that I love watching all these videos. I think this world is a better place because of people like you who make complicated stuff easy.

@Mr__B. 2 года назад

Brilliant explanation! I've read dozen of pages and still had question, but you explained everything in 15 minutes!

@shaileshgande3875 2 месяца назад

There's a mistake at 15:58 point no.2 "Because the non clustered index has to refer back to table"**

@deepaknanaware1066 Год назад

very fast trainer ......take it slow

@nimeshambalalpatel3032 3 года назад

Excellent teaching sir!! Great ! my pranams to you

@prateekrocks25 9 лет назад

I got tired searching through internet to find a simple explanation clustered and non clustered index. And my search ended with this video. Couldn't expect better. Kudos !

@YoeXCheneguein 8 лет назад

Owww amazing explanation bro. it´s help me a lot, thanks you so much. :D

@Csharp-video-tutorialsBlogspot 8 лет назад

+YT LWT Thank you for taking time to give us your feedback. This means a lot. I am very glad you found the videos useful. Free Dot Net & SQL Server video tutorials to help you become a web developer ru-vid.complaylists?view=1&sort=dd DVDs with all the videos and slides for offline viewing www.pragimtech.com/Order.aspx Slides and Text Version of the videos can be found on my blog csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-y780MwhY70s.html If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. ru-vid.com If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Best Regards Venkat

@murugesankgm1297 3 года назад

Very good teaching. Can you post a playlist about advanced sql server.

@satyanarayana8320 3 года назад

Thank you very much sir for u r kind information.This information is very useful to me..

@malharjajoo7393 8 лет назад

Can a table have both clustered and non-clustered indices ?

@AhmedMGalal 4 года назад

Of course you can do that, but depends on the business requirements...

@ChristianJensen-gu8ne Год назад

This is the best explanation I've found so far for this subject. Thank you so much for all your tutorials, they've really help me a lot.

@akhileshguptaakhi 4 года назад

Dear sir, what is the use case of creating a non-clustered index, when we already have clustered index on our table

@srini3993 4 года назад

You are generating lights in each home and family of a software guy... Because of Your explanation which we are living life right now... Thankyou so much.. Expecting many more videos from you

@sanjaypatel3506 8 лет назад

gr8 explaination ! ...However, there is some tying error (TYPO) in point 2 of the Differences screen at the end. * Non-clustered index has to refer back to the table. - - word (Non) is missing. I hope this help you to correct the very small error in typing... Thank you.

@upwalebhaiya2477 3 года назад

Thanks.... Today first time I have been able to understand this topic... Properly.... Thanks a lot...u r a boon to students....

@ankanmazumdar5000 4 года назад

God cant be everywhere, Hence he has sent Venkat to us.

@pear7777 4 года назад

ok, quite e deep in on clustered, but not so much on non clustered?

@picabloo 10 лет назад

I understand but what's the purpose of a non clustered index? How would it be useful?

@reyou7 10 лет назад

Clustered Index: You can iterate through data much faster because it is the natural order of the table. You can use it for paging purposes. Non-Clustered: You can search data much faster, because you have a reference to that column to search like names, salaries etc..

@AbhishekBose987 10 лет назад

From no where I got a clear cut explanation of clustered and non clustered indexes in sql server, but from this video.

@v2vlog622 3 года назад

One of the best explanation about indexes. For non clusters index Christian song book, songs are ordered alphabetical order wher you can find the page number of the songs. Since songs are printed in topic order.

@Csharp-video-tutorialsBlogspot 11 лет назад

Hi, it depends on the execution plan that is generated by the sql server. Hope this answers your question.

@HafizMazhar 4 года назад

Love your way for clarification of the concepts..

@malharjajoo7393 8 лет назад

More info for those interested - The keys of the index are internally stored as a structure ( B - tree ) that is associated with a table/view

@jmonagas 10 лет назад

WWOOWW thank you so much !!!

@martingrey738 9 лет назад

Mr Venkat im very impressed, that you made this videos. Thank you a lot, you are great guy. Greetings from Poland.

@walidkadhim1337 10 лет назад

You managed to explain a subject that I really was struggling with. Thank you very much!!

@trinadhkadari1213 8 лет назад

Thank you venka..for explaining in clear manner..

@aambalkamaraj 9 лет назад

In SQL Server 2005 you can have only 249 Noncluster indexes and in SQLserver 2008 you can have 999 Noncluster indexes.

@Nicky411 3 года назад

This was REALLY excellent!

@johnr3370 7 лет назад

Big Thanks to you, I got a conceptional clarity with a simple abstract example But, I felt non-clustered index is not explained enough with a real-time application. The example which you have done for the non-clustered index is still abstract only.Honestly, I have never seen DB people are explicitly doing non-clustered indexing for any scenario/purpose. As you have shown, PK setting will automatically become clustered index similarly any other key setting will make non-clustered key?

@ferrarikarthik1 12 лет назад

Very well explained...thnx!! ..BTW have a few questions. 1.Where is the non clustered index stored??is it like in the memory or where? 2.And if we write select name from tblEmployee...how does the sql server know it has to refer the non clustered location. i.e, where is the link between the table and the non clustered storage?

@bogdan13grbea 10 лет назад

Great videos. Thank you!!

@djjiang3718 4 года назад

Hello Venkat, great tutorials ! and You are such a great teacher !

@nammozhi819 2 года назад

Want to learn something related to sql..type topic name and kudvenkat videos..you will land up in the best place

@data9051 2 года назад

what is best practices for database optimization? Should you ALWAYS re-create a Clustered Index for lookup optimization as long as the data being ordered by id is not important? Should you typically create a non-clustered index on most tables on the most important/common columns that are typically used in WHERE clauses? Thanks, I am thinking the answers to these questions is yes, to optimize the speed and I am not sure how much disk space the non-clustered IX takes up, but I am guessing it is nominal.

@santhoshb4545 3 года назад

Hi sir, small doubt in clustered index. Clustered index can contain multiple columns ex. Gender and Salary. In your example male salary is 2500 3100 4500 and female is 5500 6500. So wen u do gender desc and salary asc u still get salary in asc 2500 3100 4500 5500 6500. But wat my doubt is wen male salary is 2500 5500 4500 and female is 6500 3100 so any column won't be in asc or desc. How it wil work. Please someone advice me

@arturoprepotente9837 3 года назад

As usual, this topic was well explained. Not all smart people can teach. Thank you, Venkat.

@Fcb447 7 лет назад

really well explanation, thanks alot.

@rajeshs8846 2 года назад

@Kudvenkat- Even if the data is sorted in the Clustered index, while looking for a record, the entire table has to be scanned rite? So the full table scan is happening here. How the performance would be faster than non-clustered index. Could you kindly explain?

@Sivareddy-x6s 7 месяцев назад

what is the default index of the foreign key. is clustered are non -clustered index my thinking its a non-clustered index bcz its allow null values and duplicates i am right or not please let me know. Thank you! in advance.

@hyagrivan 7 лет назад

As you said created index has been deleted only by object explorer without using of this is there any way thru queries.

@suchiravig 8 лет назад

Dear Venkat, as usual, all your videos are very clear and to the point. THANK YOU so much for all your efforts to make this happen.

@bhargava6621 5 лет назад

In-fact cluster index requires additional disk space. Here is the MSDN document to estimate the size of a clustered Index: docs.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-clustered-index?view=sql-server-2017

@prakashsarraff3393 9 лет назад

Explained in the best possible manner.

@surekhavani3859 11 лет назад

good explanation sir.i have a doubt.you told that there should be only one clustered index on a table if we have many then proper arrangement of data is not possible.if v have many non-clustered indexes i think then also v cannot arrange data in one way.Then how cum v can have any number of non-clustered indexes.

@Nikita12957 8 лет назад

As usual wonderful explanation Venkat Sir ! Just had one query ..can you please explain how can we decide which indexing should get used ?Is there any specific scenario ?

@sonamawasthi7471 9 лет назад

very clear explaination..thnks alot

@hellofriend2012 10 лет назад

My most favorite online tutor!!!!!!!!!!

@lakshmik5306 2 года назад

very helpfull content your delivering ..i have a doubt that if primary key exits to the column then it will automaticaly create the cluster index ...for composite cluster index ..you deleted the existing CI however the PK constraint is existed..so why again the CI index not get by automatically

@uniqueperls 10 лет назад

Thanks a lot, for this nice explanation of Clustered Index and Non-Clustered Index.

@dbloyd2 Год назад

When I did Informix decades ago, it clustered indexes was like that. You select from a table without an order by and it stores the data by that order. I believe it didn't keep the order as new rows were inserted and would have to rebuild it to keep that sorted order (never confirmed it). An update came out and mentioned it will not longer guarantee the sort order. There was valid technical reasons (parallel, multiple disks) but SQL standard says you have to specify an order by and you should never count on rows coming back in som pre-determined sorted output.

@AshisRaj 4 месяца назад

I was just wandering for this and my searching finished here. Thanks for the wonderful content Mr. Author. Best wishes

@knubbigfluffigkatt3972 5 лет назад

You explain stuff really well. Thanks for making me understand SQL.

@perfectgaming788 6 лет назад

Best Explanation on RU-vid by far. Great Job!

@breeze0n 9 лет назад

Thank you so much for this video ! It explains the concept very clearly. Thanks a lot !

@manojpasumarthi 6 лет назад

Simply superb. I have been struggling to learn about this from long time & u made it clear with in 15 min.

@RikdebMukherjee 8 лет назад

V nice tutorial

@anuragvashishtha6660 11 лет назад

Great explanation. I had a few issues with this topic so far, but your teaching has eliminated those doubts. The examples you consider are very easy to replicate and the way you describe is really awesome. Again a big thanks for the excellent work which you have undertaken.

@those1kidds 5 лет назад

your videos are awesome man. You're a great teacher, and that's coming from another fellow teacher who is trying hard to find good SQL materials that actually teach the content well. Your videos are just that. Great job sir!

@TraxxStudios 9 лет назад

Im pretty sure that couldn't be explained better. Thank you so much :)

@vgedits9368 2 года назад

Hello venkat I was enjoyed the course but I have question for you shall we disable the index or not ? If we disable what are the disadvantages

@rameshbevara3111 9 лет назад

This is a superb video which helps the people who doesn't have any idea about indexs

@christianeriksson7281 5 месяцев назад

How could it be approved to delete the unique clustered index by the object explorer when it was prohibited to drop it by the sql statement?

@vishaljhaveri7565 11 месяцев назад

I wish I had a girlfriend as intelligent as you are. Thank you so much for the videos.

@1989saransh 9 лет назад

very clear explaination..thnks alot

@chandrumaruthappan 4 года назад

VenkT - clustered index required for disk space and non clustered index not required the disk space...

@ahamedkabir2870 10 лет назад

Excellent Explanation.....

@malharjajoo7393 8 лет назад

A small mistake in the point 2 in the differences. It shoudl be NON clustered.

@telugudevotionalspeeches8971 2 года назад

Now only I got perfect idea about indexes. Thank you so much

@shubhamkamble4070 3 года назад

U forgot to give an example of Non clustered Index using SQL Server

@schnitzle 10 лет назад

That was so clear and easy to understand! I love seeing the examples of the actual sql code to create the clustered index table. Thanks so much :D

@asmitasharma888 10 лет назад

Great Vedio Thank you so much for your help!!

@tarocks4775 Год назад

Great video, just want to point out DESC and ASC on 'gender' does not makes sense since it's not a value

@tothedust 10 лет назад

Create Clustered Index IX_tblEmployee_Gender_Salary ON tblEmployee(Gender DESC, Salary ASC) Does this mean the table will have two clustered index i'm confused because Sir you have said there will be only one clustered index per table Anyone please help me out

@archrodney 9 лет назад

Void Gaze The answer to your question is here: 9:07 - 9:22

@sohailsyed5243 3 года назад

I am not able to find the column store index video on your list. could you please create a video on it? Thank you

@christianeriksson7281 5 месяцев назад

Where are the index rows of a clustered index stored? Together with the data rows in the same data pages?

@kunalpatil6057 2 года назад

this is excellent but had one doubt, if non-clustered index has seperated address per row, address is binary tree address right?

@bsarun7266 2 года назад

sir is this cluster index feature is support in 5.7 version or not?

@ranjithreddy8560 5 лет назад

hi can i know the need of non clustered index if table consists of a clustered index and if table is arranged by that clustered index why we should create non clustered which has no use in ordering for clustered index table

@tiagosilva856 2 года назад

When u deleted the clustered index of the primary key u also deleted the primary key?

@damonp2006 12 лет назад

I am assuming you would be forced to do this as only 1 clustered column is allowed right?

@krismaly6300 9 лет назад

Revisiting Thanks for educating

@fathimaanjabeen1630 4 года назад

Then why specify ASC in cluster index? If it stores in order

@di.vineapple 9 лет назад

Very clear, to the point. Thanks Venkat.

@stalinlouis8988 4 года назад

Explanation is good you showed how it is applied. But, how to execute that?

@bedathur9bharadwaj 11 лет назад

The order specified in the clustered index decides the physical order of the data in the 'table'. But, the order specified in the non-clustered index is for the order of data in the 'index', and this doesn't affect the physical order of the data in the table. So, we can have any number of non-clustered indexes. I hope this clarifies your doubt. @Venkat, If I am wrong, please correct me.