SQL SERVER – What is Slowly Changing Dimension – Quiz – Puzzle – 31 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

Reporting and analytics cannot be achieved without a very good data model at the background. And using Analysis services for reporting requirement is always something very common architecture to this. But getting data from relational engine and keeping data for analysis up to date is the challenge. Having these in mind, what is the concept of Slowly Changing Dimension? And what is a Hybrid Slowly Changing Dimension?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Modeling can be a tricky activity. The way to load a SCD  can be achieved interestingly via the SSIS route. Though the basics of SCD can be a larger discussion, tell us more about a thing or two on what you have learned so far in loading SCD.

Notes of Pinal Dave

Let us talk about a product in a shop. Let us assume the product is newly launched. The price of the product is very high and it is put in the ‘newly arrived products’ section. After. a while the book becomes best seller and it is moved to the ‘best seller’ section. As time progresses, the rate of sales of the book declines, leading to it getting moved off the best sellers’ group to the ‘regular items’ section. Eventually, the same book is moved to the bargain section and with a very low price value. Well, this product life process is slow changing. If we consider the price of the book as well as the duration it spent in particular section, it is very much comparable to a slowly changing dimension in SQL Server. Now that you know this simple comparison, the quiz should be easier for you.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#179-180

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 – Advantages of Partitioning – Quiz – Puzzle – 30 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 databases volumes become bigger and bigger, the best way to optimize would be to take the huge problem and break it up into smaller chunks. During a large server deployment, a DBA used this advice; he suggested implementing an extensive partitioning for our transaction tables. In line with this, what are the basics of creating, managing and deploying SQL Server partitioning?

Link to participate in SQL Quiz

Notes of Vinod Kumar

There are several options when it comes to using partitioning concepts inside SQL Server. Here’s a hint: choose and explain what SQL Server introduced and what has been enhanced in SQL Server 2008 version.  In relation to this, can you tellm at Aligned Indexes are when it comes to partitioning?

Notes of Pinal Dave

Partitioning is a very old concept, yet it has always been implemented indirectly. I remember durinv my very early career years, we used to create partitioning using the work around. When any table gets very huge and has an enormous amount of data into the table, we used to create new table by means of appending it with _1 or _2 value. After doing that, we created the view on the top of this table and selected the data from it. This way, we had to change a few parts of our code and we had to implement the concept of partitioning right away. Now, in SQL Server, we get the partitioning feature as a native feature, as well as means of getting a cool syntax to populate and remove data.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#168-170
Database Table Partitioning Tutorial – How to Horizontal Partition Database Table
White Paper – Partitioned Table and Index Strategies Using SQL Server 2008

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 – Data Collector Usage – Quiz – Puzzle – 29 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 Perfmon to SQL Profiler to other third Party tools, there are a good number of performance troubleshooting tools which a DBA can use inside SQL Server. One of the hidden features of most troubleshooting tools is the Data Collector inside SQL Server. What is a Data Collector, anyway? What are the predefined collectors inside SQL Server which can be used?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Data Collector is truly a hidden gem in SQL Server. Data Collector is a great tool to track what is happening inside the SQL Server. Are you using the Data Collector? Have you extended it by any chance? Let us hear your stories and how would you have implemented the same in SQL Server 2005 versions?

Notes of Pinal Dave

Data Collection is our old habit. Isn’t that the very reason why we have reached this far? I think we might have started to count because we wanted to remember how much inventory we have (may be not – but you got the point). Now, collection of the data is not really helpful unless they are arranged, filtered, cleaned up and put together in a nice algorithm. These can make data start talking and reveal its various intelligence. Data Collector is a very important subject, but I often see it not getting the proper exposure as it deserves.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#144

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 – Reclaiming Space Back from Database – Quiz – Puzzle – 28 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, I was contacted by one of my friends who operated a large database. He told me he was running out of disk space because some tables with many indexes were unnecessarily created. Even after truncating the table, the space was not given back to the OS. When does SQL Server give the space back once data is deleted?

Link to participate in SQL Quiz

Notes of Vinod Kumar

There are multiple options, but to get the space back, but what are your other options to explore to reclaim the space and yet not get into fragmentation problems. Let me give you a hint.

Notes of Pinal Dave

There was a time when hard drives were expensive and space was the most important asset. I remember having my very first computer with only 2 GB of hard drive and 16 MB of RAM. At that time, space was truly a very important concept. File splitter was very important as we needed to fit every thing in multiple floppies. But in the modern age we have today, the once-expensive hard drives became so much cheaper, which led the importance of the space to become not as much as it was years back. In spite of this, I still see lots of efforts in the database world to reclaim the space back from the database. I often come across people become worried about keeping the database size as small as possible. Their worries made them attempt DBCC ShrinkDB and other tricks that are seriously not recommended. I think today’s question is in this similar line. We all have faced a situation when the table is deleted and yet the space is not released to OS by SQL Server. Any idea why this occurs?

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#136
SHRINKDATABASE For Every Database in the SQL Server
Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE
Data and Page Compressions – Data Storage and IO Improvement

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 – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 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

Using datetime functions inside SQL Server have always been a struggle even if there are multiple options. There have been interesting usages of GETDATE, but appealing functions like SYSDATETIME(), GETUTCDATE() and SYSYTCDATETIME() functions were also introduced. When would you use these functions and what are their differences?

Link to participate in SQL Quiz

Notes of Vinod Kumar

GETDATE() from the SQL Server 2000 days has been a common function which developers started to utilize. To answer the question, read the hint here. The changes on usage are fascinating as the precision levels and storage needs of these can be altered drastically. Can you name some of these changes?

Notes of Pinal Dave

I remember an old story told to me when I was just a beginning DBA. I had set up a job which was supposed to run at exact 12 AM (midnight). After a few runs, we realized that the job was running always 30 seconds earlier than the desired time. Every single day, at precisely 30 seconds before 12 AM, it would start to run. After a while, we had to create a complete investigation team on this subject. A senior DBA eventually figured it out – it was because we had used SMALLDATETIME datatype for a variable. This variable was storing the current datetime, and as precision of SMALLDATETIME is in terms of minutes, we had to face that issue. Our IT manager finally made all of us to go through every single datatype available in the SQL Server and get familiarized with all of them. I learned the lesson very well so now I want to share this experience with everybody.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#133
Difference Between DATETIME and DATETIME2
Difference Between GETDATE and SYSDATETIME
Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime
Datetime Function TODATETIMEOFFSET Example
Information Related to DATETIME and DATETIME2
Datetime Function SWITCHOFFSET Example
Difference Between DATETIME and DATETIME2 – WITH GETDATE
DATE and TIME in SQL Server 2008

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 – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 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 part of performance tuning exercises, our developer always suggest to use SET based operations rather than procedural languages. One of the suggestions given as a substitute for hierarchical data structure was to use CTE. What are CTEs inside SQL Server, what are their advantages and how can you rewrite a subquery using CTEs?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Common Table Expressions a.k.a CTEs are an important addition to SQL Server. Though CTE is available as an alternative for Views, Subqueries, there is a special case scenario for using CTEs. Tell us, how innovatively have you used CTEs? Can there be recursive CTEs? If yes, have you ever tried them?

Notes of Pinal Dave

CTEs are often confused with Temp Table and Subqueries, but there are more facts about this subject. While working with recursive CTEs, one has to make sure that they do not go into an infinite loop and stop after certain numbers of fixed iteration. SQL Server does have an option to limit the numbers of iteration; however, there is a default number which is associated with number of loops. CTEs are also very useful in terms of readability. I’ve started to use CTEs in many places, but before I began to practice CTEs, I made sure that I’d spend a good amount of time to learn about them, and so I did.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#109-112
Common Table Expression (CTE) and Few Observation
Multiple CTE in One SELECT Statement Query
Delete Duplicate Rows
Simple Example of Recursive CTE
SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop
T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

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)

SQLAuthority News – Interview with Book Authors after 2 Months of Book Released

Community is the most motivating force for me. I have often found situations where I have done more and better things because there was community around me. My latest book SQL Server Interview Questions and Answers is the result of the community’s support and love. Without the wide acceptance of the community I would have never reached where I am. Thank you!

Recently, the kind folks of INETA APAC – Sanjay Shetty and Raj Chaudhuri – conducted an interview with myself and Vinod Kumar (co-author of my book). We had lots of fun during the interview. Sanjay asks questions which, even as an author, I did not think of. My hope is that talking more about our book will help the community in the same way they all helped motivate me to write this book.  Another benefit of the interview was that Raj always has questions which dig out the secrets of the book publishing business. Well, we answered each question candidly. When the interview was about to be over – we realized that book publishing is indeed long process and, just like us, many need help understanding this subject.

I know that I cannot be the only person who ever dreamed of becoming a published author. We would like to offer help to anybody who wants to write book. Writing this one book has taught us a lot and we are eager to share our hard-earned knowledge with you. If you have also written a technical book about computers, SQL Server, or just technology in general, I know that we will be able to help you in some way. Even if you are the sort of person who writes short stories in their free time, give us a try and we might be able to help you, too!

Once again: Thanks Sanjay and Raj for giving us the honor to be featured in an interview with you.

Reference: Pinal Dave (http://blog.sqlauthority.com)