1. "row compression stores fields using a variable width format"

I must be mis-reading this... it's not seriously suggesting that the database was storing row data in a fixed width format, is it ?

2. What the hell are the BI tools actually ? Sceptical minds want to know...

Break it!

Paris: cuz she doesn't think of LAMP as a single unit in the way the acronym suggests: she embraces the fact it comprises four components, which work great sans MS.

Yes, but does the optimiser work yet?

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=350485

Maybe I should work for Microsoft, its got to be damn easy to optimise the resultset when you don't care about what is being returned!

"Transparent Data Encryption (TDE) is another new feature and can be implemented at the engine level, meaning you can protect an entire database without having to change anything in the applications."

AFAIK this is only available in the Enterprise edition. I hope I'm not correct about this, as we would really like our customers (who use the Express edition) to be able to use it to protect their data.

see title

WANK !

Lessee.

XP is the upgrade to Vista.

Office 2003 is the upgrade to Office 2007.

The verdict is still out on VS2008, but I'm having trouble figuring if it's really an upgrade.

I'll be suspicious. I'm thinking SQL 2005 is the upgrade to SQL 2008.

And what is the acronym for Windoz, IIS, SQL Server and ASP .Net?

WISA?

WISAN?

WISN

WISHESWEREHORSESTHENBEGGARSWOULDRIDE?

(Business Intelligence: That's an oxymoron, right?)

Maybe the Phoenix lander can ask the Martians what DB and O/S they use.

Or is it only Internet Exploder-enabled...?

I think we should be told...

Q - How many Microsoft employees does it take to change a light bulb?

A - None... Microsoft simply redefines darkness as the new standard for lighting systems...

... if you want a flint axe wrapped in a tonne of imitation watches and the quality of support that only a monopolist would dare offer. MS SQL Server is the sort of geriatricware any sane developer would pay dearly not to support. This adds another few bags on the side, a new coat of paint, but the same old dusty skeleton's lurking inside, because after all these years (like most of what MS has landed itself with) it's fundamentally old-fashioned and unmaintainable. The new features seem tacked on, which in my view is a clear sign that the maintainers are scared to touch the guts of the thing. Probably any time they do pluck up the courage to shove an arm up the cash cow it falls apart.

Basically, if you strip off the really peripheral stuff from any new release of SQL Server since 7 you find yourself looking at an old version of Sybase without the portability, with ever increasing layers of the latest UI fashions and a growing horde of amateurish client applications with each new version.

But hey, I'm a Linux fanboy, what do I know? :)

I don't like to be pushed into defending MS, but @AC, you're doing an unjustified (IMO) hatchet job - don't use non-deterministic functions, and @Geoff Mackenzie,

"if you strip off the really peripheral stuff from any new release of SQL Server since 7 you find yourself looking at an old version of Sybase without the portability,"

Cobblers. 2000 and 2005 are strongly based on SQL 7, with polish added. IIRC SQL 7 was a ground-up rewrite of the core (thank god, as I know how flaky 6.5 was. It's optimiser really was snafu'd). I can say from experience it's a damn good optimiser, once SPs are applied. Also

"the quality of support that only a monopolist would dare offer" - I've had to deal with them, for £200 for a single support call you get some truly competent end-to-end support. I mean *really* good; someone who knows what they're doing. And the SQL server docs are the best of any software I've ever used, by a long way.

I have no love for MS, but at least diss them when they actually have earned it, not just because breaking & hurting things is fun (I used vista for a week recently - lawd! awful. Put your teeth into *that* & I'll sell tickets and lollies. And SQL server licensing is baroque at best)

And @Tim Parker, question 1, I'm not sure what that means either. I'm sure they had varchars, varbinaries and assorted blobses. I guess Mr. Whitehorn means post-compression sizes can unpredictable.

Regarding compression, I guess someone will ask why not use the native windows file compression. It's recommended against using compression for sql2000 (won't even install on a compressed folder; you have to install it uncompressed then apply compression after) because it breaks the write ahead protocol which it uses to ensure data integrity. May apply to encryption as well, can't remember. This may not apply to later versions; dunno.

If you'd actually you know, bothered to use some of this stuff instead of hiding behind a bunch of prejudices, you'd find that most recent web-enabled MS stuff is browser independent.

The server knows if a function is deterministic or not, the optimiser should take this into account when creating its plan. What other optimisations are in there which effect the results??!!

I partially concede your point since I have to admit I'm bashing '08 without having used it. I just can't forgive them for the lack of a decent range of data types in '00 and general primitiveness of all the SQL Server releases I've played with (including '05, which was a real disappointment - I was actually quite excited about the new client tools until I realised they still expected me to edit code in a proportional font in a dialog box ...)

But you've obviously had better experiences of MS Support than I have. Last time I dealt with them they wanted an extra £300 or so for an old patch for a memory leak in Win2k that used to be free until they discontinued support.

Anyway, since you've partially got me on this one I promise I'll bash only Vista for two weeks. And I'll buy one of your lollies myself if you hold up your end of the bargain. :)

Oh, yeah. I agree. PostgreSQL ftw!

Not sure what Mr Whitehorn is on about, 2005 converts the Char types into varchar before before writing unless of course it does this and then writes out to the fixed length anyway. The BI tools they provided in 2000 were a joke, SSIS in 2005 is better but still a long way behind other tools out there Be interesting to see how the tools perform in 2008

Yawn, another bland review.

Truth us is Microsoft are once again not exactly innovative when it comes to delivering new features and value to its customers;

Latest "New" features for Microsoft customers include;

Spatial - available in Oracle 7.2 in 1995 (13 years ago)

Resource Manager - available in 8i in 1999 (9 years ago)

Compression - available in Oracle 9.2.in 2002 (6 years ago)

Policy Based Management - available in 9.2 in 2002 (6 years ago)

Change Data Capture - available in 9.2 (6 years ago)

Transparent Data Encryption - available in 10.2 in 2005 (3 years ago)

So hardly "new" are they.

Things is, although you pay a high price for Oracle, you usualy get an additional 6 years of value, compared to Microsoft.

Oracle=BMW, Micosoft=Daewoo

If all you need is a car, then fine, these features aren't important to you anyway.

By the way, did you know that SQLServer only runs on Windows.....surely such a "good" database would sell on other platforms as well.

Just spent a month developing on SQL Server for the first time since 2001ish, and I have to say... I like it... I enjoy any platform where I can make stuff work quickly and refine it easily...

I'm actually pretty excited about 2008, given it's support for hierarchical data and better (but not complete) temporal data types. You try and model businesses with 5 levels or more levels of subsidiaries in MySQL... without resorting to horrible nested set queries that make your brain hurt. And the new auditing features look pretty killer to me...

It's starting to look a bit more like the featureset of Oracle but less arcane.

About 7 years ago, at an e-tailers, I learned the value of middle tier architecture. For a bet with a Yank I removed CommerceServer and SiteServer and replaced the commerce engine of a major etailer with a single stored procedure, (plus one cleanup procedure - so two then.) It ran for about 18 months, and apart from no-one being able to understand it's set based design, and a bit of locking at Christmas, it ran really well.

In order to solve the locking, I thought of moving the content, onto a series of replicated servers, behind a load balancer, each Asp page then opening two connections at page elaboration, one for read only stuff, (eg pricing, stock, descriptions) and the other for the read write stuff (eg basket and account.) This stopped the locking happening, but it didn't "solve" it perse. I learned about middle tier architecture to ultimately sort this.

It turned out though that this "readonly servers behind a load balancer" is infinitely scaleable, and with SQL 2008, they've introduced it too, though they do it with a SAN for BI, whereas my solution does it in any kind of fashion using multiple copies of the data.

This is ironic, because there's a "consultancy" of "architects" aka middle tier only developers, in the old place right now, explaining in gravelly toned voices to senior management over lunch about how "you can't put business logic in a database." Well, you can, as microsoft has proscribed.

The fact that this solution in its native format is no more than an infinitely scaleable, multi view, self updating cache of the data core, seems to have been missed. Don't you just love consultancies?

The real pity is that I was interviewed by a friend in his flat a year later for a titannic job, and the interview was full of loaded questions about "What would you do if someone was wrong, and they were insistent they weren't?" Since I knew he was talking about this project, and I knew I wasn't wrong, I ignored the question. Good to know I've been vindicated, but I was sad at the time, because I didn't get the job.

Anyway, whilst I hate Vista and all its gui nonsense, & change for change's sake rubbish, I think SQL Server is an excellent product, and I'm glad they steal other people's ideas, assess them, and put the ones that work into future products.

It's so much better in my view than having hundreds of different groups of developers all developing vastly different concepts around the world, and then deciding it's someone else's problem to integrate them.

It's also got native Linq support, which means over time, that all the graddies with only middle tier abilities will see the advantages of balancing the workload again. This is good for microsoft, because it means their architects will deliver a bigger percentage of successful projects than those where everything's done in XML because it's new, and everything's done in hibernate because everyone else does it.

I like both these technologies, but I'll be so glad when people realise hibernate isn't the be all and end all of software engineering, like they have with XML.

There are nevertheless some things I'd do different in sql 2008.

I'd allow reverse sql, a la Linq, so intellisense really worked.

I'd also have the ability to update two tables at once in a join, if one of them's a datatable, (non deterministically would be ok,) which would be drastically more powerful than the Merge statement.

I'd also acknowledge that SQL Server's not a pure relational product, and develop the ability to do ordered updates. Yes I know this can be done with cursors, but have you tried to outperform the merge statement with transact? You can't. Or at least I can't (I've got within 10% on occasion,) and I like to think I'm pretty useful at this kind of thing.

And I'd extend their mirroring even further, to have point and click readononly copies as above, with merge replicated tables using modular arithmetic to allow simple insert/updates based on the function parameters mechanism used by Partitioned Views to allow write/deletes to be parallel replicated with the touch of a button too.

I'd have a wizard which auto partitioned a huge single database, to a series of different servers, to allow massive parallel non relational but still representable by Linq query, for reasons that I won't go into, but it's just a little project I'm working on. Sort of a customistable DataMart generator, which did it via functions so it could handle additions. Yes I know OLAP handles incremental dimensions, but I don't think SQL, or OLAP are complete solutions for anything. I think they're merely subsets of a Unified query.

But on the whole, it's super.

If you look up MS KB956139, you'll find that some of the optional bits of SQL Server 2008 won't work with Visual Studio 2008 sans service pack. They include bits of VS.2008sp1, which is due to be available Monday 11th August. Hopefully, that'll save some people a few hours of tinkering and swearing.

@AC re determinism

You certainly have a point about plans & determinism. I dunno the solution beyond just not using such functions. You also say "What other optimisations are in there which effect the results??!!", well, before the stuff about short-circuit evaluation came out, I'd have said 'nothing', guaranteed. I'm now not so sure.

@Geoff Mackenzie

Seems you're speaking from bitter experience. All I can say is that MS SQL server support are gems (and on editing code, you should be able to set pretty well any option you desire, font-wise. Or use the command-line tools if not. Sorry if you know all this).

But.. Vista... Beat it with a big stick for as long as you please. Fill your boots. I'm happy to watch and you get all the lollies you want. Sadly they're e-lollies so they taste like a 9-volt battery, but this buffet's all-you-can eat, till your arm drops off from exhaustion.

Back to optimisers and correct queries, I think the main problem is actually the sql standard and the weirdness it contains. For example, from an internal doc I wrote to illustrate this, make 2 tables and fill them (we'd normally have FK's etc. Assume this is dirty data going through a cleanup phase).

---

create table tests

(

test_id int,

other_fields_omitted_here varchar(100)

)

insert into tests

select 23, null

union

select 45, null

union

select null, null

union

select 78, null

create table results

(

result_id int,

other_fields_omitted_here varchar(100)

)

insert into results

select 45, null

union

select 78, null

union

select 101, null

union

select 604, null

---

Crappy practice, this, but it's for illustration.

Now, find results for which there are no tests. There clearly are some, for example 604, so write this:

---

select *

from results

where result_id not in ( select test_id

from tests )

---

Sadly, zero result. Nulls bugger it. You need to say

---

select *

from results

where result_id not in ( select test_id

from tests

where test_id is not null )

---

This caught me out in a data scrubbing phase & cost me a day of perplexed digging. I'll *bet* it's a significant source of bugs. It's an obscure behaviour, but correct per the sql standard.

And what about transactions - you'd think that if you put

begin transaction <stuff_here> commit

around a block then it is atomic. Even leaving aside isolation levels (which most sql users don't even know about, never mind understand), it may not - it can fail in a small but significant way in the <stuff_here> bit and *not roll back the transaction*. It's stupid and wrong but that's what the sql standard seems to mandate.

My point is that optimiser plans & nondeterminism isn't the biggest problem, not by a long way.

"In summary, then, SQL Server 2008 has some nice features and is a database that I shall certainly be using more of"

Sorry to burst your bubble, but SQL Server 2008 is not a database. SQL server has never been a database (it does create some, such as "master", but that's not the product).

Please learn you subject area before reviewing such a product.

As for @BlueGreen, read about "null", you clearly don't understand its meaning.

Most people here know the difference between a database and a database server, and gloss over the essential difference for brevity without loss of clarity.

As for me, what are you talking about. Trinary logic that is caused by nulls as defined in the sql standard cause indirectly the problems I was referring to. If that's not what you meant, be specific.

I'm primarily a business-logic layer guy, and rather mediocre at the database side of things (slowly improving), but having jumped around between MySQL, SAP (running on Oracle), and SQL Server in the last few years, I gotta say that I'm quite pleased with SQL Server 2005 and am quite looking forward to the new features. This was a good article that was quite informative on what's new in SQL Server 2008. I'm particularly looking forward to the described report designer, as the one included in VS 2005 is pure crap (better than Crystal Reports, but still crap).

I didn't see any mention of the new "Date" data type in 2008...That, I think, was *just a tad* overdue. I'm tired of having to store all my dates with times included.

I'm with BlueGreen, feel free to bash MS when they screw up. But when they come out with a decent product (C#, the .NET Framework, SQL Server, Flight Simulator, and the original XBox, for example), the bashing just comes off a bit petty.

If BlueGreen will hand out lollies at the Vista bashing convention, I'll sell popsicles at the IE bashing convention. But we expect to see some Java fanbois passing out sno-cones at the C# 3.0 convention. And MySQL fanbois (if there are any left) at pretty much all conventions.

@Geoff Mackenzie

"they still expected me to edit code in a proportional font in a dialog box "

It took me a while to figure out what you were talking about...Yeah, I would hate SQL Server too if I thought I had to edit the code through the interface included in VS 2005 or enterprise manager...Next time give SSMS a try :)

I'm sorry but I totally disagree. It is not brevity to refer to a DBMS as a database, it is just incorrect. It's like referring to Microsoft Windows as Microsoft Office. Such errors really annoy me because some people do not know the difference and have their misconceptions re-enforced.

As for your beef with nulls, Please do read about the meaning of null, it should help you to realise why we have to jump through such hoops when dealing with it.

A brief explanation of it is that null is considered an unknown value. As a result you cannot safely say that 5 != null, because that null value may actually be 5. So given that WHERE Col NOT IN (a,b,c) is basically broken down into WHERE Col != a AND Col != b AND Col != c, if a, b, or c is null then this obviously evaluates to false.

Another common mistake with null include not realising that (a != b OR a = b) can actually evaluate to false if a or b is null because a neither equals be nor does not equal b, we can't be sure of either (I'm not sure if some optimisers would actually remove this statement though - they shouldn't).

picking a fight with the SQL standards is a very brave fight to pick, they were not written lightly. Can you imagine being without null, you clearly need it given that you're using it and encountering issues. So with the necessary evil of nulls come the explicit handling of the case in your logic.

Re. your point on brevity, I disagree with your disagree but you can take it up with the author.

On nulls, I know exactly what they are and precisely why the example I gave went wrong. The point was, it was a subtle issue that caught out me some years ago, and the guy who gave me the wrong code to debug, and no-one in my company at the time knew it. I wasn't trying to explain logic over t/f/u, just to alert people to a nasty gotcha (and the roots of it, which is the presence of nulls).

It was covered in an article on sql server professional (before it went downhill) because it is so nasty.

Regarding the sql standard try reading some of Date's work - "an introduction to database systems" is just brutal. He's the heavyweight and he can hold his own without my help.

On nulls he also says ('database in depth' isbn 0596100124, p. 164)

"

Regarding nulls, incidentally, I note that Codd first defined the relational model in 1969 and didn't introduce nulls until 1979; in other words, the model managed perfectly well (in my opinion, better) for some 10 years without any notion of nulls at all. What's more, early implementations managed perfectly well without them too

"

He also says on nulls, (from memory) "if there are nulls in your database, you are getting wrong results". IIRC it's that blunt. IME they are certainly are source of errors; the one illustrated isn't the only one iv'e come across.

In truth I never liked nulls and use them sparingly.

I should have read your answer more carefully. I believe your explanation is wrong. If it was correct then

WHERE Col IN (a,b,c)

should also return no results, but it does (try it).

You say

WHERE Col NOT IN (a, b, c)

evaluates to

WHERE (Col != a) AND (Col != b) AND (Col != c)

which I think is right, more or less, but assuming b is a null then you get

WHERE <expr> AND (Col != NULL) AND <expr>

let's assume both <expr> are TRUE for the moment.

The important bit is (Col != NULL) evaluates to UNKNOWN - not true or false!

so you get

WHERE TRUE AND UNKNOWN AND TRUE

which axiomatically evaluates to UNKNOWN. This is neither TRUE nor FALSE, but as it is not strictly TRUE, the WHERE condition fails - but the condition (I repeat) has not evaluated to FALSE.

(I hope I got the above right)

Nulls imply tristate logic for comparisons, which requires an UNKNOWN (or more strictly, a value between true and false usually given that name) which is where complexity lies.

Without intending to be rude, the fact that you (I think) incorrectly claimed that it "obviously evaluates to false" shows the subtlety of the issue.

The Nulls Have It.

I'm going back to Paradox.

I think they defined "NULL" as

"The space between a DBA's ears where easily-avoidable pitfalls overwhelm the ability to function rationally."

Or was the answer, "42?"

Ok, on the point that Microsoft SQL Server is a DBMS and not a database, we can agree to disagree about how important such accuracy is in an article.

WHERE Col IN (a,b,c) would break down to OR's rather than AND's, so would only be the same reasoning if all the values were null.

yes, you are correct with the "unknown" evaluating, my point with "obviously evaluates to false" should have read "fails to evaluate to true". I was trying to explain the reason for the error without muddying the water. However it seems I have misunderstood what you were arguing. I believed you were suggesting that the behaviour was illogical by the quotes "It's an obscure behaviour", and the main problem is actually the sql standard and the weirdness it contains". However you clearly know the logic, so we'll also have to agree to disagree on the obscurity and weirdness (I think it's pretty common and expected behaviour).