A Function Gotcha with the Use of an Asterisk

Question

A Function Gotcha with the Use of an Asterisk

Viewing 15 posts - 76 through 90 (of 151 total)

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

nareshparajuli Newbie Points: 8 More actions · Answer 1

using SSMS right click on the table name then script table as -> then select to ->new query editor window, instead of using excel

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 2

Type {()} into the 'Find What' box and type '\1', into the 'Replace With' box. (Please type both of these as I've pasted them, unless you know what you are doing with regexes)

Charles,

The regex option is a valuable tool to use when editing for sure in SSMS ide. I use them as much as I can when editing. But you will find it can be rather quirky when you run it at times. Just when you think you have the regex working right it encounters some string that doesn't work right. Or when you do a replace 1 at a time it works fine and then it will function slightly different when you choose to do it on a whole selection or the entire document. Thus editing it improperly.

The one that you gave will have issues with a list of filenames like:

text1.txt

text2.txt

text3.txt

It will create:

,'text1'.,'txt'

,'text2'.,'txt'

,'text3'.,'txt'

Probably not what was intended. You would need this:

{()}

But then there will be other things it will encounter like % character or what have you that it won't find. Actually getting an expression to do this correctly with any string data is a little tricky I think (in the SSMS IDE that is).

Don Rueter SSC Rookie Points: 27 More actions · Answer 3

I haven't read all the posts--sorry if I'm repeating. But an easy way in Management Studio to change your "Select *" to "Select [field names]" is to simply use the mouse to highlight your entire query, and then click the Query Editor button in the toolbar. Voila--the troublesome * is expanded.

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 4

Adam Gojdas (9/11/2009)
Charles,
...
Probably not what was intended. You would need this:
{()}
...

^(.+)$ is what's wanted.

Or, you could use an editor that has a 'Copy data as IN Clause' option...

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 5

^(.+)$ is what's wanted.
Or, you could use an editor that has a 'Copy data as IN Clause' option...

That will not work, at least in my SSMS ide. Try it.

It will give you this if you select them and do a selection replace:

text1.txt

,''

Or this if you do the entire document replace:

,''

That is why I think regex is a little quirky in SSMS editor.

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 6

Adam Gojdas (9/11/2009)That will not work, at least in my SSMS ide. Try it.

I did - rule #1 of posting 🙂 I didn't do it with a selection, however.

If you're using a selection, you can just get rid of the start / end of line zero width assertions: (.+)

It doesn't do very well at keeping the same text selected afterwards though.

edit -> oh, and I used \0 as the replacement, because the regex will fully capture what you want to replace, you don't need to use a capture group unless you want to replace with a capture sub-part of the match.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 7

Matt Whitfield (9/11/2009)
Adam Gojdas (9/11/2009)That will not work, at least in my SSMS ide. Try it.
I did - rule #1 of posting 🙂 I didn't do it with a selection, however.
If you're using a selection, you can just get rid of the start / end of line zero width assertions: (.+)
It doesn't do very well at keeping the same text selected afterwards though.
edit -> oh, and I used \0 as the replacement, because the regex will fully capture what you want to replace, you don't need to use a capture group unless you want to replace with a capture sub-part of the match.

Nice. That is probably why I see some of the quirkiness I do when using similar expressions. I should use \0.

Still though I think there are some issues with the regex in the SSMS and Visual Studio IDEs...but it may just be user related (me).

thanks

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 8

Adam Gojdas (9/11/2009)
Still though I think there are some issues with the regex in the SSMS and Visual Studio IDEs...but it may just be user related (me).

Gotta love it really though.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Paul White SSC Guru Points: 150467 More actions · Answer 9

As Mark & 'kll' have mentioned, this also applies to views.

Using the star construction is well-established as poor practice (except after EXISTS or with COUNT) for exactly the sorts of reasons highlighted in the article.

There is really no downside to explicitly naming the columns. In fact you can't schema bind a function without doing that.

So, use SCHEMABINDING whenever possible* - it will also force you to schema-qualify object names, another defensive programming tip.

Paul

* ...even if there is no schema to bind to - scalar functions will be evaluated for determinism when schema bound; if you don't use SCHEMABINDING, the engine assumes you are doing data access from the function. This can really hurt performance, especially in UPDATE plans, since the QO must add halloween protection.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

cbarber Newbie Points: 7 More actions · Answer 10

I ran into this with views many times. I've been burned too many times by my own "lazy" coding in views with select * when table definitions change! I don't do that any more, but it's really great to see a proper explanation of why this effect occurs.

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 11

Perhaps I've missed something but this is a bug in SQL Server, right?

Sad to see that Select-*-bashing has reached a point where genuine bugs aren't called bugs anymore if they involve the use of Select-*...

Still, a good article and fine analysis of the bug. Should make it easier for Microsoft to fix it!

Paul White SSC Guru Points: 150467 More actions · Answer 12

Alex (9/13/2009)
Perhaps I've missed something but this is a bug in SQL Server, right?
Sad to see that Select-*-bashing has reached a point where genuine bugs aren't called bugs anymore if they involve the use of Select-*...
Still, a good article and fine analysis of the bug. Should make it easier for Microsoft to fix it!

It is clearly documented behaviour.

SCHEMABINDING is the way to avoid it.

See sp_refreshsqlmodule.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 13

Paul White (9/13/2009)
It is clearly documented behaviour.

I'm not sure that 'in the description of a system stored procedure which 97% of sql server users have never heard of' would necessarily count as 'clear' for me... 🙂

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Paul White SSC Guru Points: 150467 More actions · Answer 14

Matt Whitfield (9/13/2009)
Paul White (9/13/2009)
It is clearly documented behaviour.
I'm not sure that 'in the description of a system stored procedure which 97% of sql server users have never heard of' would necessarily count as 'clear' for me... 🙂

It's not exactly secret in Books Online:

sys.sql_dependencies

Understanding SQL Dependencies

Anyway, I was responding to the suggestion that the behaviour is a bug that needs fixing. It isn't.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 15

The second link didn't seem to contain any reference to it?

I still wouldn't necessarily put the 'bug' to bed that easily though. If your bank 'clearly documented' the fact that if your bank balance ended with a 3 on the third friday of every month then they would zero your account, that wouldn't necessarily make it correct, would it? While an extreme example, it's a very non-obvious behaviour. If I was selling that software, and a user came to me with a bug, i'd feel pretty tight explaining it away as 'documented behaviour'.

Just my personal point of view, of course.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone