Writing SQL Queries for Dataverse – SELECT, WHERE, LIKE and More

In my previous post, I introduced an interesting addition to the Dataverse developer toolbox: the ability to send SQL queries through the Dataverse Web API.

Knowing that SQL is available is one thing.

Actually writing queries with it is where things start getting interesting.

At first glance, the syntax feels immediately familiar:

SELECT name, telephone1
FROM account
WHERE statecode = 0
ORDER BY name

If you have worked with SQL before, there isn’t much here that needs explaining.

We are selecting columns, querying a table, filtering the records and sorting the result.

But that familiarity can also be slightly dangerous.

Dataverse isn’t exposing a general-purpose SQL Server endpoint here. It supports a specific read-only subset of T-SQL, and there are some important rules around how SELECT, WHERE, functions and expressions can be used.

So let’s go beyond a basic SELECT and see what writing real Dataverse SQL queries actually looks like.

Start by Selecting Only What You Need

Let’s begin with Accounts.

Suppose an integration only needs the Account name, telephone number and website.

We can query those columns explicitly:

SELECT
    a.name,
    a.telephone1,
    a.websiteurl
FROM account AS a

The alias isn’t mandatory in a simple query, but using one can make queries much easier to read once joins start appearing.

There is also an important difference from the SQL many of us are accustomed to writing.

This won’t work:

SELECT *
FROM account

SELECT * isn’t supported.

Dataverse requires us to explicitly specify the columns we want to retrieve.

So instead of asking for everything:

SELECT *
FROM account

we should ask only for what the application actually needs:

SELECT
    name,
    telephone1,
    websiteurl
FROM account

That restriction also encourages a good API practice: don’t retrieve data you aren’t going to use.

Making the Output Easier to Understand with Aliases

Column aliases are supported as well.

For example:

SELECT
    a.name AS account_name,
    a.telephone1 AS phone
FROM account AS a

Instead of returning the original Dataverse logical column names as our primary output names, we can give them names that make more sense to the consuming application.

For example, the response can contain values such as:

{
    "account_name": "Wide World Importers",
    "phone": "(555) 100-0006"
}

This becomes particularly useful when we eventually start joining multiple tables where similarly named columns might otherwise become confusing.

There is another limitation worth knowing here.

Selecting arbitrary literal values, calculations and functions isn’t supported.

For example, don’t expect queries such as this to work:

SELECT
    'Account' AS record_type,
    1 + 2 AS calculated_value,
    name
FROM account

The SQL interface is intentionally constrained around retrieving Dataverse data rather than providing the full expression capabilities of SQL Server.

Filtering Dataverse Data with WHERE

Most real-world queries don’t retrieve every row from a table.

We normally have some business condition that determines which records we want.

This is where WHERE comes in.

Suppose we only want active Accounts:

SELECT
    name,
    statecode
FROM account
WHERE statecode = 0

The supported comparison operators include:

=
!=
<>
<
>
<=
>=

So excluding inactive records could also be expressed as:

SELECT
    name,
    statecode
FROM account
WHERE statecode <> 1

Multiple conditions can be combined using AND and OR.

For example:

SELECT
    name,
    telephone1
FROM account
WHERE statecode = 0
    AND telephone1 IS NOT NULL

Now we aren’t simply asking for active Accounts.

We specifically want active Accounts that actually have a telephone number.

That is starting to look much more like the queries we encounter in real Dataverse implementations.

AND, OR and Parentheses Matter

As our business rules become more complicated, we can combine conditions.

Suppose we want records where the Account name is either Contoso or Fabrikam:

SELECT name
FROM account
WHERE name = 'Contoso'
   OR name = 'Fabrikam'

Or perhaps our condition is:

• Account can be active or inactive
• But it must have a telephone number

We can express that using parentheses:

SELECT
    name,
    telephone1
FROM account
WHERE (statecode = 0 OR statecode = 1)
    AND telephone1 IS NOT NULL

This is familiar SQL territory, but parentheses become important when mixing AND and OR because they make the intended evaluation order explicit.

Searching Text with LIKE

Exact matches aren’t always enough.

Imagine we want Accounts whose names start with “Fourth”.

We can use:

SELECT name
FROM account
WHERE name LIKE 'Fourth%'

The % wildcard matches any sequence of characters.

So:

'Fourth%'

could match:

Fourth Coffee
Fourth Coffee Australia
Fourth Consulting

The _ wildcard can be used to represent a single character.

For example:

SELECT name
FROM account
WHERE name LIKE '_ontoso'

could match:

Contoso

We can also exclude patterns using NOT LIKE.

For example, test data frequently finds its way into environments:

SELECT name
FROM account
WHERE name NOT LIKE '%test%'

That gives us a simple way of excluding Accounts whose names contain “test”.

Be Careful with Leading Wildcards

There is an important performance consideration with LIKE.

This query may look innocent:

SELECT name
FROM account
WHERE name LIKE '%Coffee'

But the leading wildcard can be expensive because Dataverse may need to perform a full table scan.

Where possible, Microsoft recommends preferring a trailing wildcard:

SELECT name
FROM account
WHERE name LIKE 'Fourth%'

A trailing wildcard has the potential to use an index, whereas a leading wildcard can hurt query performance.

This is a small difference in syntax that can become significant once we’re querying tables containing large numbers of rows.

Filtering Against Multiple Values with IN

Sometimes we have a known list of values that should be included.

Instead of writing:

SELECT name
FROM account
WHERE name = 'Contoso'
   OR name = 'Fabrikam'
   OR name = 'Fourth Coffee'

we can make the intention clearer using IN:

SELECT name
FROM account
WHERE name IN (
    'Contoso',
    'Fabrikam',
    'Fourth Coffee'
)

The reverse is available through NOT IN:

SELECT name
FROM account
WHERE name NOT IN (
    'Contoso',
    'Fabrikam'
)

For a developer reading the query later, IN can make a multi-value filter much easier to understand.

Filtering Ranges with BETWEEN

Dataverse SQL also supports BETWEEN.

For example:

SELECT name
FROM account
WHERE name BETWEEN 'A' AND 'B'

BETWEEN represents an inclusive range.

Although this is a simple example, range-based filtering becomes particularly useful when dealing with suitable numeric or date-oriented filtering scenarios.

NULL Isn’t the Same as = NULL

This is standard SQL behaviour, but it is worth calling out because it is an easy mistake to make.

Suppose we want Accounts without a telephone number.

This isn’t the correct approach:

SELECT
    name,
    telephone1
FROM account
WHERE telephone1 = NULL

Instead, use:

SELECT
    name,
    telephone1
FROM account
WHERE telephone1 IS NULL

And to find Accounts where a telephone number exists:

SELECT
    name,
    telephone1
FROM account
WHERE telephone1 IS NOT NULL

The Dataverse SQL documentation explicitly calls out that = NULL doesn’t produce the expected result.

Use IS NULL and IS NOT NULL.

Removing Duplicates with DISTINCT

Suppose we want to know which cities appear across our Account records.

Returning every Account isn’t necessary.

Instead:

SELECT DISTINCT
    a.address1_city
FROM account AS a

DISTINCT removes duplicate values from the result.

This can be useful when we’re interested in the unique values represented within a column rather than individual Dataverse records.

Sorting the Result with ORDER BY

Once we’ve selected and filtered the records, we often need predictable ordering.

For example:

SELECT
    name,
    telephone1
FROM account
ORDER BY name ASC

ASC sorts the result in ascending order and is the default.

We can also sort descending:

SELECT
    name,
    createdon
FROM account
ORDER BY createdon DESC

Multiple columns can be used:

SELECT
    name,
    createdon
FROM account
ORDER BY
    name ASC,
    createdon DESC

That would sort primarily by Account name and then by creation date within matching names.

There is another restriction to remember here.

ORDER BY can reference columns, but complex expressions aren’t supported.

Something like:

ORDER BY LEN(name)

isn’t supported.

Again, this reinforces the idea that we are dealing with a deliberately constrained SQL implementation.

What About Dates?

There is an interesting exception when working with date filtering.

Dataverse SQL supports DATEADD and GETUTCDATE in specific circumstances.

For example:

SELECT a.name
FROM account AS a
WHERE a.createdon >= DATEADD(
    day,
    -3,
    GETUTCDATE()
)

This allows us to retrieve Accounts created within a range relative to the current UTC time.

But there is an important restriction.

Functions can’t be applied directly to column values.

For example, this isn’t supported:

SELECT a.name
FROM account AS a
WHERE DATEADD(day, 3, a.createdon) >= GETUTCDATE()

Instead, the function needs to operate against a literal value or another supported function, leaving the Dataverse column as the other side of the comparison.

So this works:

WHERE a.createdon >= DATEADD(day, -3, GETUTCDATE())

while this doesn’t:

WHERE DATEADD(day, 3, a.createdon) >= GETUTCDATE()

It is a subtle distinction, but exactly the kind of restriction that developers need to understand before assuming that normal SQL patterns will translate directly.

WHERE Has Some Important Boundaries

There are several other familiar SQL patterns that aren’t supported in the WHERE clause.

For example:

WHERE accountid IN (
    SELECT accountid
    FROM account
)

Subqueries aren’t supported.

Neither are:

WHERE EXISTS (...)

or:

WHERE NOT EXISTS (...)

Even something as simple as:

WHERE 1 = 1

isn’t supported.

That last one might surprise developers who dynamically construct SQL queries and commonly start their conditions with WHERE 1 = 1.

Column-to-column comparisons are another limitation.

This isn’t supported:

WHERE modifiedon > createdon

Nor can we use arbitrary expressions such as:

WHERE revenue > 500 + 125

These restrictions matter because they reinforce something I mentioned in my previous post:

Knowing SQL doesn’t automatically mean you know Dataverse SQL.

The syntax is familiar.

The boundaries are different.

Putting It Together

Let’s finish with something closer to a query we might actually use.

Imagine an integration needs active Accounts that:

• Have a telephone number
• Don’t contain “test” in their name
• Were created within the last three days
• Are sorted alphabetically

We can express that as:

SELECT
    a.name,
    a.telephone1,
    a.createdon
FROM account AS a
WHERE a.statecode = 0
    AND a.telephone1 IS NOT NULL
    AND a.name NOT LIKE '%test%'
    AND a.createdon >= DATEADD(day, -3, GETUTCDATE())
ORDER BY a.name ASC

Now the capability starts to feel less like a technical curiosity and more like something we can potentially use in real integrations.

The query is readable.

The business conditions are easy to identify.

And for developers coming from a SQL background, the intent is immediately recognisable.

But we also need to remember the constraints we’ve seen throughout this article.

Familiar Syntax, Different Rules

That’s probably the best way to describe SQL through the Dataverse Web API.

At first glance:

SELECT
FROM
WHERE
LIKE
IN
BETWEEN
ORDER BY

all feel exactly as expected.

And for many straightforward queries, they largely are.

But underneath that familiar syntax is a deliberately restricted Dataverse query interface.

Understanding those restrictions is what separates simply getting a SQL query to work from designing queries that are appropriate for Dataverse.

And so far, we have only queried a single table.

Things become much more interesting when Dataverse relationships enter the picture.

Because instead of thinking about $expand or link-entity, we can start writing something SQL developers know extremely well:

INNER JOIN

And that is where we’ll go next.