Querying CSV Files with OPENROWSET

A serverless SQL pool is a query engine with no database attached to it. It doesn't hold your data — it reaches out to wherever your data already lives, reads it, and hands back a result.

That single fact explains almost everything about how this chapter's syntax is shaped: why every query needs a schema spelled out by hand, why nothing you run ever "inserts" anywhere, and why the billing model looks nothing like a traditional database.

Serverless vs. dedicated, at a glance

OPENROWSET is the function that does the actual reading. Everything else in this chapter is really just options passed into it.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'raw/zone_lookup.csv',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
)
WITH (
    LocationID    SMALLINT,
    Borough       VARCHAR(15),
    Zone          VARCHAR(50),
    service_zone  VARCHAR(15)
) AS [r];

A CSV file is plain text. It has no embedded metadata — no column names guaranteed, no types, nothing a parser can inspect ahead of time the way it can with a self-describing format like Parquet.

Leave out the WITH clause on a CSV read, and this is what you get back:

The fix is always the same: add a WITH clause naming every column and its type. This is not optional cleanup — for CSV, it's the normal, expected way to write this query.

Sizing string columns deliberately

Guessing a VARCHAR length and getting it wrong doesn't fail quietly — the engine refuses to silently truncate data:

The right habit is to check the real maximum length before committing to a size, rather than guess and re-run:

SELECT
    MAX(LEN(LocationID))   AS len_location_id,
    MAX(LEN(Borough))      AS len_borough,
    MAX(LEN(Zone))         AS len_zone,
    MAX(LEN(service_zone)) AS len_service_zone
FROM OPENROWSET(
    BULK 'raw/zone_lookup.csv',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) AS [r];

Run that once, read the widest value per column, then size VARCHAR to comfortably cover it — with a little headroom for future rows you haven't seen yet.

Validating a schema without a full scan

There's a second tool worth knowing for this: sp_describe_first_result_set. It takes a query as a string and returns the schema that query would produce, without actually running it against the data.

EXEC sp_describe_first_result_set N'
SELECT *
FROM OPENROWSET(
    BULK ''raw/zone_lookup.csv'',
    DATA_SOURCE = ''nyc_taxi_data'',
    FORMAT = ''CSV'',
    PARSER_VERSION = ''2.0'',
    HEADER_ROW = TRUE
)
WITH (
    LocationID SMALLINT,
    Borough    VARCHAR(15),
    zone       VARCHAR(15),
    service_zone VARCHAR(15)
) AS [r]';

Two things are worth noticing in that call. The leading N before the string marks it as Unicode text (NVARCHAR) rather than plain VARCHAR — the procedure's parameter specifically expects Unicode, so the prefix isn't optional style, it's what the signature requires. And every single quote that belongs to the inner query is doubled ('') rather than single, because the whole statement is itself one big string literal — a single unescaped quote would end that literal early.

Both store text. The difference is which characters they can safely hold, and how much storage each character costs.

A field like service_zone, drawn from a small fixed set of English labels, is a safe fit for VARCHAR. Raw JSON text, user-entered free text, or anything that might carry a name in a non-Latin script belongs in NVARCHAR — and when the length is genuinely unpredictable, NVARCHAR(MAX) removes the guessing entirely.

Collation is the rule set that governs how text is compared and sorted — case sensitivity, accent sensitivity, and character encoding all live inside one named setting.

Synapse serverless pools consistently lean on one specific collation for reading data lake files:

The UTF8 segment is the one that matters most for this chapter's purposes. Data lake files are typically UTF-8 encoded, and without a UTF-8-aware collation, non-ASCII characters can come back garbled or compare incorrectly.

Setting it once, at the database level

Rather than repeat COLLATE on every VARCHAR column in every query, set it once on the database, and every column created afterward inherits it:

CREATE DATABASE nyc_taxi_discovery;

USE nyc_taxi_discovery;

ALTER DATABASE nyc_taxi_discovery
    COLLATE Latin1_General_100_CI_AI_SC_UTF8;

A per-column override is still available when a specific field needs different rules than the database default:

WITH (
    LocationID    SMALLINT,
    Borough       VARCHAR(15) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
    zone          VARCHAR(15) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
    service_zone  VARCHAR(15) COLLATE Latin1_General_100_CI_AI_SC_UTF8
) AS [r]

Not every file has a header row. Without column names to match against, the only way to identify a field is by counting its position — its ordinal — left to right, starting at 1.

Set HEADER_ROW = FALSE (or omit it — FALSE is the default), and unnamed columns fall back to a generic C1, C2, C3... naming scheme:

To assign real names and types, attach the ordinal position directly after each column's type in the WITH clause:

SELECT *
FROM OPENROWSET(
    BULK 'raw/zone_lookup_no_header.csv',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0'
)
WITH (
    Borough VARCHAR(15) 2,
    Zone    VARCHAR(50) 3
) AS [r];

Position 1 was skipped entirely here — mapping every ordinal position isn't required, only the ones actually needed. Notice, too, that the size mistake from section 7.3 is easy to repeat: Zone VARCHAR(15) is too narrow for "Allerton/Pelham Gardens" and throws the same truncation error, regardless of whether the column was matched by name or by position.

"CSV" is really shorthand for delimited text in general. The character that separates fields, the character that wraps a field, and the character that escapes a literal occurrence of either are all independently configurable.

Reading a tab-separated file

There is no separate FORMAT='TSV'. A TSV file is a CSV file that happens to use a tab as its delimiter, so the fix is simply to override FIELDTERMINATOR:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'raw/trip_type.tsv',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    HEADER_ROW = TRUE,
    PARSER_VERSION = '2.0',
    FIELDTERMINATOR = '\t'
)
WITH (
    trip_type   SMALLINT,
    description VARCHAR(50)
) AS [r];

Escaping a quote character embedded in a field

A field that legitimately contains the quote character has to mark it as literal, or the parser will treat it as the end of the field:

ESCAPECHAR = '\\'

The doubled backslash isn't T-SQL escaping anything — it's the literal value being passed to the option, telling the parser: the character used to escape things in this file is a single backslash. Without it set correctly, an embedded quote reads as the field's closing quote and the row breaks apart or throws a size-related error.

Typing the full abfss:// path in every single query gets old fast, and it's fragile — one storage account rename and every query breaks. A named data source solves both problems.

CREATE EXTERNAL DATA SOURCE nyc_taxi_data
WITH (
    LOCATION = 'abfss://nyc-taxi-data@<storage_account>.dfs.core.windows.net/'
);

This is metadata only — a saved alias, nothing more. No bytes move, and no copy is made anywhere. Every query that references it still reads the live file from storage at execution time, exactly as it would with a full inline path.

Private storage: adding a credential

Public containers need nothing further. A private, secured storage account needs a scoped credential attached to the data source — typically the same managed identity used elsewhere in a Unity Catalog or ADLS setup:

CREATE DATABASE SCOPED CREDENTIAL nyc_taxi_credential
WITH (
    IDENTITY = 'Managed Identity'
);

CREATE EXTERNAL DATA SOURCE nyc_taxi_data
WITH (
    LOCATION = 'abfss://nyc-taxi-data@<storage_account>.dfs.core.windows.net/',
    CREDENTIAL = nyc_taxi_credential
);

Nearly every error this chapter's syntax produces falls into one of a handful of categories.

A single end-to-end pass: register the pointer once, then read two different files against it — one with a header, one without.

CREATE EXTERNAL DATA SOURCE nyc_taxi_data
WITH (
    LOCATION = 'abfss://nyc-taxi-data@<storage_account>.dfs.core.windows.net/'
);

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'raw/zone_lookup.csv',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
)
WITH (
    LocationID    SMALLINT,
    Borough       VARCHAR(15)  COLLATE Latin1_General_100_CI_AI_SC_UTF8,
    Zone          VARCHAR(50)  COLLATE Latin1_General_100_CI_AI_SC_UTF8,
    service_zone  VARCHAR(15)  COLLATE Latin1_General_100_CI_AI_SC_UTF8
) AS [zones];

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'raw/trip_type_no_header.tsv',
    DATA_SOURCE = 'nyc_taxi_data',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    FIELDTERMINATOR = '\t'
)
WITH (
    trip_type   SMALLINT     1,
    description VARCHAR(50)  2
) AS [trip_types];

Same pointer, same underlying engine, two different shapes of source file — one leaning on a header row and named columns, the other reconstructed entirely from position and an explicit delimiter override.