Data World

Archive for the ‘MCSA 70-461’ Category

Exam 70-461 Notes – Set Theory

Posted by Pramod Singla on March 23, 2017


  1. Ordered list of returned result set is called Cursor.

  2. Without “order by” clause query doesn’t return results in particular order even if clustered index is there.

  3. Standard SQL must have “Select” and “have” whereas T-SQL can work with only “Select”

  4. “dbo”.”order” standard where as [dbo].[order] is Microsoft format.

  5. T-SQL is based upon SQL which in turn based upon relational model which further is based upon set theory and predicate logic.

         T-SQL<- SQL <- relational model<- set theory and predicate logic.

Advertisements

Posted in Certifications, MCSA 70-461 | Tagged: , | Leave a Comment »

Exam 70-461 Notes – Grouping Sets

Posted by Pramod Singla on March 23, 2017


  1. Data Analysis function can be either a group function or a window function.

  2. T-SQL Supports 3 clauses that allow define multiple grouping sets.These operators can generate the same result set as when you use UNION ALL to combine single grouping queries.Here and here are the Details.

    • ROLLUP

    • CUBE

    • Grouping Sets

  3. Rollup is used where hierarchy is formed in the input columns like for Country,Region,city.

  4. Grouping

    Grouping_ID

    Excepts one input and returns 0 if element is in grouping set else 1

    Excepts lists of columns as input and return a integer representing bitmap.

    GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values.

    https://msdn.microsoft.com/en-us/library/ms178544.aspx

    https://msdn.microsoft.com/en-us/library/bb510624.aspx

Posted in Certifications, Development, MCSA 70-461 | Tagged: , | Leave a Comment »