Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Advantage of SQL SERVER CLR

What advantages does SQLServer CLR offer over T-SQL? Is using .NET syntax easier than T-SQL? I see that you can define user types, but I'm not quite clear on why that's better. For example, you could define an email type and it would have a prefix property and a domain property. You could then search on domain or prefix or both. However, I don't see how that's any different from adding a couple of columns one called prefix and one called domain and searching on them individually. Maybe someone has real world reasons why this is better.

like image 357
Anthony Potts Avatar asked Jan 13 '09 17:01

Anthony Potts


People also ask

What is the use of CLR in SQL Server?

CLR Integration Security NET Framework common language runtime (CLR) manages and secures access between different types of CLR and non-CLR objects running within SQL Server. These objects may be called by a Transact-SQL statement or another CLR object running in the server.

What is the significance of CLR enabled option?

CLR integration allows us to use user assemblies when coding a database solution in SQL Server. It was meant to be both an improvement and a future replacement to extended stored procedures, which are a special kind of stored procedure written using C language and compiled in machine code as a dll library.

How fast is CLR?

Test code can be executed in a loop for operations that return in less than 1 millisecond.


2 Answers

I'll give one good example: CLR has a built in RegEx object, which is sorely lacking in SQL Server. Now it's trivial to write functions to do regex-based validation constraints/repairs.

like image 132
Joel Coehoorn Avatar answered Sep 23 '22 13:09

Joel Coehoorn


Different purposes. A CLR stored procedure is useful for things where writing highly procedural code or using system facilities not accessible from T-SQL would be of benefit. Although there is no inherent reason why one can't write application sprocs against it, generally you would not view CLR sprocs as a merely a different language for writing application sprocs. Typically, most uses of a CLR sproc would be for system purposes rather than application components, although this is by no means a hard and fast rule.

The CLR integration layer does offer some facilities that are not directly available from T-SQL stored procedures, such as custom aggregate functions. It also offers access to .Net libraries, which may be useful to get access to capabilities that T-SQL cannot support.

T-SQL does traditional database stuff, and integrates with the query optimiser, so it is still most appropriate for set-oriented database code. There are API hooks for CLR sprocs to provide information to the query optimiser, but this adds some complexity.

One can also use CLR integration to define functions that are accessible to T-SQL code. In some cases these can be faster and more memory efficient than T-SQL functions. The Wrox press book on CLR integration discusses this in some depth.

like image 24
ConcernedOfTunbridgeWells Avatar answered Sep 21 '22 13:09

ConcernedOfTunbridgeWells