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
Even though storage as a subsystem is cheap these days, we have seen several DBAs struggling to find Log files run out of space and stop transactions. Understanding the size of Log files is a critical aspect. And now, table data, index data, log data and other related data are becoming important competencies of an effective DBA. In relation to these, how do you enable and disable an Index? What is the behavior of the size when compared to dropping an Index? How does storage change?
Link to participate in SQL Quiz
Notes of Vinod Kumar
Understanding the Indexing basics will demystify the differences of how Clustered Index and Non-Clustered Indexes work. The nuances of how the de-allocation happens are completely dependent on the specifics of the version. In line with this, it would be helpful to learn about the formula for calculating the size of a non-clustered index.
Notes of Pinal Dave
I admit – there was a time when I had absolutely no clue about the difference between CREATE and DROP Indexes versus REBUILD Indexes. Not knowing this simple fact, I have often used each other in place where I should have not used it. There are many differences between them; the one which stands out the most is related to performance as well storage. After sometime, I came to learn about this so I started practicing what is right and more appropriate. Today, I have the puzzle put in front of you. Do you really know the difference between them or do you just use whatever comes to mind first?
Additional Notes
SQL Server Interview Questions and Answers ISBN: 1466405643 Page#149
Unused Index Script – Download
Missing Index Script – Download
Disable Clustered Index and Data Insert
Generate Report for Index Physical Statistics – SSMS
Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
Disabled Index and Index Levels and B-Tree
Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats
Size of Index Table for Each Index – Solution 2
Computed Columns – Index and Performance
Index Created on View not Used Often – Limitation of the View 12
Video – Performance Improvement in Columnstore Index
Prize
There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.
Link to participate in SQL Quiz
Note: 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)
