Latest Blog Posts - Sam Bendayan's 2 Cents - SQLServerCentralhttp://www.sqlservercentral.com/blogs/
The largest free SQL Server community.en-USTechnological Movements and the Herd Mentality....http://www.sqlservercentral.com/blogs/2cents/2011/11/29/technological-movements-and-the-herd-mentality/
Wed, 30 Nov 2011 03:19:00 UT/blogs/2cents/2011/11/29/technological-movements-and-the-herd-mentality/1http://www.sqlservercentral.com/blogs/2cents/2011/11/29/technological-movements-and-the-herd-mentality/#comments<P>It's amazing to me how the herd mentality works in Technology (and many other things) sometimes. I get the idea that since there are so many new things happening in Technology all the time, it is automatically assumed that "new = good" and "old = bad". I believe that this mentality has done much to unjustifiably hurt the database in many people's eyes. After all, it is very likely that the oldest technological component used in your applications is the database. Application languages come and go all the time, it seems, but the database has stuck around for a long time, prompting many people to look distastefully at the database wondering why this piece of technology is still even around. I know that such a mentality has been at work in some of the latest Technological innovations, especially in the recent NoSQL movement. Now, I don't want to disparage said movement, and I get the idea that the people responsible for these technologies are likely not as anti-Relational as are many of the followers, but it is those followers that make lots of noise and can generate lots of confusion. At the end of the day the Relational Database is a MASSIVELY popular platform and for good reason; it's very scalable and flexible and has been in use in a wide variety of applications for decades. Nonetheless, in my experience it is not very well-liked, in spite of its large user base, and several myths have appeared which try to debunk the Relational Database: </P>
<P>Myth #1: "Relational Databases are not scalable" This one is the rallying cry of some NoSQL pundits, who point at the incredible volumes generated by the Web 2.0 applications like Facebook or Twitter. There are 2 points to be made in reply to this. First, in reality the majority of the applications in the world don't have 600 million users. Such an application can most accurately be described as an "edge case", with special performance requirements. Second, Facebook does use MySQL with the InnoDB engine, which is a relational database, even with those huge volumes. So in fact Relational Databases have truly scaled in the real world for a long time, and continue to do so. So where does this accusation come from? I think it comes from the fact that it is not easy to scale OUT when using Relational Databases. It's easy to scale web servers out by adding more servers, but with databases it's not that simple. I think that many developers WANT the database to be more like what they are used to. While this is understandable, it is also not realistic. After all, web server scale-out doesn't have to deal with the issues inherent in a persisted data store like database servers do. Also, while Relational Databases don't scale out, they have other ways to scale. However, proper design is paramount; you can't just blindly add another server and have the problem go away. </P>
<P>Myth #2: "The database should be mated to the application" One of the great strengths of the Relational Database is its very flexible and easy to use SQL query language. This allows the database to be "decoupled" from the application and used as an independent data store. You can import data into it, export data from it, and have your users, support professionals, developers and testers run queries against the database all day long using a simple SQL Editor. Many of the NoSQL databases coming out now don't have this; the data design of these databases is not easily mapped to real world concepts (such as when you model entities after real-world business concepts). Some of these databases don't have easy query languages like SQL, and this is fine, but what is interesting is how few people talk about this; they just gloss over it as if it didn't matter. Now, I don't know about you, but nearly all the applications I've ever worked with need easy access to the data in many different ways, and not having it is a deal-breaker. So while it would be nice to have a database that integrates seamlessly with the application, it is much more important to have a flexible and easy to use independent data store. As a matter of fact, the recent trend towards Master Data Management points to the concept of a decoupled data store that many systems can use as the single, correct data store for critical pieces of information. </P>
<P>Myth #3: "SQL is an "old" language, and old is bad" SQL is certainly old, but there are advantages to this. If you know that some languages last longer than others, wouldn't it be interesting to have as much business logic as possible in that language so that you're not forced to upgrade it every few years? So, "SQL" = "old", but "old" &lt;&gt; "bad". The same could be said of some other old "technologies" such as breathing and eating :-) </P>
<P>Myth #4: "The database should be disposed of" There is a dream among Application Developers that they should never have to deal with databases. They should just be able to persist their data structures as they exist in the application, and that's it. It sounds great, and people are always talking up the Object Oriented Databases because of this. There is no doubt that this is a great advantage of Object Oriented Databases, but the question is, why hasn't it ever taken off? What has stopped it? Why do so few people talk about the downsides? Is it once again a matter of bias? I believe it is, and it causes lots of confusion and misguided expectations when you expose one side of the story and ignore the other side. </P>
<P>Myth #5: "It's all about Technology." Wrong. It's all about the Business. The business is what creates wealth, and Technology is used to support the Business. The minute you get away from the Business and focus only on Technology you have the makings of a useless, failed project. And the reality is that the Database does a great job at supporting the wild and crazy world of business, not the neat-and-tidy world of theoretical application design. </P>
<P>Myth #6: "Databases are not easily testable." If there is any truth to this it is because the database is generally de-emphasized in favor of application technologies. It is certainly possible to write tests for database code, just like any other code, but it's just not as popular. Also, there is an interesting paradox at work regarding this topic: Database Constraints, which have been around for many years, are a GREAT form of tests, yet they are very unpopular. How is it that so many people are in favor of automated tests but so few people are in favor of Database Constraints? Again, I believe it's a matter of bias in favor of the known versus the unknown. </P>
<P>So, there is my rant for today....my two pennies. What are your thoughts? Do you believe that databases have been victimized as described above?</P>Inline TVFs can't use FORCE ORDER hint but Multi-Statement TVFs can...?http://www.sqlservercentral.com/blogs/2cents/2011/11/08/inline-tvfs-can-t-use-force-order-hint-but-multi-statement-tvfs-can/
Tue, 08 Nov 2011 19:49:00 UT/blogs/2cents/2011/11/08/inline-tvfs-can-t-use-force-order-hint-but-multi-statement-tvfs-can/0http://www.sqlservercentral.com/blogs/2cents/2011/11/08/inline-tvfs-can-t-use-force-order-hint-but-multi-statement-tvfs-can/#commentsDoing some work with converting Multi-Statement TVFs to Inline TVFs and I'm finding that the Inline TVFs cannot use the OPTION FORCE ORDER hint. Multi-Statement TVFs can use it just fine. Does anyone have any more info or experience on why this is the case?
More info on nested views causing performance problemshttp://www.sqlservercentral.com/blogs/2cents/2011/02/15/more-info-on-nested-views-causing-performance-problems/
Tue, 15 Feb 2011 22:18:00 UT/blogs/2cents/2011/02/15/more-info-on-nested-views-causing-performance-problems/0http://www.sqlservercentral.com/blogs/2cents/2011/02/15/more-info-on-nested-views-causing-performance-problems/#comments<P>&nbsp;As if I needed any further convincing that nested views were bad....</P>
<P>The other day I had to write a particularly ugly query to recover some lost data.&nbsp; The query was complicated (please don't ask) and the resulting SQL had 5 (five) levels of nested views.&nbsp; I realized that this was going to need some optimization, but I didn't know it would turn out to be that bad just for testing purposes so I ran the query on an offline copy of some customer data.&nbsp; Long story short, the query was so slow that I thought the server was hanging.&nbsp; It took me 9 minutes to recover 3 rows of data and after doing some quick calculations I realized that the query was going to take 108 DAYS to complete at this rate.</P>
<P>Realizing that the customer probably wanted the fix sometime this Spring, I killed the query and began optimizing.&nbsp; I decided to take 4 out of the 5 levels of nested views out of the equation by persisting that result set into a temp table and then running the bulk of the query off the temp table.&nbsp; The results were amazing...it took the query from 108 days to just over an hour.</P>
<P>Being that I broke several personal records with this event I just had to post it and let everyone know :-)</P>Strange issue while using IDENTITY values in INSTEAD OF triggershttp://www.sqlservercentral.com/blogs/2cents/2010/12/23/strange-issue-while-using-identity-values-in-instead-of-triggers/
Thu, 23 Dec 2010 14:09:00 UT/blogs/2cents/2010/12/23/strange-issue-while-using-identity-values-in-instead-of-triggers/0http://www.sqlservercentral.com/blogs/2cents/2010/12/23/strange-issue-while-using-identity-values-in-instead-of-triggers/#commentsFound an interesting issue last week....it goes like this:
If you are using INSTEAD OF triggers on views (in SQL 2005) and you need to retrieve IDENTITY values you have to be very careful how you do it. It turns out that a simple SELECT from the inserted table can return a NULL for no apparent reason. However, if you use the OUTPUT clause from the same inserted table, it always works!
It turns out in my case that I was using both in the trigger and I actually saw both values coming through.
So, the takeaway from this post is to always use OUTPUT clause in this situation.
This is very counterintuitive and I would consider it a bug. I'm wondering how many of you have run into this? Plz reply and let me know.
Thanks,
SBRange locks do not only occur in Serializable transactions....http://www.sqlservercentral.com/blogs/2cents/2010/09/22/range-locks-do-occur-in-serializable-transactions/
Wed, 22 Sep 2010 15:10:00 UT/blogs/2cents/2010/09/22/range-locks-do-occur-in-serializable-transactions/2http://www.sqlservercentral.com/blogs/2cents/2010/09/22/range-locks-do-occur-in-serializable-transactions/#comments<P>According to BOL, "Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level".&nbsp; From this I took it to mean that Range locks are ONLY used in Serializable transactions.</P>
<P>So then I had to troubleshoot a deadlocking problem where one of these Range locks was involved.&nbsp; I removed all the Serializable transactions and I was still getting the range locks.&nbsp; Bottom line:&nbsp; it turns out that these Range locks happen in other isolation levels under certain conditions.&nbsp; In my case, if you update a range of rows then a Range lock is being taken even in the READ COMMITTED isolation level.&nbsp; I also saw on the Web that it happens during cascading deletes/updates.</P>
<P>Any other experiences with this out there?</P>
<P>SB</P>
<P>&nbsp;</P>Professional Licensing for Software Professionals?http://www.sqlservercentral.com/blogs/2cents/2010/07/14/professional-licensing-for-software-professionals/
Wed, 14 Jul 2010 17:08:00 UT/blogs/2cents/2010/07/14/professional-licensing-for-software-professionals/3http://www.sqlservercentral.com/blogs/2cents/2010/07/14/professional-licensing-for-software-professionals/#comments<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN id=PreviewBody>Over the past few days I have been thinking that the Software Industry is in drastic need of a Professional Licensing mechanism. I cannot even begin to imagine how much it costs businesses when they hire people that are unqualified, and there is presently no mechanism that I know of, outside of Vendor-based Certifications and College Degrees, to tell managers and business owners whether candidates are qualified or not. I am surprised that this issue has not been more widely discussed; I presently know of no such initiative to impose a licensing mechanism similar to that of Doctors or Engineers. </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>I think we need this for various reasons: </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>1. To give hiring managers and business owners confidence in who they are hiring (and tell them when they are taking a risk if they decide to hire someone that is unlicensed). </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>2. To give qualified professionals the respect they deserve and keep the integrity of the profession intact. </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>Without this licensing mechanism it's easy for non-Software people to fantasize about how easy it is to "code". If this doesn't sound like a big problem, consider that the entire concept of Outsourcing Software Development jobs is primarily based on this idea. Jobs that are "easy" to do are the ones that are Outsourced first. I don't think people would Outsource their Doctors or Lawyers. So the fact is that a rigorous licensing process would create a conscience which states: "yes, this job is hard. Not everyone can do it. When this person speaks, listen to him or suffer the consequences." How many of us would like a little more of that in our professional lives? </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>Another problem that is caused by this lack of respect is the promotion of a non-Software person to a Software Management role....how many times has this happened and how much grief has it caused? Would you promote a non-Doctor to be the head of Surgery at a hospital? What would happen if you did? </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>Yet another issue is how work environments degrade because of this lack of trust. I have been in many places where Executives are frustrated because they have been lied to for years and still don't have a decent Software Infrastructure in spite of large investment and commitment on their part. Then, when a good person joins the organization, he has to bear the brunt of this distrust along with all the other incompetent people who enjoy the obscurity as it offers them a place to hide. </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>In short, a Professional Licensing initiative will help to bring the Software world out of the Dark Ages. </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>One of the questions that comes to mind is "why hasn't this already happened"? I can't say for sure, but I think that Software is much more abstract than most of the other professions out there. It's hard to explain what you do to many people (especially if you're a Database person) and this makes it equally easy to "fudge" results of projects and "redefine" concepts such as "success" and "quality". Surgeons can't fudge their results that easily, because they are more obvious to the layman. If a person dies on the operating table, that's that. Same with Engineers. If a bridge collapses or a Ford Pinto blows up when it's rear-ended that's not very easy to explain away. But software is so abstract that you can easily fudge or obscure many facts about it that are not possible with the other professions.&nbsp; </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>Of course there are aspects of Medicine and Engineering that are not as clear-cut as what I mentioned above, and there are aspects of software development that are rather clear cut (a broken website), so we're not talking absolutes here, but I think that on the whole the Software industry is more abstract than most of the other professions, and this fact alone SCREAMS for a Professional Licensing initiative. </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>Naturally there are numerous issues to be resolved, and many heated arguments are going to come out of this, but that is how life works and how progress is made. However it turns out, I think this licensing mechanism should bear the following characteristics: </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>1. Should be vendor-independent. </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>2. Should be rigorous enough so that any licensed person is well-versed in the structure of how Software works. Making a piece of Software function is relatively easy; it's the deeper understanding of Software that is more important. </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>Comments? Thoughts? </SPAN></SPAN></SPAN></P>
<P><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" mce_style="WORD-SPACING: 0px; FONT: medium Arial, Helvetica; TEXT-TRANSFORM: none; COLOR: #000000; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px"><SPAN class=Apple-style-span style="FONT-SIZE: 13px; TEXT-ALIGN: left" mce_style="FONT-SIZE: 13px; TEXT-ALIGN: left"><SPAN>SB</SPAN></SPAN></SPAN></P>Option missing in NTILE function?http://www.sqlservercentral.com/blogs/2cents/2010/06/09/option-missing-in-ntile-function/
Wed, 09 Jun 2010 15:24:00 UT/blogs/2cents/2010/06/09/option-missing-in-ntile-function/0http://www.sqlservercentral.com/blogs/2cents/2010/06/09/option-missing-in-ntile-function/#comments<P>Just saw something interesting....we have a requirement to divide a result set into a certain number of groups that have a certain size. This size is controlled by a configuration setting, so the user expects to see groups of At first glance I thought the NTILE function would be the perfect match for this, until I tried using it. The problem is that if the number of rows don't evenly divide into the frame size, then you have 2 options: </P>
<P>Option #1: Honor the frame size for all the frames except the last one. Make the last frame smaller to handle the amount left over in the division. </P>
<P>Option #2: Attempt to make all the frames as evenly sized as possible. </P>
<P>So, if you have a result set of 101 rows and a frame size of 10, you would get the following: </P>
<P>Option #1: 10 frames of 10 rows and an 11th frame of 1 row. </P>
<P>Option #2: 2 frames of 10 rows and 9 frames of 9 rows. </P>
<P>It turns out that NTILE only supports option #2, and there is no setting to change that. From BOL: "If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows." </P>
<P>I wonder if anyone has thought of proposing an option for NTILE to handle option #1? I didn't see anything in Microsoft Connect. SB</P>Nested views causing query performance woes....http://www.sqlservercentral.com/blogs/2cents/2010/04/05/nested-views-causing-query-performance-woes/
Tue, 06 Apr 2010 03:09:00 UT/blogs/2cents/2010/04/05/nested-views-causing-query-performance-woes/2http://www.sqlservercentral.com/blogs/2cents/2010/04/05/nested-views-causing-query-performance-woes/#commentsJust ran into an interesting issue AGAIN. It seems that when you nest views in a query you are opening the door for massive performance problems. I have seen this in the past, but it appears intermittently and apparently only in cases where you are using complex SQL. The problem goes something like this: When you start to use views to solve a particularly hairy SQL problem and you happen to NEST your views (that is, having one view that calls another view) you sometimes see a MASSIVE slowdown in performance. I have seen this in a couple of cases: in one case a report went from 5 minutes up to 1 hour execution time, and in the other case it went from sub-second performance to 4 minutes. In both cases you could solve the problem simply by "decomposing" the nested view and including its full SQL definition inside the calling query. Just doing this alone would take the query back to its quick performance.
Upon analysis of the query execution plans, it seems that the SQL Query Optimizer is getting confused and it returns the FULL data set of the view internally, only to filter out most of the data later...clearly a very bad optimization decision. However, it looks like the nested views "blind" the optimizer to the better choices available to it. In the latest case I saw, for example, there was a view which joined 2 tables with 850,000 rows apiece and then also called another view inside this view. The SP which called the outer view was joining the results of the 850,000 row view to a 1,500 row table. However, the Query Optimizer was pulling in all 850,000 rows first, doing some 3 or 4 query operations on it, and then finally filtering the rowset down to 1,500 rows afterwards. When I decomposed the view as described above the Optimizer went back to its old self and was able to filter the rowset down in the initial operator instead of pulling down all 850,000 rows. Predictably, performance improved by several orders of magnitude.
I have no idea why the Query Optimizer does this, but would like to see if anyone, especially from Microsoft, has any insight on this and if this problem is going to be fixed in the future. I seem to remember that for SQL 2005 there was a feature called "Statistics on Views" that was later dropped and never made it into RTM. I don't believe it made it into SQL 2008 either. I wonder if this would fix my issue above..?
Access Analysis Services via T-SQL?http://www.sqlservercentral.com/blogs/2cents/2010/03/22/access-analysis-services-via-t-sql/
Tue, 23 Mar 2010 03:59:00 UT/blogs/2cents/2010/03/22/access-analysis-services-via-t-sql/0http://www.sqlservercentral.com/blogs/2cents/2010/03/22/access-analysis-services-via-t-sql/#commentsIt occurred to me the other day that it might be interesting to manage aggregates in an SQL Server database by actually moving the aggregate values to Analysis Services and having the Stored Procedures from SQL Server access the Analysis Services cube by using straight T-SQL. I see that it is theoretically possible to do this by creating a Linked Server to Analysis Services and passing the MDX query as a string to the Analysis Services by using the OPENQUERY syntax. Would this be the best way to do it? What are the shortcomings? What is performance like? I've had bad experiences with OPENQUERY before in terms of performance, but I've never used it on Analysis Services.
Keep in mind that this is an existing application that already accesses these aggregates inside the SQL Server database itself, so I would have to move these aggregates to Analysis Services and then create a view or some similar structure which gets these aggregates and makes them available to T-SQL database objects.
Has anyone out there tried this? Does it actually work? Are there better ways to go about this? Any suggestions?
BiTemporal Tables, anyone?http://www.sqlservercentral.com/blogs/2cents/2010/03/11/bitemporal-tables-anyone/
Thu, 11 Mar 2010 14:11:00 UT/blogs/2cents/2010/03/11/bitemporal-tables-anyone/0http://www.sqlservercentral.com/blogs/2cents/2010/03/11/bitemporal-tables-anyone/#commentsI'm doing some interesting research on using BiTemporal tables and I was wondering if anyone out there has any real-world experience using these. Basically these are tables where you have effective dates and audit dates in the same table. You may know these by other names, such as 'Valid Time States" (Effective Dates) and "Transaction Time States" (Audit dates). These are complicated but quite promising...does anyone have any real-world feedback on using these?
More posts coming soon...http://www.sqlservercentral.com/blogs/2cents/2010/03/10/more-posts-coming-soon/
Wed, 10 Mar 2010 19:07:00 UT/blogs/2cents/2010/03/10/more-posts-coming-soon/0http://www.sqlservercentral.com/blogs/2cents/2010/03/10/more-posts-coming-soon/#commentsHaven't been around for a while, but doing some interesting work with Database Refactoring and Effective Dating...more posts coming soon....Beware the REPLACE function when doing heavy string parsinghttp://www.sqlservercentral.com/blogs/2cents/2009/08/23/beware-the-replace-function-when-doing-heavy-string-parsing/
Mon, 24 Aug 2009 01:36:13 UT/blogs/2cents/2009/08/23/beware-the-replace-function-when-doing-heavy-string-parsing/13http://www.sqlservercentral.com/blogs/2cents/2009/08/23/beware-the-replace-function-when-doing-heavy-string-parsing/#comments<p>&#160;</p> <p><font size="2">I found something interesting the other day.&#160; I was attempting to optimize a very heavy string parsing routine using T-SQL and was having problems.&#160; I don’t normally use T-SQL for such heavy string parsing, but this was a special case of a legacy structure that I had to work with, so I had no choice.&#160; Anyway, the said routine was a table function that basically takes a fixed length text file and turns it into a table (please don’t ask why) so you can join it to other tables in a database.&#160; The thing that made this interesting is that this fixed length text file had over 4,000 rows, so in order to turn this into a table the function has to slice and dice these 4,000 rows every time it’s executed, which it does by executing a WHILE loop.&#160; </font></p> <p><font size="2"></font></p> <p><font size="2">When I finished this function, it ran in about 15 seconds, which was way too slow for our needs.&#160; Unbeknownst to me, one of my colleagues was attempting the same thing and wrote his code in a slightly different way, but he got it to run in about 1 second.&#160; When I analyzed his code, I found it to be virtually identical to mine, with one difference:&#160; during every iteration I was using the REPLACE function to ‘wittle’ down the string until none of the string was left, at which point the WHILE loop would exit and the function would finish executing.&#160; In contrast, his approach did not have any REPLACE function, he was simply navigating forward in the string until he reached the end.&#160; </font></p> <p><font size="2"></font></p> <p><font size="2"></font></p> <p><font size="2">At this point one of my very special mental “talents” kicked in:&#160; when my mind is stuck on a problem and someone ELSE tells me the solution to it my mind immediately says “oh, I knew that!”&#160; and proceeds to give me a detailed explanation of the solution and why it works.&#160; And leaves you wondering where all that great information was hiding 5 seconds ago when you didn’t have the answer and you really needed it….you have to wonder why such useless “talents” even exist….</font></p> <p><font size="2"></font><font size="2"></font></p> <p><font size="2">So, of course, once I saw this then everything became obvious:&#160; my version of the function was doing over 4,000 REPLACE operations and his wasn’t.&#160; </font><font size="2">To add to this, I have to wonder if the string datatypes, such as the VARCHAR datatype that we were both using, are implemented as immutable data types in SQL Server.&#160; Does anyone know if this is the case?&#160; If it is then it would further explain the difference in speed.&#160; Since an immutable data type doesn’t really ‘change’ the instance of the string in memory and just adds a new instance of a string, this means that my function was creating over 4,000 instances in memory of the large string that I was parsing.&#160; Talk about a waste!</font></p> <p><font size="2"></font></p> <p><font size="2"></font></p> <p><font size="2">I mention this because lots of SQL developers may not have much experience in string parsing, since this is normally not done using SQL.&#160; This may be old hat to many application programmers, but it’s not something that DBAs run into as frequently, so I’m hoping that others will find it as useful as I did.</font></p> <p><font size="2"></font></p> <p><font size="2"></font></p> <p><font size="2">So, the moral of the story is:&#160; be careful when using REPLACE in heavy string parsing operations.&#160; Like many other things, if you need it you should use it, but the trick is in knowing when you REALLY need it.&#160; In my case I thought I needed it but in reality there was a much simpler solution available, and it ended up running 15 times faster.&#160; It proves the old adage of “Keep It Simple, Stupid”…given 2 equivalent solutions the simpler one is better.</font></p> <p><font size="2"></font></p> <p><font size="2">…and that’s my $.02 for today….</font></p> <p><font size="2"></font></p> <p><font size="2">SB</font></p>To Date or Not To Date….http://www.sqlservercentral.com/blogs/2cents/2009/05/13/to-date-or-not-to-date/
Thu, 14 May 2009 03:12:00 UT/blogs/2cents/2009/05/13/to-date-or-not-to-date/13http://www.sqlservercentral.com/blogs/2cents/2009/05/13/to-date-or-not-to-date/#comments<P>&nbsp;</P>
<H1></H1>
<P><FONT size=2></FONT></P>
<P><FONT size=2>OK….so I’m in the middle of a very interesting Effective Dating project…basically we’re dealing with some tables that only store current data, but there is a need for future-dated data as well.&nbsp; Because the base table can’t hold that data, we have to come up with additional tables and a process to store future-dated data and then move it into the base table whenever we get to the future date.&nbsp; Very cumbersome and fragile, and that’s why we’re looking at this project.</FONT></P>
<P><FONT size=2></FONT><FONT size=2></FONT></P>
<P><FONT size=2>There is a great book on this subject by Richard Snodgrass which you can find at Amazon…this book is highly recommended (read:&nbsp; <STRONG>required</STRONG>) for anyone looking at Effectively Dating tables.&nbsp; Trust me, you won’t believe how complicated the subject is…here is the link to the book:</FONT></P>
<P><FONT size=2><A href="http://www.amazon.com/Developing-Time-Oriented-Database-Applications-Management/dp/1558604367/ref=sr_1_1?ie=UTF8&amp;s=books&amp;qid=1242268718&amp;sr=1-1" mce_href="http://www.amazon.com/Developing-Time-Oriented-Database-Applications-Management/dp/1558604367/ref=sr_1_1?ie=UTF8&amp;s=books&amp;qid=1242268718&amp;sr=1-1">http://www.amazon.com/Developing-Time-Oriented-Database-Applications-Management/dp/1558604367/ref=sr_1_1?ie=UTF8&amp;s=books&amp;qid=1242268718&amp;sr=1-1</A></FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>There is also a book by C.J. Date on the subject, titled “Temporal Data &amp; the Relational Model”, but I haven’t read it….if anyone has read it please feel free to post your feedback on it!</FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>Anyways, the book makes an interesting case for some major enhancements to the Relational Database products out there, SQL Server included.&nbsp; Basically, what you need when effectively dating tables is to add the valid date/time period for each row.&nbsp; So, if you have an e-commerce website, for example, and you want to schedule a sales promotion for 3 days, you would indicate in your PROMOTION table that this particular sales promotion is only valid for 3 days.&nbsp; The beauty of the solution is that the SQL queries in the application would query the tables by using the system date, as in “give me the promotions that are effective for today (the system date)”…so the website content can change as needed without any manual intervention.&nbsp; </FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>If the table was not effectively dated, you would have to schedule a job to run at the start date/time of the promotion and make all the data changes right then and there.&nbsp; Of course, the promotion will probably start at midnight, and something will break when the scheduled job runs.&nbsp; The promotion won’t show on the website, and the DBA will get called at half-past midnight in order to fix the problem to the tune of frantic curses and imprecations from the pained executive staff…but I won’t elaborate any further on the joys of our profession :-)</FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>Now, how do you specify a valid date/time period for each row?&nbsp; The obvious answer at first glance is to add 2 date columns:&nbsp; StartDate and EndDate, and then add them to the Primary Key of the table.&nbsp; That should work, right?</FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>You won’t believe how off the mark that solution is.&nbsp; Because what’s happening here is that the database is just storing 2 dates….it’s not really storing a date/time PERIOD (or INTERVAL).&nbsp; The database doesn’t know that this period should include ALL DATES IN BETWEEN the 2 dates you’re storing.&nbsp; So it’s possible to insert rows that overlap… as long as the StartDate and EndDate are different the Primary Key constraint will allow the INSERT…but you probably won’t want 2 of the same promotions running at the same time…..</FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>And what about Foreign Keys?&nbsp; As it turns out, these won’t work either….in our example, if you have a FK in the ORDER table pointing to the PromotionID, well now you can have duplicate promotionIDs in the PROMOTION table.&nbsp; What if you have a promotion that you run every year for 3 weeks?&nbsp; You keep the same promotion ID, because the substance of the promotion is the same, but every year you insert a new row with different effective dates.&nbsp; So, if a customer bought something from PromotionID 6, and you’ve been running that promotion for 5 years, you now have 5 rows with PromotionID = 6 in the PROMOTION table.&nbsp; The FK can’t handle that…it needs a unique constraint to point to.</FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>So you need lots of constrants or triggers to get the job done….this is what the Snodgrass book deals with…..and by the way, the above is just a delicious sampling of the issues encountered…it’s not an all-inclusive list.&nbsp; </FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>In response to this, I have entered a suggestion in MS Connect in order to enhance SQL Server to fix this problem.&nbsp; The solution entails defining a DATE/TIME PERIOD or INTERVAL datatype.&nbsp; It turns out that the ANSI SQL standard already has this, but it’s not implemented in SQL Server.&nbsp; Below I pasted the text of the Connect entry…if it sounds interesting to you, please follow this link and vote for it:&nbsp; </FONT></P>
<P><A href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432281" mce_href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432281"><FONT size=2>https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432281</FONT></A></P>
<P><FONT size=2></FONT><FONT size=2></FONT></P>
<P><FONT size=2></FONT>&nbsp;</P>
<P><FONT size=2>Hefty subject…I think I should get 3 cents for this one…. :-)</FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>SB</FONT></P>
<P><FONT size=2></FONT>&nbsp;</P>
<P><FONT size=2></FONT>&nbsp;</P>
<P><FONT size=2></FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>The text of the MS Connect entry follows:</FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>“Problem:&nbsp; SQL Server does not have an ANSI INTERVAL data type.&nbsp; When we create effectively dated tables we have to simulate a time period by using 2 DATETIME columns (StartDate, EndDate).&nbsp; However, often we don't want to allow 2 rows to have overlapping date ranges and many times we don't want to allow gaps in there either.&nbsp; All of these validations have to be done in SQL.&nbsp; It is cumbersome and probably doesn't perform as well as a data type would.</FONT></P>
<P><FONT size=2></FONT></P>
<P><FONT size=2>Solution:&nbsp; Create an 'Interval' or 'Time Period' data type.&nbsp; </FONT><FONT size=2>It should probably contain 2 values (StartDate, EndDate) stored in the same column and it can then treat all time periods between these 2 dates as a continuous length of time instead of just having 2 separate date columns in the DB.&nbsp; </FONT></P>
<P><FONT size=2>It should also contain a special value for 'forever' or 'until the end of time'.&nbsp; This is heavily used for rows that don't currently have an end date.&nbsp; NULLs are often used for this, but they create problems such as not being able to participate in a Primary Key and they also are treated differently in SQL and can cause performance problems.&nbsp; So many times people just use an artificial value like '9999-12-31' for this end date, but this is not standardized, nor is its meaning obvious to the user.</FONT></P>
<P><FONT size=2>It should have functions or operators to check for the gaps and overlaps.&nbsp; </FONT></P>
<P><FONT size=2>It can also contain options to make it more flexible, such as "should the period be inclusive of the start &amp; end dates?" and "allow gaps/overlaps?".</FONT></P>
<P><FONT size=2>The benefits of this would be that we don't have to code triggers and also the logic would be very neatly encapsulated in the datatype and would not clutter up our SQL excessively.&nbsp; I imagine it would be faster as well.</FONT></P>
<P><FONT size=2>Microsoft already did something like this in SQL 2008, where they created the Spatial Data Types.&nbsp; Maybe some of the existing features developed for Spatial support can be reused for this data type?&nbsp; Spatial data already has an 'Overlaps' operator, which is similar to what we would need for this data type.”</FONT></P>Welcome to Sam Bendayan's 2 cents.....content coming soon...http://www.sqlservercentral.com/blogs/2cents/2009/05/12/welcome-to-sam-bendayan-s-2-cents-content-coming-soon/
Tue, 12 May 2009 23:56:00 UT/blogs/2cents/2009/05/12/welcome-to-sam-bendayan-s-2-cents-content-coming-soon/0http://www.sqlservercentral.com/blogs/2cents/2009/05/12/welcome-to-sam-bendayan-s-2-cents-content-coming-soon/#commentsYou will be at least 2 cents richer....What's a 'DBA'?http://www.sqlservercentral.com/blogs/2cents/2009/05/12/what-s-a-dba/
Tue, 12 May 2009 23:26:00 UT/blogs/2cents/2009/05/12/what-s-a-dba/37http://www.sqlservercentral.com/blogs/2cents/2009/05/12/what-s-a-dba/#comments<P>Most of us are tired of this overused, vague term. How many of you have clicked on a 'DBA' job posting only to find that it's not what you do? Or asked for a DBA resume from a recruiter and gotten something other than what you expected? Or how about interviewing someone for a DBA position and finding that the candidate is not even close to what you're looking for, but can still rightly call himself a DBA....?</P>
<P>I got an interesting mini-speech on this a couple of years back and I've always referred to it ever since.&nbsp; I can't take credit for it...the credit goes to my friend Yitzhak Khabinsky, although I have embellished it somewhat.&nbsp; Here is the "mini-speech":</P>
<P>"Many people refer to every database professional as a 'DBA'.&nbsp; This is wrong.&nbsp; The term is much too vague.&nbsp; What really exists is 5 separate disciplines:</P>
<P>1. Data Modeler - these people work with Business Analysts and developers during the requirements and design stages of the Development Lifeycle.&nbsp; They usually are (or should be) one of the first people to get the requirements from the business people and translate those requirements into a data model.&nbsp; They are experts at creating data models and working with data modeling applications.&nbsp; It goes without saying that they are experts in relational database design from a conceptual standpoint.&nbsp; They don't necessarily meddle in any one specific database platform, since much of their work is in the Logical Design Phase and is therefore platform-agnostic.</P>
<P>2. SQL Developer - these are the SQL experts who usually code review all the SQL written by anyone in their organization and take on the biggest SQL challenges themselves.&nbsp; They are usually tied to one or more specific platforms.&nbsp; They know all the ins and outs of writing clean, performant, scalable SQL and all the pitfalls to avoid.&nbsp; Many times they are the last line of defense when confronting some particularly hairy SQL that is slow or is causing problems.&nbsp; They also are usually the ones who take over a project after the data modeler finishes his work and develop the SQL core of new product offerings.</P>
<P>3. DB Administrator - these are the professionals who often work inside the "cold rooms" and deal with the actual SQL Server hardware of their organization.&nbsp; They manage backups, restores, tape libraries, creation and consolidation of SQL instances, SANs, and the like.&nbsp; They usually get into programming in order to automate administrative functions, but are usually not involved in application programming.&nbsp; When they get involved in performance tuning it's usually the type of tuning where they don't touch the code; they try to optimize performance by laying out the database properly on the disk subsystems, making sure enough memory is available, configuring servers properly, and performing proper maintenance on the database servers.</P>
<P>4. ETL Developer - this is similar to the SQL developer above, but it has taken a direction of its own with the proliferation of Data Warehousing and Business Intelligence.&nbsp; These professionals are usually experts in one or more of the "ETL" applications (Ascential, Informatica, SSIS, etc), which go beyond the realm of&nbsp;SQL development.</P>
<P>5. Database Architect - this is "all of the above" or close to it.&nbsp; These are very senior professionals with many years of experience and can lead <BR>teams of other Database Professionals.&nbsp; In spite of many claims to this level of professionalism, there are in fact very few of these professionals around.&nbsp; These professionals also get into the internals of how the database engines work and are also usually interested in database research as well.&nbsp; The main litmus test is that they possess most of the 4 other skills if not all.</P>
<P>Also, the above 5 disciplines only apply to the OLTP world (with the exception of the ETL developer who has one foot in OLTP and the other in BI).&nbsp; There are 5 other similar disciplines that apply to the BI world (Dimensional Modeler, MDX developer, Administrator, ETL Developer, BI Architect)."</P>
<P>So, that's the 'mini-speech'.&nbsp; You will find that if you use this model you will get a FAR better idea of what you're looking for in a DBA job if you're looking for work, or a DBA candidate if you're looking to hire.&nbsp; When I interview people I usually start with this mini speech and then try to put the person into one of these categories.&nbsp; I have found it to be very effective in hiring top-notch candidates.&nbsp; Obviously, there is some overlap...most people fit into more than one category, but you get a FAR better idea of what the person is capable of by following this model.</P>
<P>&nbsp;...and that's my 2 cents....</P>
<P>SB</P>