Dbt-sqlserver

Minor release to follow up on 1.4.1 and 1.4.0.

Adding `nolock` to information_schema and sys tables/views can be overriden with the dispatched `information_schema_hints` macro. This is required for adapters inheriting from this one.

This is a minor release following up on 1.4.0 with fixes for long outstanding issues.
Contributors to this release are [cbini](https://github.com/cbini), [rlshuhart](https://github.com/rlshuhart), [jacobm001](https://github.com/jacobm001), [baldwicc](https://github.com/baldwicc) and [sdebruyn](https://github.com/sdebruyn).

Features

- Added support for a custom schema owner. You can now add `schema_authorization` (or `schema_auth`) to your profile.
If you do so, dbt will create schemas with the `authorization` option suffixed by this value.
If you are authorizing dbt users or service principals on Azure SQL based on an Azure AD group,
it's recommended to set this value to the name of the group. [153](https://github.com/dbt-msft/dbt-sqlserver/issues/153) [#382](https://github.com/dbt-msft/dbt-sqlserver/issues/382)
- Documentation: added more information about the permissions which you'll need to grant to run dbt.
- Support for `DATETIMEOFFSET` as type to be used in dbt source freshness tests. [254](https://github.com/dbt-msft/dbt-sqlserver/issues/254) [#346](https://github.com/dbt-msft/dbt-sqlserver/issues/346)
- Added 2 options related to timeouts to the profile: `login_timeout` and `query_timeout`.
The default values are `0` (no timeout). [162](https://github.com/dbt-msft/dbt-sqlserver/issues/162) [#395](https://github.com/dbt-msft/dbt-sqlserver/issues/395)

Bugfixes

- Fixed issues with databases with a case-sensitive collation
and added automated testing for it so that we won't break it again. [212](https://github.com/dbt-msft/dbt-sqlserver/issues/212) [#391](https://github.com/dbt-msft/dbt-sqlserver/issues/391)
- Index names are now MD5 hashed to avoid running into the maximum amount of characters in index names
with index with lots of columns with long names. [317](https://github.com/dbt-msft/dbt-sqlserver/issues/317) [#386](https://github.com/dbt-msft/dbt-sqlserver/issues/386)
- Fixed the batch size calculation for seeds. Seeds will run more efficiently now. [396](https://github.com/dbt-msft/dbt-sqlserver/issues/396) [#179](https://github.com/dbt-msft/dbt-sqlserver/issues/179) [#210](https://github.com/dbt-msft/dbt-sqlserver/issues/210) [#211](https://github.com/dbt-msft/dbt-sqlserver/issues/211)
- Added `nolock` to queries for all information_schema/sys tables and views.
dbt runs a lot of queries on these metadata schemas.
This can often lead to deadlock issues if you are using a high number of threads or dbt processes.
Adding `nolock` to these queries avoids the deadlocks. [379](https://github.com/dbt-msft/dbt-sqlserver/issues/379) [#381](https://github.com/dbt-msft/dbt-sqlserver/issues/381)
- Fixed implementation of `{{ hash(...) }}` for null values. [392](https://github.com/dbt-msft/dbt-sqlserver/issues/392)

Under the hood

- Fixed more concurrency issues with automated Azure integration testing.
- Removed extra `__init__.py` files. [171](https://github.com/dbt-msft/dbt-sqlserver/issues/171) [#202](https://github.com/dbt-msft/dbt-sqlserver/issues/202)
- Added commits to be ignored in git blame for easier blaming. [385](https://github.com/dbt-msft/dbt-sqlserver/issues/385)

- [Elliot2718](https://github.com/Elliot2718) made their first contribution in https://github.com/dbt-msft/dbt-sqlserver/pull/204
- [i-j](https://github.com/i-j) made their first contribution in https://github.com/dbt-msft/dbt-sqlserver/pull/345

Features

- Support for [dbt-core 1.4](https://github.com/dbt-labs/dbt-core/releases/tag/v1.4.0)
- [Incremental predicates](https://docs.getdbt.com/docs/build/incremental-models#about-incremental_predicates)
- Add support for Python 3.11
- Replace deprecated exception functions
- Consolidate timestamp macros

Bugfixes

- Add `nolock` query hint to several metadata queries to avoid deadlocks by [Elliot2718](https://github.com/Elliot2718) in https://github.com/dbt-msft/dbt-sqlserver/pull/204
- Rework column metadata retrieval to avoid duplicate results and deadlocks by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/368
- Model removal will now cascade and also drop related views so that views are no longer in a broken state by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/366
- Fixed handling of on_schema_change for incremental models by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/376

Under the hood

- Fixed lots of testing concurrency issues
- Added all available tests as of dbt 1.4.6

**Full Changelog**: https://github.com/dbt-msft/dbt-sqlserver/compare/v1.3.1...v1.4.0

<details><summary>PR changelog</summary>
<p>

- Bump pre-commit from 2.20.0 to 3.2.0 by [dependabot](https://github.com/dependabot) in https://github.com/dbt-msft/dbt-sqlserver/pull/344
- Bump docker/build-push-action from 3.2.0 to 4.0.0 by [dependabot](https://github.com/dependabot) in https://github.com/dbt-msft/dbt-sqlserver/pull/331
- [pre-commit.ci] pre-commit autoupdate by [pre-commit-ci](https://github.com/pre-commit-ci) in https://github.com/dbt-msft/dbt-sqlserver/pull/316
- Bump wheel from 0.38.4 to 0.40.0 by [dependabot](https://github.com/dependabot) in https://github.com/dbt-msft/dbt-sqlserver/pull/343
- Copy for workflow schtuff by [dataders](https://github.com/dataders) in https://github.com/dbt-msft/dbt-sqlserver/pull/350
- avoid publishing docker from other branches than master by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/351
- bump pre-commit by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/353
- fix pre-commit for python 3.7 by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/354
- use 127.0.0.1 to avoid issues with local testing by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/358
- allow for more flexible local testing with azure auth by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/359
- credit where due by [dataders](https://github.com/dataders) in https://github.com/dbt-msft/dbt-sqlserver/pull/355
- remove condition for azure testing by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/360
- ignore owner when testing docs in azure by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/365
- impl of information_schema name closer to default by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/367
- Add nolock by [Elliot2718](https://github.com/Elliot2718) in https://github.com/dbt-msft/dbt-sqlserver/pull/204
- Fix concurrency issues and document create as by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/368
- add debug tests by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/363
- add concurrency test by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/362
- add aliases tests by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/361
- add ephemeral error handling test by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/364
- mark db-wide tests as flaky by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/369
- remove azure max parallel test runs by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/370
- add nolock to more metadata calls to avoid deadlocks by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/374
- add query comment tests by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/375
- add seed tests and add cascade to drop relation by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/366
- make testing faster by running multithreaded by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/372
- add tests for changing relation type by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/373
- [incremental models] add tests, various bugfixes and support for incremental predicates by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/376

</p>
</details>

Minor release to loosen dependency on dbt-core and pyodbc

Features

- Support for [dbt-core 1.3](https://github.com/dbt-labs/dbt-core/releases/tag/v1.3.0)
- Python models are currently not supported in this adapter
- The following cross-db macros are not supported in this adapter: `bool_or`, `array_construct`, `array_concat`, `array_append`

Fixes

- The macro `type_boolean` now returns the correct data type (`bit`)

Chores

- Update adapter testing framework
- Update dependencies and pre-commit hooks

Possibly breaking change: connection encryption

For compatibility with MS ODBC Driver 18, the settings `Encrypt` and `TrustServerCertificate` are now always added to the connection string.
These are configured with the keys `encrypt` and `trust_cert` in your profile.
In previous versions, these settings were only added if they were set to `True`.

The new version of the MS ODBC Driver sets `Encrypt` to `True` by default.
The adapter is following this change and also defaults to `True` for `Encrypt`.

The default value for `TrustServerConnection` remains `False` as it would be a security risk otherwise.

This means that connections made with this version of the adapter will now have `Encrypt=Yes` and `TrustServerCertificate=No` set if you are using the default settings.
You should change the settings `encrypt` or `trust_cert` to accommodate for your use case.

Features

- Support for [dbt-core 1.2](https://github.com/dbt-labs/dbt-core/releases/tag/v1.2.0)
- Full support for the new [grants config](https://docs.getdbt.com/reference/resource-configs/grants)
- New configuration option: `auto_provision_aad_principals` - setting this to `true` will automatically create contained database users linked to Azure AD principals or groups if they don't exist yet when they're being used in grant configs
- Support for MS ODBC Driver 18
- Support automatic retries with new `retries` setting introduced in core
- The correct owner of a table/view is now visible in generated documentation (and in catalog.json)
- A lot of features of dbt-utils & T-SQL utils are now available out-of-the-box in dbt-core and this adapter. A new release of T-SQL utils will follow.
- Support for all `type_*` macros
- Support for all [cross-database macros](https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros), except:
- `bool_or`
- `listagg` will only work in SQL Server 2017 or newer or the cloud versions. The `limit_num` option is unsupported. `DISTINCT` cannot be used in the measure.

Fixes

- In some cases the `TIMESTAMP` would be used as data type instead of `DATETIMEOFFSET`, fixed that

Chores

- Update adapter testing framework to 1.2.1
- Update pre-commit, tox, pytest and pre-commit hooks
- Type hinting in connection class
- Automated testing with SQL Server 2017, 2019 and 2022
- Automated testing with MS ODBC 17 and MS ODBC 18