Avatar of Eduardo Fuerte
Eduardo Fuerte
Flag 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.
DatabasesMicrosoft SQL Server

Avatar of undefined
Last Comment
Eduardo Fuerte

8/22/2022 - Mon
Mark Wills

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 Todd

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 Chong

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Pavel Celba

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 Fuerte

ASKER
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?
Pavel Celba

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Eduardo Fuerte

ASKER
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 Wills

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 Fuerte

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Pavel Celba

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 Fuerte

ASKER
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 Wills

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
David Todd

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
Pavel Celba

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

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Pavel Celba

Agreed.
Eduardo Fuerte

ASKER
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?
Pavel Celba

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...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Eduardo Fuerte

ASKER
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?
Pavel Celba

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 Todd

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Mark Wills

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 Fuerte

ASKER
My conclusion: coding C# at T_SQL is a possibility to be exceptually used.
ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Eduardo Fuerte

ASKER
Sorry the delay, still learning with your replies to better get the concepts.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Eduardo Fuerte

ASKER
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!