How to Use Salesforce Marketing Cloud (SFMC) Query Studio

Query studio is probably my most-used AppExchange App in Salesforce Marketing Cloud (SFMC). It’s a free app for quickly querying SFMC data extensions and data views.

The version of SQL used in SFMC is based on, but doesn’t precisely correspond to, SQL Server 2016 capabilities. The main difference from standard SQL is that SFMC supports only SELECT statements. INSERT, UPDATE, and DELETE are not officially supported, because the queries are executed as a part of the backend query and are limited to provide non-breakable functionality. 

Unsupported SQL Functions in SFMC Query Studio

The following SQL functions are either not supported or require adjustments in SFMC Query Studio. 

WITH Clause

SFMC Query Studio only accepts SELECT statements, meaning that WITH clauses, which are one level above, are not supported.

Instead, use subqueries such as the below.

SELECT TOP 10 a.[Subscriber Key],
(
    SELECT TOP 1 COUNT(b.[Subscriber Key]) as ct 
    FROM [testDE] 
    WHERE b.[Subscriber Key] = a.[Subscriber Key]
) as Cnt
FROM [testDE] a

This query should validate and run with no issues, returning the expected results. Even if an error persists in SFMC Query Studio ("Unable to create temporary data extension: Field name cannot be blank."), it will definitely work in Automation Studio.

ORDER BY

Anyone who has tried to use ORDER BY in SFMC Query Studio has probably encountered this error before.

An error occurred while checking the query syntax. Errors: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

To get around this, simply add TOP 100 PERCENT or use a subquery to rank fields by subscriber.

LIMIT

Similarly, queries using the LIMIT expression will need to be written using TOP, as SFMC query studio uses T-SQL syntax.

The query below will return an error:

SELECT SubscriberKey, EmailAddress 
    FROM MASTER_IMPORT
        WHERE EmailAddress LIKE "%gmail.com" LIMIT 300

Rewrite it using the TOP expression:

SELECT TOP 300 SubscriberKey, EmailAddress 
FROM MASTER_IMPORT
WHERE EmailAddress LIKE "%gmail.com"

SELECT *

Unfortunately, SFMC Query Studio does not support SELECT *. Instead, you'll need to write out all of the column names that you need. If a column name contains a space, hyphen or is a SQL's reserved keyword, you need to capture it with squared brackets.

SELECT
	ContactKey
	, EmailAddress
	, [External Contact Id]

Using Object Explorer on the right side of SFMC Query Studio, you can type in any DE name, and it will show you all of the fields. I find it useful to copy out the fields to Excel for some formatting, and then copy them back into query.

Single Line Comments

SFMC Query Studio generally does not support the single-line SQL comment using two dashes (–). I've gotten away with very short (one or two words) single line SQL comments, but anything longer and I'm usually met with a prompt to use multi-line comments instead (even if the comment is single-line).

Date Functions

SFMC Query Studio does not support user-defined functions, but there are multiple date functions that are useful on a day-to-day basis for marketing automation purposes. In many cases, GETDATE and DATEADD are all I need.

SELECT subscriberkey
FROM testDE
WHERE createdate < DATEADD(year, -1, GETDATE())

You can also simply hard-code a date in yyyy-mm-dd format.

SELECT subscriberkey
FROM testDE
WHERE createdate < '2024-01-01'

GETDATE gets server time, which for SFMC is Central Standard Time (UTC -6), without changes between standard and daylight savings time. This remains the case even if you update your timezone settings in Setup. To get date data in your desired timezone, use the SYSDATETIMEOFFSET() function.

SFMC Query Studio Results

SFMC query studio allows us to skip the tedious process of setting up a SQL Activity in Automation Studio and manually defining a target Data Extension. Instead, the query results are automatically saved as temporary Data Extensions for quick access.

Navigate to Data Extensions > QueryStudioResults. This is where all of the query outputs are stored for 24 hours. Each result is saved as a separate Data Extension with a timestamped name (e.g., *"QueryStudioResults at 2025-07-21 T0016300000"*), making it easy to identify recent queries.

---

Although SFMC query studio can be a bit buggy at times, it has still been a huge time saver for me. Overall, I’m satisfied with this app, especially since it’s completely free. If you haven’t already, download SFMC query studio and give it a try.