Could you point if it's possible to develop MSSQLServer scripts by using another languages different than T-SQL?

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
HI Experts

Could you point if it's possible to develop MSSQLServer scripts by using another languages different than T-SQL?

If so what languages are supported.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
At the end of trying to find alternatives and examples of what you are asking, you will come to the realisation that the only *real* scripting language is T-SQL.  It is the language of SQL Server.

Even in the different programming examples, you will often see "select top nn from A_Table where ...."

Have a look at : https://docs.microsoft.com/en-us/sql/connect/homepage-sql-connection-programming?view=sql-server-ver15

Probably not the answer you want, but my answer is a qualified "No"

When you get into data cubes and such like, there are the corresponding languages - but again - they are specific to the environment. Things like dax / mdx, M and R or dplyr syntax can work in those environments. There are some tools like visual query designers, but, you cannot beat the good old T-SQL as the scripting language for SQL Server database.
David ToddSenior Database Administrator

Commented:
Hi,

Yes, you can develop CRL procedures and functions in vb.net and c#.net, and I have done so for things that T-SQL can't do, ie access the filesystem. But for data queries and scripts, you'll keep coming back to T-SQL, just like Mark said.

HTH
  David
Ryan ChongSoftware Team Lead

Commented:
you need to use the correct language when communicate with a person, this same concept applies in programming, you can use tools like an interpreter in between to translate the languages back and forth so that it can be understand, but at the end of the story, it need to be understandable. by introduce an interpreter, it comes with a cost. you need to evaluate if you really want to do it, and how you going to do it.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Hello Eduardo,

you may use whatever language you decide BUT such language must be converted into T-SQL at the end.
An example is e.g. Linq in C# or VB.NET which converts Linq query syntax to T-SQL. Entity framework is another example.

Of course, pure T-SQL code is better manageable. If you are calling Stored Procedure written in T-SQL then you have many direct options how to optimize such SP if necessary. To optimize complex Linq query is not so easy.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi

Thank you for your replies!

What caused me strangeness was this doc:
img001
It looks that if someone decides to use SSMS to simply code an PHP piece of code ( a function f.e.) inside an T-SQL code for some special reazon  it's realizable. After reading your replies I guess it is, the DML  still have to be done with T-SQL. Did I correctly catch the meaning?
Yes, you may use R and Python and all the languages listed. But that's still not suitable for direct data access. You are always accessing the data as a client or your .NET module must be compiled into DLL running on the SQL Server machine.

Important is that everything depends on security settings.
CLR .NET modules is possible to disable.
User access can be restricted to given database with restricted rights.
SQL Server itself should run under user account which may access just the data files.
Etc.

Of course, you as a developer can access almost everything.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Just to my better comprehension could you give or point an example of  code (stored procedure/ function) using a hybrid code (T-SQL and another language)?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
if you have a look at the link above, it does show some examples - but then you are talking about learning other programming languages - not just T-SQL (that link was : https://docs.microsoft.com/en-us/sql/connect/homepage-sql-connection-programming?view=sql-server-ver15 )

And each language does tend to have its own variant of SSMS. A lot of the MS world will be using Visual Studio for their .Net based solutions.

Or, if curious about R then you will want to find out about RStudio and associated packages such as dpylr. Have a read of https://db.rstudio.com/overview Also note that database access via this type of approach tends to become ODBC based (but not limited to). You basically encapsulate the T-SQL code and execute it at the server end via your ODBC connection.

Then there are compounded environments like a Web UI + LINQ + C-Sharp + T-SQL. For example : https://www.c-sharpcorner.com/article/stored-procedure-with-linq-to-sql/

There isnt a single "clean" and simple/straight forward answer to your question. It is very dependant on what you are needing to achieve. Which is sounding a lot more than an option (or alternative) to T-SQL for scripting.... It almost sounds as if you are wanting an all encompassing development environment....
Eduardo FuerteDeveloper and Analyst

Author

Commented:
After reading your replies I guess I have to reformulate my question to: is it possible by using SSMS API to code with another languages different than T-SQL, mixed with PHP code f.e. - embeded in the T-SQL code itself, based on the doc I read (maybe it's a non sense)
It's very common to connect and call an sp from inside PHP code f.e.
I'm frequently in the role of MSSQLServer developer and had a strangeness about this possibility, so I formulated this question.
No, you should not use SSMS for this purpose. SSMS is just the SQL Server client tool and you may use it to handle everything about the data and SQL Server setup. SSMS is not intended to develop UIs.

PHP, ASP.NET, C# or whatever language used for Presentation layer and its connection to data sources requires to use appropriate tool, e.g. Visual Studio which allows it and which is available for free. You may design UIs for various platforms in VS and VS also allows to connect to SQL Server data during the app development.

SQL Server also offers Reports creation via SSRS but again this is also easily achievable in Visual Studio.

MS SQL Developer develops queries, various SQL scripts, Stored procedures, Triggers, designs tables, views etc. MS SQL Developer is far from UI design obviously.

Of course, the above is also valid for non-Microsoft databases and tools like MySQL, Qt etc.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Just to clear I wouldn't expect the use of PHP f.e. that is a server language as an user interface code mainly inside T-SQL code, just eventually any server jobs that could make sense with PHP use like file directory location.

But after reading I think my doubt is solved.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
I think what that diagram is trying to do is to reinforce Visual Studio as "the" developer platform for a number of possible solutions.

There is a level of support for PHP with appropriate plug-ins/extensions for Visual Studio.

I am not a PHP developer, certainly played with it, but not in a position to validate Visual Studio capability in that regard.

There are a couple of sites for you that might help your deliberations....

https://code.visualstudio.com/docs/languages/php
https://marketplace.visualstudio.com/items?itemName=DEVSENSE.PHPToolsforVisualStudio

Having gone through those sites, it does look very interesting.... certainly supports the claims that the diagram suggests as being true.

Being predominantly a database person, I tend to use SSMS for SQL Server directly, and I know that wont support PHP. But Visual Studio is a bit different, and probably can achieve the goals. Reading through the sites above, it certainly indicates that it will work for you. You definitely can write T-SQL via Visual Studio : https://visualstudio.microsoft.com/vs/features/ssdt/

Hope that helps.
David ToddSenior Database Administrator

Commented:
Hi,

An example of mixed language development in SQL.

I needed to parse a directory and find the latest backup file. Of course this can be done with xp_cmdshell, but it is fairly clunky. To do this we had a small application that was called as a job step that located the desired file, and copied it across to the destination server prior to restore.

Based on that logic, I developed a CLR routine, that identified the correct file, and returned the full URL for it. That was encapsulated as a function, so when building the string for the restore command, the function was invoked to return the source path and filename, and I let T-SQL manage the restore. Because this was now SQL 2008R2 and I was using the backup compression feature, I didn't need to copy the backup file, but could instead perform a restore from the network. Testing showed this was stable and reliable.

HTH
  David
Interesting approach but not for everybody. Many SQL Server installations do have CLR option disabled because it can possibly be vulnerable.
David ToddSenior Database Administrator

Commented:
Hi pcelba

Ditto with xp_cmdshell. I think CLR is safer.

And, what I didn't say, was this was a test SQL instance, that was restoring production every night. So automation was pretty much essential.

Maybe today I'd be using Ola Hallengren's maintenance script, and then Brent Ozar's sp_restoredatabase procedure to manage the restore, rather than writing my own CLR routine.

Kind regards
  David
Agreed.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
I don't know if I misunderstood the last interactions.

Since CLR is the core of .Net and could be enabled from inside SSMS it's possible to access .Net functionalities from MSSQLServer, that's it?
Yes, that's true. But you as a developer can enable it on your development machine whenever you need it. Once we are talking about deployment to production then customers can have their own (more restrictive) rules...
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Good.

I guess that is a good example of  sp using CLR functionality:

sp calling .Net

If so, returning to the original question it's possible to mix languages at sp code, at least  C#. Ok?
Yes, the .NET CLR SP is some kind of languages mixing as any other code samples which call SQL commands via ODBC.

Just remember the way how the above example presents the Tabular Result is a little bit counterproductive - You are calling the .NET code from a session which is connected to SQL server but the code connects inside the same SQL engine again...

Of course, to use CLR for complex calculations based on given input values is good implementation.
David ToddSenior Database Administrator

Commented:
Hi,

The CLR function I mentioned above was written in c#.

Its deployment to the server and registration and all that was done with T-SQL.

In my T-SQL code it worked just like any other scaler function that returned a string.

But the CLR function had no database interaction - this is possible of course. And the reason for using c# was I wanted access to the filesystem object to search a directory for a particular file.

Regards
  David
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
And it is more often "the other way around" in so much as your dev environment can call and access SQL Server - Visual Studio for example.

Yes, there are some things you can do from SSMS, but SSMS and T-SQL are still and always will be the tools for SQL Server.

You cannot write C# apps from within SSMS. But you can write T-SQL from within Visual Studio, where you can also write your C# code and PHP and VB etc etc.

So the answer is still "No", not from SSMS and T-SQL.

But via Visual Studio, you can.

Must choose the best toolset for the appropriate tasks. Especially in a multi faceted project where you have to mix-n-match components to then combine into an overall solution.

It really is the Visual Studio side (and SSMS was born that way), that enables Microsoft to make those statements.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
My conclusion: coding C# at T_SQL is a possibility to be exceptually used.
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Interesting conclusion you have arrived at....

While it is possible to access some C# objects (and other objects)) via T-SQL, I think it is inaccurate to say "coding C# at T-SQL ...". Those inaccuracies are reflected in the how and when and traps and restrictions and constraints - hopefully echoed in your "exceptually" clause above. But there are profound differences in database engine vs database objects and the tools available (depending on task and therefore which tool, and language choices for that tool).

Using your own example of that "HelloWorld" CLR routine above, you will note that the C# code was first written (and compiled) in Visual Studio. You will then notice that SQL Server had to be configured to enable "external calls". Then you can create (in sql server using T-SQL), a SQLCLR assembly pointing to the compiled DLL. It is then ready to use from SQL Server (after configuring your database engine)...

Note that you have not written any C# from T-SQL or SSMS, rather, enabled that piece of code to be accessed via T-SQL.

A more recent variant of that "HelloWorld" can be found at : https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/getting-started-with-clr-integration?view=sql-server-ver15 and worthwhile having a look at the steps described there.

Things have changed a little more in the latest editions of Microsoft products/tools. For example, using SSDT within your Visual Studio project, there is a tighter integration (via SSDT = Sql Server Data Tools). It can avoid some of the manual steps in between (as per that HelloWorld example) - by automating (behind the scenes) some of those steps.

In fact there is an ever increasing inclusion of database development tasks into Visual Studio (SSDT). And to help support that process, SQL Server now incorporates and leverages the .Net framework components.

Have a read of : https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/common-language-runtime-integration-overview?view=sql-server-ver15 
and at a more general level : https://docs.microsoft.com/en-us/sql/ssdt/how-to-work-with-clr-database-objects?view=sql-server-ver15

Just to further clarify, some of us database people often refer to SQL Server, when sometimes we should be saying "database engine". T-SQL is the language interface we use to interact with the database engine. SSMS is the tool that allows that level of interaction. With SSDT in Visual Studio, you can create database objects. And Microsoft has always been very keen to have a more traditional developer approach of using Visual Studio for (application) development work.

The history of the two interfaces (ie SSMS v Visual Studio), leveraged each other (or maybe more accurately "competed" with each other). SQL Server 2005 saw the emergence of the new (now familiar) interface. That coincides with the extensions of Visual Studio 2005 (when MS finally dropped the .Net monnicker).  Now, the latest versions of SSMS are built on the Visual Studio Shell. Visual Studio now supports approximately 36 different programming languages.....

So, the interface used to provide all that flexibility seen in that graphic you posted above, is not SSMS, it is Visual Studio.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Sorry the delay, still learning with your replies to better get the concepts.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
The meaning of the slide is that the T-SQL code is included in the language coding itself. Just it.


Thank you for the guidance!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial