25 Salesforce Marketing Cloud SQL Tips

While Salesforce Marketing Cloud supports a large subset of SQL Server syntax, the environment is optimized for segmentation and automation workflows rather than heavy analytical processing. Following a few best practices can help your queries run faster, produce predictable results, and prevent unexpected failures in Automation Studio. SQL in Automation Studio is based on Microsoft SQL Server (roughly SQL Server 2016 capabilities). Many familiar T-SQL functions are available, such as:

• ROW_NUMBER
• DATEADD
• DATEDIFF
• CASE
• CAST

Common Table Expressions (WITH) are not supported

One of unsupported feature is WITH.

WITH RecentJobs AS (
    SELECT
        JobID,
        EmailName
    FROM _Job
    WHERE CreatedDate >= DATEADD(day, -30, GETDATE())
)
SELECT
r.JobID,
s.SubscriberKey
FROM RecentJobs r
INNER JOIN _Sent s
ON r.JobID = s.JobID

Other unsupported elements:

• Variables
• Cursors
• User-Defined Functions
• Transaction and Locking
• GOTO
• PRINT
• Any sp_* stored procedure
• EXEC
• Temporary Tables and Common Table Expressions
• TEXT and IMAGE Functions
• Open-ended comment designations such as ‘–‘ to comment out a line

Avoid SELECT * in production queries

While SELECT * is convenient during testing, it is better practice to explicitly specify the columns you need.

• Improves query performance by reducing unnecessary data processing
• Protects your query if new columns are added to the source Data Extension
• Makes your SQL easier to maintain and understand

Avoid:

SELECT *
FROM _sent

Better:

SELECT
JobID,
ListID,
Domain,
SubscriberKey
FROM _sent

Align field types between source and target Data Extensions

Every Query Activity writes its results into a target Data Extension, so the field types between source and target must be compatibleCommon problems include:

• Writing a VARCHAR(100) value into a VARCHAR(50) field
• Writing a DATETIME value into a DATE column
• Writing numeric values into text fields

If field types do not match, the Query Activity may fail or truncate data.

Prefer EXISTS over NOT IN for exclusion logic

When building audiences in Salesforce Marketing Cloud, it is common to exclude subscribers who recently bounced. While some people use NOT IN for this logic, using NOT EXISTS is usually safer, faster and avoids NULL-related issues.

The following example selects active subscribers who have not bounced.

SELECT
s.SubscriberKey,
s.EmailAddress
FROM _Subscribers s
WHERE s.Status = 'active'
AND NOT EXISTS (
    SELECT 1
    FROM _Bounce b
    WHERE b.SubscriberKey = s.SubscriberKey AND
    b.BounceCategory = 'Hard bounce'
)

Use clear table aliases

When joining multiple tables or Data Views, aliases make queries easier to read and maintain.

SELECT
s.SubscriberKey,
s.EventDate,
j.EmailName
FROM _Sent s
JOIN _Job j
ON s.JobID = j.JobID

Filter early to improve performance

Many Data Extensions contain millions of records. Adding filters early reduces the amount of data processed and improves query performance.

SELECT
s.SubscriberKey,
s.EventDate,
j.EmailName
FROM _Sent s
JOIN _Job j
ON s.JobID = j.JobID

Be careful with NOT IN and NULL values

SELECT
s.SubscriberKey,
s.EventDate,
j.EmailName
FROM _Sent s
JOIN _Job j
ON s.JobID = j.JobID

Understand Query Definition Activity Write Modes

The SQL query itself is only part of the logic. The write mode of the Query Activity determines how results are inserted into the target Data Extension.

Mode	Behavior
Overwrite	replaces all records
Append	adds new records
Update	updates existing rows based on primary key if not found new record is added

Many segmentation problems occur because the write mode was misconfigured rather than the SQL being incorrect.

Consider Primary Keys to improve performance

In Salesforce Marketing Cloud, there is no direct way to create indexes on Data Extension columns. The only way to introduce indexing behavior is by defining a Primary Key. Primary keys can significantly improve query performance because Marketing Cloud internally indexes those fields. However, this approach must be used carefully, since primary keys enforce uniqueness and may cause queries to fail if duplicate values are inserted.

Primary keys help when:

• joining large Data Extensions
• updating records using Query Activities
• ensuring subscriber uniqueness

Use _CustomObjectKey to split large data loads

When working with very large Data Extensions (millions of rows), processing the entire dataset in a single query can be slow and resource intensive. One approach is to split the workload into smaller batches using the internal _CustomObjectKey field.

_CustomObjectKey is an internal identifier automatically assigned to rows in Data Extensions. It can be used to divide datasets into ranges for batch processing.

Query activity 1

SELECT
SubscriberKey,
EmailAddress
FROM Customers_DE
WHERE _CustomObjectKey % 2 = 0

Query activity 2

SELECT
SubscriberKey,
EmailAddress
FROM Customers_DE
WHERE _CustomObjectKey % 2 = 1

This technique distributes records evenly across batches and helps reduce the workload of individual Query Activities.

When this technique is useful

This approach is commonly used when:

• processing very large Data Extensions
• splitting heavy segmentation queries
• preventing Automation Studio timeouts
• distributing workloads across multiple automations

Because _CustomObjectKey values are distributed across the dataset, modulo operations usually produce balanced batches without requiring knowledge of the dataset size.

Use ROW_NUMBER() for deduplication or batch processing

The ROW_NUMBER() window function is very useful in Salesforce Marketing Cloud when you need to either deduplicate records or divide a dataset into manageable batches.

Deduplicate records (keep latest record)

SELECT
t.SubscriberKey,
t.EmailAddress,
t.CreatedDate
FROM (
SELECT
SubscriberKey,
EmailAddress,
CreatedDate,
ROW_NUMBER() OVER (PARTITION BY SubscriberKey ORDER BY CreatedDate DESC) AS rn
FROM Customers_DE
) t
WHERE t.rn = 1

How it works:

1. ROW_NUMBER() assigns a sequential number to each row.
2. PARTITION BY SubscriberKey groups records per subscriber.
3. ORDER BY CreatedDate DESC places the newest record first.
4. WHERE rn = 1 keeps only the newest record.

This is one of the most common deduplication techniques in SFMC SQL.

Split large datasets using ROW_NUMBER()

You can also use ROW_NUMBER() to divide a dataset into smaller batches. This can help when processing large Data Extensions that might otherwise slow down Automation Studio queries.

Example splitting records into two batches.

Batch 1

SELECT *
FROM (
SELECT
SubscriberKey,
EmailAddress,
ROW_NUMBER() OVER (ORDER BY SubscriberKey) AS rn
FROM Customers_DE
) t
WHERE rn % 2 = 0

Batch 2

SELECT *
FROM (
SELECT
SubscriberKey,
EmailAddress,
ROW_NUMBER() OVER (ORDER BY SubscriberKey) AS rn
FROM Customers_DE
) t
WHERE rn % 2 = 1

Avoid very wide Data Extensions

Data Extensions with many columns or very large text fields can slow down queries.

Performance improves when tables are designed with:

• fewer columns
• appropriate field sizes
• clear primary keys

Use staging Data Extensions for complex workflows

For complex transformations, it is often better to use multiple Query Activities.

Example workflow:

1. Filter source data
2. Perform joins
3. Deduplicate results
4. Write final audience

This improves stability, readability and avoids timeouts

WITH (NOLOCK) is not supported, but the platform already behaves this way

Developers coming from SQL Server environments often try to use the WITH (NOLOCK) table hint to prevent queries from being blocked by locks.

Example from SQL Server:

SELECT
SubscriberKey,
EmailAddress
FROM Customers_DE WITH (NOLOCK)

However, Salesforce Marketing Cloud does not support SQL Server table hints, including WITH (NOLOCK).

The good news is that you typically do not need it. Salesforce Marketing Cloud SQL queries automatically run using the equivalent of:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This behavior is functionally similar to using WITH (NOLOCK) in SQL Server. Queries read data without waiting for locks created by other operations, which helps prevent blocking during automation runs.

Because of this platform behavior:

• NOLOCK cannot be used explicitly
• but queries already behave as if it were enabled

So the Salesforce Marketing Cloud query simply omits the NOLOCK even though the syntax will allow it.

Understand Data Views and their retention limits

Salesforce Marketing Cloud provides system tables called Data Views that store operational data generated by the platform. Unlike regular Data Extensions, Data Views are maintained automatically and allow you to query tracking and engagement information such as email sends, opens, clicks, bounces, and subscriber status.

Most Data Views in Salesforce Marketing Cloud retain only about six months of data.

Common examples include:

• _Sent
• _Open
• _Click
• _Bounce

If you require longer historical data, it is recommended to store tracking information in your own Data Extensions using scheduled automations.

You should also be aware that Data Views are not updated in real time. In some cases, data may appear with a delay, and in rare situations the delay can exceed 72 hours.

Use _Job to enrich tracking queries

Many tracking queries become more useful when joined with _Job.

Example fields available in _Job:

• EmailName
• EmailSubject
• Category
• SendClassification

SELECT
s.SubscriberKey,
j.EmailName,
s.EventDate
FROM _Sent s
JOIN _Job j
ON s.JobID = j.JobID

Be careful with DISTINCT on large datasets

DISTINCT forces the platform to compare rows across the dataset, which can slow down queries significantly.

If duplicates exist, it is often better to solve the root problem using:

• ROW_NUMBER() logic
• proper joins
• cleaner source data

Document your queries with comments

Automation workflows often run for years. Adding comments helps future maintainers understand the purpose of each query.

SELECT
s.SubscriberKey,
j.EmailName,
s.EventDate
FROM _Sent s
JOIN _Job j
ON s.JobID = j.JobID

Since single-line comments -- may pass the syntax check but wont let you save the query definition, prefer multi-line comments.

Use a Last Run Timestamp to Process Daily Deltas

When extracting or processing tracking data from Salesforce Marketing Cloud Data Views, it is common to export data from the previous day. This approach works fine until an automation fails, skips a run, or is delayed. When that happens, records can be missed or duplicated.

A more reliable approach is to process only new records since the last successful automation run, using a stored timestamp as a reference point.

SQL queries are case-insensitive

Salesforce Marketing Cloud SQL is generally case-insensitive. This means that keywords, table names, and column names can be written in any letter case and the query will still execute.

SELECT
s.SubscriberKey,
j.EmailName,
s.EventDate
FROM _Sent s
JOIN _Job j
ON s.JobID = j.JobID

works the same as:

SELECT
s.Subscriberkey,
j.emailname,
s.eventdate
FROM _sent s
JOIN _job j
ON s.jobid = j.jobid

Both queries return identical results.

However, while the SQL engine itself is case-insensitive, it is still considered good practice to maintain consistent naming conventions for readability and maintainability.

For example:

• use uppercase for SQL keywords (SELECT, FROM, WHERE)
• keep column names consistent with the Data Extension schema
• avoid mixing different naming styles in the same query

Maintaining consistent formatting makes queries easier to understand and maintain, especially in large automation workflows.

Share With Others