Link to home
Start Free TrialLog in
Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil

asked on

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

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.
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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.
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
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.
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.
Avatar of Eduardo Fuerte

ASKER

Hi

Thank you for your replies!

What caused me strangeness was this doc:
User generated image
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.
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)?
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....
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.
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.
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.
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.
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.
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...
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.
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
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.
My conclusion: coding C# at T_SQL is a possibility to be exceptually used.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry the delay, still learning with your replies to better get the concepts.
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!