Ok, so it sort of is about SQL....

Question

Post reply

Ok, so it sort of is about SQL....

mister.magoo

SSC-Forever

Points: 47174
More actions
April 18, 2013 at 9:43 am

#100515

...but this made me laugh today...
Seen in some production code:
LEFT OUTER JOIN [highlight="#ffff11"]Northwind[/highlight].dbo.orders_actioned ON scheme.opheadm.order_no = [highlight="#ffff11"]Northwind[/highlight].dbo.orders_actioned.order_no
...just WOW!
MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

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

I was just going to post about some nonsense I've heard an seen in my new job (and it's only my second day)

I'm changing the code, but I leave the basic idea of what made me say WTF

TRUNCATE TABLE Temp_Table

INSERT INTO Temp_Table

SELECT period_id,

1 AS country_id,

column_x,

column_y,

column_z

FROM SomeCountryDB..SomeTable

DELETE FROM Temp_Table

WHERE period_id BETWEEN @start_period AND @end_period

AND country_id = 2

INSERT INTO Temp_Table

SELECT period_id,

2 AS country_id,

column_x,

column_y,

column_z

FROM OtherCountryDB..SomeTable

DELETE FROM Temp_Table

WHERE period_id BETWEEN @start_period AND @end_period

AND country_id = 3

---And so on with 7 tables

Maybe some rows appeared out of nowhere, we'll better delete them.

And the quote that made my day:

I'm not sure if it's using a LEFT JOIN or an LEFT OUTER JOIN, I'll have to check

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Luis Cazares (4/18/2013)
I was just going to post about some nonsense I've heard an seen in my new job (and it's only my second day)

Maybe you should keep that resume handy? That is really scary!!! There are so many things wrong with that it is hard to know where to start. Let's start with a permanent table named temp_table. WTF??? Then of course there is the insanity of what originally made your eyes bleed. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

But...what if truncate doesn't actually remove everything that was there before? Don't we have to be sure we delete it before we insert? :w00t:

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

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

Sean Lange (4/18/2013)
Maybe you should keep that resume handy? That is really scary!!! There are so many things wrong with that it is hard to know where to start. Let's start with a permanent table named temp_table. WTF??? Then of course there is the insanity of what originally made your eyes bleed. :w00t:

Well the names aren't actually the real ones, I changed them. But it's still a permanent table used as stage table. However, the worst part is that I've seen a permanent table named something like Accounts_Movements_2008_temp that is still used to store all the information from 2006 to the current date.

Seems like I have bad luck with companies's databases. However, I hope to make some changes for the better or I'll be looking for a new job really soon.

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

OCTom SSChampion Points: 11785 More actions · Answer 5

Ha ha ha ha. Using Northwind. :w00t:

I had someone ask me about Northwind a few years ago. It looked like such a good database layout that a manager actually thought about using it.

I explained to them that it was intended only for practice. The response was something along the lines of why bother designing a database when one was already there. It would cost time and money to not use it.

I should should follow up with the company and see if they did use Northwind.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

I had to dig around a bit to find this one. One of our legacy tables replicated from the Main Frame has a table that nobody really knows it is for but...

There is a column ConstantC char(1).

Anyone want to take a guess what value you will find in all 1.2 million rows???

Not only do we have 1.2 million copies of the same value, just using the column is 9 or 10 extra keystrokes than just using the actual constant from the keyboard. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Sean Lange (4/19/2013)
I had to dig around a bit to find this one. One of our legacy tables replicated from the Main Frame has a table that nobody really knows it is for but...
There is a column ConstantC char(1).
Anyone want to take a guess what value you will find in all 1.2 million rows???
Not only do we have 1.2 million copies of the same value, just using the column is 9 or 10 extra keystrokes than just using the actual constant from the keyboard. :w00t:

Please tell me there is an index on it as well!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Jeffrey Williams 3188 (4/18/2013)
But...what if truncate doesn't actually remove everything that was there before? Don't we have to be sure we delete it before we insert? :w00t:

Obviously truncate means randomly delete data iif you feel like it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

mister.magoo (4/19/2013)
Sean Lange (4/19/2013)
I had to dig around a bit to find this one. One of our legacy tables replicated from the Main Frame has a table that nobody really knows it is for but...
There is a column ConstantC char(1).
Anyone want to take a guess what value you will find in all 1.2 million rows???
Not only do we have 1.2 million copies of the same value, just using the column is 9 or 10 extra keystrokes than just using the actual constant from the keyboard. :w00t:
Please tell me there is an index on it as well!

No the table is a heap!!! I have been asking around and nobody knows what the table is even used for. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

Sean Lange (4/22/2013)
mister.magoo (4/19/2013)
Sean Lange (4/19/2013)
I had to dig around a bit to find this one. One of our legacy tables replicated from the Main Frame has a table that nobody really knows it is for but...
There is a column ConstantC char(1).
Anyone want to take a guess what value you will find in all 1.2 million rows???
Not only do we have 1.2 million copies of the same value, just using the column is 9 or 10 extra keystrokes than just using the actual constant from the keyboard. :w00t:
Please tell me there is an index on it as well!
No the table is a heap!!! I have been asking around and nobody knows what the table is even used for. 😛

Oh, that's a shame, an index on 1.2 million repetitions of "C" would have been sooooo nice 😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

mister.magoo (4/22/2013)
Sean Lange (4/22/2013)
mister.magoo (4/19/2013)
Sean Lange (4/19/2013)
I had to dig around a bit to find this one. One of our legacy tables replicated from the Main Frame has a table that nobody really knows it is for but...
There is a column ConstantC char(1).
Anyone want to take a guess what value you will find in all 1.2 million rows???
Not only do we have 1.2 million copies of the same value, just using the column is 9 or 10 extra keystrokes than just using the actual constant from the keyboard. :w00t:
Please tell me there is an index on it as well!
No the table is a heap!!! I have been asking around and nobody knows what the table is even used for. 😛
Maybe I will add a clustered index.
Oh, that's a shame, an index on 1.2 million repetitions of "C" would have been sooooo nice 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Sean Lange (4/22/2013)
mister.magoo (4/22/2013)
Sean Lange (4/22/2013)
mister.magoo (4/19/2013)
Sean Lange (4/19/2013)
I had to dig around a bit to find this one. One of our legacy tables replicated from the Main Frame has a table that nobody really knows it is for but...
There is a column ConstantC char(1).
Anyone want to take a guess what value you will find in all 1.2 million rows???
Not only do we have 1.2 million copies of the same value, just using the column is 9 or 10 extra keystrokes than just using the actual constant from the keyboard. :w00t:
Please tell me there is an index on it as well!
No the table is a heap!!! I have been asking around and nobody knows what the table is even used for. 😛
Maybe I will add a clustered index.
Oh, that's a shame, an index on 1.2 million repetitions of "C" would have been sooooo nice 😛

How about adding a filtered index ;-):-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 13

SQLRNNR (4/19/2013)
Jeffrey Williams 3188 (4/18/2013)
But...what if truncate doesn't actually remove everything that was there before? Don't we have to be sure we delete it before we insert? :w00t:
Obviously truncate means randomly delete data iif you feel like it.

Well, that inline if does stuff like that! :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2