dbt.type_string() compiles to varchar(8000)

[pSpitzner]

I noticed that dbt.type_string() compiles to varchar(8000) and wondered if this is intended behaviour.

On other backends, like duckdb, or postgres in particular, it comiles to TEXT, which I think in sql server would be closer to varchar(max) than varchar(8000). Also, in the dbt docs it says _"This macro yields the database-specific data type for TEXT.", implying the unlimited length?

In my current use-case, this leads to a bit of inconsistent behaviour, and I am now wondering if I want to introduce a custom macro to ensure a string type of unlimited length. However, if other users would also expect strings of unlimited length, we might just make the change here?

Thanks!
Paul

ps: thanks for maintaining this awesome project!

[Benjamin-Knight]

In the base DBT adapter it definitely appears as you say that they expect that string specifically goes to a TEXT type data type. In fact its the only type defined by default

This adapter does expose a varchar and nvarchar data type as well already, although because we inherit from Fabric currently the nvarchar is actually just varchar again, if users want to access the varchar(8000).

My only concern with this change is that varchar(max) and varchar(8000) are very different beasts and that any users currently using dbt.type_string() this could easily be a breaking change, if they were using it as an index column. Whilst they really shouldn't be doing that it would technically work now and not after this change.

I'm still inclined that this is a change that should be made.

[pSpitzner]

Thanks for looking into it, @Benjamin-Knight

I fully agree, this would be a breaking change.

My suggestion would be to clearly document it as such in the changelog, along instructions on how to override the macro to restore the old behaviour. I guess this should be a simple macro.sql with notes about dispatch order.

The alternative would be the inverse, we close this issue, add it to the docs, and post here how users can create the consistent behaviour.

If you point me at the relevant code sections, I will try to start a PR (not very familiar with dbt adapter structure yet)

[Benjamin-Knight]

I've prepared the update but its based on the pull to update to remove Fabric in #628

When that makes it in we can progress this. It uses behaviour flags to restore the legacy functionality which is how DBT normally handles this.

If you want to see the code sections I updated you can view it in the last 2 commits on the branch.

https://github.com/Benjamin-Knight/dbt-sqlserver/tree/%23626_string_types