Cluster ratio? Cluster factor?

MS SQL Server 2000
let's say there is a table ORDERS with a clustered index on
(order_date, some other column). Also there is a non-clustered index on
shipment_date. Since most orders are shipped within 3 business days,
the data is stored almost ordered by shipment_date.
Most rows for the same shipment date are stored on adjacent data pages.
There is another index on zipcode, which does not correlate with order
date at all. Is there anything I can read from system views to tell the
difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
TIAHi
You probably need DBCC SHOWCONTIG, you can check out the documentation in
Books online. If you want more information on this sort of thing you may als
o
want to read "Inside SQL Server 2000" by Kalen Delaney ISBN 0-7356-0998-5 an
d
Ken Henderson's "The Guru's guide to SQL Server Architecture and
Internals" ISBN 0-201-7004706
John
"ford_desperado@.yahoo.com" wrote:

> MS SQL Server 2000
> let's say there is a table ORDERS with a clustered index on
> (order_date, some other column). Also there is a non-clustered index on
> shipment_date. Since most orders are shipped within 3 business days,
> the data is stored almost ordered by shipment_date.
> Most rows for the same shipment date are stored on adjacent data pages.
> There is another index on zipcode, which does not correlate with order
> date at all. Is there anything I can read from system views to tell the
> difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
> TIA
>

Cluster indexing time increasing each week

SQL 7.0
There's a table with 100M records (and growing) that's
needing to be reindexed every week. If I don't recreate
the index, the fragmentation causes performance of the
queries to get slower and slower. I have a job that runs
the following script, but it's taking a little longer
each week and I'm wondering if there's a way to speed it
up...
Any help appreciated.
Thx,
Don
job script:
CREATE UNIQUE CLUSTERED
INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
table structure:
CREATE TABLE [dbo].[DATA] (
[Name] [varchar] (32) NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[TD] [smalldatetime] NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[SP] [decimal](18, 6) NULL ,
[SD] [smalldatetime] NULL ,
[DV] [int] NOT NULL ,
[DI] [int] NOT NULL ,
[Fg] [int] NULL
) ON [PRIMARY]
GODon wrote:
> SQL 7.0
> There's a table with 100M records (and growing) that's
> needing to be reindexed every week. If I don't recreate
> the index, the fragmentation causes performance of the
> queries to get slower and slower. I have a job that runs
> the following script, but it's taking a little longer
> each week and I'm wondering if there's a way to speed it
> up...
> Any help appreciated.
> Thx,
> Don
> job script:
> CREATE UNIQUE CLUSTERED
> INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> table structure:
> CREATE TABLE [dbo].[DATA] (
> [Name] [varchar] (32) NOT NULL ,
> [Date] [smalldatetime] NOT NULL ,
> [TD] [smalldatetime] NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [SP] [decimal](18, 6) NULL ,
> [SD] [smalldatetime] NULL ,
> [DV] [int] NOT NULL ,
> [DI] [int] NOT NULL ,
> [Fg] [int] NULL
> ) ON [PRIMARY]
> GO
Is there a reason you chose those columns for the clustered index? What
is the PK on the table? Are there other indexes? One reason for
fragmentation has to do with clustered index keys that change. Or it
could be caused by newly inserted keys that require new pages. You could
try using DBCC INDEXDEFRAG (oh, that's SQL 2000 and you are on 7). It is
an online operation and may keep the table from getting too fragmented.
I don't know if you have other indexes, but if you do, rebuilding a
clustered index can cause the non-clustered indexes to rebuild as well,
although using the DROP_EXISTING should deal with this somewhat. Another
thing to remember is that the clustered keys are a part of all
non-clustered indexes. A large clustered index key like yours can cause
the non-clustered index to bulk up.
I assume you are using a 90 Fill Factor because you have a lot of
inserts that occur over the course of a week. Leaving 10% of each page
free is a lot of available space and makes the table that much larger.
It does give you some room for growth before page splits start to occur,
but you might consider lowering the free space if your inserting reach
high levels. Are you really adding 10% to the table each week? If not,
consider leaving less space.
What types of queries are you running on this table? Do the clustered
keys get updated? Do you have other indexes? Have you considering using
the clustered index for something else? It would help to understand your
table and its use a little more.
--
David Gugick
Imceda Software
www.imceda.com|||I don't know what values you are putting into name and date columns. If date
values are subsequent then making date first key in clustered index would
decrease fragmentation.
--
Thank you,
Alex
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:09bf01c4f1c0$c086a180$a501280a@.phx.gbl...
> SQL 7.0
> There's a table with 100M records (and growing) that's
> needing to be reindexed every week. If I don't recreate
> the index, the fragmentation causes performance of the
> queries to get slower and slower. I have a job that runs
> the following script, but it's taking a little longer
> each week and I'm wondering if there's a way to speed it
> up...
> Any help appreciated.
> Thx,
> Don
> job script:
> CREATE UNIQUE CLUSTERED
> INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> table structure:
> CREATE TABLE [dbo].[DATA] (
> [Name] [varchar] (32) NOT NULL ,
> [Date] [smalldatetime] NOT NULL ,
> [TD] [smalldatetime] NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [SP] [decimal](18, 6) NULL ,
> [SD] [smalldatetime] NULL ,
> [DV] [int] NOT NULL ,
> [DI] [int] NOT NULL ,
> [Fg] [int] NULL
> ) ON [PRIMARY]
> GO
>|||"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10tjnve4gf28cd7@.corp.supernews.com...
> I don't know what values you are putting into name and date columns. If
date
> values are subsequent then making date first key in clustered index would
> decrease fragmentation.
subsequent = sequential, of course|||Thank you Mark, that's what I meant, sorry for confusion, English is not my
first language.
--
Thank you,
Alex
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:NtWdnTDXGpYUeUTcRVn-qw@.sti.net...
> "Alex" <alex_removethis_@.healthmetrx.com> wrote in message
> news:10tjnve4gf28cd7@.corp.supernews.com...
> > I don't know what values you are putting into name and date columns. If
> date
> > values are subsequent then making date first key in clustered index
would
> > decrease fragmentation.
> subsequent = sequential, of course
>|||On Mon, 3 Jan 2005 10:19:23 -0800, "Don"
<anonymous@.discussions.microsoft.com> wrote:
>SQL 7.0
>There's a table with 100M records (and growing) that's
>needing to be reindexed every week. If I don't recreate
>the index, the fragmentation causes performance of the
>queries to get slower and slower. I have a job that runs
>the following script, but it's taking a little longer
>each week and I'm wondering if there's a way to speed it
>up...
Are these SELECT queries or INSERT, UPDATE, or DELETE, and whichever,
do they reference the clustered index?
If the file grows each week then yes, it is going to take a little
longer every week.
Maybe you should look into partitioned tables?
J.|||You should really consider moving non-current data to a DSS or Warehouse
structure. I suspect that it is not fragmentation that is your problem as
much as it is the statistics. As the base number of records increases, the
more new records require to be inserted or modified before the AUTOSTATS
kicks in. You might just try updating the statistics more frequently and
rely less on the index rebuild operations.
Sincerely,
Anthony Thomas
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:09bf01c4f1c0$c086a180$a501280a@.phx.gbl...
SQL 7.0
There's a table with 100M records (and growing) that's
needing to be reindexed every week. If I don't recreate
the index, the fragmentation causes performance of the
queries to get slower and slower. I have a job that runs
the following script, but it's taking a little longer
each week and I'm wondering if there's a way to speed it
up...
Any help appreciated.
Thx,
Don
job script:
CREATE UNIQUE CLUSTERED
INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
table structure:
CREATE TABLE [dbo].[DATA] (
[Name] [varchar] (32) NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[TD] [smalldatetime] NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[SP] [decimal](18, 6) NULL ,
[SD] [smalldatetime] NULL ,
[DV] [int] NOT NULL ,
[DI] [int] NOT NULL ,
[Fg] [int] NULL
) ON [PRIMARY]
GO

Cluster indexing time increasing each week

SQL 7.0
There's a table with 100M records (and growing) that's
needing to be reindexed every week. If I don't recreate
the index, the fragmentation causes performance of the
queries to get slower and slower. I have a job that runs
the following script, but it's taking a little longer
each week and I'm wondering if there's a way to speed it
up...
Any help appreciated.
Thx,
Don
job script:
CREATE UNIQUE CLUSTERED
INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
table structure:
CREATE TABLE [dbo].[DATA] (
[Name] [varchar] (32) NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[TD] [smalldatetime] NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[SP] [decimal](18, 6) NULL ,
[SD] [smalldatetime] NULL ,
[DV] [int] NOT NULL ,
[DI] [int] NOT NULL ,
[Fg] [int] NULL
) ON [PRIMARY]
GODon wrote:
> SQL 7.0
> There's a table with 100M records (and growing) that's
> needing to be reindexed every week. If I don't recreate
> the index, the fragmentation causes performance of the
> queries to get slower and slower. I have a job that runs
> the following script, but it's taking a little longer
> each week and I'm wondering if there's a way to speed it
> up...
> Any help appreciated.
> Thx,
> Don
> job script:
> CREATE UNIQUE CLUSTERED
> INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> table structure:
> CREATE TABLE [dbo].[DATA] (
> [Name] [varchar] (32) NOT NULL ,
> [Date] [smalldatetime] NOT NULL ,
> [TD] [smalldatetime] NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [SP] [decimal](18, 6) NULL ,
> [SD] [smalldatetime] NULL ,
> [DV] [int] NOT NULL ,
> [DI] [int] NOT NULL ,
> [Fg] [int] NULL
> ) ON [PRIMARY]
> GO
Is there a reason you chose those columns for the clustered index? What
is the PK on the table? Are there other indexes? One reason for
fragmentation has to do with clustered index keys that change. Or it
could be caused by newly inserted keys that require new pages. You could
try using DBCC INDEXDEFRAG (oh, that's SQL 2000 and you are on 7). It is
an online operation and may keep the table from getting too fragmented.
I don't know if you have other indexes, but if you do, rebuilding a
clustered index can cause the non-clustered indexes to rebuild as well,
although using the DROP_EXISTING should deal with this somewhat. Another
thing to remember is that the clustered keys are a part of all
non-clustered indexes. A large clustered index key like yours can cause
the non-clustered index to bulk up.
I assume you are using a 90 Fill Factor because you have a lot of
inserts that occur over the course of a week. Leaving 10% of each page
free is a lot of available space and makes the table that much larger.
It does give you some room for growth before page splits start to occur,
but you might consider lowering the free space if your inserting reach
high levels. Are you really adding 10% to the table each week? If not,
consider leaving less space.
What types of queries are you running on this table? Do the clustered
keys get updated? Do you have other indexes? Have you considering using
the clustered index for something else? It would help to understand your
table and its use a little more.
David Gugick
Imceda Software
www.imceda.com|||I don't know what values you are putting into name and date columns. If date
values are subsequent then making date first key in clustered index would
decrease fragmentation.
Thank you,
Alex
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:09bf01c4f1c0$c086a180$a501280a@.phx.gbl...
> SQL 7.0
> There's a table with 100M records (and growing) that's
> needing to be reindexed every week. If I don't recreate
> the index, the fragmentation causes performance of the
> queries to get slower and slower. I have a job that runs
> the following script, but it's taking a little longer
> each week and I'm wondering if there's a way to speed it
> up...
> Any help appreciated.
> Thx,
> Don
> job script:
> CREATE UNIQUE CLUSTERED
> INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> table structure:
> CREATE TABLE [dbo].[DATA] (
> [Name] [varchar] (32) NOT NULL ,
> [Date] [smalldatetime] NOT NULL ,
> [TD] [smalldatetime] NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [SP] [decimal](18, 6) NULL ,
> [SD] [smalldatetime] NULL ,
> [DV] [int] NOT NULL ,
> [DI] [int] NOT NULL ,
> [Fg] [int] NULL
> ) ON [PRIMARY]
> GO
>|||"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10tjnve4gf28cd7@.corp.supernews.com...

> I don't know what values you are putting into name and date columns. If
date
> values are subsequent then making date first key in clustered index would
> decrease fragmentation.
subsequent = sequential, of course|||Thank you Mark, that's what I meant, sorry for confusion, English is not my
first language.
Thank you,
Alex
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:NtWdnTDXGpYUeUTcRVn-qw@.sti.net...
> "Alex" <alex_removethis_@.healthmetrx.com> wrote in message
> news:10tjnve4gf28cd7@.corp.supernews.com...
>
> date
would[vbcol=seagreen]
> subsequent = sequential, of course
>|||On Mon, 3 Jan 2005 10:19:23 -0800, "Don"
<anonymous@.discussions.microsoft.com> wrote:
>SQL 7.0
>There's a table with 100M records (and growing) that's
>needing to be reindexed every week. If I don't recreate
>the index, the fragmentation causes performance of the
>queries to get slower and slower. I have a job that runs
>the following script, but it's taking a little longer
>each week and I'm wondering if there's a way to speed it
>up...
Are these SELECT queries or INSERT, UPDATE, or DELETE, and whichever,
do they reference the clustered index?
If the file grows each week then yes, it is going to take a little
longer every week.
Maybe you should look into partitioned tables?
J.|||You should really consider moving non-current data to a DSS or Warehouse
structure. I suspect that it is not fragmentation that is your problem as
much as it is the statistics. As the base number of records increases, the
more new records require to be inserted or modified before the AUTOSTATS
kicks in. You might just try updating the statistics more frequently and
rely less on the index rebuild operations.
Sincerely,
Anthony Thomas
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:09bf01c4f1c0$c086a180$a501280a@.phx.gbl...
SQL 7.0
There's a table with 100M records (and growing) that's
needing to be reindexed every week. If I don't recreate
the index, the fragmentation causes performance of the
queries to get slower and slower. I have a job that runs
the following script, but it's taking a little longer
each week and I'm wondering if there's a way to speed it
up...
Any help appreciated.
Thx,
Don
job script:
CREATE UNIQUE CLUSTERED
INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
table structure:
CREATE TABLE [dbo].[DATA] (
[Name] [varchar] (32) NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[TD] [smalldatetime] NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[SP] [decimal](18, 6) NULL ,
[SD] [smalldatetime] NULL ,
[DV] [int] NOT NULL ,
[DI] [int] NOT NULL ,
[Fg] [int] NULL
) ON [PRIMARY]
GOsqlsql

Cluster indexing time increasing each week

SQL 7.0
There's a table with 100M records (and growing) that's
needing to be reindexed every week. If I don't recreate
the index, the fragmentation causes performance of the
queries to get slower and slower. I have a job that runs
the following script, but it's taking a little longer
each week and I'm wondering if there's a way to speed it
up...
Any help appreciated.
Thx,
Don
job script:
CREATE UNIQUE CLUSTERED
INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
table structure:
CREATE TABLE [dbo].[DATA] (
[Name] [varchar] (32) NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[TD] [smalldatetime] NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[SP] [decimal](18, 6) NULL ,
[SD] [smalldatetime] NULL ,
[DV] [int] NOT NULL ,
[DI] [int] NOT NULL ,
[Fg] [int] NULL
) ON [PRIMARY]
GO
Don wrote:
> SQL 7.0
> There's a table with 100M records (and growing) that's
> needing to be reindexed every week. If I don't recreate
> the index, the fragmentation causes performance of the
> queries to get slower and slower. I have a job that runs
> the following script, but it's taking a little longer
> each week and I'm wondering if there's a way to speed it
> up...
> Any help appreciated.
> Thx,
> Don
> job script:
> CREATE UNIQUE CLUSTERED
> INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> table structure:
> CREATE TABLE [dbo].[DATA] (
> [Name] [varchar] (32) NOT NULL ,
> [Date] [smalldatetime] NOT NULL ,
> [TD] [smalldatetime] NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [SP] [decimal](18, 6) NULL ,
> [SD] [smalldatetime] NULL ,
> [DV] [int] NOT NULL ,
> [DI] [int] NOT NULL ,
> [Fg] [int] NULL
> ) ON [PRIMARY]
> GO
Is there a reason you chose those columns for the clustered index? What
is the PK on the table? Are there other indexes? One reason for
fragmentation has to do with clustered index keys that change. Or it
could be caused by newly inserted keys that require new pages. You could
try using DBCC INDEXDEFRAG (oh, that's SQL 2000 and you are on 7). It is
an online operation and may keep the table from getting too fragmented.
I don't know if you have other indexes, but if you do, rebuilding a
clustered index can cause the non-clustered indexes to rebuild as well,
although using the DROP_EXISTING should deal with this somewhat. Another
thing to remember is that the clustered keys are a part of all
non-clustered indexes. A large clustered index key like yours can cause
the non-clustered index to bulk up.
I assume you are using a 90 Fill Factor because you have a lot of
inserts that occur over the course of a week. Leaving 10% of each page
free is a lot of available space and makes the table that much larger.
It does give you some room for growth before page splits start to occur,
but you might consider lowering the free space if your inserting reach
high levels. Are you really adding 10% to the table each week? If not,
consider leaving less space.
What types of queries are you running on this table? Do the clustered
keys get updated? Do you have other indexes? Have you considering using
the clustered index for something else? It would help to understand your
table and its use a little more.
David Gugick
Imceda Software
www.imceda.com
|||I don't know what values you are putting into name and date columns. If date
values are subsequent then making date first key in clustered index would
decrease fragmentation.
Thank you,
Alex
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:09bf01c4f1c0$c086a180$a501280a@.phx.gbl...
> SQL 7.0
> There's a table with 100M records (and growing) that's
> needing to be reindexed every week. If I don't recreate
> the index, the fragmentation causes performance of the
> queries to get slower and slower. I have a job that runs
> the following script, but it's taking a little longer
> each week and I'm wondering if there's a way to speed it
> up...
> Any help appreciated.
> Thx,
> Don
> job script:
> CREATE UNIQUE CLUSTERED
> INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> table structure:
> CREATE TABLE [dbo].[DATA] (
> [Name] [varchar] (32) NOT NULL ,
> [Date] [smalldatetime] NOT NULL ,
> [TD] [smalldatetime] NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [SP] [decimal](18, 6) NULL ,
> [SD] [smalldatetime] NULL ,
> [DV] [int] NOT NULL ,
> [DI] [int] NOT NULL ,
> [Fg] [int] NULL
> ) ON [PRIMARY]
> GO
>
|||"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10tjnve4gf28cd7@.corp.supernews.com...

> I don't know what values you are putting into name and date columns. If
date
> values are subsequent then making date first key in clustered index would
> decrease fragmentation.
subsequent = sequential, of course
|||Thank you Mark, that's what I meant, sorry for confusion, English is not my
first language.
Thank you,
Alex
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:NtWdnTDXGpYUeUTcRVn-qw@.sti.net...[vbcol=seagreen]
> "Alex" <alex_removethis_@.healthmetrx.com> wrote in message
> news:10tjnve4gf28cd7@.corp.supernews.com...
> date
would
> subsequent = sequential, of course
>
|||On Mon, 3 Jan 2005 10:19:23 -0800, "Don"
<anonymous@.discussions.microsoft.com> wrote:
>SQL 7.0
>There's a table with 100M records (and growing) that's
>needing to be reindexed every week. If I don't recreate
>the index, the fragmentation causes performance of the
>queries to get slower and slower. I have a job that runs
>the following script, but it's taking a little longer
>each week and I'm wondering if there's a way to speed it
>up...
Are these SELECT queries or INSERT, UPDATE, or DELETE, and whichever,
do they reference the clustered index?
If the file grows each week then yes, it is going to take a little
longer every week.
Maybe you should look into partitioned tables?
J.
|||You should really consider moving non-current data to a DSS or Warehouse
structure. I suspect that it is not fragmentation that is your problem as
much as it is the statistics. As the base number of records increases, the
more new records require to be inserted or modified before the AUTOSTATS
kicks in. You might just try updating the statistics more frequently and
rely less on the index rebuild operations.
Sincerely,
Anthony Thomas

"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:09bf01c4f1c0$c086a180$a501280a@.phx.gbl...
SQL 7.0
There's a table with 100M records (and growing) that's
needing to be reindexed every week. If I don't recreate
the index, the fragmentation causes performance of the
queries to get slower and slower. I have a job that runs
the following script, but it's taking a little longer
each week and I'm wondering if there's a way to speed it
up...
Any help appreciated.
Thx,
Don
job script:
CREATE UNIQUE CLUSTERED
INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
table structure:
CREATE TABLE [dbo].[DATA] (
[Name] [varchar] (32) NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[TD] [smalldatetime] NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[SP] [decimal](18, 6) NULL ,
[SD] [smalldatetime] NULL ,
[DV] [int] NOT NULL ,
[DI] [int] NOT NULL ,
[Fg] [int] NULL
) ON [PRIMARY]
GO

Cluster index on 2 columns order

I have 2 columns in a table namely ColA and ColB.all DML operations are through views n every view has

Where clause i.e where ColA=”” with check option .

All most all my DML queries are using where clause on ColB

Where ColB=””

Now my question is I have a clusted index on both ColA and ColB.in which order I have to create cluster index .

i.e ColA ASC,ColB ASC or ColB ASC,ColA ASC.

Is there any performance gain we can achieve with their order

The only way to find out for sure is to try it both ways. It also depends on which queries or SP's are run most frequently.

Generally speaking, you want the most selective column to be first in the clustered index.

I would turn on SET STATISTICS IO ON, and turn on your Graphical Execution plan, and then run some of your most frequently executed queries and see which version of the clustered index gives you the best results.

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> > Hi,
> >
> > I read in one sql document tht--If the table have a clustered index, the
> > bookmarks of all non-clustered indexes will have clustering keys for each
> > row, and physically moving a row on disk would of course not have any
> > effect
> > on these.
> >
> > My question is wht is getting stored in clustered index keys which is
> > independent of data row location(as it can be inferred from above tht it
> > doesnt affect non clus. index bookmarks).
> >
> > Thanks in advance.
> > Manu Jaidka
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>> Manu,
>> What is stored in the non-clustered key is the value of the associated
>> clustered key.
>> Therefore, searching a non-clustered index results in the clustered index
>> key, after which the clustered index is searched to find the row.
>> One side effect of this is that the size of the non-clustered index is
>> affected by the size of the clustered index, so keeping the cluster as
>> small
>> as is reasonable is a good idea.
>> RLF
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>> > Hi,
>> >
>> > I read in one sql document tht--If the table have a clustered index,
>> > the
>> > bookmarks of all non-clustered indexes will have clustering keys for
>> > each
>> > row, and physically moving a row on disk would of course not have any
>> > effect
>> > on these.
>> >
>> > My question is wht is getting stored in clustered index keys which is
>> > independent of data row location(as it can be inferred from above tht
>> > it
>> > doesnt affect non clus. index bookmarks).
>> >
>> > Thanks in advance.
>> > Manu Jaidka
>>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:

> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as sma
ll
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu Jaidka
Manu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:

> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>
|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David

Clueless client who wants to create a table using reserved words

Hello all,
I have a database that supports two different applications.
For arguments sake I'll call them "intelligent app" and "clueless app"

The client that provided me the schema for the clueless app used reserved words as field names.
The words used are:
value, state, and time

I explained to the client that modifying them could prevent current/future issues.

The client is an "oracle" db (and in my mind should know better but ....) and responded back to me with the following:

BEGIN QUOTE
They are not currently reserved and there is no guarantee they will ever be reserved. In fact, the SQL server line eliminates as many words from the reserved list as they add with new releases. There are other words on the
possible future reserved keyword list that I do not wish to avoid either unless forced to some day, like depth, size, class, zone, level and others.
END QUOTE

Now for the assistance request, where can I find the documented ramifications of using reserved words?
I want to have my documentation (and my ducks lined up) when this clients portion of the app fails.

Any and all assistance is greatly appreciated.

T. Mullins'splain dat one again... Whyfor you have to be prepared when their Data Modeler's choices hit the fan?

SQL Server will gleefully tolerate them, as long as they are properly quoted using [], so what the heck do you care? The developers may revolt when their tools act up, but those column names aren't your choices, so they aren't your problems as far as I can see.

-PatP|||Do a search for "Reserved Keywords" in Books OnLine, and you should be presented with the current list of reserved words. One caution, I did come up with tw lists, one for C++ and one for T-SQL.

A quote from this article reads:
Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only using delimited identifiers.

This is pretty much what Pat has said in his post, though.|||Gracias guys !
I knew of the BOL & MSDN info. I was hoping to have examples of apps that have crashed and burned due to poor design. :)
I think I'll wait in the tall grass ......
Regards to all.
tam

Originally posted by MCrowley
Do a search for "Reserved Keywords" in Books OnLine, and you should be presented with the current list of reserved words. One caution, I did come up with tw lists, one for C++ and one for T-SQL.

A quote from this article reads:
Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only using delimited identifiers.

This is pretty much what Pat has said in his post, though.|||Oh, there are fabulous stories that go from the 1970s into the 1990s, but they peter out pretty quickly there. By that point in time, nearly everybody in the compiler/database/toolset community had abandoned the L-R grammer parsers for yacc or its equivalent, which allowed them to quickly and easily make it possible to make a product's entire grammer "quote sensistive". That change allowed the product vendors to make it possible for the user to "un-reserve" words that were otherwise reserved by quoting them somehow.

That change, combined with wider use of code-generators (sometimes by different names, but code generators nonetheless) made it relatively easy to "dodge the bullet" of reserved words.

It can still be a pain in the posterior, but it is no longer a case of Mohammed and the Mountian. Now the developer can work around the reserved-word problem if they choose to do so.

-PatP

ClrTrigger.HelpMe("Get Table Name?", Please)

So, I've read some past articles that indciate there's no easy way of knowin
g
within a CLR trigger what table the trigger is acting against?!
I read there are 2 possible solutions:
1) Add a String argument to the CLR Trigger to pass the table name. Then
Create a user defined function for each table that will have this trigger
registered on.
or
2) Use the dm_tran_locks to retrieve the name of the table
Is this still the case?Hi Trex,
Welcome the MSDN newsgroup.
As for the SQL CLR managed trigger, actually it is designed for binding to
a certain target table. that's why at runtime the SQLTriggerContext class
dosn't contain any database/tablename info...
Also, is the article you mentioned the below one?
#SQL Server 2005: CLR Integration
http://blogs.msdn.com/sqlclr/archiv.../21/495438.aspx
I think the method mentioned there such as using an additional string input
parameter to supply table name may be a workaround if you do need to get
the bound table's name in the CLR trigger's code.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Yes, that's the one I had read. I guess I still don't understand why but
that's not as important to me now. So, can the trigger define a parameter?
Create Trigger EnforceInheritanceTriggerOnCar on [Foo].[Car]
For INSERT as External Name
CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
GO
Or do have have to convert my CLRTrigger to a CLRFunction with a parameter
then have the SQL trigger simply call it?
Thanks for your reply!!
"Steven Cheng[MSFT]" wrote:

> Hi Trex,
> Welcome the MSDN newsgroup.
> As for the SQL CLR managed trigger, actually it is designed for binding to
> a certain target table. that's why at runtime the SQLTriggerContext class
> dosn't contain any database/tablename info...
> Also, is the article you mentioned the below one?
> #SQL Server 2005: CLR Integration
> http://blogs.msdn.com/sqlclr/archiv.../21/495438.aspx
> I think the method mentioned there such as using an additional string inpu
t
> parameter to supply table name may be a workaround if you do need to get
> the bound table's name in the CLR trigger's code.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your response.
Normal SQL native trigger also dosn't provide parameters. So I think we
still have to create a CLR function (have a parameter) and let our SQL CLR
trigger call that function and pass a fixed name when attatched to a
certain table.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

CLR TVF Error: Msg 6260, Level 16, State 1, Line 1

Dear All, I always got this error in CLR TVF:

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed.
System.InvalidOperationException:
at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount()
at System.Data.Common.DbEnumerator.BuildSchemaInfo()
at System.Data.Common.DbEnumerator.MoveNext()

Here is my code:

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;

public static class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "rowfiller",DataAccess=DataAccessKind.Read,TableDefinition = "ActID int, ActName nvarchar(50), ActCreatorID int,ActDesp nvarchar(200),ActCreateDate datetime,ActModifyDate datetime, ActStartDate datetime, ActEndDate datetime, Status int, Cost int")]
public static IEnumerable Func_GetSchCatActivityIDTable(int CatActivityID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
string sqlstring = "select * from Activity where CatActivityID=@.CatActivityID;";

connection.Open();
SqlCommand command = new SqlCommand(sqlstring, connection);
command.Parameters.AddWithValue("@.CatActivityID", CatActivityID);

return command.ExecuteReader(CommandBehavior.CloseConnection);

}
}

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft Performance","CA1811:AvoidUncalledPrivateCode")]
public static void rowfiller(Object obj,
out SqlInt32 ActID,
out SqlString ActName,
out SqlInt32 ActCreatorID,
out SqlString ActDesp,
out SqlDateTime ActCreateDate,
out SqlDateTime ActModifyDate,
out SqlDateTime ActStartDate,
out SqlDateTime ActEndDate,
out SqlInt32 Status,
out SqlInt32 Cost,
)
{

SqlDataRecord row = (SqlDataRecord)obj;
int column = 0;

ActID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreatorID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActDesp = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreateDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActModifyDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActStartDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActEndDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
Status = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
Cost = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
}

};

Can anyone tell me what I am doing wrong? Many thanks

.

You can not do data access from a Data Reader in a CLR TVF. Basically, after accessing the first row in the reader the data reader closes down.

Niels
|||Just a correction, as soon as you leave the TVF method the datareader closes down - you won't be able to get even the first row.

Niels
|||

Hi, Niels

Thanks for your responding. Do you have any idea how to get arround this problem, if I do need a table value by query other tables?

|||Use T-SQL!! T-SQL is much, much better than CLR for in-database data access anyway. From your code above I ca not see any reason per se to use CLR (however, there may be more stuff going on than what you show in your code).

Niels

CLR Triggers and inserted table

I have created a CLR trigger that validates an address that has been enetere
d
into our address database does not already exist and that it meets country
critera for addressing.
I have a RowVersion column (timestamp), I use this to determin if any
changes have been made to a record in the table on update saving me having t
o
write long SELECT statements.
The problem I have is when I update a row in one instance of Manegement
Studio then try to update in another instance no rows are returned in the
inserted table. What I would like to have happen is a check to see if someon
e
else has changed the row and if so fail the trigger. My SQL trigger works
fine and does everything I need except all the complicated validation and is
why I have choosen to write using the CLR.
At present I have converted the following SQL to C#
CREATE TRIGGER [AddressDuplicateTrigger] ON [dbo].[Addresses] INSTEAD OF
INSERT, UPDATE AS
SET NOCOUNT ON;
IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Countries] ON
[inserted].[CountryCode] = [Countries].[CountryCode]
WHERE
NULLIF(LTRIM(RTRIM([Line1])),'') IS NU
LL AND [Line1Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line2])),'') IS NU
LL AND [Line2Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line3])),'') IS NU
LL AND [Line3Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line4])),'') IS NU
LL AND [Line4Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line5])),'') IS NU
LL AND [Line5Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line6])),'') IS NU
LL AND [Line6Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line7])),'') IS NU
LL AND [Line7Required] = 1
OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NULL AND [PostalCodeRequired] = 1
OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeRegExpression] IS NOT NULL AND [PostalCode] NOT
LIKE [PostalCodeRegExpression] OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeMinimumCharacters]
<> 0 AND LEN([PostalCode]) <
[PostalCodeMinimumCharacters] OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeMaximumCharacters]
<> 0 AND LEN([PostalCode]) >
[PostalCodeMaximumCharacters])
RAISERROR('Address does not meet the Countries address criteria.',16,1)
ELSE IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Addresses] ON
([inserted].[CountryCode] = [Addresses].[CountryCode] OR [inserted].[CountryCode] IS
NULL AND [Addresses].[CountryCode] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') = [Addresses]. [BuildingName] OR NULLIF(LTRIM(RTRIM([i
nserted].[BuildingName])),'') IS
NULL AND [Addresses].[BuildingName] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') = [Addresses]. [BuildingNumber] OR NULLIF(LTRIM(RTRIM([
inserted].[BuildingNumber])),'') IS
NULL AND [Addresses].[BuildingNumber] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') = [Addresses]. [Line1] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine1])),'') IS
NULL AND [Addresses].[Line1] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line2] )),'') = [Addresses]. [Line2] OR NULLIF(LTRIM(RTRIM([insert
ed].[
Line2])),'') IS
NULL AND [Addresses].[Line2] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') = [Addresses]. [Line3] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine3])),'') IS
NULL AND [Addresses].[Line3] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') = [Addresses]. [Line4] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine4])),'') IS
NULL AND [Addresses].[Line4] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') = [Addresses]. [Line5] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine5])),'') IS
NULL AND [Addresses].[Line5] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') = [Addresses]. [Line6] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine6])),'') IS
NULL AND [Addresses].[Line6] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') = [Addresses]. [Line7] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine7])),'') IS
NULL AND [Addresses].[Line7] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') = [Addresses]. [PostalCode] OR NULLIF(LTRIM(RTRIM([ins
erted].[PostalCode])),'') IS
NULL AND [Addresses].[PostalCode] IS NULL))
RAISERROR('Address already exists.',16,1)
ELSE
IF (NOT EXISTS (SELECT NULL FROM [deleted])) -- Insert Address
BEGIN
INSERT INTO Addresses (
[Addresses].[CountryCode],
[Addresses].[BuildingName],
[Addresses].[BuildingNumber],
[Addresses].[Line1],
[Addresses].[Line2],
[Addresses].[Line3],
[Addresses].[Line4],
[Addresses].[Line5],
[Addresses].[Line6],
[Addresses].[Line7],
[Addresses].[PostalCode]
)
SELECT
[inserted].[CountryCode],
NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
FROM [inserted]
END
ELSE -- Update Address
BEGIN
IF EXISTS (
SELECT
NULL
FROM
[inserted] INNER JOIN [Addresses] ON [inserted].[AddressID] =
[Addresses].[AddressID] AND [inserted].[RowVersion] = [Addresses].[RowVersion]
)
UPDATE [Addresses] SET
[Addresses].[CountryCode] = [inserted].[CountryCode],
[Addresses]. [BuildingName] = NULLIF(LTRIM(RTRIM(
[inserted].[BuildingName])),''),
[Addresses]. [BuildingNumber] = NULLIF(LTRIM(RTRIM
([inserted].[BuildingNumber])),''),
[Addresses]. [Line1] = NULLIF(LTRIM(RTRIM([inse
rted].[Line1])),''),
[Addresses]. [Line2] = NULLIF(LTRIM(RTRIM([inse
rted].[Line2])),''),
[Addresses]. [Line3] = NULLIF(LTRIM(RTRIM([inse
rted].[Line3])),''),
[Addresses]. [Line4] = NULLIF(LTRIM(RTRIM([inse
rted].[Line4])),''),
[Addresses]. [Line5] = NULLIF(LTRIM(RTRIM([inse
rted].[Line5])),''),
[Addresses]. [Line6] = NULLIF(LTRIM(RTRIM([inse
rted].[Line6])),''),
[Addresses]. [Line7] = NULLIF(LTRIM(RTRIM([inse
rted].[Line7])),''),
[Addresses]. [PostalCode] = NULLIF(LTRIM(RTRIM([i
nserted].[PostalCode])),'')
FROM [inserted] WHERE [inserted].[AddressID] = [Addresses].[AddressID]
ELSE
RAISERROR('Address has since been modified.',16,1)
END
My tables are as follows:
CREATE TABLE [dbo].[Addresses](
[AddressID] [numeric](8, 0) IDENTITY(10000001,1) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[CountryCode] [smallint] NOT NULL CONSTRAINT
[DF_Entities_Addresses_CountryCode] DEFAULT ((44)),
[BuildingName] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[BuildingNumber] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
[Line1] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line2] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line3] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line4] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line5] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line6] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line7] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[PostalCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Entities_Addresses] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_Addresses] UNIQUE NONCLUSTERED
(
[RowVersion] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Eddie]
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
[FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([CountryCode])
CREATE TABLE [dbo].[Countries](
[CountryCode] [smallint] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Line1Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line1ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line1Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine1Required] DEFAULT ((0)),
[Line1ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line1ForceUpper]
DEFAULT ((0)),
[Line1Visible] AS (CONVERT([bit],case when [Line1Label] IS NULL then (0)
else (1) end,(0))),
[Line2Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line2ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line2Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine2Required] DEFAULT ((0)),
[Line2ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line2ForceUppser]
DEFAULT ((0)),
[Line2Visible] AS (CONVERT([bit],case when [Line2Label] IS NULL then (0)
else (1) end,(0))),
[Line3Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line3ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line3Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine3Required] DEFAULT ((0)),
[Line3ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line3ForceUpper]
DEFAULT ((0)),
[Line3Visible] AS (CONVERT([bit],case when [Line3Label] IS NULL then (0)
else (1) end,(0))),
[Line4Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line4ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line4Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine4Required] DEFAULT ((0)),
[Line4ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line4ForceUpper]
DEFAULT ((0)),
[Line4Visible] AS (CONVERT([bit],case when [Line4Label] IS NULL then (0)
else (1) end,(0))),
[Line5Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line5ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line5Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine5Required] DEFAULT ((0)),
[Line5ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line5ForceUpper]
DEFAULT ((0)),
[Line5Visible] AS (CONVERT([bit],case when [Line5Label] IS NULL then (0)
else (1) end,(0))),
[Line6Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line6ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line6Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine6Required] DEFAULT ((0)),
[Line6ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line6ForceUpper]
DEFAULT ((0)),
[Line6Visible] AS (CONVERT([bit],case when [Line6Label] IS NULL then (0)
else (1) end,(0))),
[Line7Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line7ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line7Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine7Required] DEFAULT ((0)),
[Line7ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line7ForceUpper]
DEFAULT ((0)),
[Line7Visible] AS (CONVERT([bit],case when [Line7Label] IS NULL then (0)
else (1) end,(0))),
[PostalCodeLabel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeRequired] [bit] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeRequired] DEFAULT ((1)),
[PostalCodeForceUpper] [bit] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeForceUpper] DEFAULT ((1)),
[PostalCodeVisible] AS (CONVERT([bit],case when [PostalCodeLabel] IS NULL
then (0) else (1) end,(0))),
[PostalCodeRegExpression] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeMinimumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMinimumCharacter
s] DEFAULT ((0)),
[PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMaximumCharacter
s] DEFAULT ((0)),
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
(
[CountryCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Eddie]
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
[FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([CountryCode])
My CLR is:
using System;
using System.ComponentModel;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
namespace CoullByte.Eddie.SQL.Address.Triggers
{
/// <summary>
/// Address Trigger
/// </summary>
public class Address
{
/// <summary>
/// Prevents Duplicate and Validates the information being stored
/// </summary>
[SqlTrigger(Name = @." Eddie_v6_Address_Duplicate_Validate_Trig
ger", Target
= "[dbo].[Addresses]", Event = "INSTEAD OF INSERT, UPDATE")]
public static void DuplicateValidate()
{
if (!SqlContext.IsAvailable)
{
throw (new NotSupportedException());
}
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
SqlCommand command;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
#region Address Declaration
decimal AddressID;
byte[] RowVersion;
short CountryCode;
object BuildingName;
object BuildingNumber;
object Line1;
object Line2;
object Line3;
object Line4;
object Line5;
object Line6;
object Line7;
object PostalCode;
#endregion
#region Country Validation Declaration
bool Line1Required;
bool Line1Visible;
bool Line2Required;
bool Line2Visible;
bool Line3Required;
bool Line3Visible;
bool Line4Required;
bool Line4Visible;
bool Line5Required;
bool Line5Visible;
bool Line6Required;
bool Line6Visible;
bool Line7Required;
bool Line7Visible;
bool PostalCodeRequired;
bool PostalCodeVisible;
object PostalCodeRegExpression;
int PostalCodeMinimumCharacters;
int PostalCodeMaximumCharacters;
#endregion
try
{
switch (triggerContext.TriggerAction)
{
case TriggerAction.Insert:
break;
case TriggerAction.Update:
using (SqlConnection connection = new SqlConnection(@."context
connection=true"))
{
connection.Open();
#region Read Address
command = connection.CreateCommand();
command.CommandText = @."SELECT AddressID, RowVersion, CountryCode,
BuildingName, BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6,
Line7, PostalCode FROM [inserted];";
reader = command.ExecuteReader();
if (!reader.HasRows)
{
throw (new ApplicationException("No Rows"));
}
reader.Read();
if (reader == null)
{
throw (new ApplicationException("Reader"));
}
AddressID = (decimal)reader["AddressID"];
// RowVersion = (byte[])reader["RowVersion"];
CountryCode = (short)reader["CountryCode"];
BuildingName = reader["BuildingName"];
BuildingNumber = reader["BuildingNumber"];
Line1 = reader["Line1"];
Line2 = reader["Line2"];
Line3 = reader["Line3"];
Line4 = reader["Line4"];
Line5 = reader["Line5"];
Line6 = reader["Line6"];
Line7 = reader["Line7"];
PostalCode = reader["PostalCode"];
reader.Close();
#endregion
#region Read Country Validation
command = connection.CreateCommand();
command.CommandText =
@." Eddie_v6_Country_Select_Validation_By_Co
untryCode";
command.Parameters.Add("@.CountryCode", SqlDbType.SmallInt, 0);
command.CommandType = CommandType.StoredProcedure;
command.Parameters["@.CountryCode"].Value = CountryCode;
reader = command.ExecuteReader();
reader.Read();
Line1Required = (bool)reader["Line1Required"];
Line1Visible = (bool)reader["Line1Visible"];
Line2Required = (bool)reader["Line2Required"];
Line2Visible = (bool)reader["Line2Visible"];
Line3Required = (bool)reader["Line3Required"];
Line3Visible = (bool)reader["Line3Visible"];
Line4Required = (bool)reader["Line4Required"];
Line4Visible = (bool)reader["Line4Visible"];
Line5Required = (bool)reader["Line5Required"];
Line5Visible = (bool)reader["Line5Visible"];
Line6Required = (bool)reader["Line6Required"];
Line6Visible = (bool)reader["Line6Visible"];
Line7Required = (bool)reader["Line7Required"];
Line7Visible = (bool)reader["Line7Visible"];
PostalCodeRequired = (bool)reader["PostalCodeRequired"];
PostalCodeVisible = (bool)reader["PostalCodeVisible"];
PostalCodeRegExpression = reader["PostalCodeRegExpression"];
PostalCodeMinimumCharacters =
(int)reader["PostalCodeMinimumCharacters"];
PostalCodeMaximumCharacters =
(int)reader["PostalCodeMaximumCharacters"];
reader.Close();
#endregion
#region Validate Address
bool addressIsValid =
CoullByte.Eddie.SQL.Address.Functions.Validation.AddressIsValid(BuildingName
,
BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6, Line7, PostalCode,
Line1Required, Line1Visible, Line2Required, Line2Visible, Line3Required,
Line3Visible, Line4Required, Line4Visible, Line5Required, Line5Visible,
Line6Required, Line6Visible, Line7Required, Line7Visible, PostalCodeRequired
,
PostalCodeVisible, PostalCodeRegExpression, PostalCodeMinimumCharacters,
PostalCodeMaximumCharacters);
#endregion
command = connection.CreateCommand();
command.CommandText = @."SELECT COUNT(*) FROM [inserted] INNER JOIN
[Addresses] ON [inserted].[AddressID] = [Addresses].[AddressID] AND
[inserted].[RowVersion] = [Addresses].[RowVersion]";
command.CommandType = CommandType.Text;
int addressUnchanged = (int)command.ExecuteScalar();
if (addressUnchanged != 1)
{
throw (new ApplicationException("The address has since been changed
by someone else."));
}
if (!addressIsValid)
{
throw (new ApplicationException("The updated changes made do not
meet the countries address criteria."));
}
command = connection.CreateCommand();
command.CommandText = @."UPDATE [Addresses] SET
[Addresses].[CountryCode] = [inserted].[CountryCode],
[Addresses].[BuildingName] =
NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''), [Addresses].[BuildingNumber]
= NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''), [Addresses].[Line1] =
NULLIF(LTRIM(RTRIM([inserted].[Line1])),''), [Addresses].[Line2] =
NULLIF(LTRIM(RTRIM([inserted].[Line2])),''), [Addresses].[Line3] =
NULLIF(LTRIM(RTRIM([inserted].[Line3])),''), [Addresses].[Line4] =
NULLIF(LTRIM(RTRIM([inserted].[Line4])),''), [Addresses].[Line5] =
NULLIF(LTRIM(RTRIM([inserted].[Line5])),''), [Addresses].[Line6] =
NULLIF(LTRIM(RTRIM([inserted].[Line6])),''), [Addresses].[Line7] =
NULLIF(LTRIM(RTRIM([inserted].[Line7])),''), [Addresses].[PostalCode] =
NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') FROM [inserted] WHERE
[inserted].[AddressID] = [Addresses].[AddressID]";
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
}
break;
}
}
catch (System.Data.SqlClient.SqlException sqlExp)
{
throw (sqlExp);
}
catch (System.NullReferenceException nullRef)
{
throw (new ApplicationException(nullRef.StackTrace));
}
catch (System.Exception exp)
{
throw (exp);
}
}
}
}Hi Paul,
What complicated validation? The IS NULL stuff etc... should be done in SQL
too.
Only use CLR if you are going to do something that cannot be better done
using set based methods, such examples would be if you had a post code look
up software to validate the post code exists, you'd probably do that using a
CLR UDF in a CHECK constraint.
Ditch the C# and stick with the SQL trigger - seriously!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:D04F7671-BB3B-42BB-A3C2-0ADC8810B708@.microsoft.com...
>I have created a CLR trigger that validates an address that has been
>enetered
> into our address database does not already exist and that it meets country
> critera for addressing.
> I have a RowVersion column (timestamp), I use this to determin if any
> changes have been made to a record in the table on update saving me having
> to
> write long SELECT statements.
> The problem I have is when I update a row in one instance of Manegement
> Studio then try to update in another instance no rows are returned in the
> inserted table. What I would like to have happen is a check to see if
> someone
> else has changed the row and if so fail the trigger. My SQL trigger works
> fine and does everything I need except all the complicated validation and
> is
> why I have choosen to write using the CLR.
> At present I have converted the following SQL to C#
> CREATE TRIGGER [AddressDuplicateTrigger] ON [dbo].[Addresses] INSTEAD OF
> INSERT, UPDATE AS
> SET NOCOUNT ON;
> IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Countries] ON
> [inserted].[CountryCode] = [Countries].[CountryCode]
> WHERE
> NULLIF(LTRIM(RTRIM([Line1])),'') IS NULL AND [Line1Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line2])),'') IS NULL AND [Line2Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line3])),'') IS NULL AND [Line3Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line4])),'') IS NULL AND [Line4Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line5])),'') IS NULL AND [Line5Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line6])),'') IS NULL AND [Line6Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line7])),'') IS NULL AND [Line7Required] = 1 OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NULL AND [PostalCodeRequired] = 1
> OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeRegExpression] IS NOT NULL AND [PostalCode] NOT
> LIKE [PostalCodeRegExpression] OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeMinimumCharacters] <> 0 AND LEN([PostalCode]) <
> [PostalCodeMinimumCharacters] OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeMaximumCharacters] <> 0 AND LEN([PostalCode]) >
> [PostalCodeMaximumCharacters])
> RAISERROR('Address does not meet the Countries address criteria.',16,1)
> ELSE IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Addresses] ON
> ([inserted].[CountryCode] = [Addresses].[CountryCode] OR
> [inserted].[CountryCode] IS
> NULL AND [Addresses].[CountryCode] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') =
> [Addresses].[BuildingName] OR
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') IS
> NULL AND [Addresses].[BuildingName] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') =
> [Addresses].[BuildingNumber] OR
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') IS
> NULL AND [Addresses].[BuildingNumber] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') = [Addresses].[Line1] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') IS
> NULL AND [Addresses].[Line1] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line2] )),'') = [Addresses].[Line2] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),'') IS
> NULL AND [Addresses].[Line2] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') = [Addresses].[Line3] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') IS
> NULL AND [Addresses].[Line3] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') = [Addresses].[Line4] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') IS
> NULL AND [Addresses].[Line4] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') = [Addresses].[Line5] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') IS
> NULL AND [Addresses].[Line5] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') = [Addresses].[Line6] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') IS
> NULL AND [Addresses].[Line6] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') = [Addresses].[Line7] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') IS
> NULL AND [Addresses].[Line7] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') =
> [Addresses].[PostalCode] OR
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') IS
> NULL AND [Addresses].[PostalCode] IS NULL))
> RAISERROR('Address already exists.',16,1)
> ELSE
> IF (NOT EXISTS (SELECT NULL FROM [deleted])) -- Insert Address
> BEGIN
> INSERT INTO Addresses (
> [Addresses].[CountryCode],
> [Addresses].[BuildingName],
> [Addresses].[BuildingNumber],
> [Addresses].[Line1],
> [Addresses].[Line2],
> [Addresses].[Line3],
> [Addresses].[Line4],
> [Addresses].[Line5],
> [Addresses].[Line6],
> [Addresses].[Line7],
> [Addresses].[PostalCode]
> )
> SELECT
> [inserted].[CountryCode],
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
> FROM [inserted]
> END
> ELSE -- Update Address
> BEGIN
> IF EXISTS (
> SELECT
> NULL
> FROM
> [inserted] INNER JOIN [Addresses] ON [inserted].[AddressID] =
> [Addresses].[AddressID] AND [inserted].[RowVersion] =
> [Addresses].[RowVersion]
> )
> UPDATE [Addresses] SET
> [Addresses].[CountryCode] = [inserted].[CountryCode],
> [Addresses].[BuildingName] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> [Addresses].[BuildingNumber] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> [Addresses].[Line1] = NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
> [Addresses].[Line2] = NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
> [Addresses].[Line3] = NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
> [Addresses].[Line4] = NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
> [Addresses].[Line5] = NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
> [Addresses].[Line6] = NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
> [Addresses].[Line7] = NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
> [Addresses].[PostalCode] =
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
> FROM [inserted] WHERE [inserted].[AddressID] = [Addresses].[AddressID]
> ELSE
> RAISERROR('Address has since been modified.',16,1)
> END
> My tables are as follows:
> CREATE TABLE [dbo].[Addresses](
> [AddressID] [numeric](8, 0) IDENTITY(10000001,1) NOT NULL,
> [RowVersion] [timestamp] NOT NULL,
> [CountryCode] [smallint] NOT NULL CONSTRAINT
> [DF_Entities_Addresses_CountryCode] DEFAULT ((44)),
> [BuildingName] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [BuildingNumber] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
> [Line1] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line2] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line3] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line4] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line5] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line6] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line7] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [PostalCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> CONSTRAINT [PK_Entities_Addresses] PRIMARY KEY CLUSTERED
> (
> [AddressID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
> CONSTRAINT [IX_Addresses] UNIQUE NONCLUSTERED
> (
> [RowVersion] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> USE [Eddie]
> GO
> ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
> [FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
> REFERENCES [dbo].[Countries] ([CountryCode])
> CREATE TABLE [dbo].[Countries](
> [CountryCode] [smallint] NOT NULL,
> [RowVersion] [timestamp] NOT NULL,
> [Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
> [Line1Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line1ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line1Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine1Required] DEFAULT ((0)),
> [Line1ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line1ForceUpper]
> DEFAULT ((0)),
> [Line1Visible] AS (CONVERT([bit],case when [Line1Label] IS NULL then (0)
> else (1) end,(0))),
> [Line2Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line2ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line2Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine2Required] DEFAULT ((0)),
> [Line2ForceUpper] [bit] NOT NULL CONSTRAINT
> [DF_Countries_Line2ForceUppser]
> DEFAULT ((0)),
> [Line2Visible] AS (CONVERT([bit],case when [Line2Label] IS NULL then (0)
> else (1) end,(0))),
> [Line3Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line3ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line3Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine3Required] DEFAULT ((0)),
> [Line3ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line3ForceUpper]
> DEFAULT ((0)),
> [Line3Visible] AS (CONVERT([bit],case when [Line3Label] IS NULL then (0)
> else (1) end,(0))),
> [Line4Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line4ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line4Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine4Required] DEFAULT ((0)),
> [Line4ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line4ForceUpper]
> DEFAULT ((0)),
> [Line4Visible] AS (CONVERT([bit],case when [Line4Label] IS NULL then (0)
> else (1) end,(0))),
> [Line5Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line5ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line5Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine5Required] DEFAULT ((0)),
> [Line5ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line5ForceUpper]
> DEFAULT ((0)),
> [Line5Visible] AS (CONVERT([bit],case when [Line5Label] IS NULL then (0)
> else (1) end,(0))),
> [Line6Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line6ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line6Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine6Required] DEFAULT ((0)),
> [Line6ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line6ForceUpper]
> DEFAULT ((0)),
> [Line6Visible] AS (CONVERT([bit],case when [Line6Label] IS NULL then (0)
> else (1) end,(0))),
> [Line7Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line7ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line7Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine7Required] DEFAULT ((0)),
> [Line7ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line7ForceUpper]
> DEFAULT ((0)),
> [Line7Visible] AS (CONVERT([bit],case when [Line7Label] IS NULL then (0)
> else (1) end,(0))),
> [PostalCodeLabel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [PostalCodeToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [PostalCodeRequired] [bit] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeRequired] DEFAULT ((1)),
> [PostalCodeForceUpper] [bit] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeForceUpper] DEFAULT ((1)),
> [PostalCodeVisible] AS (CONVERT([bit],case when [PostalCodeLabel] IS NULL
> then (0) else (1) end,(0))),
> [PostalCodeRegExpression] [nvarchar](255) COLLATE Latin1_General_CI_AS
> NULL,
> [PostalCodeMinimumCharacters] [int] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeMinimumCharacter
s] DEFAULT ((0)),
> [PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeMaximumCharacter
s] DEFAULT ((0)),
> CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
> (
> [CountryCode] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> GO
> USE [Eddie]
> GO
> ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
> [FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
> REFERENCES [dbo].[Countries] ([CountryCode])
> My CLR is:
> using System;
> using System.ComponentModel;
> using System.Collections.Generic;
> using System.Diagnostics;
> using System.Text;
> using System.Data;
> using System.Data.Sql;
> using Microsoft.SqlServer.Server;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Xml;
> namespace CoullByte.Eddie.SQL.Address.Triggers
> {
> /// <summary>
> /// Address Trigger
> /// </summary>
> public class Address
> {
> /// <summary>
> /// Prevents Duplicate and Validates the information being stored
> /// </summary>
> [SqlTrigger(Name = @." Eddie_v6_Address_Duplicate_Validate_Trig
ger", Target
> = "[dbo].[Addresses]", Event = "INSTEAD OF INSERT, UPDATE")]
> public static void DuplicateValidate()
> {
> if (!SqlContext.IsAvailable)
> {
> throw (new NotSupportedException());
> }
>
> SqlTriggerContext triggerContext = SqlContext.TriggerContext;
> SqlCommand command;
> SqlPipe pipe = SqlContext.Pipe;
> SqlDataReader reader;
> #region Address Declaration
> decimal AddressID;
> byte[] RowVersion;
> short CountryCode;
> object BuildingName;
> object BuildingNumber;
> object Line1;
> object Line2;
> object Line3;
> object Line4;
> object Line5;
> object Line6;
> object Line7;
> object PostalCode;
> #endregion
> #region Country Validation Declaration
> bool Line1Required;
> bool Line1Visible;
> bool Line2Required;
> bool Line2Visible;
> bool Line3Required;
> bool Line3Visible;
> bool Line4Required;
> bool Line4Visible;
> bool Line5Required;
> bool Line5Visible;
> bool Line6Required;
> bool Line6Visible;
> bool Line7Required;
> bool Line7Visible;
> bool PostalCodeRequired;
> bool PostalCodeVisible;
> object PostalCodeRegExpression;
> int PostalCodeMinimumCharacters;
> int PostalCodeMaximumCharacters;
> #endregion
> try
> {
> switch (triggerContext.TriggerAction)
> {
> case TriggerAction.Insert:
> break;
> case TriggerAction.Update:
> using (SqlConnection connection = new SqlConnection(@."context
> connection=true"))
> {
> connection.Open();
> #region Read Address
> command = connection.CreateCommand();
> command.CommandText = @."SELECT AddressID, RowVersion, CountryCode,
> BuildingName, BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6,
> Line7, PostalCode FROM [inserted];";
> reader = command.ExecuteReader();
> if (!reader.HasRows)
> {
> throw (new ApplicationException("No Rows"));
> }
> reader.Read();
> if (reader == null)
> {
> throw (new ApplicationException("Reader"));
> }
> AddressID = (decimal)reader["AddressID"];
> // RowVersion = (byte[])reader["RowVersion"];
> CountryCode = (short)reader["CountryCode"];
> BuildingName = reader["BuildingName"];
> BuildingNumber = reader["BuildingNumber"];
> Line1 = reader["Line1"];
> Line2 = reader["Line2"];
> Line3 = reader["Line3"];
> Line4 = reader["Line4"];
> Line5 = reader["Line5"];
> Line6 = reader["Line6"];
> Line7 = reader["Line7"];
> PostalCode = reader["PostalCode"];
> reader.Close();
> #endregion
> #region Read Country Validation
> command = connection.CreateCommand();
> command.CommandText =
> @." Eddie_v6_Country_Select_Validation_By_Co
untryCode";
> command.Parameters.Add("@.CountryCode", SqlDbType.SmallInt, 0);
> command.CommandType = CommandType.StoredProcedure;
> command.Parameters["@.CountryCode"].Value = CountryCode;
> reader = command.ExecuteReader();
> reader.Read();
> Line1Required = (bool)reader["Line1Required"];
> Line1Visible = (bool)reader["Line1Visible"];
> Line2Required = (bool)reader["Line2Required"];
> Line2Visible = (bool)reader["Line2Visible"];
> Line3Required = (bool)reader["Line3Required"];
> Line3Visible = (bool)reader["Line3Visible"];
> Line4Required = (bool)reader["Line4Required"];
> Line4Visible = (bool)reader["Line4Visible"];
> Line5Required = (bool)reader["Line5Required"];
> Line5Visible = (bool)reader["Line5Visible"];
> Line6Required = (bool)reader["Line6Required"];
> Line6Visible = (bool)reader["Line6Visible"];
> Line7Required = (bool)reader["Line7Required"];
> Line7Visible = (bool)reader["Line7Visible"];
> PostalCodeRequired = (bool)reader["PostalCodeRequired"];
> PostalCodeVisible = (bool)reader["PostalCodeVisible"];
> PostalCodeRegExpression = reader["PostalCodeRegExpression"];
> PostalCodeMinimumCharacters =
> (int)reader["PostalCodeMinimumCharacters"];
> PostalCodeMaximumCharacters =
> (int)reader["PostalCodeMaximumCharacters"];
> reader.Close();
> #endregion
> #region Validate Address
> bool addressIsValid =
> CoullByte.Eddie.SQL.Address.Functions.Validation.AddressIsValid(BuildingNa
me,
> BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6, Line7,
> PostalCode,
> Line1Required, Line1Visible, Line2Required, Line2Visible, Line3Required,
> Line3Visible, Line4Required, Line4Visible, Line5Required, Line5Visible,
> Line6Required, Line6Visible, Line7Required, Line7Visible,
> PostalCodeRequired,
> PostalCodeVisible, PostalCodeRegExpression, PostalCodeMinimumCharacters,
> PostalCodeMaximumCharacters);
> #endregion
> command = connection.CreateCommand();
> command.CommandText = @."SELECT COUNT(*) FROM [inserted] INNER JOIN
> [Addresses] ON [inserted].[AddressID] = [Addresses].[AddressID] AND
> [inserted].[RowVersion] = [Addresses].[RowVersion]";
> command.CommandType = CommandType.Text;
> int addressUnchanged = (int)command.ExecuteScalar();
> if (addressUnchanged != 1)
> {
> throw (new ApplicationException("The address has since been changed
> by someone else."));
> }
> if (!addressIsValid)
> {
> throw (new ApplicationException("The updated changes made do not
> meet the countries address criteria."));
> }
> command = connection.CreateCommand();
> command.CommandText = @."UPDATE [Addresses] SET
> [Addresses].[CountryCode] = [inserted].[CountryCode],
> [Addresses].[BuildingName] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> [Addresses].[BuildingNumber]
> = NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> [Addresses].[Line1] =
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),''), [Addresses].[Line2] =
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),''), [Addresses].[Line3] =
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),''), [Addresses].[Line4] =
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),''), [Addresses].[Line5] =
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),''), [Addresses].[Line6] =
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),''), [Addresses].[Line7] =
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),''), [Addresses].[PostalCode] =
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') FROM [inserted] WHERE
> [inserted].[AddressID] = [Addresses].[AddressID]";
> pipe.Send(command.CommandText);
> command.ExecuteNonQuery();
> }
> break;
> }
> }
> catch (System.Data.SqlClient.SqlException sqlExp)
> {
> throw (sqlExp);
> }
> catch (System.NullReferenceException nullRef)
> {
> throw (new ApplicationException(nullRef.StackTrace));
> }
> catch (System.Exception exp)
> {
> throw (exp);
> }
> }
> }
> }
>|||In my orginal post I said "...and that it meets country
critera for addressing...". The CLR is the only way I can validate an
address is enetered that meets the countries addressing criteria. SQL
triggers do not allow me to do this and is why I chose to use c#. All
countries are stored in the Countries table and each country have diffrent
ways of handling addresses such as that between UK and US. US use Zip Code
and UK use Post Code for example and US Zip codes are not the same format as
UK Post Codes. In the address table I have columns such as Building Name,
Building Number, Line 1, Line 2 - Line 7 then Postal Code. When using our ow
n
software changing country code changes labels and validation requirements on
the Windows Form or on the Web Forms. The c# trigger validates based on the
country and forces NULL into the columns where their is empty strings etc.
Hope this help to explain what I am trying to acheive.|||Based on the code snippet you post, you might have some design issues as
well. Can you post the table structures, a few sample data along with the
address validation rules you have?
In general, Tony's last statement in his response would be a suitable
advice.
Anith|||My Address Validation class is as follows, other stuff already posted and
table scripts also posted:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
namespace CoullByte.Eddie.SQL.Address.Functions
{
/// <summary>
/// Address Validation Class
/// </summary>
public class Validation
{
#region SQL Context
/// <summary>
/// Validate if running under SQL
/// </summary>
private static void ValidateSQLContext()
{
if (!SqlContext.IsAvailable)
{
throw (new NotSupportedException());
}
}
#endregion
#region Address Is Valid
/// <summary>
/// Address Data Validation
/// </summary>
/// <param name="BuildingName">Building Name</param>
/// <param name="BuildingNumber">Building Number</param>
/// <param name="Line1">Line 1</param>
/// <param name="Line2">Line 2</param>
/// <param name="Line3">Line 3</param>
/// <param name="Line4">Line 4</param>
/// <param name="Line5">Line 5</param>
/// <param name="Line6">Line 6</param>
/// <param name="Line7">Line 7</param>
/// <param name="PostalCode">Postal Code</param>
/// <param name="Line1Required">Line 1 Required?</param>
/// <param name="Line1Visible">Line 1 Visible to User?</param>
/// <param name="Line2Required">Line 2 Required?</param>
/// <param name="Line2Visible">Line 2 Visible to User?</param>
/// <param name="Line3Required">Line 3 Required?</param>
/// <param name="Line3Visible">Line 3 Visible to User?</param>
/// <param name="Line4Required">Line 4 Required?</param>
/// <param name="Line4Visible">Line 4 Visible to User?</param>
/// <param name="Line5Required">Line 5 Required?</param>
/// <param name="Line5Visible">Line 5 Visible to User?</param>
/// <param name="Line6Required">Line 6 Required?</param>
/// <param name="Line6Visible">Line 6 Visible to User?</param>
/// <param name="Line7Required">Line 7 Required?</param>
/// <param name="Line7Visible">Line 7 Visible to User?</param>
/// <param name="PostalCodeRequired">Postal Code Required?</param>
/// <param name="PostalCodeVisible">Postal Code Visible?</param>
/// <param name="PostalCodeRegExpression">Postal Code Regular
Expression</param>
/// <param name="PostalCodeMinimumCharacters">Postal Code Minimum
Characters</param>
/// <param name="PostalCodeMaximumCharacters">Postal Code Maximum
Characters</param>
/// <returns>Validation Result</returns>
public static bool AddressIsValid(object BuildingName, object
BuildingNumber, object Line1, object Line2, object Line3, object Line4,
object Line5, object Line6, object Line7, object PostalCode, bool
Line1Required, bool Line1Visible, bool Line2Required, bool Line2Visible, boo
l
Line3Required, bool Line3Visible, bool Line4Required, bool Line4Visible, boo
l
Line5Required, bool Line5Visible, bool Line6Required, bool Line6Visible, boo
l
Line7Required, bool Line7Visible, bool PostalCodeRequired, bool
PostalCodeVisible, object PostalCodeRegExpression, int
PostalCodeMinimumCharacters, int PostalCodeMaximumCharacters)
{
return Line1IsValid(Line1Required, Line1Visible, Line1) &&
Line2IsValid(Line2Required, Line2Visible, Line2) &&
Line3IsValid(Line3Required, Line3Visible, Line3) &&
Line4IsValid(Line4Required, Line4Visible, Line4) &&
Line5IsValid(Line5Required, Line5Visible, Line5) &&
Line6IsValid(Line6Required, Line6Visible, Line6) &&
Line7IsValid(Line7Required, Line7Visible, Line7) &&
PostalCodeIsValid(PostalCodeRequired, PostalCodeVisible,
PostalCodeMinimumCharacters, PostalCodeMaximumCharacters,
PostalCodeRegExpression, PostalCode);
}
#endregion
#region Line 1 Data Validation
/// <summary>
/// Line 1 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line1IsValid (bool Line1Required, bool Line1Visible,
object Line1Data)
{
ValidateSQLContext();
if (!Line1Visible)
{
return true;
}
else
{
return ((!Line1Required) || (Line1Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line1Data)));
}
}
#endregion
#region Line 2 Data Validation
/// <summary>
/// Line 2 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line2IsValid(bool Line2Required, bool Line2Visible,
object Line2Data)
{
ValidateSQLContext();
if (!Line2Visible)
{
return true;
}
else
{
return ((!Line2Required) || (Line2Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line2Data)));
}
}
#endregion
#region Line 3 Data Validation
/// <summary>
/// Line 3 Data Validation
/// </summary>
/// <param name="Line3Required">Data Required?</param>
/// <param name="Line3Visible">Visible to User?</param>
/// <param name="Line3Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line3IsValid(bool Line3Required, bool Line3Visible,
object Line3Data)
{
ValidateSQLContext();
if (!Line3Visible)
{
return true;
}
else
{
return ((!Line3Required) || (Line3Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line3Data)));
}
}
#endregion
#region Line 4 Data Validation
/// <summary>
/// Line 4 Data Validation
/// </summary>
/// <param name="Line4Required">Data Required?</param>
/// <param name="Line4Visible">Visible to User?</param>
/// <param name="Line4Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line4IsValid(bool Line4Required, bool Line4Visible,
object Line4Data)
{
ValidateSQLContext();
if (!Line4Visible)
{
return true;
}
else
{
return ((!Line4Required) || (Line4Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line4Data)));
}
}
#endregion
#region Line 5 Data Validation
/// <summary>
/// Line 5 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line5IsValid(bool Line5Required, bool Line5Visible,
object Line5Data)
{
ValidateSQLContext();
if (!Line5Visible)
{
return true;
}
else
{
return ((!Line5Required) || (Line5Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line5Data)));
}
}
#endregion
#region Line 6 Data Validation
/// <summary>
/// Line 6 Data Validation
/// </summary>
/// <param name="Line6Required">Data Required?</param>
/// <param name="Line6Visible">Visible to User?</param>
/// <param name="Line6Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line6IsValid(bool Line6Required, bool Line6Visible,
object Line6Data)
{
if (!Line6Visible)
{
return true;
}
else
{
return ((!Line6Required) || (Line6Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line6Data)));
}
}
#endregion
#region Line 7 Data Validation
/// <summary>
/// Line 7 Data Validation
/// </summary>
/// <param name="Line7Required">Data Required?</param>
/// <param name="Line7Visible">Visible to User?</param>
/// <param name="Line7Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line7IsValid(bool Line7Required, bool Line7Visible,
object Line7Data)
{
if (!Line7Visible)
{
return true;
}
else
{
return ((!Line7Required) || (Line7Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line7Data)));
}
}
#endregion
#region Postal Code Validation
/// <summary>
/// Postal Code Validation
/// </summary>
/// <param name="PostalCodeRequired">Data Required</param>
/// <param name="PostalCodeVisible">Visible to User?</param>
/// <param name="PostalCodeMinimumCharacters">Minimum Characters
Required?</param>
/// <param name="PostalCodeMaximumCharacters">Maximum Characters
Required?</param>
/// <param name="PostalCodeRegExpression">Regular Expression for
Validating Data</param>
/// <param name="PostalCodeData">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool PostalCodeIsValid(bool PostalCodeRequired, bool
PostalCodeVisible, int PostalCodeMinimumCharacters, int
PostalCodeMaximumCharacters, object PostalCodeRegExpression, object
PostalCodeData)
{
string postalCodeData = string.Empty;
string postalCodeRegExpression = string.Empty;
if (PostalCodeRegExpression != null && PostalCodeRegExpression !=
DBNull.Value && PostalCodeRegExpression is string)
{
postalCodeRegExpression = Convert.ToString(PostalCodeRegExpression);
}
if (PostalCodeData != null && PostalCodeData != DBNull.Value &&
PostalCodeData is string)
{
postalCodeData = Convert.ToString(PostalCodeData);
}
bool lengthIsValid =
(
(
PostalCodeMinimumCharacters == 0
) ||
(
PostalCodeMinimumCharacters != 0 &&
postalCodeData.Length >= PostalCodeMinimumCharacters
) &&
(
(
PostalCodeMaximumCharacters == 0
) ||
(
PostalCodeMaximumCharacters != 0 &&
postalCodeData.Length <= PostalCodeMaximumCharacters
)
)
);
bool regExpIsValid =
(
(
postalCodeRegExpression == string.Empty
) ||
(
CoullByte.Eddie.SQL.Functions.Data.IsMeaningless(PostalCodeRegExpression)
) ||
(
postalCodeRegExpression != string.Empty &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(postalCodeRegExpression) &&
System.Text.RegularExpressions.Regex.IsMatch(postalCodeData,
postalCodeRegExpression)
)
);
if (PostalCodeRequired)
{
return (CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(PostalCodeData)
&& lengthIsValid && regExpIsValid);
}
else
{
return (lengthIsValid && regExpIsValid);
}
}
#endregion
}
}
a sample row for the Countries table is as follows:
INSERT INTO [Eddie].[dbo].[Countries]
([CountryCode]
,[Country]
,[Line1Label]
,[Line1ToolTip]
,[Line1Required]
,[Line1ForceUpper]
,[Line2Label]
,[Line2ToolTip]
,[Line2Required]
,[Line2ForceUpper]
,[Line3Label]
,[Line3ToolTip]
,[Line3Required]
,[Line3ForceUpper]
,[Line4Label]
,[Line4ToolTip]
,[Line4Required]
,[Line4ForceUpper]
,[Line5Label]
,[Line5ToolTip]
,[Line5Required]
,[Line5ForceUpper]
,[Line6Label]
,[Line6ToolTip]
,[Line6Required]
,[Line6ForceUpper]
,[Line7Label]
,[Line7ToolTip]
,[Line7Required]
,[Line7ForceUpper]
,[PostalCodeLabel]
,[PostalCodeToolTip]
,[PostalCodeRequired]
,[PostalCodeForceUpper]
,[PostalCodeRegExpression]
,[PostalCodeMinimumCharacters]
,[PostalCodeMaximumCharacters])
VALUES
(44
,'United Kingdom'
,'Thoroughfare'
,NULL
,0
,0
,'Locality'
,NULL
,0
,0
,'Post Town'
,NULL
,1
,1
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,'Post Code'
,NULL
,1
,1
,'(GIR0AA)|((([A-Z-[QVX]][0-9][0-9]?)|(([A-Z-[QVX]][A-Z-[IJZ]][0-9][0-9]?)|(([A-Z-[QVX]][0-9][A-HJKSTUW])|([A-Z-[QVX]][A
-Z-[IJZ]][0-9][ABEHMNPRVWXY]))))
[0-9][A-Z-[CIKMOV]]{2})'
,3
,8)
A sample address for the Addresses table is:
INSERT INTO [Eddie].[dbo].[Addresses]
([CountryCode]
,[BuildingName]
,[BuildingNumber]
,[Line1]
,[Line2]
,[Line3]
,[Line4]
,[Line5]
,[Line6]
,[Line7]
,[PostalCode])
VALUES
(44
,NULL
,NULL
,'Queens Walk'
,NULL
,'READING'
,NULL
,NULL
,NULL
,NULL
,'RG1 7PT')