4 ms·
Coming from mostly a Linux/MySQL background, I've had reason to use MSSQL for the last few years. It's really solid, and seems to optimize queries well, which
by kbenson 11y ago
Coming from mostly a Linux/MySQL background, I've had reason to use MSSQL for the last few years. It's really solid, and seems to optimize queries well, which is good, because there's a few things that I still find really annoying, such as no GROUP_CONCAT. There is a more general method to do the same thing, but wow is that method cumbersome in comparison (although undoubtedly more powerful).
- sixdimensional 11y agoAgreed on GROUP_CONCAT. MSSQL has been missing that for a long time and it is a very reasonable and handy function to have. It sounds like you are talking about the XML conversion method for concatenating groups. There is one other way around it using custom .NET CLR functions, but it is definitely cumbersome as well.
- kbenson 11y agoYes, I'm referring to the XML conversion method. I'm aware you can register your own .NET CLR functions, but truth be told, it's a database for our POS application (which we purchased from a vendor), so I try to mess with the database as little as possible beyond reading the data through our own internal application so we can do useful things with it. I try to stay away from messing with the schema or even providing extra routines (I can't be sure anything would survive upgrades, which are regular and I don't want to deal with that).
- wvenable 11y agoThis is also my big complaint when it comes to MS SQL -- there has been precious little development on their SQL language. It's almost like they're in a complete feature-freeze when it comes to the SQL language. MS SQL is missing some very basic SQL functionality.
- eterm 11y agoWhat in particular do you miss from other SQL languages?
- manigandham 11y agoUPSERT (or similar logic, not merge).
- jklowden 11y agoOh, then that's your problem. MERGE is standard, and has clear semantics. UPSERT is nonstandard, and semantics vary among implementations. Microsoft doesn't need to make SQL Server look like MySQL. Quite the other way around.
- techdragon 11y agoThere's not really a standard anymore for UPSERT to be added to. SQL has fragmented and since the last few iterations of the standard allowed for "additional non standard features", adding an UPSERT is perfectly compliant with the SQL standards.
- manigandham 11y agoUPSERT should be a standard, why isn't it? And it's very useful in a ton of situations which is why so many other SQL dialects have added it. It's not like SQL Server is completely standards compliant so I'd rather have the functionality then have to use MERGE because it's "standard".
- tyfon 11y agoDate handling, simple things like postgres date_trunc('month', now()) and especially looping over records in stored procedures without using cursors (that are prone to crashes). I work with both on a daily basis and there is a mile of difference. Yes I can accomplish whatever I want in sql server but there are so many small things that could improve. Some of other pet peevees are: You can't use a lot of left joins, it slows down the query dramatically. If there is only one to one relation, use subselects instead. Thou shall not do queries like this: select foo into baz from bar where fooid not in (select fooid from bar) They are the death of the engine. And many "unwritten rules" like that. Postgres just accepts the queries and run them..
- orionblastar 11y agoSQL Server was based on Sybase SQL Server. https://en.wikipedia.org/wiki/Microsoft_SQL_Server https://en.wikipedia.org/wiki/Microsoft_SQL_Server Microsoft rewrote the code so there was no more Sybase code, but it still followed Transact SQL standards. T-SQL is not as complete as PL/SQL and others. It is just that Microsoft is that 800 pound gorilla so people use their products. My last job I did migration from Excel and MS-Access to SQL Server using VB 6.0 code and ADO recordsets to copy and sync up data. They key was to use a column named date modified that had a time stamp when it was last modified to figure out which record was the newest and back up the old record to a history table in case it needed to be reverted later on.
- rat87 11y agoI thought Oracle or even db2 would be the Gorillas for serious corporate/goverment databases, and MySql would be the Gorilla for cheap + web stuff. I suppose Microsoft is pretty popular in beetween those 2 markets.
- pionar 11y agoOracle IS the gorilla, but with their recent pricing/licensing moves, more large orgs are exploring moving to SQL Server, especially ones that are already a Microsoft shop. My company is doing just that.
- ryao 11y agoWhy not explore PostgreSQL? You would be free of licensing/pricing moves.
- breakingcups 11y agoNot GP, but I've found SQL Server significantly easier to both install and manage. Performance-wise I've yet to encounter a significant difference.
- jaytaylor 11y agoCan you share any links which cover the more general and powerful approach in detail?
- zamalek 11y ago> optimize queries well There are a few obvious cases which don't work (e.g. scalar UDFs aren't inlined - always use table UDFs), but the performance battle Oracle and Microsoft had circa 2000 really shows. The vast majority of the time you can code your intent and the query optimizer will simply do the right thing. The main reason I would choose SQL over any of the competition is DataDude/VSSQL. We started using it where I work (~5100 schema objects) and it's revolutionary. T-SQL becomes a first-class language in Visual Studio: - Full editor integration: intellisense and errors as you type. - Build system: build (including static analysis) and deploy from Visual Studio. MSBuild-based integration for CI. - Schema delta: you write/update your schema as though you are writing it for the first time and, as part of the build, the ALTER script will be generated for you. You rarely have to write migration scripts yourself (I've only had to when migrating data across columns/tables). I have been thinking about solving this for PostgreSQL because I'm now completely unable to work without it - I just wish I had the time. Any other form of SQL development now feels like VBScript development in Notepad.
- wernercd 11y agoLinks to more information about this? I've found some information about VSDBPro but it all is dated 2008. I like the sounds of this, but it seems dated... Update: Seems like this has become SQL Server Data Tools (but not the BI tools of the same name?). I'll have to look into this more.
- zamalek 11y agoAs far as I remember it has only ever been optional in VS2010. It was introduced by default in 2013 (.dbproj) and then reworked in 2015 (.sqlproj). All you need to get this going is: Visual Studio > File > New > Project > Other Languages > SQL Server. You can also get it to reverse-engineer an existing database into a project[2]. Getting it to work for CI isn't turn-key. When you build a SQL/DB project it results in both a CREATE/ALTER script (depending on whether you do a diff build or a CREATE build) as well as a schema file (.dbschema). You need to track previous versions of the dbschema yourself (we use branching to track this file across releases), so that it can it has the base for diffing during the build. It's a good idea to add a database reference to [sys] and [master] in all your projects (which is not done by default). Knowing the right keywords I Googled a bit for you: [1]: https://visualstudiomagazine.com/articles/2015/01/01/visual-studio-database-project.aspx https://visualstudiomagazine.com/articles/2015/01/01/visual-... [2]: https://www.youtube.com/watch?v=kWKllVyozOg https://www.youtube.com/watch?v=kWKllVyozOg (talks about SQLAzure, but these tools do work on-premise) [3]: http://www.codeproject.com/Tips/998465/Creating-a-SQL-Server-Database-Project-Use-in-Visu http://www.codeproject.com/Tips/998465/Creating-a-SQL-Server... [4]: https://blogs.msdn.microsoft.com/ssdt/2014/07/24/sql-server-database-projects-and-team-foundation-build/ https://blogs.msdn.microsoft.com/ssdt/2014/07/24/sql-server-... (if you use TFS CI, otherwise you can invoke MSBUILD directly from whatever CI you use)