The Heap™ – Consultancy ©® - 2013-02-08 (page 2 of 2)

6:00 PM

ok lol

you ever have one of those ideas you think is BRILLIANT and then it comes back and just bites you in the ass in a few years

swasheck

@rfusca having kids?

kidding. i love mine

rfusca

@swasheck lol no

@swasheck how many you got?

swasheck

@rfusca dos zwei ni

rfusca

ic

SQL Kiwi

@swasheck No UNION is a logical concept. There are several possible physical implementations, only some of which involve sorting. Also, optimization may remove a UNION or translate it to something else given the context of the query as a whole. It is a mistake to try to correlate features from a declarative SQL statement to a particular physical implementation. That said, it is valid to look at an execution plan and wonder "where did that sort come from?"

swasheck

6:14 PM

it's fun for the most part. today i'm working from home because the oldest is sick

SQL Kiwi

DECLARE @T1 AS TABLE (col1 int);
DECLARE @T2 AS TABLE (col1 int);

INSERT @T1 VALUES (1), (2), (3);
INSERT @T2 VALUES (2), (3), (4);

SELECT col1 FROM @T1
UNION
SELECT col1 FROM @T2
OPTION (HASH UNION, HASH GROUP);

swasheck

so now i get to see all of your (pl) imgur posts

bluefeet

Is there a better sargable way to get the date at midnight? Obviously my lack of sleep is getting to me. Current code -- CAST(FLOOR(CAST(getdate() AS FLOAT ))AS DATETIME)

swasheck

@SQLKiwi that's kinda where i was. where's that SORT operation coming from?

SQL Kiwi

@swasheck I don't know, I can't see it :)

swasheck

6:16 PM

@SQLKiwi no, i know ... i'm just saying that's what prompted my question in here about it

rfusca

@swasheck i'm working from home because my boss said "There's a release friday night...nobody else will be in the office...you should work from home too."

SQL Kiwi

@bluefeet In SQL Server? By the way casting datetime to float is just offensive ;)

swasheck

@SQLKiwi but thanks for the wicked-cool explanation

SQL Kiwi

@swasheck No worries, any excuse to post an execution plan!

bluefeet

@SQLKiwi yes in sql server. Let me just say that is not my code, it is in a very poorly written stored proc that was handed to me to fix

SQL Kiwi

6:17 PM

@bluefeet In SQL Server 2008 a cast or convert to the date data type is very popular.

swasheck

@SQLKiwi i wish there was a way to trick the Execution PLan to use different names so that i could post it without fear of backlash from "the man"

bluefeet

@SQLKiwi How about 2005?

This code is in 2005 so cast to date doesn't work

rfusca

working with dates in sql server....nightmare

oracle is marginally better

SQL Kiwi

@swasheck I can never understand the general reluctance to post execution plans with table names and such in them. Do people really think they're using such amazing concepts that their whole business could be ruined this way? Pah.

swasheck

@rfusca mongo kills it though

rfusca

6:19 PM

@swasheck hmm i've not played with dates in mongo much

pg is freaking awesome with dates though

teradata is better than oracle but not as good as pg I think

swasheck

@SQLKiwi i could talk until i'm blue in the face to my boss about that, but he's very sensitive about it. that's why i'm so guarded

SQL Kiwi

@bluefeet SELECT DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101');

swasheck

@rfusca i see your sarcasm detector needs to upgrade it's heuristics

rfusca

@swasheck oh

lol

SQL Kiwi

@swasheck Yes. My question was why. I do not understand it at all - unless it's fear of embarrassment?

Everyone's database code sucks, people shouldn't be afraid of that.

@bluefeet Any fixed date will work. Many people use zero instead of a date, but the implicit cast offends me. Is the logic clear?

bluefeet

6:22 PM

@SQLKiwi I thought datediff was not sargable

swasheck

@SQLKiwi well i think that my director is a paranoid schizo. super nice guy but WOW is he delusional about worst-case scenarios

SQL Kiwi

@bluefeet It depends on the context, hang on a second.

@swasheck Oh I think I see your problem :)

@bluefeet Not when applied to a column reference, no (unless a convenient indexed computed column exists). It's fine as a seek value though:

DECLARE @T1 AS TABLE (some_datetime datetime PRIMARY KEY);

SELECT *
FROM @T1 AS t
WHERE some_datetime = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101');

swasheck

(by the way, how cool is it that we have P.. SQL Kiwi and Aaron Bertrand in here just interacting with us ... teaching and chatting away)

4

SQL Kiwi

Paul White you mean? I don't know, I've heard he's a bit of a dick.

swasheck

i heard he had a good heart

Paul Dudley White

Paul Dudley White (June 6, 1886 - October 31, 1973), American physician and cardiologist, was born in Roxbury, Massachusetts, the son of Herbert Warren White and Elizabeth Abigail Dudley. He was one of the leading cardiologists of his day, and a prominent advocate of preventative medicine. Early life and education White's interest in medicine was sparked early in life, when he accompanied his father, a family practitioner, on rounds and house calls in a horse and buggy. A 1903 graduate of the Roxbury Latin School, his undergraduate education at Harvard College encompassed history and fores...

bluefeet

6:34 PM

@SQLKiwi ok, thanks for the info

i have drive by downvoters

Lamak

I was about to ask if this question should be on DBA

0

Q: catastrophic failure trying to select from linked server

I have created a linked oledb/odbc connection to Pervasive SQL from SQL SERVER 2012: USE [master] GO /****** Object: LinkedServer [KSLAP208] Script Date: 2/8/2013 10:38:55 AM ******/ EXEC master.dbo.sp_addlinkedserver @server = N'KSLAP208', @srvproduct=N'Pervasive ODBC Interface', @provider...

and then I saw that it is aptem

njk

@Lamak Who is aptem?

Lamak

@njk Oh, you don't know aptem

njk

Nope. Enlighten me

Lamak

hm, not sure how can I explain it

njk

6:46 PM

I see he has 1250 questions

swasheck

@Lamak shhhhhhhhhhhh

Lamak

@swasheck?, your help

swasheck

@njk you should help him. the best way to learn is through experience, right?

njk

@swasheck I'm not getting myself into something if you guys are with holding

Mark Storey-Smith

@ConcernedOfTunbridgeWells Tomorrow pm should be fine

Lamak

6:47 PM

@njk we may be kinda biased

njk

@Lamak Help me understand your bias

SQL Kiwi

@njk No, go right ahead. I'll get the popcorn. We have your back, honest.

Lamak

But is a guy that asked a lot of terrible questions, had the habit to kinda stalk users that helped him....

sounds very grateful

njk

@Lamak Oh no.

Lamak

but then starts changing his questions

njk

6:49 PM

@SQLKiwi Thanks. Care for some butter on your popcorn?

Lamak

and keep on asking

and asking

SQL Kiwi

@njk Ooo ta

Lamak

sometimes accepted answers, then had new questions, then unaccept

njk

@Lamak Looks like Gordon answered

Lamak

so, he got himself in the position where many of the regulars here (through expierence) decided not to keep helping him

Michael Fredrickson

6:51 PM

wow... he's got 25 famous question gold badges....

njk

I suggested he take his question to dba. Hope you guys don't mind ;)

Lamak

@MichaelFredrickson yes, he has 27 gold badges

@njk I have the feeling that you are gonna be on @Aaron's "favorite" users list for that

;-)

njk

@Lamak Ha :)

jcolebrand

@rfusca it is a rhombus

swasheck

@Lamak @SQLKiwi @njk I'M GOING IN

Lamak

7:02 PM

@swasheck best of lucks

njk

@swasheck You'll be remembered

rfusca

@jcolebrand exactly, and thats the awesomeness considering my 4 year old little girl knew that

jcolebrand

@rfusca verrnice

ypercube

7:19 PM

@rfusca My advice is to not confront the teacher (unless you know he/she is a cool guy). I'd say to my daughter that is a Rhombus and some people call them Diamonds, too.

Up to age 10-11, teachers should be always right, in kids minds.

rfusca

@ypercube eh, she knows its both (rhombus and diamonds)..but the teacher was trying to say it wasn't a rhombus to her then

bluefeet

@ypercube feel free to remove that code from my answer and post your own.

ypercube

Nah, I don't feel like posting at SO tonight.

bluefeet

@ypercube I am just about done posting tonight. New users who don't get the typical rules of accepting are driving me nuts

I had someone start commenting about how great my answer was and that is helped them but then they accepted a different one. WTF

njk

@bluefeet Yeah I feel you

bluefeet

7:26 PM

not only that but they got 3 different answers from me.

0

Q: SQL, multiple rows to one column

So I have the following date ID NAME MONTH COUNT 1 David December2012 500 2 Rob December2012 320 1 David January2013 400 2 Rob January2013 280 I am trying to make this....... ID Na...