<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Jeff Moden
»
Tally OH! An Improved SQL 8K “CSV Splitter”...
549 posts, Page 54 of 55
««
«
51
52
53
54
55
»»
Tally OH! An Improved SQL 8K “CSV Splitter” Function
Rate Topic
Display Mode
Topic Options
Author
Message
Usman Butt
Usman Butt
Posted Friday, February 22, 2013 1:24 PM
SSC-Addicted
Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:00 AM
Points: 444,
Visits: 807
mister.magoo (2/22/2013)
Usman Butt (2/22/2013)
L' Eomot Inversé (2/22/2013)
mister.magoo (2/22/2013)
What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"?
Always makes me nervous when that is the case...
That feeling was bothering me too - I can't understand why I didn't spot the opportunity the first time I looked at the code, let alone why Jeff or Paul or another of the top experts who contribute so much to SQLServerCentral didn't do it long ago.
Looks as if you're the only one who's awake around here, Mr M.
IIRC, Jeff Moden already knows it. But I guess the conversation we had somewhere else was something like that he likes to use the default collation. (I may be wrong. So I sincerely apologize in advance in case I said anything/everything wrong).
But in my case, the binary collated splitter version is already in production for comma delimeted strings
And to supplement my earlier post, please read posts from
here
onwards. I guess both of our top experts Jeff Moden and Paul White already knows it very well?
Post #1423239
L' Eomot Inversé
L' Eomot Inversé
Posted Friday, February 22, 2013 2:02 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 12:25 PM
Points: 7,110,
Visits: 7,184
Usman Butt (2/22/2013)
And to supplement my earlier post, please read posts from
here
onwards. I guess both of our top experts Jeff Moden and Paul White already knows it very well?
Ouch. Less than 6 months ago and I already forgot!
Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.
Post #1423260
peter-757102
peter-757102
Posted Tuesday, February 26, 2013 3:48 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:57 AM
Points: 288,
Visits: 1,903
Using a binary comparisons is a good speedup for specific cases and good to be aware of
and certainly worth mentioning in bold
with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.
Post #1423980
ChrisM@Work
ChrisM@Work
Posted Tuesday, February 26, 2013 3:53 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618,
Visits: 10,990
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of
and certainly worth mentioning in bold
with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.
The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation
will
retain the new collation cast and could affect subsequent operations.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”
- Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read
this article
.
Understanding and using APPLY, (I)
and
(II)
Paul White
Hidden RBAR: Triangular Joins
/
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Jeff Moden
Exploring Recursive CTEs by Example
Dwain Camps
Post #1423985
dwain.c
dwain.c
Posted Tuesday, February 26, 2013 4:32 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346,
Visits: 3,192
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of
and certainly worth mentioning in bold
with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.
The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation
will
retain the new collation cast and could affect subsequent operations.
Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?
I always use Latin1_General_BIN and now I'm not sure if that is CS or CI!
No loops! No CURSORs! No RBAR! Hoo-uh!
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?
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1423998
ChrisM@Work
ChrisM@Work
Posted Tuesday, February 26, 2013 4:46 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618,
Visits: 10,990
dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of
and certainly worth mentioning in bold
with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.
The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation
will
retain the new collation cast and could affect subsequent operations.
Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?
I always use Latin1_General_BIN and now I'm not sure if that is CS or CI!
It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:
Latin1_General_BIN_:s
Latin1_General_BIN_:]
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”
- Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read
this article
.
Understanding and using APPLY, (I)
and
(II)
Paul White
Hidden RBAR: Triangular Joins
/
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Jeff Moden
Exploring Recursive CTEs by Example
Dwain Camps
Post #1424004
mister.magoo
mister.magoo
Posted Tuesday, February 26, 2013 5:11 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 1,308,
Visits: 3,899
ChrisM@Work (2/26/2013)
dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of
and certainly worth mentioning in bold
with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.
The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation
will
retain the new collation cast and could affect subsequent operations.
Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?
I always use Latin1_General_BIN and now I'm not sure if that is CS or CI!
It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:
Latin1_General_BIN_:s
Latin1_General_BIN_:]
Surely you mean that Latin1_General_BIN
is
case sensitive (CS)
and
accent sensitive (AS) ?
Binary collation will definitely find 'a' and 'A' to be different and 'á' to be different to 'a'.
That is part of the reason it is quicker for CHARINDEX and equality testing - it doesn't have to compare 'a' to 'A','á','Á' etc... it is either 'a' or NOT 'a'.
I do agree that it is smirk sensitive though :o) != :O)
MM
Post #1424015
ChrisM@Work
ChrisM@Work
Posted Tuesday, February 26, 2013 5:15 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618,
Visits: 10,990
mister.magoo (2/26/2013)
ChrisM@Work (2/26/2013)
dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of
and certainly worth mentioning in bold
with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.
The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation
will
retain the new collation cast and could affect subsequent operations.
Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?
I always use Latin1_General_BIN and now I'm not sure if that is CS or CI!
It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:
Latin1_General_BIN_:s
Latin1_General_BIN_:]
Surely you mean that Latin1_General_BIN
is
case sensitive (CS)
and
accent sensitive (AS) ?
Binary collation will definitely find 'a' and 'A' to be different and 'á' to be different to 'a'.
That is part of the reason it is quicker for CHARINDEX and equality testing - it doesn't have to compare 'a' to 'A','á','Á' etc... it is either 'a' or NOT 'a'.
I do agree that it is smirk sensitive though :o) != :O)
Oops! Smirk in me eyes
yes of course. "binary" is a bit of a giveaway.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”
- Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read
this article
.
Understanding and using APPLY, (I)
and
(II)
Paul White
Hidden RBAR: Triangular Joins
/
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Jeff Moden
Exploring Recursive CTEs by Example
Dwain Camps
Post #1424019
dwain.c
dwain.c
Posted Tuesday, February 26, 2013 5:12 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346,
Visits: 3,192
ChrisM@Work (2/26/2013)
mister.magoo (2/26/2013)
ChrisM@Work (2/26/2013)
dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of
and certainly worth mentioning in bold
with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.
The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation
will
retain the new collation cast and could affect subsequent operations.
Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?
I always use Latin1_General_BIN and now I'm not sure if that is CS or CI!
It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:
Latin1_General_BIN_:s
Latin1_General_BIN_:]
Surely you mean that Latin1_General_BIN
is
case sensitive (CS)
and
accent sensitive (AS) ?
Binary collation will definitely find 'a' and 'A' to be different and 'á' to be different to 'a'.
That is part of the reason it is quicker for CHARINDEX and equality testing - it doesn't have to compare 'a' to 'A','á','Á' etc... it is either 'a' or NOT 'a'.
I do agree that it is smirk sensitive though :o) != :O)
Oops! Smirk in me eyes
yes of course. "binary" is a bit of a giveaway.
Doh! (Homer Simpson moment)
No loops! No CURSORs! No RBAR! Hoo-uh!
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?
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1424288
ChrisM@Work
ChrisM@Work
Posted Wednesday, February 27, 2013 1:51 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618,
Visits: 10,990
dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
mister.magoo (2/26/2013)
ChrisM@Work (2/26/2013)
dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of
and certainly worth mentioning in bold
with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.
The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation
will
retain the new collation cast and could affect subsequent operations.
Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?
I always use Latin1_General_BIN and now I'm not sure if that is CS or CI!
It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:
Latin1_General_BIN_:s
Latin1_General_BIN_:]
Surely you mean that Latin1_General_BIN
is
case sensitive (CS)
and
accent sensitive (AS) ?
Binary collation will definitely find 'a' and 'A' to be different and 'á' to be different to 'a'.
That is part of the reason it is quicker for CHARINDEX and equality testing - it doesn't have to compare 'a' to 'A','á','Á' etc... it is either 'a' or NOT 'a'.
I do agree that it is smirk sensitive though :o) != :O)
Oops! Smirk in me eyes
yes of course. "binary" is a bit of a giveaway.
Doh! (Homer Simpson moment)
About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”
- Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read
this article
.
Understanding and using APPLY, (I)
and
(II)
Paul White
Hidden RBAR: Triangular Joins
/
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Jeff Moden
Exploring Recursive CTEs by Example
Dwain Camps
Post #1424397
« Prev Topic
|
Next Topic »
549 posts, Page 54 of 55
««
«
51
52
53
54
55
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
