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

From: Tony Rogerson <tonyrogerson_at_sqlserverfaq.com>
Date: Sun, 12 Feb 2006 21:21:40 -0000
Message-ID: <dso8se$fl7$1$8300dec7@news.demon.co.uk>

> 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

>>>Can you change a database concurrency model via CLR ?

Thats simply using the ALTER DATABASE statement or using SET TRANSACTION ISOLATION, you would use ALTER DATABASE to allow the new isolation levels once and then use SET TRANSACTION ISOLATION whenever you needed a different isolation level.

>>>Can you have the database change the way it physicall stores data and >>>generates incremental backups via CLR ?

A User Definied Type can be a CLR class so can be anything you want, that C# class is then serialised with the table row.

I can go on, but to be frank, its a waste of my time, this forums time and probably your time so I won't; you have your view, which quite frankly seems to like to twist what the reality is - a 'twilight zone reality' and then there is mine which relies on fact and proof.

Tony.

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


"Mark Townsend" <markbtownsend_at_comcast.net> wrote in message 
news:43EF9059.2040501_at_comcast.net...


> Tony Rogerson wrote:

>>>Can you then build an index on a column using the regex, gather stats for 
>>>it, and then have the optimiser consider this index in it's costing 
>>>calculation ?
>>
>>
>> So long as its deterministic - yes.
>


> Can you show me an example of this ?

>
>>
>>
>>>Can you change a database concurrency model via CLR ?
>>>
>>
>>
>> Yes
>


> Can you show me an example of this ?

>
>>
>>
>>>Can you have the database change the way it physicall stores data and 
>>>generates incremental backups via CLR ?
>>>
>>
>>
>> You have User Definied Types which are serialised, so you can create 
>> whatever objects you want as a UDT in CLR, a good example would be a 
>> temporal object.
>>
>


> I don't think you understood the question.

>
>>
>>>Can you have a database support active/active clustering via CLR ?
>>>
>>>Can you have a database run on Linux or any other operating system via 
>>>CLR ?
>>
>>
>> The last two brings to think you don't actually know what CLR and .NET 
>> actually are, you can CLR in a database that is clustered - not a 
>> problem.
>>


> That wasn't the question 

Received on Sun Feb 12 2006 - 15:21:40 CST