SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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 (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Methods for Accessing SQL Server XML Datatype – Quiz – Puzzle – 20 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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

From SQL Server 2000, XML has gained more and more popular due to its many applications. With SQL Server 2005 XML, became a first class datatype and applications are likely to use more natively. In line with this, what are the basic 5 methods provided by XML Datatype for access? When would you use one over the other?

Link to participate in SQL Quiz

Notes of Vinod Kumar

The standard methods of using XML are interesting. Read here for further details. Using the right option is what makes the usages remarkable, though. Just dig a little more in understanding Typed Vs Untyped XML as part of this learning.

Notes of Pinal Dave

XML is a very exciting subject. I often see two different kinds of opinion about XML; some developers like it very much while others just hate it. I have seen expert developers sweltering when they have to deal with XML.

Let me ask you one question: “If a webpage has 100 of the form element and we want to capture all of those which are filled, how would you do it?” See, there are cases where XML just works and moreover it works like a charm. Learning how to read XML data is essential.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#137-139
SELECT * FROM XML
Shredding XML
Validate an XML document in TSQL using XSD
Simple Example of Reading XML File Using T-SQL
Simple Example of Creating XML File Using T-SQL

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 (http://blog.sqlauthority.com)

SQL SERVER – MERGE or INSERT, UPDATE, DELETE – Quiz – Puzzle – 19 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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

In any performance-testing discussion for a huge, significant development, one of the common requirements is to send multiple values to SQL Server in a single shot. So to help achieve this this requirement, SQL Server introduced the MERGE statement. What is the construct of a MERGE statement? What are the restrictions of using the MERGE?

Link to participate in SQL Quiz

Notes of Vinod Kumar

A MERGE statement has great benefits as ETL requirements. Read for more details. As you use them in relational engine, can you tell how you can get details of how many INSERTS, UPDATES and DELETES have happened as part of the database?

Notes of Pinal Dave

What is UPSERT? It is commonly used word for INSERT and UPDATE. How about adding DELETE to the UPSERT as well – what will you call? I will call it MERGE. When I came across MERGE I was very happy as it was not only INSERT, UPDATE and DELETE but if used correctly there were many advantages associated with this operation. Initially, it was very hard into use MERGE statement as I was so used to writing INSERT, UPDATE and DELETE. As I kept myself open to this new procedure; I learned that this can be very helpful. To master any feature, solution or tool, one needs to learn about the limitations of MERGE . Today’s question is based on the main questions above: “What is MERGE and what are the restrictions of the MERGE statement?”

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#112
Merge Operations – Insert, Update, Delete in Single Execution
Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
Explanation SQL SERVER Merge Join

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 (http://blog.sqlauthority.com)

SQL SERVER – Importance of Resource Database – Quiz – Puzzle – 18 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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

As an effective DBA, it is critical to keep updated on the various server side databases. Recently, I encountered a Developer who was saying that the “sys” schema objects were no longer in the MASTER database. Interestingly, what is the use of RESOURCE Database inside SQL Server? Tell us one change in RESOURCE Database inside SQL Server 2008 R2 edition. Also, how can we find the properties of the RESOURCE database, like what its version is, etc.?

Link to participate in SQL Quiz

Notes of Vinod Kumar

RESOURCE databases are special databases. And these were created separately for a specific reason. Can you also tell how we can find the details of a RESOURCE database version, and other description of this database? In addition, in your plan to backup the system databases, are you also planning to take the backup of the RESOURCE database? Answering these questions can lead you to the right answer to the SQL Quiz question mentioned earlier. Tell me your views.

Notes of Pinal Dave

Today, instead of hint and sound bytes, let me give a question back at you. How many system databases are there in SQL Server? I often end up receiving the answer “FOUR”.

I think that during the course of SQL Server evolution, DBAs and developers frequently miss their respective rhythm to keep updated. I often say that there’s no empty knowledge; we can find small knowledge from everything. At one place, I have seen a developer deleting a resource database, then SQL Server began to face issues when it was attempting to start. This experience tells us that resources database is very important and it has a specific and crucial use. Honestly, every single system database is very significant, and one should know more about them and keep their knowledge updated.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#104
Resource Database ID – 32767
Location of Resource Database in SQL Server Editions
mssqlsystemresource – Resource Database
Importance of Master Database for SQL Server Start-up

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 (http://blog.sqlauthority.com)

SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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

Auditing is always fun when we do code reviews. And in a product back-end check we found a lot of junk names for the Default constraints inside the database. What are the different ways you can use to create a CONSTRAINT inside SQL Server?

Link to participate in SQL Quiz

Notes of Vinod Kumar

CONSTRAINTS are critical from a business requirement as we design the databases. Read more about them. Are you still using the RULE mode? Well, be careful because this is getting removed in the future release.

Notes of Pinal Dave

When I heard of a question about how many different types of ways there are in order for us to create a constraint, I could not believe the answers given to this, at first. Maybe it’s because I find it a bit confusing at times. However, I think the real answer to this question can be achieved if we understand the fundamentals properly. It is important to know how many types of constraints exist in SQL Server before trying to figure out how many different ways the constraints can be created. There are also cases when the constraint has not only helped maintain data integrity but also performance.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#137-139
Prevent Constraint to Allow NULL
Create Default Constraint Over Table Column
Create Primary Key with Specific Name when Creating Table
Creating Primary Key, Foreign Key and Default Constraint
How to ALTER CONSTRAINT
Disable CHECK Constraint – Enable CHECK Constraint
Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

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 (http://blog.sqlauthority.com)

SQL SERVER – CHECKPOINT Behavior and Database Recovery Models – Quiz – Puzzle – 16 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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

Recently, a DBA said that the Log files were growing bigger than the data file size. During the discussion, he mentioned using CHECKPOINT to solve this problem. In relation to this, what does CHECKPOINT do inside SQL Server? Is the behavior change based on Database recovery models?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Database CHECKPOINTS are interesting. Read more about them. And, as you read more and more, you will surely stumble upon these related questions: What are the correlations of RECOVERY models and their effect? Do dirty records also force flushed to data file during CHECKPOINT? As a DBA, go ahead and play around with this; you’ll find out this is an interesting exercise, trust me.

Notes of Pinal Dave

CHECKPOINT is very exciting concept. I have seen how this function is used, and mis-used. The only advice I have for everyone who is not familiar with this command is that NEVER experiment with it on your production server. In one of the recent technical presentations, I noticed that a few attendees were using CHECKPOINT incorrectly. When I asked a few follow up questions, I learned that their understanding of the dirty records was incorrect. All the advanced concepts require a solid understanding of the basic concepts. Without the basic understanding one person can never master the subject.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#137
Target Recovery Time of a Database – Advance Option in SQL Server 2012

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 (http://blog.sqlauthority.com)

SQL SERVER – Difference between CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) – Quiz – Puzzle – 15 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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

It is a common saying that, “where you should use a nail, you should not use a sword”.  Every little tool has its own usage and its own purpose. What are the differences of CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) datatypes?

Link to participate in SQL Quiz

Notes of Vinod Kumar

As we start moving our old ways of using “text” datatypes to VARCHAR(MAX), what is it that we get as an advantage? Read about more specific advantages. And while you’re at it, you might as well answer this question: “What is the storage requirements and special cases when using these datatypes?”

Notes of Pinal Dave

When people ask me why there are similarly looking features that exist in any product, here is my response: In the real world, even identical twins are not really identical. Likewise human finger prints are not alike. In the same way, most products do not come up with two similar features. Every feature has its own usage and specific advantages. Therefore as a product consumer, it is our duty to understand the feature correctly and try to take advantage of its positive side. With regards to CHAR, VARCHAR and NVARCHAR, there are three major components to pay attention to: 1) Storage, 2) Performance and 3) Capabilities.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#85-87
Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types
Constraint on VARCHAR(MAX) Field To Limit It Certain Length

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 (http://blog.sqlauthority.com)