Feeds

Programmability of SQL Server 2005

The programmibility of a database is often what matters most to developers

  • alert
  • submit to reddit

Security for virtualized datacentres

T-SQL is Microsoft’s own version of SQL. Like most database companies, Microsoft supports most of the standards and extends the standards where it feels there is a lack.

So, SQL Server 2005 supports almost all of the ANSI 99 and 2003 SQL standards. As Euan Garden, product unit manager for SQL Server Tools, said back in 2004: "We have implemented the pieces of the standards that we feel were most appropriate."

[Which, some of us might think, means that they haven't really implemented standards at all; but a sort of "standards... but" instead - Ed.]

Without doubt, the biggest programming news is that SQL Server 2005 supports the use of languages other than SQL for writing stored procedures and triggers. This is implemented via the Common Language Runtime (CLR) and .NET integration. So these components can be written in any CLR language such as C#, Visual C++ and Visual Basic .NET.

When this capability was announced it received a very mixed reception. SQL programmers were instantly up in arms saying that SQL was the one true language for set-based operations and it was heresy to replace it with a procedural language (of any flavour whatsoever). Microsoft has subsequently spent a great deal of time and effort trying to get over the message that there was never any intention to replace SQL. These languages are simply an alternative that can be used as and when the programmer deems fit.

I am a SQL programmer, I love the language. But I also know that there are certain operations that I need to perform which are inherently much easier to write in a procedural language; so I welcome the addition of the CLR capability here.

Will people misuse this new capability? Undoubtedly. But then humans also misuse virtually everything they are given, so this isn't a good reason to fear it.

SQL Server 2005 also boasts some new data types that include Varchar(max), nvarchar(max), and varbinary(max). These are alternatives to text, ntext, and image datatypes; and can store up to 2 GB of data. The new datatypes are likely to be used mainly in data warehouse applications to store extended metadata and other descriptive information. User-defined custom datatypes are also supported.

There is also a range of new analytic functions such as ROW_NUMBER, RANK, and DENSE_RANK, which are return values to do with row numbers. There is also NTILE, which can split an ordered set into a specific number of groups of approximately equal size.

And then, of course, there is XML. No self-respecting database engine can afford to ignore XML, so SQL Server 2005 doesn’t. It is supported as a native data type; indexing and full-text searching are supported and there is also XQuery for querying XML.

Return to Page 1 of main article here - otherwise use "back" button.

Intelligent flash storage arrays

More from The Register

next story
PEAK APPLE: iOS 8 is least popular Cupertino mobile OS in all of HUMAN HISTORY
'Nerd release' finally staggers past 50 per cent adoption
Microsoft to bake Skype into IE, without plugins
Redmond thinks the Object Real-Time Communications API for WebRTC is ready to roll
Microsoft promises Windows 10 will mean two-factor auth for all
Sneak peek at security features Redmond's baking into new OS
Mozilla: Spidermonkey ATE Apple's JavaScriptCore, THRASHED Google V8
Moz man claims the win on rivals' own benchmarks
Yes, Virginia, there IS a W3C HTML5 standard – as of now, that is
You asked for it! You begged for it! Then you gave up! And now it's HERE!
FTDI yanks chip-bricking driver from Windows Update, vows to fight on
Next driver to battle fake chips with 'non-invasive' methods
DEATH by PowerPoint: Microsoft warns of 0-day attack hidden in slides
Might put out patch in update, might chuck it out sooner
Ubuntu 14.10 tries pulling a Steve Ballmer on cloudy offerings
Oi, Windows, centOS and openSUSE – behave, we're all friends here
prev story

Whitepapers

Why cloud backup?
Combining the latest advancements in disk-based backup with secure, integrated, cloud technologies offer organizations fast and assured recovery of their critical enterprise data.
Forging a new future with identity relationship management
Learn about ForgeRock's next generation IRM platform and how it is designed to empower CEOS's and enterprises to engage with consumers.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
Intelligent flash storage arrays
Tegile Intelligent Storage Arrays with IntelliFlash helps IT boost storage utilization and effciency while delivering unmatched storage savings and performance.
Managing SSL certificates with ease
The lack of operational efficiencies and compliance pitfalls associated with poor SSL certificate management, and how the right SSL certificate management tool can help.