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.
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..
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?
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.
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.
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.
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?
+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. ua-cam.com/users/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. ua-cam.com/video/y780MwhY70s/v-deo.html To receive an email alert, whenever a new video is uploaded, please subscribe using the link below ua-cam.com/users/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
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.
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.
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
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.
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 tutorial sir.. i am having a doubt ... at last where did the data stored and retreived..? is it from unique clustered index(indexes)...?
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?
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!
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?
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."
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.
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
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!!
thanks so much. I am learning SQL server from your tutorial. please upload more tutorial videos of Sql if there is more to learn..
SQL videos are very well explained, Thanks.
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.
Really ,I learnt lots from your video sir...and all doubts clr
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
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?
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.
No words to each issue you are discussing.
Your webcast are excellent.....Thanks a lot for ur good work
I think I have a place for indexed views but not where I had originally planned after watching your video. Thanks
Excellent tutorial. Thanks very much!!
Pls. Cover SSIS SSAS SSRS sir. Thanks for Your valuable Video.😊
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.
Superb Explanation!!
very good tutor
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.
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?
Excellent.. thanks
Thanks for the good work...
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.
ua-cam.com/users/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.
ua-cam.com/video/y780MwhY70s/v-deo.html
To receive an email alert, whenever a new video is uploaded, please subscribe using the link below
ua-cam.com/users/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
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.
can you make tutorials on java and kotlin?
Mr. VenKat thank you for your useful videos can you please explain the XML and how is it used Thank you
nice explanation :)
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.
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.
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
Excellent.!
gd explaination.........continue ur gdd wrk
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.
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
5 Stars for this module
wallah thank you bro
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)...?
It depends on what you have written in your SQL query for view "vwTotalSalesByProduct" based on that, it displays / gets
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
thank you sir...
Bro you said that indexed views are not suitable for oltp systems then what we can use for that purpose...
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!
Thank you
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?
Is there on demand refresh for Indexed View?
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."
Can we create index on view?
Sir, can you please explain the count_big function?
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.
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
can we add pk on view
?
views are basically for representational purpose only
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
SchemaBinding Option explained in Part 33 of this video series..
here's quick link to that Video - ua-cam.com/video/WNoTgfg3mGc/v-deo.html
What if data populated is non unique?
What is count_big() function?
Why indexed view's select query cant have count(*) ...why Count_Big(*)?
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