PROCEDURES AND FUNCTIONS IN .NET LANGUAGES CREATE TABLE

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES CREATE TABLE R1 ( Id CHAR(1), Other AS dbo.Repeat(3, Id) ) INSERT INTO R1 VALUES ( A ) SELECT * FROM R1 GO A AAA The value of the Other column is just the character in the first column, repeated three times. Although this is not a very useful use of a computed column, it does illustrate how they work. Because a function can be used in a query or a column definition, SQL Server needs to know a few extra things about it. What would happen if the Repeat function were modified so that it repeated the character a random number of times, but not more than three? Each time you did a select from the R1 table, you could potentially see a different number of characters in the Other column. There is nothing intrinsically wrong with this behavior; some built-in functions in SQL Server give different results each time you call them. Suppose further that an index was built on the Othercolumn, with the random Repeatfunction. Would that make any sense? The index wouldn t be very useful, because it would only have a snapshot of what was in the column when it read it, so selects using it wouldn t work. By default, SQL Server assumes that a function based on a CLR method cannot be used to build an index, and will produce an error if you try to make one. Every function in SQL Server has two properties that are associated with it that SQL Server uses to decide if that function can be used in an index: IsDeterministic and IsPrecise. By default, a function based on a CLR method has both of these properties as false. Besides using a computed column as in index, you can also make it PERSISTED. When you make a computed column PERSISTED, it is calculated only once, and the resulting value is stored and then retrieved whenever it is needed. A PERSISTED computed column takes more space than one that isn t, but makes selects run faster. Obviously, a computed column that returned a different value every time you read it would not be any more useful as a PERSISTED column than as an index. Table 3-5 shows how SQL Server decides whether or not to allow a computed column to be PERSISTEDor used in an index. The Y* indicates the computed column involved must be PERSISTEDor the index will not be allowed. An indexed view is special kind of view that

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

Bookmark the permalink.

Comments are closed.