Feeds

Programmability of SQL Server 2005

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

  • alert
  • submit to reddit

3 Big data security analytics techniques

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.

Top three mobile application threats

More from The Register

next story
This time it's 'Personal': new Office 365 sub covers just two devices
Redmond also brings Office into Google's back yard
Inside the Hekaton: SQL Server 2014's database engine deconstructed
Nadella's database sqares the circle of cheap memory vs speed
Oh no, Joe: WinPhone users already griping over 8.1 mega-update
Hang on. Which bit of Developer Preview don't you understand?
Microsoft lobs pre-release Windows Phone 8.1 at devs who dare
App makers can load it before anyone else, but if they do they're stuck with it
Half of Twitter's 'active users' are SILENT STALKERS
Nearly 50% have NEVER tweeted a word
Internet-of-stuff startup dumps NoSQL for ... SQL?
NoSQL taste great at first but lacks proper nutrients, says startup cloud whiz
IRS boss on XP migration: 'Classic fix the airplane while you're flying it attempt'
Plus: Condoleezza Rice at Dropbox 'maybe she can find ... weapons of mass destruction'
Ditch the sync, paddle in the Streem: Upstart offers syncless sharing
Upload, delete and carry on sharing afterwards?
Microsoft TIER SMEAR changes app prices whether devs ask or not
Some go up, some go down, Redmond goes silent
prev story

Whitepapers

Top three mobile application threats
Learn about three of the top mobile application security threats facing businesses today and recommendations on how to mitigate the risk.
Combat fraud and increase customer satisfaction
Based on their experience using HP ArcSight Enterprise Security Manager for IT security operations, Finansbank moved to HP ArcSight ESM for fraud management.
The benefits of software based PBX
Why you should break free from your proprietary PBX and how to leverage your existing server hardware.
Five 3D headsets to be won!
We were so impressed by the Durovis Dive headset we’ve asked the company to give some away to Reg readers.
SANS - Survey on application security programs
In this whitepaper learn about the state of application security programs and practices of 488 surveyed respondents, and discover how mature and effective these programs are.