Create an Azure Synapse temp table from a SELECT with ORDER BY
I would imagine this would work for you:
CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT 
    ROW_NUMBER() OVER (
        ORDER BY S.[name], T.[name], C.column_id) AS ID,
    S.[name] as [schema], 
    T.[name] as [table], 
    C.[column_id] as [ordinal],
    C.[name] as [column],
    TY.[name] as [type]
FROM sys.columns AS C
JOIN sys.types AS TY
    ON TY.system_type_id = C.system_type_id
JOIN sys.tables AS T
    ON T.[object_id] = C.[object_id]
JOIN sys.schemas AS S
    ON S.[schema_id] = T.[schema_id];

The key difference is moving the ordering to the ROW_NUMBER window function, to determine the order in which numbers are assigned.