http://beyondrelational.com/quiz/default.aspxThe SQL Server and .NET Quiz series aimes to help you refine your technical skillsbeyondrelationalen-ushttp://beyondrelational.com/images/quiz/sqlquiz50x50.pnghttp://beyondrelational.com/quiz/default.aspx6060Copyright © Beyondrelational.comWed, 19 Jun 2013 03:13:33 GMT60http://beyondrelational.com/quiz/sqlserver/general/2012/questions/reporting-and-analytics-cannot-be-achieved-without-a-very-good-data-model-at-the-background.aspxReporting 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. But getting data from relational engine and keeping data for analysis upto date is the challenge. What is the concept of Slowly Changing Dimension? And what is a Hybrid Slowly Changing Dimension.http://beyondrelational.com/quiz/sqlserver/general/2012/questions/reporting-and-analytics-cannot-be-achieved-without-a-very-good-data-model-at-the-background.aspxTue, 31 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/as-databases-volumes-become-big-and-big-the-best-way-to-optimize-would-be-take-the-big-problem.aspxAs databases volumes become big and big the best way to optimize would be take the big problem and break it up into smaller chunks. During a large server deployment DBA suggested implementing extensive partitioning for our transaction tables. What are the basics of creating, managing and deploying SQL Server partitioning?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/as-databases-volumes-become-big-and-big-the-best-way-to-optimize-would-be-take-the-big-problem.aspxMon, 30 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/there-are-multiple-performance-troubleshooting-tools-which-a-dba-can-use-inside-sql-server.aspxThere are multiple performance troubleshooting tools which a DBA can use inside SQL Server from Perfmon, SQL Profiler and multiple third Party tools. One of the hidden features most forgotten is the Data Collector inside SQL Server. What is a Data Collector? What are the predefined collectors inside SQL Server which can be used?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/there-are-multiple-performance-troubleshooting-tools-which-a-dba-can-use-inside-sql-server.aspxSun, 29 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/recently-i-was-contacted-by-one-of-my-friends-operating-a-large-database-that-he-was-running-out-of.aspxRecently I was contacted by one of my friends operating a large database that he was running out of disk space because of some table with many indexes 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?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/recently-i-was-contacted-by-one-of-my-friends-operating-a-large-database-that-he-was-running-out-of.aspxSat, 28 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/using-datetime-functions-inside-sql-server-have-always-been-a-struggle-and-there-are-multiple-option.aspxUsing datetime functions inside SQL Server have always been a struggle and there are multiple options. There have been interesting usages of GETDATE but interesting functions like SYSDATETIME(), GETUTCDATE() and SYSYTCDATETIME() functions were also introduced. When would you use these functions and what are their differences?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/using-datetime-functions-inside-sql-server-have-always-been-a-struggle-and-there-are-multiple-option.aspxFri, 27 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/as-part-of-performance-tuning-exercise-our-developer-always-suggested-to-use-set-based-operations.aspxAs part of performance tuning exercise our developer always suggested to use SET based operations rather than using procedural languages. One of the suggestions given for substitute for hierarchical data structure was to use of CTE. What are CTEs inside SQL Server, what are its advantages and how can you rewrite a subquery using CTE?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/as-part-of-performance-tuning-exercise-our-developer-always-suggested-to-use-set-based-operations.aspxThu, 26 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/compliance-is-one-of-the-non-negotiable-in-this-era.aspxCompliance is one of the non-negotiable 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?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/compliance-is-one-of-the-non-negotiable-in-this-era.aspxWed, 25 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/any-database-design-can-include-only-the-normal-forms.aspxAny 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. On that lines, explain FILL_FACTOR and PAT_FACTOR inside SQL Server? What are its uses and when would you use the same?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/any-database-design-can-include-only-the-normal-forms.aspxTue, 24 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/though-high-availability-is-something-critical-for-all-large-database-deployments.aspxThough High-Availability is something critical for all large database deployments. There are multiple options one can use. For discussion today, what is database mirroring option? What are the Database Mirroring enhancements done with SQL Server 2008 R2 edition? How is it different from Log shipping?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/though-high-availability-is-something-critical-for-all-large-database-deployments.aspxMon, 23 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/work-of-a-dba-is-never-to-be-underestimated.aspxWork of a DBA is never to be underestimated. An effective DBA’s role maybe understated for the 364 days in a year but for 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 24x7 large database split into multiple filegroups got into a corrupt state. DBA brought the database ONLINE quickly and was doing a restore in the background. To understand what he would have done, what is Piecemeal Restore inside SQL Server? When was thttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/work-of-a-dba-is-never-to-be-underestimated.aspxSun, 22 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/even-though-storage-as-a-subsystem-is-cheap-these-dayswe-have-seen-dbas-struggle-to-find-log-file.aspxEven though storage as a subsystem is cheap these days. We have seen DBA’s struggle to find Log files run out of space and stop transactions. Understanding size is a critical aspect, now tables data, index data, log data and more is becoming a competency of an effective DBA. So, how do you enable and disable an Index? What is the behavior of the same when compared to dropping an Index? How does storage change?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/even-though-storage-as-a-subsystem-is-cheap-these-dayswe-have-seen-dbas-struggle-to-find-log-file.aspxSat, 21 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/from-sql-server-2000-the-use-of-xml-has-been-more-and-more-by-applications.aspxFrom SQL Server 200 the use of XML has been more and more by applications. With SQL Server 2005 XML became a first class datatype and applications are likely to use more natively this datatype. What are the Basic 5 methods provided by XML Datatype for access? When would you use one over the other?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/from-sql-server-2000-the-use-of-xml-has-been-more-and-more-by-applications.aspxFri, 20 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/in-any-performance-testing-discussion-for-large-development-one-of-the-common-requirements.aspxIn any performance testing discussion for large development one of the common requirements has been to send a multiple values to SQL Server in one shot. So to help this requirement, SQL Server introduced the MERGE statement. What is the construct of a MERGE statement? What are the restrictions of using the same?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/in-any-performance-testing-discussion-for-large-development-one-of-the-common-requirements.aspxThu, 19 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/as-an-effective-dba-it-is-critical-to-keep-tap-on-the-various-server-side-databases.aspxAs an effective DBA it is critical to keep tap on the various server side databases. Recently was encountered with a Developer saying 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? How can we find the properties of the RESOURCE database like version etc?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/as-an-effective-dba-it-is-critical-to-keep-tap-on-the-various-server-side-databases.aspxWed, 18 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/auditing-is-always-fun-when-we-do-code-reviews.aspxAuditing is always fun when we do code reviews. And in a product backend check we found a lot of junk names for the Default constraints inside the database. What are the different ways you can create a CONSTRAINT inside SQL Server?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/auditing-is-always-fun-when-we-do-code-reviews.aspxTue, 17 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/recently-got-a-dba-who-said-the-log-files-was-growing-bigger-than-the-data-file-size.aspxRecently got a DBA who said the Log files was growing bigger than the data file size. On discussion he mentioned using CHECKPOINT to solve this problem. So what does CHECKPOINT do inside SQL Server? Does the behavior change based on Database recovery models?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/recently-got-a-dba-who-said-the-log-files-was-growing-bigger-than-the-data-file-size.aspxMon, 16 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/it-is-saying-that-where-you-should-use-nail-you-should-not-use-sword.aspxIt is saying that where you should use nail, you should not use sword. Every little tool has its own usage and its own purpose. What are the differences of CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) datatypes?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/it-is-saying-that-where-you-should-use-nail-you-should-not-use-sword.aspxSun, 15 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/compile-and-recompile-has-very-interesting-meaning-when-learning-machine-learning.aspxCompile and Recompile has very interesting meaning when learning Machine learning. However, the same thing has similar but very interesting effect in SQL Server. Many related compilation of the code with performance at some degree it is correct and at some degree they have nothing to do with each other. When does your stored Procedures RECOMPILE inside SQL Server?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/compile-and-recompile-has-very-interesting-meaning-when-learning-machine-learning.aspxSat, 14 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/some-questions-are-good-for-debates.aspxSome questions are good for debates. There are so many myths associated with few subjects that when the discussion starts it is hard to convince stubborn friend who is not read to accept the truth. Let me see you attempt both the side of the following question: What is the difference of table variable and temp tables?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/some-questions-are-good-for-debates.aspxFri, 13 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/developer-and-dba-both-sometime-have-their-own-priority-and-preferences-when-it-is-about-database.aspxDeveloper and DBA both sometime have their own priority and preferences when it is about database designs. If you are DBA or Developer you must learn cross domain expertise features. One of the similar features is DACPAC. What is the use of DACPAC?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/developer-and-dba-both-sometime-have-their-own-priority-and-preferences-when-it-is-about-database.aspxThu, 12 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/performance-tuning-expert-often-encounter-situation-where-it-is-difficult-to-figure-out-why-certain.aspxPerformance tuning expert often encounter situation where it is difficult to figure out why certain activities are happening behind the scene in SQL Server Engine. Every action has some root cause and understanding the same is the key to be successful performance tuning expert. Here is one question which often puzzles many - When are the non-Clustered Indexes automatically rebuilt?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/performance-tuning-expert-often-encounter-situation-where-it-is-difficult-to-figure-out-why-certain.aspxWed, 11 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/i-noticed-very-long-stored-procedure-in-the-production-database.aspxI noticed very long stored procedure in the production database. This database creating temporary tables, using insert and select statement. When I asked the purpose of the stored procedure, I realize they should have just used OUTPUT clause. What is the OUTPUT clause inside SQL Server?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/i-noticed-very-long-stored-procedure-in-the-production-database.aspxTue, 10 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/a-query-always-ran-in-split-second-and-suddenly-it-started-to-timeout.aspxA query always ran in split second and suddenly it started to timeout. A quick look at the error log the cause was deadlock. What is a Deadlock and how to identify and resolve a Deadlock inside SQL Server 2008 R2?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/a-query-always-ran-in-split-second-and-suddenly-it-started-to-timeout.aspxMon, 09 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/a-sql-developer-got-surprised-when-he-learned-that-he-does-not-have-to-use-cursors-to-calculate-rank.aspxA SQL developer got surprised when he learned that he does not have to use cursors to calculate ranks in SQL Server 2008. He can now just use newly introduced ranking functions. What are the different RANKING functions inside SQL Server 2008 R2 and what are the new functions introduced inside SQL Server 2012?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/a-sql-developer-got-surprised-when-he-learned-that-he-does-not-have-to-use-cursors-to-calculate-rank.aspxSun, 08 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/a-new-trainee-was-confused-when-he-discovered-that-organization-is-discouraging-the-indexed-view.aspxA new trainee was confused when he discovered that organization is discouraging the Indexed View (or materialized views). After talking to 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?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/a-new-trainee-was-confused-when-he-discovered-that-organization-is-discouraging-the-indexed-view.aspxSat, 07 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/dba-had-to-restore-its-database-on-new-server-runs-a-query-to-validate-the-data.aspxDBA had to restore its database on new server runs a query to validate the data. The same query on the same database started to give different results. After a quick research he figured out that collation was different of both the database. What is Collation and Collation Sensitivity inside SQL Server?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/dba-had-to-restore-its-database-on-new-server-runs-a-query-to-validate-the-data.aspxFri, 06 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/have-you-ever-faced-situation-when-you-trying-to-retrieve-any-data-and-the-query-keeps-on-waiting-on.aspxHave you ever faced situation when you trying to retrieve any data and the query keeps on waiting on the data and eventually times out. One of the reasons for the same can be locking. What are the different types of locks inside SQL Server 2008 R2?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/have-you-ever-faced-situation-when-you-trying-to-retrieve-any-data-and-the-query-keeps-on-waiting-on.aspxThu, 05 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/writing-where-clause-has-been-easiest-thing.aspxWriting where clause has been easiest thing. Most of the time we learn this as a first thing in SQL. I have often seen that many times a simple question with where one has to write about where clause they often fail or get confusedhttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/writing-where-clause-has-been-easiest-thing.aspxWed, 04 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/what-is-an-identity-value.aspxWhat is an IDENTITY Value? What are the different ways to get IDENTITY values and properties of IDENTITY Values? If you are going to answer that identity value is ever increasing, is it possible to have identity value ever decreasing value?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/what-is-an-identity-value.aspxTue, 03 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/every-time-when-developer-attempts-to-update-the-tablea-the-table-is-not-updated-but-instead-tableb.aspxEvery time when developer attempts to update the TableA the table is not updated but instead TableB is updated. After a while confused developer figured out the reason for the same – it was trigger created on the table. What are the different types of triggers inside SQL Server 2008 R2 edition?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/every-time-when-developer-attempts-to-update-the-tablea-the-table-is-not-updated-but-instead-tableb.aspxMon, 02 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/general/2012/questions/ansi-isolations-levels-are-very-important-part-of-any-database.aspxANSI ISOLATIONS levels are very important part of any database. There are two database servers both have different isolations levels. Due to the same different results are returned to the users. Let us help them by educating regarding various isolations levels and their various behaviors with example. What are the different ANSI ISOLATION Levels? What is the additional ISOLATION Level with SQL Server and explain its behavior?http://beyondrelational.com/quiz/sqlserver/general/2012/questions/ansi-isolations-levels-are-very-important-part-of-any-database.aspxSun, 01 Jan 2012 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/demonstrate-the-different-ways-to-get-the-internal-database-version-for-sql-server.aspxSQL Server has Versions like 6.0, 6.5, 7.0, 2000, 2005, 2008, 2008 R2 and editions like Enterprise, Standard, Express etc and Compatibility levels like 60, 65, 70, 80, 90, 100 etc but it also has something known as internal version numbers like 515, 539, 611, 612, 661, 665 etc...http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/demonstrate-the-different-ways-to-get-the-internal-database-version-for-sql-server.aspxFri, 30 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/which-are-the-options-available-to-a-dba-to-tune-a-sql-azure-database.aspxWhich are the options available to a DBA to tune a SQL Azure database?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/which-are-the-options-available-to-a-dba-to-tune-a-sql-azure-database.aspxThu, 29 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/importance-of-temporary-database.aspxWhy it is important to maintain Temporary database?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/importance-of-temporary-database.aspxWed, 28 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/what-is-the-impact-of-an-open-transaction-with-the-lsn-sequence-captured-during-a-full-backup-.aspxBackup is a consistency operation with SQL Server that means the engine should capture a piece of the log during backup. When we use the command RESTORE HEADERONLY or ask the backupset table in msdb databasehttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/what-is-the-impact-of-an-open-transaction-with-the-lsn-sequence-captured-during-a-full-backup-.aspxTue, 27 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/identifying-employee-level-in-employee-hierarchical-structures.aspxThe Employee table of the JProCo sample database (from Joes 2 Pros) shows Sally Smith is the CEO at the top of the hierarchy of all employees. There are three levels of employees. For example Lisa Kendall works for ManagerID 4 which is David Kennson. David works for ManagerID 11 which is Sally. Therefore Sally is at Level 1, David Kennson is at Level 2, and Lisa Kendall is at level 3.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/identifying-employee-level-in-employee-hierarchical-structures.aspxMon, 26 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/design-a-high-availability-architecture.aspxThe application is used by over 1000+ retail avenues across the world that makes application databases as highly transactional by the active processes from retail applications, ad hoc queries by marketing, reporting queries by business and continous audit by tools.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/design-a-high-availability-architecture.aspxSun, 25 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/data-storage-problem.aspxYou're migrating an old system into SQL Server back-end. In one of the tables you're currently working on you had a description field. You ran a statement to find the average and maximum length of that column and found 500 and 3785 respectively.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/data-storage-problem.aspxSat, 24 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/how-do-you-fix-rpc-server-is-unavailable-error-message.aspxWhen accessing SSIS Remotely, you might get an error RPC Server is unavailable what is the work around you would suggest in this case?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/how-do-you-fix-rpc-server-is-unavailable-error-message.aspxFri, 23 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/dynamic-date-conversion-on-legacy-systems.aspxMany legacy systems from the 20th century stored dates as decimals due to limitations of the technology at the time. For example 08/03/2011 would be stored as a decimal with value 20110803. Modern reporting platforms that enable self-service reporting like Izenda Reports require dates to be stored in the proper format.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/dynamic-date-conversion-on-legacy-systems.aspxThu, 22 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/can-constraints-helps-optimizer.aspxCan a constraint help optimizer to get optimized plan and there by better performance? If Yes explain with a scenariohttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/can-constraints-helps-optimizer.aspxWed, 21 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/maintaining-product-database-for-different-cycles.aspxAs a DBA of a large product, there can be many service packs and hot fixes needed to be applied each day. Now, the problem the organization faces id to apply the incremental changes to the environment.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/maintaining-product-database-for-different-cycles.aspxTue, 20 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/tempdb-page-contention.aspxHow do you identify Page Contention in tempdb and what actions you will take to reduce page contentions?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/tempdb-page-contention.aspxMon, 19 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/service-broker-message-poisoning.aspxWhat is Message Poisoning ? How to create a notification of Message Poisoning and how to remove Poison Messages Administratively ?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/service-broker-message-poisoning.aspxSun, 18 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/how-do-you-transfer-ssrs-subscription-from-one-server-to-another.aspxYou are responsible for a large application/database having thousands of SSRS reports. Many of those reports are configured as data driven subscriptions for email delivery.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/how-do-you-transfer-ssrs-subscription-from-one-server-to-another.aspxSat, 17 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/storage-question.aspxImagine you're a stubborn (or maybe creative :D) DBA that does not take for granted all what you're told. In the beginning of your career, you read that storage-wise you can get as deep as a filegroup when it comes to tables - you can only assign it to filegroup or partition. Now you want to show that it's possible to find a data in a file.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/storage-question.aspxFri, 16 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/best-practices-for-dealing-with-transaction-logs.aspxThis question tries to assess your understanding of SQL Server Transaction logs and invites thoughtful discussion on best practices for managing transaction logshttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/best-practices-for-dealing-with-transaction-logs.aspxThu, 15 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/when-was-the-last-time-a-stored-procedure-was-executed.aspxAssuming the cache has not been flushed, how can you report on the last time a specific stored procedure was executed?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/when-was-the-last-time-a-stored-procedure-was-executed.aspxWed, 14 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/filtered-indexes.aspxThere are certain occasions when a comparison operator used in a filtered index expression will result in an error. What condition will cause this error and what can be done to rectify it?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/filtered-indexes.aspxTue, 13 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/database-mirroring.aspxWe have to set up a database mirroring with an automatic failover option. There are three servers in different data centers and they are not in the same domain. Also these SQL Server instances do not have a domain account; they run under Local System account.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/database-mirroring.aspxMon, 12 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/dead-lock-live-lock-blocking.aspxExplain following terms and explain how would you face this problem?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/dead-lock-live-lock-blocking.aspxSun, 11 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/how-will-you-restrict-users-at-certain-period.aspxHow will you restrict users at certain period?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/how-will-you-restrict-users-at-certain-period.aspxSat, 10 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/all-was-well-few-moments-before-all-went-wrong-reasons-and-resolutions.aspxQuery which was working fine all the time suddenly got slow. There has been on change in the workload, no new development changes have been deployed and there are no other configuration changes to the system.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/all-was-well-few-moments-before-all-went-wrong-reasons-and-resolutions.aspxFri, 09 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/strategies-for-handling-a-heavily-fragmented-database.aspxYou have a database that is heavily prone to fragmentation. This database is on Standard version of SQL Server 2005. The tables have GUIDs as primary keys, and most clustered indexes are also created on GUIDs. The database is close to 700 GB in size approx.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/strategies-for-handling-a-heavily-fragmented-database.aspxThu, 08 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/raid-level-considerations-for-deployment.aspxThis is one of the easiest of questions and Open ended very common DBA scenario for Deployment. Todays question is around RAID Levelshttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/raid-level-considerations-for-deployment.aspxWed, 07 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/execution-plan.aspxif two identical queries posed by the same user using two different sessions at a SQL Server simultaneously, two query plans exists while they execute.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/execution-plan.aspxTue, 06 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/bulk-logged-recovery.aspxExplain why the size of transaction log backups can be much larger when the database is using the Bulk-logged recovery modelhttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/bulk-logged-recovery.aspxMon, 05 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/why-dbas-should-have-basic-knowlede-on-powershell-or-shell-programming.aspxWhy DBAs should have basic knowlede on PowerShell?Or Shell Programming?http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/why-dbas-should-have-basic-knowlede-on-powershell-or-shell-programming.aspxSun, 04 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/why-are-we-waiting-how-do-we-calculate-total-query-response-time.aspxWaits are represented by SQL Server wait statistics. SQL Server tracks wait information any time that a user connection or sessionid is waiting. This wait information is summarized and categorized across all connections so that a performance profile can be obtained for a given work load.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/why-are-we-waiting-how-do-we-calculate-total-query-response-time.aspxSat, 03 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/connection-strings-impact-on-performance-security.aspxConnection strings are an important part of any database oriented application. Please list mechanisms by which the performance and security of the application be improved by simply changing the connection string alone (examples would be welcome).http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/connection-strings-impact-on-performance-security.aspxFri, 02 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/dba/2011/questions/inline-table-valued-functions-v/s-views.aspxInline table-valued functions are sometimes called 'parameterized views'. This is under the assumption that they provide all the functionality that a view provides plus the ability to add parameters.http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/inline-table-valued-functions-v/s-views.aspxThu, 01 Sep 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/aging-pivots-for-financial-reports-and-dashboards.aspxFinancial and accounting organizations often require an aging report that pivots transactions by their age. This lets the organization organize a response to the lack of activity...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/aging-pivots-for-financial-reports-and-dashboards.aspxThu, 30 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/to-follow-the-squiggly-or-not.aspxOne of the best practice design alerts in Business Intelligence Development Studio suggests “Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.”http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/to-follow-the-squiggly-or-not.aspxWed, 29 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/how-to-merge-three-different-resultsets-into-one-and-export-that-to-one-csv-file.aspxHow to merge three different resultsets into one and export that to one csv file? Let's have a example of invoice header and details export in one single file...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/how-to-merge-three-different-resultsets-into-one-and-export-that-to-one-csv-file.aspxTue, 28 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/display-breadcrumbs-for-members-of-a-user-hierarchy.aspxFill in the definition of [Measures].[Parent Breadcrumbs] by writing an MDX expression. "Parent Breadcrumbs" is a string consisting of all parent names separated by " -> " in the user hierarchy.http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/display-breadcrumbs-for-members-of-a-user-hierarchy.aspxMon, 27 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/ssis-package-execution-error-bypass.aspxThe goal is to create package and execute successfully. Please note that the package should continue execution even a task in execution sequence fails...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/ssis-package-execution-error-bypass.aspxSun, 26 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/what-data-warehouse-model-am-i-thinking-of.aspxThere are various industry approaches to building a data warehouse. This approach is meant to have data rapidly loaded, keep data source data in-tact without undergoing transformation...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/what-data-warehouse-model-am-i-thinking-of.aspxSat, 25 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/package-and-tasks.aspxHow would you restart package from previous failure point? What are the different ways of sending mail in SSIS? what are the advantages and disadvantages of those approaches?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/package-and-tasks.aspxFri, 24 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/scripting-analysis-services-commands.aspxAs most Sql Server developers, you use sqlcmd.exe to script some of your commands to Sql Server...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/scripting-analysis-services-commands.aspxThu, 23 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/building-an-etl-and-bi-solutions-as-a-prototype.aspxSSQDBI Retail Corporation is a solution provider for majority of Superstores across Europe, the service provided by SSQDBI helps the Vendors & Retailers in understanding their best customers and what they buy...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/building-an-etl-and-bi-solutions-as-a-prototype.aspxWed, 22 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/ssis-advanced-services.aspxI run SQL Server 2008 express with advanced services and I'm pointing external images in my reports, however when I try to view the report it's not displaying the image, what might the possible reason?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/ssis-advanced-services.aspxTue, 21 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/how-do-you-track-your-variable-values-in-ssis.aspxDescribe the steps required to enable auditing of variable value changes in an SSIS package to a table in a SQL Server database?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/how-do-you-track-your-variable-values-in-ssis.aspxMon, 20 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/ssis.aspxHow can I pause control flow in SSIS?...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/ssis.aspxSun, 19 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/uploading-via-ssis.aspxYou have a text file which you decide to upload into SQL Server via SSIS. The only problem is that the text file has some names in quotes.http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/uploading-via-ssis.aspxSat, 18 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/graphs-in-ssrs.aspxSuppose you are presented with the following data which shows the price data for your client’s brand (MyBrand), your client’s competitor brand (CompBrand) and the highest and lowest price data within that segment in the market...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/graphs-in-ssrs.aspxFri, 17 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/best-ssis-components-for-generalized-extraction-and-snchronization-process.aspxIn this Question we just have to give What, Why and How we are going to use component. No need of Writing queries or Script just mention best Component and GO ahead So For fast processing which of the following method you will use from the following...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/best-ssis-components-for-generalized-extraction-and-snchronization-process.aspxThu, 16 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/schedule-ssis-package-in-sql-server-2008.aspxList out detailed steps to create job to execute SSIS package in SQL Server 2008, what security layers you have to cross and what roles should be assigned to job executor account in order to execute package smoothly?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/schedule-ssis-package-in-sql-server-2008.aspxWed, 15 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/pagewise-total.aspxSSRS allows you to make use of summary functions like SUM, COUNT etc so you can calculate total of each group. How do you do pagewise total?...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/pagewise-total.aspxTue, 14 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/parent-child-loading-in-ssis.aspxThis is common SSIS problem which is related to parent child loading when parent destination column has identity value as primary key. Now, suppose we are loading the data from multiple sources, how to maintain the relationships?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/parent-child-loading-in-ssis.aspxMon, 13 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/what-is-ssis-catalog.aspxIn SQL 11 (code name : Denali) CTP1, we have something known as SSIS Catalog...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/what-is-ssis-catalog.aspxSun, 12 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/mass-mailing-your-report.aspxEmail report to people on a mailing list with parametershttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/mass-mailing-your-report.aspxSat, 11 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/schema-design-for-a-high-performance-rolap.aspxWhat shema type would you use to create a high performance ROLAP (Real Time Online Analytical Processing) system? Why?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/schema-design-for-a-high-performance-rolap.aspxFri, 10 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/mdx-syntax-error.aspxWhy that MDX statement generates an error ?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/mdx-syntax-error.aspxThu, 09 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/how-to-do-pivot-transformation-of-tables-between-different-dbssource-oracle-destination-sql-server.aspxHow to do pivot transformation of tables between different DBs(Source-Oracle, Destination-SQL Server) With Integration Services(SSIS).http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/how-to-do-pivot-transformation-of-tables-between-different-dbssource-oracle-destination-sql-server.aspxWed, 08 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/use-of-foreign-key-constraints.aspxShould Foreign Key constraints be used in a Data Warehouse?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/use-of-foreign-key-constraints.aspxTue, 07 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/to-key-or-not-to-key.aspxHere's a question that could go both ways. Would you or would you not have key constraints and indexes for your data mart?...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/to-key-or-not-to-key.aspxMon, 06 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/troubleshooting-cube-performance.aspxList three things to diagnose cube performance and tune it.http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/troubleshooting-cube-performance.aspxSun, 05 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/dimension-optimization-with-ssas-2008.aspxWell-tuned dimension design is one of the most critical success factors of a high-performing Analysis Services solution. Todays question revolved around just this factor of tuning techniques with examples for your workload we want to explore.http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/dimension-optimization-with-ssas-2008.aspxSat, 04 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/ssis-select-distinct-files-from-folder-based-on-certain-conditions.aspxA folder has a lot of files dumped in and each file has an ID suffixed with timestamp followed by any other value. Now the challenge is to get one copy of file per day based on the unique ID. In case there are multiple files for the same date and same ID you could select any one of them.http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/ssis-select-distinct-files-from-folder-based-on-certain-conditions.aspxFri, 03 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/adding-hierarquie-in-as-cube.aspxWhen adding an hierarquie inside a dimension in AS cube, do you need to run a full process? why?http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/adding-hierarquie-in-as-cube.aspxThu, 02 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/bi/2011/questions/is-it-a-good-idea-to-use-sspi-authentication-for-report-data-sources.aspxThe title says it all. When does it make sense to use SSPI (Windows user) authentication when creating a data source for use with a Reporting Services report?...http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/is-it-a-good-idea-to-use-sspi-authentication-for-report-data-sources.aspxWed, 01 Jun 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/do-you-know-what-you-think-you-know.aspxBe advised in advance that this is a bit of a "trick question". You are asked to review this query: select a.base_id, coalesce(b.order_Id, c.order_Id ) as Order_Id from kaw_ref xRef left join kaw_Base a on a.base_id = xref.base_Id left join kaw_subtype1 b on b.base_id = a.base_Id left join kaw_subtype2 c on c.base_id = a.base_Id where a.base_Id is not null You respond by modifying it to something like this: select a.base_id, coalesce(b.order_Id, c.order_Idhttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/do-you-know-what-you-think-you-know.aspxThu, 31 Mar 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/difference-in-getting-total-count-value-from-table.aspxQuestion on COUNT functionhttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/difference-in-getting-total-count-value-from-table.aspxWed, 30 Mar 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/advantage-and-disadvantage-of-using-indexed-view-in-sql-server.aspxUsing Indexed view in SQL Server comes with a cost. But there are places which it perfectly fits in and at the same time there are places where it create problem than solution. So, what all are the pros and cons of using Indexed viewhttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/advantage-and-disadvantage-of-using-indexed-view-in-sql-server.aspxTue, 29 Mar 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/advantages-of-with-result-setsql-server-2011.aspxThe question looks for the real time applicability of SQL Server 2011's With Result Set Statementhttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/advantages-of-with-result-setsql-server-2011.aspxMon, 28 Mar 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/explain-the-difference-why-scenario-2-uses-2-data-pages-instead-of-one-as-in-1.aspxIn SQL Server, a page is 8K (8192 bytes) and what is the largest data size [when storing one or many rows] that can be stored in a page if you exclude LOB data?http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/explain-the-difference-why-scenario-2-uses-2-data-pages-instead-of-one-as-in-1.aspxSun, 27 Mar 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/human-resources-report.aspxImagine you want to automate Human Resources department's operations. One of the reports the HR Manager will like to run is to get the following information for any given employee: The people who manage this employee and all the people whom he/she manages.http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/human-resources-report.aspxSat, 26 Mar 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/data-analysis-and-queries-across-multiple-databases-without-etl.aspxThis question invites you to share your thoughts on writing reporting queries that deals with more than one databases.http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/data-analysis-and-queries-across-multiple-databases-without-etl.aspxFri, 25 Mar 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/finding-total-order-amount-for-current-date.aspxAn invoice table contains order information such as order date and order amount. Your job is to find the best way to calculate the total amount of orders created today. Your query should run in SQL Server 2008 or later versions.http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/finding-total-order-amount-for-current-date.aspxThu, 24 Mar 2011 00:00:00 GMThttp://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/rewrite-two-tsql-queries-into-a-single-query-using-one-of-the-new-features-of-sql-server-2008.aspxYour task is to generate a single TSQL statement to fetch the difference between the SYSTNODE value without having the need to use EXCEL.http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/rewrite-two-tsql-queries-into-a-single-query-using-one-of-the-new-features-of-sql-server-2008.aspxWed, 23 Mar 2011 00:00:00 GMT