MS SQL Server vs MySQL question..

I use MS profiler to run traces to diagnose issues/problems with any MS SQL Server database. Is there an equivalent tool when using MySQL database? What about Enterprise Manger and Query Analyzer? Is it fair to assume that the administrative tools available for MySQL will do the job that MS tools do for MS SQL Server with comparable ease?

Thanks.I don't know of any Profiler-like applications for MySQL but I use SQLyog to administer my MySQL databases (http://www.webyog.com).

In my opinion comparing MySQL to MS SQL Server is comparing a bycicle with a Mercedes. They both get you from A to B but that's the only fair comparison. MySQL is a nice database for small non-critical applications but it can't do halve the things that SQL Server can. So tools for MySQL won't do the same as the ones for SQL Server.

Lex

PS. Again, this is my own opinion on the matter. So feel free to contradict :D|||small non-critical applications? whoa

have a look at some of the mysql customers (http://www.mysql.com/customers/), my friend, and you will see some fairly big, critical names

craigslist, slashdot, technorati are small, non-critical?

ticketmaster is small, non-critical?

you've been buying the "open source can't be any good" comic books, haven't you

:)|||In terms of industry support, developer and dba tools, and more importantly vendor support, I think Microsoft SQL Server is the best bang for the buck available. The features available (many of them "out of the box" such as SQL Profiler, Enterprise Manager, Analysis Services, and Reporting Services) are more readily available and well understood than comparable tools for any product currently available, at any price.

This doesn't mean that MySQL is a bad tool, and it emphatically does not mean that it isn't suitable for doing serious work. It does however mean that you need to find support from someone other than the vendor (MySQL AB), which gives you a very low cost of entry, but often a HUGE ongoing cost for the people you need to support the product. For large organizations that need people with "heavy firepower" to support their applications like the ones that R937 pointed out, the cost is irrelevant. They need to have folks that can handle the support on staff 24 by 365 anyway, so they have no additional cost of ownership.

For most of us, the cost of the people needed to support the tools would exceed what the company makes. Many folks choose to live without the support, and hope that they never need it. That has proven to be a fatal assumption for a number of small (under $10 million per year gross revenue) businesses every year.

Microsoft SQL Server isn't a "magic bullet". It won't solve every problem. I don't always recommend it, but for more than 80% of the business use that I see, MS-SQL is the best database tool for the job in my opinion.

-PatP|||Alright, alright, I take back some of the words I said about MySQL. It was after one o'clock at night when I wrote that post and I was feeling rebellious. I overstated the comparison and my humble opinion actually comes close to that of Pat's.

I'm still not fully over the first impression I got when, years back, I learned of MySQL and did some research. I looked up the support for referential integrety and was horrified to read that, not only was that support at the time non-existing but it was stated that it was cumbersome and troublesome and you wouldn't need it.

I tossed it aside and didn't look at it for a few years. When I re-researched it a few years later the support was added and I find the product most agreeable. But aside from the features it lacks in comparison to SQL Server (and Oracle and DB2 and Sybase and ...) I think the most crucial thing is the absence of a big vendor who is and feels responsible for the product. And that's what you need when your core business depends on it!

For the record: I think I'm not biased when it comes to open source or even databases. Oracle is as good a database as Sybase or SQL Server. They all have their weaknesses and strong points (When's MS gonna give me function based idexes and a sequence generator!? ;)). MySQL has it's strongpoints and I will keep using it for the projects I think it is most suitable for.

Lex

PS. is that comic on the same shelve as the "Big commercial companies are all money grabbin' crooks thus open source is the solution for everything" comics? :D

MS SQL Server equivalent query

I have this query I use on MySql, and I'm trying to translate that to make it work on MS SQL Server.

INSERT INTO totable (col1,col2,col3)

(SELECT col1,col2,col3 FROM fromtable AS x)

ON DUPLICATE KEY UPDATE col1=x.col1,col2=x.col2,col3=x.col3;

Basically it inserts all rows from one table into another, and if you get a unique key constraint, it updates that rows instead. So far I haven't found any equivalent for MS SQL Server Anyone have a suggestion?

I'm afraid you will have to wait for SQL Server 2008 with the new MERGE statement :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

WesleyB wrote:

I'm afraid you will have to wait for SQL Server 2008 with the new MERGE statement :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Well...I don't have that amount of time Anyway...to elaborate a little bit...I have a working solution, but I'm trying to make it better. Currently this is done from a c++ program in a cursor loop. I send a select, insert and update statement to a function, selects a dataset and for each row in that dataset I'll first try to update, and if the update fails (returns no affected rows), I execute the insert statement. Needless to say that it isn't very efficient, but I can be flexible in terms of prepare temporary tables, send in help queries etc. I just can't figure out a better way than the current scenario...I just think that it really have to be a better way to do this.

|||

Rather than use a CURSOR, I would load the data in question into a staging table, using a table variable or #temp table, then with a single update statement, update all pertinent rows in the production table, a second query to delete those rows from the staging table, and then a third query to add the remainder to the production table.

Depending upon the number of rows, it is likely to be quite a bit more efficient and 'faster'.

|||

Why not doing first the update then the insert ?

Update SomeTable
SET
col1=x.col1
col2=x.col2,
col3=x.col3
From SomeTable
INNER JOIN fromtable x
ON --Place your join conditions here

INSERT INTO totable (col1,col2,col3)

(
SELECT col1,col2,col3
FROM fromtable AS x
WHERE NOT EXISTS
(
Select * from totable T
Where t.SomeColumn = x.SomeColumn --These should be your join conditions
)
)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

ms sql equivalent of this oracle

Hi.
I'm a casual sql user. I have found a situation where I need to convert an
oracle statement to tsql, one I can just fire off in any sql tool against an
ms sql server database.

I studied the exists statement and I think I understand it somewhat, however I
was not sure how to get it quite right. If you have an idea and a minute or
two I'd appreciate any insight or tutorial.

insert into authorization (program, optiontitle, usergroup, authorizationid)
select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval
from allgroups where exists (select * from authorization
where authorization.USERGROUP = allgroups.USERGROUP and
authorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')

I believe that because in my data, three values of usergroup from allgroups
return true from the exists, that this is supposed to insert three rows into
authorization.

But I can't figure out what to do about the authorization.nextval.. I tried
various max(authorization)+1
etc but nothing seemed to compile/work

thanks
Jeff KishHi

You can make the column an identity, this will not guarantee contiguous
number but it will be increasing/decreasing and unique. You can then miss it
out from the statement altogether.

These may help:
http://vyaskn.tripod.com/oracle_sql...equivalents.htm
http://www.microsoft.com/technet/pr...art2/c0761.mspx

John

"Jeff Kish" <jeff.kish@.mro.com> wrote in message
news:b5a7u1lod76n3at35csf2b6f8nvpj35jn8@.4ax.com...
> Hi.
> I'm a casual sql user. I have found a situation where I need to convert an
> oracle statement to tsql, one I can just fire off in any sql tool against
> an
> ms sql server database.
> I studied the exists statement and I think I understand it somewhat,
> however I
> was not sure how to get it quite right. If you have an idea and a minute
> or
> two I'd appreciate any insight or tutorial.
> insert into authorization (program, optiontitle, usergroup,
> authorizationid)
> select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval
> from allgroups where exists (select * from authorization
> where authorization.USERGROUP = allgroups.USERGROUP and
> authorization.optiontitle = 'READ' and authorization.program =
> 'EVERYWHERE')
>
> I believe that because in my data, three values of usergroup from
> allgroups
> return true from the exists, that this is supposed to insert three rows
> into
> authorization.
> But I can't figure out what to do about the authorization.nextval.. I
> tried
> various max(authorization)+1
> etc but nothing seemed to compile/work
> thanks
> Jeff Kish|||Jeff,

authorizationseq seems to be a sequence. Sequences just don't exist in
MS SQL Server 2000/7/6.5. You might want to have an identity column.|||On Sat, 4 Feb 2006 16:17:55 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:

>Hi
>You can make the column an identity, this will not guarantee contiguous
>number but it will be increasing/decreasing and unique. You can then miss it
>out from the statement altogether.
>These may help:
>http://vyaskn.tripod.com/oracle_sql...equivalents.htm
>http://www.microsoft.com/technet/pr...art2/c0761.mspx
thanks. I'm still not sure of how to do something here, though.

This is directly related to the problem but re-worded because I need to
get the next value using max(authorizationid)+1 ...

Given two tables:
allgroups(usergroup, otherdata) =
{'group1',otherdata1,
'group2',otherdata2,
'group3',otherdata3,
:
:
'groupn',otherdatan}

and
authorization(program,optiontitle,
usergroup,authorizationid) =
{'pro1','title1','ug1',3,
'pro2','title2','ug2',4,
:
'pron','titlen','ugn',m}

How can I insert multiple
lines (one for each usergroup
in allgroups) using one sql statement
into authorization if this is correct for a
single insert:
insert into authorization(program,
optiontitle,usergroup,
authorizationid)
select 'proq','titleq','ug1',
max(authorizationid)+1
from authorization

bascially I'd like each usergroup
from allgroups to be used to create a
new line in authorization, having
the authorizationid increment one from
the current max.

Yes, I have no control over the design/use of
an identity column.

Is it possible?
Thanks
Jeff Kish|||Jeff Kish wrote:

> thanks. I'm still not sure of how to do something here, though.
> This is directly related to the problem but re-worded because I need to
> get the next value using max(authorizationid)+1 ...
> Given two tables:
> allgroups(usergroup, otherdata) =
> {'group1',otherdata1,
> 'group2',otherdata2,
> 'group3',otherdata3,
> :
> :
> 'groupn',otherdatan}
> and
> authorization(program,optiontitle,
> usergroup,authorizationid) =
> {'pro1','title1','ug1',3,
> 'pro2','title2','ug2',4,
> :
> 'pron','titlen','ugn',m}
> How can I insert multiple
> lines (one for each usergroup
> in allgroups) using one sql statement
> into authorization if this is correct for a
> single insert:
> insert into authorization(program,
> optiontitle,usergroup,
> authorizationid)
> select 'proq','titleq','ug1',
> max(authorizationid)+1
> from authorization
> bascially I'd like each usergroup
> from allgroups to be used to create a
> new line in authorization, having
> the authorizationid increment one from
> the current max.
> Yes, I have no control over the design/use of
> an identity column.
> Is it possible?
> Thanks
> Jeff Kish

Assuming SQL Server 2005 (you didn't specify otherwise), use the
ROW_NUMBER function. For example:

INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT MAX(authorizationid)
FROM [authorization])
FROM
(SELECT 'proq','titleq', usergroup, ROW_NUMBER()
OVER (ORDER BY usergroup)
FROM allgroups)
AS T(program, optiontitle, usergroup, authorizationid) ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||<snip>
>Assuming SQL Server 2005 (you didn't specify otherwise), use the
Sql Server 2000. Really sorry I did not say it up front. Do you know
of a way using that?
Regards,
Jeff

>ROW_NUMBER function. For example:
>INSERT INTO [authorization]
> (program, optiontitle, usergroup, authorizationid)
> SELECT program, optiontitle, usergroup,
> authorizationid+
> (SELECT MAX(authorizationid)
> FROM [authorization])
> FROM
> (SELECT 'proq','titleq', usergroup, ROW_NUMBER()
> OVER (ORDER BY usergroup)
> FROM allgroups)
> AS T(program, optiontitle, usergroup, authorizationid) ;
>--
>David Portas, SQL Server MVP
>Whenever possible please post enough code to reproduce your problem.
>Including CREATE TABLE and INSERT statements usually helps.
>State what version of SQL Server you are using and specify the content
>of any error messages.
>SQL Server Books Online:
>http://msdn2.microsoft.com/library/...US,SQL.90).aspx|||Jeff Kish wrote:
> <snip>
> >Assuming SQL Server 2005 (you didn't specify otherwise), use the
> Sql Server 2000. Really sorry I did not say it up front. Do you know
> of a way using that?
> Regards,
> Jeff

INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT COALESCE(MAX(authorizationid),0)
FROM [authorization])
FROM
(SELECT 'proq','titleq', A1.usergroup, COUNT(*)
FROM allgroups AS A1
JOIN allgroups AS A2
ON A1.usergroup >= A2.usergroup
GROUP BY A1.usergroup)
AS T(program, optiontitle, usergroup, authorizationid) ;

This assumes that usergroup is unique in Allgroups. If I'm wrong then
use the key of that table in the join. You need a key in order to
generate the sequence.

Read my signature. It may help you get faster answers in future.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--