New T-SQL Features in SQL Server 2005 Part 1
-
Comments posted here are about the content posted at temp
-
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 231 / 230
-
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.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
-
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...
-
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.
-
KILLER AND SIMPLER!
-
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...
**ASCII stupid question, get a stupid ANSI !!!** -
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
-
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...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 231) in the document... gosh, I sure hope Sushila was right about this one and that it's a "compilation" and not a plagerism.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
-
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
-
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
-
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...
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply