The CUBE Operator is useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.
- 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.
- 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.
- 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.
- 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
- 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
This database holds information for all databases located on the SQL Server instance. SQL Server cannot start without a functioning master database.
This database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
This holds temporary objects such as global and local temporary tables and stored procedures.
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.