USER-DEFINED TYPES AND AGGREGATES SQL Server provides a number of aggregate functions, such as MAXand AVG, for many of its scalar types. Likewise, in SQL Server 2005 you can create user-defined aggregates for the user-defined types that you create. To illustrate a user-defined type, we will implement one that supports a simple linear dimension. It is called LDim. An instance of this type will contain a numeric value and units. The units can be any of in , ft , or yd . A typical LDimwe might write would look like 1 ft or 7 yd . We want to be able to treat LDimas we would any other scalar variable, so we will also implement the aggregate functions LDimAvgand LDimMaxfor it. Listing 5-1 shows a SQL batch that uses LDim. Listing 5-1: Adding and Using a User-Defined Type First we catalog an assembly we have previously built that contains the implementation of LDim This chapter is about how to make this assembly CREATE ASSEMBLY LDim FROM c:userTypesbinLDim.dll GO Next we add LDim to the scalar type system CREATE TYPE LDim EXTERNAL NAME LDim.[UDT.LDim] GO Now we can use LDim just like any other scalar type Create a table that contains rectangular tiles, using LDim for the dimensions of the tile CREATE TABLE Tiles ( Id IDENTITY PRIMARY KEY, Length LDim, Width LDim ) GO add some tiles to the table INSERT INTO Tiles VALUES (N 1 ft , N 6 in ) INSERT INTO Tiles VALUES (N 2 yd , N 2 in ) INSERT INTO Tiles VALUES (N 7 in , N 1 in ) INSERT INTO Tiles VALUES (N 2 ft , N 3 in ) INSERT INTO Tiles VALUES (NULL, NULL) find all the tiles that are greater than 18 in long SELECT Id FROM Tiles WHERE Length > N 18 in GO 2 order tiles by their width SELECT Id from Tiles ORDER BY Width go 3 2 4 1 5

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

Bookmark the permalink.

Comments are closed.