The Heap™ – Consultancy ©® - 2012-08-16 (page 1 of 2)

Simon Righarts

03:55

Hmm. Is this a good question for us?

0

Is there a public dataset of Jeopardy or Jeopardy/Trivia style questions/answers anywhere? Even better -- is there list of all known trivia public datasets?

performance-testing

jcolebrand

not really

Simon Righarts

There's actually a trivia/puzzle game site on Area51, pity we can't migrate to there

@jcolebrand Fast on the draw today ;)

jcolebrand

@SimonRigharts you pointed it out

Simon Righarts

True. I guess that makes me the one with nothing better to do ... whoops

(waiting for a script to finish running, doobie doo)

jcolebrand

lol

dezso

04:25

@Mr.Brownstone Which one was that?

Jack Douglas

05:51

@AlexKuznetsov Great - I'm looking forward to reading it once jco has got you logged in :-)

jcolebrand

@AlexKuznetsov and then promptly missed you in chat?

dezso

06:33

'How to solve problem x in MySQL?'
'use MongoDB'

I know it's probably not a practical option, but it's still something to think about: this seems like a good use case for a document-oriented database like MongoDB. You could store all of the comments to the post within the post, and then nested comments on each comment within that comment. Querying all the data for some posting is as simple as grabbing the document—no joins required! However, the rest of your design might not work so nicely with MongoDB. But, like I said, it's something interesting to think about. — DaoWen 8 mins ago

Phil

Morning

dezso

Moorning a ding ding ding

Phil

NoSQL baffles me sometimes. They're all proprietary APIs. It's like we've all gone back to the 1960s and invented our own data-stores all over again.

@jcolebrand Rarely see that mod around. Does he do anything these days?

jcolebrand

@Phil what who huh?

Phil

I was scrolling up & saw the discussion about denny

jcolebrand

06:45

Do you mean this Microsoft MVP, VMWare vExpert, mod on two sites: mrdenny.com itknowledgeexchange.techtarget.com/sql-server stackoverflow.com/users/4197/mrdenny serverfault.com/users/1674/mrdenny linkedin.com/in/mrdenny

he isn't a particularly active mod, but he does mod, and he's generally more active on Server Fault

And it does add a bit of prestige to the site in the realms of the internet whit large that one of the more knowledgeable people in the field is a moderator here.

Just like it help that BOzar spends some time here.

Phil

Aha. I don't hang around in SQL Server land ;)

dezso

Now this is cross-posted:

0

Q: CREATE UNIQUE TOKEN(NUMERIC) ON POSTGRESQL

I have a database where I want to generate unique tokens of six numeric characters. This tokens should be generated each time an invoice number is inserted in the invoice_no column. This would mean that every time I do an insert and the column invoice_no is not null, the token is generated of the...

postgresql sequence random

jcolebrand

What we need are two more close-> dba votes on:

0

Q: CREATE UNIQUE TOKEN(NUMERIC)

I have a database where I want to generate unique tokens of six numeric characters. This tokens should be generated each time an invoice number is inserted in the invoice_no column. This would mean that every time I do an insert and the column invoice_no is not null, the token is generated of the...

postgresql token sequences

Then JackD or JNK or I can close as dupe

dezso

let me get 2.6K more rep first :)

dezso

07:10

foolish problem here

solution on DBA.SE, of course :)

6

A: Postgres PgAdmin III Query Builder crash

We had the same problem 2 times on two different computer, in just a few days of interval ! PgAdmin started to crash each time we open query builder ! Restarting the computer, reinstalling pgAdmin in differents versions... Nothing resolved the problem ! But we found the solution to this problem...

(and my hairsplitting session began already :)

gbn

08:30

morning all

sigh

0

Q: Getting #1111 - Invalid use of group function error

I see there is an error in the last line. Can someone plz tell me what is the alternative. INSERT INTO month_week_qty ( site_id, cat_id, cat_name, scat_id, scat_name, sscat_id, sscat_name, duration, w_m, year, sum_qty ) SELECT c.site_id, NULL, NULL, NULL, NULL, NULL, NULL, 'mon...

mysql group-by aggregate-functions

Phil

Clickety

gbn

Not my downvote.

but where to start on that...

Phil

I HATE the whole "plz" thing. It's so f***ing rude!

dezso

08:49

plz check it once more

gbn

09:19

@dezso I thought that, but couldn't be bothered to explain "ANSI standard group by"

dezso

I'm still considering adding a paragraph or two on that

Simon Righarts

09:37

Explaining "why" is lost on the "plz send teh codez" crowd.

(And yes I did accidentally mis-spell that worse than I intended to. Bad sign, right?)

ConcernedOfTunbridgeWells

Morning all

dezso

@SimonRigharts Come on...

Simon Righarts

Evenin'

dezso

morning

Simon Righarts

@dezso too cynical?

dezso

09:47

a bit, I think

ConcernedOfTunbridgeWells

What, a dba? cynical?
perish the thought ...

dezso

the majority may be unaffected by that sort of educational stuff

Simon Righarts

I think you'll find a lot of the smaller SE sites (still in the technology space, i.e. DBA, Cryptography, ITSec etc.) are largely composed of jaded SO vets

And there's a shitton of "don't tell me why just tell me what" questions on SO. Even when five minutes of reading why would save them three days further down the track, they'll plow into whatever they're doing headfirst, blinkers on.

dezso

but if there are two or three out of a hundred who is not, than it's worth it

Phil

It's the instant gratification generation

gbn

09:52

ah sh1t. How to convert a string 0x1122 etc to actual binary

(sorry, off topic)

I've bloody forgotten

Phil

In SQL Server?

gbn

yeah

Simon Righarts

hex to binary?

gbn

CAST would give the ASCII for 0, x, 1, 1, 2, 2 etc

An actual string '0x1122'

my brain has stopped

(BRB)

Simon Righarts

Head down to the pub for a couple hours, guaranteed to cure all ills

Today I learned: don't laugh at people that look tired after a 6AM start, because then you get called for a 5AM start tomorrow morning. Boo.

Jack Douglas

09:55

@gbn beyondrelational.com/modules/1/justlearned/388/tips/10007/…

select len(CONVERT(varbinary(max),'0x1122', 1));

dezso

So, folks

it's a birthday today

Biju jose

yes dezso

hei where is @gbn is he here?

ConcernedOfTunbridgeWells

@dezso Well, you know what to do. Sadly I don't think many DBA.SE regulars live in Budapest.

dezso

looks like that

Biju jose

so a great great happy birthday to dba.se

5

Jack Douglas

10:05

@gbn of course it still looks like a string in the output because it renders the binary as hex :-)

but len reveals it's true nature

dezso

this is a great occasion for asking:
how do you call a sp/function/udf or whatever, which gets a bunch of arguments and returns some data filtered on those arguments?

Jack Douglas

@dezso I call it slow

:p

0

A: Fix for orphan user 'guest'?

Note : Whenever you move your database from one server to another server there is a problem of orphan user usually occur. FileListOnly is the new term in sql server that has all the details of the created backup that who has access to it. So there are sequence of task that you have to ...

1

A: SQL Server 2008 user, group, or role already exists in the current database

Note : Whenever you move your database from one server to another server there is a problem of orphan user usually occur. FileListOnly is the new term in sql server that has all the details of the created backup that who has access to it. So there are sequence of task that you have to ...

2 questions, identical answers: are they both relevant to the questions? Can they be edited into shape? Do I need to nuke the new one?

dezso

@JackDouglas I do as well

planning to prepare a question on this topic

especially how to make it really slow :)

gbn

@JackDouglas thanks. I left ym desk for a sandwaich and it came back to me.

msdn.microsoft.com/en-us/library/ms187928.aspx

Jack Douglas

@gbn that could be read so many ways :-)

This is well above the call of duty:

2

A: How can I generate a random bytea

Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use: CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer) RETURNS bytea AS $body$ SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex') ...

Phil

10:22

@JackDouglas Upboats

The man's a lunatic ;)

Jack Douglas

@Phil You know each other? Or do you just basing that on his answer :-) I'm trying to encourage him to pop in here so we can pat him on the back...

Simon Righarts

@JackDouglas That's ... impressive. And a little bit scary, actually.

Phil

Basing it on that answer :P

dezso

10:47

Matter of personal habit. ANSI joins would be fine if you're more comfortable using them. — pjama 9 mins ago

Craig Ringer

11:09

Thanks @JackDouglas for the interesting problem. Nice change from endless Rails questions and problems with Pg on Macs after upgrades.

Phil

11:20

:)

Jack Douglas

@CraigRinger Great answer - we love that kind of stuff over here, as you probably know. C extensions are not for the faint-hearted I see :-)

and thanks for dropping in too

I was toying with the idea of using a single random() call to generate more than 1 byte of data, but I think the gains would be too small to make messing up your nice concise SQL version worth it

Craig Ringer

@JackDouglas C extensions to Pg look complicated but they really aren't too bad. The intro docs are pretty decent and after that existing functions and headers in the main Pg sources are a good guide and model to follow.

@JackDouglas The docs certainly aren't anywhere near as good as for the SQL level though.

Jack Douglas

@CraigRinger yes - I guess so, though I was really referring to the overhead in deployment - I have quite a few postgres databases at different clients so I'm sensitive to that

select lpad(to_hex(width_bucket(random(), 0, 1, 256*256)-1),4,'0');

btw

Craig Ringer

@JackDouglas ow, yeah. Not too bad if they're all the same version & OS so you can build once and pish the extension out as binaries but pretty icky otherwise.

Even then, custom extensions make upgrades "fun"

Jack Douglas

11:37

@Craig - I notice you have a blog

what does explain.depesz.com do?

oh - I see it has an example

yes, I'm glad I know that exists now - depesz contributes here every now and again you know?

Craig Ringer

Yeah, I do. Great guy.

His "waiting for <x>" posts are very handy.

Sorry if I'm a bit scatty; on SO by phone right now.

Jack Douglas

:-)

kgrittn is another - one of the developers, clever chap

I have a soft spot for postgres though I mostly earn a living with Oracle

postgres just gives me far less brain pain

Craig Ringer

11:54

Heh. I tried an Oracle express edition once. shudder

I'm lucky enough to be able to choose the DB I work with, so I mostly use Pg. I had a narrow brush with death^UniVerse once though.

Jack Douglas

@CraigRinger UniVision?

Craig Ringer

12:16

@JackDouglas nope, IBM UniVerse. Straight out of the eighties but still pushed as a live product. Its a freeform row store. None of that nasty "data typing" and "constraints' stuff. The NoSQL crowd should love it :-p

Argh it's. Sorry. Phone.

Phil

0

A: Character Set Database Migration

The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring m...

Aren't character sets fun?

You haven't lived until you've worked with far eastern clients :D

Aaron Bertrand

0

Q: How can I get a MD5 of an entire database and use it to poll for changes?

I'm trying to do some sort of polling to listen to updates occuring on a database. So far I was considering getting a MD5 hash (or any type or hash) of the database (or couple tables), store that in a variable, and run a thread that loops this procedure over and over, comparing it with the last r...

c# sql-server linq-to-sql

Phil

"Is that a good way to do it? If not, why? If yes, how?" - haha

Jack Douglas

@AaronBertrand has SQL Server got some sort of 'notify' API?

obviously his 'solution' is a) insane and b) will not work - I merely ask out of curiosity

Aaron Bertrand

There are event notifications, not sure it would fit the bill here. I think it's funny that he wants to reload the entire database every time there is a single change.

Craig Ringer

12:26

@JackDouglas My work has the unfortunate habit of (nearly) selecting weird legacy stuff for new projects. About 5 years ago they bought Progress, a "4GL" database/RAD system.

Jack Douglas

@CraigRinger that's unfortunate if you ever need to move jobs :)

Craig Ringer

@JackDouglas ... and I'm currently working on a project to migrate them off a 4GL called "Plain English". It's a shared ISAM file database with a business-y language on top. OK-ish so far as it goes, but that's not far. The company that made it went out of business in 1983, which the the build date of the binaries the system runs on. Despite that, the system was only implemented in 1991 (!), on a system already 8-years dead and gone. Nuts.

Phil

Bonkers

Craig Ringer

@JackDouglas It's a Microsoft Xenix binary. Seriously. Runs on SCO OpenServer 5.0.5 in Xenix kernel personality emulation. I'm building a whole replacement system for it - finally - now, but only after convincing them not to build a replacement in Progress. They wanted to use Progress 'cos the guy who built the first one knew it, in fact it was the only other tool/language the ex-salesman developer knew.

Funky.

@JackDouglas As for job-moving, I'm lucky to now have the freedom to spec the technologies, so I'm building with Pg. Also with Java EE, which seemed like a good idea at the time.

Jack Douglas

@CraigRinger I saw that on your profile and considered asking about it, then thought better of it.

Craig Ringer

12:33

@JackDouglas so I'm not doing dead-end stuff. Thankfully. It was a near thing with UniVerse, I had to talk them down from buying some solo developer's VB6+UniVerse creation. His baby. I asked him if it'd run with a RDBMS and he started frothing about how nobody needs constraints and types, they waste lots of time and just get in the way of a decent developer.

@JackDouglas Even better, when asked when he was porting off VB6 to VB.NET he said "Never" and started ranting about how Microsoft ruined the only good things they'd ever made: Visual FoxPro and VB6. Thankfully the boss could be talked down from buying that one.

Small newspapers are interesting places!

Jack Douglas

@CraigRinger hilarious - actually I'm half with him on VB6 v .NET - apples and oranges, no easier than porting to any other language. You could have suggested Java :p

How does a small newspaper even afford 1 competent IT bod?

Craig Ringer

@JackDouglas BTW, re SQL Server notifications, I read something about that recently. Trying to find the SO question about it. The short version was "yes, but it's different and incompatible with each different SQL server version"

@JackDouglas You're making the pretty iffy assumption that I'm competent ;-)

Jack Douglas

ha ha - I have evidence - at least on pg. Looks like Remus has that covered btw

Craig Ringer

@JackDouglas BTW, re your mention of Oracle, I frequently wish that Pg had more of Oracle's managability features. Managing Pg is a PITA, especially prioritizing queries, controlling resource use, etc. No query priorities, no quotas, no nothing. Yet it's so much nicer, easier, cheaper and friendlier. I really must knuckle down with an Oracle express edition and learn it some time - it's so powerful, the usability is just so awful I'm put off whenever I try.

@JackDouglas Does anybody actually use SQL*Plus? Is there some "make it sane" wrapper with readline etc?

Phil

@CraigRinger i use it all the time with rlwrap

Craig Ringer

12:45

OK, so it just needs a wrapper. Good to know.

ConcernedOfTunbridgeWells

@CraigRinger Oracle offer SQL Developer as a freebie.

Also, yasql supports readline if it's still maintained, but it's some years since I last used it.

Phil

I used to use TOAD religiously, but have got used to SQL Developer now

Jack Douglas

@ConcernedOfTunbridgeWells yes, I held out using SQL*Plus for ages but now I only use it on the server

Craig Ringer

@ConcernedOfTunbridgeWells @Phil Advice appreciated. I know I need to make another foray into Oracle-land, if nothing else to improve my knowledge for when those weird "how do I do <x> in Pg like in Oracle" questions come in on the Pg list, and to have another perspective on problems.

Jack Douglas

@Phil how many times in the last week have you had to redock a window?

Phil

12:48

@JackDouglas I don't have problems with it

Aaron Bertrand

@JackDouglas Change tracking and change data capture can work but they have to be implemented on every single table. Maybe that's ok but seemed the op was looking for something much simpler.

Jack Douglas

@AaronBertrand do you know what he is talking about here:

DataContext => BindingSource. Not sure what would be considered cached. — Pluc 19 mins ago

Aaron Bertrand

Absolutely no idea. These kids and their fan-dangled new tech

Jack Douglas

:-)

Phil

13:13

Can you ignore questions? Bored of dba.stackexchange.com/questions/22534/… popping to the top

ConcernedOfTunbridgeWells

@Phil Can I add PEBKAC? Alternatively you could re-tag the question so the 'interesting' metric doesn't rate as high for you.

Aaron Bertrand

My last of three blog posts on splitting strings. This time I compare CLR and TVP: sqlperformance.com/2012/08/t-sql-queries/…

2

Jack Douglas

@Phil ignore terminology until the fuss dies down?

Phil

@JackDouglas excellent idea. ta

Jack Douglas

@Aaron you may be interested in skiming this conversation I had last week. Or you may not. I thought I should let you know it happened either way as it concerns you. tl;dr: she's not a bad sort at all just speaks a different language to us and got the wrong end of the stick on a couple of occasions.

Aaron Bertrand

13:47

@JackDouglas my comment about no guarantees on order, at least I think, was not meant about separating SELECT from FROM or SELECT from WHERE, but rather two operations in a query (e.g. a merge join and a hash join).

Jack Douglas

@AaronBertrand ok sorry

Aaron Bertrand

There are a bunch of scenarios where a join may lead to a certain plan (this has little to do with the SELECT vs. ORDER BY alias discussion)

Jack Douglas

it's still my understanding that it is best to assume noting at all about execution order though :-)

arghhh Postini are trying to force me on to Google Apps

Aaron Bertrand

And even separating those into CTE / sub-query doesn't force SQL Server to separate them. So to force the issue you can separate them into a #temp table then run the other part of the query against the #temp table. SQL Server can't optimize that into a single query. :-)

gbn

@Phil So tempted for Computer User Non Technical

Aaron Bertrand

13:50

@JackDouglas yes, that is true, though the language rules prevent you from writing anything that would take advantage of a bizarre scenario where SQL Server would somehow execute the SELECT first.

Phil

Le sigh. Why post a 1 line rubbish answer when I've explained in detail what the issue is?

Aaron Bertrand

@JackDouglas this is why WHERE alias = something won't even parse - SQL just won't let you try that, even if the underlying behavior at runtime might allow that to make sense.

Phil

0

Q: Character Set Database Migration

This is a follow-up question to a previous thread I had posted. We're in the process of migrating to a new character set. Now, I am a programmer and we have DBA's who will be performing the work but I'd like to get a little bit more information about the process. Our current character set is WE8I...

oracle migration character-set

Jack Douglas

@AaronBertrand I was trying to come up with a situation with sequences where it might matter but discovered there are rather strict rules about how they are used

Phil

Jack Douglas

13:53

@Phil Don't forget that downvoted rubbish answers are actually useful

it is worth the cost

Aaron Bertrand

Yes Sequences have a bunch of limitations. A lot of them were last-minute because of let's say "interesting" behavior spotted by MVPs during the beta.

Phil

Heh, should've used this character in my reply. Looks rude: Ѿ

Aaron Bertrand

@JackDouglas I am quite reluctant to down-vote when my rep is at a nice round number (ending in 5 or 0). That probably makes me a total weirdo.

@JackDouglas on the other hand, I am much, much more likely to find something to downvote if my rep ends in a 1 or a 6. :-)

Jack Douglas

@AaronBertrand hah, just find another 4 to downvote :-)

but yes, that is weird. I usually have no idea what my rep is

Phil

@AaronBertrand Proper OCD

Aaron Bertrand

13:57

@Phil no question. For a while I would re-word sentences until they contained a word count that ended in 5 or 0. I'm not sure what broke me of that habit.

I know exactly where it started, though. I should write a blog post about it.

dezso

@Phil why, you can edit something somewhere and you get 2 downvotable reps

let's see, I earned today reps worth 56 downvotes on SO

I have to find something to waste it on

Aaron Bertrand

@JackDouglas as for the conversation in general, I'm not sure what to do with the information. She was over-the-top abrasive when I was only trying to help. I don't need to give up my time helping people who are only going to aggravate me - whether that's just the way they are, or whether we are feeding each other's aggravations unwittingly.

dezso

Now here is this guy

0

Q: Need some help with this query get all status and their associated comments of me and all my friends

I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was totally wrong and lost :(. So basically is the same schema as the other post: 5 tables: default_users,...

mysql query mysql-5.5 query-performance

I think his schema is screwed up a bit

but I get headache every time looking at that

Aaron Bertrand

@JackDouglas I've marked several user accounts with this extension - only it doesn't mean "favorite" it means "steer clear."

Jack Douglas

I'm amused that you are using it for precisely the opposite purpose it was intended

Aaron Bertrand

14:10

@JackDouglas since I typically don't really give a crap who is asking the question, the problem I came across was this:

I would get into the same types of useless discussions with the same people asking bad or misleading questions. For a while I had a sticky note on my monitor reminding me of the users I should avoid bothering to help, but I just didn't check it. This extension makes it impossible to miss. :-)

bluefeet

morning all

Aaron Bertrand

Aptem was my first "favorite"

swasheck

14:21

@AaronBertrand sicky notes, eh?

Aaron Bertrand

@swasheck no, just one. The list was small but represented a very large portion of my frustration on these sites.

swasheck

do you still have it "for old time's sake?"

Aaron Bertrand

@swasheck I do, actually. But to answer your next question: no I will not take a picture of it and post it. :-)

swasheck

great anticipation as that was my next question, though i'd not actually ask it given the nature of the contents.

Aaron Bertrand

Okay, very easy to blur out the ones I might not want to know I've placed them on my list (the first two I really couldn't care less):

Craig Ringer

14:30

Personally, if I had a "sticky note" list it'd be: user\d+$ . People who don't create usernames seem t o be much more likely to drop a half-thought-out bad question, not follow up, and just abandon it. Also much more prone to re-posting 3 slight variants of it, etc.

AlexKuznetsov

@jcolebrand Hi there

Aaron Bertrand

Craig Ringer

Alas, they're the new users who need a hand if they're going to join the community :S

Aaron Bertrand

@CraigRinger yeah I'm torn on that - while probably true for a large portion, I don't think it's anywhere near enough to generalize.

There are plenty of bad users who went to the additional 5-second "hassle" of customizing the username.

swasheck

@AaronBertrand you have the penmanship of someone who types all day

( i keed, i keed )

ConcernedOfTunbridgeWells

14:32

@swasheck Mine's a whole lot worse than his.

Aaron Bertrand

@swasheck I used to have the best hand-writing in class, consistently, until I started using computers. Now it's really chicken scratch.

bluefeet

@AaronBertrand boy I am glad my name is not on there. You sure used to give me a hard time on my answers

swasheck

yeah. i only say it as someone who has the penmanship of a kindergartner

ConcernedOfTunbridgeWells

@AaronBertrand Mine was always the worst in class :D

swasheck

@bluefeet he only does that to make you stronger

@ConcernedOfTunbridgeWells mine too

Jack Douglas

14:33

@JakeFeasel I asked in the mod room and I was told to ask you to suggest SE host the service on mSO - if you are willing to do that, and spell out exactly how it would work, I think there would be a lot of support, particularly if we can work out how to make SQLFiddle and dba.se benefit each other by working together more closely. If you do post, please link in here...

Aaron Bertrand

@bluefeet on answers it's never personal (though Gordon Linoff has really been giving me a hard time lately).

Jack Douglas

@AlexKuznetsov I haven't seen @jco for a while

Phil

This looks alright?

0

A: Duplicate Customer Records for SQL Query from ODBC with Joins and Group Bys

You can use a windowing function to partition the data. If I knew your data better I'd rewrite it without a subselect, but the wall of text is making my head hurt ;) Here you go: SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY OM01U1.OM01015 ORDER BY EC01_LASMTCDAT DESC) as r, OM01U1.OM01...

Nasty schema

swasheck

@bluefeet check out this one

LEN makes me cry :-) — Aaron Bertrand 16 hours ago

Aaron Bertrand

@bluefeet so sorry if it came across that way. As @swasheck said, I can't let vague or ambiguous answers go, and I correct so that you think about it next time. No matter who you are. I've had run-ins with Skeet even where I haven't agreed with him (though I typically bow out because his massive following of lemmings down-votes me to oblivion on principle, not merit - he is the almighty Skeet, and therefore infallible).

bluefeet

14:38

@AaronBertrand he bugs me

swasheck

i dont take it personally. just remember to consider the source.

being a professional means handling and incorporating the expertise of others in a way that doesn't take things personally.

bluefeet

@AaronBertrand I appreciated it. I learned a lot from you calling me out on things like varchar without length or why use varchar instead of char....etc. It was all part of my learning process. I am still very green with a lot of this stuff

JNK

@AaronBertrand What's your issue with LEN?

Aaron Bertrand

@JNK there is some context missing. LEN makes me cry when it's not necessary, e.g. SET @foo = LEFT('x' + @foo, LEN(@foo) + 1); - you know the max length that @foo can be, since you declared it, so writing SET @foo = LEFT('x' + @foo, 256); is better. By orders of magnitude when that is a column and not a variable.

bluefeet

@AaronBertrand yea what's wrong with LEN?

swasheck

14:52

@AaronBertrand i can see that. would that create whitespace for strings < 256? or is that related to what you and brownstone were discussing?

Aaron Bertrand

In the cited example it may not be a best practice to hard-code the column width in the code, since the column width can change. But if the column changes you should be revisiting any code that references it anyway.

@swasheck no, nothing to do with padding. Just expensive and wasteful to check the length of every single value, when whitespace just gets dropped anyway.

bluefeet

0

Q: Analyising Implict CAST

I have an academic scenario, which I would like to know how to analyse. DECLARE @date DATETIME SET @date = getDate() SET @date = DATEADD(DAY, DATEDIFF(DAY, 0, @date-3), 3) This will round the date down to a Thursday. What I have been challenged on is to evidence where there are implic...

sql sql-server-2005 implicit-conversion execution-plan

Mikael Eriksson

STUFF is a little gem that makes things like that easier. No need to know the length of the string. Just stuff the stuff you need in there.

swasheck

@bluefeet HAHAHAHA ... is "academic scenario" the new term for "homework"?

@AaronBertrand fascinating. thanks!

bluefeet

@swasheck I doubt it is homework from Dems but I guess it could be

swasheck

14:56

@bluefeet didnt even check. just read the beginning of the description.

Aaron Bertrand

@MikaelEriksson yes, true. In this case STUFF wasn't necessary either.

swasheck

knowing when to use which tool is key in achieving performance gains.

my answer was like weeding your garden by setting it on fire, i guess.

Mikael Eriksson

@AaronBertrand It could be done with stuff and pretty neat to I think. Using the patindex expression to find the number of characters that should be stuffed with an empty string from the beginning.

Aaron Bertrand

@MikaelEriksson yes, it could be used, but I think it would be far less intuitive for newbs. It's not always about who can craft the most clever query. :-)

Mikael Eriksson

15:18

@AaronBertrand True, I guess you could be talking about answers like this and well... I confess to sometimes get carried away with XML. :)

Aaron Bertrand

@MikaelEriksson updated my answer and gave you credit. dba.stackexchange.com/a/22554/1186

@MikaelEriksson I actually think STUFF does have an advantage here, as you said. But the output for non-alpha strings (at least the way I structured the PATINDEX expression) is slightly different and may need additional handling.

Mikael Eriksson

@AaronBertrand Hehe. Now I really need to upvote. Done.

Aaron Bertrand

@Mikael Thanks!

Phil

15:51

0

Q: Good tool for simulating many threads doing INSERTS UPDATES SELECTS and DELETES on an ODBC database?

I've done load testing on a web application already using tools like JMeter, and tested my database for transactional integrity. But are there any tools for testing an ODBC connected database (like MS-Access) using just the database? I'd like to be able to simulate a real world situation of m...

tools odbc performance-testing testing

AlexKuznetsov

@JackDouglas Jack, I wanted to contribute a blog post. Can you help me out if it makes sense to you?

Jake Feasel

@JackDouglas What is mSO?

bluefeet

should I post a question/error with BCP here or on SO?

Phil

16:06

here

Lamak

@JakeFeasel Meta Stackoverflow

Jake Feasel

@Lamak ah, right

@JackDouglas hmm, hosting on mSO - what would that look like?

Jack Douglas

@JakeFeasel sorry, not hosting on mSO

I meant posting the suggestion on mSO - it's where the community has a chance to vote and give feedback on suggestions

Jake Feasel

ah ha

"posting" on mSO

Jack Douglas

@JakeFeasel If you can imply that the relationship might be good for dba.se I think it will be better received ;-)

Jake Feasel

16:14

well, I may go that route at some point but I think there may be other options available already. Thanks for the support!

jcolebrand

@AlexKuznetsov try loading this page and what do you see? dba.blogoverflow.com/wp-admin

Jack Douglas

@JakeFeasel sure - check back if they fall through. Shame though - I was thinking we could promote each other...

maybe we could anyway...

Jake Feasel

One of the things I'm planning on attempting in the near future is building tighter integration with SO / dba.se

regardless

This is my core group, and I want to promote you guys however I can

basically, I'm thinking of offering a "freemium" type of model, but granting all SE users who meet certain criteria a free premium account

for example - over X rep on dba.se, immediate pro account

or, over X answers referencing SQL Fiddle

It's going to take some time to build all of that up, but that's what I'd like to do

swasheck

what'd be the feature set of each?

Jake Feasel

just to be clear - none of the features that are currently available would become limited to pro accounts

those would all remain as is

swasheck

16:20

what dreams do you have in the pipeline?

Jake Feasel

Some ideas for pro account features - unlimited schema size

Jack Douglas

I've been racking my brains about if there is a way of getting tighter integration directly onto the site - perhaps via images

Jake Feasel

Immediate, online collaboration a la share.js

AlexKuznetsov

@jcolebrand it let me in, setting up profile now. Thanks!

Jake Feasel

@JackDouglas What do you mean?

swasheck

16:21

@JackDouglas that'd be handled from the SO side though, right?

Jake Feasel

@JackDouglas I've got openid user authentication now. My plan is to build a way that would allow you to associate your sql fiddle account with your SE account

Jack Douglas

@JakeFeasel so casual visitors can see the SQLFiddle directly in the answer here on dba.se

Jake Feasel

ah ha

Well, it works great as an iframe

Jack Douglas

@swasheck perhaps - I wondered if there is anything we can do without though

Jake Feasel

but of course that's problematic

swasheck

16:22

@JackDouglas what do you mean?

Jake Feasel

well, it might not be too problematic if you made a special case for SQL Fiddle, I suppose

Jack Douglas

.

Jake Feasel

@JackDouglas Thanks to some handy responsive CSS, the content shrinks down quite nicely if you put it in a smaller iframe window

Jack Douglas

sorry messing around

asciiflow.com/#Draw

example of stuff generated on an external site that ends up as an image on SE

Lamak

@JakeFeasel - In any case, whatever new features you add, I hope that your business model succeed. I love your site

Jack Douglas

16:25

works better in an answer because you can link to the generator directly

@JakeFeasel I hope but do not expect SE to be willing to give us that sort of personal attention - hence I was thinking about what we can do without their help :-)

@swasheck any of that make sense to you?

Jake Feasel

@Lamak Thanks. Though calling it a "Business model" is pretty generous at this point.

bluefeet

@Phil posted, hopefully it makes sense

0

Q: BCP error new the keyword 'as'

I am new to using BCP but I am trying to extract data generated via a stored proc. This is a continuation of my original question which was a stored procedure with temp tables. This procedure has grown a bit but the core is the same, generate temp tables, run dynamic SQL to populate the tables a...

sql-server sql-server-2008 sql bcp

Lamak

@JakeFeasel Business model idea?, dream?

Jake Feasel

@JackDouglas Interesting to think about. I could probably provide a URL that would be a generated image that showcased the core of the fiddle

wouldn't be that hard, actually

@Lamak Dream is probably most appropriate at this point

Lamak

@bluefeet In your question, are you posting your current query?, cause you are still using temp tables

Jack Douglas

16:30

@JakeFeasel that would be superb. It would need to be narrow but could be tall. The image link could then just jump you straight to the fiddle

Jake Feasel

@JackDouglas One problem I could see is fitting all of the SQL within the static box of the image

swasheck

@JackDouglas are you asking if there's anything in sqlfiddle (integration) that we could do without?

bluefeet

@Lamak I can post it. I didn't because it is roughly the same thing. gonna try a global temp table first

Jake Feasel

@swasheck I think we're trying to figure out if there is any way to easily embed sql fiddle content on SE

Jack Douglas

@swasheck yes, what Jake said - ie by changing the layout

Cade Roux

16:32

On the Math and CS sites, they do support a JS TeX markup thing, I would imagine they would support something - probably not dynamically loaded from another site, though.

swasheck

right - but that'd be enabled by SO ... not from the end-user, right?

Jake Feasel

@JackDouglas Changing the layout, but also presenting it as a static image, right?

Jack Douglas

@swasheck if it is on another site it is going to be much easier to get it here than if it is new

@JakeFeasel exactly

swasheck

@JackDouglas sure

Jake Feasel

@swasheck Imagine there is a button on SQL Fiddle that said "Link to this fiddle as an image" (or some such). Then you could just embed it within your answer on SE

swasheck

16:34

@JakeFeasel how would an image be beneficial?

Jake Feasel

@swasheck wouldn't have to jump to sql fiddle to see it, I guess

swasheck

yeah

Jack Douglas

@swasheck in some cases I'm imagining you would not have the code here - just on SQLFiddle

Lamak

@bluefeet - This can be a stupid idea, but what happens if you change as 'Response' by as Response?

Jake Feasel

@JackDouglas Hmm, that's probably not something you'd want to encourage, though

Jack Douglas

16:37

maybe not :-)

swasheck

forget it ... i just realized i got myself all backwards. i was thinking of the onebox in chat ...

Jack Douglas

it might depend where you are hosted though ;-)

Jake Feasel

@JackDouglas Heh, that's true

swasheck

@JackDouglas @JakeFeasel if you're part of the SO network then they could almost guarantee that as long as they're around, you're around (outside of "business decisions')

but of course if they wanted to do that theyd've just ported your code to MS stack

bluefeet

@Lamak nothing. I removed all of those completely leaving just the as varchar since I need it

Jake Feasel

16:39

@swasheck I suspect I'd have to rewrite my application tier as ASP.NET MVC before they'd host it for me

swasheck

@JakeFeasel fortunately i think that there are just a couple of people here who could help with that.

Jack Douglas

@swasheck code is not as valuable as Jake

Jake Feasel

Which wouldn't be a huge undertaking, since I am already using a similar MVC architecture

swasheck

@JackDouglas a great point

@JakeFeasel perhaps you could parlay this into a sweet NYC job!!

Jake Feasel

@swasheck LOL I already turned down a sweet NYC job with Codecademy

my wife was not interested in that idea (particularly since we have kids and like having a yard for them to play in)

Lamak

16:41

@bluefeet hm. Is casting the column on the the variable @ColumnName what you really want?, not the column columnName?

swasheck

you're solidifying my rank as "subjugated"

Jake Feasel

So, I'll work on getting an image-export feature in place, see how that goes

bluefeet

@Lamak yes unfortunately I need to cast on the @ColumnName since it is dynamic, that is getting replaced with the value in the column

Lamak

bummer. I don't know what might be wrong then. The last thing that comes to mind is adding QUOTENAME on your cast, so it ends with `CAST(s.[Q_4] AS VARCHAR(1000))

Jack Douglas

@JakeFeasel great - thanks

swasheck

16:49

@JakeFeasel autopost to stack.imgur

Jake Feasel

@swasheck Hmm, okay I'm not familiar with that, got a link?

meta.stackoverflow.com/questions/90342/… ?

Jack Douglas

@JakeFeasel another thing you might like to consider is a way of exporting pretty markup guys can cat'n'paste over here

does that sound do-able?

Jake Feasel

@JackDouglas Well, I do have plain-text result output, just for that purpose

Jack Douglas

I mean schema, SQL and results

Jake Feasel

and, of course copy/paste the SQL is easy to do

ah

yeah, that should be do-able - of course, it can be done now, but requires three copy/paste actions

and you don't get the xplan when you do that

Jack Douglas

16:52

@JakeFeasel that usually won't be needed

Jake Feasel

@JackDouglas Right, most of time

I might be able to output a clean, ASCII version of the whole shebang. Would have a nice retro feel to it, too

yeah, I like that idea

Lamak

@bluefeet - I'mt guessing that adding the QUOTENAME didn't help, right?

Jack Douglas

@JakeFeasel I love that idea

bluefeet

@Lamak nope and I just posted my full stored proc

Jake Feasel

@JackDouglas Sweet, I'll make it happen. Hopefully I can find some time this weekend to bang it out

Jack Douglas

16:56

[SQLFiddle](http://sqlfiddle.com/#!1/d41d8/415) schema:
...
SQL:
select 'A'
result:
| ?COLUMN? |
------------
| A |

but you can do much better than that probably

drat - doesn't preserve whitespace in here

Jake Feasel

Heh, ya I was thinking of adding some more of the ASCII formatting (pipes and dashes, etc...)

Something like that

Jack Douglas

@JakeFeasel do you know markdown well - I guess you do if you hang around here

Jake Feasel

@JackDouglas I've learned it pretty well, recently. Mostly for github readmes

jcolebrand

@JackDouglas you removed my ctrl-k nice formatting?

Jake Feasel

And here, too, ofc

jcolebrand

16:59

afk

Transcript for

The Heap™ – Consultancy ©®