SQL SERVER – Different Aspect of Policy Based Management – Quiz – Puzzle – 25 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

Compliance is one of the non-negotiable things in this era. SQL Server has had so many options like C2 Auditing since the SQL Server 2000 days. With the advent of Policy Based Management inside SQL Server 2005, what are the different aspects involving PBM one needs to know?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Tough Auditing is a great way to monitor SQL Server, but when you try to explore what all methods does the PBM give when compared to Auditing, Policy Based Management can help you take the management of SQL Server to the next level better than Auditing. Also, there are some interesting ways to extend and make your own custom policy using special commands. To learn more, explore these concepts and tell us your views.

Notes of Pinal Dave

Have you ever desired to know who has touched your data? In this tightly conformed world, it is very important to know who has an access to the data and which among the data has been accessed. There are important cases when some data are never accessed by anybody. Preventing data access is one story, while being a DBA who might have his best practices is another story. A DBA may never want the Auto Shrink to become activated in their databases. All these and more can be done using Policy Based Management.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#154-155
Policy Based Management – Create, Evaluate and Fix Policies
Introduction to Policy Management – Enforcing Rules on SQL Server

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 – Correct Value for Fillfactor – Quiz – Puzzle – 24 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

Any database design can include only the Normal forms. But while a DBA performs a product deployment, it is critical to take care of certain factors for Index creation. With this noted, explain FILLFACTOR and PATFACTOR inside SQL Server. What are their uses and when would you use each of them?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Tough FILLFACTOR is used from the side of performance tuning techniques. It also has an impact on storage needs – the IO increases for Reads if done incorrectly. This Hint has the information related to these notes. Is there a skill that you use regarding this matter? Feel free to share.

Notes of Pinal Dave

Fillfactor is an interesting concept. When I tell several DBA and developers that fillfactor 0 is the same as 100, I have seen them not believing what I said. An appropriate fillfactor can have a heavy impact on performance. The fillfactor can be adjusted at server level as well as at index level. A similar concept is PATFACTOR. I have seen it being abused, leading to a terrible performance on server. Aside from fillfactor, there are many additional performance tuning concepts one needs to master if performance is his primary concern for database.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#151-153
SQL SERVER – What is Fill Factor and What is the Best Value for Fill Factor
Fillfactor, Index and In-depth Look at Effect on Performance
Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor
Change Default Fill Factor For Index
Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

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 – Database Mirroring and Fine-Prints – Quiz – Puzzle – 23 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

Though high availability is something critical for all large database deployments, there are other multiple options one can use. For today’s discussion, what is database mirroring option? What are the Database Mirroring enhancements done in SQL Server 2008 R2 edition? How is it different from Log shipping?

Link to participate in SQL Quiz

Notes of Vinod Kumar

DBM is a very famous method of HA options that I have seen enterprises use in their environments. The concepts are simple and well-explained. There are also fine-prints when implementing DBM, which need special attention.

Notes of Pinal Dave

“Is database mirroring a high-availability solution, or a disaster recovery solution?” When I ask this question, I keep on getting a different answer every single time. Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Now, mirroring requires that the database has to be in full recovery mode and the database resides on different instances. A lesser known fact is that mirroring is also related to the Page Restore concept. Why not spend time to know what the main interesting factors related to database mirroring are? I’m sure it would be a worthwhile learning experience for you.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#158-159, 166
Quick Note of Database Mirroring
Standby Servers and Types of Standby Servers
Mirroring Configured Without Domain

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 – What is Piecemeal Restore – Quiz – Puzzle – 22 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

The work of a DBA is never to be underestimated. An effective DBA’s role maybe understated for the 364 days in a year, but that 1 day when the database gets into a corrupt state is when all the skills come to the fore.

 Recently, while working on a 24×7 large database split into multiple filegroups, it suddenly got into a corrupt state. The DBA brought the database ONLINE quickly and was doing a restore process in the background. To understand what he had done, what is Piecemeal Restore inside SQL Server? When was this introduced and what are the options of using this feature?

Link to participate in SQL Quiz

Notes of Vinod Kumar

The behaviors and options of using a Database backup are indeed critical, yet an effective DBA should be able to harness the best results in the shortest time. Hint: it is like a great bookmark to understand the options available when looking for restore strategies with SQL Server. To help you more, read topics around ONLINE restores.

Notes of Pinal Dave

Here are a few quotes from users I often hear:

“I have all the necessary backups but I could not restore the backup.”
“Three hours have passed and yet our restore is still going on?”
“We have a mirroring set up and never faced an issue of corruption.”

When I hear them, I often wonder if they really know the aspects of backup and restore. Piecemeal is another similar concept that people often overlook, ignore and avoid learning. I think all of us should explore this technology further and see if we can avoid saying the above quotes.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#161
Backup Timeline and Understanding of Database Restore Process in Full Recovery Model
Effect of Compressed Backup Setting at Server Level on Database Backup
Target Recovery Time of a Database – Advance Option in SQL Server 2012
A Quick Script for Point in Time Recovery – Back Up and Restore
Copy Database from Instance to Another Instance – Copy Paste in SQL Server
Mirrored Backup and Restore and Split File Backup
Corrupted Backup File and Unsuccessful Restore

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 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)

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)