Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


New T-SQL Features in SQL Server 2005 Part 1


New T-SQL Features in SQL Server 2005 Part 1

Author
Message
Sureshkumar Ramakrishnan
Sureshkumar Ramakrishnan
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 37
Comments posted here are about the content posted at temp


Kindest Regards,

Sureshkumar Ramakrishnan

Vasant Raj
Vasant Raj
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 137

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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45450 Visits: 39946

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168

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


B Hilderman
B Hilderman
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 40
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...
Confucius247
Confucius247
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 40
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.
Sean D. Rittenhouse
Sean D. Rittenhouse
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
KILLER AND SIMPLER!
sushila
sushila
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2565 Visits: 639
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 !!!**
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168

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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45450 Visits: 39946

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search