Indexed views in sql server Part 41
HTML-код
- Опубликовано: 6 сен 2024
- In this video we will learn about, creating indexed views. A unique clustered index, is the first index that should be created on a view. Indexed views are also called as materialized views
Text version of the video
csharp-video-tu...
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 RUclips channel. Hope you can help.
/ @aarvikitchen5572
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
Hi, thank you very much for taking time to give feedback. I am really glad you found these videos useful. To receive email alerts, when new videos are uploaded, please feel free to subscribe to my youtube channel. 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.
I was too lazy to comment before in a video where you demonstrating replace or mask function, but I have to say the practicality that you show of each concept is ingenious. Its really amazing for beginners as well as novices.
OLTP: Online transaction processing.
Great tutorial again, thank you very much for sharing!
Thanks a lot sir! You are changing many of our lifes by making such well explained videos! Kuddoos to you Sir!!
SQL videos are very well explained, Thanks.
thanks so much. I am learning SQL server from your tutorial. please upload more tutorial videos of Sql if there is more to learn..
You told index on view should be unique clustered index. And view is a virtual table. If i create unique clustered index it create index on underlying table?If it already having primary key , whether it allow create index? Clustered index tell physical ordering , this schema changes actual record ordering in underlying table?
Dear Venkat- Your tutorial are excellent, keep it up.
thanks for your help,your way of teaching & rate of speech are perfecting matched.
Very clear explanation, Thanks for those all videos. Simply Superb.
I think I have a place for indexed views but not where I had originally planned after watching your video. Thanks
Hi Venkat - These lectures are really well made and shows a deep level of effort that you have put in. Hats off to your commitment.
One question : After I created the view - I executed a select query on this and the result was expected - The Names of the products were generated in alphabetical order with the total Sales. Next I put in the Unique clustered index on the Name, but in descending order. Now when I executed the select statement - I got the same result as previous. Is this because the view is never really stored and instead has to refer to the table ? Please advise..
View doesn't stored manually until you create a index, remember we create clustered index on view which means it sorts the data physically
Excellent tutorial. Thanks very much!!
Really ,I learnt lots from your video sir...and all doubts clr
Your webcast are excellent.....Thanks a lot for ur good work
Superb Explanation!!
No words to each issue you are discussing.
Great tutorial. Thanks for uploading. One "nice-to-have" would have been to show the Query Execution Plan before and after creating an index on that view.
Great suggestion sir.
very good tutor
I like the SQL videos.
+Kuai Leong Wong I am very grateful to you for taking the time to give your feedback. Means a lot. Very glad you found the tutorials useful.
The playlists at the following link have all the videos in logical sequence. Please share if you find them useful.
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
If you want to download all our videos and slides or to order DVDs for offline viewing please use the link below
www.pragimtech.com/kudvenkat_dvd.aspx
Please find slides, text version of the videos and code samples on my blog
csharp-video-tutorials.blogspot.com
For simple tips to use our training channel.
ruclips.net/video/y780MwhY70s/видео.html
To receive an email alert, whenever a new video is uploaded, please subscribe using the link below
ruclips.net/user/kudvenkat
If you like and think the videos are useful, please click the THUMBS UP button.
We love to see our videos helping others as well. Please help us by sharing the link with your friends and family on your social media network
Thanks
Venkat
Hi venkat sir, I very big fan for you , i love so much for your way of explanation about session and topic that is very help full for while requirement to data through of view , And one more thinks, Just add some real example with every topic how use it is view with index in developer an real project.
Pls. Cover SSIS SSAS SSRS sir. Thanks for Your valuable Video.😊
Hi Venkat Sir.. I am good fan of you. I love and love a lot that your way of explaining the concepts.. It's superb. I was inspired by you.
I have one doubt about indexed views that's, is it need to drop and recreate everytime when base table data change?
I understood from your explanation, there is SCHEMABINDING option should not allow to modify base tables that means we need to drop the indexed view if we want to do any changes to base tables. It is difficult to drop and create everytime when changes need to do for underlying base tables. For this, be any useful techinique? like creating a trigger on table for DML operations and capturing the dependancy and then drop that indexed view and recerete it after changes allowed. I think, it is not suitable for OLTP databases. It is suitable for OLAP databases. Could you please give any suggestion on this?
Thanks for the good work...
wallah thank you bro
Excellent.!
Excellent.. thanks
Actually i just want to say that in the above example , Index on Name was not practically clear to me. But Indexes on Sales or TotalTransactions would help in speeding up searching queries like Find Sales between some range or highest, lowest no of transaction.
If you create the index in the way we did here (only the name), does this index include also the corresponding aggregation values ? I suppose, yes, otherwise we would need to go back to the underlining tables again
nice explanation :)
I have a question, when record in an underlying table is updated or created, does the entire View is updated or only the records affected by the newly added/modified rows? This is improtant to know for me so I can determine how expensive the update will be in case a View takes 5+ times or longer to update.
gd explaination.........continue ur gdd wrk
thank you
can you make tutorials on java and kotlin?
It depends on what you have written in your SQL query for view "vwTotalSalesByProduct" based on that, it displays / gets
thank you sir...
There is one query, I actually dropped the view, then inserted two more rows in Productsales table, again created the view, still I am getting the table similar to the previous one, is there something I am doing wrong? Please advice!
Hi Sir , View doesn't stored manually until we create a index, remember we should create clustered index on view which means it sorts the data physically. till now i got the point. But i am confused that we should create UNIQUE Clustered index , because base table might not have any column which doesn't have unique values/names/,,. Could you please briefly explain on this
Thanks for the nice informative videos , But can you please recheck the point that index view are same as materialized view in oracle , Cos materialized view can get refreshed automatically but in index view that needs to be dropped and recreated if the underlying table data is changed.
Great video .I have one question If a row is updating in base table will it automatically update in indexed view or how this views is refresh.
SchemaBinding Option explained in Part 33 of this video series..
here's quick link to that Video - ruclips.net/video/WNoTgfg3mGc/видео.html
What to do? I m getting below error message while creating unique clustered index:
"Msg 1962, Level 16, State 1, Line 1
Cannot create index on view 'SSTB.dbo.vwtblTxnBySalesProduct' because column 'ProductId' that is referenced by the view in the WHERE or GROUP BY clause is imprecise. Consider eliminating the column from the view, or altering the column to be precise."
Mr. VenKat thank you for your useful videos can you please explain the XML and how is it used Thank you
If SQL server developer edition is used, "WITH (NOEXPAND)" should be used to use the actual indexed view in the query.
Eg: SELECT * FROM [dbo].[vWTotalSalesByProduct] WITH (NOEXPAND);
Otherwise SQL server will just use the execution plan used for the equivalent normal view.
5 Stars for this module
Question: How to update the results if the base tables updated? Just update the index? 2. What if I alter the view, then after I also need to update the INdex?
thanks for the tutorial sir..
i am having a doubt ...
at last where did the data stored and retreived..?
is it from unique clustered index(indexes)...?
Bro you said that indexed views are not suitable for oltp systems then what we can use for that purpose...
After creating unique clustered index for view, what if i drop the base table. will the index will have the records as it stores physically or we will get error?
Probably an error since the view references the base table, right?
You'll not be able to delete the base table as we have created view with schemabinding
Hi Venkat i need your i hard view on database called First Term , Second Term and Third term but i need to group them again to add all subject mark from first term to third term
Is there on demand refresh for Indexed View?
Why indexed view's select query cant have count(*) ...why Count_Big(*)?
views are basically for representational purpose only
Sir, can you please explain the count_big function?
Can we create index on view?
What is count_big() function?
can we add pk on view
?
Can we add 2pks on table?
No, a table can only have one primary key. Having multiple primary keys in a table violates the fundamental principles of relational database design
What if data populated is non unique?
great content, but videos quality is not good . please can u correct this. then its a perfect series
.
See first value of total value wrong
Thank you