August 30, 2024 at 3:27 pm
ScottPletcher wrote:There might be some fair overhead to getting the current length of a text column. If you really have to have that, add a column to the table to store the LEN or DATALENGTH and use a trigger(s) to maintain it.
Would you use a trigger in preference to a COMPUTED PERSISTED column for this? If so, can you explain why? Thanks.
I know your question was to Scott, but I personally wouldn't even persist this to disk. I'd just use a nested query or a CTE to get that data UNLESS there was some good reason to persist to disk. If it is a one-off query, no need to persist to disk. If it is a rarely run query, no need to persist to disk.
As for trigger vs computed persisted column, for me it really depends on IF more work needs to be done and on the times I need to calculate. For example, if I only care about calculating it on an INSERT or only on an UPDATE, then it'll be a trigger. IF the computed column needs data from other rows or other tables, then it's a trigger. So, in this specific use case, I'd probably use a computed persisted column.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 30, 2024 at 3:31 pm
ScottPletcher wrote:There might be some fair overhead to getting the current length of a text column. If you really have to have that, add a column to the table to store the LEN or DATALENGTH and use a trigger(s) to maintain it.
Would you use a trigger in preference to a COMPUTED PERSISTED column for this? If so, can you explain why? Thanks.
Personally, yes, I would use a trigger in this case, based on my last research into this issue.
(1) A scalar function in a computed column, persisted or not, prevents all queries against the table from going parallel. That could be a big performance hit.
(2) Computed columns in indexes also require some specific settings (ANSI, etc.) that sometimes aren't set properly. Perhaps they "should" be, but that's gonna be irrelevant when the prod code blows up when someone tries to UPDATE table data and a setting(s) isn't(aren't) correct. For example, I've seen developers deliberately set ANSI_WARNINGS off, (and I understand why they did it), but that will cause statement failure if there is an index on a computed column.
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".
August 30, 2024 at 3:34 pm
Phil Parkin wrote:ScottPletcher wrote:There might be some fair overhead to getting the current length of a text column. If you really have to have that, add a column to the table to store the LEN or DATALENGTH and use a trigger(s) to maintain it.
Would you use a trigger in preference to a COMPUTED PERSISTED column for this? If so, can you explain why? Thanks.
Personally, yes, I would use a trigger in this case, based on my last research into this issue.
(1) A scalar function in a computed column, persisted or not, prevents all queries against the table from going parallel. That could be a big performance hit.
(2) Computed columns in indexes also require some specific settings (ANSI, etc.) that sometimes aren't set properly. Perhaps they "should" be, but that's gonna be irrelevant when the prod code blows up when someone tries to UPDATE table data and a setting(s) isn't(aren't) correct. For example, I've seen developers deliberately set ANSI_WARNINGS off, (and I understand why they did it), but that will cause statement failure if there is an index on a computed column.
I learned something new today! Thanks for that Scott!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 30, 2024 at 3:59 pm
Phil Parkin wrote:ScottPletcher wrote:There might be some fair overhead to getting the current length of a text column. If you really have to have that, add a column to the table to store the LEN or DATALENGTH and use a trigger(s) to maintain it.
Would you use a trigger in preference to a COMPUTED PERSISTED column for this? If so, can you explain why? Thanks.
I know your question was to Scott, but I personally wouldn't even persist this to disk. I'd just use a nested query or a CTE to get that data UNLESS there was some good reason to persist to disk. If it is a one-off query, no need to persist to disk. If it is a rarely run query, no need to persist to disk.
As for trigger vs computed persisted column, for me it really depends on IF more work needs to be done and on the times I need to calculate. For example, if I only care about calculating it on an INSERT or only on an UPDATE, then it'll be a trigger. IF the computed column needs data from other rows or other tables, then it's a trigger. So, in this specific use case, I'd probably use a computed persisted column.
I was addressing the OP. Once the value is stored in the row, then an index could be built on ( documentid, <resolution_text_length> ) that would match the original query WHERE. Presumably the results would be few enough rows from one documentid to allow that index to be used to satisfy the query, thereby speeding it up, as was originally requested.
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 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply