Database Reference
In-Depth Information
Chapter 10
User-Defined Functions
One of the first things that developers learn about in their career is the benefits of code reuse. Encapsulating and
reusing code into separate libraries speeds up the development and testing process and reduces the number of bugs
in the system.
Unfortunately, the same approach does not always work well in the case of T-SQL. From a development
and testing standpoint, code reuse definitely helps. However, from a performance standpoint, it could introduce
unnecessary overhead when implemented incorrectly. One such example is a “one size fits all” approach where
developers create a single stored procedure or function and then use it to support different use-cases. For example,
consider a system with two tables— Orders and Clients —as shown in Listing 10-1.
Listing 10-1. Code reuse: Tables creation
create table dbo.Clients
(
ClientId int not null,
ClientName varchar(32),
constraint PK_Clients
primary key clustered(ClientId)
);
create table dbo.Orders
(
OrderId int not null identity(1,1),
Clientid int not null,
OrderDate datetime not null,
OrderNumber varchar(32) not null,
Amount smallmoney not null,
IsActive bit not null,
constraint PK_Orders
primary key clustered(OrderId)
);
create index IDX_Orders_OrderNumber
on dbo.Orders(OrderNumber)
include(IsActive, Amount)
where IsActive=1;
 
Search WWH ::




Custom Search