Database Reference
In-Depth Information
,ZipCodes(Zip)
as
(
select v.v
from (
values('99991'),('99992'),('99993'),('99994'),('99995'),
('99996'),('99997'),('99998'),('99999'),('99990')
) v(v)
)
,States(state)
as
(
select v.v
from (
values('AL'),('AK'),('AZ'),('AR'),('CA'),('CO'),('CT'),('DE'),('FL'),
('GA'),('HI'),('ID'),('IL'),('IN'),('IA'),('KS'),('KY'),('LA'),('ME'),
('MD'),('MA'),('MI'),('MN'),('MS'),('MO'),('MT'),('NE'),('NV'),('NH'),
('NJ'),('NM'),('NY'),('NC'),('ND'),('OH'),('OK'),('OR'),('PA'),('RI'),
('SC'),('SD'),('TN'),('TX'),('UT'),('VT'),('VA'),('WA'),('WV'),('WI')
,('WY'),('DC'),('PR')
) v(v)
)
insert into dbo.Addresses(Address,City,State,ZipCode)
select Street,City,State,Zip
from Streets cross join Cities cross join States cross join ZipCodes;
insert into dbo.AddressesCLR(Address)
select Address + ', ' + City + ', ' + State + ', ' + ZipCode
from dbo.Addresses;
Now let's run the test and look at the performance of the queries against both tables. We will use the queries
shown in Listing 14-5.
Listing 14-5.
UDT performance: Querying the data
select State, count(*)
from dbo.Addresses
group by State
select Address.State, count(*)
from dbo.AddressesCLR
group by Address.State
As you see in Figure
14-2
, the second select introduces a CLR method call for every row, and this significantly
affects the performance of the query. You can see information about the call in the
Computer Scalar
operator
properties, as shown in Figure
14-3
.
