November 14, 2011 at 10:00 am
Brandie Tarvin (11/14/2011)
After looking at the link Remi posted, and noticing the MERGE answer there, I have to wonder. For a simple one or two table update like that, is MERGE really more effective then writing an UPDATE with a WHERE clause?I'm just now starting to look up all the nifty new T-SQL features of 2008 (been putting out fires since the upgrade). Anything else I should put at the top of my "check this out" list with MERGE?
Table Value Parameters
Table Value Constructors
Inline Variable Assignment
Those are the ones I find most useful, not in any particular order. Merge is my favorite new T-SQL feature for 2008, but the other three are really, really useful too.
Well, Inline Variable Assignment is mostly just mildly convenient, not "incredibly useful". But it is quite nice.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 14, 2011 at 10:03 am
Jack Corbett (11/14/2011)
Brandie Tarvin (11/14/2011)
After looking at the link Remi posted, and noticing the MERGE answer there, I have to wonder. For a simple one or two table update like that, is MERGE really more effective then writing an UPDATE with a WHERE clause?I'm just now starting to look up all the nifty new T-SQL features of 2008 (been putting out fires since the upgrade). Anything else I should put at the top of my "check this out" list with MERGE?
I'd still use UPDATE in that case, but I have gotten away from using the FROM the clause with JOIN in most cases.
I'm moving to Merge instead of Update From. Mainly for consistency, since I use Merge for upserts all over the place these days.
I've been tempted to use Update From in the forums a few times, instead of Merge, just in case Joe shows up. He finds Update From very annoying, because it's a T-SQL feature, not part of ANSI-SQL. But twitting him isn't all that entertaining any more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 14, 2011 at 10:26 am
Koen Verbeeck (11/12/2011)
Trey Staker (11/11/2011)
SQLRNNR (11/11/2011)
Trey Staker (11/11/2011)
I signed up for a Twitter account today. I'm not sure what to do with it. Is that a sign that I'm a geezer?If you are, does that make the rest of us geezers?
Do you have a list of people to follow?
I realized after I signed up that today was 11-11-11. So I searched #sqlhelp and found the first 11 people I recognized and am following them. To take it to another goofier level I made 11 tweets.
I think what makes me a geezer are that my two teenage children do stuff like twitter and it just comes natural to them. To me it feels awkward. In my career I've set up some very complex systems, billing systems, large scale projects with point of sale devices in thousands of convient stores. I've managed hundreds of sql servers the last few years...etc... But it's taken me years to set myself up with twitter.
Somehow I missed 11-11-11 11:11:11 yesterday. I'm still angry with myself 🙂
Followed you on Twitter, now you're not so alone 😉
Thanks Koen for being my first follower! I'm starting to like twitter a lot. There's so much info and links to stuff. It no longer feels awkward but very natural.
My twitter name is @TreySQL. The plan is to only use it for work and SQL. I've had another Twitter account that I signed up for years ago but never used called @Thermo1969. I'm using it to follow all the non-work stuff since I'm using the work one on a work monitored phone.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
November 14, 2011 at 10:32 am
Trey Staker (11/14/2011)
Thanks Koen for being my first follower! I'm starting to like twitter a lot. There's so much info and links to stuff. It no longer feels awkward but very natural. My twitter name is @TreySQL.
Now following http://twitter.com/#!/treysql
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 14, 2011 at 10:34 am
Brandie Tarvin (11/14/2011)
Anything else I should put at the top of my "check this out" list with MERGE?
Yeah, do a Google search on "site:connect.microsoft.com sql server merge bug" 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 14, 2011 at 10:39 am
Ninja's_RGR'us (11/14/2011)
GSquared (11/14/2011)
SQLRNNR (11/13/2011)
I find the practice of prefixing with tbl_ just a waste of 4 chars in a table name. It would be funny if a proc were prefixed with tbl_ though.:hehe:I've ended up with too many tables prefixed with vw and views prefixed with t or tbl to consider it amusing any more. Just annoying.
Ok then let me throw something at you (never having had the partitioning issue).
How do you dev directly in a ERP that continuously updates itself and can destroy your objects? I have been told that they don't use prefix on views so that if I do, I won't have any issues.
I haven't the faintest idea what I'd do in a system like that. Never worked with one.
Prefixing view names might be necessary there. Probably is.
In which case, by all means use 'em. Or build the views in a separate database. Or a separate schema maybe. But either of those might not be convenient/useful, so maybe you're stuck with prefixed view names. If so, use what you have.
I'm writing more generally. I've had more problems caused by object name prefixes than fixed by them. That doesn't make them universally bad, it just speaks to my experiences with them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 14, 2011 at 10:43 am
GSquared (11/14/2011)
Ninja's_RGR'us (11/14/2011)
GSquared (11/14/2011)
SQLRNNR (11/13/2011)
I find the practice of prefixing with tbl_ just a waste of 4 chars in a table name. It would be funny if a proc were prefixed with tbl_ though.:hehe:I've ended up with too many tables prefixed with vw and views prefixed with t or tbl to consider it amusing any more. Just annoying.
Ok then let me throw something at you (never having had the partitioning issue).
How do you dev directly in a ERP that continuously updates itself and can destroy your objects? I have been told that they don't use prefix on views so that if I do, I won't have any issues.
I haven't the faintest idea what I'd do in a system like that. Never worked with one.
Prefixing view names might be necessary there. Probably is.
In which case, by all means use 'em. Or build the views in a separate database. Or a separate schema maybe. But either of those might not be convenient/useful, so maybe you're stuck with prefixed view names. If so, use what you have.
I'm writing more generally. I've had more problems caused by object name prefixes than fixed by them. That doesn't make them universally bad, it just speaks to my experiences with them.
I'm just fishing for other POV. I had never had the partition issue so I'm wondering if you see anything wrong with my current option.
Also paritioning is definitely NEVER going to happen on that system. 20 GB after 4 years and starting over!
November 14, 2011 at 10:52 am
Steve Jones - SSC Editor (11/14/2011)
Gianluca Sartori (11/14/2011)
Congrats Jason for winning T-SQL challenge 63!+1, congrats
Danke
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 14, 2011 at 10:53 am
Brandie Tarvin (11/14/2011)
Especially when DBAs are forced to use vendor databases over which they have little schema control. Don't beat up the people who have to deal with the hand dealt to them just because they got those particular cards. It's not their fault.
As Gail already said, we are not aiming to beat up on anyone, just to make sure that people understand that they should never create a naming scheme of the wrong sort.
And certainly no-one can be expected to change vendor databases. I've no quarrel with someone who is stuck with vendor database over which they have little or no control - I've been there and have the deepest sympathy with them; it was a day of celebration for all our technical staff when I persuaded our CEO to break with one partcularly pernicious vendor (who shall remain nameless, to protect the guilty as well as protecting me from lawsuits).
Tom
November 14, 2011 at 10:55 am
SQL Kiwi (11/14/2011)
Brandie Tarvin (11/14/2011)
Anything else I should put at the top of my "check this out" list with MERGE?Yeah, do a Google search on "site:connect.microsoft.com sql server merge bug" 🙂
Do you mean this one? http://connect.microsoft.com/SQLServer/feedback/details/699055/merge-query-plans-allow-fk-and-check-constraint-violations
I tried that out, and can't seem to get it to work except when I try to insert a value into a calculated column, as per the sample provided. It does work correctly (checks the constraint and fails the insert) if I try to insert into a column that's a valid target of an insert statement.
Definitely something that should be fixed, but I'd call it very, very low priority. It only comes up in an edge case that should be impossible in any reasonably designed database anyway. Yes, preventing our own stupidity is part of what error messages are supposed to do, but in this case, it's pretty rare that it'll matter.
Or do you have something else in mind? That search string gets some results about Merge Replication, which obviously aren't the issue at hand. Some similar results about filtered indexes, with the same bug as this one, also show up. Or am I missing one that matters more?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 14, 2011 at 11:03 am
GSquared (11/14/2011)
SQL Kiwi (11/14/2011)
Brandie Tarvin (11/14/2011)
Anything else I should put at the top of my "check this out" list with MERGE?Yeah, do a Google search on "site:connect.microsoft.com sql server merge bug" 🙂
Do you mean this one? http://connect.microsoft.com/SQLServer/feedback/details/699055/merge-query-plans-allow-fk-and-check-constraint-violations
No, not that one so much; the search I gave should bring up MERGE bugs on Connect like these:
http://connect.microsoft.com/SQLServer/feedback/details/581548
http://connect.microsoft.com/SQLServer/feedback/details/620367
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 14, 2011 at 11:16 am
WOW...
...just... WOW.
EDIT: In regards to all the MERGE connect bug issues, that is.
November 14, 2011 at 11:23 am
L' Eomot Inversé (11/14/2011)
Brandie Tarvin (11/14/2011)
Especially when DBAs are forced to use vendor databases over which they have little schema control. Don't beat up the people who have to deal with the hand dealt to them just because they got those particular cards. It's not their fault.As Gail already said, we are not aiming to beat up on anyone, just to make sure that people understand that they should never create a naming scheme of the wrong sort.
I get that. I do.
It just seems lately that there are a lot of people on this forum (including long-time posters like us) have been jumping on the "Smack down the newbie" bandwagon lately. I know it's not intentional, but no one can read tone of voice from a post and it seems the word choices offer themselves up to an interpretation that we didn't think they did.
Maybe I'm a little too sensitive to this issue lately. We're sitting here on a thread created for grousing, and we grouse, and then we start taking it outside The Thread ... and I'm just worried about the cliquish impression we may be starting to give people.
I apologize for jumping all over this issue. It just hit that nerve this morning, if you know what I mean.
November 14, 2011 at 11:26 am
Brandie Tarvin (11/14/2011)
It just seems lately that there are a lot of people on this forum (including long-time posters like us) have been jumping on the "Smack down the newbie" bandwagon lately. I know it's not intentional, but no one can read tone of voice from a post and it seems the word choices offer themselves up to an interpretation that we didn't think they did.Maybe I'm a little too sensitive to this issue lately. We're sitting here on a thread created for grousing, and we grouse, and then we start taking it outside The Thread ... and I'm just worried about the cliquish impression we may be starting to give people.
I apologize for jumping all over this issue. It just hit that nerve this morning, if you know what I mean.
Fair point, well presented.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 14, 2011 at 11:28 am
Brandie Tarvin (11/14/2011)
WOW......just... WOW.
EDIT: In regards to all the MERGE connect bug issues, that is.
Ah, thanks for clarifying.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 31,696 through 31,710 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply