In this blog let's understand the difference between selecting columns in SQL vs DAX, i am using a fairly simple Product table which contains a limited column but enough for supporting the examples:
To compare the results I am going to SQL Server Management Studio and DAX Studio
Selecting a Table
The most basic query that you can write in DAX and SQL is just simply selecting the whole table:
SQL:
DAX:
Bonus: If you are into DAX optimizations then you would be aware about xmSQL ( Pseudo SQL code that is executed against the storage engine to extract data caches, this internally is in the form of binary but has been converted into human readable format by DAX Studio )
You can see that the simple selection of a table in DAX internally executes an xmSQL query that requests for all of the columns from the Products table.
The row number is used internally by DAX engines to optimized the query.
Selecting a single or multiple columns
To select multiple or single column you would write a following query in SQL:
DAX equivalent:
DAX Storage Engine queries:
Selecting unique rows
To select unique rows for the column specified in SQL you would write:
Now there are way too many ways of getting unique rows in DAX, so let's cover them all with the difference.
ALL
Query Plan: Notice there is no DISTINCT in the xmSQL, the reason is DISTINCT/GROUP BY is implicit in xmSQL query
DISTINCT
This time I have wrapped the SELECTCOLUMNS inside DISTINCT
Query Plan:
SUMMARIZE
Query Plan:
SUMMARIZECOLUMNS
Query Plans:
