Specifying VARCHAR and NVARCHAR size for db targets

[cody-scott]

Hi! Curious if it's possible to specify the length of VARCHAR and NVARCHAR columns when using a db (sql server specifically) as a target.

Ideally on a per column level too.

Thanks!

Awesome tool too!

[flarco]

Hey @cody-scott, just curious, can you share on the reasoning for your use case? Since Sling automatically determines the varchar length needed per column basis.

Here is the mapping of all types into respective databases: https://github.com/flarco/dbio/blob/main/database/templates/types_general_to_native.tsv

As you can see, for SQL Server, string maps to nvarchar(), which then gets filled with a length based on this logic: https://github.com/flarco/dbio/blob/main/database/database.go#L2260-L2272

[cody-scott]

Hi @flarco. Right now I'm trying to go from an oracle db to SQL Server.

What i'm seeing is the replication of the oracle table seems to be bringing the the tables in with NVARCHAR(MAX). The other piece i'm seeing is that the PK fields are being replicated as decimal, but should be BIGINT. Perhaps this is something in the way that the db is configured on our side.

In any case, I think i could solve most of these issues if i could define the types of the column, then rely on sling to replicate the data itself, inserting into the defined typed columns i specify (as some form of DDL i'm guessing).

[cody-scott]

Another example is a column that would be VARCHAR(20) is being stored as VARCHAR(MAX).

[flarco]

K I see. Yes, Oracle uses NUMBER for integers and decimals, so sling defaults that to decimals. And I'm guessing the string columns are CLOB or BLOB? Those get mapped to the text general type which translates to NVARCHAR(MAX), since those are usually large values.

Indeed your best bet is to define the source.options.columns, where you can specify integer / bigint for the PKs, and string for the others if you don't want text/NVARCHAR(MAX) for them. Sling will determine the proper length and auto create NVARCHAR({length}) columns. A suggestion is to define those in the defaults section in the Replication YAML (if the column names are re-used across many tables, typical for PKs).

[flarco]

Another example is a column that would be VARCHAR(20) is being stored as VARCHAR(MAX).

I see why, look here: https://github.com/flarco/dbio/blob/main/database/templates/types_native_to_general.tsv#L86-L101

varchar2 and nvarchar2 are being mapped to general type text. Don't recall why I put it that way 🤔

[flarco]

Ah, Oracle allows up to 4000 bytes for varchar2, where in other dbs this can be limited to 255. So yeah text is correct to limit errors, which gets translated to NVARCHAR(MAX) in destination. Maybe just put string for those columns?

[cody-scott]

Awesome! I will try it out! Thanks @flarco!

[cody-scott]

One potential bug i'm seeing is a column that has all NULL is returning as NVARCHAR(0) in sql server; i'm assuming its scanning and seeing all null, then returning 0 as the length. In this case it seems like the text would solve that inference. Maybe a default of 1000 or something when all are null for a string type?

Interesting if i probe the source from SQL using a linked server, its giving back VARCHAR(100) for the column (which is how it is defined in the Oracle Source).