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

JNK

1:11 AM

you guys can tell i don't do much injection :)

ErikE

5:05 AM

Was hoping for some rollicking good discussion of DB design. I guess I'll just have to imagine up something. :)

Sathya

7:19 AM

My software asset management team just informed me Oracle 10g is an "open source application". the heck, dudes?

Simon Righarts

7:37 AM

Ahahaha what.

Sathya

@SimonRigharts just received an email.

backstory: I was "asked" to help out on an another project.

so my TL sent them a mail asking for licensing details so that it can be approved. That was the reply I got

Simon Righarts

That is pretty special. Did they manage to conflate Oracle DB and MySQL?

gbn

@JNK You can fix this with SET SQL_MODE = 'ONLY_FULL_GROUP_BY' . And other wierd MySQL brhvaiour. dev.mysql.com/doc/refman/5.5/en/…

@MarkStoreySmith Shame he talks a lot of pants though

Some good stuff, but some plian useless or dangerous

Simon Righarts

It's also possible that they're using Express Edition, which is free (albeit with more conditions than a Hollywood pre-nup)

gbn

And 'morning all

Simon Righarts

7:46 AM

Evenin'

Sathya

@SimonRigharts I have no clue what are they smokin'

morning

Mark Storey-Smith

8:07 AM

Morning chaps

Simon Righarts

9:38 AM

Quiet morning, you lot all trying to work through last night's hangover or something?

gbn

@SimonRigharts "working".

Simon Righarts

I'm playing openTTD, vacation is a wonderful thing

ConcernedOfTunbridgeWells

12:08 PM

Afternoon all

Simon Righarts

Evening

Hmm. It's 1AM. Should probably go to bed

catch you all tomorrow

ConcernedOfTunbridgeWells

Nice, balmy evening in the fatherland there?

Goodnight

Simon Righarts

not exactly wonderfully warm, but not cold either

was a high of about 22°C today

JNK

1:05 PM

@gbn agreed on Pinal Dave. A lot of his stuff is super duper basic, and I've seen one or two things that were basically just wrong

gbn

2:19 PM

@MarkStoreySmith I'm flattered. TLC applied dba.stackexchange.com/q/12337/630

Mark Storey-Smith

@gbn Bravo, just what I had in mind :)

+1 primarily for reference to meatsack

gbn

@MarkStoreySmith I was going to use "long pig" but wasn't sure who'd get it

Mark Storey-Smith

@gbn I'll keep an eye out for that appearing in a future answer :)

ConcernedOfTunbridgeWells

There's always the good old standby 'luser'

gbn

churchofeuthanasia.org/e-sermons/butcher.html

ho hum.

JNK

2:24 PM

thanks for answering that @gbn

gbn

he he. The Q or "what is long pig"

^ probably NSFW. We don't have a firewall yet. company is too new

Ben Brocka

2:46 PM

Is there any particular reason to use an implicit join over an explicit join? I always explicitly join and tutorials I read usually implicitly join

jcolebrand

7

Q: Is it really more secure to go through a 3rd database to connect two databases using the same login?

We have three databases on the same SQL instance: a production database containing private data which is used by desktop software, a web database for a public website which needs data from the private database, and an intermediary database which contains a few views and stored procedures from the...

She also asked on meta how to put a bounty on it, I suggested she become familiar with this room, so you guys may want to let her know how to check the backlog so she can see ya'll were trying to give her love like 12 hours ago :p

She's still a few hours from bounty, I believe

JNK

@BenBrocka - i think it just used to be the syntax to use

Rachel

@jcolebrand Thanks :) I assumed the chatroom here was as dead as the one on programmers

jcolebrand

Wha? Already? That was FAST!

Rachel

I'm a stack-exchange addict at work >.<

Ben Brocka

2:48 PM

@JNK ah, this is an old tutorial...but it's a SS 2005 hack so that's to be expected

jcolebrand

Welcome Rachel, don't let the neckbeards in here get your goat.

Ben Brocka

@jcolebrand the second you think someone should be in chat, they are

Do you have brain powers you need to tell us about?

gbn

@jcolebrand I'm presentably clean shaven, thank you.

jcolebrand

@BenBrocka and yet someone tries to betelgeuse me and nothing ...

@TimStone ping

Tim Stone

Yo

gbn

2:50 PM

@BenBrocka Laziness, bad habits

jcolebrand

was it you or josh with the garbage disposal story?

@TimStone re: chat.stackexchange.com/transcript/message/3315225#3315225

Tim Stone

Josh was the unfortunate victim in that story

Hahah

jcolebrand

hahaha, damnit, I'm not drafting him in here

@BenBrocka ok, so the backstory to this side conversation:

Our buddy Josh, who is one of the highest-by-volume chatters on chat.meta, along with me and rchern and atwood, was giving rchern some flack one time and then his garbage disposal goes apeshit.

so the urban legend is that once you get the diamond, you get crazy powers ;-)

I'm really not giving the story justice tho, so assume it was funny when it happened, and if you really wanna know get @TimStone to find the story in the Tavern on the Meta

Ben Brocka

@jcolebrand Josh chats a lot? Noooo. I'm pretty sure Cog Sci's chat only started up because he never shut up

Tim Stone

Ahahah

jcolebrand

2:53 PM

@BenBrocka Yes, that guy. ;-) Ask him about his disposal ;-)

gbn

@jcolebrand His Java or .net developed a memory leak?

jcolebrand

haha, memory leak, more like a stuck bit

Ben Brocka

In UPDATEs, OUTPUT comes between SET and FROM, right?

gbn

@BenBrocka Without checking MSDN, can't be sure...

Josh

@jcolebrand I believe you're looking for this (CC @BenBrocka)

rchern Strikes Back!

Nov 12 '10 at 15:29, 2 minutes total – 21 messages, 6 users, 1 star

Bookmarked Nov 12 '10 at 15:34 by Yi Jiang's Proble_

jcolebrand

2:58 PM

@BenBrocka I don't think so ... run it with a test table ...

Ben Brocka

Apparently it is

JNK

3:26 PM

anyone know of any issues with increased log growth in SS 2008r2 after SP1 upgrade?

I got a 500GB db that had a 670GB log file in simple recovery mode after like a day of normal activity (deletes/inserts)

Ben Brocka

Apparently you can make a scalar variable a table?

JNK

huh?

Ben Brocka

Based off a tutorial: declare @updated_ids table(TicketNo int, UniqueID int, itemNo smallint);

JNK

thats a table variable

not a scalar variable

gbn

That's still a table

JNK

3:32 PM

table vars are nice but have limits

don't use them for anything more than a few k rows

or anything important

gbn

table variable vs temp table is quite common on SO and DBA.SE

JNK

yeah

when in doubt use a temp table ;)

Ben Brocka

Ah, didn't know the difference. What's the limit on table variables?

JNK

i think table vars and ctes are both overused

theres a number of them

Ben Brocka

Sounds like I need a temp table though

JNK

3:33 PM

no indexes

Ben Brocka

I meant by rows

JNK

no stats, optimizer assumes it's a single row

not affected by transactions, i.e. you can't rollback a tablevar delete

Ben Brocka

I'm only using it to hack an upsert. I do need lots of rows though

JNK

not visiible outside t he current scope

you can see it in an inner scope but not an outer scope

so if you create a tablevar in a subproc, the outer proc won't see it

gbn

allow me to find a Q or article

Ben Brocka

3:34 PM

Only need it within one proc and it dies with the proc. I'll stick with temp tables anyway

gbn

52

Q: What's the difference between a temp table and table variable in SQL Server?

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between the two? I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memor...

sql-server

JNK

temp tables have some overhead though

i mean if it's something small that you will refernece a lot a table var makes sense

they are normally memory-only and if its like 1000 records you don't need an index anyways

gbn

See @MartinSmith answer to that Question

Ben Brocka

3:45 PM

well the way this is set up it's supposed to be 200 or less per query right now but I'm not sure

@gbn thanks

JNK

as long as you don't need any indexes or constraints it should be OK for your scenario

Ben Brocka

Nah, it's just a list of updated rows from a staging table

JNK

then a table var should be fine

I normally only use them for testing myself

another limit is you need to declare them

so you can SELECT INTO one

Ben Brocka

What's the N before a string (e.g. N'String')? Specifying an nvarchar string?

gbn

"NATIONAL"

but yes, nvarchar literal

jcolebrand

3:51 PM

does it really stand for national variable character

Ben Brocka

2

A: N prefix before string in Transact-SQL query

This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. See Article #2354 for a comparison of these data types. Unicode is typicall...

Apparently?

jcolebrand

@gbn do we want that migrated over?

gbn

dunno

also:

2

A: searching sql DB table for japanese terms

Need an N to make the string literal unicode. select * from jtable where searchterm = N'ろくでなし' Without the N the 'ろくでなし' is implicit varchar and is seen as '?????' See my related answer about khmer text for examples of why: Khmer Unicode, English and Microsoft SQL Server 2008 results in ques...

4

A: TSQL: Any benefits for explicitly specifying NVARCHAR in a string?

Only if the string contains unicode characters The string is implictly converted to nvarchar when the passed into the stored proc. However, before SP execution, it's a literal varchar ("character string constant") without N prefix. So, if you Japanese names, you'll need "N" to make it a "unicod...

jcolebrand

@BenBrocka I think this one as well is a good candidate for here

gbn

@jcolebrand yes. SQL standard is "NATIONAL VARYING" according to my ORM tool's generated SQL

Ben Brocka

3:53 PM

@jcolebrand I agree and flagged both

gbn

SQL

| file_ext = .sql }} }} SQL (officially , often ; sometimes referred to as Structured Query Language) is a programming language designed for managing data in relational database management systems (RDBMS). Originally based upon relational algebra and tuple relational calculus, its scope includes data insert, query, update and delete, schema creation and modification, and data access control. SQL was one of the first commercial languages for Edgar F. Codd's relational model, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Bank...

(there is an anchor there to the string bit)

jcolebrand

odd that it's not "International"

JNK

N ternational?

Ben Brocka

Name must have been picked by an Ntern

Wait, SO is only 120~GB? nickcraver.com/blog/2012/02/07/stack-overflow-short-on-space

jcolebrand

Yarr, if that much

Ben Brocka

4:05 PM

Huh. I guess it is just text

@MarkStoreySmith Oh god, your starred comment about AV on DBs. Even we don't have AV on our DB servers. I had to double check though...

Aarthi

pokes head in

Mark Storey-Smith

@BenBrocka I'm ambivalent on whether it should be installed on DB servers at all. What I'm 100% on is that I need visibility of the configuration!

jcolebrand

@Aarthi tugs shoulders to pull body in

Aarthi

@jcolebrand ! (hugs) I came here 'cause....I kinda missed the savvy stylings of jco.

Ben Brocka

@MarkStoreySmith I'm just glad we don't have it scanning. I've noticed intermittant slowness on very low-access systems so I wondered if it was AV. We don't have any installed on the DB boxes so it's not that.

jcolebrand

4:19 PM

@Aarthi ;-) Hey I hope you don't mind (I can't imagine you would) I shared the 1/2 twitter convo with JackD so he knew what I was thinking stancewise

Aarthi

@jcolebrand No worries, I don't mind so much. Hell, you could share the whole thing -- I feel much the same way about @Jack

jcolebrand

@Aarthi I'm gonna use part of it as a meta post, just not sure which meta. Or maybe a google+ post. Or something. But you've heard me out on that ;-)

So what's shakin bacon?

also, who opened reddit on my computer?

Aarthi

@jcolebrand Not much. Just appointing protems and stuff. The usual.

jcolebrand

@Aarthi cogsci?

Aarthi

@jcolebrand four sites, actually, including cogsci, mma, and poker

HEY

I can't talk about protems in here

jcolebrand

4:23 PM

@Aarthi duh duh duh...

Aarthi

:o

jcolebrand

lol, you can after you appoint them

Aarthi

you're trying to trick me into telling @BenBrocka stuff :P

jcolebrand

you only said you were going to, I didn't ask for hames ;-)

ahahahahahaa

nah, I merely opened cogsci.stackexchange.com/about to wait

Aarthi

I SEE YOUR DASTARDLY PLAN

:P

Tim Stone

4:23 PM

SECRETS! gets popcorn

jcolebrand

No no, I need to laugh cos I opened this don't click, really and ... onions man ... onions

Aarthi

What the---! @TimStone is everywhere.

It's true

He is

It's scary.

4:24 PM

Altho, there's probably something else to that...

silly twitter DMs

ConcernedOfTunbridgeWells

@MarkStoreySmith I've seen a couple of occasions where it's broken SQL server to the point of corrupting data. The problem is more than just performance.

JNK

what when did tim stone get in here

Aarthi

He never wasn't. :o

jcolebrand

@JNK he like has a master key or something

I keep telling him to apply himself and get a diamond ...

JNK

to our hearts!

Aarthi

4:29 PM

@JNK Hee! A+

:D You guys seem fun.

wait now aarthi too

from diy

I want to go to these Chinese tea fields ...

@JNK yeah, she went and got HIRED by SE

Tim Stone

@jcolebrand Meh, I did the whole moderation thing for a while...I'm more a behind-the-scenes kind of person now.

Although if Josh goes and gets himself a diamond I might have to play game just to not feel excluded. :P

Mark Storey-Smith

4:31 PM

@ConcernedOfTunbridgeWells I can well believe it. In a perfect world I'd be dealing with servers that no-one ever logs in to and wouldn't feel a need for AV but...

Ben Brocka

@jcolebrand purdy!

Mark Storey-Smith

Hence I tolerate so long as I can see the configuration and manage the exclusions.

I wouldn't want to be the guy that said no to AV, then caught a cold :)

jcolebrand

@TimStone That's what I'm telling you. But you should get an elusive team member # ;-) :p

Ben Brocka

@MarkStoreySmith our situation might be one where AV might be warranted (WITH exclusions for DB files), but still...I trust me more than I trust the AV in that case

And I didn't say no to AV so no blame for me >>

Aarthi

@jcolebrand wait what? no i was already hired before I was on DIY! :P

jcolebrand

4:33 PM

@Aarthi I thought you were just interning >.>

Aarthi

ugly lies spread by yoda

I was never an intern; I was always a full-time hire.

Tim Stone

@jcolebrand She was so hired that she had to open the door for me one time, I think!

jcolebrand

@Aarthi or by the whole KAOS initiative...

JNK

I may do a walk by of the SE offices in NYC

since im not far

and be like "WHAT YOU DON'T KNOW ME?!?!"

jcolebrand

@JNK aye, you should.

but wear the HAL 9000 eye

JNK

4:35 PM

im like 2 hrs from nyc in connecticut

yeah ill bring a cutout to put over my face

Tim Stone

Security is pretty tight in that neck of the woods. ;)

ConcernedOfTunbridgeWells

@MarkStoreySmith There are a couple of issues - first is the performance, and the second is the I/O interept drivers. Even if you configure the AV software to ignore the files the drivers are still present.

jcolebrand

@JNK That constitutes pretty far in my opinion

JNK

not here

i have a 1hr commute to work anyways

i can't afford to live closer

CT is expensive

3 br 1500 sf house in the town I work in starts at around $500k

and property taxes on that will be around $20k a year

jcolebrand

good god man. I rent a 2br 1400 sf apartment for 1k

JNK

4:38 PM

yeah

its more reasonable upstate so most folks work near the coast and live further inland

jcolebrand

loft style, downtown, walking distance, everything but lights and internet included, gated community, covered (indoor) parking

Aarthi

@jcolebrand My half of my 900sf 2br apartment is 1375

ConcernedOfTunbridgeWells

Property tax is fairly steep. - Council tax on a property of that value wouldn't be that high in the UK.

Although $500k would barely get you a hovel anywhere near London.

JNK

its high here but most of it goes to schools which are pretty much stellar

jcolebrand

@Aarthi if you know technology more than you know community building I can offer you a job if you move closer to mama and papa ;-)

JNK

4:39 PM

i used to live in the south and public schools in CT are like private schools down there

jcole you are in TX?

jcolebrand

@JNK reason #17 this country is horked

Aarthi

@jcolebrand pfft hahaha. that's a tough question. I dunno how well I know either, though i'm turning in the direction of the latter.

jcolebrand

@JNK Shreveport LA

@Aarthi not a bad direction to turn under the tutelage of Spolsky.

JNK

LA isn't bad

jcolebrand

he's like this guy of computing/social networking:

JNK

4:41 PM

but you know I like being near NYC

i think neil needs some guidance guys

0

Q: Best practices for Database Maintenance Plans

Are there any best practices for configuring a Maintenance Plan in SQL Server 2008? Currently, I'm removing database backups and transaction logs greater than 40 hours old, then backing them up. Problem I've seen is that the transaction log is still very large. Should I be including a Shrink D...

sql-server-2008-r2

Aarthi

@jcolebrand I need to read through JoS one weekend. Maybe this month, since I have to be a miser.

JNK

"Should I schedule database shrinks"

What are you doing, Neil? Step away from the shrink, Neil.

jcolebrand

@Aarthi me too >.<

Aarthi

@jcolebrand awww.

So, hey, @jco, how is your back?

jcolebrand

@Aarthi Much better, thanks for asking. six week followup in about ten days, then hopefully I start PT and then maybe I can get in on a spin class.

Aarthi

4:48 PM

Nice! :D I'm glad you're recovering nicely. And, I'm glad you're no longer pained. :( Pain is bad.

jcolebrand

Oh, the pain was gone when I woke up from surgery

all I had then was a sore back from the muscles being retracted

Aarthi

Still, remaining pain-free is the key aspect. I'm glad that you're much better now. :D

jcolebrand

that was NOTHING compared to what it had been, which I'm told was not that far a cry from "natural childbirth" from my mother in law, who has had both.

@Aarthi ty.

Aarthi

eep

JNK

@jcolebrand - what sort of issue was it if you don't mind me asking?

jcolebrand

4:58 PM

L5/S1 disc degeneration

JNK

ouch

jcolebrand

Additionally there was a bone spur when he got inside

JNK

you use your spine a lot is my understanding

jcolebrand

Went from mildly annoying to omgwtfwhathappenedtomyeffingleg in about a month

Aarthi

eep

JNK

4:59 PM

glad its better then!

or betterish

jcolebrand

Oh, I was at work the Tuesday after my Wednesday surgery.

Thanks for the well wishes :D

Aarthi

\o/

well i'm off to lunchtimes. /waves see you all later :D

Tim Stone

5:16 PM

@Aarthi Bring us back food!

Rachel

5:53 PM

@gbn I am a bit confused about your answer to my question http://dba.stackexchange.com/q/12337/6173

Do you have a minute to explain it to me?

jcolebrand

You really didn't have to offer a bounty so soon you know

Rachel

@jcolebrand I'm impatient, what can I say :)

gbn

@Rachel yep

what is unclear? (Not "all of it hopefully")

jcolebrand

@Rachel haha, well I encourage you to answer a Q or two so a bounty can be given you for work well done. heh

Rachel

@gbn my question was about if the intermediary database provided any additional security, so I was a bit confused about your answer to use schemabinding

jcolebrand

6:01 PM

@Rachel as I read the answer, he's a bit of sidestepping that issue and showing how it can be done with no intermediary database and provide stronger integrated security.

gbn

schemas, not schemabing

schemabinding is an added value withing a single database

jcolebrand

Which alternately provides that the answer to your question is "yes, it only serves to complicate things"

gbn

@jcolebrand yes

jcolebrand

granted, it's simpler to do it the way it is architected now, but provides many issues that the "more proper" method should prevent.

gbn

alternate solution

Rachel

6:02 PM

@jcolebrand Thanks, that's easier to understand :)

gbn

The key is "ownership chaining"

jcolebrand

@Rachel keep in mind it took me two re-readings of both points ;-)

gbn

@jcolebrand you have a useful quick edit to make it better? Or addyour own?

Rachel

@gbn I'm not a DBA so you speak a lot of greek to me :)

gbn

@Rachel I'm just a code monkey too

Rachel

6:04 PM

I was hoping to drop some extra complexity from the existing design, because I have to duplicate it for a 2nd site

jcolebrand

@gbn not really, no.

I was only speaking to the specific Q she asked here.

Rachel

@gbn I've seen some great sql answers from you on SO, don't lie :)

gbn

@Rachel SQL = Code, no?

JNK

GBN is at a minimum a code APE

jcolebrand

I personally would prefer to see the obfuscation of a second layer removed and the usefulness of integrated security improved in this situation

@JNK a great ape?

JNK

6:05 PM

he may even be in the code genus homo

hes a grape ape at least

Rachel

green or purple?

JNK

mullet colored of course

Rachel

talk of grapes is making me hungry... off to lunch

Thanks @jcolebrand and @gbn :)

jcolebrand

Aye. Also, answer some Qs so we can give you reps ;-)

gbn

6:18 PM

@Rachel: I've pimped the answer. HTH

JNK

the new rims on the answer are awesome

gbn

wait till it's dark. it's got a KIT light too

JNK

sick

Nick Chammas

@gbn It now looks like a Rolando answer with the fancy headings.

gbn

@JNK I have a mullet. So did the Hoff.

@NickChammas nah, too short.

JNK

6:27 PM

Also it's about an RDBMS

;)

Nick Chammas

@gbn true, but the the style is there

lol

gbn

@JNK cheeky yet subtle

JNK

eventually "MySQL is a non-relational store" will become a meme

gbn

@RolandoMySQLDBA: you need to defend your flat file store here...

2

Rachel

@jcolebrand I have to know answers before I can post them. I'm pretty sure you wouldn't upvote an answer that said "Hi, I'm just posting because jcolebrand said he'd give me rep if I did" :)

jcolebrand

6:39 PM

I'ld have to delete it after I upvoted if I did.

haha

JNK

looking up answers is a good way to learn though

someone asks a Q, you look it up to find the answer

jcolebrand

Also, nobody tell her that I'm really not a DBA, that I just make a good janitor

JNK

then you know it!

jcolebrand

2

A: What's the difference between a temp table and table variable in SQL Server?

This link is in my bookmarks. Very good explanation.

Rachel

I look for that link anytime I encounter that question

I know it's always one of the top few on google :)

jcolebrand

6:42 PM

I hate it when ya'll flag these, because I never know to delete it or dismiss the flag as helpful. It's a comment, sure enough, but I can't convert it to a comment because the author isn't attached.

Rachel

So mark the flag as helpful and ignore it :)

jcolebrand

But it's a shitty answer. :-\

Rachel

True... would be better with a copy/paste of the important bits

JNK

@gbn do you know anything about exec plans for querying linked servers?

say I join to a linked table, how does it get the results back?

jcolebrand

@Rachel I just like to maintain attribution. I suppose I'll destroy it for now.

gbn

6:44 PM

@JNK You need to ping @Rory, He just lost 15% of his rep on SO

@JNK "it depends"

JNK

don't know if I know rory

jcolebrand

@gbn would you be a dear and link me to his Q so I can superping him?

gbn

5

A: Performance implications of using OPENQUERY in a view

Summary Let the linked server do as much as possible. It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server Long The key factor is where the query runs. In this case, it is a trivial SELECT so all rows from a table will be sent down the wire. It doesn...

Rachel

@jcolebrand I'll edit it :)

jcolebrand

@Rachel it still wouldn't have author attribution

gbn

6:45 PM

This is the migrated one stackoverflow.com/users/8479/rory