The Heap™ – Consultancy ©® - 2014-10-06 (page 1 of 2)

« first day (1371 days earlier) ← previous day next day → last day (3480 days later) »

6:46 AM

Anyone tried access DBA.SE on Windows 10 preview? I get a 404 error when trying to login using Google account.

5 hours later…

11:31 AM

Just read a comment in a LinkedIn discussion that "LAG and LEAD are terribly slow compared to things like the "Quirky Update" or even a very well written cursor."

I guess they are not followers of Aaron's blog.

11:49 AM

hi

i have an error
Error: PL/SQL: ORA-00904: "GPS_TIMESTAMP": invalid identifier

as per the error number it referees to miss spelt column names

but in my table there is a colmn with name gps_timestamp

then what possibly is the error referring to

@user285oo6 add a question in the site, with your full code that produces this error.

gps_timestamp is not the same as "GPS_TIMESTAMP" by the way.

??

column names are dsiplayed in caps by default

in sql

It might be. But we can't see your code.

user image

@ypercube is your crystal ball broken?

12:03 PM

@ypercube i hope the code is clear

@user285oo6 Then that table (or view, whatever it is) does not have a gps_timestamp column. As I said above, add a question at the site. Including the code and the table's definition (create table or create view).

k i will ask but i have the table created with restricted permission that's why i cannot post the table's defination

i can only post the screen shot of the defination

What do you mean? Can you not copy paste the code?

i will try please edit it if any mistakes are there

0

Q: Error in the procedure Invalid identifier is given,sql statement ignored

In the below procedure i have the gps_timestmp column name but still its pointing it as invalid identifier. create or replace procedure prc_rpt_vmu_current_state_n(pv_vmu_id m_vmu.vmu_id%type, pv_from_date varchar2, ...

12:25 PM

morning all

morning

morning people

1:05 PM

What is wrong with some guys?

You miss the point that it isn't only doctors. Each doctor is related to a Department. Each Nurse is related to a Ward etc. — ProfK 2 mins ago

Of course I miss the point. Because your are not explaining anything.

Another "need crystal ball" question.

very tempted to close as "not clear what you're asking"

but I avoid the modhammer on stuff like that

...and he deleted it.

1:23 PM

@LalitKumarB the solution of the question you posted is kind of complex for me since I am a beginner. — user3610008 4 hours ago

Give me the easy version because I'm a beginner and therefore my problem must be in beginner domain as well

1 hour later…

2:23 PM

crickets

seem to have invaded my house so if you need some, I'm happy to ship them

we've got plenty of those and mosquitoes after all the rain this summer

Don't say I didn't offer

People didn't like the joke, it seems

-2

A: What's this grey gradient doing over here?

It does also happen with answers, that you selected with J or K, while keyboard shortcuts are enabled, as Joe pointed out.

3:00 PM

bbc.com/news/world-us-canada-29508566

Yeah!

3:21 PM

user image

Damnit, I hate when I misplace my Rage. I just want to punch something every time I lose it

@swasheck So, are you saying that the whole internet is wrong and should be terminated?.

Hey people, click here to see @swasheck say that internet is more dangerous than cancer

@Lamak that's my default mode

@Lamak too fanciful. it should be more like @swasheck says that we can't trust any information on the Internet

yeah, but that's no fun

but it's more realistic

3:29 PM

ok, you convinced me

that it's more realistic or that the information on the Internet is all wrong?

the realistic one. There's good information on the webz

No, "fast" is not ambiguous.

Fast can only mean one thing, regardless of your scale. "Faster" may be difficult for us to gauge, but it's still not ambiguous. Meanwhile, performant can mean a bunch of things, as can "more performant." — Aaron Bertrand ♦ 3 mins ago

@Lamak That was good information, for instance.

there's this chat, lots and lots of information

3:36 PM

@AaronBertrand Performant is a phrase thought up by someone who thought. Could I sound like more of an arrogant prig while speaking about performance?

Hi, @Zane. How's it going?

Pretty great how've you been?

I'm good. I'm going to Panama tomorrow

Van Halen just went off in my head, for some reason

Nice!

@swasheck Jump?

3:42 PM

@Zane Panama

You really got me?

Hot for Teacher?

Runnin with the devil maybe?

oh. i get it. you're trying to be funny.

Aren't I always.

try harder

AVIS™

3:45 PM

Is this guy serious?

1

Q: SQL: Using outer join in SQL Server 2012

I'm trying to learn how to use the OUTER JOIN function in SQL Server, so that the select query returns all values from two tables, even the unmatched ones. Here are the two sample tables I'm using (Boys and Girls): Boys: Name City Andrew Cape Town Mike Paphos Simon Amsterdam Girls:...

sql sql-server join sql-server-2012 outer

This is like 1984 all over again

@billinkc No, Zane and I didn't exist in 1984 ;-)

@billinkc party like it's 1985

@Zane what's wrong?

My client has an internal time entry system. That's not unheard of

What I do find ... unique is that they record time in increments of a day

Not hours mind you, just how much of the day did you do X

3:49 PM

" FROM boys, girls
RIGHT OUTER JOIN boys as men" Sounds like a bad SQL Server R and B band."

Spend your entire day doing something, you write 1D

@Zane well maybe they've never written sql before

Still funny.

agree, but at least they actually included a query they tried no matter how bad it was

that's a rarity

does there exist good documentation/whitepaper info on how CPU architecture affects SQL Server performance? i've been trawling berry's blog and have some good information, but was just wondering what else was out there

3:52 PM

@mmarie how long?

@swasheck he has some good ones on sqlperformance.com too. Although by "CPU architecture" do you mean x86 vs. x64 or do you mean generation / size / number of cores / clock speed or ...?

nehalem versus mmx

@swasheck Quantum processors or Biological?

poor sap

-4

Q: Querying 50+ tables for a record

I have 50+ tables in a database. From the information schema I can get the table names. I want to query all these tables to find a record without using joins. All of them have the same primary key but different table schema. I just want to know whether the value I am looking for exists in any of ...

sql database select

@Zane tomorrow til sunday

4:03 PM

oven?

-1

Q: Several days oven for what you have now

this is so that my user has the opportunity to acquisitions access to additional features on the website, What I'm into is that it should be 60 days in the oven if you buy it again, just also say that I buy now and then again in the morning the same package so it must be just 60 + 60 together s...

@swasheck Zilog Z80 or Ricoh 2A03

4:15 PM

@AaronBertrand yeah. i've been reading through his stuff and most of his recommendations are for OLTP workloads. i guess i'm looking for what to look for in a CPU for data warehouses. my working theory is that i should sacrifice "performance" for "capacity" (in berry's parlance) to increase the potential MAXDOP for scans and such.

I'd say you should focus on number of cores and amount of memory. For DW you don't need blazing fast CPU speed.

i thought i'd referenced data warehousing in my original chat message but clearly i didnt. derp. i'm reading the FTDW.

@AaronBertrand right. that's the sense i got from berry's articles. Core count, chip cache, and memory. tune storage for best sequential reads.

Ideally you don't care about storage because you spent enough on memory

@AaronBertrand Agree the CPU we had at Wand was nothing special. It was all about the RAM.

@AaronBertrand ideally. sure. :)

4:21 PM

Right and I didn't mean you don't care about storage but you don't have to spend 99% of your money and time on the storage if you can get most or all of the data into memory

absolutely. i didnt take it that way. "all reads come from memory" so if you can get most/all of your reads to come from memory then that's the ideal.

@mmarie That is awesome.

@swasheck In a DW scenario I always notice the users almost all gravitate towards the same few sets of data anyway.

@Zane yeah. the most recent set (for us). that's a logical model change that we're recommending. we're also buying new hardware and we've been asked to make recommendations (this time, we weren't last time and it blew up on us)

There always seem to be a few stand out items that they go to over and over.

@swasheck I'd recommend upgrading from a Ricoh 2A03 to a Ricoh 5A22. Enter the blazing world of 16 bit!

Yeah so I'd say take most cores vs. fastest CPUs, maybe balance with cache (but I wouldn't go nuts about that), and spend the savings on more memory. Don't buy a server unless you stuff it with the largest amount of memory it will support. IMHO.

4:27 PM

Sorry I spent all Sunday playing and doing research on old game systems.

cool. thanks for the rec, @aaron. i suspected as much, but it's nice to have guidance from someone i trust more than myself :)

@Zane well - we're currently on AMD so you're not far off

Glenn has some specific recommendations but like you said he focuses on OLTP workloads (and it may be difficult to cross-ref that list with the supported CPUs for the motherboard in the server you need, which may be driven by other requirements such as U size, network, storage)

@swasheck lol.

@Zane I learned assembly on a Z80A

@swasheck oh yuck

4:29 PM

AMD was were it was at back in 2004.

AKA the last time I built a rig.

yeah. the per-core licensing penalty really kills you with amd

I didn't even trust it then. Maybe for a PC for my grandma

(we're going to 2014 with this as well)

@swasheck but there's a built-in AMD discount!

That original like of 64 Bit processors were sick nasty(how we talked back then).

4:30 PM

@AaronBertrand yeah. not worth it.

And my xp3200 that and my Radeon 9800 I could run some serious CoD 1 on that rig. :)

But oh the times have changed.

It's decided. I'm building a new PC.

I miss having one.

@James ... i found what appears to be the source of your vendor's recommendation - and it's not because of performance

> Logical File System: Mounting LUNs to mount-point folder paths in Windows, rather than drive letters, is preferred due to the number of volumes in many Fast Track systems.

I'm so over building PCs anymore.

@MikeFal I took about a 10 year break.

hi, i'm @MikeFal and i just purchase surface tablets on day 0

4:37 PM

Bah, day 30, please

@Zane I'm just tired of spending 8-10 hours trying to figure what I plugged in wrong.

And I don't game NEARLY as much as I used to. I gamed for 5 hours yesterday, which was the longest stretch of gaming time I had in about 4 months.

That's actually why I did consoles for about 10 years.

I'm just not a console gamer.

The only thing I played on console was HAlo.

The idea of working a 14 hour day at Wand and then come home to fix driver issues was sickening. However now the console market is really starting to feel like a rip off.

The a bunch of the games I've bought for my Xbox One have been rushed. Hell BF didn't work at all.

Destiny was all hype and no content.

Yeah, I've heard some poor things about Destiny, which is pretty disappointing considering it's from Bungie.

Do any of these games work like bowling on the Wii? (I feel so old all of a sudden)

4:48 PM

The content all got gutted for reasons unknown. Gameplay = great. Story = appallingly bad. Content = 4 hours worth.

Ummm....no

@Zane The main reason I play Bungie games is FOR the content. Damn shame if they took all that out.

Yeah their head writer quit like a year prior to the launch and they took out the entire story.

Now it's literally a fight between the good guys(light) and the bad guys(darkness).

All information about the world is given to you by telling you've unlocked a story card and to go to Bungie.net to read said card....

@AaronBertrand Shooter games.

@AaronBertrand tecmo super bowl FTW!

@swasheck Still the best sports game ever made.

@swasheck did you know this is a thing? tecmobowl.org/topic/…

i liked some of the old NES hockey games. eric lindros was badass. could score goals and destroy fools with massive checks. lemieux was still the scoring machine, but lindros was fun to play

@Zane didnt

but it's blocked

4:55 PM

Tecmo 2015 Updated teams and Rosters.

You can even get it loaded onto a cartridge.

interesting

Oppy doesn't want you playing tecmobowl on company time!

i'm not that much into gaming right now

but ... it may be fun for a trip down memory lane

@MikeFal no doubt. they also dont want me to be productive either

Any good recomendations for an SSD?

I love Samsung 840 - I have two 1TBs in my Promise J4

5:09 PM

I don't know if I could do the TB as that would make it the most expensive part on my system.

You probably don't need the TB. I bought it because of $/GB, not because it was the biggest

I built a concatenated disk set - 2 x 1TB, plus a 512 and 256 I had laying around, nets me a 2.69 TB backup/archive drive, connected by Thunderbolt. I don't need to store anything locally anymore, so all my downloads, ISOs, VMs etc. go there.

blogs.sqlsentry.com/team-posts/like-plan-explorer-win

<--- because I can point to it graphically and say "That's why I'm right!"

#ILovePlanExplorer

Can I use the hashtag here?

You can, but it doesn't do anything

that's what I thought

5:20 PM

#StackOverflowDoesn'tTrackHashtags

#EspeciallyInChat

much error

Yikes.

That's not error. That's like his full package log.

Wow. Mongo is such a steaming pile of poo

@swasheck You should try MangoDB™

user image

^ mango

5:34 PM

@jnk you should try shutting the funk up

6:01 PM

Sorry, early enter shakes... So with the giveaway I am guessing the point is to visit that giveaway page ever day to get the minimum of the 9 points possible each day along with tweeting?

@ShawnMelton yessum

Edited comment above

@ShawnMelton that's right

Ah, missed doing that last time...Thanks

6:17 PM

@swasheck Yes, that's part of why we originally started using mount points. I don't think it's the source of the SAP's consultant's misconception, though. He explicitly said that he thought using mount points improves performance. I'll try to ask him why he thinks that someday.

I guess it's possible that he read the Fast Track whitepaper and thought anything suggested in it must be fast -- is that what you're thinking?

The SAP consultants have also argued that they can't develop on Developer Edition, only Enterprise, and that log shipping is the only DR technology that works with SAP. I think they are more used to working with Oracle.

@swasheck It kind of loses at you 'write Javascript to query the database', doesn't it?

wired.com/2014/10/plantronics-backbeat-pro $50 is now considered less expensive

It was funny the contractor I worked for a bit with Army bought everyone the Boise headphones in an office that was "built" for collaboration (low wall cubicles)...I think that kind of defeated the purpose.

@ShawnMelton I recently got a pair of Sony MDR-10RBT - not quite as good as my wired QC-15s, but pretty darned close. And the number of times I've yanked the cord out (or someone next to me on the plane has)...

I never really like them, the high pitch frequency always gave me a headache if I wore them to long

Never had that problem. Usually only wear for an hour or two at a time - except on long flights overseas

(And I'm not brushing off the problem or suggesting it doesn't exist, just doesn't happen to me. :-)

6:42 PM

Aschenbrenner's SQL Server Quickies are so funny

I confess I have yet to watch one.

@AaronBertrand You might not learn anything. But they are quite entertaining.

@mmarie what's your schedule look like after SQL Sat MSP?

@Zane Flying back Sunday at 1:05 pm

I need to take an opportunity to go down and free roam your town and want to pick an weekend you're not busy.

6:48 PM

Good luck finding one

@Zane OH.... hmmmm I'm free Nov 1 and 15 and Dec 6th and 13

Which reminds me I should probably book holiday flights to go see my family

My cousin and I are going to down there to scout it out. We'll see when works for us.

There will be a new brewery open by the time you get down here

It might even be in the New Year. I'm not quite sure yet.

Torn Label BrewCo

@TornLabelBrewCo, Kansas City

Torn Label is a craft brewery in downtown Kansas City's Crossroads District slated to open this year.

2 tweets, 339 followers, following 2 users

6:53 PM

@AaronBertrand - could someone (perhaps a moderator) have modified the "About Me" section of my user profile? Is there any way to know?

@zane I also discovered a delicious local one at the brewfest a couple weeks ago.

Excellent.

@CraneBrewing, Raytown,MO

We are brewers & beer loving professionals brought together by our passions and expertise to provide authentically inspired Belgian farmhouse ales.

46 tweets, 379 followers, following 356 users

There were probably 30 breweries at the fest and I had 3 beers from these guys and all of them were great

not just acceptable but actually really good

That reminds me. We have to go to Busters when you're in town. They have tons of Sours on tap.

which is a little surprising because there isn't really anything good in Raytown

Ooooh, yes please. <3 sours

6:55 PM

I know I made a brain note for that.

Is this in the grey area or more on-topic here?

0

Q: MSSQL Server 2012: Pass in table value parameter or the individual parameters into a stored procedure?

Is there a general performance difference in MSSQL Server 2012 passing in a table valued parameter versus just passing in individual parameters into a stored procedure? Also, does passing in the same individual set of parameters reduce recompilation of your stored procedure versus passing in a ...

sql sql-server query-performance recompile

And now we can see two examples by fairly high rep users in SO of why using shorthands for the date part is bad

0

Q: convert time portion of date to an int

I have a column called enteredindate that includes data such as: 2012-10-10 18:02:00 2012-10-11 13:30:00 2012-10-11 14:27:00 2012-10-12 14:14:00 How do I make a new column that has an INT data value next to this enteredindatecolumn like this? 1802 1330 1427 1414

@Lamak kinda harsh to downvote for that reason though

@bluefeet they were using mm when op wanted minute, not month

both answers were wrong

^^^

No worse than DVing Gordon when he fires off halfbaked answers all in the name of FGITW

7:09 PM

@Lamak ah ok, then proceed :)

Nuke it from orbit, it's the only way to be sure.

@bluefeet :-)

I undownvoted now, but one guy still used a shorthand

@bluefeet In any case, it's a good real life example of why one should listen to @AaronBertrand's advice, even if you are experienced in SQL Server, you still can make mistakes that can be avoided by using good practices

Suitable duplicate?

8

A: Extracting hours from a DateTime (SQL Server 2005)

... you can use it on any granularity type i.e.: DATEPART(YEAR, [date]) DATEPART(MONTH, [date]) DATEPART(DAY, [date]) DATEPART(HOUR, [date]) DATEPART(MINUTE, [date]) (note: I like the [ ] around the date reserved word though. Of course that's in case your column with timestamp is lab...

@MaxVernon yes, it is possible, and I don't think there is any auditing exposed to moderators (might take a ping to an employee). What was changed, exactly?

@AaronBertrand Actually, I thought there was a change, but realized now I'm just a dummy. Thanks for the answer, and your time.

7:16 PM

You should edit your profile to indicate you are in fact, a dummy

3

@billinkc SHUDDDDUP!

@MaxVernon Yeah I wouldn't really have believed it if you suggested that a moderator here changed your profile (or any site, really)

Don't even consider it. TVPs will outperform individual parameters and be much more scalable and flexible too (let's say you decide you want to support 100 parameters, then later change to 200, then pointy-haired boss wants 500 parameters - have fun maintaining that stored procedure definition). I really doubt the TVPs are the issue - this is like saying all the cars on the highway are slow, it must be the wheels - because they all have wheels! — Aaron Bertrand 55 secs ago

Can anyone else reproduce what this guy is saying?

Like I said, I have no problem modifying your inserts, but your selects both return 3 rows. They return exactly same result. — ajeh 1 min ago

@James that's more along the lines of what i was thinking

I want you to scrap whatever you've tried already, and take the current version of the code in my answer, and see if you still get three rows for both queries. — Aaron Bertrand ♦ 3 mins ago

How hard can be to understand this?

7:31 PM

No idea. Guy has spent more time arguing with me about this than it would have taken to go get access to his original sample. You're welcome for the help buddy!

@AaronBertrand I'd like to know why he is doing a presentation on SQL Server 2005 ???

@PatrickHofman this is one of my all time greatest pet peeves. If you people don't like your answer they are free to downvote it for whatever reason they choose. It does not have to fit your or anyone else's standards. If someone feel shorthand is a good cause to DV then that's entirely their prerogative. Don't tell me how to use my vote. P.S. I didn't downvote but you should totes not use shorthand it's needless and confusing. — Zane 1 min ago

Did you just use "totes"?

Indeed. I don't know why but I thought it would be funny.l

@ShawnMelton I addressed that in my answer and got a little more direct about it in the comments

7:33 PM

@Lamak yeah, I know. @AaronBertrand is always right

Just as it makes the 100% opposite tone of the super serious portion of my comment.

TIL datetime only has 3.33ms precision

@Zane If they ask what it means, say it's a shortcut.

@JNK yup. DATETIME2(7) FT Storage-Bloating WIN!!!!!!!!!!!!!!!!!!!!!!!!!!

lol

we are using datetimeoffset(3) for most stuff where milliseconds matter

7:39 PM

Wasn't my down-vote either, but seriously, your initial error was wrong because you were using lazy shorthand, someone suggested an edit and you changed mm to mi instead of actually fixing it? Write your own code however you want, but I am opposed to promoting lazy shorthand that leads to the same problem that made your initial answer wrong in the first place. See #6 here blogs.sqlsentry.com/aaronbertrand/… and sqlblog.com/blogs/aaron_bertrand/archive/2011/09/20/… — Aaron Bertrand 17 secs ago

I think I've seen a question with a query with ambiguous results but I can't remember it. It must have been long time ago.
I think it was more complicated than your example, and had three tables so it could be seen as either `(a LEFT JOIN b) JOIN c` or `a LEFT JOIN (b JOIN c)`

@AaronBertrand "your initial error was wrong" ?

@AaronBertrand Why do people get so pissy about loosed 2 magical unicorn points?

@ypercube I'm human, sorry

@Zane who knows

It's like users see that little -2 in the corner and thinK "How dare you sir! Explain yourselft?!"

@ypercube department of redundancy department

7:43 PM

Some people really love their magical unicorns. I think it's because they're horny.

I don't think he is that pissed. He is just lazy - to edit the answer.

rawr

@ypercube but he had edited it, he just still chose to use mi

@Lamak Yeah, that's my point. He only edited 1 character.

always entertaining when The Heap™ gets on a comment thread

7:45 PM

@Lamak that's the part that bugged me.

@AaronBertrand yeah, it's kinda bizarre, moreso taking into account that using a shorthand led him to an error in the first place

Yeah "learn from your mistakes" comes to mind

Edited.

@AaronBertrand And have I said that I love your "bad habits to kick" blog posts?

moreso, totes, 2 new words in my lexikon.

7:47 PM

/golfclap

I've use them so much

I've done the work for him.

@ypercube ah, is it wrong?

@Lamak :-) They are sometimes useful for settling arguments. Because when I try to find an SO answer where I made a point, it's impossible.

@AaronBertrand I would say "many times" instead

7:48 PM

lakhs of times

lakhs and crores

@billinkc you really love "lakh"

lakhs of cores and more geebees for our deebees

@ypercube I wouldn't recommend Totes as it make you sound like a 13 year old.

I do. I haz a sad that we don't have a useful notation like that

7:49 PM

Looks like @bluefeet didn't quite predict all of a user's eventual requirements

0

Q: How to add a default null row if no results from the dynamic pivot query

I execute this procedure and display results in my application. If my query does not return any results, I still need to print out the column headers and since its a dynamic query I cannot hard code. My query runs fine, Its just how do I add a NULL row if not results are found. So for instance i...

sql sql-server-2008

@billinkc I like to use "cubic hectare"

So, apparently "moreso" is an incorrect (but sometimes used) way of writing "more so". Didn't know that

@Zane I like it moreso because there is a greek word that sounds the same - and is also not a proper word.

I'll make the ask of Oxford to include it in their next edition

Yes, please doso

that ask better be made most performantly

7:52 PM

@Lamak Clever.

@ypercube Moreso is more performant because you don't have to type a space

You could use a computed column for BedId. No need to be actually stored. Would something like Bedid AS CONCAT(WardId, '-', Id) work for you? — ypercube 50 mins ago

@ypercube ^ concat is 2012+ only; OP is 2008 R2

@AaronBertrand Sorry. I should have put some CASTs as well, shouldn't I?

@Zane :)

@ypercube no, should be fine, implicit conversions FTW

7:55 PM

@AaronBertrand I didn't write that

don't blame me for that mess

@bluefeet I believe you... but it may be loosely based on one of your answers nonetheless. :-)

@AaronBertrand :p

This is probably a long shot, but does anyone have an MSDN subscription promo code I can use?

Promo code? I haven't seen one of those in a couple of years.

cool...

7:59 PM

You mean for a free MSDN subscription? Or a promo code for a discount?

for a discount

We used to get the former as MVPs - three a year, IIRC. I think they canned it because the Subscription IDs were appearing on eBay or similar.

I haven't seen any for a discount, sorry.

« first day (1371 days earlier) ← previous day next day → last day (3480 days later) »

Transcript for

Oct '146

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile