MS SQL server anyone?
tkearns at fastmail.fm
Thu Sep 5 23:31:49 EST 2002
I have experience with MS SQL server and PHP/ColdFusion.
Personally, I find postgresql easier to install and use. Mostly MSSQL is
fine to install - er, unless something goes wrong... you can probably
solve that dilemma by crossing your fingers while running setup.
Also, since Postgresql is a lot more like Oracle, it is easier to
migrate when it comes time to upgrade. At the last place I worked, we
had to upgrade to Oracle because MS SQL wasn't cutting it - it was a
nightmare but the performance increased 10-fold. Postgresql is almost
like a lite version of oracle it's so similar.
Also, a pet hate of mine is that ms sql's IDENTITY "feature"...
in order for it's insert... select @@idenity construct to work (which if
you ask me, looks dodgey anyway), we had to wrap it in a stored
procedure when using it against cold fusion. At least this was better
insert into foo...
select max(is) AS id
<<spot the race condition>> which is what some of the predecessor
developers resorted to (no, they didn't lock the transaction either).
These and other hacks actually made it more cumbersome to use than a
decent database such as postgresql.
If you don't have unlimited funds and you can't afford the expense of
Oracle, then by all means, start with Postgres. It's got all the
features you'll need and can be tweaked to perform exceptionally well
should the need arise. Otherwise, just use it out-of-the-box.
Just because it's MS doesn't mean that MS sql server is easier to use.
I'm not saying anyone suggested this but it seems to be a popular
opinion amongst others. If it's GUIs you want, you can get a reasonable
windoze one at http://www.pgadmin.org
GUI management isn't the issue for me. Ease of development is, and for
my er.. money, I'd use postgresql. It costs less developer time. This
makes it very attractive from a total cost of ownership point of view.
It will also do all those things you asked about including SQL dumps
without bouncing it.
Yes, MS SQL server used to be Sybase which is why you can use "tabular
data stream" protocol with it. TDS is what the sybase PHP module uses so
the mssql [native] functions in php are aliases for the sybase functions.
while I'm on about it, I don't know that the ANSI compliance thing is
all that big a deal anyway (I'm _usually_ a staunch standards person).
For instance, ANSI says that fields with reference constraints cannot
have a null value. In many practical implimentations, you want to either
have null, or a value that can be referenced from some foreign key. So
in many ways, features that break compliance are not only
"nice-to-have", but are essential in the real world. Aside from that ms
sql server is just down-right quircky. It allows you to do dodgey things
WHERE foo IN ('blah1','blah2','blah3')
and then foo can be null and SQL server won't raise an error!
That's another example of it allowing no-idea lazy programmers to get
away with shit code. This just introduces unpredictable behavior into
your code because situations aren't "handled".
Anyaway... no point wasting any more energy on this topic (ms sql
server). hmm... I wouldn't touch it with a ten-foot pole given the choice.
oh yeah, and I hate how they call it "sql server" - like it's the only
one on the planet... That alone is reason enough to stay away from it IMNSHO
I'm done now... :/
Michael James wrote:
> A vendor is trying to sell us a membership database
> but at present it only runs against a foxpro ODBC file
> or a microsoft SQL (Sequel?) server.
> I don't like the ODBC file idea.
> A system involving shared access to a file
> (between our Linux/Solaris and their Windows systems)
> sounds like an invitation to untraceable db-file corruption.
> So that leaves us with a windows server,
> but it's just answering SQL queries on the intranet,
> it need not be visible from the internet.
> Does anyone know of any glaring faults of MS SQL?
> Can Perl DBI and PHP work with it?
> Does it provide reasonable robustness,
> the ability to throw down checkpoints
> ie: a consistent snapshot of the DB, while running,
> or dump the DB into an ascii file of SQL commands like MySQL?
> And for the conversion from the existing foxpro 2.6 system,
> what open source tools exist to read a Foxpro data file.
> Could they dump it into SQL that could be loaded into a MySQL/Postgress server?
> TIA for any experience in this area,
More information about the linux