I've been using SQL Server to write queries. I was browsing online and I found that python and C# can be used to write query logic as well.
I was wondering is it more efficient to use another programming language instead of SQL for SQL Server?
-
you can't replace T-SQL completely, but you can extend its functionality via SQL CLR (see marc_s's answer below). Or you can use another language to interact with the SQL database by calling SQL queries.ADyson– ADyson2019-05-17 10:59:46 +00:00Commented May 17, 2019 at 10:59
-
2Short answer: for query logic, no. The best implementation of SQL in SQL Server is T-SQL. This is true even though T-SQL itself is not a particularly efficient language, because the query optimizer and the engine have an intimate relationship with SQL's internals in a way that the runtimes for other languages don't. This is why almost all ORMs will still involve translating things from/to SQL at some point. For things other than query logic, though (numerical processing, statistics, complicated locale-dependent string manipulation), it's a different story.Jeroen Mostert– Jeroen Mostert2019-05-17 11:02:05 +00:00Commented May 17, 2019 at 11:02
Add a comment
|
2 Answers
Essentially any logic that happens in a C# or Python program will be sent back to the data layer, simplistically, using SQL queries. For some more complex processing or buisness logic it may be preferable to push this logic up to the business logic layer away from the data layer.
Making changes to SQL is easier that code as in the case of C# or Python a new version with updates to logic etc will require a new version of the software to be shipped. Whereas a DB change can happen behind the scenes.
It depends entirely on what you want to acheive.
Comments
SQL Server as of version 2005 offers the SQL CLR - basically a trimmed-down version of the .NET runtime.
This allows you to write .NET code (in C#, VB.NET or any other .NET language) and have this executed within SQL Server.
It's a compelling feature for some scenarios (especially areas where the "native" T-SQL is lacking - string manipulation, regex handling etc.) - but it's often a bit tricky and hard to convince your company's DBA's to enable this feature; DBA's often don't really like the idea of C# code being executed inside their "sacred" database environment...
Read more about the SQL CLR integration here:
4 Comments
David Makogon
Not sure I'd be promoting SQL CLR, since general guidance is to not use it, especially given the fact that CLR can really abuse a SQL Server instance. It's not that it's a "sacred" database environment. It's that it's a server-side anti-pattern.
marc_s
@DavidMakogon: what *general guidance" are you referring to?? Any Microsoft docs that discourages use of SQL CLR?? I know a lot of DBA's a reluctant to enable SQL CLR - but I'm not aware of a "general guidance" against its use - it DOES HAVE its very compelling use cases!
David Makogon
I'm referring to guidance from database specialists who have been working with this tech for years (as well as DBAs). I can't point to any official Microsoft docs. Maybe I should have said general guidance based on what I've experienced over the past decade+, as this is my area of expertise (database work). I just see it as a red-flag. Sure, there are always exceptions. I just don't see it as something to promote as general case. Also, this is a feature that is unavailable in cloud implementations (such as Azure SQL DB service).
marc_s
@DavidMakogon: good point about Azure - that could definitely be a reason not to commit to SQL CLR stuff
