# QueryBuilder

> QueryBuilder is the query builder for MS SQL Server databases (version 2019 and above).

[API reference](/docs/api/index.md) › [yii](/docs/api/yii/index.md) › [db](/docs/api/yii/db/index.md) › [mssql](/docs/api/yii/db/mssql/index.md) › QueryBuilder

| class | `yii\db\mssql\QueryBuilder` |
| --- | --- |
| Extends | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) » [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| Implements | [`yii\base\Configurable`](/docs/api/yii/base/configurable/index.md) |
| Since | 2.0 |
| Source | [`framework/db/mssql/QueryBuilder.php`](https://github.com/yiisoft/yii2/blob/22.0/framework/db/mssql/QueryBuilder.php#L37) |

QueryBuilder is the query builder for MS SQL Server databases (version 2019 and above).

## Properties

| Property | Type | Description | Defined by |
| --- | --- | --- | --- |
| <span id="property-db"></span>[`$db`](/docs/api/yii/db/querybuilder/index.md#property-db) | [`yii\db\Connection`](/docs/api/yii/db/connection/index.md) | The database connection. Default: `null`. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="property-separator"></span>[`$separator`](/docs/api/yii/db/querybuilder/index.md#property-separator) | `string` | The separator between different fragments of a SQL statement. Default: `' '`. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="property-typeMap"></span>`$typeMap` | `array` | Mapping from abstract column types (keys) to physical column types (values). Default: `[\yii\db\mssql\Schema::TYPE_PK => 'int IDENTITY PRIMARY KEY', \yii\db\mssql\Schema::TYPE_UPK => 'int IDENTITY PRIMARY KEY', \yii\db\mssql\Schema::TYPE_BIGPK => 'bigint IDENTITY PRIMARY KEY', \yii\db\mssql\Schema::TYPE_UBIGPK => 'bigint IDENTITY PRIMARY KEY', \yii\db\mssql\Schema::TYPE_CHAR => 'nchar(1)', \yii\db\mssql\Schema::TYPE_STRING => 'nvarchar(255)', \yii\db\mssql\Schema::TYPE_TEXT => 'nvarchar(max)', \yii\db\mssql\Schema::TYPE_TINYINT => 'tinyint', \yii\db\mssql\Schema::TYPE_SMALLINT => 'smallint', \yii\db\mssql\Schema::TYPE_INTEGER => 'int', \yii\db\mssql\Schema::TYPE_BIGINT => 'bigint', \yii\db\mssql\Schema::TYPE_FLOAT => 'float', \yii\db\mssql\Schema::TYPE_DOUBLE => 'float', \yii\db\mssql\Schema::TYPE_DECIMAL => 'decimal(18,0)', \yii\db\mssql\Schema::TYPE_DATETIME => 'datetime', \yii\db\mssql\Schema::TYPE_TIMESTAMP => 'datetime', \yii\db\mssql\Schema::TYPE_TIME => 'time', \yii\db\mssql\Schema::TYPE_DATE => 'date', \yii\db\mssql\Schema::TYPE_BINARY => 'varbinary(max)', \yii\db\mssql\Schema::TYPE_BOOLEAN => 'bit', \yii\db\mssql\Schema::TYPE_MONEY => 'decimal(19,4)']`. | `QueryBuilder` |
| <span id="property-conditionClasses"></span>[`$conditionClasses`](/docs/api/yii/db/querybuilder/index.md#property-conditionClasses) protected | `array` | Map of condition aliases to condition classes. Default: `[]`. Available since 2.0.14. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="property-expressionBuilders"></span>[`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders) protected | `string[]\|`[`yii\db\ExpressionBuilderInterface`](/docs/api/yii/db/expressionbuilderinterface/index.md)`[]` | Maps expression class to expression builder class. Default: `[]`. Available since 2.0.14. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |

## Methods

| Method | Description | Defined by |
| --- | --- | --- |
| <span id="method-__construct"></span>[`__construct()`](/docs/api/yii/db/querybuilder/index.md#method-__construct) | Constructor. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-__call"></span>[`__call()`](/docs/api/yii/base/baseobject/index.md#method-__call) | Calls the named method which is not a class method. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-__get"></span>[`__get()`](/docs/api/yii/base/baseobject/index.md#method-__get) | Returns the value of an object property. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-__isset"></span>[`__isset()`](/docs/api/yii/base/baseobject/index.md#method-__isset) | Checks if a property is set, i.e. defined and not null. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-__set"></span>[`__set()`](/docs/api/yii/base/baseobject/index.md#method-__set) | Sets value of an object property. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-__unset"></span>[`__unset()`](/docs/api/yii/base/baseobject/index.md#method-__unset) | Sets an object property to null. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-addCheck"></span>[`addCheck()`](/docs/api/yii/db/querybuilder/index.md#method-addCheck) | Creates a SQL command for adding a check constraint to an existing table. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-addColumn"></span>[`addColumn()`](/docs/api/yii/db/querybuilder/index.md#method-addColumn) | Builds a SQL statement for adding a new DB column. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`addCommentOnColumn()`](#method-addCommentOnColumn) | Builds a SQL command for adding comment to column. | `QueryBuilder` |
| [`addCommentOnTable()`](#method-addCommentOnTable) | Builds a SQL command for adding comment to table. | `QueryBuilder` |
| [`addDefaultValue()`](#method-addDefaultValue) | Creates a SQL command for adding a default value constraint to an existing table. | `QueryBuilder` |
| <span id="method-addForeignKey"></span>[`addForeignKey()`](/docs/api/yii/db/querybuilder/index.md#method-addForeignKey) | Builds a SQL statement for adding a foreign key constraint to an existing table. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-addPrimaryKey"></span>[`addPrimaryKey()`](/docs/api/yii/db/querybuilder/index.md#method-addPrimaryKey) | Builds a SQL statement for adding a primary key constraint to an existing table. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-addUnique"></span>[`addUnique()`](/docs/api/yii/db/querybuilder/index.md#method-addUnique) | Creates a SQL command for adding an unique constraint to an existing table. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`alterColumn()`](#method-alterColumn) | Builds a SQL statement for changing the definition of a column. | `QueryBuilder` |
| <span id="method-batchInsert"></span>[`batchInsert()`](/docs/api/yii/db/querybuilder/index.md#method-batchInsert) | Generates a batch INSERT SQL statement. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-bindParam"></span>[`bindParam()`](/docs/api/yii/db/querybuilder/index.md#method-bindParam) | Helper method to add $value to $params array using [`PARAM_PREFIX`](/docs/api/yii/db/querybuilder/index.md#const-PARAM_PREFIX). | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-build"></span>[`build()`](/docs/api/yii/db/querybuilder/index.md#method-build) | Generates a SELECT SQL statement from a [`Query`](/docs/api/yii/db/query/index.md) object. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildColumns"></span>[`buildColumns()`](/docs/api/yii/db/querybuilder/index.md#method-buildColumns) | Processes columns and properly quotes them if necessary. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildCondition"></span>[`buildCondition()`](/docs/api/yii/db/querybuilder/index.md#method-buildCondition) | Parses the condition specification and generates the corresponding SQL expression. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildExpression"></span>[`buildExpression()`](/docs/api/yii/db/querybuilder/index.md#method-buildExpression) | Builds given $expression | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildFrom"></span>[`buildFrom()`](/docs/api/yii/db/querybuilder/index.md#method-buildFrom) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildGroupBy"></span>[`buildGroupBy()`](/docs/api/yii/db/querybuilder/index.md#method-buildGroupBy) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildHaving"></span>[`buildHaving()`](/docs/api/yii/db/querybuilder/index.md#method-buildHaving) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildJoin"></span>[`buildJoin()`](/docs/api/yii/db/querybuilder/index.md#method-buildJoin) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildLimit"></span>[`buildLimit()`](/docs/api/yii/db/querybuilder/index.md#method-buildLimit) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildOrderBy"></span>[`buildOrderBy()`](/docs/api/yii/db/querybuilder/index.md#method-buildOrderBy) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`buildOrderByAndLimit()`](#method-buildOrderByAndLimit) | Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL. | `QueryBuilder` |
| <span id="method-buildSelect"></span>[`buildSelect()`](/docs/api/yii/db/querybuilder/index.md#method-buildSelect) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-buildUnion"></span>[`buildUnion()`](/docs/api/yii/db/querybuilder/index.md#method-buildUnion) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`buildUnionOrderByAndLimit()`](#method-buildUnionOrderByAndLimit) | Builds the global ORDER BY and LIMIT/OFFSET clauses for a UNION query. | `QueryBuilder` |
| <span id="method-buildWhere"></span>[`buildWhere()`](/docs/api/yii/db/querybuilder/index.md#method-buildWhere) |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`buildWithQueries()`](#method-buildWithQueries) |  | `QueryBuilder` |
| <span id="method-canGetProperty"></span>[`canGetProperty()`](/docs/api/yii/base/baseobject/index.md#method-canGetProperty) | Returns a value indicating whether a property can be read. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-canSetProperty"></span>[`canSetProperty()`](/docs/api/yii/base/baseobject/index.md#method-canSetProperty) | Returns a value indicating whether a property can be set. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| [`checkIntegrity()`](#method-checkIntegrity) | Builds a SQL statement for enabling or disabling integrity checks on table constraints. | `QueryBuilder` |
| <span id="method-className"></span>[`className()`](/docs/api/yii/base/baseobject/index.md#method-className) static | Returns the fully qualified name of this class. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-createConditionFromArray"></span>[`createConditionFromArray()`](/docs/api/yii/db/querybuilder/index.md#method-createConditionFromArray) | Transforms $condition defined in array format (as described in [`Query::where()`](/docs/api/yii/db/query/index.md#method-where) to instance of ConditionInterface according to [`$conditionClasses`](/docs/api/yii/db/querybuilder/index.md#property-conditionClasses) map. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-createIndex"></span>[`createIndex()`](/docs/api/yii/db/querybuilder/index.md#method-createIndex) | Builds a SQL statement for creating a new index. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-createTable"></span>[`createTable()`](/docs/api/yii/db/querybuilder/index.md#method-createTable) | Builds a SQL statement for creating a new DB table. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-createView"></span>[`createView()`](/docs/api/yii/db/querybuilder/index.md#method-createView) | Creates a SQL View. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`delete()`](#method-delete) | Creates a DELETE SQL statement. | `QueryBuilder` |
| <span id="method-dropCheck"></span>[`dropCheck()`](/docs/api/yii/db/querybuilder/index.md#method-dropCheck) | Creates a SQL command for dropping a check constraint. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`dropColumn()`](#method-dropColumn) | Drop all constraints before column delete. | `QueryBuilder` |
| [`dropCommentFromColumn()`](#method-dropCommentFromColumn) | Builds a SQL command for adding comment to column. | `QueryBuilder` |
| [`dropCommentFromTable()`](#method-dropCommentFromTable) | Builds a SQL command for adding comment to table. | `QueryBuilder` |
| [`dropDefaultValue()`](#method-dropDefaultValue) | Creates a SQL command for dropping a default value constraint. | `QueryBuilder` |
| <span id="method-dropForeignKey"></span>[`dropForeignKey()`](/docs/api/yii/db/querybuilder/index.md#method-dropForeignKey) | Builds a SQL statement for dropping a foreign key constraint. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-dropIndex"></span>[`dropIndex()`](/docs/api/yii/db/querybuilder/index.md#method-dropIndex) | Builds a SQL statement for dropping an index. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-dropPrimaryKey"></span>[`dropPrimaryKey()`](/docs/api/yii/db/querybuilder/index.md#method-dropPrimaryKey) | Builds a SQL statement for removing a primary key constraint to an existing table. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-dropTable"></span>[`dropTable()`](/docs/api/yii/db/querybuilder/index.md#method-dropTable) | Builds a SQL statement for dropping a DB table. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-dropUnique"></span>[`dropUnique()`](/docs/api/yii/db/querybuilder/index.md#method-dropUnique) | Creates a SQL command for dropping an unique constraint. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-dropView"></span>[`dropView()`](/docs/api/yii/db/querybuilder/index.md#method-dropView) | Drops a SQL View. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-executeResetSequence"></span>[`executeResetSequence()`](/docs/api/yii/db/querybuilder/index.md#method-executeResetSequence) | Execute a SQL statement for resetting the sequence value of a table's primary key. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`getColumnType()`](#method-getColumnType) | Converts an abstract column type into a physical column type. | `QueryBuilder` |
| <span id="method-getExpressionBuilder"></span>[`getExpressionBuilder()`](/docs/api/yii/db/querybuilder/index.md#method-getExpressionBuilder) | Gets object of [`ExpressionBuilderInterface`](/docs/api/yii/db/expressionbuilderinterface/index.md) that is suitable for $expression. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-hasMethod"></span>[`hasMethod()`](/docs/api/yii/base/baseobject/index.md#method-hasMethod) | Returns a value indicating whether a method is defined. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-hasProperty"></span>[`hasProperty()`](/docs/api/yii/base/baseobject/index.md#method-hasProperty) | Returns a value indicating whether a property is defined. | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| <span id="method-init"></span>[`init()`](/docs/api/yii/db/querybuilder/index.md#method-init) | Initializes the object. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`insert()`](#method-insert) | Creates an INSERT SQL statement. | `QueryBuilder` |
| [`renameColumn()`](#method-renameColumn) | Builds a SQL statement for renaming a column. | `QueryBuilder` |
| [`renameTable()`](#method-renameTable) | Builds a SQL statement for renaming a DB table. | `QueryBuilder` |
| [`resetSequence()`](#method-resetSequence) | Creates a SQL statement for resetting the sequence value of a table's primary key. | `QueryBuilder` |
| [`selectExists()`](#method-selectExists) | Creates a SELECT EXISTS() SQL statement. | `QueryBuilder` |
| <span id="method-setConditionClasses"></span>[`setConditionClasses()`](/docs/api/yii/db/querybuilder/index.md#method-setConditionClasses) | Setter for [`$conditionClasses`](/docs/api/yii/db/querybuilder/index.md#property-conditionClasses) property. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-setExpressionBuilders"></span>[`setExpressionBuilders()`](/docs/api/yii/db/querybuilder/index.md#method-setExpressionBuilders) | Setter for [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders) property. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-truncateTable"></span>[`truncateTable()`](/docs/api/yii/db/querybuilder/index.md#method-truncateTable) | Builds a SQL statement for truncating a DB table. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`update()`](#method-update) | Creates an UPDATE SQL statement. | `QueryBuilder` |
| [`upsert()`](#method-upsert) | Generates a `MERGE` statement that inserts a row or updates the matching one in a single atomic operation. | `QueryBuilder` |
| [`buildAddCommentSql()`](#method-buildAddCommentSql) protected | Builds the SQL to add or update the `MS_Description` extended property of a table or column. | `QueryBuilder` |
| [`buildRemoveCommentSql()`](#method-buildRemoveCommentSql) protected | Builds the SQL to remove the `MS_Description` extended property from a table or column. | `QueryBuilder` |
| <span id="method-concatUnionSql"></span>[`concatUnionSql()`](/docs/api/yii/db/querybuilder/index.md#method-concatUnionSql) protected | Concatenates the first `SELECT` statement with the UNION clauses built by [`buildUnion()`](/docs/api/yii/db/querybuilder/index.md#method-buildUnion). | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-defaultConditionClasses"></span>[`defaultConditionClasses()`](/docs/api/yii/db/querybuilder/index.md#method-defaultConditionClasses) protected | Contains array of default condition classes. Extend this method, if you want to change default condition classes for the query builder. See [`$conditionClasses`](/docs/api/yii/db/querybuilder/index.md#property-conditionClasses) docs for details. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`defaultExpressionBuilders()`](#method-defaultExpressionBuilders) protected | Contains array of default expression builders. Extend this method and override it, if you want to change default expression builders for this query builder. See [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders) docs for details. | `QueryBuilder` |
| [`extractAlias()`](#method-extractAlias) protected | Extracts table alias if there is one or returns false | `QueryBuilder` |
| <span id="method-hasLimit"></span>[`hasLimit()`](/docs/api/yii/db/querybuilder/index.md#method-hasLimit) protected | Checks to see if the given limit is effective. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-hasOffset"></span>[`hasOffset()`](/docs/api/yii/db/querybuilder/index.md#method-hasOffset) protected | Checks to see if the given offset is effective. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-prepareInsertSelectSubQuery"></span>[`prepareInsertSelectSubQuery()`](/docs/api/yii/db/querybuilder/index.md#method-prepareInsertSelectSubQuery) protected | Prepare select-subquery and field names for INSERT INTO ... SELECT SQL statement. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-prepareInsertValues"></span>[`prepareInsertValues()`](/docs/api/yii/db/querybuilder/index.md#method-prepareInsertValues) protected | Prepares a `VALUES` part for an `INSERT` SQL statement. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| [`prepareSelectQuery()`](#method-prepareSelectQuery) protected | Performs driver-specific preparation after [`Query::prepare()`](/docs/api/yii/db/query/index.md#method-prepare). | `QueryBuilder` |
| <span id="method-prepareUpdateSets"></span>[`prepareUpdateSets()`](/docs/api/yii/db/querybuilder/index.md#method-prepareUpdateSets) protected | Prepares a `SET` parts for an `UPDATE` SQL statement. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-prepareUpsertColumns"></span>[`prepareUpsertColumns()`](/docs/api/yii/db/querybuilder/index.md#method-prepareUpsertColumns) protected |  | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |
| <span id="method-prepareUpsertSets"></span>[`prepareUpsertSets()`](/docs/api/yii/db/querybuilder/index.md#method-prepareUpsertSets) protected | Prepares the `SET` assignments for the update branch of an upsert statement. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |

## Constants

| Constant | Value | Description | Defined by |
| --- | --- | --- | --- |
| <span id="const-PARAM_PREFIX"></span>[`PARAM_PREFIX`](/docs/api/yii/db/querybuilder/index.md#const-PARAM_PREFIX) | `':qp'` | The prefix for automatically generated query binding parameters. | [`yii\db\QueryBuilder`](/docs/api/yii/db/querybuilder/index.md) |

## Method details

### addCommentOnColumn() <span id="method-addCommentOnColumn"></span>

```php
public function addCommentOnColumn(mixed $table, mixed $column, mixed $comment): string
```

Builds a SQL command for adding comment to column.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` | The table whose column is to be commented. The table name will be properly quoted by the method. |
| `$column` | `mixed` | The name of the column to be commented. The column name will be properly quoted by the method. |
| `$comment` | `mixed` | The text of the comment to be added. The comment will be properly quoted by the method. |

**Returns**

`string`: The SQL statement for adding comment on column

**Since**

2.0.8

### addCommentOnTable() <span id="method-addCommentOnTable"></span>

```php
public function addCommentOnTable(mixed $table, mixed $comment): string
```

Builds a SQL command for adding comment to table.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` | The table whose column is to be commented. The table name will be properly quoted by the method. |
| `$comment` | `mixed` | The text of the comment to be added. The comment will be properly quoted by the method. |

**Returns**

`string`: The SQL statement for adding comment on table

**Since**

2.0.8

### addDefaultValue() <span id="method-addDefaultValue"></span>

```php
public function addDefaultValue(mixed $name, mixed $table, mixed $column, mixed $value): string
```

Creates a SQL command for adding a default value constraint to an existing table.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$name` | `mixed` | The name of the default value constraint. The name will be properly quoted by the method. |
| `$table` | `mixed` | The table that the default value constraint will be added to. The name will be properly quoted by the method. |
| `$column` | `mixed` | The name of the column to that the constraint will be added on. The name will be properly quoted by the method. |
| `$value` | `mixed` | Default value. |

**Returns**

`string`: The SQL statement for adding a default value constraint to an existing table.

**Throws**

- [`yii\base\NotSupportedException`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS.

**Since**

2.0.13

### alterColumn() <span id="method-alterColumn"></span>

```php
public function alterColumn(string $table, string $column, string $type): string
```

Builds a SQL statement for changing the definition of a column.

Drops the default, check, and unique constraints attached to the column before the `ALTER COLUMN` statement,
because SQL Server rejects altering a column while such constraints are bound to it. Default, check, and unique
constraints defined by a [`ColumnSchemaBuilder`](/docs/api/yii/db/mssql/columnschemabuilder/index.md) type are re-created after the column is altered.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `string` | The table whose column is to be changed. The table name will be properly quoted by the method. |
| `$column` | `string` | The name of the column to be changed. The name will be properly quoted by the method. |
| `$type` | `string` | The new column type. The [`getColumnType()`](/docs/api/yii/db/mssql/querybuilder/index.md#method-getColumnType) method will be invoked to convert abstract column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. |

**Returns**

`string`: The SQL statement for changing the definition of a column.

**Throws**

- [`yii\base\NotSupportedException`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS.

### buildOrderByAndLimit() <span id="method-buildOrderByAndLimit"></span>

```php
public function buildOrderByAndLimit(mixed $sql, mixed $orderBy, mixed $limit, mixed $offset): string
```

Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$sql` | `mixed` | The existing SQL (without ORDER BY/LIMIT/OFFSET) |
| `$orderBy` | `mixed` | The order by columns. See [`Query::orderBy()`](/docs/api/yii/db/querytrait/index.md#method-orderBy) for more details on how to specify this parameter. |
| `$limit` | `mixed` | The limit number. See [`Query::limit()`](/docs/api/yii/db/querytrait/index.md#method-limit) for more details. |
| `$offset` | `mixed` | The offset number. See [`Query::offset()`](/docs/api/yii/db/querytrait/index.md#method-offset) for more details. |

**Returns**

`string`: The SQL completed with ORDER BY/LIMIT/OFFSET (if any)

### buildUnionOrderByAndLimit() <span id="method-buildUnionOrderByAndLimit"></span>

```php
public function buildUnionOrderByAndLimit(string $sql, array|null $orderBy, int|yii\db\ExpressionInterface|null $limit, int|yii\db\ExpressionInterface|null $offset): string
```

Builds the global ORDER BY and LIMIT/OFFSET clauses for a UNION query.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$sql` | `string` | The complete UNION SQL without global ORDER BY/LIMIT/OFFSET clauses. |
| `$orderBy` | `array\|null` | The global order by columns. |
| `$limit` | `int\|`[`yii\db\ExpressionInterface`](/docs/api/yii/db/expressioninterface/index.md)`\|null` | The global limit. |
| `$offset` | `int\|`[`yii\db\ExpressionInterface`](/docs/api/yii/db/expressioninterface/index.md)`\|null` | The global offset. |

**Returns**

`string`: The completed UNION SQL.

**Since**

22.0

### buildWithQueries() <span id="method-buildWithQueries"></span>

```php
public function buildWithQueries(mixed $withs, mixed &$params): string
```

SQL Server does not support the `RECURSIVE` keyword for CTEs. Recursion is implicit when a CTE references itself.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$withs` | `mixed` | Of configurations for each WITH query |
| `$params` | `mixed` | The binding parameters to be populated |

**Returns**

`string`: Compiled WITH prefix of query including nested queries

**Since**

2.0.35

### checkIntegrity() <span id="method-checkIntegrity"></span>

```php
public function checkIntegrity(bool $check = true, string $schema = '', string $table = ''): string
```

Builds a SQL statement for enabling or disabling integrity checks on table constraints.

Resolves the table and schema names without loading metadata, so the statement is built without opening a
database connection. A single table yields a direct `ALTER TABLE` statement; an empty table targets every base
table in the schema, enumerated server-side from `sys.tables` (views excluded) and run in one batch.

Enabling uses `WITH CHECK CHECK CONSTRAINT ALL`, re-validating existing rows and marking the constraints trusted;
disabling uses `NOCHECK CONSTRAINT ALL`.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$check` | `bool` | Whether to enable (`true`) or disable (`false`) the integrity checks. |
| `$schema` | `string` | The schema of the tables. Defaults to an empty string, meaning the default schema. |
| `$table` | `string` | The table name. Defaults to an empty string, meaning every table in the schema. |

**Returns**

`string`: The SQL statement for enabling or disabling the integrity checks.

**See also**

- <https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql>
- <https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql>

### delete() <span id="method-delete"></span>

```php
public function delete(mixed $table, mixed $condition, mixed &$params): string
```

Creates a DELETE SQL statement.

Renders any `rowversion` in the WHERE condition as a binary literal, enabling optimistic-lock deletes.

For example,

```
$sql = $queryBuilder->delete('user', 'status = 0');
```

The method will properly escape the table and column names.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` | The table where the data will be deleted from. |
| `$condition` | `mixed` | The condition that will be put in the WHERE part. Please refer to [`Query::where()`](/docs/api/yii/db/query/index.md#method-where) on how to specify condition. |
| `$params` | `mixed` | The binding parameters that will be modified by this method so that they can be bound to the DB command later. |

**Returns**

`string`: The DELETE SQL

**See also**

- <https://github.com/yiisoft/yii2/issues/9653>

### dropColumn() <span id="method-dropColumn"></span>

```php
public function dropColumn(mixed $table, mixed $column): mixed
```

Drop all constraints before column delete.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` |  |
| `$column` | `mixed` |  |

**Returns**

`mixed`

### dropCommentFromColumn() <span id="method-dropCommentFromColumn"></span>

```php
public function dropCommentFromColumn(mixed $table, mixed $column): string
```

Builds a SQL command for adding comment to column.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` | The table whose column is to be commented. The table name will be properly quoted by the method. |
| `$column` | `mixed` | The name of the column to be commented. The column name will be properly quoted by the method. |

**Returns**

`string`: The SQL statement for adding comment on column

**Since**

2.0.8

### dropCommentFromTable() <span id="method-dropCommentFromTable"></span>

```php
public function dropCommentFromTable(mixed $table): string
```

Builds a SQL command for adding comment to table.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` | The table whose column is to be commented. The table name will be properly quoted by the method. |

**Returns**

`string`: The SQL statement for adding comment on column

**Since**

2.0.8

### dropDefaultValue() <span id="method-dropDefaultValue"></span>

```php
public function dropDefaultValue(mixed $name, mixed $table): string
```

Creates a SQL command for dropping a default value constraint.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$name` | `mixed` | The name of the default value constraint to be dropped. The name will be properly quoted by the method. |
| `$table` | `mixed` | The table whose default value constraint is to be dropped. The name will be properly quoted by the method. |

**Returns**

`string`: The SQL statement for dropping a default value constraint.

**Throws**

- [`yii\base\NotSupportedException`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS.

**Since**

2.0.13

**See also**

- <https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-default-constraints-transact-sql>
- <https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql>

### getColumnType() <span id="method-getColumnType"></span>

```php
public function getColumnType(mixed $type): string
```

Converts an abstract column type into a physical column type.

The conversion is done using the type map specified in [`$typeMap`](/docs/api/yii/db/mssql/querybuilder/index.md#property-typeMap).
The following abstract column types are supported (using MySQL as an example to explain the corresponding
physical types):

- `pk`: an auto-incremental primary key type, will be converted into "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY"
- `bigpk`: an auto-incremental primary key type, will be converted into "bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY"
- `upk`: an unsigned auto-incremental primary key type, will be converted into "int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY"
- `char`: char type, will be converted into "char(1)"
- `string`: string type, will be converted into "varchar(255)"
- `text`: a long string type, will be converted into "text"
- `smallint`: a small integer type, will be converted into "smallint(6)"
- `integer`: integer type, will be converted into "int(11)"
- `bigint`: a big integer type, will be converted into "bigint(20)"
- `boolean`: boolean type, will be converted into "tinyint(1)"
- `float``: float number type, will be converted into "float"
- `decimal`: decimal number type, will be converted into "decimal"
- `datetime`: datetime type, will be converted into "datetime"
- `timestamp`: timestamp type, will be converted into "timestamp"
- `time`: time type, will be converted into "time"
- `date`: date type, will be converted into "date"
- `money`: money type, will be converted into "decimal(19,4)"
- `binary`: binary data type, will be converted into "blob"

If the abstract type contains two or more parts separated by spaces (e.g. "string NOT NULL"), then only
the first part will be converted, and the rest of the parts will be appended to the converted result.
For example, 'string NOT NULL' is converted to 'varchar(255) NOT NULL'.

For some of the abstract types you can also specify a length or precision constraint
by appending it in round brackets directly to the type.
For example `string(32)` will be converted into "varchar(32)" on a MySQL database.
If the underlying DBMS does not support these kind of constraints for a type it will
be ignored.

If a type cannot be found in [`$typeMap`](/docs/api/yii/db/mssql/querybuilder/index.md#property-typeMap), it will be returned without any change.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$type` | `mixed` | Abstract column type |

**Returns**

`string`: Physical column type.

### insert() <span id="method-insert"></span>

```php
public function insert(mixed $table, mixed $columns, mixed &$params): string
```

Creates an INSERT SQL statement.

Wraps the `INSERT` with an `OUTPUT INSERTED.* INTO @temporary_inserted` block so that the inserted row
(including computed columns and `IDENTITY` values) can be retrieved by the caller.

For example,
```
$sql = $queryBuilder->insert('user', [
    'name' => 'Sam',
    'age' => 30,
], $params);
```
The method will properly escape the table and column names.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` | The table that new rows will be inserted into. |
| `$columns` | `mixed` | The column data (name => value) to be inserted into the table or instance of [Query](/docs/api/yii/db/query/index.md) to perform INSERT INTO ... SELECT SQL statement. Passing of [Query](/docs/api/yii/db/query/index.md) is available since version 2.0.11. |
| `$params` | `mixed` | The binding parameters that will be generated by this method. They should be bound to the DB command later. |

**Returns**

`string`: The INSERT SQL

### renameColumn() <span id="method-renameColumn"></span>

```php
public function renameColumn(string $table, string $oldName, string $newName): string
```

Builds a SQL statement for renaming a column.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `string` | The table whose column is to be renamed. The name will be properly quoted by the method. |
| `$oldName` | `string` | The old name of the column. The name will be properly quoted by the method. |
| `$newName` | `string` | The new name of the column. The name will be properly quoted by the method. |

**Returns**

`string`: The SQL statement for renaming a DB column.

### renameTable() <span id="method-renameTable"></span>

```php
public function renameTable(string $oldName, string $newName): string
```

Builds a SQL statement for renaming a DB table.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$oldName` | `string` | The table to be renamed. The name will be properly quoted by the method. |
| `$newName` | `string` | The new table name. The name will be properly quoted by the method. |

**Returns**

`string`: The SQL statement for renaming a DB table.

### resetSequence() <span id="method-resetSequence"></span>

```php
public function resetSequence(string $tableName, int|null $value = null): string
```

Creates a SQL statement for resetting the sequence value of a table's primary key.

The sequence will be reset such that the primary key of the next new row inserted will have the specified value
or the next value after the current maximum identity value.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$tableName` | `string` | The name of the table whose primary key sequence will be reset. |
| `$value` | `int\|null` | The integer value for the primary key of the next new row inserted. If this is not set, the next new row's primary key will have the next value after the current maximum identity value. |

**Returns**

`string`: The SQL statement for resetting sequence.

**Throws**

- [`yii\base\InvalidArgumentException`](/docs/api/yii/base/invalidargumentexception/index.md): if the table does not exist or there is no sequence associated with the table.

**See also**

- <https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql>
- <https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-identity-columns-transact-sql>
- <https://learn.microsoft.com/en-us/sql/t-sql/functions/ident-seed-transact-sql>
- <https://learn.microsoft.com/en-us/sql/t-sql/functions/ident-incr-transact-sql>

### selectExists() <span id="method-selectExists"></span>

```php
public function selectExists(mixed $rawSql): string
```

Creates a SELECT EXISTS() SQL statement.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$rawSql` | `mixed` | The subquery in a raw form to select from. |

**Returns**

`string`: The SELECT EXISTS() SQL statement.

**Since**

2.0.8

### update() <span id="method-update"></span>

```php
public function update(mixed $table, mixed $columns, mixed $condition, mixed &$params): string
```

Creates an UPDATE SQL statement.

Excludes server-managed `rowversion` columns from the `SET` clause and renders any `rowversion` in the WHERE as a
binary literal, enabling a `rowversion` column to serve as the optimistic lock attribute.

Note: SQL Server regenerates the `rowversion` on every write and the in-memory attribute is not refreshed
afterwards; reload the record before saving the same instance again to avoid a spurious `StaleObjectException`.

For example,

```
$params = [];
$sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params);
```

The method will properly escape the table and column names.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` | The table to be updated. |
| `$columns` | `mixed` | The column data (name => value) to be updated. |
| `$condition` | `mixed` | The condition that will be put in the WHERE part. Please refer to [`Query::where()`](/docs/api/yii/db/query/index.md#method-where) on how to specify condition. |
| `$params` | `mixed` | The binding parameters that will be modified by this method so that they can be bound to the DB command later. |

**Returns**

`string`: The UPDATE SQL

**See also**

- <https://github.com/yiisoft/yii2/issues/9653>

### upsert() <span id="method-upsert"></span>

```php
public function upsert(mixed $table, mixed $insertColumns, mixed $updateColumns, mixed &$params): mixed
```

Generates a `MERGE` statement that inserts a row or updates the matching one in a single atomic operation.

The `WITH (HOLDLOCK)` table hint serializes concurrent upserts so they cannot insert duplicate keys.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` |  |
| `$insertColumns` | `mixed` |  |
| `$updateColumns` | `mixed` |  |
| `$params` | `mixed` |  |

**Returns**

`mixed`

**See also**

- <https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql>
- <https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-condition-with-merge/>

### buildAddCommentSql() <span id="method-buildAddCommentSql"></span>

```php
protected function buildAddCommentSql(string $comment, string $table, string|null $column = null): string
```

Builds the SQL to add or update the `MS_Description` extended property of a table or column.

Resolves the table name without loading metadata, so the statement is assembled without opening a database
connection. The comment is added when the property is absent and updated otherwise, and the extended-property
routines are qualified with the catalog when the name includes one.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$comment` | `string` | Comment text. Escaped and wrapped as an `N'...'` literal by the method. |
| `$table` | `string` | Target table. May be 'catalog-', 'schema-', or bracket-qualified. |
| `$column` | `string\|null` | Target column, or `null` to comment the table itself. |

**Returns**

`string`: SQL statement that adds or updates the comment.

**Since**

2.0.24

### buildRemoveCommentSql() <span id="method-buildRemoveCommentSql"></span>

```php
protected function buildRemoveCommentSql(string $table, string|null $column = null): string
```

Builds the SQL to remove the `MS_Description` extended property from a table or column.

Resolves the table name without loading metadata, so the statement is assembled without opening a database
connection. The property is dropped only when present, and the extended-property routines are qualified with the
catalog when the name includes one.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `string` | Target table. May be 'catalog-', 'schema-', or bracket-qualified. |
| `$column` | `string\|null` | Target column, or `null` to remove the table comment. |

**Returns**

`string`: SQL statement that removes the comment.

**Since**

2.0.24

### defaultExpressionBuilders() <span id="method-defaultExpressionBuilders"></span>

```php
protected function defaultExpressionBuilders(): array
```

Contains array of default expression builders. Extend this method and override it, if you want to change
default expression builders for this query builder. See [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders) docs for details.

**Returns**

`array`

**Since**

2.0.14

### extractAlias() <span id="method-extractAlias"></span>

```php
protected function extractAlias(mixed $table): bool|array
```

Extracts table alias if there is one or returns false

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$table` | `mixed` |  |

**Returns**

`bool|array`

**Since**

2.0.24

### prepareSelectQuery() <span id="method-prepareSelectQuery"></span>

```php
protected function prepareSelectQuery(yii\db\Query $query): yii\db\Query
```

Performs driver-specific preparation after [`Query::prepare()`](/docs/api/yii/db/query/index.md#method-prepare).

SQL Server rejects `FETCH NEXT 0` and wrapping a compound query requires every result column to have a unique
name. Applying `TOP (0)` to every UNION operand preserves zero-row semantics without a derived table.

Implementations must not modify the given query; they must return a modified clone instead.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$query` | [`yii\db\Query`](/docs/api/yii/db/query/index.md) | The prepared query. |

**Returns**

[`yii\db\Query`](/docs/api/yii/db/query/index.md): The query to build.

**Since**

22.0