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:
Hi Antriksh, Based on this example you wrote do you think DAX can convert these TSQL code? /*
The query grabs the first time for each ID (the first SELECT, before the UNION ALL)
and then recurses, finding the next row for each ID which is at least 15 minutes appart.
*/
-- the delta reset when rowcount reach 15 min - not a windows of 15 min
WITH times AS (
SELECT e.ID, MIN(e.DTTM) AS DTTM
FROM example e
GROUP BY e.ID
UNION ALL
SELECT r.ID, r.DTTM
FROM (
SELECT e.ID, e.DTTM,
ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY e.DTTM) AS rn
FROM example e
INNER JOIN times t
ON e.ID = t.ID AND e.DTTM >= DATEADD(mi, 15, t.DTTM)
) r