This document provides an overview of new features for database development in MS SQL Server 2005.
An Overview of SQL Server 2005 for the Database Developer
About The Author
Eugenia Loli
Ex-programmer, ex-editor in chief at OSNews.com, now a visual artist/filmmaker.
Follow me on Twitter @EugeniaLoli
As a database developer, you now have the option to appropriately locate your code in relation to its functionality, to access data in native formats such as XML, and to build complex systems that are driven by the power of the database server. Database development is becoming more integrated than ever before, and all of the tools that you need are available right at your fingertips.
Locate my source code? For the database server? In Pl/Python, or that crummy Transact-SQL stuff? Nein danke.
Postgresql has had most of these new features for awhile.
And if I am not mistaken Postgres will have a PLC# procedure language via mono and a realy robust PL-Java
For most in house corporate applications Postgresql can do anything M$ SQL server can do. Also the postgresql .net dataprovider is excellent (plpgsql)
People think that because M$ SQL serer is GUI based that it is easier to maintain, but it is simply not true. I have seen instances of moving a M$ SQL database from one server to another that took 2 days because of various ownership issue etc. To do the same thing on PG takes 5 minutes.
All in all M$ SQL server is not worth the price.
Anyone care to comment?
I’d like to comment…
I think that like anything else, you need the right tool to do what you’re trying to accomplish. Personally, SQL server has been awesome for me, and I’ve never had any real problems with it. However, that’s not to say I’ve done a large enterprise solution with it. For small groups, medium groups; if it works that’s fine.
I agree with you about moving databases between servers. That’s always been a pain in the backside, i don’t know why it’s this way. The existing groups in the database apparently need to be deleted, and recreated; (sort of the NT sid issue I suppose). But, I’m just a small time developer, so that wasn’t a big deal. Frustrating, annoying, but not a big deal. Enterprise production servers: hmm, I don’t want to deal with that problem.
I am no huge fan of M$, however one must admit that they do have a lot of really cool stuff coming out these days, and they seem to be addressing a lot of the old crap that people hate them for (strange apis, stupid controls) with .Net. I’ve been developing with M$ stuff for 5 years now, and I honestly think that if they do embrace open source, (albeit in a non-traditional oss method), I think linux/whatever zealots are going to have a lot of catching up to do with their (M$’s) existing code base. SQL server is no exception.
I like the idea of OSS, but when it comes down to it; my boss doesn’t pay me to do cool or cutting edge stuff. It’s simply get the crap out there, and do it yesterday. I hate that attitude, but it’s what pays the bills. We’ve had pretty good luck with SQL server, and really how often do you move a database between servers anyway? If it’s that often, there’s something else wrong.
Cheers!
Just think how much money you could save your clients by using Postgres instead of M$
For a small workgroup or department is the large M$ SQL server price tag really worth it?
Postgres is not really cutting edge, it’s just a rock solid RDBMS.
The only point I am trying to convey is anything you can do with M$ SQL server you can do with Postgres for a lot less.
Sql2k5 will support xcopy deployment of *df files, so if that was a show stopper for you before, it won’t be now. i’ve never used postgresql, but from everything i’ve read/heard about it, it doesn’t scale up/out well at all. i also went to tpc.org; none of the systems tested use postgresql (b/c no one has submitted a system on postgre), and the top price/transactions per minute pretty much belongs to MSSQL (http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp). also, postgressql can’t run on windows natively (of course MSSQL can do this).
MSSQL has matured quite a bit since the days of <6.5 and routinely performs as good as oracle/db2. if i never had to write another line of TSQL, tomorrow wouldn’t be soon enough.
Hi,
We use MSSQL here for our main DB. We tried MaxDB which our data crushed, and we use a little MySQL. We routinely insert/update 500,000-1,000,000 rows a day in multiple databases. Our experience with Postgresql is that it gets crushed with that many updates. So far MSSQL has worked for us fine without a problem.
ndt
Today someone posted this URL to pgsql-advocacy mailing list:
http://www.netezza.com/news/media/META-Spectrum-Netezza.pdf
it’s about a 27TB Postgres database.
… so if anyone tells you that PostgreSQL doesn’t scale, point them to that. 🙂
If most of that 500k-1mil insert/update is update, you _might_ be having problem with the MVCC architecture of Postgres, which creates new record version with each update. Firebird/Interbase and MySQL+Innodb might have the same problem if you tried it, since they both also do MVCC. The current MSSQL doesn’t do MVCC, just like MySQL+ISAM. I also maintain a database which gets UPDATE-d very often, so I am currently sticking with MySQL+MyISAM. I tried switching to MySQL+InnoDB once, but load is considerably higher due to higher disk activity.
The trick is to reduce update activity, by some caching mechanism.
Or perhaps you haven’t tried VACUUM-ing the Postgres database more often when you tried it?
also, postgressql can’t run on windows natively (of course MSSQL can do this).
This meme will probably go on for another 1-2 years, just like “MySQL doesn’t do transactions”. 🙂 Good news: PostgreSQL 8.0 will come with native Windows support. Expect the beta of 8.0 sometime this week or the next.
MSSQL has matured quite a bit since the days of <6.5 and routinely performs as good as oracle/db2. if i never had to write another line of TSQL, tomorrow wouldn’t be soon enough.
I’ve also heard good things about SQL Server. Interestingly, Postgres is now at 7.x (will be entering 8.x soon). Back in the days of 6.4-6.5, it’s also kind of fragile, slow, and unstable. Postgres has come a long way now: it’s very robust, quite fast, and has many bells and whistles that often belong with a commercial database. The things lacking in Postgres compared to MSSQL are probably GUI tools (design tools, query visualization tools, OLAP/data mining/BI tools, etc). Everything else: performance, scalability, features, documentation, and community are very solid. I recommend you to try it out. You can’t beat the price of free anyway 🙂
IMO, Postgres certainly will be the prominent open source database in the future. MySQL, MaxDB, and Firebird are [sadly] just not moving forward fast enough… There’s a new kid in town, Ingres, but I still doubt whether it will really take off as an open source project, given Postgres’ many years of lead time.
Postgres could handle that with ease.
You didn’t specify how you are doing these updates i.e. if it is from a dataload or what, but in such cases you need to do your updates/inserts in a transaction and do say 1000 per transaction.
MS SQL server does a lot of automatic optimization which cause MS developers to become sloppy and not practice good design behavior. I ported a M$ database to postgres and the guy who originally created it never even bothered to use primary keys. It worked good on M$ because it would automaticly create the PK for the tables.
MS SQL server does a lot of automatic optimization which cause MS developers to become sloppy and not practice good design behavior. I ported a M$ database to postgres and the guy who originally created it never even bothered to use primary keys. It worked good on M$ because it would automaticly create the PK for the tables.
So, your agrument is that Microsoft (or any other DB provided) should NOT automatically optimize their database for best performance?
Thanks for the info, especially the part about v8.0 shipping w/ full native support. that’s been the only thing stopping me from installing it thusfar. also, in the .pdf it simply mentions that they _support_ up to 27tb, which is great, but i would really like to see some numbers about throughput/performance. also, the 27tb it mentions as a warehouse, which i am assuming is mainly OLAP. i also agree w/ you about postgresql being the preemptive OSS frontrunner (other OSS’s are just simply lame and waaaaaay behind). i’ve had some spare time today to look over the docs on the postgresql site, but there was no mention of indexed views; is this possible in postgre? of course, MSSQL didn’t have this capability until 2k, so it wouldn’t surprise me if it’s not possible. i have yet to see an actual postgre implementation in any of the (large) corporate IT dept’s i’ve worked for; it’s always oracle, mssql, db2, or sybase, the main reason being that there are formal support channels (i know i know, the OSS community provides excellent support, but that’s usually not enough for corp IT). cheers.
Keep in mind this is SQL Server 2005 not the past versions. And it’s still in beta, keep that in mind. As open Oracle supporter. I have to say SQL Server 2005 looks to be a really nice database. The only downside I see is it only runs on Windows machines. Other than that it looks very very nice.
I love PostgreSQL, however all of these OSS databases and OS’s in IMHO lack the polish and completeness that I require. Anyone who has ever written an enterprise level stored procedure can’t say PostgreSQL will do the trick. It is missing some key things. For starters it has little to no exception handeling in PG/PLSQL. That face alone will not allow me to created the needed systems my company needs. We can’t have a system that you have to do work arounds to do things that other databases like Oracle,DB2 and MS SQL Server give you out of the box.
This is not to bash PostgreSQL or any other OSS database. It’s fact, as time goes on it will improve. Will it catch the commerical versions? That I’m not sure of. Seems OSS will always be playing catch up.
This is not to bash PostgreSQL or any other OSS database. It’s fact, as time goes on it will improve. Will it catch the commerical versions? That I’m not sure of. Seems OSS will always be playing catch up.
Yes, understandable. Otherwise commercial companies will not have any competitive advantage over OSS. 🙂
OSS might always play catch up, but over time they will be *good enough* for more and more people.
Yes, unfortunately indexed views and materialized views are not supported yet by Postgres. There are plans though, and since there seems to be quite an interest in it, chances are that they *will* be implemented in the future. Currently the things being worked on are PITR (so you can take incremental backups and recover to _any_ point between your last full backup and last incremental backup), nested transactions/savepoints, tablespaces, replication, and Windows port. After that, it’s probably 2-phase commit. After that, who knows… partitioned tables, materialized views, etc.
Right now, Postgres already has features that I couldn’t find on commercial databases, so I’m sticking with it. Indexing “BLOB”, very convenient version of BLOB & CLOB (BYTEA & TEXT), many more data types that make my life easier (IPv4/IPv6, geometry), etc.
Postgres is a great database no question about it. Anyone who doesn’t give it a chance is missing out on something great. It may not meet the needs for everything. But it will meet the need for a lot of stuff. And of course you get a great DB that is free!
Just think how much money you could save your clients by using Postgres instead of M$
In our development work, we tend to find that customers already have databases and want us to use their own system – be it MSSQL, Oracle or whatever. So the cost (and platform) tends to be a non-issue.
In the rare cases where we need to supply a powerful database system, we’ll supply MSDE (mostly compatible with MSSQL and good enough for low number of user systems), which is free given our developer license. Companies requiring a large enough system that MSDE can’t be used usually already have a database licence or are happy to spend money on big-name databases, in my experience.
As an aside, we’ve always found SQL Server a bit easier to get along with than Oracle. SQL Server also seems to handle queries faster than Oracle on data without good indices – not an issue on a finished system, but useful during development.
I have to agree Jordax. That is what I am seeing as well. The cost to switch is to much effort and in many cases pointless. Why switch if you have 50 Oracle DBA’s and 500 developers that know Oracle really well.? Now if you are a sole developer running your own garage company I can see where OSS databases can fit in.
As a resident of USA I have no problem spending money on USA products to help my USA company make a profit. It’s good for the economy. And if it’s a country that I like other than good old USA I have no problem spending my money there either. “Yes I know there are money streams from OSS, but these are in different ways and not in the same amounts”
Most companies with a bottom line of 100 million+ or 1 billion+ for that matter are not going to use postgre,mysql etc in place of Oracle, DB2 or SQL Server for the big time stuff. Sure you might use it to store your timesheets or something, but why when you already have the struture to utilize what you already have. The commerical DB’s have the bonus of being to the enterprise market before the open source db’s got in the picture of enterprise computing for the masses.
For one those commerical products have been proven in real world systems for years and years. Postgresql is still going to have to prove it’s self. This will take time and no matter how well it works, companies will not adopt it until it’s proven.
OSS DB have nich markets that’s for sure but that goes without saying for just about anything.