June 21, 2012 at 7:57 pm
hisakimatama (6/21/2012)Unfortunately, I can't add anymore columns to the existing table
We hear that on the forum a lot (not saying it is an invalid response, just saying). That's why I suggested it could be done with a CTE or a VIEW.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 22, 2012 at 8:08 am
Unfortunately, I can't add anymore columns to the existing table without causing it to bloat up more.
Keep in mind that computed columns are NOT physically stored in the table, just their definition is [unless you explicitly tell SQL to store the result, which you would not do in this case]. Computed columns are materialized at run time by SQL only if they are referenced. That is, computed columns are virtual columns.
Yes, a view is a decent alternative. A CTE or a derived table sub-query will work, but then everyone codes their own and so the column names are not consistent.
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".
June 22, 2012 at 12:13 pm
Ah, interesting! I wasn't aware of that. For a bit more clarity, if I included those computed columns and accessed the table via a SELECT * query elsewhere, would it also return those columns?
I cringe at the mention of it, but I'm having to use Microsoft Access as the front-end to this database. It's what the company I work for has run on since it began, and while I've gotten their database moved to SQL Server, the front-end components are still entirely in Access. Furthermore, most of the queries that were written before my time here are the dreaded SELECT * variety, so it's entirely possible that returning more data than what was originally planned for will cause a query or process to malfunction entirely.
That said, I can fully see where computed columns might come in handy around the database (particularly in one of the daily processes I have to run that has to run a bunch of calculations whose result could easily be stored in a computed column), but I don't want to potentially cause a process or two to suddenly explode :hehe:.
- 😀
June 22, 2012 at 12:29 pm
if I included those computed columns and accessed the table via a SELECT * query elsewhere, would it also return those columns?
Yes. In that case, SQL would consider that a "reference" to the computed columns, and so would materialize them and output them as part of a SELECT *.
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".
June 22, 2012 at 1:05 pm
Ack. Oh well, that rules it out in the specific scope of this question, but I can certainly toss computed columns around in a few other things. I'll do a bit of testing and see how they work out compared to the usual routine, but I'd imagine the computed column method would be more efficient in the situations I'm thinking of.
- 😀
June 22, 2012 at 2:27 pm
Computed columns are generally very efficient.
Also, they keep the column name the same for everyone -- CTEs get re-coded and so the column names can vary greatly from one piece of code to the next.
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".
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply