RE: [PHP] Re: How important is your Express or Web Edition database? Please weigh in--

On Mon, 2009-03-02 at 09:30 -0600, Boyd, Todd M. wrote: > > -----Original Message----- > > From: Andrew Ballard [mailto:aballard@gmail.com] > > Sent: Saturday, February 28, 2009 12:05 AM > > To: ash@ashleysheridan.co.uk > > Cc: Boyd, Todd M.; PHP General list > > Subject: Re: [PHP] Re: How important is your Express or Web Edition > > database? Please weigh in-- > > > > On Fri, Feb 27, 2009 at 7:32 PM, Ashley Sheridan > > <ash@ashleysheridan.co.uk> wrote: > > > On Fri, 2009-02-27 at 16:41 -0600, Boyd, Todd M. wrote: > > >> > -----Original Message----- > > >> > From: Andrew Ballard [mailto:aballard@gmail.com] > > >> > Sent: Friday, February 27, 2009 3:26 PM > > >> > To: Bastien Koert > > >> > Cc: Shawn McKenzie; php-general@lists.php.net > > >> > Subject: Re: [PHP] Re: How important is your Express or Web > > Edition > > >> > database? Please weigh in-- > > >> I use SQLExpress (SQL Server Express) all the time at work for > > prototyping and such... although, I have to say--if my company hadn't > > installed it on my machine to begin with, and they weren't running SQL > > Server 2005 on the production servers, I would rather just use a > > private MySQL installation for prototyping and then push to a MySQL > > production server. Alas... > > >> > > >> > > >> // Todd > > > For me it's MySQL all the way. My company is too cheap to pay for > > later > > > versions of MS SQL Server, so the versions we have there are *very* > > > limited in features (for example, no limit function!) MySQL also > > seems a > > > lot faster for me too. I regularly deal with large databases (think > > > millions of records) and MSSQL is a real bottleneck here, whereas > > MySQL > > > seems fine (althogh, it is running on Linux, which frees up more > > > resources for actually getting stuff done!) > > > > > > Oh, funny thing. I filled in the questionnaire above, and when it got > > to > > > the final 'thanks' page, I clicked the button, and it bombed out to a > > > completely blank page. Doesn't bode too well for a company attempting > > to > > > sell a product for use in enterprise situations! > > > > > > > > > Ash > > > www.ashleysheridan.co.uk > > > > > > > It all depends on what you need. I know from your previous posts that > > you're not very well disposed to SQL Server, but I've used it quite a > > bit now for the last 8 years and haven't really had any problems with > > performance. I'll grant that it doesn't have the LIMIT clause (Is it > > part of the actual ANSI SQL spec, or is it something handy that MySQL > > added to their product?) The newer versions offer a row number > > function that can be used to provide the the same functionality, but > > I'll admit it is not nearly as simple as being able to say LIMIT 25, > > 50. > > > > While I like MySQL, it has its oddities as well. I've run into > > situations where I had to add ORDER BY clauses to UPDATE statements > > (I'm not sure that's really valid SQL either) because it updated the > > rows sequentially and validated a unique index after each row rather > > than after all the rows were processed. I wish it would support CHECK > > constraints. And as convenient as I've found the SET and ENUM > > datatypes in simple databases, I'm coming to the notion that they are > > not a good idea in most situations. And while the availability of > > different engines has benefits, it can also cause issues. > > Wait, wait, wait... I know SQL Server doesn't have "LIMIT", but haven't you > guys ever used "TOP"? As in... > > select top 10 * from some_table where some_column = 'some_value'; > > ?? I'm not sure about getting lower bounds (maybe there is a BOTTOM, but I'm too lazy > right now)... but if you're just trying to limit the number of rows in your result with a cap, > then TOP does the trick just fine. > > I've had to do a lot of searching to find ways to do stuff in SQL Server that were already > natural for me in MySQL (as I learned on MySQL and develop independently with it), but I have yet to > be completely taken aback by something that's missing in SQL Server. (I am a little miffed that > you have to do a sub-query on information_schema in order to test for object existence, though.) > > Anyway, I don't see what all the anti-MSSQL sentiment is all about. I use it all the time > (SQL Express, SQL Server 2000 and 2005 Professional) and I don't find myself wanting for > something I could have done in MySQL but cannot do in MSSQL. > > SSIS packages are pretty sweet to work with, BTW, if you've ever needed to build DTS > solutions. :D > > My 2c, > > > // Todd There isn't a BOTTOM, the solution looks something like this: SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM table WHERE clause ORDER BY col) AS temp ORDER BY col DESC) as temp2 ORDER BY col It's an awful mess, but was the only way I found to select results x to y in a reliable manner. Ash www.ashleysheridan.co.uk