Replacing CRLF's with <BR> in a view

Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..
I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi
|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:

> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>
sql

Replacing CRLF's with <BR> in a view

Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column
)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:

> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>

Replacing CRLF's with <BR> in a view

Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:
> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>

Replace on a text field.

I need to search for all occurances of particular string within a column on a table. The column has a data type of Text. It will not allow me to use the replace function on a Text field only on varchars or chars. Does anybody have any ideas of how I can do this?njjones,

Look at Full Text Indexing in Books Online (BOL).

If however you can guarantee that none of the fields exceed 8000 characters you could CAST the TEXT field to a VARCHAR(8000). ie.

REPLACE(CAST(yourtextcolumn AS VARCHAR(8000)),'ABC','DEF')

macka.|||I have done that however quite a lot of the fields I need to affect are greater than 8000 characters (hence using the text datatype). I wanted to run a query to find out how many were longer but you can't use Len on a text field either - is there an easy way of finding the character length of text in a text column?|||Chances are not many will be exactly 8000 in length, so the following query gives you a rough idea of how many are bigger than 8k, but truncating all fields to 8000 characters.

SELECT COUNT(*)
FROM yourtable
WHERE LEN((CAST(yourtextcolumn AS VARCHAR(8000)))) = 8000

macka.|||The following works - seems a little heavy handed for a replacement of a one line function, but any:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%25.224.8.30%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%25.224.8.30%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 11
'modconnect1.qinetiq.r.mil.uk'
select @.pos1 =
patindex('%25.224.8.30%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||njjones,

Did you ever get the 'replace' issue resolved in a Text field? I need to do a similar action, finding all the commas in a text field and replacing it with a semi-colon.

Thanks.|||The answer is above, however I have recopied and pasted it below and updated it so that it should work for , and ; - probably could have parameterised this and turned it in a user defined function but it is not something I have needed to do often enough to bother with:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%,%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%,%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 1
';'
select @.pos1 =
patindex('%,%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||Nicky, thanks. A couple of quick modifications and I had this working well for my table. I appreciate it.

RY

Reorganizing/Rebuilding an index seems to have no effect

We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database Architect
Sounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>
|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM
|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect
|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>
|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM
|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM

Reorganizing/Rebuilding an index seems to have no effect

We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM

Reorganizing/Rebuilding an index seems to have no effect

We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM|||If you're doing a defrag , don't forget the statistics update
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OJSaclJ1HHA.1204@.TK2MSFTNGP03.phx.gbl...
> I'm going to re-examine this particular issue after we have updated the
> schema which will be sometime in October.
> Thanks.
> MTM
>

renumber a column...

Hi. How can I renumber a column in my database? This is what we use to identify a particular record:

CASEID PARTYID
11 1
11 2
23 1
23 2
23 3
I want to stop renumering for each entry. How can I update the entire so each PARTYID is renumbered. I want to make this the primary key for the table. Thanks for your help!CREATE TABLE myTable99(CASEID int, PARTYID int)
GO

INSERT myTable99(CASEID, PARTYID)
SELECT 11, 1 UNION ALL
SELECT 11, 2 UNION ALL
SELECT 23, 1 UNION ALL
SELECT 23, 2 UNION ALL
SELECT 23, 3
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD newPARTYID int IDENTITY(1,1) PRIMARY KEY
GO

SELECT * FROM myTable99
GO

EXEC sp_rename 'myTable99.PARTYID', 'PARTYID_Old', 'COLUMN'
EXEC sp_rename 'myTable99.newPARTYID', 'PARTYID', 'COLUMN'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Thanks! Worked like a charm!