Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 57,016 through 57,030 (of 66,712 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Michael L John (1/3/2017)
Just putting the finishing touches on a new reporting server.
I run this old legacy ETL process that nobody knows about, and nobody remembers, when this error occurs:
Msg 4121, Level 16, State 1, Procedure ETL_sp_Source_Dimensions_AT, Line 1168 [Batch Start Line 0]
Cannot find either column "Master" or the user-defined function or aggregate "Master.dbo.fn_WorkDays", or the name is ambiguous.
Msg 4121, Level 16, State 1, Procedure ETL_sp_Update_Offline_Data, Line 57 [Batch Start Line 0]
Cannot find either column "Master" or the user-defined function or aggregate "Master.dbo.fn_WorkDays", or the name is ambiguous.
What? Why would they create this function in master? It's probably loops and cursors and lots of other bad stuff.
I open the function, and this appears in the comments:
Revisions:
Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
*
Well, no loops here!

I'm guessing that this is the function: http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

I'm also guessing that Jeff would suggest to stop using that function and change it into an iTVF.:-D

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 736188 More actions · Answer 2

ICYMI: SQL in the City Streamed playlist: https://www.youtube.com/playlist?list=PLhFdCK734P8DvVfg9O_csDlxp7jeyAsXV

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

Luis Cazares (1/3/2017)
Michael L John (1/3/2017)
Just putting the finishing touches on a new reporting server.
I run this old legacy ETL process that nobody knows about, and nobody remembers, when this error occurs:
Msg 4121, Level 16, State 1, Procedure ETL_sp_Source_Dimensions_AT, Line 1168 [Batch Start Line 0]
Cannot find either column "Master" or the user-defined function or aggregate "Master.dbo.fn_WorkDays", or the name is ambiguous.
Msg 4121, Level 16, State 1, Procedure ETL_sp_Update_Offline_Data, Line 57 [Batch Start Line 0]
Cannot find either column "Master" or the user-defined function or aggregate "Master.dbo.fn_WorkDays", or the name is ambiguous.
What? Why would they create this function in master? It's probably loops and cursors and lots of other bad stuff.
I open the function, and this appears in the comments:
Revisions:
Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
*
Well, no loops here!
I'm guessing that this is the function: http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
I'm also guessing that Jeff would suggest to stop using that function and change it into an iTVF.:-D

The next step is to take this database offline, and see if anyone notices. It's that old.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

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

Luis Cazares (1/3/2017)
Michael L John (1/3/2017)
Just putting the finishing touches on a new reporting server.
I run this old legacy ETL process that nobody knows about, and nobody remembers, when this error occurs:
Msg 4121, Level 16, State 1, Procedure ETL_sp_Source_Dimensions_AT, Line 1168 [Batch Start Line 0]
Cannot find either column "Master" or the user-defined function or aggregate "Master.dbo.fn_WorkDays", or the name is ambiguous.
Msg 4121, Level 16, State 1, Procedure ETL_sp_Update_Offline_Data, Line 57 [Batch Start Line 0]
Cannot find either column "Master" or the user-defined function or aggregate "Master.dbo.fn_WorkDays", or the name is ambiguous.
What? Why would they create this function in master? It's probably loops and cursors and lots of other bad stuff.
I open the function, and this appears in the comments:
Revisions:
Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
*
Well, no loops here!
I'm guessing that this is the function: http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
I'm also guessing that Jeff would suggest to stop using that function and change it into an iTVF.:-D

Heh... oh my. That was my very first article from 12 years ago. Love what you did with Rev 03. 😛

--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)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Sean Lange (1/3/2017)
The real downside is that overall it really seems like the number of questions has significantly decreased over the last 6-12 months. Hopefully this is just a trend and it will turn around in the near future.

Ditto.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

Luis Cazares (12/27/2016)
Carrie Fisher is now one with the force.[/url] :crying:
Can 2016 end already?

2016: The Movie (Trailer)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Sean Lange (1/3/2017)
Many thanks to you, Ed and anyone else bearing this task.

You're most welcome. I've gotten a lot from using this site and I just want to help keep it the best in the world.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Alan.B (1/3/2017)
Sean Lange (1/3/2017)
The real downside is that overall it really seems like the number of questions has significantly decreased over the last 6-12 months. Hopefully this is just a trend and it will turn around in the near future.
Ditto.

Yes it does. The questions seem fewer, but I haven't seen any statistics on it. The number of SS-style questions are up. I originally said I never wanted to skip responding to a post because of who it was from, but I admit I've implemented my own "black list" because I found that the frustration of trying to teach someone who doesn't want to learn was too much.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Repost from Twitter:

Research for a blog post: What are the common forms of 'catch-all' / 'multi-parameter query' you've seen?

I've seen:

(Col = @C1 or @C1 is NULL)

Col = COALESCE(@C1, Col) (and a similar form with ISNULL)

Col = CASE WHEN @C1 is NULL THEN Col ELSE @C1 END.

CASE WHEN @C1 is NULL THEN 1 WHEN Col = @C1 THEN 1 ELSE 0 END = 1

What else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

GilaMonster (1/4/2017)
Repost from Twitter:
Research for a blog post: What are the common forms of 'catch-all' / 'multi-parameter query' you've seen?
I've seen:
(Col = @C1 or @C1 is NULL)
Col = COALESCE(@C1, Col) (and a similar form with ISNULL)
Col = CASE WHEN @C1 is NULL THEN Col ELSE @C1 END.
CASE WHEN @C1 is NULL THEN 1 WHEN Col = @C1 THEN 1 ELSE 0 END = 1
What else?

Here are two numeric specific methods that you are missing, I tend to use the second one.

😎

ISNULL(((COL + @N) - @N),0) = ISNULL(@N,0)

COL BETWEEN ISNULL(@N,-2147483648) AND ISNULL(@N,2147483647)

Edit: missed this one

Method for a character predicate

[Name] LIKE ISNULL(@NAME,'%')

BrainDonor SSCoach Points: 19254 More actions · Answer 11

DBMS of the year, apparently - http://db-engines.com/en/blog_post/67

Steve Hall
Linkedin
Blog Site

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

BrainDonor (1/4/2017)
DBMS of the year, apparently - http://db-engines.com/en/blog_post/67

At a 100,000 foot view, sounds more like a report on marketing success than anything else. 🙂

--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)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

Jeff Moden (1/4/2017)
BrainDonor (1/4/2017)
DBMS of the year, apparently - http://db-engines.com/en/blog_post/67
At a 100,000 foot view, sounds more like a report on marketing success than anything else. 🙂

Close, but not quite.

Method of calculating the scores of the DB-Engines Ranking
The DB-Engines Ranking is a list of database management systems ranked by their current popularity.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

Eirikur Eiriksson (1/4/2017)
GilaMonster (1/4/2017)
Repost from Twitter:
Research for a blog post: What are the common forms of 'catch-all' / 'multi-parameter query' you've seen?
I've seen:
(Col = @C1 or @C1 is NULL)
Col = COALESCE(@C1, Col) (and a similar form with ISNULL)
Col = CASE WHEN @C1 is NULL THEN Col ELSE @C1 END.
CASE WHEN @C1 is NULL THEN 1 WHEN Col = @C1 THEN 1 ELSE 0 END = 1
What else?
Here are two numeric specific methods that you are missing, I tend to use the second one.
😎
ISNULL(((COL + @N) - @N),0) = ISNULL(@N,0)
COL BETWEEN ISNULL(@N,-2147483648) AND ISNULL(@N,2147483647)
Edit: missed this one
Method for a character predicate
[Name] LIKE ISNULL(@NAME,'%')

Note that both for the versions in your post as well as for the versions given by Eirikur, only some work correctly for nullable columns.

Additionally, the numeric methods mentioned by Eirikur depend on the data type.

They can easily be modified to work with date/time data as well, and then still depend on the data type.

I consider all of these to be bad ideas.

(I have seen the first three in your post, and I will not always rip it out. I do usually add a RECOMPILE query hint to such queries)

Another pattern that I have used is

IF @C1 IS NOT NULL

BEGIN;

SET @Qry += ' AND Col1 = @C1';

END;

(...)

EXEC sp_executesql @Qry, '@C1', @C1;

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 15

BrainDonor (1/4/2017)
DBMS of the year, apparently - http://db-engines.com/en/blog_post/67

I use this list a lot (and usually Microsoft hovers around #3 or #4, occasionally #2). It's a great tool for discussing why Accumulo (or pick the obscure NOSQL db du jour) may not be a great choice for your career. It's not that great a measure of which DBMS is better, just which one is getting used a lot.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning