MS SQL server anyone?

[Terence Kearns]

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 
than the
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.

ps.
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 
like
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,
> michaelj
>