December 12, 2020 at 3:43 pm
The big disadvantage I see for dynamic-only code is that you can't analyze them at all, whereas with views you can.
For example, "How many Configs use 'Integer3'?" or "Do we have an existing Attributes column that is not being used at all? (So that maybe we can repurpose it)".
The other disadvantages are the somewhat increased overhead and the possibility of increased and/or undetected errors. If you create a view, any error will be found immediately. If you generate dynamic code, you won't find an error until you actually exec the code.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 12, 2020 at 5:34 pm
The other disadvantages are the somewhat increased overhead and the possibility of increased and/or undetected errors. If you create a view, any error will be found immediately. If you generate dynamic code, you won't find an error until you actually exec the code.
I definitely agree about the "increased overhead" of using dynamic SQL. I tried to minimize that a bit by using sp_executeSQL in hopes of getting caching similar to what using a view would but, I agree, dynamic code will always have more overhead.
As for increased errors, there's no more chance of that in what I wrote than using the view template that you used because the code follows an inline template. Neither will detect errors that will only be generated at runtime until they are exectued.
As for the dynamic code not being able to answer the questions of...
The big disadvantage I see for dynamic-only code is that you can't analyze them at all, whereas with views you can.
For example, "How many Configs use 'Integer3'?" or "Do we have an existing Attributes column that is not being used at all? (So that maybe we can repurpose it)".
... remember that the code is generated based on a config table, which has all the information you need to answer those questions.
The other thing to remember is that I'm actually opposed to both methods and wouldn't do or allow either on my production boxes. I'm also opposed to the whole principle of the Attributes table that Aaron has to contend with. Heh... and so with that, with no evidence to the contrary that I've seen yet, I'll say that all methods used against the Attributes table are going to have a major suck factor because the whole premise of the Attributes table is faulty at best.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply