SQL Server scans defines some of the data types as the same (variable length character, binary, string data)

[jelenadreskai]

nvarchar[n] and nvarchar [max] are treated the same from SQL Server Importer perspective. When they are imported, they are imported as nvarchar. In certain situations, users would want to know the difference. Can the nvarchar[max] be scanned and stored as a different data type nvarchar[max] for example.

This applies to the following TSQL data types:

1. nvarchar[n] & nvarchar [max] -> both considered as nvarchar
2. varbinary[n] & varbinary [max] -> both considered as varbinary
3. varchar[n] & varchar[max] -> both considered as varchar

Not super important from our perspective, as these are rarely used, but thought it may be a good idea to keep track of the observation. Perhaps it is like this by design.

[aaronforshaw]

@jelenadreskai as a side note the length of each column is saved as profiled metadata. For example I have a table t1 with a column c1 defined as nvarchar(20). The length (20) is saved in the Character Maximum Length metadata property, which is in the 'MS SQL Column' profile on the Data Element:

In the case of nvarchar(max) the Character Maximum Length is -1, which refers to a large type. See https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql?view=sql-server-ver15

It would be possible to do what you suggest i.e. use the Character Maximum Length when constructing the Data Types, but whether or not this is intentionally not done by design, I don't know.

[olliefreeman]

@aaronforshaw @jelenadreskai the above information stated by aaron is by intent. The choice was to define the datatype as a (for instance) varchar and then have the column itself state the size of the column. This meant we could define a fixed list of datatypes rather than what would potentially be an unlimited amount of datatypes, one for each random length.
The same goes for any datatype which has addtional constraints, such as numeric and precision

[olliefreeman]

this will be something which will be in the documentation for the plugin when we get round to it

[olliefreeman]

leaving open to remind that it needs documenting across all the database plugins