November 14, 2011 at 11:29 am
Ninja's_RGR'us (11/14/2011)
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!
Ah.
It's not just about partitioning.
An example I've got is a system that relies on ETL loads throughout the day, and the data is static in between loads. It was originally developed with several very complex views, named with "vw" prefixes, which were necessary to render the data into a format consumable by the application. I replaced those views with actual tables, loaded during the ETL process. Tremendous query performance increase. But now I have tables named "vw...".
I have material in these that disallows indexed views, so couldn't go that route.
Alternately, I could create tables under the views, named "t...", and just make the views be "SELECT * FROM t...", but the extra pass-through seems like a waste to me. Just adds confusion to the objects in the database and makes documentation more complex.
I've had similar situations in reverse, where I found that a table needed to be more normalized than originally thought, due to changes in requirements. To maintain backwards compatibility, I create a view with the old table name and structure, and then normalize the table by splitting it up. No code changes required, just the advantages of the new tables to the things that can take advantage of it. But it's then easy to end up with views named "t...".
- 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:32 am
SQL Kiwi (11/14/2011)
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
Okay. I'm getting those later in the search results is all. Not sure why.
- 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:32 am
GSquared (11/14/2011)
Ninja's_RGR'us (11/14/2011)
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!
Ah.
It's not just about partitioning.
An example I've got is a system that relies on ETL loads throughout the day, and the data is static in between loads. It was originally developed with several very complex views, named with "vw" prefixes, which were necessary to render the data into a format consumable by the application. I replaced those views with actual tables, loaded during the ETL process. Tremendous query performance increase. But now I have tables named "vw...".
I have material in these that disallows indexed views, so couldn't go that route.
Alternately, I could create tables under the views, named "t...", and just make the views be "SELECT * FROM t...", but the extra pass-through seems like a waste to me. Just adds confusion to the objects in the database and makes documentation more complex.
I've had similar situations in reverse, where I found that a table needed to be more normalized than originally thought, due to changes in requirements. To maintain backwards compatibility, I create a view with the old table name and structure, and then normalize the table by splitting it up. No code changes required, just the advantages of the new tables to the things that can take advantage of it. But it's then easy to end up with views named "t...".
:w00t: I C!
November 14, 2011 at 11:32 am
Brandie Tarvin (11/14/2011)
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.
DBA WWE style 😀
Seriously though, let me know if it seems like I am doing that. I try to step away from those things
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 11:33 am
SQLRNNR (11/14/2011)
Brandie Tarvin (11/14/2011)
WOW......just... WOW.
EDIT: In regards to all the MERGE connect bug issues, that is.
Ah, thanks for clarifying.
T'was clear enough without the edit! 😉
November 14, 2011 at 11:33 am
SQLRNNR (11/14/2011)
Brandie Tarvin (11/14/2011)
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.
DBA WWE style 😀
Seriously though, let me know if it seems like I am doing that. I try to step away from those things
+1
I don't want to scare 'em away!
November 14, 2011 at 11:37 am
Brandie Tarvin (11/14/2011)
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.
I try to avoid doing that.
I think the only people I've "beat up on" recently have been Phil Factor (he can take it), and SanDroid (wasn't even aware I was beating up on him, but apparently he took my posts as a rant of some sort). Other than occassional fisticuffs with Stefan, of course, but he can take and dish it just fine.
If I've inadvertently stepped on other toes than those, please let me know so I can straighten it out. (Sounds like I'm trying for a position as a podiatrist or something. I need better metaphores.)
- 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:38 am
GSquared (11/14/2011)
Okay. I'm getting those later in the search results is all. Not sure why.
Primarily it's because I was originally too lazy to hunt all the links down and format them nicely 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 14, 2011 at 11:38 am
GSquared (11/14/2011)
Brandie Tarvin (11/14/2011)
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.
I try to avoid doing that.
I think the only people I've "beat up on" recently have been Phil Factor (he can take it), and SanDroid (wasn't even aware I was beating up on him, but apparently he took my posts as a rant of some sort). Other than occassional fisticuffs with Stefan, of course, but he can take and dish it just fine.
If I've inadvertently stepped on other toes than those, please let me know so I can straighten it out. (Sounds like I'm trying for a position as a podiatrist or something. I need better metaphores.)
It's appropriate for foot in mouth scenario!
November 14, 2011 at 11:56 am
SQL Kiwi (11/14/2011)
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.
And a good reminder that we can all use a refresher on from time to time.
As others have said, let me know if I cross that line. As "threadizens" we should all let each other know when we cross that line so we can do our best to straighten it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2011 at 12:08 pm
SQL Kiwi (11/14/2011)
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
+1
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
November 14, 2011 at 12:09 pm
SQLRNNR (11/14/2011)
Steve Jones - SSC Editor (11/14/2011)
Gianluca Sartori (11/14/2011)
Congrats Jason for winning T-SQL challenge 63!+1, congrats
Danke
Yep. You rock man.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
November 14, 2011 at 12:10 pm
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?
No, updaste with a from clause still delivered better performance last time i did any tests 9that was on 2008, not R2, so isn't up to date). I offered a Merge solution in that thread only because the FROM clause on update is non-standard (Hugo even raised a connect item suggesting it be deprecated) so Merge is more secure long term, but for myself I still write from clauses in update statements.
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?
The top one from a DML programming point of view has to be table types and table valued parameters to stored procs. New collations are interesting too - for me, at any rate. On table structure filtered indexes look interesting, sparse columns less so. On the admin side, I would have given a lot for the new dependency objects if I could have had then when taking over legacy databases and refactoring everything, and the new deprecation related event classes look useful, as do the associated usage counters.
Tom
November 14, 2011 at 12:12 pm
Jim Murphy (11/14/2011)
SQLRNNR (11/14/2011)
Steve Jones - SSC Editor (11/14/2011)
Gianluca Sartori (11/14/2011)
Congrats Jason for winning T-SQL challenge 63!+1, congrats
Danke
Yep. You rock man.
Thanks
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 12:17 pm
Sean Lange (11/14/2011)
And a good reminder that we can all use a refresher on from time to time.As others have said, let me know if I cross that line. As "threadizens" we should all let each other know when we cross that line so we can do our best to straighten it out.
Agreed, I've needed it on occasion when I'm not even trying to be that *** just getting frustrated that my point is either being ignored not not even heard. It happens.
Btw, hey, I'm back, I got ran over by the Flu Truck and there's still tread marks. Hopefully your father's doing better Remi, Normal Form will always be an odd duck discussion, I'm just glad people are even teaching it, and my favorite Myth: Jupiter wrote the first database in clay tablets. He didn't, it was Hermes. (Yes, I'm mixing them, 😛 )
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 31,711 through 31,725 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply