Choosing the Right Microsoft Database

T-SQL vs PL/SQL

The dialect of SQL supported by Microsoft SQL Server 2000 is called Transact-SQL (T-SQL). The dialect of SQL supported by Oracle 9i Database is called PL/SQL. PL/SQL is more powerful language than T-SQL. This is the brief comparison of PL/SQL and T-SQL:
======================
Indexes :
pl sql supports
B-Tree indexes,
Bitmap indexes,
Partitioned indexes,
Function-based indexes,
Domain indexes

t-sql supports only
B-Tree indexes
======================
Tables
pl sql supports
Relational tables,
Object tables,
Temporary tables,
Partitioned tables,
External tables,
Index organized tables

t- sql

Relational tables,
Temporary tables

NOTE : sql server does not support partioned tables...

================
Triggers

pl sql :
BEFORE triggers,
AFTER triggers,
INSTEAD OF triggers,
Database Event triggers

t-sql
AFTER triggers,
INSTEAD OF triggers

=================
Procedures

pl sql :

PL/SQL statements,
Java methods,
third-generation language
(3GL) routines

t-sql
T-SQL statements


================
Arrays :

pl sql :

supported

t-sql
not supported
====================
=====================
===================
=======================

A link would have sufficed. Tables get garbled here.

Wise guy mendhak
:wave:

I still believe that it all depends on how well the server is configured as I said I have seen SQL Server running huge and when I say huge mean a size of database which you guyz are talking that oracle should be better off and it runs fantastic.

Nothing more to say.

At the end I would like to say all that this topic started about choosing a database for ur needs and that's for newbees only. For someone who is already working in a company, the company might be using a kinda of database system so they wont need to decide.

Cheers.

Winter Corp.’s TopTen Program, which identifies the largest and most heavily used databases in the world, is complete.

http://www.wintercorp.com/VLDB/2003_...Tenwinners.asp

Just for your curiosity sake...

I also wanted to note that I think alot of fokes forget that MySQL AB develops an Enterprise level database, along with MySQL. Yes there is MySQL DBMS but there is also MaxDB which does support things like:

Views
Server-side cursors
Stored procedures and triggers
Automatic failover (to a standby server)
Scheduling and automatic messaging on alerts
Snapshots
Archive tables
Synonyms

http://www.mysql.com/products/maxdb/index.html

MySQL and MaxDB doesn't come closer to SQL Server and Oracle at all.

Quote:

---

Originally posted by ryan007
Winter Corp.’s TopTen Program, which identifies the largest and most heavily used databases in the world, is complete.

http://www.wintercorp.com/VLDB/2003_...Tenwinners.asp

Just for your curiosity sake...

---

Nice information there ryan. I think at the end it all depends on the type of database u want.

Those figures are cool and indicates that SQL Server is better in one while oracle is better in another or in some case they both are out numbered by something else.

But agaig they all depends.

Cheers.

Quote:

---

MySQL and MaxDB doesn't come closer to SQL Server and Oracle at all.

---

Look at the benchmarks.

As a mater of fact:

eWeek
http://www.eweek.com/slideshow/0,301...a=23120,00.asp
http://www.eweek.com/article2/0,3959,293,00.asp

There an official TCP-C benchmark but I can't find it.

How does all that help anyone???
Here is something from someone who knows Noting about it.
Chevy, Ford, Dodge........ get it?????????
:confused:

Quote:

---

Originally posted by nkad
Look at the benchmarks.

---

I have.

Quote:

---

Originally posted by Rally2000
How does all that help anyone???
Here is something from someone who knows Noting about it.
Chevy, Ford, Dodge........ get it?????????
:confused:

---

It depends how you look at it. :D

Has anyone here used MSDE ? I have been using SQL server for a long time, but never came across MSDE. Is it really a cut down version of SQL server?

Since the thread is about "Choosing MS Database" thought i will add that to the discussion :)

Not that much. I guess I have used it for 2 days but since I already have SQL Server I use that and yes it's a cut down version of SQL Server.

I wonder how SQL Server 2003 or .NET or Longhorn whatever it is will look like....?

Quote:

---

Originally posted by wrack
Not that much. I guess I have used it for 2 days but since I already have SQL Server I use that and yes it's a cut down version of SQL Server.

---

Do you have any ideas of what are the limitations? I am told that its free and no license is needed to be purchased. If thats the case i can use it as a replacement for Access, will never need to tuch Access again :)

Quote:

---

Originally posted by Danial
Do you have any ideas of what are the limitations? I am told that its free and no license is needed to be purchased. If thats the case i can use it as a replacement for Access, will never need to tuch Access again :)

---

Did u read the original document...??? There is a link in the very first post. It describes it all. Here

and yes it is free but it doesn't come with any user interface so you need to live with it.

Who the heck is comparing SQL SERVER 2000 to be almost as expensive as ORACLE 9i?

Oracle is a superior database, especially for super-huge amounts of data. But,we use SQL SERVER for our web applications. The reason we use it:

IT COSTS FAR LESS! (1 cpu Oracle9i Enterprise with OLAP datamining costs $80,000 , compared to $20,000 for SQLServer. For 32cpus, $2.5 million for oracle, $0.6 million for SQLServer)

And hellishly easier to install for a client.


SQL server handles most small-mid size companies data for far less than Oracle.

yups got that right. SQL Server is not the most costly DBMS around.

As far as the size goes. There are bigger database than Oracle too.

http://www.wintercorp.com/VLDB/2003_...Tenwinners.asp

Well honestly...:rolleyes:

Who gives a flying rats arse which is most expensive.
Non of us here can afford that :)

Saying Oracle is the best, or SQL Server for that matter, is just plain silly.
Neither Oracle or SQL Server are the best database for my app, although it is currently pointing at a SQL Server 2000 DB.
The whole “Which DB is the best?” question completely revolves around the architecture and design of your application. If I wanted a DB with 2 tables with a max 20 rows then I can assure you that Oracle will be at the bottom of list, as will SQL Server.
I am writing a client-server application, well it’s already written actually, and it currently points at my customers SQL Server. They already had SQL Server so this made it easy fort me.
It has come to the stage now where I want to be able to sell my package commercially and not just restricted to one customer. I do not want each customer to have to purchase SQL Server separately, this makes for bad business practice on my behalf. My app has a target user base of 50 to 100, and only say 20, on average, users will be logged in at any one time.
I was thinking Access, but before anyone jumps down my throat I’d just like to mention that I only thought about it for a total of 13.5ms. Due to Wracks link this morning MSDE has been brought to my attention. This seems like the perfect solution. I can package it with my install and wayyyhayyyyy everything works…in theory anyways.
Has anyone here ever:

• Used MSDE?
• Installed MSDE?
• Packaged and deployed MSDE with an apps install package?
• Eaten raw cat?

These are serious questions, as I would like to package MSDE with my app. But I am not sure where to start :(
These are questions about MSDE specifically. Notice how non of the questions asked “Is MSDE better than oracle?” :D haha

Woka

Wokawidget,

MSDE was were my company's first crack at a real database server. Our CTO was way to concerned that we could distribute free with our application that he didn't look at the limitations. Good thing the development process could easily switch to sql server... :)

Here are the main limitations of MSDE:
Max 2 GB database size
Contains a workload Governor (kicks in after 8 concurrent sessions)
Workload Governor - http://msdn.microsoft.com/library/de...r_sa2_0ciq.asp

Once it has been activated, the workload governor limits performance by stalling a user connection for a few milliseconds each time the connection requests a logical read or write on any of the pages in the data files of a database.


[HTTP]
http://www.microsoft.com/sql/evaluat...ew/default.asp
[/HTTP]

Right now, I am researching alternative database engines. Basically what I am looking for is a DB Engine that is cross platform and is low cost (there is no such thing as free beer).

These are the 2 that I am looking at; 1) MySQL and 2) Firebird. I am leaning more towards my sql through. It's $495 US for a commercial licence, but that's pretty cheap compared to others.

www.mysql.com
www.firebirdsql.org

Ryan

A few milli seconds...Hmmmmm...is that 2-5, 5-20, 20-300ms???
If it's 2-5 or 5-20 then am happy with this. Even if it's 100ms then my app should still be speedy speedy.

Still trying to find info on how to deploy it will my app :(

As of yet I do not have the financial backing to purchase a DB :( SQL Server is NOT an option since I can't distribute this. It needs to be a DB that I can't deploy with my apps setup program.

Woka

Woka,
I have similar dilema and very simila situation as you. I have written a application targeted for small businesses, and I cant afford to buy MS SQL Server. So like you I did some research, and MSDE looked like a very good option, since I use stored procedure extensively and prefer doing most of the DB stuff in back end. I would definately go for MySQL if it supported stored proc. I found out that it will be in the next release which is currently released as beta.

But currently for limited number of users, MSDE looks the best option for people who already have experience with MS SQL Server.

Ryan i was under the impression that mySQL was free.


Woka check this link it might be what you are looking for.

http://www.microsoft.com/sql/msde/te...ntegration.asp

Woof Woof back :)

Quote:

---

from Woka
Used MSDE?
Installed MSDE?
Packaged and deployed MSDE with an apps install package?
Eaten raw cat?

---

I just implemented a small (very small - 6 tables) MSDE test database for a website I am creating. I developed the db using SQL Server 2000 Developer Edition. Generated the SQL scripts to create database, tables sprocs logins etc.. I used OSQL to run the scripts on my web server.

MSDE2000 Release A for installation. Its in the SQL Server downloads section on the MS site. If you don't already, make sure you understand the concept of "SQL Server Instances".

Not yet. But from what I read there is an MSI installer that you can call from your applications MSI package (can run in "silent mode").

mmmmm, cat.

MySQL is $500 for a commercial thingy :(

I have downloaded the install exe for the latest MSDE.
But can't find any decent documentation on what params I should launch it with as it suggested. There are many flags I cans use, but I am not sure which is the best for my situation :(
Right OK, just checked those links. Cheers Danial. Will go over them with a toothcomb tmrw.
Still cam't find any info on the time delay that happens once the connections reach 8...Hmmm.

Fried cat.

SQL Server instances?
Oh dear :( What are they?

Woka

When I was trying to figure out how to install MSDE I stumbled across this document. Database was up within half an hour after reading it.

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A

Instances are basically duplicate copies of sql server on one pc. Each instance has its own master, msdb, tempdb databases. Kind of like MS Access mdb files.

From VB you access the database using the ServerName\Instance name instead of just the usual ServerNam.

db.Open "provider=sqloledb;data source=MyServer\MyInstanceName; initial catalog=MyDatabasename;integrated security=sspi"

Quote:

---

Originally posted by Wokawidget
MySQL is $500 for a commercial thingy :(

I have downloaded the install exe for the latest MSDE.
But can't find any decent documentation on what params I should launch it with as it suggested. There are many flags I cans use, but I am not sure which is the best for my situation :(
Right OK, just checked those links. Cheers Danial. Will go over them with a toothcomb tmrw.
Still cam't find any info on the time delay that happens once the connections reach 8...Hmmm.

Fried cat.

---

Woka, what i suggest to you is that Download the SQL Server 2000 Evaluation Version from MS Web site. Once you install that even after you un intstall it the Enterprise Manager (the tool you use to perform task on SQL Server) is not un-installed. So that means you can use that Enterprise manager to Manage MSDE which does not come with Enterprise manager.

Or just spend the $50 bucks to get the SQL Server Developer Edition http://www.microsoft.com/sql/howtobuy/development.asp).

I have heard/read conflicting stories regarding the 8 connection govenor on MSDE. Some say 5 same say 25. MS says 25 here(http://www.microsoft.com/sql/msde/do...s/download.asp)

Quote:

---

Originally posted by brucevde
Or just spend the $50 bucks to get the SQL Server Developer Edition http://www.microsoft.com/sql/howtobuy/development.asp).

I have heard/read conflicting stories regarding the 8 connection govenor on MSDE. Some say 5 same say 25. MS says 25 here(http://www.microsoft.com/sql/msde/do...s/download.asp)

---

I read it in the MS web site that MSDE is can be used up to 25 users/connections, but it is optimised for a maximum of 8 concurrent users, anything over that you will see performence loss.

8 connections does not mean 8 users. With a well written application and database, you could easily handle many many users. I won't give an actual figure without concrete proof.

Basically

Connect
Execute Stored Procedure
Disconnect

What does that take < 1 second. If it takes an extra .3 secs big deal who would notice.

Of course a lot would depend on the nature of the application.

Installing SQL Server on a pc is not an option.
You have to remember that teh DB that gets shipped with my app but install itself.
I can't expect all of my customers to fork out cash for SQL Server or any other DB for that matter. My app should be self contained.
Cheers for the link. Gonan loook at it now.

Woof

I have SQL Server 2000 on my PC, so I get the service manager icon in my systray.
When you install MSDE does this place a service manager icon in the systray?
How do you know if this MSDE instance is running?

Woika

Quote:

---

When you install MSDE does this place a service manager icon in the systray?

---

Yes. You may need a reboot to get service manager to appear but msde will still be running.

Quote:

---

How do you know if this MSDE instance is running?

---

Each MSDE instance runs as a service, so a manual check would be to look for MSSQL$YourInstanceName in Windows Services.

Doesn't anyone else like DB2?

Bill

Actually your IT manager is not 100% wrong about Oracle. Oracle is strong and complete enough to stand at its own, because of her great tools to enable friendly query to the backend database.

If comparison is done between Oracle (e.g v8i) and MS SQL Server (e.g v2000), I think most people will choose MS SQL Server. The reasons are:

• DBA for Oracle can be triple of the cost to hire a DBA for SQL Server
• Functionility of MS SQL Server is not less than the Oracle and it s enough to complete any of your requirements
• License for MS SQLServer is cheap enough to be owned by small company
• Most of the tools in Oracle is not useful to most of the company if it is just used as backend database

If your requirement of database that can handle until 1.5-2GB data and small amount of concurent users, I will recommend to use MS Access 2000. It is simple enough to start with without a lot of installation because most of the MS Office Installer will bundled with it.

Hope this can help.

Quote:

---

Originally Posted by vbAries

Actually your IT manager is not 100% wrong about Oracle. Oracle is strong and complete enough to stand at its own, because of her great tools to enable friendly query to the backend database.

If comparison is done between Oracle (e.g v8i) and MS SQL Server (e.g v2000), I think most people will choose MS SQL Server. The reasons are:

• DBA for Oracle can be triple of the cost to hire a DBA for SQL Server
• Functionility of MS SQL Server is not less than the Oracle and it s enough to complete any of your requirements
• License for MS SQLServer is cheap enough to be owned by small company
• Most of the tools in Oracle is not useful to most of the company if it is just used as backend database

If your requirement of database that can handle until 1.5-2GB data and small amount of concurent users, I will recommend to use MS Access 2000. It is simple enough to start with without a lot of installation because most of the MS Office Installer will bundled with it.

Hope this can help.

---

You are not going to get 1.5 to 2GB of data in an Access database and have a database you can do anything with. Access wasn't made to handle that kind of storage.

Personally, now I would use SQL Server 2005 Express. The DB engine is free to distribute with your apps.

Hack is right...1 gig in an access DB. Ouch.

Woka