September 5, 2012 at 9:19 am
Yea I was surprised as well. I am not near my system, but if you can create one table and try the script on SQL Server 2008 to just check the script that would be great. Thanks so much for your help!
September 5, 2012 at 9:27 am
The update statement does not delete data. It simply updates the data as you tell it to. If it deleted data from your table it is because you did something wrong with the statement.
September 5, 2012 at 9:29 am
Lynn Pettis (9/5/2012)
The update statement does not delete data. It simply updates the data as you tell it to. If it deleted data from your table it is because you did something wrong with the statement.
Not really something wrong with an update but a different statement entirely. If it set your values to '' that is ENTIRELY different than deleting the data. Deleting means the row(s) are removed from the database and that is impossible with an update statement.
_______________________________________________________________
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/
September 5, 2012 at 9:33 am
Sean - SELECT state FROM state.state.state.state WHERE state = @state ORDER BY state;
Could be perfectly valid :ermm: (even if it should be a sackable offence) and reminds me a little of this:
http://en.wikipedia.org/wiki/Buffalo_buffalo_Buffalo_buffalo_buffalo_buffalo_Buffalo_buffalo
---
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
September 5, 2012 at 10:04 am
derek.colley (9/5/2012)
Sean - SELECT state FROM state.state.state.state WHERE state = @state ORDER BY state;Could be perfectly valid :ermm: (even if it should be a sackable offence) and reminds me a little of this:
http://en.wikipedia.org/wiki/Buffalo_buffalo_Buffalo_buffalo_buffalo_buffalo_Buffalo_buffalo
---
Yes it is valid for two things...a horrible naming convention and a pink slip. The ridiculousness of my example was to show just exactly how ridiculous it is. And I use that buffalo page sometimes myself when discussions about ambiguity come up. 🙂
_______________________________________________________________
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/
September 5, 2012 at 12:41 pm
You should use a trigger to trim the data during INSERTs and UPDATEs -- NEVER let poorly formatted data be queried directly from the database.
Personally I don't see anything wrong with the table name -- it is an accurate business description of the data. Sure, it's much better not to use reserved words, but it's not a capital crime. Simply bracket the names appropriately as needed.
As to singular vs plural table names, plural names are more common, but to me the key thing is consistency within your database. If other table names are singular, use [state] as the table name, not states.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 5, 2012 at 12:42 pm
Edit: Removed, repeat.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 5, 2012 at 12:55 pm
You should use a trigger to trim the data during INSERTs and UPDATEs
[/quote]
I don't think I agree that you should just add a trigger to trim spaces but we can agree to disagree here.
NEVER let poorly formatted data be queried directly from the database.
I am not sure I quite understand what you are saying here. How else could you get poorly formatted data out? Also there are VERY few absolutes in any aspect of SQL server and your above stated rule is certainly not one of them.
_______________________________________________________________
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/
September 5, 2012 at 2:51 pm
Sean Lange (9/5/2012)
I don't think I agree that you should just add a trigger to trim spaces but we can agree to disagree here.
To me that is vastly better than using LTRIM/RTRIM on all subsequent queries!
NEVER let poorly formatted data be queried directly from the database.
I am not sure I quite understand what you are saying here. How else could you get poorly formatted data out? Also there are VERY few absolutes in any aspect of SQL server and your above stated rule is certainly not one of them.
If you intend to LTRIM/RTRIM the data prior to use anyway, why EVER make the data available in an improper format? Preventing that is an absolute to me. There's no valid reason to give variable and/or false results when you can easily avoid it.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 5, 2012 at 3:09 pm
To me that is vastly better than using LTRIM/RTRIM on all subsequent queries!
You certainly have a valid point. Not sure that adding an insert/update trigger on every table is the best solution to this though.
If you intend to LTRIM/RTRIM the data prior to use anyway, why EVER make the data available in an improper format? Preventing that is an absolute to me. There's no valid reason to give variable and/or false results when you can easily avoid it.
I guess I was splitting hairs, perhaps it was just the wording. I agree that we should do what we can to prevent garbage data going in.
Of course this whole thread and discussion is talking about a states table. Something that should be a table in every database that needs state information. And is doesn't need to be changed, well at least not in the last 50 years.
_______________________________________________________________
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/
September 5, 2012 at 3:14 pm
Sean Lange (9/5/2012)
To me that is vastly better than using LTRIM/RTRIM on all subsequent queries!
You certainly have a valid point. Not sure that adding an insert/update trigger on every table is the best solution to this though.
If you intend to LTRIM/RTRIM the data prior to use anyway, why EVER make the data available in an improper format? Preventing that is an absolute to me. There's no valid reason to give variable and/or false results when you can easily avoid it.
I guess I was splitting hairs, perhaps it was just the wording. I agree that we should do what we can to prevent garbage data going in.
Of course this whole thread and discussion is talking about a states table. Something that should be a table in every database that needs state information. And is doesn't need to be changed, well at least not in the last 50 years.
What method is better than a trigger for this type of data change??
A static table has all the more reason to correct the data ONCE, as it goes into the table, rather than re-correcting the data EVERY TIME IT'S QUERIED -- that's just asking for problems including inconsistent results.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 5, 2012 at 3:20 pm
ScottPletcher (9/5/2012)
Sean Lange (9/5/2012)
To me that is vastly better than using LTRIM/RTRIM on all subsequent queries!
You certainly have a valid point. Not sure that adding an insert/update trigger on every table is the best solution to this though.
If you intend to LTRIM/RTRIM the data prior to use anyway, why EVER make the data available in an improper format? Preventing that is an absolute to me. There's no valid reason to give variable and/or false results when you can easily avoid it.
I guess I was splitting hairs, perhaps it was just the wording. I agree that we should do what we can to prevent garbage data going in.
Of course this whole thread and discussion is talking about a states table. Something that should be a table in every database that needs state information. And is doesn't need to be changed, well at least not in the last 50 years.
What method is better than a trigger for this type of data change??
A static table has all the more reason to correct the data ONCE, as it goes into the table, rather than re-correcting the data EVERY TIME IT'S QUERIED -- that's just asking for problems including inconsistent results.
I agree with you. The point I am making is this is a static table. It doesn't need a trigger because the data doesn't change, it just needs to be cleaned up.
_______________________________________________________________
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/
September 5, 2012 at 3:49 pm
It doesn't need a trigger because the data doesn't change, it just needs to be cleaned up.
But the trigger DOES the clean up, which guarantees that (1) it occurs and (2) it occurs before anyone can read it (except for dirty reads, of course).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 6, 2012 at 7:10 am
ScottPletcher (9/5/2012)
It doesn't need a trigger because the data doesn't change, it just needs to be cleaned up.
But the trigger DOES the clean up, which guarantees that (1) it occurs and (2) it occurs before anyone can read it (except for dirty reads, of course).
Well I would have to argue to a point. The trigger will only do the cleanup when something happens. It isn't going to fix the crap data that is already there and that is the problem the OP has. It would do a nice job of it on any subsequent insert/updates but as we have already discussed this shouldn't happen because the states don't change too frequently. 😀
_______________________________________________________________
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/
September 6, 2012 at 7:29 am
Surely there's an argument here that string validation (to take one example) should be handled app-side anyway, so that your table only ever receives clean data?
If not, and validation must be done database-side, If you're using a stored procedure to insert the data, and the app is calling the SP, the stored procedure COULD use functions like LTRIM and RTRIM to tidy up the data before entry. I would have thought this would be a function alongside existing validation criteria (like, e.g., removing restricted characters and checking the length of the input string).
The idea of using a trigger to clean data on INSERT to the database seems OK but a bit cumbersome tbh. Surely vulnerable to injection too, what if the string was
" abnormal string ';DISABLE TRIGGER ALL ON DATABASE; --" ?
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply