things I can't do in a TVF table-valued function (1 Viewer)

[Isaac]

Hello,
I'm doing some experimentation with various SQL objects to connect them to PowerBI just to see if I get any differences in performance.
For example, someone made a View that's pretty slow that we use to connect to PowerBI.

My experiment wants to use a table-valued function, multi-statement, so I can segment the data a little better into temp tables and do joins that are more appropriate/efficient based on the cardinality/relationships between the tables.

I'm finding limitations, but unsure which ones are a hard "no" versus which ones I just have the syntax wrong.

I'm trying to:

• select into #temp tables, several times
• create some indexes on the #temp tables
• Update the temp tables while joining to other base tables
• finally , the Insert into the #Table that the function officially returns.

Can I not do those first 3 things?? What is the value of a multistatement TVF if you can't select into temp tables inside of it? Or am I wrong on this and just messed up syntax somewhere? I would post code but would have to do so much sanitizing/rigging - so am trying to describe the question in a way that it can still hopefully be answered by a T-SQL expert

Thanks
Isaac

Edit - for clarity, I'm getting the error about side-effecting operators whenever I try to select into, and it's leading to a lot of stack overflow pages about things you can't do in a tvf

 

[Isaac]

I'm starting to read and understand more, and it seems like regardless of what I 'can' or 'can't' do, it might be a better idea to figure out a way to convert those planned temp tables to CTE's and do an inline function for performance's sake..which may lead to me not having to worry about temp tables being disallowed, if indeed they are ... but would still like to understand the can's and cant's a little better, any insights appreciated.

 

[cheekybuddha]

You can probably do something else.

(But without any idea of your schema, nor what you are trying to achieve, it's difficult to suggest what!)

 

[Minty]

You can use a temp table in a function but it has to be declared as a table variable @tablename
More here : https://stackoverflow.com/questions/9844854/is-it-possible-to-have-temp-tables-in-a-function

I don't think you can use any index on it though.

If it's that complex maybe a stored procedure that returns a table could perform better?

 

[Galaxiom]

Sounds like a job for a stored procedure.

BTW I once said on here that a CTE was run just once at the beginning but I have since been told that a CTE is simply a quick way to repeat the code and it runs separately for each instance it is used.

 

[Isaac]

Sounds like a job for a stored procedure

It does, and I was just racking my brain trying to remember why we weren't using one and it was because I was told by our resident expert that power bi could not connect to a stored procedure. But I believe it actually CAN. So I'm going to revisit that.

 

[theDBguy]

It does, and I was just racking my brain trying to remember why we weren't using one and it was because I was told by our resident expert that power bi could not connect to a stored procedure. But I believe it actually CAN. So I'm going to revisit that.

I think it can. I believe I've done it before, but may have to check how I did it. I might have used DAX to connect to it.

 

[theDBguy]

I think it can. I believe I've done it before, but may have to check how I did it. I might have used DAX to connect to it.

I checked my files, and this is what I have.

Hope that helps...

 

[Isaac]

I checked my files, and this is what I have.
View attachment 118413
Hope that helps...

Thank you