Yeah, it’s a tuple that specifies both the digits within a second and the second element is the precision of it. So that should be able to work I’d imagine? This is a fairly new change for note so it is understandable that Ecto2’s mssql adapter didn’t take advantage of it.
Ecto2 mssql adapter worked with erlang datetime tuple, so it kept precision ok so far. Problem is if we cut off 7th digit that MSSQL returns in select and then if you try to use that value, say in where clause, it will probably miss ore include some results. As I said that could be rare, but if you use DateTime2 you probably need that 7th digit. Second thing, in migrations, if you do not set precision for datetime2 it will default to 7 digits. Jose proposed that in migrations we use 6 digits precision for datetime2, if nothing is specified. But what if one is not using migrations (existing database and this is common cases for MSSQL dbs) and do not know that we nailed precision in ecto adapter to max 6 digits? I was thinking adding
Ecto.Adapter.MSSQL.DateTime2 custom type BUT integration test are then useless for me. Integration test schema is not organised by type in ecto repo, so I can’t exclude tables or test cases that are using
MSSQL still uses
ntext types, and what so ever, fulltext search works with such fields (project I’m working on is using fulltext search in MSSQL so I vote to support as is it now )
Same as above,
varbinary is what TDS is using for
binary and almost all DB I worked on had varbinary in some tables and did binary operations to achieve e.g. masking. types in MSSQL really matters, and there are some penalties if you try not taking care about what you are sending. For example, if you send
nvarchar parameter and try to compare it with
varchar field, execution plan will have greater cost since number of “reads” will increase since each column has to be converted to nvarchar (unicode), so queries that was normally running in milliseconds then will run in seconds if table has millions of rows. Also, I will check, but I think that you can’t store nvarchar parameter value into varbinary filed, especially case if you try to store 16 unicode characters into varbinary max sized to 16 bytes
All ecto types has corresponding mssql type, and I’m not sure why we should remove support for any db type. Especially cause MSSQL users are not startups that just started a business. They are rather users that decided to give elixir a try on existing database. Such users (companies) are, or near to be, fully developed and doing stuff from scratch is not so common thing unfortunately.