Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Searching for case sensitive data Expand / Collapse
Author
Message
Posted Thursday, April 16, 2009 7:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606

By the way, my compliments on the Tally thread. It has been fascinating to follow. I haven't thought of any alternatives worth adding to the thread, but it has been thought provoking and educational all the same. I haven't seen such substantive debate going on in any forum outside SSC. Congratulations!



Do you mind posting the link to this thread. I must have missed it.
Thanks.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #698411
Posted Thursday, April 16, 2009 7:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 4,013, Visits: 6,087
Somehow users managed to input some of the customer order numbers as lower case


Looks like they tried and failed, Jack. The counter-argument would be that if that column used an uppercase-only collation (or added UPPER to all procs that do inserts), then it wouldn't matter if the application code was broken. I know scrubbing is more of a load on the SQL server, but enforcing data integrity is a primary role of a database.




__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #698425
Posted Thursday, April 16, 2009 7:45 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 4,013, Visits: 6,087
Do you mind posting the link to this thread. I must have missed it.


Here you go. Flo stirred up quite a debate by identifying a situation where RBAR using CLR was outperforming the set-based solution.

Performance issue with tally solution


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #698430
Posted Thursday, April 16, 2009 7:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
Thanks a bunch.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #698435
Posted Thursday, April 16, 2009 11:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
Jack Corbett (4/16/2009)
Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.

Never thought I would ever have a different opinion than you, but never say never.
I strongly advise to move this task to the DB side. Thats why we have constraints within the DB as well. We don't trust the application by definition

(It could also be that I misunderstood you and you meant to only validate the UPPER case in the DB, but UPPER the data itself in the application)


Best Regards,
Chris Büttner
Post #698742
Posted Thursday, April 16, 2009 1:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Christian Buettner (4/16/2009)
Jack Corbett (4/16/2009)
Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.

Never thought I would ever have a different opinion than you, but never say never.
I strongly advise to move this task to the DB side. Thats why we have constraints within the DB as well. We don't trust the application by definition

(It could also be that I misunderstood you and you meant to only validate the UPPER case in the DB, but UPPER the data itself in the application)


Bob Hovious (4/16/2009)
Somehow users managed to input some of the customer order numbers as lower case


Looks like they tried and failed, Jack. The counter-argument would be that if that column used an uppercase-only collation (or added UPPER to all procs that do inserts), then it wouldn't matter if the application code was broken. I know scrubbing is more of a load on the SQL server, but enforcing data integrity is a primary role of a database.


I figured I'd hit both of these in one post.

Christian,

I'm not sure we disagree. The point I was trying to make, and obviously not clearly, is that a business rule should be enforced in the application so that you do not "waste" a round-trip to the DB with invalid data. Would I want to put some kind of validation in the database, too? Yes. I'd probably use UPPER in my insert/update procedures. If you aren't using stored procedures, but an ORM tool or ad-hoc SQL, you'd have to use an INSTEAD OF trigger in the database to make sure the data was inserted properly and apply the UPPER, otherwise it still has to be done in the application. Ideally you would use a case-sensitive collation for the column and a check constraint.

Bob,

I missed the part of the post you quoted, which causes us to assume that the application IS supposed to be validating the data. As I said above, that doesn't mean I wouldn't validate in the database as well, mainly to protect the database from some ad hoc update by me or another DBA. Is there an upper case only collation? I know you can do case-sensitive, but didn't know you could require one case.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #698816
Posted Thursday, April 16, 2009 2:04 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 4,013, Visits: 6,087
Hey Jack:

I think we're on the same page. I would never advocate that front-end editing be abandoned in favor of ONLY bouncing errors back from the server, but I would rather have redundant checks, and an occasional bounce, than allow data in that breaks a business rule. I've seen what happens with DBs created by developers who don't like constraints, or even foreign keys, because they "interfere with flexibility." (No, I don't think you would ever do this.)

Let me clarify the "upper case collation". Bad choice of words on my part. Every time I get in a hurry I lose precision. I assumed (possibly in error) that the order numbers coming in are validated against an orders table. Using a case-sensitive collation would make the lower-case 'cqs' strings fail. (Of course, it might also cause unexpected duplicates, depending on how the applications are written.)

The point was and is, if keeping those lower case values out is critical, steps can and should be taken to prevent it at the db level. Even if it's only changing the insert procedure to store UPPER(custOrderNo) instead of custOrderNo. In this case, it sounds as if it were more of an annoyance than a show-stopper.

Regards,

Bob




__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #698874
Posted Thursday, April 16, 2009 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
I didn't really think we disagreed, I understood what you meant, and I wanted to make sure that you, and everyone else, understood what I meant. I also was trying to be quick and did not fully explain, or probably think through thoroughly, what I meant.

You and Christian are among the folks I respect on SSC (fortunately a growing group) so I wanted to make sure I clarified. I didn't want you to think I was a fool.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #698882
Posted Thursday, April 16, 2009 2:34 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 4,013, Visits: 6,087
I'm not one to throw stones... especially where the word "fool" is involved

By the way, the respect is returned.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #698904
Posted Friday, April 17, 2009 12:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
Jack Corbett (4/16/2009)
You and Christian are among the folks I respect on SSC (fortunately a growing group) so I wanted to make sure I clarified.

Now that makes me feel honored

I didn't want you to think I was a fool.

Never would. And just to repeat - this "would have been" the first time I disagreed.
So if you were a fool, then I would be one as well (because I mostly (99,9%) agree with what you say). But now don't get the feeling that you are on the safe side, since I actually may be a fool (and sometimes I definitively am - just cannot get this human behaviour out of myself)


Best Regards,
Chris Büttner
Post #699134
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse