April 22, 2009 at 2:00 pm
In another thread/post on how to format a phone number in T-SQL, SQL Guru Jeff Moden made a great suggestion of adding to the table a computed column to store the phone number formatted. This way the combination of string functions and concatenations needed would occur only when a record is added/updated in the table instead of everytime the phone number is queried (which is often).
My question for everyone is what effect if any would adding a computed column have on existing DML statements; specifically INSERTS & UPDATES? The DB is used by a third party vendor who frowns on customer customizations that alter/affect any of the applications core objects, especially a table. I'm not as familiar with computed columns; just haven't used them for a while.
We are running SQL Server 2005 with the DB Compatability level set to 8.0/2000 (required by vendor who doesn't natively support their product on SQL 2005 yet). I will of course run this customization by the vendor for approval before actually making a change like this but I'd like to know in advance if anyone knows how if any effect this would have in existing Inserts & Updates. If the column is computed would it interfere with any existing insert or update?
Thanks
Kindest Regards,
Just say No to Facebook!April 22, 2009 at 2:08 pm
It shouldn't have any affect. In fact, if you try to insert or update a computed column sql throws an exception. The one potential issue is that if the formatting logic is kind of slow it might slow down the app if the data is retrieved via select *.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 2:12 pm
I wouldn't be quite so optimistic. If the inserts being done look something like
insert tableA
select * from TableB
(i.e with no specific names on the destination), altering that table could have devastating effects. Most likely you would get errors due to the # of columns being different, but you could also end up with columns being reordered (so data wouldn't be getting inserted into the columns they used to be in).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 22, 2009 at 2:13 pm
hence my comment about select * (and yet another reason it should not be used)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 2:24 pm
slange (4/22/2009)
It shouldn't have any affect. In fact, if you try to insert or update a computed column sql throws an exception. The one potential issue is that if the formatting logic is kind of slow it might slow down the app if the data is retrieved via select *.
slange (4/22/2009)
hence my comment about select * (and yet another reason it should not be used) .
Slange - I believe Matt posted what he did because your intial reply read like you were saying the addition of the computed column would have no effect at all meaning it wouldn't change how any existing DML would function after the addition of the column. What you mean though was that it would of the effect of causing nothing to work, that sound about right?
SELECT * - I can definately see this being a problem and while I'm not saying this as a justification fro why it should be Ok to add the computed column but isn't the use of SELECT * as a source for a table insert in a production DB considered BAD, VERY-VERY BAD PRACTICES?
Thanks for posting!
Kindest Regards,
Just say No to Facebook!April 22, 2009 at 2:29 pm
Good point YSLGuru. I (once again) assume that a production environment would not use things like select * expecially in an insert. I certainly understand his point and agree whole heartedly. 😉
I think we all agree that select * is a painfully bad thing and can make things like this extremely painful.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 2:41 pm
Another thing to think about...
In the other thread, you had mentioned that you'll do a bunch of data retrieval operations through a view.
If the view is coded with a select *, then you can through things off unless you rebuild the view.
Since this db is a vendor db, is their front-end application doing any selects like that? It could cause problems in the front-end also. If all of their data access is through stored procedures, then your search just became a lot easier... you won't have to dig through a bunch of application code or run traces to see what the application is doing. Since your vendor is still on 2000, I wouldn't be too confident in what they're doing. They aren't supporting 2005 most likely because their app doesn't (yet) work on it.
The updates and deletes shouldn't be a problem. The issues will be with selects that do a select *. Inserts that do a "insert into table select * from anothertable" can cause a problem. Another potential issue is if there are any "insert/exec" statements anywhere.
Jeff's suggestion is an excellent one, but you need to exercise due deligence to ensure that you won't break something in implementing it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2009 at 3:17 pm
One way you might be able to get around problems with adding columns to tables is to create a view with the computed column in it and index the view, and use that view in the future selects that include that column.
That will depend on your edition of SQL Server, and the indexability of the column. Some computed columns can be indexed, some can't. The rules for indexing are the same as the rules for persisting a computed column, so if you can't index it, you can't persist it, and then it returns to being a runtime calculation anyway.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2009 at 3:36 pm
WayneS (4/22/2009)
Another thing to think about...In the other thread, you had mentioned that you'll do a bunch of data retrieval operations through a view.
If the view is coded with a select *, then you can through things off unless you rebuild the view.
Since this db is a vendor db, is their front-end application doing any selects like that? It could cause problems in the front-end also. If all of their data access is through stored procedures, then your search just became a lot easier... you won't have to dig through a bunch of application code or run traces to see what the application is doing. Since your vendor is still on 2000, I wouldn't be too confident in what they're doing. They aren't supporting 2005 most likely because their app doesn't (yet) work on it.
The updates and deletes shouldn't be a problem. The issues will be with selects that do a select *. Inserts that do a "insert into table select * from anothertable" can cause a problem. Another potential issue is if there are any "insert/exec" statements anywhere.
Jeff's suggestion is an excellent one, but you need to exercise due deligence to ensure that you won't break something in implementing it.
Well said. Sadly enough I would be tickled pink-{insert yet to be discovered color} if the vendor where doing the INSERT/UPDATE commands thru an SP but atlas the SP's in the DB are primarily for reporting purposes.
AS for the evil red-headed step child of SQL and it's mistress, also know as 'SELECT *' , I can't say with certainity that this doesn't occur but I have yet to see it show up when profiling/tracing the activities between the DB & The front end app. That being said the vendor is staffed mainly by procedural only programers and if they have any true DB or DBA like person on staff besides the Oracle person they had a few years back I would be highly suprised. And even if they did he/she would probably NOT have any involvement in development and used primarily as an in house DBA to manage tehir own server farm and not to provide solid DB expereince in the design of the DB. Heavy Sigh
Kindest Regards,
Just say No to Facebook!April 22, 2009 at 3:40 pm
GSquared (4/22/2009)
One way you might be able to get around problems with adding columns to tables is to create a view with the computed column in it and index the view, and use that view in the future selects that include that column.That will depend on your edition of SQL Server, and the indexability of the column. Some computed columns can be indexed, some can't. The rules for indexing are the same as the rules for persisting a computed column, so if you can't index it, you can't persist it, and then it returns to being a runtime calculation anyway.
This is a great idea and I appreciate it your posting back but I don't believe we can do this because of another FEATURE [/size]in the product; it automatically sets ANSI_NULLS to OFF upon connection to the DB and Indexed Views as far as I know require that this be set to ON or any any DML will fail. Now if that's only partially correct and this being set to OFF will only prevent UPDATES, DELETES & INSERTS when they are done thru the view and not affect SELECTs done against the view then this might be a solution.
Please advise.
Thanks
Kindest Regards,
Just say No to Facebook!April 23, 2009 at 6:51 am
YSLGuru (4/22/2009)
GSquared (4/22/2009)
One way you might be able to get around problems with adding columns to tables is to create a view with the computed column in it and index the view, and use that view in the future selects that include that column.That will depend on your edition of SQL Server, and the indexability of the column. Some computed columns can be indexed, some can't. The rules for indexing are the same as the rules for persisting a computed column, so if you can't index it, you can't persist it, and then it returns to being a runtime calculation anyway.
This is a great idea and I appreciate it your posting back but I don't believe we can do this because of another FEATURE [/size]in the product; it automatically sets ANSI_NULLS to OFF upon connection to the DB and Indexed Views as far as I know require that this be set to ON or any any DML will fail. Now if that's only partially correct and this being set to OFF will only prevent UPDATES, DELETES & INSERTS when they are done thru the view and not affect SELECTs done against the view then this might be a solution.
Please advise.
Thanks
Nope. That pretty much rules out using an indexed view.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply