Find the brackets

Question

Find the brackets

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

Laurie Dunn (11/17/2016)
This would also work and keep the code simple...
SELECT *
FROM dbo.MyData
WHERE Left(mychar,1)='[';

Nice alternative, thanks.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

Jeff Moden (11/16/2016)
IMHO, cleaner code if you take the shortcut.
SELECT mychar FROM mydata WHERE mychar LIKE '[[]%'

I like your shortcut. It seems easier to understand.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Iwas Bornready (11/21/2016)
Laurie Dunn (11/17/2016)
This would also work and keep the code simple...
SELECT *
FROM dbo.MyData
WHERE Left(mychar,1)='[';
Nice alternative, thanks.

It could be a huge killer of performance and use a large number of unnecessary resources because it's a non-SARGable query that will force, at the very least, an Index Scan instead of an Index Seek. If the number of rows expected to start with a '[' is small compared to the actual row count of the index, it can be quite the waste. It's a bad habit to get into.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

TomThomson (11/18/2016)
Jeff Moden (11/17/2016)
Stewart "Arturius" Campbell (11/17/2016)
Another cause of disparity are scalar functions, which the execution plan appears to ignore completely.
Agreed. In fact, those little beasties can be downright troublesome to measure for performance. Although I agree that they should be generally avoided, it shouldn't be because of SET STATISICS measurements against them because SET STATISTICS can make them look hundreds of times worse than they actually are.
For more on that nuance, please see the following article.
[font="Arial Black"]How to Make Scalar UDFs Run Faster (SQL Spackle)
[/font][/url]
Your reminder triggered me to take another look at this and the results I got make me think both that SET STATISTICS doesn't behave as badly in SQL Server 2016 as it did in SQL Server 2005 and that scalar UDFs are more of a performance hit (at least on simple stuff like doubling) on SS 2016 than on SS 2005. But probably my measurements on an old laptop with Windows 10 Home aren't very relevant to serious SQL Server usage.

Oh my. Thanks for checking, Tom. I hope your findings are incorrect but I know how you are with this type of thing. I don't have 2016 loaded anywhere so I hope someone will step up and confirm or deny with the kind of testing I know you did.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Nice question, interesting discussion.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

adman3613 SSC Veteran Points: 273 More actions · Answer 6

Im not sure i understand your use of like

i get the like '[ and even the wild card % but not the inclusion of the additional set of brackets []between them .

im looking for instances where the open bracket is the first character so i see the use of say

like '[%'

what am i missing

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

adman3613 (11/22/2016)
Im not sure i understand your use of like
i get the like '[ and even the wild card % but not the inclusion of the additional set of brackets []between them .
im looking for instances where the open bracket is the first character so i see the use of say
like '[%'
what am i missing

Not sure to whom you speak but see the following URL. You should read the whole article but, to answer your question, look for the sections "Arguments" (where you find out that brackets are used for wildcarding characters), "Using Wildcard Characters As Literals", and "Pattern Matching with the ESCAPE Clause".

https://msdn.microsoft.com/en-us/library/ms179859.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adman3613 SSC Veteran Points: 273 More actions · Answer 8

now i see its not like '[ then [] its actually like '[] with the [ inside the brackets .

thanks for the article ... ive used the brackets for ranges but didnt realize the inorder to look at the open bracket [ i needed to include it in its own brackets

cheers

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 9

Jeff Moden (11/16/2016)
IMHO, cleaner code if you take the shortcut.
SELECT mychar FROM mydata WHERE mychar LIKE '[[]%'

Got this wrong because I use the above statement instead.

dstrickrott SSCommitted Points: 1951 More actions · Answer 10

dstrickrott

SSCommitted

Points: 1951

November 22, 2016 at 2:10 pm

#1914123

Thanks for the fun question Steve.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

adman3613 (11/22/2016)
now i see its not like '[ then [] its actually like '[] with the [ inside the brackets .
thanks for the article ... ive used the brackets for ranges but didnt realize the inorder to look at the open bracket [ i needed to include it in its own brackets
cheers

Correct. And thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)