SQLServerCentral / Discuss Content Posted by Sureshkumar Ramakrishnan / Article Discussions / Article Discussions by Author / New T-SQL Features in SQL Server 2005 Part 1 / Latest Posts

RE: New T-SQL Features in SQL Server 2005 Part 1

Jeff Moden — Mon, 04 Dec 2006 21:28:00 GMT

The other articles are not the immediate concern. Plagerism and breaking of copy right laws should be your immediate concern... I think you make too little of the good advice you've been given. Whatever...

RE: New T-SQL Features in SQL Server 2005 Part 1

Sureshkumar Ramakrishnan — Mon, 04 Dec 2006 05:19:00 GMT

Jeff,I think there is too much made about the plagarism stuff.Everyone understands it.The theoritical part of the defintion of CTE for instance is used referrring to MSDN and I have added a link of the reference in the article itself.

For you all to have a background the article was of 12 pages and it was broken down in two parts by the administrators of the site so that its more readible.The second part has most of the features with examples .

I have agreed in my previous comments that I should have added the reference more explicitly even if the article was splitted into two parts.

This is not the first article that I have written,Please have a look at the other articles before commenting.

Lets not forget that we are using Microsofts product and its source is more authentic than anything else.

Please provide some constructive information/comments that can be used by all the authors and the technology driven audience.

RE: New T-SQL Features in SQL Server 2005 Part 1

Jeff Moden — Fri, 01 Dec 2006 10:34:00 GMT

I don't think the author intended it this way, but to answer your question of "So what if parts are copied from BOL"? Because...

1. Plagerism is in pretty bad form. Most learned readers would rather eat pond scum than read plagerized material. By itself, copying material isn't plagerism... not giving very specific credit to the creators of the original infomation is.

2. Because of the copy right Microsoft has on the info, the fact that the terms of use say "personal" and "non-commercial use" and may not be republished (in so many words), and the author got paid $50 for the web article which makes it both commercial and published, it's also against the federal copy right laws.

Some folks are worried about item 1 above... me too... I hate plagerism when it happens and represents one of the lowest forms of life there is. Of more concern to me, though, is to warn my fellow authors that you can get the pant's sued off of you by Microsoft and other companies and, depending on the offense, may spend a little time in jail by doing this type of thing. Ignorance is no excuse in the eyes of the courts. Foreign nationals can also be expelled from the country within minutes of the conviction or jail time served.

Be careful what you copy, folks, even if you do give due credit...

Will Microsoft or other company come after you for this type of "minor" offense? Dunno for sure but are you sure you want to test those waters?

RE: New T-SQL Features in SQL Server 2005 Part 1

Dave Ott — Fri, 01 Dec 2006 07:42:00 GMT

I thought this was a great article, it sums up the key T-SQL additions. Just what we DBAs/developers need to know. So what if parts are copied from BOL. Not all of us are need deep into 2005 yet to have 2005 BOL loaded and reference everyday. (Personally I do not like the new BOL, I guess I'm too used to 200 BOL).

Thanks

Dave

RE: New T-SQL Features in SQL Server 2005 Part 1

Jeff Moden — Thu, 30 Nov 2006 13:52:00 GMT

Perfect, Mike... Thanks for the tip.

And I see what everyone is talking about, especially you and Confucius247... If you really want to get deeper, read the "Terms of Use" at the bottom of each page on the WebSite you just provided the link for... especially the part about "NOTICE SPECIFIC TO DOCUMENTS AVAILABLE ON THIS WEB SITE." Authors might want to be a little bit more careful than they have...

RE: New T-SQL Features in SQL Server 2005 Part 1

Mike C — Thu, 30 Nov 2006 08:44:00 GMT

No need to download it Just go to BOL on the msdn2 website: http://msdn2.microsoft.com/en-us/library/ms130214.aspx

RE: New T-SQL Features in SQL Server 2005 Part 1

Sureshkumar Ramakrishnan — Thu, 30 Nov 2006 08:41:00 GMT

Thanks for the comments.I think this would definitely help me to better my articles in future.

In the next article I have concentrated more on providing the examples rather than describing the features in theory.Hope you guys would like it

Yeah One thing I admit of not having done is that I havent added all the references.This Iam definitely going to do in my future articles.

RE: New T-SQL Features in SQL Server 2005 Part 1

Mike C — Thu, 30 Nov 2006 08:13:00 GMT

Here's what BOL says about CTE's (not at either link you posted, but rather here: http://msdn2.microsoft.com/en-us/library/ms175972.aspx)

"Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression."

Here's what the author said (http://www.sqlservercentral.com/columnists/sramakrishnan/2734.asp):

"Common Table Expressions : Common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. Unlike a derived query that was commonly used in SQL 2000, you dont need to copy the query definition multiple times each time it is used. You can also use local variables within a CTE definitionsomething you cant do in a view definition."

Basically the sentence fragment "defined within the execution scope of a single SELECT, INSERT, UPDATE, [or] DELETE" looks to be the same on this one. The reference the author posted to BOL at http://msdn2.microsoft.com/en-gb/library/ms190766.aspx has this to say:

"A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query."

Looks like a chunk of text was lifted word-for-word from BOL in this instance. I'm not going to personally compare the whole article to BOL, but the author should have definitely used parenthetical and in-text citations, or footnotes, so that readers could tell exactly what was being quoted from BOL versus his original work.

To the author, here's what it might look like with a proper citation:

Common Table Expressions : Books Online provides this definition of a Common Table Expression: "Common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement." (http://msdn2.microsoft.com/en-gb/library/ms190766.aspx). Unlike a derived query that was commonly used in SQL 2000, you dont need to copy the query definition multiple times each time it is used. You can also use local variables within a CTE definitionsomething you cant do in a view definition."

It's OK to quote from other sources, but you have to properly cite the sources. Properly quoting and citing authoritative sources on a topic does not detract from your original work, but not properly citing sources makes all your work suspect. Someone should write an article on that...

RE: New T-SQL Features in SQL Server 2005 Part 1

Sureshkumar Ramakrishnan — Thu, 30 Nov 2006 04:16:00 GMT

If you read the article properly

The article clearly states the below

For further reference on CTE read the link: http://msdn2.microsoft.com/en-gb/library/ms190766.aspx.

If you are expecting us to know the features of SQL 2005 without refering to MSDN.I think you are wrong

RE: New T-SQL Features in SQL Server 2005 Part 1

Confucius247 — Thu, 30 Nov 2006 04:10:00 GMT

It's not 'referring', you've cut and paste 90% of the text from BOL. Without even providing a reference.

If you copy random chunks of your article and put the whole phrase in Google, result number 1 is MSDN with an article with the same name as your section heading and the exact phrase in the MSDN article.

e.g. "Common table expression (CTE) can be thought of as a temporary result", "A recursive CTE is one in which an initial CTE", "following example first creates a synonym for the base object"

The exact word-for-word phrases are returned on MSDN. If you use word-for-word without putting it in quotes and providing where the original is located it is plagiarism, you would be thrown out of a University, fired from a Newspaper, never be able to work in the field again. To do it not once but as an entire 'article' is extreme.

And you've still not provided the references you used:

New features SQL2005: http://msdn2.microsoft.com/en-us/library/ms170363.aspx

New TSQL Features: http://msdn2.microsoft.com/en-us/library/ms177591.aspx

RE: New T-SQL Features in SQL Server 2005 Part 1

Sureshkumar Ramakrishnan — Thu, 30 Nov 2006 03:25:00 GMT

Thanks all for the comments on the article.I do agree there has been a bit of typing errors ,but I would be more careful next time.

The whole idea of writing this article came to me, as I realise there are many SQL Developers who are still working with SQL 2000 and dying to get hands on SQL 2005.I wrote this article specifically for them because Transact SQL is the easiest for any SQL developer to relate himself to.

I dont think referring MSDN can be termed as Plagiarism,after all we are using Microsoft product and there cant be anything more authentic.

RE: New T-SQL Features in SQL Server 2005 Part 1

Jeff Moden — Wed, 29 Nov 2006 21:43:00 GMT

NOW I remember why I never looked at BOL 2005 ... Microsoft pushes the .NET stuff down your throat even on simple documentation... BOL 2005 wants me to install the .NET Framework just to install it...

I cannot confirm nor deny the plagerism... with that in mind, I hate to cast a doubt, but that would explain the kinds of typos (words run together and 231 instead of 2³¹) in the document... gosh, I sure hope Sushila was right about this one and that it's a "compilation" and not a plagerism.

RE: New T-SQL Features in SQL Server 2005 Part 1

Jeff Moden — Wed, 29 Nov 2006 21:23:00 GMT

Thanks for the tip... I've not seen BOL 2005 so I couldn't tell just by reading the article... I'll check it out...

RE: New T-SQL Features in SQL Server 2005 Part 1

Mike C — Wed, 29 Nov 2006 15:21:00 GMT

Plagiarism is a pretty serious charge I'm gonna have to take a look at BOL when I get home and see for myself. I hope you're mistaken!

As for moving from SQL 2000 to SQL 2005, I've been working with 2005 since the Beta's and CTP's, and I can honestly say (from a developer's point of view) I'm impressed with the new functionality they've added. As you say, the documentation doesn't do it justice... I've found dozens of issues with BOL where the info. is either misleading or just flat-out wrong. Even submitted a few corrections to MS. We'll see if any of them get fixed.

The SSMS interface is a little "busy" for me... but then again I might just be used to having my QA and EM functionality in separate apps. But the tabbed query window, integrated online help search, and the ability to connect directly to source control come in very handy. BTW, AFAIK you can still use SQL 2000 SP4 QA to connect to SQL 2005, although EM doesn't connect.

As for the big bang - here's a few items (some of which were mentioned in the article):

- Being able to specify the structure of your FOR XML PATH results using XPath instead of the ridiculous node!node!node notation (who came up with that??) [SQL 2K5]

- Being able to manipulate large object (LOB) data without resorting to the wild and crazy TEXTPTR, READTEXT, WRITETEXT, etc. You can even create LOB local variables - try that in SQL 2000; and functions like LEN() work how you would expect them to! [SQL 2K5]

- How about being able to encrypt and decrypt your data directly in T-SQL instead of having to resort to third party tools that operate differently from vendor to vendor? [SQL 2K5]

- Let's not forget built-in XQuery querying of your XML data and the XML Data Manipulation Language (XML DML) statements that allow you to manipulate your XML data without resorting to COM object machinations -- that's huge! [SQL 2K5]

- Of course there's also SQLCLR integration which are a heckuva lot easier to write, much better documented, and a lot safer all around than extended stored procedures. Plus they give you the advantage of using the managed .NET libraries to perform tasks in a few lines that would have taken hundreds of lines of unmanaged C++ in an XP wrapper on 2000. [SQL 2K5]

- Let's not forget the new ranking and windowing functions like ROW_COUNT() that allow you to efficiently do simple tasks that used to require some of the strangest, most complex, and inefficient self join contortions you've ever witnessed. [SQL 2K5]

We could go on and talk about the improvements to existing keywords like TOP(@local_variable), new keywords like DISABLE TRIGGER, improvements to query plan caching and the SQL engine in general, improvements to security (EXECUTE AS, etc.), HTTP Endpoints, and even some of the more obscure things like NEWSEQUENTIALID.

There's definitely some meat on them SQL bones

RE: New T-SQL Features in SQL Server 2005 Part 1

sushila — Wed, 29 Nov 2006 15:08:00 GMT

I thought it was a great comprehensive overview....that's a pretty hefty accusation you make there....no one really has the time to go through BOL indepth..usually that's the first stop for most developers when they run into an issue but not otherwise..if the author has "compiled" an overview using BOL for reference that's ok....it crosses the acceptable boundaries/norms only if it's lifted word for word with no personal input...is that the case here...

RE: New T-SQL Features in SQL Server 2005 Part 1

Sean D. Rittenhouse — Wed, 29 Nov 2006 14:23:00 GMT

KILLER AND SIMPLER!

RE: New T-SQL Features in SQL Server 2005 Part 1

Confucius247 — Wed, 29 Nov 2006 13:11:00 GMT

Nice cut and paste jobbie from MSDN, ever heard of plagiarism? I avoid Microsoft technical documentation for good reason, namely that they find it impossible to write human readable documentation. I wish I'd avoided this article too. Jeff, you'd be better off linking directly to SQL 2005 Books Online, this is all lifted from there.But apart from being pissed off that you're low enough to try and pass this work off as your own, my actual beef is:"It is evident from the comparison [to SQL 7 and SQL 2000 improvements]; there is a huge new list of features that is included in SQL 2005"Being able to store lots of data in a single column, that's huge [SQL 7]. Being able to pump XML straight into your SQL so you can insert it, that's huge [SQL 2000]. Where's the big bang of SQL 2005? You've listed a bunch of small audience tweaks, not major improvements. What I was hoping for from this article, and which it spectactularly failed to deliver, was some real life examples of how SQL2005 was making life easier.I've not had that much of a look at 2005, most of our clients are still on 2000. But so far I've been disappointed. First impression was, it had a clunky GUI that somehow was actually worse than enterprise manager. It just served as an advert to not develop large applications in .Net.

RE: New T-SQL Features in SQL Server 2005 Part 1

B Hilderman — Wed, 29 Nov 2006 09:51:00 GMT

Your table really had me confused after the PIVOT example. The '3' is hanging outside the table and I had to really look at that to figure out that it was a bad table display and not a correct table display. Since I've never seen PIVOT before I assumed your table displayed correctly, but it can't unless I'm really slow...

RE: New T-SQL Features in SQL Server 2005 Part 1

Mike C — Wed, 29 Nov 2006 08:41:00 GMT

Very good information, and a nice reference. There are a lot of cool new features in SQL 2K5 - I suspect this is going to be a lengthy and much-appreciated series The xml data type and FOR XML enhancements are particularly interesting. Are you going to expand on the XPath/XQuery functionality in a future article of the series?

You got my vote

RE: New T-SQL Features in SQL Server 2005 Part 1

Jeff Moden — Wed, 29 Nov 2006 08:39:00 GMT

Hang the typo's!! There aren't that many and it's very obvious what they are/should be. Heck, I've seen more errors in published books that have supposedly been professionally proof-read and edited!

Sureshkumar, this is an outstanding article... you cover some of the previous enhancements in previous versions, you nailed many of the big, more useful changes, and you gave examples of many of them. Certainly, your summaries of each new feature are interesting and useful. Without getting bogged down in unnecessary levels of detail, you even hit the UPDATE.WRITE requirement.

I've had many people ask me to summarize some of the differences between 2000 and 2005... I'm just going to refer them to this URL from now on. Developers making the transition to 2005 should sit down with Books OnLine and use your article as a guide of what to study in depth. This should be required reading for all experienced Developers making the transition.

I think you've accomplished exactly what you set out to do with this article... Thank you VERY much for taking the time to write such a great overview of some of the new functionality available... I can't wait for you to publish Part 2 and, hopefully, a Part 3.

RE: New T-SQL Features in SQL Server 2005 Part 1

Vasant Raj — Wed, 29 Nov 2006 01:56:00 GMT

Good and informative article..

But lots of typo errors... [crucial errors]

Example: "VARCHAR (MAX) or VARBINARY (MAX) can store 231 (or about 2 billion) characters. A variable declared as NVARCHAR (MAX) can store 230or about 1 billion) characters."

It should be 2³¹/ 2³⁰

New T-SQL Features in SQL Server 2005 Part 1

Sureshkumar Ramakrishnan — Sun, 26 Nov 2006 11:00:00 GMT

Comments posted here are about the content posted at temp