Materialize/doc/user/shared-content/sql-server-considerations.md

Schema changes

{{< include-md file="shared-content/schema-changes-in-progress.md" >}}

{{% schema-changes %}}

Supported types

Materialize natively supports the following SQL Server types:

• tinyint
• smallint
• int
• bigint
• real
• double
• bit
• decimal
• numeric
• money
• smallmoney
• char
• nchar
• varchar
• nvarchar
• sysname
• binary
• varbinary
• json
• date
• time
• smalldatetime
• datetime
• datetime2
• datetimeoffset
• uniqueidentifier

Replicating tables that contain unsupported data types is possible via the EXCLUDE COLUMNS option for the following types:

• text
• ntext
• image
• varchar(max)
• nvarchar(max)
• varbinary(max)

Columns with the specified types need to be excluded because SQL Server does not provide the "before" value when said column is updated.

Timestamp Rounding

The time, datetime2, and datetimeoffset types in SQL Server have a default scale of 7 decimal places, or in other words a accuracy of 100 nanoseconds. But the corresponding types in Materialize only support a scale of 6 decimal places. If a column in SQL Server has a higher scale than what Materialize can support, it will be rounded up to the largest scale possible.

-- In SQL Server
CREATE TABLE my_timestamps (a datetime2(7));
INSERT INTO my_timestamps VALUES
  ('2000-12-31 23:59:59.99999'),
  ('2000-12-31 23:59:59.999999'),
  ('2000-12-31 23:59:59.9999999');

-- Replicated into Materialize
SELECT * FROM my_timestamps;
'2000-12-31 23:59:59.999990'
'2000-12-31 23:59:59.999999'
'2001-01-01 00:00:00'