Question 341 – What is the use of CUBE Operator in SQL?

The CUBE Operator is useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.

Question 342 – What is the Difference between ROLLUP and COMPUTE?

• ROLLUP returns a single result set while COMPUTE BY returns multiple result sets that increase the complexity of application code.
• ROLLUP can be used in a server cursor while COMPUTE BY cannot.
• The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

Question 343 – What is the use of COMPUTE in SQL?

• The optional BY keyword. This calculates the specified row aggregate on a per column basis.
• A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
• A column upon which to perform the row aggregate function.

Question 344 – What is the use of COMPUTE BY in SQL?

• It allows you to see both detail and summary rows with one SELECT statement.
• You can calculate summary values for subgroups, or a summary value for the whole result set.

Question 345 – What is the use of With TIES in SQL?

• The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group.
• The SELECT TOP N WITH TIES query always return N records with any record having the same value as the last record.
• The TOP N PERCENT clause also do the same WITH TIES and Without TIES

Question 346 – What is the use of ALL & ANY operator in SQL?

• Comparison operators that introduce a SubQuery can be modified by the keywords ALL or ANY
  Using the > comparison operator as an example, >ALL means greater than every value, In other words, it means greater than the maximum value.
• For example, >ALL (1, 2, 3) means greater than 3. >ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.
• Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the SubQuery must be greater than at least one of the values in the list of values returned by the SubQuery

Question 347 – What is the use of Master Database in SQL?

This database holds information for all databases located on the SQL Server instance. SQL Server cannot start without a functioning master database.

Question 348 – What is the use of MSDB Database in SQL?

This database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

Question 349 – What is the use of TEMPDB Database in SQL?

This holds temporary objects such as global and local temporary tables and stored procedures.

Question 350 – What is the use of MODEL Database in SQL?

This is essentially a template database used in the creation of any new user database created in the instance.

Disclaimer – F5debug Interview Questions & Answers Series:

You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:
You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.