The Heap™ – Consultancy ©®

@user3732505 Have you posted it as a question or?..

8:49 AM

@AndriyM Seems like they answered a totally different question.

@user3732505 Not if you don't provide it :)

user3732505

9:06 AM

@ypercube i got answer

Oh, OK. It's the "S1" one?

Jack Douglas

9:38 AM

@ypercube on that meta question, It'd be good to have an answer saying 'no this isn't a good idea' so that we can vote on it. Do you want me to post one?

@JackDouglas Yeah, that would be nice. I could post one, later, if you don't have time.

Jack Douglas

The third option is to switch the synonyn as you mentioned - having MV a synonym of IV is obviously daft

@ypercube thanks :)

A: Remove tag alias indexed-view for materialized-view

10:19 AM

0

The two terms are essentially about the same concept – materialisation of the result of a query. The differences are outlined in Jack's answer and I personally believe them to be inessential to warrant the split. However, I disagree with the current state of affairs too. An alternative to splitt...

Simon Righarts

Saw an answer claiming that all business logic should be in the app layer. Hrm.

12:06 PM

have a Q for the room whenever a SQL Server DBA is here

Q: Can we use mysql database for crore of data available in tables? Which database is better to use for huge data and more number of joins are used?

Crore of data ...

0

Kindly give answer with explanation? I used mysql database for 2 crores of data present in mysql table. And my application needs more analysis on this data with join queries and string matches. How can we solve this problem? Kindly give complete answer with explanation. Thanks

@ypercube thanks, closed

12:31 PM

@BrentO I prefer MangoDB myself (https://github.com/dcramer/mangodb), as @JasonNKyle said "MangoDB has more vitamin C than most SQL solutions".

Tweeted by marian_chicu on August 1, 2014 at 12:30 PM in reply to BrentO

Brent was mocking Mongodb

@JNK couldn't help not sharing your wisdom

no I saw

with amusement

@Marian do you work with trans replication?

@JNK yeah, I do work with it (translate that to: drop and recreate it every time we deploy a new version)

@JNK what's your ask?

1:14 PM

@Marian bear with me there's some background here

But I guess without background I'll just ask:

If you have an upcoming database change on the publisher

A largish (19m) row table is going to have 2 columns added and then update all the rows to populate those columns

would you have any concerns or recommendations?

is your replication created with option of replicating schema changes, too?

or you're dropping the article, and re-add it afterwards?

it replicates schema changes

and we aren't planning to drop the article

(this has already happened I'm just trying to see what someone knowledgeable would recommend given the information at hand)

It's a long time since I played with schema replication..so not sure how it's delivered to the subscriber; I'd suppose it's creating a new snapshot, right?

what I'd like to make sure is it's delivering only this article and not the whole db snapshot

well I found out it replicates the schema change

but the update apparently runs RBAR on the subscriber

The DBA™ was trying to say it was because I didn't batch the update statement

(to cover his own ass for not reviewing the migration scripts)

I googled and found a lot of references to the fact that big updates it's normally better to reinitialize

which we ended up doing but I had to stay up all night to get it working

1:32 PM

well, not sure there's a different approach, but the default update stored procedure created by the replication is row by row

checked one of my replicated tables and replication has created for it a procedure "[sp_MSupd_dboTable]"

ok, and this is not something that is like hidden knowledge right?

nope, it's the default behavior AFAIK

you can look at the subscriber, in the database, for each article there's a set of replication stored procedures

oh I did eventually

And I'm glad to hear you know that

only the snapshot delivery method can send them'all bulk

but after the replication has been initialized and it's working, I don't see any bulk method on the way, the default procedures are row by row

(based on the PK)

Q: regarding http://dba.stackexchange.com/questions/3552/how-do-i-output-mysql-logs-to-syslog

um, no

0

How do I output MySQL logs to syslog?. I dint understand this way of doing this . Specifically the part where rsyslog.conf is to be modified. Can you please explain that part and the changes that needs to be done there.It ll be really helpful. Thanks

mysql

Jack Douglas

1:59 PM

@bluefeet would you mind migrating stackoverflow.com/q/25078561/533832 so we can close it as a dupe?

@JackDouglas done

thanks :)

2:14 PM

2:33 PM

smoke monster... Bad mojo man

i miss that show

Anybody knows what to do with stackoverflow.com/q/25075786/298282

I think it should be either migrated or closed as not a real Q.

@YasirArsanukaev my hand just automatically downvoted and closed it

i've undone dat downvote

@YasirArsanukaev I don't think it should be migrated but I'll let a DBA mod weigh in

I hope you control your hand outside dba.se, SO

2:47 PM

A short question. Please someone assure me MSSQL gathers table stats system-wide OOTB as Oracle does via Automated Maint. Tasks. (I didn't configure the instance.)

sigh

I wonder where you got your list? — Aaron Bertrand ♦ 2 mins ago

@YasirArsanukaev Maybe he's thinking something in this line?

not sure if in Oracle you can do that

@YasirArsanukaev Yes, it does.

3:02 PM

@Marian Thanks.

@Marian I'd love to know. scratching the head Trying to find a use case for MSSQL's EXECUTE AS clause. (Not familiar with one.)

@AaronBertrand Shouldn't this answer be deleted?

It's clearly a bad copy of your list. Even the link and the last paragraph are copied from yours.

Q: UPPER() breaks my entire oracle SQL

any want to debug this huge query?

0

I am using the following query that works fine: SELECT COUNT (SPRIORITY)as iTOTAL,SPLANTSECTION,STYPE,SUM(LASTDAY) AS LASTDAY,SUM(TWODAYS)AS TWODAYS, SUM(P4)AS P4 ,min(SPriority) AS TOTALPRIORITY,MIN(LASTDAYPRIORITY) AS LASTDAYPRIORITY,MIN(TWODAYSPRIORITY) AS TWODAYSPRIORITY FROM (select REPL...

sql oracle union

@ypercube But if it's deleted, then FreshPrince's comment also goes and that was my laughter for the day

@YasirArsanukaev impersonation of different users in procedures (didn't use it much, either, but it appears it's used)

@billinkc True.

3:14 PM

@YasirArsanukaev At the last gig, we needed to be able to truncate staging tables. Truncate is a DDL operation and the DBAs would not grant us or the service account that ability.

Instead, they created a proc that would truncate a table based on parameters and that proc executed under the context of an admin account

It was also subject to sql injection that a nefarious developer could use to escalate their privilieges

I think. I'm not good at such, umm things

@billinkc Yeah, I get it. I think something similar I already encountered in Oracle, but I can't remember correctly.

is aaron still technically on vacation?

@billinkc A 'gig' is a "A job, usually in the entertainment industry." (as per meaning 2 in UD urbandictionary.com/define.php?term=gig) ?

@YasirArsanukaev he is an entertainer.. just ask @mmarie

ARE YOU NOT ENTERTAINED?

3:19 PM

Sings Nirvana "Smells Like Teen Spirit"

No, someone link the scene from Gladiator for me

tubes of you are blocked here

@YasirArsanukaev I've heard it (in the UK) used for other areas as well, not only entertainment.

►

Oh, but anyways, I was a consultant at my last position and while 99% of my billable hours were at a single client, I think of those engagements as a gig

Marian comes through again

@YasirArsanukaev @billinkc smells worse than teen spirit

@Marian @YasirArsanukaev ... extended cut

►

3:22 PM

I rode my bicycle into work today so this is probably more true than usual

:-]

i've got a bike. you can ride it if you like. it's got a basket, a bell that rings, and things to make it look good. i'd give it to you if i could, but i borrowed it.

No need, I love mine

I have white/sweaty grey tape on my handlebars though

@swasheck Is it just me or does that sound like a fragment of a rap song?

@AndriyM ooooooooooollllllllllllllllllllldddddddddddddddddddddddddddd pink floyd song

3:36 PM

@swasheck Ah, not rap then but still a song, thanks.

@billinkc How much does a decent bike cost?

@mmarie To me, hundreds but not thousands. My first "adult" bike was a prior model year and ran about 600

This one, was more than 600. The dread secret to spending money on bikes is that the accessories is where you can easily double the price

Helmet, 50 to 100. Shoes & pedals 50 to 200+. Clothes, heh

@billinkc + spandex =

Although when I was gearing up, I'd buy a lot of stuff from places like hincapie.com They'd have stuff like previous year's JDRF outfits made out of good quality material for the cheap

@swasheck don't drink too much h8teraid

Yes, hatteraid because I'm anti-headwear

There's a guy running around KC on an Elliptigo, and I'm tempted to go buy one

3:45 PM

ugh. i wish i'd never made my ridiculous comment about ssc

@mmarie sweet

@mmarie Did you ever see the video of some college. They mounted a treadmill on wheels or some such nonsense

@billinkc heh, no. i haven't seen that

@mmarie splain why this is better

@Kermit The truth? I hate bicycle seats because I have a big butt, and I love ellipticals.

@mmarie does this thing force you to move when you're going down hill for example, or can you coast?

3:51 PM

I think it's otherwise like a bicycle. It has gears and hand brakes.

@mmarie but the wheels are so small

they have different versions that have more gears and are better with steeper inclines

how much do they go for? $50-$60?

elliptigo.com/ElliptiGO-3C

$1,799?!?! HOLY $@%^ do you know how many boxes of diapers I can buy with that $?

3:53 PM

yeah, i know it's expensive. that's why i haven't bought one yet

if i were handy i would try to build one

@Kermit Shitload of $ to buy diapers? That's kind of ironic.

2

My baby fails to see the irony

His exact words are "aaaaahhh hhaaaa woooo"

I have hit the point where I am 29 with no dependents and super cheap rent. I already have emergency savings and fun savings and retirement savings at healthy levels for my age. I think I have concluded that I might as well have some fun.

@mmarie do it!

@swasheck Thanks.

4:01 PM

Oh, I'll just stop commenting on MySQL questions. Shoot me if I do it again.

I can't deal with users posting queries to optimize and then their comments reveal they want something different.

Locked and loaded and I've sighted you in

Awww yeah! I get to build a relational model. With all the Star Schema stuff I've been working on I'm super pumped to build a normalized data set.

@Zane what for?

Audit application. Employee/manager + custom roles in between.

interesting

Q: SQL Stored Procedure not handling error from nested stored procedure

4:21 PM

All sorts of things it needs to able to account for but I feel like I've got it pretty well figured out.

0

Really stumped by this one. I have two stored procedures, one nested inside the other. When the nested stored procedure is called, at the moment, it should error with a foreign key constraint violation and then rollback the earlier call to insert into the ProductLicene table. The nested proced...

sql sql-server stored-procedures

you lost me at nested stored procedures.

need more input for that one

nested SPs aren't too crazy but you need to understand how the stack works

@Zane that should be migrated here, no?

its fine either place I think

@JNK I find it adds additional complexity that isn't usually needed. When I'm tearing apart code there are few things I want to see less than nesting. Now I need to open another object and keep referencing between the two.

@MaxVernon I can see that. @bluefeet you want to make that happen?

4:42 PM

@Zane it makes certain code easier to use and maintain

It also makes it a LOT easier to test

Is google down for any of you?

You use facebook as your google?

??

nope

@Zane facebook is indeed down

(so say the news)

Mike Fal

Ummm...I'm logged in to Facebook right now?

4:48 PM

@MikeFal for many

Just my browser was f'ed up apparently.

Mike Fal

Yeah, those people don't count.

>.>

gizmodo.com/…

5:13 PM

@Zane what's up?

5:32 PM

I suspect "give it a try!!" is unnecessary, since its implied that anyone asking a question is highly likely to try the recommendations offered. — Max Vernon 20 mins ago

Migration. In at lunch and can't link. Can someone else post?

@Zane Too terse. Cannot comprehend.

@MaxVernon That's Rolando's trademark. Like snark is Kermit's.

@ypercube yah, I know. Snark is far more entertaining.

6:01 PM

microsoft.com/en-us/powerBI/solutions/demo/qna/…

Homophones story: iwritetheblogggs.com/2014/07/24/…

^ dumb

@billinkc wow. that thing sucks

No, QnA is the next "big thing"

all of my Q had no A

or maybe my Q were too complex

6:12 PM

@swasheck try using ANSI standard SQL syntax

Does it not work without Silverlight?

that's the big drawback, is you have to know the model to write a good question

so MS sells it as this great quick tool so you don't have to be good with data to get info

but it's not

@ypercube need silverlight apparently

I'm old - is fiberchannel still a fast thing or are there faster networky things?

A: How to physically/logically separate integration layer and data warehouse and is that necessary?

6:30 PM

boom

0

Most database systems use the database level as the highest logical hierarchy. However, Vertica only allows a single database to be running at a single time. This means that the logical design takes place at the schema level. For example, a traditional database system may have databases such as c...

@ypercube FWIW I try to avoid imposing mod privileges on any content that involves me directly, so I don't think it's my place to actually delete it with a binding vote. I also didn't down-vote, and think that one of the other mods should mod-message the author.

@Kermit will every one of your vertica answers be announced with a "boom"?

@Lamak glad you noticed

@AaronBertrand OK. I've flagged it, so mods will see it

Someone may already have flagged that answer.

6:32 PM

@Kermit of course I did

@AaronBertrand are you back from vacation?

Don't you feel the sun beating against the clouds? Of course, he's nearly back to us

@swasheck no, Monday

@AaronBertrand then ignore me until monday

Wow SSMS seems to have forgotten all of my saved Servers.

Isn't that lovely.

6:43 PM

Tasks, Export MyServerList.regsrv

Would the Previously Registered Servers option come into play here? Never touched that one

@Lamak I'm guessing yes :/

it does seems so

Q: How to physically/logically separate integration layer and data warehouse and is that necessary?

could we move this q to Database Administrators ?

2

On our testing DWH server, we're using Vertica Community Edition. We're very pleased with ETL process and queries performance. We import data from few sources (Informix, SQL Server, MySQL, Google Docs) putting it in one schema, with prefix bussinessProcessName_stage_ At the end of ETL process t...

database schema etl data-warehouse vertica

The plagiarized answer has gone. System works.

Pity that Aaron had to lose all the rep but that's the price he's gotta pay

Mike Fal

6:54 PM

@billinkc ERROR!

@billinkc maybe he'll learn to not plagiarize next time!

yes. apologies for my involvement.

shameface

@Kermit Doubt it. It's like every answer he gives links back to this other Aaron on sqlblog.com

@billinkc I think I saw some linked to sqlperformance.com too

convenience is bliss

still. i dont like to be an agent of stress or division.

6:59 PM

meh, whatever

swasheck, agent of Stress and Division. Part-time agent of SHIELD

3

sooooo ... how's the lake?

Available for weekends, parties and bar mitzvahs

@billinkc GUARDIAN OF THE GALAXY

I was unfamiliar with the graphic novel until npr edumacated me on it

7:01 PM

Glad I'm still on vacation :-)

@PaulWhite just the one?

@AaronBertrand Everything I've ever written if you like :)

@billinkc LOL

@PaulWhite once again, I'm on vacation. :-)

this is like a soviet chat board

The chair is against the wall.

@AaronBertrand Ha!

7:03 PM

John has a large mustache

The penguins swim north for the winter.

Current status:

what in the world is this syntax

UPDATE CustomerAccount
        SET MeteredBalance = (SELECT MeteredBalance FROM CustomerAccount WHERE SystemId = @systemId) + @amount
        WHERE SystemId = @systemId

Nice cottage literally 20 feet from the water

Gosh that looks fantastic. (the beach, not the SQL syntax)

7:07 PM

@AaronBertrand which lake is it, again?

@JNK oh yuck, that is really ugly and unintuitive

@swasheck Lake Nipissing

@AaronBertrand But there are animals in that water. I don't think that duck has a diaper on it either

geez I step away for a minute and a bunch of things are deleted

@JNK a bad one

@bluefeet sorry

7:08 PM

that's from the code in the nested SP error handling Q

Lake Nipissing

Lake Nipissing (French: lac Nipissing) is a lake in the Canadian province of Ontario. It has a surface area of 873.3 square kilometres (337.2 sq mi), a mean elevation of 196 metres (643 ft) above sea level, and is located between the Ottawa River and Georgian Bay. Excluding the Great Lakes, Lake Nipissing is the third-largest lake in Ontario. It is relatively shallow for a large lake, with an average depth of only 4.5 m (15 ft). The shallowness of the lake makes for many sandbars along the lake's irregular coastline. The lake has many islands most of which are protected under the Protection of...

@JNK Oh yeah, did you see my error handling?

IF @@Error = 0
BEGIN
	IF @@RowCount = 0
	BEGIN
		RETURN 1
	END
	ELSE
	BEGIN
	    RETURN @@RowCount
	END
END

RETURN @@ERROR

@bluefeet Modened comments

I grew up in North Bay

@billinkc wow, @@rowcount will ALWAYS be 0 there

And @@ERROR

7:10 PM

@AaronBertrand ah. that's right. every year that you go there i think we have the same conversation due to my tired brain. i have an aunt and uncle who own a cottage on a "Skeleton Lake" (which i can't find on the map but exists in google) near Huntsville

Huntsville is nice

Ok I'm going to go see if I can find some duck poop to swim in

2

@JNK Where dd you find that?

enjoy

(There are literally 40 ducks, including about 10 babies, hanging around this cottage since we got here on Saturday)

foie gras

3

A: SQL Stored Procedure not handling error from nested stored procedure

7:15 PM

3

Catching errors with a call stack like this using @@Error can be problematic. It's a lot more reliable to use TRY/CATCH The basic format is: BEGIN TRY <BEGIN TRAN> ... do stuff ... <COMMIT TRAN> END TRY BEGIN CATCH <ROLLBACK TRAN> ... do error stuff like re-raise the error to outer scope ... ...

@billinkc that's goose isn't it?

Do I look French?

Foie gras

Foie gras (/ˌfwɑːˈɡrɑː/, French for "fat liver") is a food product made of the liver of a duck or goose that has been specially fattened. By French law, foie gras is defined as the liver of a duck or goose fattened by force-feeding corn with a gavage, although in Spain and other countries outside of France it is occasionally produced using natural feeding. Foie gras is a popular and well-known delicacy in French cuisine. Its flavor is described as rich, buttery, and delicate, unlike that of an ordinary duck or goose liver. Foie gras is sold whole, or is prepared into mousse, parfait, or pâté, and...

@billinkc yeah. kinda

@JNK Did you ask because you were just amazed by the syntax and wanted to share, or do you want feedback about its correctness and/or whether the obvious improvement is valid?

Also, our surname of Fellows is an Anglicization of g'fellars (something like that) so vive la france!

@PaulWhite I'd just never seen it and was amazed/disgusted

7:23 PM

@JNK this smells horrible. wanna whiff? shoves in face

@swasheck exactly

@JNK I see. I know it's SO, but is it worth pointing it out in the answer?

it might be but it's sort of a deck chairs/Titanic situation at this point

wow

#Facebook is not a Law Enforcement issue, please don't call us about it being down, we don't know when FB will be back up!

Tweeted by LASDBrink on August 1, 2014 at 4:37 PM

good grief

7:24 PM

@JNK Understood.

Finally made up my mind about the indexed/materialized tag issue.

+1 There seems to be enough distinction (to me) to make it worthwhile having two tags (with two helpful tag wikis). — Paul White 1 min ago

@PaulWhite And will we need another tag for Postgres' materialized views - if they differ from Oracle's?

@ypercube I don't think there's a perfect answer. I weighed up all the answers and comments before deciding which I thought best, on balance. I don't know about IBM or PG.

I don't know much about Oracle's either.

But we are talking about a tag with 53 questions.

I don't see what problem does the splitting solve.

Well currently, if you tag materialized view you get indexed view, with a very SQL Server specific tag wiki.

Yes, I agree, something must be done about that.

7:40 PM

If the synonym hadn't been created, and both tags had good wikis, I doubt there'd be much support for adding a synonym.

That's the way I see it, anyway.

The way I see it is that SQL Server specific fits my needs. Let the other DBMS's eat cake.

:)

@Zane how very american of you

4

jcolebrand

@swasheck you take that back! :p

hahaha

So I've decided the new way to remove people from my list is massive misuse of #

#Maplesalmonwithhomemadesalsaandbrownrice#tatesamazing has put me over the edge. This person has gone to far.

7:55 PM

We should start using hashtags here #Newwayoftalking #TheHeap #Zaneisgonnalostit

I went to far once but I didn't like the scenery. I'm more of an ocean kind of guy

@billinkc bahahahahahaahahah

@billinkc har har har.

...

@JNK #swimmingInDuckPoop

8:00 PM

nvm I figured it out!

had SQL Sentry licensing issue

powers that be finally paid up

Q: How to cast Date from varchar to DATETIME

Wouldn't it be nice if StackOverflow could automate closing-as-dupe varchar to datetime questions like this one?

0

Hey I have column called DATE_Hour in my table which is varchar and have values like 'Tue, 29 Jul 2014 14:00' .How can i convert that date in to '2014-07-29 14:00:00.000' Thanks

sql sql-server sql-server-2008

I wonder how many questions there are on SO that have that (or a very similar) title.

But all the unique snowflakes out there would no longer be unique

@billinkc #rotflmao

@billinkc If only summer could return to StackOverflow

blogs.sqlsentry.com/aaronbertrand/updated-sql-2014-books-online

Some holiday!

9:28 PM

@PaulWhite - the halloween protection is implied since it's an update on a CTE?

@MaxVernon Updating the CTE directly allows the optimizer to see that HP is not required. Using a join makes the issues more complex, and the QO cannot assume HP will not be needed. Long story.

cool. thanks!

I suppose I could look at the plan for this to find the answer, but would I be correct in assuming the QO would choose to do an eager spool to implement HP? Is that a valid assumption, or are there multiple ways the QO can implement HP?

There are multiple ways (see my articles) but an Eager Table Spool is certainly common.

thanks.

10:19 PM

What a helpful fella:

@KirkLogan I edited your question. Please let me know if any of that information is not correct. — Kermit 15 secs ago

it's a tarp

Troubled Asset Relief Program

Troubled Asset Relief Program ?

The Troubled Asset Relief Program (TARP) is a program of the United States government to purchase assets and equity from financial institutions to strengthen its financial sector that was signed into law by U.S. President George W. Bush on October 3, 2008. It was a component of the government's measures in 2008 to address the subprime mortgage crisis. The TARP program originally authorized expenditures of $700 billion. The Dodd–Frank Wall Street Reform and Consumer Protection Act reduced the amount authorized to $475 billion. By October 11, 2012, the Congressional Budget Office (CBO) stated that...

It's a play on It's a trap but with intentional typo