Table of Contents

Class SqlServerDialect

Namespace
NextORM.SqlServer
Assembly
nextorm.sqlserver.dll

SQL Server dialect: bracket-quoted identifiers, @name parameters, offset/fetch paging (which requires an ORDER BY), count_big and a boolean-less rendering of subquery predicates.

public sealed class SqlServerDialect : SqlDialectBase, ISqlDialect
Inheritance
SqlServerDialect
Implements
Inherited Members
Extension Methods

Constructors

SqlServerDialect()

Creates the version-agnostic dialect and its capability renderers. Every version-dependent capability stays at its historical value, so SQL declared by this dialect keeps working on every supported server.

public SqlServerDialect()

Fields

Instance

The shared SQL Server dialect instance.

public static readonly SqlServerDialect Instance

Field Value

SqlServerDialect

Properties

BatchUsesJoinedCommand

SqlBatch runs each command in its own scope, so a #temp created by one command is not visible to the next; the joined command keeps them in one batch scope.

public override bool BatchUsesJoinedCommand { get; }

Property Value

bool

CreateTableAsSelectUsesSelectInto

SQL Server renders the materialisation as SELECT ... INTO rather than CREATE TABLE ... AS SELECT.

public override bool CreateTableAsSelectUsesSelectInto { get; }

Property Value

bool

Iif

SQL Server implements the iif conditional function and the choose value picker.

public override IIifRenderer Iif { get; }

Property Value

IIifRenderer

IndexHints

SQL Server renders index hints as a WITH (INDEX(...)) table hint.

public override IIndexHintRenderer? IndexHints { get; }

Property Value

IIndexHintRenderer

Lock

SQL Server supports row locking through the updlock/holdlock table hints.

public override ILockRenderer Lock { get; }

Property Value

ILockRenderer

NativeJsonProviderType

The native json column is transported as the plain CLR string: SqlClient has no DOM representation that binds and reads both a json and an nvarchar(max) column, so the native storage resolves to the existing text converter with an inferred nvarchar parameter (no SqlDbType.Json, no double serialization).

public override Type NativeJsonProviderType { get; }

Property Value

Type

NullSafeComparison

T-SQL has no IS [NOT] DISTINCT FROM before SQL Server 2022, so both forms are emulated over the ordinary equality primitives as a two-valued guarded predicate. Captured operands need an explicit SQL type, which is resolved from the compared column's metadata.

public override INullSafeComparisonRenderer? NullSafeComparison { get; }

Property Value

INullSafeComparisonRenderer

Pivot

SQL Server supports the native PIVOT/UNPIVOT source constructs.

public override IPivotRenderer Pivot { get; }

Property Value

IPivotRenderer

RequireSubqueryAlias

A SQL Server derived table (subquery in FROM) must have an alias.

public override bool RequireSubqueryAlias { get; }

Property Value

bool

RequiresIdentityInsertToggle

SQL Server writes explicit identity values only inside SET IDENTITY_INSERT ... ON/OFF.

public override bool RequiresIdentityInsertToggle { get; }

Property Value

bool

RequiresRecursiveCteUnionAll

SQL Server requires a recursive CTE body to use UNION ALL; a distinct UNION is rejected by the server, so preparation rejects it locally before execution and before the plan cache.

public override bool RequiresRecursiveCteUnionAll { get; }

Property Value

bool

ScalarFunctions

SQL Server renders every cross-provider scalar function (lpad/rpad through replicate).

public override IScalarFunctions ScalarFunctions { get; }

Property Value

IScalarFunctions

SessionInfoFunctions

SQL Server renders current_user/session_user as the ANSI keywords and the database/schema/version information through db_name()/schema_name()/@@version.

public override ISessionInfoFunctions SessionInfoFunctions { get; }

Property Value

ISessionInfoFunctions

SqlServerFunctions

SQL Server renders the T-SQL-only scalar functions (patindex, quotename, the trigonometric functions, the SQL/JSON constructors and aggregates, …). The renderer is bound to this dialect instance so the version-gated names answer from the caller's declaration and never from the version-blind singleton.

public override ISqlServerFunctions? SqlServerFunctions { get; }

Property Value

ISqlServerFunctions

StringFormats

SQL Server renders CLR format specifiers through FORMAT (a .NET formatter).

public override IStringFormatFunctions? StringFormats { get; }

Property Value

IStringFormatFunctions

SupportsAnyValueAggregate

any_agg<T>(T?) stays gated off on SQL Server: the T-SQL ANY_VALUE aggregate does not exist on the recorded engine build 17.0.5005.3 (Msg 195 in the aggregate and OVER () forms, at compatibility levels 160 and 170 and with PREVIEW_FEATURES = ON), so there is no execution contract to enable. The any_value<T>(T?) member is a deterministic refusal, not this aggregate's replacement; the providers that do enable any_agg keep their dispatch.

public override bool SupportsAnyValueAggregate { get; }

Property Value

bool

SupportsApply

SQL Server spells a lateral source as CROSS APPLY/OUTER APPLY.

public override bool SupportsApply { get; }

Property Value

bool

SupportsApplyOnPlainTable

CROSS APPLY/OUTER APPLY accept a plain table name, unlike a LATERAL reference.

public override bool SupportsApplyOnPlainTable { get; }

Property Value

bool

SupportsApproxCountDistinctAggregate

APPROX_COUNT_DISTINCT arrived with SQL Server 2019 (major version 15). Version-only, like every other function-availability gate here: no edition substitutes for a major version.

public override bool SupportsApproxCountDistinctAggregate { get; }

Property Value

bool

SupportsApproxPercentileCont

The approximate ordered-set percentiles arrived with SQL Server 2022 (major version 16). Name-specific by design: the broad ordered-aggregate and interpolating-percentile flags stay untouched, because SQL Server expresses those only as window functions.

public override bool SupportsApproxPercentileCont { get; }

Property Value

bool

SupportsApproxPercentileDisc

APPROX_PERCENTILE_DISC arrived with SQL Server 2022 (major version 16); name-specific for the same reason as SupportsApproxPercentileCont.

public override bool SupportsApproxPercentileDisc { get; }

Property Value

bool

SupportsBase64Decode

SQL Server 2025 has the native BASE64_DECODE function returning varbinary.

public override bool SupportsBase64Decode { get; }

Property Value

bool

SupportsBase64Encode

SQL Server 2025 has the native BASE64_ENCODE function over varbinary.

public override bool SupportsBase64Encode { get; }

Property Value

bool

SupportsBatch

SQL Server batches through SqlBatch.

public override bool SupportsBatch { get; }

Property Value

bool

SupportsBulkCopy

SQL Server has a native bulk path (SqlBulkCopy).

public override bool SupportsBulkCopy { get; }

Property Value

bool

SupportsChecksumAggregate

CHECKSUM_AGG has no version floor: every declared SQL Server major version has it.

public override bool SupportsChecksumAggregate { get; }

Property Value

bool

SupportsChoose

SQL Server is the only provider with the choose value-picker function.

public override bool SupportsChoose { get; }

Property Value

bool

SupportsCollation

SQL Server supports a per-expression COLLATE clause.

public override bool SupportsCollation { get; }

Property Value

bool

SupportsColumnDefault

SQL Server accepts DEFAULT as a value in the VALUES list.

public override bool SupportsColumnDefault { get; }

Property Value

bool

SupportsCreateTableAsSelect

SQL Server materialises a query into a table through SELECT ... INTO.

public override bool SupportsCreateTableAsSelect { get; }

Property Value

bool

SupportsCreateTableAsSelectIfNotExists

SQL Server's SELECT ... INTO has no IF NOT EXISTS clause.

public override bool SupportsCreateTableAsSelectIfNotExists { get; }

Property Value

bool

SupportsCrossDatabase

SQL Server qualifies a table with a database name ([db].[schema].[table]).

public override bool SupportsCrossDatabase { get; }

Property Value

bool

SupportsCryptGenRandom

SQL Server has the CRYPT_GEN_RANDOM cryptographic generator over a constant or variable length.

public override bool SupportsCryptGenRandom { get; }

Property Value

bool

SupportsCube

SQL Server supports GROUP BY CUBE (...).

public override bool SupportsCube { get; }

Property Value

bool

SupportsDateArithmetic

SQL Server has no interval arithmetic; addition is exposed through dateadd and the last day of the month through eomonth.

public override bool SupportsDateArithmetic { get; }

Property Value

bool

SupportsDateTimeOffsetTranslation

SQL Server has a native datetimeoffset, so DTO operations are supported and preserve the value's offset.

public override bool SupportsDateTimeOffsetTranslation { get; }

Property Value

bool

SupportsDateTrunc

SQL Server 2022 introduced datetrunc(datepart, date). It takes an unquoted part name and uses singular spellings (the ANSI/PostgreSQL microseconds/milliseconds are mapped); decade/century/millennium have no T-SQL equivalent.

public override bool SupportsDateTrunc { get; }

Property Value

bool

SupportsDefaultValues

SQL Server supports INSERT ... DEFAULT VALUES for an all-defaults row.

public override bool SupportsDefaultValues { get; }

Property Value

bool

SupportsDeleteJoin

SQL Server deletes rows based on a join through the DELETE <alias> FROM ... JOIN extension.

public override bool SupportsDeleteJoin { get; }

Property Value

bool

SupportsForJson

SQL Server renders the result set as JSON through a trailing FOR JSON clause.

public override bool SupportsForJson { get; }

Property Value

bool

SupportsForXml

SQL Server renders the result set as XML through a trailing FOR XML clause.

public override bool SupportsForXml { get; }

Property Value

bool

SupportsFullText

SQL Server renders the full-text predicates contains/freetext.

public override bool SupportsFullText { get; }

Property Value

bool

SupportsGreatestLeast

SQL Server 2022 introduced greatest()/least() with the standard syntax, so the base rendering applies. The functions are unavailable on older servers, but the dialect cannot detect the server version at SQL-generation time.

public override bool SupportsGreatestLeast { get; }

Property Value

bool

SupportsGroupingIdAggregate

GROUPING_ID has no version floor: every declared SQL Server major version has it.

public override bool SupportsGroupingIdAggregate { get; }

Property Value

bool

SupportsGroupingSets

SQL Server supports GROUP BY GROUPING SETS (...).

public override bool SupportsGroupingSets { get; }

Property Value

bool

SupportsIdentityFunction

SQL Server reads the last generated identity through the batch-scoped SCOPE_IDENTITY(). The identity-function terminal appends it to the insert as a single batch, which keeps SCOPE_IDENTITY() in scope and unaffected by triggers.

public override bool SupportsIdentityFunction { get; }

Property Value

bool

SupportsJoinHints

SQL Server renders a join hint inside the join clause, between the join kind and JOIN.

public override bool SupportsJoinHints { get; }

Property Value

bool

SupportsJson

True when the declared state says the server has a native json type: the declared major version is 17 or higher, or the declared engine edition is one of the products that has the type while reporting a lower version — SqlDatabase (5), AzureSqlManagedInstance (8) or MicrosoftFabricSqlDatabase (12). Edition 11 (AzureSynapseServerlessSqlPoolOrMicrosoftFabric) is deliberately excluded: its serverless pool has no json column type. The decision is a pure function of the declared state — the connection, the database compatibility level and SERVERPROPERTY('EngineEdition') are never probed, and the version-only gate is unchanged. The unversioned, edition-less dialect keeps false, so an existing nvarchar-backed [JsonColumn] mapping is never retargeted by Auto.

public override bool SupportsJson { get; }

Property Value

bool

SupportsLinkedServer

SQL Server qualifies a table with a linked server name ([server].[db].[schema].[table]).

public override bool SupportsLinkedServer { get; }

Property Value

bool

SupportsMerge

SQL Server expresses a key upsert as a MERGE over a VALUES derived source.

public override bool SupportsMerge { get; }

Property Value

bool

SupportsMergeBySourceDelete

SQL Server is the only supported provider with WHEN NOT MATCHED BY SOURCE.

public override bool SupportsMergeBySourceDelete { get; }

Property Value

bool

SupportsMergeConditionalBranches

SQL Server MERGE supports an explicit ON <condition> and WHEN ... AND <condition>.

public override bool SupportsMergeConditionalBranches { get; }

Property Value

bool

SupportsMergeDelete

SQL Server MERGE supports a WHEN MATCHED THEN DELETE branch.

public override bool SupportsMergeDelete { get; }

Property Value

bool

SupportsMergeStatement

SQL Server renders a general, multi-branch MERGE.

public override bool SupportsMergeStatement { get; }

Property Value

bool

SupportsOrdinalComparison

SQL Server can express ordinal comparison through the Latin1_General_100_BIN2 collation.

public override bool SupportsOrdinalComparison { get; }

Property Value

bool

SupportsOutput

SQL Server returns the generated identity through the OUTPUT inserted.<column> clause of the insert (preferred over the session-scoped SCOPE_IDENTITY()).

public override bool SupportsOutput { get; }

Property Value

bool

SupportsOutputInto

SQL Server writes the modified rows into an existing table through the OUTPUT ... INTO <target>(columns) form of a data-modifying statement.

public override bool SupportsOutputInto { get; }

Property Value

bool

SupportsPercentRankCumeDist

SQL Server 2012+ implements the percent_rank/cume_dist window functions.

public override bool SupportsPercentRankCumeDist { get; }

Property Value

bool

SupportsPercentileWindow

SQL Server renders percentiles as the PERCENTILE_CONT/PERCENTILE_DISC analytic functions (no exact ordered-set aggregate form).

public override bool SupportsPercentileWindow { get; }

Property Value

bool

SupportsProductAggregate

The PRODUCT aggregate arrived with SQL Server 2025, so it follows that gate.

public override bool SupportsProductAggregate { get; }

Property Value

bool

SupportsQueryHints

SQL Server renders statement-level hints as a trailing OPTION (...) clause.

public override bool SupportsQueryHints { get; }

Property Value

bool

SupportsRangeColumns

True when the provider can store a Range<T> as a pair of scalar columns declared with RangeColumnsAttribute; false by default. See SupportsRangeColumns.

public override bool SupportsRangeColumns { get; }

Property Value

bool

SupportsRawSqlSource

SQL Server supports a raw SQL derived table (FROM (<sql>) AS alias).

public override bool SupportsRawSqlSource { get; }

Property Value

bool

SupportsRegex

SQL Server 2025 matches with the RE2-based REGEXP_LIKE/REGEXP_REPLACE. The capability is unconditional: the static dialect cannot see the server version or sys.databases.compatibility_level, so translation targets 2025+ and SQL Server 2019/2022 rejects the emitted call at execution. On 2025 the match additionally requires database compatibility level 170, while REGEXP_REPLACE is available at every level. Flags: c (case-sensitive, the default) or i.

public override bool SupportsRegex { get; }

Property Value

bool

SupportsRollup

SQL Server supports GROUP BY ROLLUP (...) and GROUP BY CUBE (...).

public override bool SupportsRollup { get; }

Property Value

bool

SupportsSavepoints

SQL Server supports SAVE TRANSACTION and ROLLBACK TRANSACTION <name>; its ADO.NET release is a driver no-op, which this capability still reports as supported.

public override bool SupportsSavepoints { get; }

Property Value

bool

SupportsSelectWhereMinMax

SQL Server 2012+ renders SelectWhereMax/SelectWhereMin with a window function.

public override bool SupportsSelectWhereMinMax { get; }

Property Value

bool

SupportsSequentialAccess

Microsoft.Data.SqlClient streams LOB columns through GetStream/GetTextReader when the reader is opened with SequentialAccess.

public override bool SupportsSequentialAccess { get; }

Property Value

bool

SupportsSqlServer2022Functions

True when the declared server major version is 16 or later: the 2022 function surface (LEFT_SHIFT/RIGHT_SHIFT) is available.

public override bool SupportsSqlServer2022Functions { get; }

Property Value

bool

SupportsSqlServer2025Functions

True when the declared server major version is 17 or later: the 2025 function surface (REGEXP_COUNT, REGEXP_SPLIT_TO_TABLE, PRODUCT) is available.

public override bool SupportsSqlServer2025Functions { get; }

Property Value

bool

SupportsStoredProcedures

SQL Server executes a stored procedure by name with CommandType.StoredProcedure.

public override bool SupportsStoredProcedures { get; }

Property Value

bool

SupportsStringAgg

SQL Server 2017 introduced string_agg; it has no array type, so array_agg stays unavailable.

public override bool SupportsStringAgg { get; }

Property Value

bool

SupportsTableHints

SQL Server renders table hints as with (hint, ...) after the table name.

public override bool SupportsTableHints { get; }

Property Value

bool

SupportsTableValuedParameters

SQL Server binds a table-valued parameter natively through a user-defined table type.

public override bool SupportsTableValuedParameters { get; }

Property Value

bool

SupportsTablesInScopeHints

SQL Server renders tables-in-scope hints as a WITH (...) table hint on every table.

public override bool SupportsTablesInScopeHints { get; }

Property Value

bool

SupportsTemporalTable

SQL Server supports the FOR SYSTEM_TIME temporal-table clause, including CONTAINED IN.

public override bool SupportsTemporalTable { get; }

Property Value

bool

SupportsTemporaryCreateTableAsSelect

SQL Server has no CREATE TEMPORARY TABLE ... AS SELECT; a session-scoped table is ToTable("#name").

public override bool SupportsTemporaryCreateTableAsSelect { get; }

Property Value

bool

SupportsTextJson

SQL Server 2016+ renders the JSON-as-text functions (json_value, ...) over nvarchar.

public override bool SupportsTextJson { get; }

Property Value

bool

SupportsTruncate

SQL Server has a native TRUNCATE TABLE.

public override bool SupportsTruncate { get; }

Property Value

bool

SupportsUpdateJoin

SQL Server updates rows based on a join through the UPDATE <alias> ... FROM ... JOIN extension.

public override bool SupportsUpdateJoin { get; }

Property Value

bool

SupportsWithTies

SQL Server supports TOP(n) WITH TIES and FETCH NEXT ... WITH TIES.

public override bool SupportsWithTies { get; }

Property Value

bool

TableSample

SQL Server supports the TABLESAMPLE table modifier; only the SYSTEM method exists.

public override ITableSampleMethods TableSample { get; }

Property Value

ITableSampleMethods

UuidGenerators

SQL Server can generate a random UUID through newid(); it has no v7 generator.

public override IUuidGenerators UuidGenerators { get; }

Property Value

IUuidGenerators

XmlFunctions

SQL Server is the only provider with the postfix XML data-type methods.

public override IXmlFunctions XmlFunctions { get; }

Property Value

IXmlFunctions

Methods

Escape(string)

SQL Server bracket-quotes identifiers, doubling an embedded ] so the result is a single safe identifier (the same bracket semantics as QuoteIdentifier(string)). Single quoted aliases (the base default) are accepted for columns but produce a syntax error for table and derived table aliases.

public override string Escape(string keyword)

Parameters

keyword string

Returns

string

GetPagingOrderBy(QueryCommand)

Injects a constant sort because SQL Server rejects OFFSET/FETCH without an ORDER BY.

public override string? GetPagingOrderBy(QueryCommand queryCommand)

Parameters

queryCommand QueryCommand

Returns

string

MakeApply(JoinType, string, KeywordCase)

Renders a lateral source as CROSS APPLY or OUTER APPLY.

public override string MakeApply(JoinType applyType, string source, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

applyType JoinType
source string
keywordCase KeywordCase

Returns

string

MakeAverageArgument(string, Type)

SQL Server evaluates avg over an integer column as an integer and truncates the fractional part. Widen the argument to float inside the aggregate; casting the aggregate result cannot recover the discarded fraction.

public override string MakeAverageArgument(string argument, Type inputType)

Parameters

argument string
inputType Type

Returns

string

MakeBase64Decode(string)

Defaults to the unsupported-provider exception; reached only when SupportsBase64Decode is true.

public override string MakeBase64Decode(string value)

Parameters

value string

Returns

string

MakeBase64Encode(string)

Defaults to the unsupported-provider exception; reached only when SupportsBase64Encode is true.

public override string MakeBase64Encode(string value)

Parameters

value string

Returns

string

MakeBoolCoalesce(string, string)

Renders the boolean coalesce as (isnull(v1, v2)) = 1 so it stays usable as a predicate.

public override string MakeBoolCoalesce(string v1, string v2)

Parameters

v1 string
v2 string

Returns

string

MakeBooleanPredicate(string, bool)

T-SQL has no boolean type: a scalar boolean is materialised as cast(case when ... then 1 else 0 end as bit).

public override string MakeBooleanPredicate(string predicate, bool asPredicate)

Parameters

predicate string
asPredicate bool

Returns

string

MakeBooleanValuePredicate(string)

A bit value is not a valid T-SQL predicate, so it is compared with its true literal ((value) = 1).

public override string MakeBooleanValuePredicate(string value)

Parameters

value string

Returns

string

MakeCase(string, bool, bool, KeywordCase)

Materialises a boolean-valued CASE as a bit scalar, compared with 1 in a predicate context.

public override string MakeCase(string caseExpression, bool isBooleanResult, bool asPredicate, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

caseExpression string
isBooleanResult bool
asPredicate bool
keywordCase KeywordCase

Returns

string

MakeCoalesce(string, string)

T-SQL has both isnull and coalesce; this renders the historical isnull(v1, v2).

public override string MakeCoalesce(string v1, string v2)

Parameters

v1 string
v2 string

Returns

string

MakeCollate(string, string, KeywordCase)

Renders value COLLATE collation over the already-rendered value. collation is the provider-native collation name, quoted where the provider requires it (PostgreSQL). Only called when SupportsCollation is true.

public override string MakeCollate(string value, string collation, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

value string
collation string
keywordCase KeywordCase

Returns

string

MakeColumnReference(string)

SQL Server uses a bracket-quoted identifier for references as well, so that aliases that collide with a T-SQL keyword (e.g. "double") stay usable from an outer query.

public override string MakeColumnReference(string name)

Parameters

name string

Returns

string

MakeCount(bool, bool)

Renders count_big(...) for the large form (count otherwise), preserving the distinct modifier.

public override string MakeCount(bool distinct, bool big)

Parameters

distinct bool
big bool

Returns

string

MakeCreateTableAsSelectInto(CreateTableAsClause, KeywordCase)

Renders the SQL Server INTO <table> clause inserted into the top-level select list.

public override string MakeCreateTableAsSelectInto(CreateTableAsClause clause, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

clause CreateTableAsClause
keywordCase KeywordCase

Returns

string

MakeCryptGenRandom(string)

Defaults to the unsupported-provider exception; reached only when SupportsCryptGenRandom is true.

public override string MakeCryptGenRandom(string length)

Parameters

length string

Returns

string

MakeDateAdd(string, string, string)

Renders dateadd(part, amount, value), folding decade/century/millennium onto a scaled year.

public override string MakeDateAdd(string field, string amount, string value)

Parameters

field string
amount string
value string

Returns

string

MakeDateAdd(string, string, string, bool)

dateadd truncates a fractional amount in the requested unit, so a DateTimeOffset amount is scaled to the next finer unit that keeps the fraction. A ×1000 scale (seconds→milliseconds, milliseconds→microseconds) is an int product that overflows dateadd's 32-bit amount for a large whole value (for example AddSeconds(2_200_000)), so the whole part is added in the requested unit and only the fractional remainder is scaled into the finer unit. Year/month amounts are CLR int and unchanged; the datetimeoffset operand is used directly so its offset is preserved.

public override string MakeDateAdd(string field, string amount, string value, bool isDateTimeOffset)

Parameters

field string
amount string
value string
isDateTimeOffset bool

Returns

string

MakeDateDiff(string, string, string)

Renders datediff(part, start, end) with the singular T-SQL part names.

public override string MakeDateDiff(string field, string start, string end)

Parameters

field string
start string
end string

Returns

string

MakeDateDiffBig(string, string, string)

Renders the 64-bit datediff_big(part, start, end) with the singular T-SQL part names.

public override string MakeDateDiffBig(string field, string start, string end)

Parameters

field string
start string
end string

Returns

string

MakeDateFromParts(string, string, string)

Renders datefromparts(year, month, day).

public override string MakeDateFromParts(string year, string month, string day)

Parameters

year string
month string
day string

Returns

string

MakeDatePart(string, string)

SQL Server extracts date parts through datepart(part, value); T-SQL spells day-of-year as dayofyear. The ISO week is isowk; dow/isodow are normalised against the session DATEFIRST through datepart(weekday) and @@datefirst.

public override string MakeDatePart(string part, string value)

Parameters

part string
value string

Returns

string

MakeDateTimeOffsetNow(bool)

Renders the native offset-bearing sysdatetimeoffset(), or a zero-offset value built from the UTC clock.

public override string MakeDateTimeOffsetNow(bool utc)

Parameters

utc bool

Returns

string

MakeDateTrunc(string, string)

Renders datetrunc(part, value), mapping the plural ANSI sub-second names to the singular T-SQL ones.

public override string MakeDateTrunc(string field, string value)

Parameters

field string
value string

Returns

string

MakeEndOfMonth(string)

Renders eomonth(value).

public override string MakeEndOfMonth(string value)

Parameters

value string

Returns

string

MakeForJson(ForJsonClause, KeywordCase)

Renders the trailing FOR JSON clause with its mode, root and null-value options.

public override string MakeForJson(ForJsonClause clause, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

clause ForJsonClause
keywordCase KeywordCase

Returns

string

MakeForXml(ForXmlClause, KeywordCase)

Renders the trailing FOR XML clause with its mode, element name, root and elements options.

public override string MakeForXml(ForXmlClause clause, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

clause ForXmlClause
keywordCase KeywordCase

Returns

string

MakeFullText(string, string, string)

Renders the full-text predicate contains(column, search) or freetext(column, search).

public override string MakeFullText(string functionName, string column, string search)

Parameters

functionName string
column string
search string

Returns

string

MakeIdentityFunction(KeywordCase)

Renders the identity-function query select scope_identity().

public override string MakeIdentityFunction(KeywordCase keywordCase = KeywordCase.Lower)

Parameters

keywordCase KeywordCase

Returns

string

MakeIsJson(string, bool)

T-SQL isjson returns an int: 1/0, so a value context casts it to bit.

public override string MakeIsJson(string value, bool asPredicate)

Parameters

value string
asPredicate bool

Returns

string

MakeJoinKeyword(JoinType, JoinStrictness, bool, string?, KeywordCase)

Inserts the join hint before the JOIN keyword of the already-rendered keyword, e.g. inner join with loop becomes inner loop join. A null hint delegates to the plain keyword.

public override string MakeJoinKeyword(JoinType joinType, JoinStrictness strictness, bool isGlobal, string? hint, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

joinType JoinType
strictness JoinStrictness
isGlobal bool
hint string
keywordCase KeywordCase

Returns

string

MakeMathFunction(string, IReadOnlyList<string>)

Renders round(number, 0, 1) for trunc and round(number, 0) for the one-argument round.

public override string MakeMathFunction(string name, IReadOnlyList<string> args)

Parameters

name string
args IReadOnlyList<string>

Returns

string

MakeMaxRecursion(int, KeywordCase)

Renders the trailing option (maxrecursion n) clause.

public override string? MakeMaxRecursion(int maxRecursion, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

maxRecursion int
keywordCase KeywordCase

Returns

string

MakeMerge(string, IReadOnlyList<string>, IReadOnlyList<string>, IReadOnlyList<string>, string, KeywordCase, IReadOnlyList<string>?)

Renders the key-upsert MERGE ... USING (VALUES ...) AS source (...) ON ... statement, optionally followed by an OUTPUT inserted.<column> clause (T-SQL requires the terminating semicolon).

public override string MakeMerge(string target, IReadOnlyList<string> columns, IReadOnlyList<string> keys, IReadOnlyList<string> updateColumns, string valuesRows, KeywordCase keywordCase = KeywordCase.Lower, IReadOnlyList<string>? returningColumns = null)

Parameters

target string
columns IReadOnlyList<string>
keys IReadOnlyList<string>
updateColumns IReadOnlyList<string>
valuesRows string
keywordCase KeywordCase
returningColumns IReadOnlyList<string>

Returns

string

MakeMergeStatementTerminator(KeywordCase)

SQL Server requires a terminating semicolon after MERGE.

public override string MakeMergeStatementTerminator(KeywordCase keywordCase = KeywordCase.Lower)

Parameters

keywordCase KeywordCase

Returns

string

MakeNow(bool)

Renders getutcdate() for UTC or getdate() for local time.

public override string MakeNow(bool utc)

Parameters

utc bool

Returns

string

MakeOrdinal(string, bool)

Renders a string operand normalised for ordinal comparison. A provider with a per-expression binary collation emits value COLLATE <binary>; a provider whose native String order is already ordinal (ClickHouse) returns the value unchanged. ignoreCase additionally case-folds the operand. Only called when SupportsOrdinalComparison is true.

public override string MakeOrdinal(string value, bool ignoreCase)

Parameters

value string
ignoreCase bool

Returns

string

MakePage(Paging, StringBuilder, KeywordCase)

Renders offset n rows and an optional fetch next m rows only|with ties.

public override void MakePage(Paging paging, StringBuilder sqlBuilder, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

paging Paging
sqlBuilder StringBuilder
keywordCase KeywordCase

MakeParam(string)

Renders a positional parameter as @name.

public override string MakeParam(string name)

Parameters

name string

Returns

string

MakeQualifiedTableName(string?, string?, string?, string)

Renders the SQL Server four-part name [server].[database].[schema].[table], omitting the parts that are null. Quoting is applied later by the SQL builder.

public override string MakeQualifiedTableName(string? server, string? database, string? schema, string table)

Parameters

server string
database string
schema string
table string

Returns

string

MakeRegexMatch(string, string, bool)

Renders a regular-expression match predicate over the already-rendered value. pattern is the raw pattern text (the dialect renders and escapes the string literal itself) and ignoreCase selects IgnoreCase. Reached only when SupportsRegex is true.

public override string MakeRegexMatch(string value, string pattern, bool ignoreCase)

Parameters

value string
pattern string
ignoreCase bool

Returns

string

MakeRegexReplace(string, string, string, bool)

Renders a regular-expression replace over the already-rendered value, replacing every match as Replace(string, string, string) does. pattern and replacement are raw text (the dialect renders and escapes the literals). Reached only when SupportsRegex is true.

A replacement backreference uses the provider's group syntax (C# $1, SQL \1); it is not rewritten, so a replacement with a group reference is not portable as written. 

public override string MakeRegexReplace(string value, string pattern, string replacement, bool ignoreCase)

Parameters

value string
pattern string
replacement string
ignoreCase bool

Returns

string

MakeRepeat(string, string)

Renders replicate(value, count).

public override string MakeRepeat(string value, string count)

Parameters

value string
count string

Returns

string

MakeStringLength(string)

SQL Server has no length; it renders len(value).

public override string MakeStringLength(string value)

Parameters

value string

Returns

string

MakeStringPosition(string, string)

Renders the one-based position through charindex(substring, value) (0 when absent).

protected override string MakeStringPosition(string value, string substring)

Parameters

value string
substring string

Returns

string

MakeStringPosition(string, string, string)

Renders the one-based position from a zero-based start through charindex(substring, value, start + 1).

protected override string MakeStringPosition(string value, string substring, string start)

Parameters

value string
substring string
start string

Returns

string

MakeStringReverse(string)

Renders reverse(value).

protected override string MakeStringReverse(string value)

Parameters

value string

Returns

string

MakeStuff(string, string, string?, string)

Renders stuff(value, start + 1, count, newValue), or the truncated prefix when the count is null.

public override string MakeStuff(string value, string start, string? count, string newValue)

Parameters

value string
start string
count string
newValue string

Returns

string

MakeSubqueryPredicate(string, string, bool)

T-SQL has no boolean type: exists/any/all are materialised as a bit scalar in a projection.

public override string MakeSubqueryPredicate(string keyword, string query, bool asPredicate)

Parameters

keyword string
query string
asPredicate bool

Returns

string

MakeTableHints(IReadOnlyList<string>, KeywordCase)

Renders the hints as a with (hint, ...) suffix.

public override string MakeTableHints(IReadOnlyList<string> hints, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

hints IReadOnlyList<string>
keywordCase KeywordCase

Returns

string

MakeTablesInScopeHints(IReadOnlyList<string>, KeywordCase)

Renders the tables-in-scope hints as a with (hint, ...) suffix on a table.

public override string MakeTablesInScopeHints(IReadOnlyList<string> hints, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

hints IReadOnlyList<string>
keywordCase KeywordCase

Returns

string

MakeTop(int, bool, out string?, KeywordCase)

SQL Server renders the inline limit as top(n), or top(n) with ties.

public override bool MakeTop(int limit, bool withTies, out string? topStmt, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

limit int
withTies bool
topStmt string
keywordCase KeywordCase

Returns

bool

MakeTryCast(string, Type)

Renders try_cast(expression as <type>) through the closed conversion map. The map is deliberately separate from MakeTypeName(Type): that one falls back to type.Name, which is invalid T-SQL for bool/Guid/DateTime/string and wrong for TimeSpan (ticks). Lengths and precisions are fixed in the map (no configurable length/precision yet); the server applies its native truncation and rounding. An unmapped target is refused before the command is prepared.

public override string MakeTryCast(string expression, Type targetType)

Parameters

expression string
targetType Type

Returns

string

Exceptions

NotSupportedException

The target has no entry in the closed map.

MakeTryConvert(string, Type, string?)

Renders try_convert(<type>, expression[, style]) through the same closed map. A null style omits the argument; a supplied style (including a typed SQL NULL) is the native style argument and keeps the server's NULL-style semantics, which is a NULL result even for a convertible value.

public override string MakeTryConvert(string expression, Type targetType, string? style)

Parameters

expression string
targetType Type
style string

Returns

string

Exceptions

NotSupportedException

The target has no entry in the closed map.

MakeTypeName(Type)

Maps the CLR type to its SQL Server column type (tinyint, smallint, int, bigint, real, float, decimal).

public override string MakeTypeName(Type type)

Parameters

type Type

Returns

string

MakeUpdateJoin(string, string, string, string, string, string, string?, KeywordCase)

Renders the SQL Server multi-table update: the SET list is qualified by the target alias and the target is named again (with its joins) in the FROM clause.

public override string MakeUpdateJoin(string target, string targetAlias, string assignments, string fromAndJoins, string usingSources, string joinConditions, string? whereSql, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

target string
targetAlias string
assignments string
fromAndJoins string
usingSources string
joinConditions string
whereSql string
keywordCase KeywordCase

Returns

string

MakeWith(bool, KeywordCase)

T-SQL has no RECURSIVE keyword, so the flag is ignored and with is always rendered.

public override string MakeWith(bool recursive, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

recursive bool
keywordCase KeywordCase

Returns

string

PromoteDateOperand(string, string)

T-SQL dateadd on a date operand for a sub-day part drops the time of day (and datediff cannot see it), so a sub-day operand is promoted to datetime2.

public override string PromoteDateOperand(string field, string value)

Parameters

field string
value string

Returns

string

QuoteIdentifier(string)

SQL Server quotes a physical identifier with brackets, doubling an embedded ].

public override string QuoteIdentifier(string name)

Parameters

name string

Returns

string

RenderQueryHints(string, IReadOnlyList<string>, string?, KeywordCase)

Appends the hints as an option (...) clause, folding an existing maxrecursion option into it.

public override string RenderQueryHints(string sql, IReadOnlyList<string> hints, string? maxRecursionOption, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

sql string
hints IReadOnlyList<string>
maxRecursionOption string
keywordCase KeywordCase

Returns

string

SupportsDatePart(string)

SQL Server additionally accepts the normalised weekday and epoch date parts.

public override bool SupportsDatePart(string part)

Parameters

part string

Returns

bool

SupportsDateTruncField(string)

Accepts the date-trunc fields except decade, century and millennium, which T-SQL datetrunc lacks.

public override bool SupportsDateTruncField(string field)

Parameters

field string

Returns

bool

SupportsTableFunction(string)

SQL Server exposes string_split, openjson, containstable and freetexttable on every supported version, and regexp_split_to_table/ regexp_matches from 2025 (declared major version 17) on. The 2025 names are additionally usable only at database compatibility level 170, which the dialect cannot inspect; that prerequisite is documented and asserted live rather than silently assumed.

public override bool SupportsTableFunction(string name)

Parameters

name string

Returns

bool

SupportsTemporalKind(TemporalKind)

SQL Server supports every FOR SYSTEM_TIME kind.

public override bool SupportsTemporalKind(TemporalKind kind)

Parameters

kind TemporalKind

Returns

bool

WrapTableFunction(string, string, IReadOnlyList<string>)

Rewrites the regexp_matches match-type argument to varchar. The native third argument is varchar-only: an nvarchar literal, NULL or parameter is Msg 8116 ("Argument data type nvarchar is invalid for argument 3"), while a cast to varchar is accepted for every shape (probe matches_nvarchar/cast_lit_matches/cast_param_matches). Only the SQL type changes; the value is never validated client-side.

public override string WrapTableFunction(string name, string call, IReadOnlyList<string> arguments)

Parameters

name string
call string
arguments IReadOnlyList<string>

Returns

string
Edit this page