Click here to get free chapters (PDF) in the mailbox
Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.
Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.
SQL Quiz
A new trainee was confused when he discovered that the organization is discouraging the Indexed View (or materialized views). After talking to the senior team members he realized that Index Views which are good at certain tasks it comes with lots of restrictions. What are the restrictions of using INDEXED VIEWS inside SQL Server?
Link to participate in SQL Quiz
Notes of Vinod Kumar
Not that I am a great supporter of using INDEXED VIEWS, but there are obviously some special case scenarios. This Hint gives some amazing documentation around its usage.
Notes of Pinal Dave
As far as I can remember, Views are one of the very old concepts. Earlier in my career, around year 2000, Views were extremely useful to me. I used to rely on them for many reasons – preventing access to the data to users, hiding table definition from users, creating modular T-SQL logic, using materialized views for performance, you name it. However, I gradually started to use stored procedures and user access security features leading less and less use Views. However, I sometimes use Index Views for special cases until now. And these special cases are what exactly we should learn today.
Additional Notes
SQL Server Interview Questions and Answers ISBN: 1466405643 Page#50
The Limitations of the Views – Eleven and more…
Interesting Observation – Index on Index View Used in Similar Query
Outer Join Not Allowed in Indexed Views – Limitation of the View 8
Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
Prize
There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.
Link to participate in SQL Quiz
Note: The SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.
Reference: Pinal Dave (https://blog.sqlauthority.com)
