Database Reference
In-Depth Information
There are some cases, however, when you must use CLR code. One example is on queries that perform RegEx
(regular expression) evaluation as part of the
where
clause. It would be inefficient to move such an evaluation to the
client code, and there is no regular expressions support in SQL Server. CLR is your only option in this instance. In
other cases, however, when imperative logic can be moved to application servers, you should consider such an option,
especially when those servers reside closely to SQL Server, and network latency and throughput is not an issue.
In this section, we will compare the performance of CLR and T-SQL in a few different areas. Similar to other SQL
Server technologies, the choice between CLR and T-SQL fits into the “It depends” category.
Before we begin, let's look at Table
13-1
and compare the objects supported by both technologies.
Table 13-1.
CLR and T-SQL object types
T-SQL
CLR
Scalar user-defined functions
Yes
Yes
Multi-statement table-valued
user-defined functions
Yes
Yes
Inline table-valued user defined
functions
Yes
No
Stored procedures
Yes
Yes
Triggers
Yes
Yes
User-defined aggregates
No
Yes
User-defined types
No
Yes
■
even though you can create t-SQL types with
CREATE TYPE
statements, t-SQL user-defined types are delivered
from scalar t-SQL types. CLr user-defined types, on the other hand, are classes that can have multiple attributes and/or
methods. We will discuss t-SQL and CLr user-defined types in greater depth in Chapter 14, “CLr types.”
Note
CLR lets you create user-defined aggregates and complex types, which cannot be done with T-SQL.
User-defined aggregates are a great way to expand the standard SQL Server function library and, as you will see later
in the chapter, they can provide very good performance when compared to T-SQL code. User-defined types can also
help in some cases.
Let's create a simple table and populate it with some data, as shown in Listing 13-6.
Listing 13-6.
Test table creation
create table dbo.Numbers
(
Num int not null,
constraint PK_Numbers
primary key clustered(Num)
);
