RDBMSes that you pay for are still able to out perform free, community-developed systems [2]. I've done work on both the DBA and the developer side on Postgres, MySQL, and SQL Server, and I can tell you that if platform and cost were never an issue, I'd choose the latter every time.
[1] http://www.indeed.com/jobs?q=Java+SQL+Server [2] https://www.periscope.io/blog/count-distinct-in-mysql-postgr...
If I could, I would choose PostgreSQL every time. But my clients do not have a person who is willing to learn to use Linux and PostgreSQL. Even fancy things like ultra fast backups with ZFS snapshots and PostgreSQL doesn't sell.
Anyway, working with SQL Server is fine, it's solid DB. But Microsoft should really invest time in a proper CLI tool that work on Linux.
The tools SQL Server provides are just GUI'fied versions of cmd line tools available on the other DB's mentioned here... so just saying "it's better because you can click on things" is not really a valid argument.
Not even mentioned above, but IBM's DB2 has a lot of GUI'fied admin tools as well as very robust terminal tools and a huge toolset available in the jtopen library. It's a good choice as well, although not as common in stand-alone installations (you'll encounter it more often bundled with things like as the backing db for AS/400 systems, etc)
select * from Product select * from Category
Then in C# you can pull out 2 different result sets and get two different collections. With 1 call to the database. So no round trips.
PostgreSQL supports multiple result sets if they contain the same columns. But the drivers don't support them. :(
Writing a single projected SELECT with a deep list of JOINs causes the single result set wire size to explode as you include more and more to-many relations. However, breaking up the joins into separate SELECT roundtrips to reduce wire size will increase latency.
With multiple result sets, you can take all the separate selects, stuff them up in a stored procedure, connect them with insert joins through temp tables, then return multiple result sets from the temp table contents. This gives the best of both worlds: low latency from one roundtrip and a small wire footprint.
Even with this example - I need a list of all products and I need a list of all categories... Joining them means I have to write client code to split them up again for display.
In any case, I use this tactic a lot for CRUD apps and it makes everything quicker when I can call one stored procedure and get back 7 lists of stuff that I need to display on my page.
Well, I''m not going to say never, but I will say very rarely. The entire point of a RDBMS is to have related data.
> I need a list of all products and I need a list of all categories
Sure maybe, but in our ecommerce platform products have a field which is an id of a category and is linked via FOREIGN KEY. Each category has another id which is it's parent category... so you traverse upwards until you build the entire category path.
I can see wanting to eliminate a round-trip, but one could also just do two separate queries and then cache the results...
I can see why this feature might be a nice-to-have, but I don't think that single case is enough to justify using that DB exclusively (if it were that much af a demanded feature, I'd wager other DB's would have implemented it by now, especially heavy-weights like Oracle).
For instance - https://www.google.com/search?q=oracle+return+multiple+resul...
Also, have you considered that you don't really know how useful this technique can be since this features doesn't exist in the databases that you use? In any case, availability of features ultimately dictates style. (And I bet money that if you looked in your code, you'd find a lot of places with multiple trips to the database.)
When you look at the search results, you'll find that there are some kludgy ways for people to work around this limitation in Oracle, PostgreSQL and MySQL. (Actually MySQL might have this feature now.) So I'm sure plenty of people are settling for the kludge and moving on instead of complaining.
For instance - SSDT is basically an IDE for creating a SQL Server Project. You use it to create your tables, procedures, functions, etc. Every object's DDL is stored in it's own file. You store that in your source repo. SSDT will diff one server-database with another server-database and generate an update script. It will diff the project's DDL with a server-database and generate an update script (or update your project's DDL from the server). It also performs data diffs so that you can make two different server-databases contain the same data (or generate an UPDATE/INSERT script).
SSMS is not an IDE, it just lets you run ad-hoc queries and provides a GUI to manage most configuration element of SQL Server. It's got a GUI to create users, roles, permissions, etc. You can start the SQL Profiler from SSMS. It's got syntax highlighting and intellisense/autocomplete for database objects.
Other databases have some of these tools, but they don't come in one unified package - you pretty much have to cobble together your own kit for other databases.
Doubtful SQL Server on the 'nix stack has much demand nor will it gain traction. In all OS' not Windows... Oracle, PostgreSQL and MySQL/MariaDB dominate. Not because SQL Server isn't available, but because they are all very solid very good SQL servers.