4 ms·
The problem with this theory is programming in some databases (MS SQL Server) is extremely unpleasant. For example, you can finally, in the year 2016, split st
by mistermann 10y ago
The problem with this theory is programming in some databases (MS SQL Server) is extremely unpleasant.
For example, you can finally, in the year 2016, split strings natively in TSQL:
https://www.brentozar.com/archive/2016/03/splitting-strings-sql-server-2016-rescue/ https://www.brentozar.com/archive/2016/03/splitting-strings-...
Compare that to this SO question:
http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x http://stackoverflow.com/questions/2647/how-do-i-split-a-str...
Note there are 32 answers on that question, many of which fail under various edge cases. Also notice that no one finds this situation absurd in the slightest (Stockholm Syndrome is alive and well in the MSSQL community apparently). This is just one example of the nonsense you have to put up with on SQL Server, there are many others.
And while PG is getting yet another new language capability, it appears Microsoft is pulling SQLCLR support (in Azure, so far):
https://www.brentozar.com/archive/2016/04/breaking-news-literally-sql-clr-support-removed-azure-sql-db/ https://www.brentozar.com/archive/2016/04/breaking-news-lite...
...which probably doesn't matter at the end of the day because almost no DBA would ever let you use it in production.
While it would cause harm to my personal career, nothing would make me happier than to see Postgres crush MSSQL, Microsoft has earned nothing less with their absolute disdain for their users.
- NDizzle 10y agoYou have been able to split strings in t-sql forever. The link you are referring to splits a string by a delimiter and returns the result as a table. I've also used CLRs in production since SQL Server 2005. Not sure which circles you run in, but it's quite different than what I've seen. I also use Service Broker, so maybe I'm just an odd duck?!
- mistermann 10y ago> You have been able to split strings in t-sql forever. The link you are referring to splits a string by a delimiter and returns the result as a table. Generally ones wants to either extract the Nth element from a delimited string, or convert a delimited string into a table, are you thinking of a different scenario? Somewhere on an official Microsoft page, there is something to the effect that SQLCLR is "not recommended", which gives your typical DBA a perfect excuse to say "no", with no interest in the particulars of why Microsoft might have written that warning.
- EvanAnderson 10y agoIn my experience the fact that you even know the words "Service Broker" makes you an odd duck. (My company uses the heck out of it, BTW... It boggles my mind that it's so unknown to most developers using the Microsoft SQL Server platform.)
- mistermann 10y agoWhat's an example use case for Service Broker?
- EvanAnderson 10y agoAsynchronous processing. Service Broker has a queue-based metaphor and it can execute stored procedures asynchronously when new messages arrive. I gnash my teeth every time I see problems "solved" by the "we'll just poll the database periodically for changes" pattern that could be better-served by Service Broker. Service Broker performs store-and-forward reliably message delivery inside a single SQL Server instance and between instances (on the same machine or over a network). The inter-instance delivery is particularly nice insofar as that it can use certificates for authentication (which is very nice when you have disparate Active Directory environments in play). We've built a couple of system that use it and have been very pleased with the results. I can't figure out why it's so obscure. The documentation isn't terrible but it's definitely got a steep learning curve.
- kbenson 10y ago> This is just one example of the nonsense you have to put up with on SQL Server, there are many others. The equivalent of GROUP_CONCAT in MySQL or STRING_AGG in Postgres in MSSQL is ridiculous. Yes, I understand it's more versatile and extensible to use a subselect which can be mostly optimized away, output as XML, prefix each field with your delimiter, and then ignore the first character of the concatenation of those fields, but come on[1]. Throw your users a bone once in a while... 1: http://stackoverflow.com/a/5981860/210749 http://stackoverflow.com/a/5981860/210749
- mistermann 10y agoFrom that post: "Or a version that works correctly if the data might contain characters such as <" What other edge cases are you missing? Do you care? Microsoft certainly doesn't. TSQL is an embarrassment, it is a perfect example of the arrogance of Microsoft that is luckily finally being corrected on many teams (C#, Azure) but persists in the MSSQL camp.
- anton_gogolev 10y ago> The problem with this theory is programming in some databases (MS SQL Server) is extremely unpleasant. Oh forget about string splitting and the like. The language itself (T-SQL and SQL in general) is a disaster. No modularization possibilities (wanna do a this particular "SELECT" but with slightly different something? well, copy-paste the whole thing), sensible code autocompletion is next to impossible (type "SELECT _" wat? from wat?), don't get me started on type conversions, dumbest possible error messages, the "IN (@multipleValues)" disaster, and oh my.
- michaelmior 10y agoI'm not saying this is ideal, but there are certainly possibilities for modularization. You can create views that cover cimmin query predicates and then write queries against those views.
- michaelmior 10y agos/cimmin/common/g
- ZenoArrow 10y ago> "which probably doesn't matter at the end of the day because almost no DBA would ever let you use it in production." Where I work, we have a DBA and use SQL CLR in production. In fact I'm in the process of writing a SQL CLR user defined function today. It's interesting that Microsoft have dropped support for them in Azure, but they're still supported in SQL Server 2016. Furthermore, starting from Visual Studio 2013 efforts were made to make them easier to deploy direct from Visual Studio, so clearly some people in Microsoft think they're worth supporting. I'd say as long as you have a sane database structure where you don't try to do all your processing in a single database instance, SQL CLRs are fine. For example, use them in databases that handle scheduled processes rather than in ones that can't have any downtime and you it'll be easier to redeploy them without much ceremony.
- mistermann 10y agoHave you ever tried (and succeeded) in signing a sqlclr dll? I wasn't able to accomplish it and neither could this guy: http://stackoverflow.com/questions/7032872/what-are-specific-steps-to-install-signed-clr-storedproc-to-sql http://stackoverflow.com/questions/7032872/what-are-specific... And if your answer is RTFM then you've probably not used SQL Server before, or were fortunate to have always work on a perfectly configured instance, because a lot of things (say, debugging) often don't work on a brand new install on a brand new windows box. There are tons of things like this where people have been crying for help from Microsoft for years, but the SQL Server team clearly does not give one fuck about their users problems.
- ZenoArrow 10y agoI've not needed to sign a SQLCLR DLL, all the SQLCLR code I've written has been using SAFE assemblies. That being said, I don't mind helping out. Here are the relevant notes I made when following the "Developer's Guide to SQL Server CLR Integration" on Pluralsight: "To configure assembly signing, open project properties, SQLCLR, scroll down to find and select the 'Signing...' button, check the 'Sign the assembly' checkbox, choose or create a 'Strong name key file' (if select <New...>, set key file name e.g. CLRLogReader, and set a password, OK to finish), OK to finish, then close project properties tab." From the troubleshooting section of the same course... "If you want to deploy an assembly that uses EXTERNAL_ACCESS (or UNSAFE) permissions instead of SAFE permissions, you need to perform some extra steps in order to grant those permissions to the login deploying the assembly. There are a few different ways to set those permissions (including enabling the EXTERNAL ACCESS ASSEMBLY permission for the DBO and switching the TRUSTWORTHY property on, or signing the assembly with a certificate), but the recommended method is to sign the assembly with an asymmetric key (recommended because it is both easy to do and secure). In VS, load properties for SQL Database project, SQLCLR tab, scroll down to find and click on Signing... button. Check the 'Sign the assembly' option, create/select strong key file, OK. Build project, make sure this process completes without errors. Then in SSMS will need to set up a user that can use the key that was used in the build process. Example: USE master; CREATE ASYMMETRIC KEY CLRLog FROM EXECUTABLE FILE = 'Path to SQL CLR DLL' --note this DLL was the one created in the build process carried out in VS CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY CLRLog GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin Note that you only have to configure this assembly signing once, subsequent deployments will reuse the key and login we configured to deploy the assembly, so deployment then becomes as straightforward as deployment of SAFE assemblies." Hope these notes prove useful.