Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec

> This example is a little trite, in that the function used returns a known
> data type - int. So presumably the optimizer can gather stats on this
> column (and index) as it would any other int column in the database -
> there are a known range of values, after all. Also, the column (in this
> example at least), has been presisted in the table.
>
> My question was a little more complicated than that - an index on a column
> that is computed using a regex expression. It's still deterministic, and
> can still be persisted. But now the optimizer has a much harder job to
> gather and evaluate a range of stats.

I posted that basic example to illustrate how you can do it - that is the example; i could go and knock another one up if you like.

I think you do not understand the terms 'deterministic' and 'persisted' within a programming model; derterministic means given the same input the output will be the same, so if you use time in your calculation then it is 'non-deterministic', persisted means the value is held as it is, with that in mind - the CLR scalar function only needs to be called on UPDATE or INSERT because that is the only time the value could change. So, on the INSERT or UPDATE the CLR is called, the value got and stored and the stats only need to worry about the value because the CLR function is both persisted and deterministic.

You can index on a CLR User Definied Type because SQL Server just indexes the binary (serialised) stream, again it needs to be deterministic to index the CLR types or UDF's in computed columns.

Going back to the RegEx example, it would be a simple matter of changing the example to accept say an email address as input to the CLR UDF and give back a Y or N if the email is valid (by using the regex expression).

Does Oracle have the ability to create UDT's or UDF's using CLR? Can you index them?

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Mark Townsend" <markbtownsend_at_comcast.net> wrote in message 
news:43EFF3DB.8030701_at_comcast.net...


> Tony Rogerson wrote:

>>>Can you show me an example of this ?

>>

>>

>> This gives an explanation and example 

>> http://blogs.msdn.com/sqlclr/archive/2005/10/03/476661.aspx entitled 

>> 'Tutorial on SqlClr Computed Columns and Indexability'

>>

>> Make sure you read to the end, but for convience i will post the example 

>> here...

>>

>> using System;

>> using Microsoft.SqlServer.Server;

>>

>> public class testclass

>> {

>>      [SqlFunction(IsDeterministic=true, IsPrecise=true)]

>>      public static int clr_func(int i)

>>      {

>>           return i*i;

>>      }

>> }

>>

>>

>> create table clr_persisted_table

>> (

>> col int,

>>                    comp_col as dbo.clr_udf(col) persisted

>>           ) go

>>           -- create clustered and nonclustered indexes on col and 

>> comp_col from clr_table

>>           create clustered index pri_idx on clr_persisted_table(col)

>>           go create nonclustered index sec_idx on 

>> clr_persisted_table(comp_col)

>>           go

>

> This example is a little trite, in that the function used returns a known 

> data type - int. So presumably the optimizer can gather stats on this 

> column (and index) as it would any other int column in the database - 

> there are a known range of values, after all. Also, the column (in this 

> example at least), has been presisted in the table.

>

> My question was a little more complicated than that - an index on a column 

> that is computed using a regex expression. It's still deterministic, and 

> can still be persisted. But now the optimizer has a much harder job to 

> gather and evaluate a range of stats.