SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Introduction – Day 1 of 31

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

List of all the Interview Questions and Answers Series blogs

Posts covering interview questions and answers always make for interesting reading.  Some people like the subject for their helpful hints and thought provoking subject, and others dislike these posts because they feel it is nothing more than cheating.  I’d like to discuss the pros and cons of a Question and Answer format here.

Interview Questions and Answers are Helpful

Just like blog posts, books, and articles, interview Question and Answer discussions are learning material.  The popular Dummy’s books or Idiots Guides are not only for “dummies,” but can help everyone relearn the fundamentals.  Question and Answer discussions can serve the same purpose.  You could call this SQL Server Fundamentals or SQL Server 101.

I have administrated hundreds of interviews during my career and I have noticed that sometimes an interviewee with several years of experience lacks an understanding of the fundamentals.  These individuals have been in the industry for so long, usually working on a very specific project, that the ABCs of the business have slipped their mind.

Or, when a college graduate is looking to get into the industry, he is not expected to have experience since he is just graduated. However, the new grad is expected to have an understanding of fundamentals and theory.  Sometimes after the stress of final exams and graduation, it can be difficult to remember the correct answers to interview questions, though.

An interview Question and Answer discussion can be very helpful to both these individuals.  It is simply a way to go back over the building blocks of a topic.  Many times a simple review like this will help “jog” your memory, and all those previously-memorized facts will come flooding back to you.  It is not a way to re-learn a topic, but a way to remind yourself of what you already know.

A Question and Answer discussion can also be a way to go over old topics in a more interesting manner.  Especially if you have been working in the industry, or taking lots of classes on the topic, everything you read can sound like a repeat of what you already know.  Going over a topic in a new format can make the material seem fresh and interesting.  And an interested mind will be more engaged and remember more in the end.

Interview Questions and Answers are Harmful

A common argument against a Question and Answer discussion is that it will give someone a “cheat sheet.” A new guy with relatively little experience can read the interview questions and answers, and then memorize them. When an interviewer asks him the same questions, he will repeat the answers and get the job. Honestly, is he good hire because he memorized the interview questions? Wouldn’t it be better for the interviewer to hire someone with actual experience?  The answer is not as easy as it seems – there are many different factors to be considered. If the interviewer is asking fundamentals-related questions only, he gets the answers he wants to hear, and then hires this first candidate – there is a good chance that he is hiring based on personality rather than experience.  If the interviewer is smart he will ask deeper questions, have more than one person on the interview team, and interview a variety of candidates.  If one interviewee happens to memorize some answers, it usually doesn’t mean he will automatically get the job at the expense of more qualified candidates.

Another argument against interview Question and Answers is that it will give candidates a false sense of confidence, and that they will appear more qualified than they are. Well, if that is true, it will not last after the first interview when the candidate is asked difficult questions and he cannot find the answers in the list of interview Questions and Answers.  Besides, confidence is one of the best things to walk into an interview with!

In today’s competitive job market, there are often hundreds of candidates applying for the same position.  With so many applicants to choose from, interviewers must make decisions about who to call back and who to hire based on their gut feeling.  One drawback to reading an interview Question and Answer article is that you might sound very boring in your interview – saying the same thing as every single candidate, and parroting answers that sound like someone else wrote them for you – because they did.  However, it is definitely better to go to an interview prepared, just make sure that you give a lot of thought to your answers to make them sound like your own voice.  Remember that you will be hired based on your skills as well as your personality, so don’t think that having all the right answers will make get you hired.  A good interviewee will be prepared, confident, and know how to stand out.

My Opinion

A list of interview Questions and Answers is really helpful as a refresher or for beginners. To really ace an interview, one needs to have real-world, hands-on experience with SQL Server as well. Interview questions just serve as a starter or easy read for experienced professionals. When I have to learn new technology, I often search online for interview questions and get an idea about the breadth and depth of the technology.

Next Action

I am going to write about interview Questions and Answers for next 30 days. I have previously written a series of interview questions and answers; now I have re-written them keeping the latest version of SQL Server and current industry progress in mind. If you have faced interesting interview questions or situations, please write to me and I will publish them as a guest post. If you want me to add few more details, leave a comment and I will make sure that I do my best to accommodate.

Tomorrow we will start the interview Questions and Answers series, with a few interesting stories, best practices and guest posts. We will have a prize give-away and other awards when the series ends.

List of all the Interview Questions and Answers Series blogs

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

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31

This blog post is running list of the blog posts in the series of Interview Questions and Answers. At the end of the 31st day of the month, a FREE PDF will be posted here which can be downloadable for offline review.

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Introduction – Day 1 of 31

In this very first blog post – various aspect of the interview questions and answers are discussed. Some people like the subject for their helpful hints and thought provoking subject, and others dislike these posts because they feel it is nothing more than cheating.  I’d like to discuss the pros and cons of a Question and Answer format here.

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31

  • What is RDBMS?
  • What are the Properties of the Relational Tables?
  • What is Normalization?
  • What is De-normalization?
  • How is ACID property related to Database?
  • What are the Different Normalization Forms?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31

  • What is a Stored Procedure?
  • What is a Trigger?
  • What are the Different Types of Triggers?
  • What is a View?
  • What is an Index?
  • What is a Linked Server?
  • What is a Cursor?
  • What is Collation?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31

  • What is the Difference between a Function and a Stored Procedure?
  • What is subquery? Explain the Properties of a Subquery?
  • What are Different Types of Join?
  • What are Primary Keys and Foreign Keys?
  • What is User-defined Functions? What are the types of User-defined Functions that can be created?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 5 of 31

  • What is an Identity?
  • What is DataWarehousing?
  • What languages BI uses to achieve the goal?
  • What is Standby Servers? Explain Types of Standby Servers.
  • What is Dirty Read?
  • Why can’t I use Outer Join in an Indexed View?
  • What is the Correct Order of the Logical Query Processing Phases?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31

  • Which TCP/IP port does the SQL Server run on? How can it be Changed?
  • What are the Difference between Clustered and a Non-clustered Index?
  • What are the Different Index Configurations a Table can have?
  • What are Different Types of Collation Sensitivity?
  • What is OLTP (Online Transaction Processing)?
  • What’s the Difference between a Primary Key and a Unique Key?
  • What is Difference between DELETE  and TRUNCATE Commands?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31

  • What are Different Types of Locks?
  • What are Pessimistic Lock and Optimistic Lock?
  • When is the use of UPDATE_STATISTICS command?
  • What is the Difference between a HAVING clause and a WHERE clause?
  • What is Connection Pooling and why it is Used?
  • What are the Properties and Different Types of Sub-Queries?
  • What are the Authentication Modes in SQL Server? How can it be Changed?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31

  • Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
  • What is an SQL Server Agent?
  • Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
  • What is Log Shipping?
  • Name 3 ways to get an Accurate Count of the Number of Records in a Table?
  • What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
  • What is the Difference between a Local and a Global Temporary Table?
  • What is the STUFF Function and How Does it Differ from the REPLACE Function?
  • What is PRIMARY KEY?
  • What is UNIQUE KEY Constraint?
  • What is FOREIGN KEY?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31

  • What is CHECK Constraint?
  • What is NOT NULL Constraint?
  • What is the difference between UNION and UNION ALL?
  • What is B-Tree?
  • How to get @@ERROR and @@ROWCOUNT at the Same Time?
  • What is a Scheduled Job or What is a Scheduled Task?
  • What are the Advantages of Using Stored Procedures?
  • What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
  • Can SQL Servers Linked to other Servers like Oracle?
  • What is BCP? When is it Used?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31

  • What Command do we Use to Rename a db, a Table and a Column?
  • What are sp_configure Commands and SET Commands?
  • How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
  • What is Difference between Commit and Rollback when Used in Transactions?
  • What is an Execution Plan? When would you Use it? How would you View the Execution Plan?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31

  • What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
  • What is the difference between CHAR and VARCHAR Datatypes?
  • What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
  • What is the Difference between VARCHAR and NVARCHAR datatypes?
  • Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
  • How to Optimize Stored Procedure Optimization?
  • What is SQL Injection? How to Protect Against SQL Injection Attack?
  • How to Find Out the List Schema Name and Table Name for the Database?
  • What is CHECKPOINT Process in the SQL Server?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31

  • How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
  • How to Find the List of Fixed Hard Drive and Free Space on Server?
  • Why can there be only one Clustered Index and not more than one?
  • What is Difference between Line Feed (\n) and Carriage Return (\r)?
  • Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
  • What is a Hint?
  • How to Delete Duplicate Rows?
  • Why the Trigger Fires Multiple Times in Single Login?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31

  • What is Aggregate Functions?
  • What is Use of @@ SPID in SQL Server?
  • What is the Difference between Index Seek vs. Index Scan?
  • What is the Maximum Size per Database for SQL Server Express?
  • How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
  • What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
  • How to Create Primary Key with Specific Name while Creating a Table?
  • What is T-SQL Script to Take Database Offline – Take Database Online
  • How to Enable/Disable Indexes?
  • Can we Insert Data if Clustered Index is Disabled?
  • How to Recompile Stored Procedure at Run Time?
  • Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
  • What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
  • Does the Order of Columns in UPDATE statements Matter?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31

  • What are the basic functions for master, msdb, model, tempdb and resource databases?
  • What is the Maximum Number of Index per Table?
  • Explain Few of the New Features of SQL Server 2008 Management Studio
  • Explain IntelliSense for Query Editing
  • Explain MultiServer Query
  • Explain Query Editor Regions
  • Explain Object Explorer Enhancements
  • Explain Activity Monitors

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31

  • What is Service Broker?
  • Where are SQL server Usernames and Passwords Stored in the SQL server?
  • What is Policy Management?
  • What is Database Mirroring?
  • What are Sparse Columns?
  • What does TOP Operator Do?
  • What is CTE?
  • What is MERGE Statement?
  • What is Filtered Index?
  • Which are the New Data Types Introduced in SQL SERVER 2008?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31

  • What are the Advantages of Using CTE?
  • How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
  • What is CLR?
  • What are Synonyms?
  • What is LINQ?
  • What are Isolation Levels?
  • What is Use of EXCEPT Clause?
  • What is XPath?
  • What is NOLOCK?
  • What is the Difference between Update Lock and Exclusive Lock?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 17 of 31

  • How will you Handle Error in SQL SERVER 2008?
  • What is RAISEERROR? What is RAISEERROR?
  • How to Rebuild the Master Database?
  • What is the XML Datatype?
  • What is Data Compression?
  • What is Use of DBCC Commands?
  • How to Copy the Tables, Schema and Views from one SQL Server to Another?
  • How to Find Tables without Indexes?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31

  • How to Copy Data from One Table to Another Table?
  • What is Catalog Views?
  • What is PIVOT and UNPIVOT?
  • What is a Filestream?
  • What is SQLCMD?
  • What do you mean by TABLESAMPLE?
  • What is ROW_NUMBER()?
  • What are Ranking Functions?
  • What is Change Data Capture (CDC) in SQL Server 2008?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31

  • How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
  • What is the CPU Pressure?
  • How can I Get Data from a Database on Another Server?
  • What is the Bookmark Lookup and RID Lookup?
  • What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
  • What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
  • How can I Check that whether Automatic Statistic Update is Enabled or not?
  • How to Find Index Size for Each Index on Table?
  • What is the Difference between Seek Predicate and Predicate?
  • What are Basics of Policy Management?
  • What are the Advantages of Policy Management?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 20 of 31

  • What are Policy Management Terms?
  • What is the ‘FILLFACTOR’?
  • Where in MS SQL Server is ’100’ equal to ‘0’?
  • What are Points to Remember while Using the FILLFACTOR Argument?
  • What is a ROLLUP Clause?
  • What are Various Limitations of the Views?
  • What is a Covered index?
  • When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
  • What are Wait Types?
  • How to Stop Log File Growing too Big?
  • If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 21 of 31

  • What is Data Warehousing?
  • What is Business Intelligence (BI)?
  • What is a Dimension Table?
  • What is Dimensional Modeling?
  • What is a Fact Table?
  • What are the Fundamental Stages of Data Warehousing?
  • What are the Different Methods of Loading Dimension tables?
  • Describes the Foreign Key Columns in Fact Table and Dimension Table?
  • What is Data Mining?
  • What is the Difference between a View and a Materialized View?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 22 of 31

  • What is OLTP?
  • What is OLAP?
  • What is the Difference between OLTP and OLAP?
  • What is ODS?
  • What is ER Diagram?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 23 of 31

  • What is ETL?
  • What is VLDB?
  • Is OLTP Database is Design Optimal for Data Warehouse?
  • If denormalizing improves Data Warehouse Processes, then why is the Fact Table is in the Normal Form?
  • What are Lookup Tables?
  • What are Aggregate Tables?
  • What is Real-Time Data-Warehousing?
  • What are Conformed Dimensions?
  • What is a Conformed Fact?
  • How do you Load the Time Dimension?
  • What is a Level of Granularity of a Fact Table?
  • What are Non-Additive Facts?
  • What is a Factless Facts Table?
  • What are Slowly Changing Dimensions (SCD)?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 24 of 31

  • What is Hybrid Slowly Changing Dimension?
  • What is BUS Schema?
  • What is a Star Schema?
  • What Snow Flake Schema?
  • Differences between the Star and Snowflake Schema?
  • What is Difference between ER Modeling and Dimensional Modeling?
  • What is Degenerate Dimension Table?
  • Why is Data Modeling Important?
  • What is a Surrogate Key?
  • What is Junk Dimension?
  • What is a Data Mart?
  • What is the Difference between OLAP and Data Warehouse?
  • What is a Cube and Linked Cube with Reference to Data Warehouse?
  • What is Snapshot with Reference to Data Warehouse?
  • What is Active Data Warehousing?
  • What is the Difference between Data Warehousing and Business Intelligence?
  • What is MDS?
  • Explain the Paradigm of Bill Inmon and Ralph Kimball.

SQL SERVER – Azure Interview Questions and Answers – Guest Post by Paras Doshi – Day 25 of 31

Paras Doshi has submitted 21 interesting question and answers for SQL Azure.

1.What is SQL Azure?
2.What is cloud computing?
3.How is SQL Azure different than SQL server?
4.How many replicas are maintained for each SQL Azure database?
5.How can we migrate from SQL server to SQL Azure?
6.Which tools are available to manage SQL Azure databases and servers?
7.Tell me something about security and SQL Azure.
8.What is SQL Azure Firewall?
9.What is the difference between web edition and business edition?
10.How do we synchronize On Premise SQL server with SQL Azure?
11.How do we Backup SQL Azure Data?
12.What is the current pricing model of SQL Azure?
13.What is the current limitation of the size of SQL Azure DB?
14.How do you handle datasets larger than 50 GB?
15.What happens when the SQL Azure database reaches Max Size?
16.How many databases can we create in a single server?
17.How many servers can we create in a single subscription?
18.How do you improve the performance of a SQL Azure Database?
19.What is code near application topology?
20.What were the latest updates to SQL Azure service?
21.When does a workload on SQL Azure get throttled?

SQL SERVER – Interview Questions and Answers – Guest Post by Malathi Mahadevan – Day 26 of 31

Malathi has asked a simple question which have several answer. Each answer make you think and ponder about the reality of the IT world. Look at the simple question – ‘What is the toughest challenge you have faced at your present job and how did you handle it’? and its various answers. Each answer has its own story.

SQL SERVER – Interview Questions and Answers – Guest Post by Rick Morelan – Day 27 of 31

Rick Morelan of Joes2Pros has written excellent blog post on subject how to find top N values. Most people are fully aware of how the TOP keyword works with a SELECT statement. After years preparing so many students to pass the SQL Certification I noticed they were pretty well prepared for job interviews too. Yes, they would do well in the interview but not great. There seemed to be a few questions that would come up repeatedly for almost everyone. Rick addresses similar question in his lucid writing skills.

SQL SERVER – Interview Questions and Answers – Guest Post by Nakul Vachhrajani – Day 28 of 31

Nakul explains the word – ‘interview ‘ with entire different angle to the subject. What really attacks in Nakul’s writing is his extremely clear ideas which are super crisp and to the point. Nakul talks about ‘Educational Qualification is never the only requirement’ with such a decent argument, that makes this article a must read.

SQL SERVER – Interview Questions and Answers – Guest Post by Feodor Georgiev – Day 29 of 31

Feodor is our most read gust blog author. He always comes up with interesting subject. Feodor start writing with interesting concept that – the job interview is like an enchanted dance between a potential employee and a potential employer which plays a defining role in their entire collaboration. This article has many witty comments and many interesting subject. A long article which has not a single dull moment.

SQL SERVER – Interview Questions and Answers – Guest Post by Jacob Sebastian – Day 30 of 31

Jacob Sebastian never likes to talk on easy subject. He always talks on difficult subjects. This time he talks about the philosophy of XML. The purpose of the questions and their answers given below is to ensure that the people who read them and learn them will get a basic understanding of the XML functionalities provided by SQL Server. Jacob makes the dry subject like XML to worth reading and learning it.

SQL SERVER – The Difficult Interview Question – Moment of the Life – Day 31 of 31

I guess the title says all – Interview is the moment of life. Final write up on this subject by myself.

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

SQL SERVER – Two Puzzles – Answer and Win USD 25 Gift Card

Today I have two simple T-SQL Puzzle. You can answer them and win USD 25 Gift card. The gift card will be sent in email to winner. You will get choice of Gift Card brand based on your preference and country location.

Puzzle 1: What will be the outcome and why?

DECLARE @x REAL;
SET @x = 9E-40
SELECT @x;

The outcome here is obvious as I have used negative number in assignment. What is the reason behind the same?

Puzzle 2: Why will be the outcome different from Puzzle 1:

DECLARE @y REAL;
SET @y = 9E+40
SELECT @y;

The outcome of this puzzle very different from puzzle 1  as I have used positive number. There is number six (6) in the resultset why?

Msg 232, Level 16, State 2, Line 2
Arithmetic overflow error for type real, value = 90000000000000006000000000000000000000000.000000.

How to participate

  1. To win the Gift Card USD 25 you will have to answer both of the question on my Facebook page.
  2. If you are on twitter – you can increase the chance of winning by tweeting your participation.

This contest is open for any one from any country. The winner will be selected Randomly. Winner will be announced on July 7, 2011.

Related Post: SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com

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

SQL SERVER – Find Details for Statistics of Whole Database – DMV – T-SQL Script

I was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command but I remembered that this is marked to be deprecated in future. Again, using DMV is the right thing to do moving forward. I quickly wrote following script which gives a lot more information than sp_helpstats.

USE AdventureWorks
GO
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1);

If you have better script to retrieve information about statistics, please share here and I will publish it with due credit.

Update: Read follow up excellent blog post by Jason Brimhall.

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

SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com

This month has been very interesting month for SQLAuthority.com we had multiple and various puzzles which everybody participated and lots of interesting conversation which we have shared. Let us start in latest puzzles and continue going down. There are few answers also posted on facebook as well.

SQL SERVER – Puzzle Involving NULL – Resolve – Error – Operand data type void type is invalid for sum operator

This puzzle involves NULL and throws an error. The challenge is to resolve the error. There are multiple ways to resolve this error. Readers has contributed various methods. Few of them even have supplied the answer why this error is showing up. NULL are very important part of the database and if one of the column has NULL the result can be totally different than the one expected.

SQL SERVER – T-SQL Scripts to Find Maximum between Two Numbers

I modified script provided by friend to find greatest number between two number. My script has small bug in it. However, lots of readers have suggested better scripts. Madhivanan has written blog post on the subject over here.

SQL SERVER – BI Quiz Hint – Performance Tuning Cubes – Hints

This quiz is hosted on my friend Jacob‘s site. I have written many hints how one can tune cubes. Now one can take part here and win exciting prizes.

SQL SERVER – Solution – Generating Zero Without using Any Numbers in T-SQL

Madhivanan has asked very interesting question on his blog about How to Generate Zero without using Any Numbers in T-SQL. He has demonstrated various methods how one can generate Zero. I asked the same question on blog and got many interesting answers which I have shared.

SQL SERVER – Solution – Puzzle – Statistics are not Updated but are Created Once

I have to accept that this was most difficult puzzle. In this puzzle I have asked even though settings are correct, why statistics of the tables are not getting updated. In this puzzle one is tested with various concepts 1) Indexes, 2) Statistics, 3) database settings etc. There are multiple ways of solving this puzzles. It was interesting as many took interest but only few got it right.

SQL SERVER – Question to You – When to use Function and When to use Stored Procedure

This is rather straight forward question and not the typical puzzle. The answers from readers are great however, still there is chance of more detailed answers.

SQL SERVER – Selecting Domain from Email Address

I wrote on selecting domains from email addresses. Madhivanan makes puzzle out of a simple question. He wrote a follow-up post over here. In his post he writes various way how one can find email addresses from list of domains.

Well, this is not a puzzle but amazing Guest Post by Feodor Georgiev who has written on subject Job Interviewing the Right Way (and for the Right Reasons). An article which everyone should read.

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

SQL SERVER – Puzzle Involving NULL – Resolve – Error – Operand data type void type is invalid for sum operator

Today is Monday let us start this week with interesting puzzle. Yesterday I had also posted quick question here: SQL SERVER – T-SQL Scripts to Find Maximum between Two Numbers

Run following code:

SELECT SUM(data)
FROM (SELECT NULL AS DATA) t

It will throw following error.

Msg 8117, Level 16, State 1, Line 1
Operand data type void type is invalid for sum operator.

I can easily fix if I use ISNULL Function as displayed following.

SELECT SUM(data)
FROM (SELECT ISNULL(NULL,0) AS DATA) t

Above script will not throw an error. However, there is one more method how this can be fixed.

Can you come up with another method which will not generate error?

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

SQL SERVER – T-SQL Scripts to Find Maximum between Two Numbers

There are plenty of the things life one can make it simple. I really believe in the same. I was yesterday traveling for community related activity. On airport while returning I met a SQL Enthusiast. He asked me if there is any simple way to find maximum between two numbers in the SQL Server. I asked him back that what he really mean by Simple Way and requested him to demonstrate his code for finding maximum between two numbers.

Here is his code:

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 - @Value2))) AS MaxColumn
GO

I thought his logic was accurate but the same script can be written another way. I quickly wrote following code for him and which worked just fine for him.

Here is my code:

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn
GO

He agreed that my code is much simpler but as per him there is some problem with my code which apparently he does not remember at this time. There are cases when his code will give accurate values and my code will not. I think his comment has value but both of us for the moment could not come up with any valid reason.

Do you think any scenario where his code will work and my suggested code will not work?

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

SQLAuthority News – Download Whitepaper – SQL Server 2008 R2 Analysis Services Operations Guide

SQL Server Analysis Service (SSAS) has been always interesting subject for research. Analysis Services cubes are a very powerful tool in the hands of the business intelligence (BI) developer. They provide an easy way to expose even large data models directly to business users. Microsoft has published very informative white paper on Analysis Services Operations Guide. This white paper is authored by Thomas Kejser, John Sirmon, and Denny Lee.

In this guide you will find information on how to test and run Microsoft SQL Server Analysis Services in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 in a production environment. The focus of this guide is how you can test, monitor, diagnose, and remove production issues on even the largest scaled cubes. This paper also provides guidance on how to configure the server for best possible performance. It is the goal of this guide to make your operations processes as painless as possible, and to have you run with the best possible performance without any additional development effort to your deployed cubes. In this guide, you will learn how to get the best out of your existing data model by making changes transparent to the data model and by making configuration changes that improve the user experience of the cube.

Download SQL Server 2008 R2 Analysis Services Operations Guide

Note: Abstract taken white paper.

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

SQL SERVER – BI Quiz Hint – Performance Tuning Cubes – Hints

I earlier wrote about SQL BI Quiz over here and here.

The details of the quiz is here:

Working with huge data is very common when it is about Data Warehousing. It is necessary to create Cubes on the data to make it meaningful and consumable. There are cases when retrieving the data from cube takes lots of the time. Let us assume that your cube is returning you data very quickly. Suddenly on one day it is returning the data very slowly. What are the three things will you to diagnose this. After diagnose what you will do to resolve performance issue.

Participate in my question over here

I required BI Expert Jason Thomas to help with few hints to blog readers. He is one of the leading SSAS expert and writes a complicated subject in simple words.

If queries were executing properly before but now take a long time to return the data, it means that there has been a change in the environment in which it is running. Some possible changes are listed below:-

 1) Data factors:– Compare the data size then and now. Increase in data can result in different execution times. Poorly written queries as well as poor design will not start showing issues till the data grows. How to find it out?

(Ans : SQL Server profiler and Perfmon Counters can be used for identifying the issues and performance  tuning the MDX queries)

 2) Internal Factors:- Is some slow MDX query / multiple mdx queries running at the same time, which was not running when you had tested it before? Is there any locking happening due to proactive caching or processing operations? Are the measure group caches being cleared by processing operations?

(Ans : Again, profiler and perfmon counters will help in finding it out. Load testing can be done using AS Performance Workbench (http://asperfwb.codeplex.com/) by running multiple queries at once)

 3) External factors:- Is some other application competing for the same resources?

 HINT : Read “Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services” (http://sqlcat.com/whitepapers/archive/2007/12/16/identifying-and-resolving-mdx-query-performance-bottlenecks-in-sql-server-2005-analysis-services.aspx)

Well, these are great tips. Now win big prizes by participate in my question over here.

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

SQLAuthority News – Ahmedabad Tech Ed On Road June 11, 2011 – An Event to Remember – A Grand Success of Community Tech Days

I am very excited to announce the huge success of the Microsoft Community TechDays at Ahmedabad, on 11 June 2011.  The turn-out for this seminar was huge, and there was a great response from the audience.  In fact, the AMA where the conference was held can seat 275 people – but there were over 50 people standing, the event coordinators had to find 150 more chairs, and we even had to turn away 30 people at the door because there was just no more room.  This means that there were over 500 attendees!

The event started right on time, at 10 am, with my introduction and welcome to the audience.  My presentation on my favorite subject of SQL Server Performance Troubleshooting Using Waits and Queues.  Because of the number of speakers, I had to cut my presentation short by 10 minutes, so I only had 50 minutes to explain how to use swaits and queues to fine tune performance.  There was a good response to my talk from audience.

I feel the best presentation, though, was “HTML5 – Future of the Web” by Harish Vaidyanathan.  He explained how HTML5 is going to change the internet, and taught everyone a lot about how to best use Internet Explorer 9, and discussed CSS3, SVG and DOM specifications.  Many people in the audience came specifically for this session – many had to take a half day leave off work just to travel there.

At this point we all took a break for lunch, but there was no one taking a nap with a full stomach because we had a presentation of the new Windows Mango phone from Dhananjay Kumar.  New technology like this always wakes everyone up!

After this came “TSQL Worst Practices” by Jacob Sebastian.  He too had to cut his talk short by 10 minutes in order to accommodate everyone, but his discussion of what SQL queries to avoid was still excellent. He is magnificent presenter and Ahmedabad loves him.

The final presentation was “ASP.NET Tips and Tricks” by Tejas Shah.  This was a good overview of asp.net fundamentals, and how to use them to improve application performance.  However, the day was not over here!  We kept the audience entertained with prizes and give-aways.  Names were drawn for prizes and there was a quiz session with great gifts for the winners.

Overall, the day was a huge success.  There was a good mix of SQL and non-SQL subjects, and many audiences members commented on how much they learned.  We had a much bigger turn-out than expected – all the chairs were filled 45 minutes before we even started!  For our next conference we need to find a space that will hold everyone, especially since we are hoping to have 600-800 people attending.  We definitely feel we can reach this goal.  We are already looking forward to the next Ahmedabad Microsoft Community TechDays.

Download presentations:

HTML5 Beauty of Web -By Harish Vaidyanathan

TSQL Worst Practices- By Jacob Sebastian

SQL SERVER Performance troubleshooting using Waits and Queues -By Pinal Dave

ASP.NET Tips and Tracks -By Tejas Shah

Other reports:

Tech-Ed on Road 2011- Ahmedabad–A great event- By Jalpesh

Tech-Ed 2011 on the Road in Ahmedabad – by Ritesh Shah

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