The Heap™ – Consultancy ©® - 2013-11-27 (page 1 of 2)

12:36 AM

Paul - do you have any opinion on this query using far memory by virtue of the MAXDOP = 0 setting? Could far memory access be causing a visible slowdown, or am I totally barking up the wrong tree with that line of thought? — Max Vernon 27 mins ago

@MaxVernon What do you mean by "far memory"?

Both systems have eight schedulers by the way

If you mean local/foreign memory NUMA-wise, then no, the optimizer does nothing with that.

Q: is there something wrong with my sql expression?

1:29 AM

0

I'm using Crystal Report v8.5 to display the list of the clients who're still have balance. Below is the SQL expression I wrote SELECT "c"."firstname","c"."lastname" "transaction"."record", "transaction"."balsheet" FROM "dbproj"."dbo"."c" "c" "dbproj"."dbo"."transact...

sql sql-server crystal-reports

I don't even...

@AaronBertrand vtc that junk

@bluefeet that makes me do this - I'm starting to lose appreciation for the "must demonstrate" close reason - I mean these folks don

't know what they don't know, and it's not fault their they don't understand (yet). I think she literally paused a YouTube video and tried to write the code that the guy on the video said would work, and just did it wrong...

I am more flexible with certain questions but that one is just horrible.

There are many times that I will answer and VtC.

A: Closing Etiquette: Why Do Some Answer *and* Close?

1:45 AM

Yeah I can see both sides

39

If I know the answer (or can be helpful), I'll give it. If the question isn't supposed to be asked, I'll vote to close. I see it as generous, not hypocritical - after all, I didn't make the rules. I don't see anything wrong with this. We're being strict, but generous. Additionally, I think it ma...

well I'm off for the night. have a good one

ciao

@AaronBertrand I find it generous to answer and still VtC - helping the person out is never a bad thing. Good point in there about "what if it doesn't get closed"

2:08 AM

Uh oh

> This site is currently in read-only mode; we’ll return with full functionality soon.

More SQL Server 2014 issues I suspect

So much I want to say about the conversations in the TAP program, but can't.

@AaronBertrand :-)

@AaronBertrand who is in read only? DBA and SO look okay to me?

A: is there something wrong with my sql expression?

The outage only lasted about 2 or 3 minutes. I only noticed because I was trying to save changes to an answer.

This stuff drives me nuts. Aside from removing the inline comments, how is this answer different from mine?

0

You don't need to put in any of the quotes, even though CR will show it with a single quote " ' " around every table and field name. You can also shorten the whole thing up by not specifically calling out the tables in the SELECT statement but rather using the aliases that you declare in the "FRO...

2:30 AM

@AaronBertrand People are annoying, what can I say? Just don't let it eat you up inside too much, it's only SO ;-)

Gave you a +1 anyway

Thx

enderland

jeeesh do you guys ever sleep? :D

And yeah, people are f*ing annoying

Simon Righarts

@enderland We're always watching. Except on weekends.

@enderland :-) At times I do. Actually about to go read me some more Voddie Baucham in a minute and turn this blasted invention off.

Q: Best Solution to have a Live copy of a Database when replication is not an Option

2:42 AM

Egads, there isn't even any code and I don't want to even try to correct the formatting

0

Hi Recently I had to implement transactional replication on one of the database to have a live copy of that database on another server for reporting purposes. while configuring replication I realised that a lot of tables didnt have any Primary Keys on them, so I could not Publish all the tables I...

sql-server sql-server-2008-r2 database-replication log-shipping

Simon Righarts

@AaronBertrand Some things are better left as 'someone else's problem' ;)

adieu

(Also, why are you highlighting things like "reporting purposes" and "a better solution" as code? This is really annoying for me to read; I can only imagine what kind of havoc it wreaks for someone using a screen reader. — Aaron Bertrand 5 mins ago

3:08 AM

@PaulWhite yes, I was referring to accessing memory in a remote numa node. I was thinking, aside from the obvious plan differences, that perhaps a portion of the overall difference in execution times between the two machines might be due in part to the query accessing a remote numa node. The UAT machine is only a single numa node, whereas the PROD machine is 2 nodes. Could be a 30% difference or more in memory access times.

@MaxVernon Never been able to measure that difference myself.

3:24 AM

@PaulWhite I've never had the opportunity to do scientific measurements; just have anecdotal experience on large memory (512GB) numa boxes, and of course the docs which mention setting MAXDOP to be at or below the number of cores per numa node. AMD has an interesting doc where they state that crossing two numa nodes has a read duration 1.3x longer than accessing memory in the local node.

developer.amd.com/resources/documentation-articles/…

I'm not sure if Intel has a similar document.

<TomTom> Pffft, 512GB, my phone has more memory than that

@MaxVernon Yes I have heard the theory before. Adam M and I spent a while testing it on large hardware, and we couldn't measure it.

Ha!

so you saw no difference or could not test it?

@MaxVernon We saw no measurable, repeatable differences.

If there was an effect, other effects were always much more important.

We stopped worrying about it and moved on to things that actually made a difference :)

Very interesting. And good to know. I wonder if a Hekaton table stretching across numa nodes makes it measurable.

@PaulWhite lots of bigger fish to fry, no doubt!

3:28 AM

@MaxVernon Maybe. I sort of lost interest. I think if memory latency ever becomes a limiting factor, I probably finished tuning the workload a while back.

I guess memory is so damn fast, it doesn't really matter as long as the data is somewhere in memory.

@PaulWhite Thanks for taking the time to chat with me about it. I'm sure you are very busy!

Gavin Payne did a presentation at Bits last year where he investigated memory latency across NUMA nodes. His measurements were that remote memory access was up to 4 times as slow, but I have to agree with Paul, take that with a grain of salt. If I can go from the bedroom to my office 4 times faster, I've still only saved about 2 seconds.

You can see the slide deck here but to make any practical measurements against a real workload, good luck.

Also bear in mind that people only show demos that show the intended effect. I was disappointed that the effort we put in was essentially wasted. It was good geeky stuff, but ultimately ineffectual.

Still laughing about the TomTom comment.

Well ok smirking

@PaulWhite no kidding! Do you know how many hekaton samples I've tried to create where it actually works slower than non-memory-optimized? Sadly, I'll leave it at far more than half.

Good repros are hard.

2

Ok I am going to catch up on Homeland with my wife and pack for Jamaica.

@AaronBertrand Same here. I have been blaming myself, my hardware, and CTP software so far.

And I am a fan of the technology formerly known as Hekaton.

3:38 AM

I think it's squarely the latter. Just don't think all the optimizations are there yet, and there's also very little guidance on bucket count choice (which can have a huge impact in my testing)

@AaronBertrand thanks for sharing that.

Windows Azure Family OLTP Database In-Memory System or whatever it's called now.

@AaronBertrand Everything I've seen (which is not much) has 1,000,000 buckets... lol

@AaronBertrand Excellent article on HK with TVPs btw. (Talking of bucket count)

@PaulWhite wow, thanks!

3:39 AM

HK?

I enjoyed it. Hadn't noticed that opportunity at all.

HK = Hekaton

@PaulWhite well, it's of limited use. On larger tables it doesn't perform quite so well. At all. I'm still trying to get a decent sample going for the follow-up.

@MaxVernon See sqlperformance.com/2013/11/t-sql-queries/hekaton-twist

Ok really going now. Ciao folks

@PaulWhite thanks.

3:41 AM

Ciao.

ConcernedOfTunbridgeWells

ciao

@PaulWhite reading that now. This place is such a fantastic resource.

10:21 AM

NUMA memory latency is much, much less of a big deal than it used to be back in the early days of NUMA box vendors like Sequent. Back in the bad old days remote memory was something like 50x slower than local.
Now it's much faster - the default memory configuration on most two way NUMA boxes is to interleave pages across both CPUs. This is slower on average than a NUMA aware application but not slow enough to cause a significant issue in most cases.

11:52 AM

@campagnolo_1 why on earth does Crystal do that? With single quotes those look like string literals, not identifiers, and some forms of that syntax have been deprecated anyway (documented here and elsewhere). I hope this isn't how it works in the most recent and future versions (if they are still being produced). — Aaron Bertrand 10 hours ago

@AaronBertrand In my experience Crystal reports does place double quotes around everything. It is really annoying

12:31 PM

That is pretty stupid.

On a more positive note, it took a long time, but they deleted that crappy answer and marked my flag as helpful .

It was Andrew Barber. There must have been some serious deliberation.

stackoverflow.com/questions/20200693/…

It was nice to meet you @bluefeet

:-)

well I'm not dead but I am very surprised that they marked that flag as helpful. It kinda goes against everything they always say on meta.

@bluefeet it does

Deep down though it was not an answer to the question. It's all in how you word the flag. We saw that before where your NAA flag was rejected but my custom flag was not.

That was a link only answer, and the SO moderators still don't see those the same way and there's no official stance. Flag success depends on wording and which mod gets to it.

Q: Highly upvoted and accepted link only answer

12:46 PM

wow, I am actually surprised by this.

1

I ran into this question today: How to execute an SSIS package from .NET? The accepted answer with 32 upvotes is just a link. Its a pretty old one too, which is probably why it hasn't been picked up by anyone. Is this flagable? I know in the good ol' days SO was a bit more lawless, but how shou...

support stackoverflow link-only-answers

The answer was accepted and had 32 upvotes. They deleted it, instead of attempting to get the user to expand on the answer.

Oded deleted an accepted answer? Wow.

Winds of change...

Do you think that maybe a comment should have been left to the person who answered explaining why it was deleted, stating that if they expand on the link-only, then it could be undeleted? I would be surprised if the person who received 32 upvotes doesn't post to meta asking why the answer was deleted. — bluefeet 1 min ago

1:02 PM

So, I'm still not clear about link only answers. I remember clearly that in a meta discussion I was saying that those aren't answers, and the consensus was that they could be if they in fact solved the question. So I'm confused with this:

> I went ahead and converted that answer to a comment, as we would do today if flagged.

Yup, there is no formal stance on what is done with the link only answers. Some mods will delete/convert to comment, others delete, other will decline those flags. Just like @AaronBertrand's flag that was marked helpful. I get that it is about how you word a custom flag, but it is really annoying that one mod does one thing and another takes different action

Q: can anyone help me in migrating this sql code to postgresql?

very annoying

the part that really bothers me is when they all seem to be on the same line on meta, piling up on other opinions, and then they go and do something that is not the consensus

enderland

1:24 PM

stuff like that is handled differently on all sites, too

on Workplace if you post a "non answer" our mods are pretty good about deleting them (even if ti's not blatant spam)

ypercube

VoteToClose:

-4

-- temp table for testing if object_id('tempdb..#temp') is not null drop table #temp create table #temp (SID1 int ,Sub varchar(10),ct int) if object_id('tempdb..#temp1') is not null drop table #temp1 create table #temp1 (SID1 int,Sub varchar(100),ct varchar(100),is_flag int) insert into #te...

sql sql-server postgresql

On the other hand, in borderline cases like this one, I'm sure that several mods deferred because they weren't positive they wanted to decline my flag. So it seems (from my limited exposure anyway) that they won't jump on declining a flag the way they'll jump on acting on it and marking it helpful. This one took more than 30 hours.

Q: Concesutive Rows to fetch from SQL server table

1:45 PM

sigh two very wrong answers

0

sql-server sql

2:20 PM

still haven't received a tweet back

2:33 PM

@FreshPrinceOfSO that doesn't surprise

A: How To Update Random ID to a table in mysql?

@bluefeet i thought this would turn into the Tet Offensive

ypercube

I wonder how this answer got accepted. Long lines of code, good and fine for SQL-Server. But the question is tagged with mysql

0

You want to only update 1 value at a time right? In order to UPDATE existing values, you will need to generate a table with the possible values and a selectable value, something like a counter, which I added into the @table. The reason for this is you might not always have sequential IDs with no...

Q: Connect to SQL Server from internet

the amount of bad questions coming from SO is too damn high

0

I want to connect to my SQL Server that placed at my office from my home through the internet. I'm newbie, I tried various things from Google like opening port 1433, add inbound rule 1433 & 1434 for SQL Server, but when I tried to connect from SQL Server Query Analyzer with the public IP and por...

sql sql-server

3:13 PM

Are you asking questions or answering the OP's question? — FreshPrinceOfSO 6 secs ago

3:27 PM

@AaronBertrand the site seems fine now. It was probably just my junk browser.

finally, someone's alive

Phil

Looks

@Phil you drink much?

Thanks for your advice, Your query kinda blew my mind so I went with the count approach, but its good to know there is a more efficient way instead of count when I really need it. — Pearce 1 min ago

I would normally say I'm turning off the SO but I can't quit it (yet)

@billinkc SO is definitely not a hit it and quit it.. it's just an abusive relationship that you're comfortable with

3:35 PM

@FreshPrinceOfSO stockholm syndrome

@swasheck i'll star that

Does anyone know if Trace Flag 4199 is always necessary to enable query optimizer fixes? For instance, if a hotfix comes out that requires TF 4199, is 4199 still required when that hotfix is rolled-up into a service pack?

@MaxVernon KB Number?

@MaxVernon or i suppose you could install the hotfix, cycle the service, and DBCC TRACESTATUS

@FreshPrinceOfSO I'll star that star. Get some Escher starting going on in here.

this is not a question about a specific KB, just in general. 4199 controls so many fixes, I'm wondering if it should be applied generically.

@swasheck I was really thinking about this for @Kin's situation with the query optimizer in this question: dba.stackexchange.com/questions/53726/…

I think @Kin should at least try DBCC TRACEON (4199) to see if that fixes his situation on PROD.

Kin

3:47 PM

@MaxVernon I will try it to see the effect of it

I have already opened a case .. but the support engineer is working off US hours .. so its a bit frustrating to not be directly working with him ...

@Kin cool. SQL Server 2008 R2 RTM has the switch available which leads me to believe the fixes covered by it are only enabled if the flag is on.

I assume Paul would have mentioned it to you if he thought it mattered, so I wouldn't hold my breath!

@Kin tell me about it. At my past job, one of our suppliers was based in Israel where the time zone thing is an issue, but also, they have Thursdays off, and work on Sunday, just to compound the issue!

Kin

@PaulWhite tried his best ... and I am convinced by his answer as well.. but I cant explain the issue to management .. as they will be sleeping if I go into tech details ..

@Lamak Tell me of Yacon

Yacón

The Yacón (Smallanthus sonchifolius, Syn.: Polymnia edulis, P. sonchifolia) is a perennial plant traditionally grown in the Northern and Central Andes from Colombia to Northern Argentina for its crisp, sweet-tasting tuberous roots. The texture and flavour are very similar to jicama mainly differing in that yacon has some slightly sweet resinous and floral (similar to violet) undertones to its flavor. This flavoring is probably due to a sweet substance called inulin, as replicates the sweet taste found in the roots of elecampane, which also contains this substance. Another name for the ...

I feel this plant should taste like bacon based on name alone

@billinkc I can tell you that it's not something that you see in Chile

Q: MS Excel - Handling polytherapy medical cases

um, should this be kicked to SO ?

0

I am working with building a patient database of patients diagnosed with a specific condition. This condition is usually treated with one drug at a time (monotherapy) but at times doctors will opt with a polytherapeutic approach (multiple drugs at the same time). Now, so far with the monotherapy ...

database-design excel

3:57 PM

@Lamak But, but the other article said the andes goes to Chile

@bluefeet Might also be a superuser type of thing

@billinkc it does, but its still not a mainstream food here. So, only for hipsters

Phil

@FreshPrinceOfSO Only at breakfast time

@Lamak And yet you aren't eating it... <3

can someone flag this comment as obsolete? I have no more comment flags for the day -- stackoverflow.com/questions/20246652/…

@billinkc hey, I'm no hipster man, I use android

@bluefeet flagged

4:00 PM

@Lamak thanks

@Lamak I am fairly certain that if we ever meet as part of the introductory hug, you'll follow it up with a solid slug to the guts

@billinkc can you explain what a "slug" is?, so I can understand my future actions

@Lamak slug: a solid punch

@billinkc Oh, ok.....then I might do that

PUPUSA ...

YUM

4:13 PM

@billinkc ??

@Lamak see a mod who disagreed with the deletion of that highly upvoted link only answer

I don't think I agree with the conversion of this answer to a comment. Just because an answer consists of little more than a link doesn't necessarily mean we should remove it or convert it to a comment. The fact that this was accepted and highly voted showed that people found value in it. The link was not broken. I typically decline flags that ask us to remove older answers like this. — Brad Larson 35 mins ago

more confusion about what to do

@Zane I just love that when I was googling on the term merge sql server that the first hit was msdn on how to use, the second was @AaronBertrand on why you shouldn't use it

@bluefeet Let SO slide further into the toilet?

@bluefeet yup, I'll end not doing anything

@Zane like a greasy thanksgiving turd

Q: Is SSRS 2012 significantly better than SSRS 2008?

4:14 PM

Anyone want to VTC?

0

I am facing serious problems with SSRS 2008 - the complex sophisticated reports are behaving odd and things are just not working as Microsoft documented - too complicated to describe them here. The development tool is VS 2008 BI Studio while the target server is 2012. Are there any significant im...

reporting-services

I need more SO points so I can VTC. Is it just the BI subject areas, or are most questions left with no chosen answer?

I would answer more questions on dba.se, but there aren't many BI questions over there.

@mmarie IIRC there was a time when there was deliberation about creating a BI site or not, and if DBA should handle those or not

@Lamak Well, there are tags on DBA, but there are not many new questions.

@mmarie iVTC

@billinkc I'm really surprised this one isn't coming up higher in the results yet

@swasheck lovely visual, thanks

4:18 PM

@bluefeet hey, @Zane sets 'em up and i knock 'em down. plus ... drugs

@swasheck drugs, smugs you always blame the drugs

@AaronBertrand It's the sixth in my search results. Simple-talk is 3, pinal is 4 & 5

@bluefeet the last few days, yeah.

Ah, Pinal

@billinkc and 7, and 9

UBIQUITIPINAL

4:19 PM

@swasheck are you sure it has just been the last few days?? :)

@bluefeet monday, tuesday, and whatever today is, yeah.

@swasheck negatory ghostrider, utexas, codeproject (ew) and wiki round out my top 10

@billinkc sorry. hyperbole --- for dramatic effect

i actually dont even know your search terms

@swasheck It wouldn't have surprised me mind you

Which is why I had to check

:D

Q: Eliminating/Rounding values in SQL Server 2005

4:23 PM

sigh

0

I need to round the values from 9.5178 to 9.517 or to just eleminate 8 from 9.5178. Please help. Regards, Rashid

sql-server

I don't think they're so much guidelines as they're basic requirements of a question. — FreshPrinceOfSO 11 secs ago

@bluefeet i threw the kitchen sink at that question

@FreshPrinceOfSO did you ever figure out the a-hole question?

@swasheck which one are we talking about again?

@swasheck You're a classy guy Seth

@FreshPrinceOfSO the twitter comment

ypercube

4:26 PM

@swasheck One of the dozens that he comments and/or votes to close.

@Zane takes one to know one

:)

@swasheck nope. you'd hear it here first

You clearly need a CASE statement UPDATE T SET Col = CASE WHEN T.Col = 9.5178 THEN 9.517 ELSE T.Col END FROM dbo.MyTable AS T; — billinkc 12 secs ago

g'morning folks

4:28 PM

@MikeWalsh what's so g' about it?

@FreshPrinceOfSO we're alive

@FreshPrinceOfSO it's just a g thang

@MikeWalsh so you claim

Q: Concesutive Rows to fetch from SQL server table

How does a question show up in the close queue if no-one has VtC'd it?

0

sql-server sql

@FreshPrinceOfSO Good to see you too :)

4:30 PM

@billinkc no, I have many rows in table from where am getting these values. I can not use CASE statement. — Raja 21 secs ago

@MikeWalsh I don't know you exist. You could be a NSA robot

holy cow

espnfc.com/news/story/_/id/1630257/…

warning: palm to face ratio insufficient

Have any of you used SVN?

@Zane I did use it.

Then I quit

4:34 PM

@Zane @billinkc has

I now use Git

@Zane i have to use it here. it's what our ancient devs love

@Zane svn .. hit it and quit it

@Zane svnbook.red-bean.com

I love it's ability to error out and never recover forcing me to reload all objects.

4:36 PM

o.O Define error out + never recover.

@billinkc Is that the second our third website ever put on the internet?

Fifth. Geocities was the 4th

@billinkc Ran into a colision and crashed. Now it thinks it has a lock that doesn't exist and I can't remove. Now I can not update or check in anything.

She's done.

A lock? Are y'all explicitly using locking there and not the copy-modify-merge model?

@FreshPrinceOfSO Yes it is a lovely day out. How about for you, my friend?

4:39 PM

@billinkc No.

Russia doesn't like Louis Vutton

Like I said there is no lock.

I didn't lock anything.

No one else see's a lock.

It just crashed mid update and is unable to recover. All objects are listed as locked for me and no-one else.

@Zane What client are you using to interact with SVN? By collision you mean, conflict, yes?

@MikeWalsh not a day goes by that fighting the embeddedness becomes more useless

@billinkc I think so I saw the error message for about a quarter of a second before the appliaction bombed.

Tortoise

4:41 PM

How about: how old is the SVN backend you're using? I wonder if that's the legacy backing store (berkleydb) sh*tting the bed

Needs to be more of these

@billinkc No clue. However if it were I would imagine the problem wouldn't been only occuring on my local folder.

@FreshPrinceOfSO Listen, kid, we're all in it together

@MikeWalsh the only way we're in it together is if we don't follow the herd

@FreshPrinceOfSO I was just talking about happiness.

4:44 PM

@MikeWalsh that's just an idea

@FreshPrinceOfSO I agree

@Zane If you right click in the folder you were working with, TortoiseSVN menu, Release Lock does it report anything locked?

@billinkc It does not.

I've tried that.

Latest version of svn?

Make that, latest version of tortoisesvn (it's good at nagging about updates)

Also like I said it's every single file. Even ones in folders that I've never touched.

4:48 PM

Your computer is a special and unique snowflake

My computer hates my fuckin guts.

Pardon my french but it seriously hates me.

It takes 30 minutes to reboot and crashes multiple times a day.

Do you have a .svn folder in each folder under source control or is there only a single .svn folder at the base level

@Zane It or IT ?

@Zane Sounds more and more like this may not be an SVN issue endemic of a bigger issue. I propose you copy everything of value to a network location and accidentally set a magnet on the machine

@FreshPrinceOfSO Edit resolved.

@billinkc I'm getting a windows 7 update next week so I'm hoping that fixes it.

We have such a hideously bastardized version of XP it's sickening.

Mike Fal

4:55 PM

I feel like one should be shocked by companies insisting on using 10 year old software.

But my cynicism won't let me.

ConcernedOfTunbridgeWells

We are getting a bunch of good updates.

Windows 7 and SQL server 2012

At least I'm not working fore on of our mainframe places.

@James Yep. Still on.

5:20 PM

Any of you have to work friday?

No sah, I will be in Jermaica

@Zane My boss might make me do a few things for a couple clients. Mostly because he slacked off this week and spent too much time answering questions on dba.se

@AaronBertrand Really? tha'ts cool.

We go for our anniversary every year (except last year, because we were having a baby). Same resort, same villa, etc.

This time we're taking the baby.

@AaronBertrand awww

5:22 PM

@AaronBertrand Sounds great

We'll see. Nicole is already having a heart attack thinking about Madeline near the pool.

She's been taking swim classes, but still...

@AaronBertrand I was going to yell at you.. But then I thought "well.. she is the first." ;-)

My assumtion is you have more of a she'll be fine sort of approach?

@Zane nope, took the day off

@AaronBertrand lucky you

@Zane yeah, I mean it's not like we're going to grab drinks and tell her to go have fun at the pool

5:25 PM

@bluefeet Plans? If I recall corectly you're family is located on the East Coast.

@AaronBertrand Lol right.

@Zane cooking for 20 tomorrow

Got my old SVN solution removed finally. The only problem is my desktop seems to have gone with it somehow....

This machine is going to haunt my dreams.

sooooo ... not trying to start a flame war here, but can someone provide me with a good use case for a trigger? i'm just so unfamiliar with them.

Attempt to enforce RI across databases

Sending an email every time a row is added to the table

Wait, no strike that one

@AaronBertrand i'm a bit protective of our kids. i'm not helicopter but i see all of the dumb things i did as a kid and all of the ways i could have died doing them ...

and i worry

5:35 PM

You're not helicopter?

@AaronBertrand whoopsie. a helicopter parent

We had a table with no primary key, or any distinguishable row identifier

@billinkc so is this a good use case or simply a use case?

@billinkc knew that was a joke

They (data modelers) claimed the model was correct and we insisted we needed something to make the rows unique so the UI could you know, update rows

@swasheck sure, I use instead of triggers all the time to validate business rules before bothering to try to insert or update (letting SQL Server raise an exception, for those business rules that can be enforced at the table level, is quite expensive). This way I can prevent the insert/update and all of the associated logging and simply raise an error.

5:37 PM

They won that (and most every modeling) argument via decree that they were right so we had to suck it up and deal with it.

@swasheck found this

Can't always enforce those business rules in the application - sometimes the code is frozen, sometimes it would require modifying multiple distributed apps, sometimes it's just more efficient to check the data where the data rests

A: SQL Triggers and when or when not to use them.

11

The Wikipedia article on database triggers presents a good overview of what triggers are and when to use them in different databases. The following discussion is based on SQL Server only. Using triggers is quite valid when their use is justified. For example, they have good value in auditing (k...

@AaronBertrand cool. thanks.

Our approach was to put a view on top of the table with a row_number function to fake the uniqueness and used an instead of trigger on the view to figure out what to update. Godawful, bloody hack but it worked

5:39 PM

> Triggers have types. DDL Triggers and DML Triggers (of types: INSTEAD OF, For, and AFTER)

And you can't always perform business logic in stored procedures, because, well, ORMs (and people who think stored procedures are for suckers).

not to be nitpicky, but in sql server isnt FOR the same thing as AFTER?

Yes, for and after are the same.

(because i'm not good at triggers i'm really just trying to learn)

DDL triggers are only after. There is no INSTEAD OF DDL trigger.

5:40 PM

@AaronBertrand thus the ROLLBACK in all of the DROP examples on MSDN

The thing that sucks about a DDL trigger and no ability to use BEFORE/INSTEAD OF? Imagine you have a naming convention you're enforcing, and someone creates an index that violates your naming convention. The DDL trigger doesn't even check until after the index is created. So then it rolls back. Now imagine the index took 12 hours to create. It will most certainly take even longer to rollback.

yowza.

This exact same process is why I prefer instead of DML triggers. No data modification should take 12 hours, but no matter how long it takes, it's wasteful to wait until after you've performed a modification, and logged it, to check if you've violated some rule, just so you can press undo.

Remus is not a big fan of INSTEAD OF triggers. At all. I think it's because he's never been bitten by an AFTER trigger. Maybe he just doesn't use triggers at all. Sadly, we can't all live without them.

i've been able to not have to deal with them until now

thanks all for the input. i clearly have a lot to learn about them

6:07 PM

I still learn things about them. It's a perpetual process.

Just like anything in SQL Server. Very few people ever know everything about even the skinniest silo.

@RobertHarvey I like to tell myself that preventing this person from making a bad decision will subsequently prevent his successor from coming on here and being torn apart for having stored the date in a non-standard format. — FreshPrinceOfSO 7 secs ago

@AaronBertrand sure. i'm not expecting to know everything about everything. just neat getting the exposure

Comments got cleared.

Thank god as that other knob had some craptastic advice.

I think I had a valid point

@FreshPrinceOfSO Yes.

6:25 PM

@swasheck I know, I'm just saying that even the experts who have been working with, say, triggers for years are still learning stuff. That wasn't a knock on you.

@AaronBertrand yeah. i took no offense. if you're not learning then you're not trying :)

6:53 PM

That comment was short lived.

Heh. i was going to comment, then pasted what i thought was my link and then it wasn't.

Thank you mmarie! will do. — Sagar Facesagar 13 hours ago

That is the nicest comment anyone has ever left me for telling them to get off their butt and do their own research.

Q: SQL Server Doesn't Find Record Containing Float Value That It Returned

for @AaronBertrand

0

I have a query similar to this: SELECT SpecimenID, TestPeriodID, Grams, ConsumptionRate FROM LabData WHERE TestPeriodID = 255 AND TestID = 1 AND Grams = 728560 The record that is returned has a value of 16.5667068820687 for the last query column. I now add the following to the end of my que...

sql tsql sql-server-2012 rounding-error server

7:20 PM

Golly gee isn't float the best. I don't have to think about anything just let it do it's thing.

got my nexus 5

it's the shit

couldn't stand using the iPhone any longer

@FreshPrinceOfSO jealous.

@Zane i waited 4 weeks

If I wasn't on Verizon I would have one now.

when you press the power button to lock it, it looks like you're turning off a tube tv

/kindaAwesome

7:24 PM

@Buggieboy What kind of range are you looking at for this column and how close is the value to other samples in the population? You might be able to use BETWEEN on one of the latter decimal places to capture the value you are looking for — Lloyd Banks 3 mins ago

Q: run time parameters in sql string dont get their values ExecuteNonQuery fails with no run time errors

Is that what makes it better than the iPhone? :-)

barf

-1

the following code does not even throw an exception and the parameters @p1, @p2 and @p3 dont get have their run time values: ExecuteNonQuery return zero 'Dim parm As List(Of String) strSql_globalXrefObsSet.Append("DECLARE @cur_date_time datetime; ") strSql_glo...

sql-server vb.net tsql stringbuilder

@AaronBertrand That's the reason I got a nexus 7 instead of an iPad /notreally

@AaronBertrand ha, yep. and the 5 inch display. I've been on a 4s for the past 4 weeks. I've had to hold the thing a foot from my face to be able to read it

Use this AND CAST(ConsumptionRate as numeric(18,2)) = '16.56' — Justin 5 mins ago

I want to post an image meme from The Big Lebowski "You're out of your element!"

Here's me, mincing words again

Ugh... @Justin I don't think that's a good idea at all, for a variety of reasons — Aaron Bertrand 27 secs ago