TSQL vs Gen2 Dataflow

[st_0999]

Hi, 

Will support be added soon to be able to write tables into a Lakehouse via T-SQL?

I know it's possible for a Warehouse, but not yet for a Lakehouse. 

I've found that some queries are just 10x faster with T-SQL vs a Gen2 Dataflow, so I've been migrating over into T-SQL Views where possible (saving them in the SQL Endpoint environment). Trouble with Views is that you can't apply any ordering. That's only possible with Stored Procedures, and Stored procedures are only available so far with Warehouses.

I'm not Spark SQL via a notebook since I don't like how you can't retain upper case naming on the Table names). 

Would be awesome to find out more!

[miguel]

Hey!

I will be moving this to the Synapse forum given that this goes out of the scope of what Dataflows can do. This might also be a good suggestion for the ideas site aka.ms/FabricIdeas

[DennesTorres]

Hi,

The explanation about what exactly you are doing is a bit confused. But let me try to add something.

I don't think this support will be added soon, it doesn't seems to be the purpose. But I'm not in position to answer this part.

You can use Spark SQL in a notebook to create tables in a lakehouse, I'm not sure if this helps.

About views, there is an old trick about views and order by: If you add a TOP 100 PERCENT in your query, you are able to add an order by to your view. I haven't tested in a lakehouse yet, but it should work.

Kind Regards,

Dennes

[st_0999]

I'm not Spark SQL via a notebook since I don't like how you can't retain upper case naming on the Table names.

With T-SQL you have this control. That's the only reason I'm using them - T-SQL Views

Otherwise, would happily use Spark SQL. There I wouldn't even use Views, (not sure if Spark SQL even supports views), but would instead do a write back to the Lakehouse, with Ordering added too. 

Hopefully support for T-SQL to write back to the Lakehouse might be added soon...? Or the ability to write from a Spark SQL notebook, where I can make sure that the Table name retains it's Upper Casing.