Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

New T-SQL Features in SQL Server 2005 Part 1 Expand / Collapse
Author
Message
Posted Sunday, November 26, 2006 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 2:55 AM
Points: 142, Visits: 37
Comments posted here are about the content posted at temp


Kindest Regards,

Sureshkumar Ramakrishnan

Post #325518
Posted Wednesday, November 29, 2006 1:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 7:23 AM
Points: 199, Visits: 136

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

 

 

Post #326311
Posted Wednesday, November 29, 2006 8:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #326408
Posted Wednesday, November 29, 2006 8:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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

Post #326410
Posted Wednesday, November 29, 2006 9:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 5, 2008 9:56 AM
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...
Post #326449
Posted Wednesday, November 29, 2006 1:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 27, 2009 4:01 AM
Points: 14, 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.
Post #326525
Posted Wednesday, November 29, 2006 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 12, 2007 5:35 PM
Points: 1, Visits: 1
KILLER AND SIMPLER!
Post #326541
Posted Wednesday, November 29, 2006 3:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:20 AM
Points: 2,555, Visits: 607
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 !!!**
Post #326562
Posted Wednesday, November 29, 2006 3:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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

Post #326565
Posted Wednesday, November 29, 2006 9:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #326601
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse