Removing a server: rows

Showing posts with label rows. Show all posts

Wednesday, March 21, 2012

Rename Column in Very large table

I would like to rename a column in a table that has over 770 million rows and
around 300-400 gigs of data. I would like to minimize time, resources, and
risk. I am looking for suggestions, risks, or any other gotcha's i may not be
considering.Check out sp_rename in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>I would like to rename a column in a table that has over 770 million rows
>and
> around 300-400 gigs of data. I would like to minimize time, resources, and
> risk. I am looking for suggestions, risks, or any other gotcha's i may not
> be
> considering.|||Does this have any impact in the size of the transaction log, or since i
believe it uses tempdb will it cause any errors due to log file size or disc
space issues?
in Enterprise manager in design mode the gui will handle the name change but
i'm more worried about the impact it may have to log file size ...ect
"Tom Moreau" wrote:
> Check out sp_rename in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
> >I would like to rename a column in a table that has over 770 million rows
> >and
> > around 300-400 gigs of data. I would like to minimize time, resources, and
> > risk. I am looking for suggestions, risks, or any other gotcha's i may not
> > be
> > considering.
>
>|||An object rename goes very quickly and has practically no impact on the log.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
> Does this have any impact in the size of the transaction log, or since i
> believe it uses tempdb will it cause any errors due to log file size or
> disc
> space issues?
> in Enterprise manager in design mode the gui will handle the name change
> but
> i'm more worried about the impact it may have to log file size ...ect
> "Tom Moreau" wrote:
>> Check out sp_rename in the BOL.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>> >I would like to rename a column in a table that has over 770 million
>> >rows
>> >and
>> > around 300-400 gigs of data. I would like to minimize time, resources,
>> > and
>> > risk. I am looking for suggestions, risks, or any other gotcha's i may
>> > not
>> > be
>> > considering.
>>|||How about the TempDB size, what effect does this have on the size of this db.
Do you need atleast the same size of free space on the tempdb to successfully
complete the rename?
"Tom Moreau" wrote:
> An object rename goes very quickly and has practically no impact on the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
> > Does this have any impact in the size of the transaction log, or since i
> > believe it uses tempdb will it cause any errors due to log file size or
> > disc
> > space issues?
> >
> > in Enterprise manager in design mode the gui will handle the name change
> > but
> > i'm more worried about the impact it may have to log file size ...ect
> >
> > "Tom Moreau" wrote:
> >
> >> Check out sp_rename in the BOL.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >>
> >> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> >> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
> >> >I would like to rename a column in a table that has over 770 million
> >> >rows
> >> >and
> >> > around 300-400 gigs of data. I would like to minimize time, resources,
> >> > and
> >> > risk. I am looking for suggestions, risks, or any other gotcha's i may
> >> > not
> >> > be
> >> > considering.
> >>
> >>
> >>
>
>|||There is no impact on tempdb during an object rename.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:ED637B0F-7A3E-40B5-81E2-B6445C9D5381@.microsoft.com...
> How about the TempDB size, what effect does this have on the size of this
> db.
> Do you need atleast the same size of free space on the tempdb to
> successfully
> complete the rename?
> "Tom Moreau" wrote:
>> An object rename goes very quickly and has practically no impact on the
>> log.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
>> > Does this have any impact in the size of the transaction log, or since
>> > i
>> > believe it uses tempdb will it cause any errors due to log file size or
>> > disc
>> > space issues?
>> >
>> > in Enterprise manager in design mode the gui will handle the name
>> > change
>> > but
>> > i'm more worried about the impact it may have to log file size ...ect
>> >
>> > "Tom Moreau" wrote:
>> >
>> >> Check out sp_rename in the BOL.
>> >>
>> >> --
>> >> Tom
>> >>
>> >> ----
>> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> SQL Server MVP
>> >> Columnist, SQL Server Professional
>> >> Toronto, ON Canada
>> >> www.pinpub.com
>> >>
>> >> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> >> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>> >> >I would like to rename a column in a table that has over 770 million
>> >> >rows
>> >> >and
>> >> > around 300-400 gigs of data. I would like to minimize time,
>> >> > resources,
>> >> > and
>> >> > risk. I am looking for suggestions, risks, or any other gotcha's i
>> >> > may
>> >> > not
>> >> > be
>> >> > considering.
>> >>
>> >>
>> >>
>>

Tuesday, March 20, 2012

Removing the spaces left by hidden columns or rows

I have written a comprehensive report that is used as a subreport for many
others. The comprehensive report supports dynamic grouping and the dynamic
hiding of columns or rows.
My problem is that if a column is hidden, a huge gap is seen in the report
where the column is hidden.
A C D
+=====+ (hidden column) +=====+==========+
|xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
...
Is there any way to make column A move to live where the hidden column "B"
would have been had it not been hidden?
Thanks.
--
Jim Campbell
FujiFilm eSystemsYou have to put the colum width to 0 (in the properties window)
"Jim Campbell" wrote:
> I have written a comprehensive report that is used as a subreport for many
> others. The comprehensive report supports dynamic grouping and the dynamic
> hiding of columns or rows.
> My problem is that if a column is hidden, a huge gap is seen in the report
> where the column is hidden.
> A C D
> +=====+ (hidden column) +=====+==========+
> |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> ...
> Is there any way to make column A move to live where the hidden column "B"
> would have been had it not been hidden?
> Thanks.
> --
> Jim Campbell
> FujiFilm eSystems|||If only it were that simple... I need to set the width based on whether or
not the column is hidden. Yet, I read the post below:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
that seems to imply there is no way to do this under the current release.
Has anyone found a work around?
- Jim
--
Jim Campbell
FujiFilm eSystems
"Antoon" wrote:
> You have to put the colum width to 0 (in the properties window)
> "Jim Campbell" wrote:
> > I have written a comprehensive report that is used as a subreport for many
> > others. The comprehensive report supports dynamic grouping and the dynamic
> > hiding of columns or rows.
> >
> > My problem is that if a column is hidden, a huge gap is seen in the report
> > where the column is hidden.
> > A C D
> > +=====+ (hidden column) +=====+==========+
> > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> >
> > ...
> >
> > Is there any way to make column A move to live where the hidden column "B"
> > would have been had it not been hidden?
> >
> > Thanks.
> > --
> > Jim Campbell
> > FujiFilm eSystems|||You probably have a condition that you use to hide the column; try setting
the colum width to =iif(condition;0;20)
"Jim Campbell" wrote:
> If only it were that simple... I need to set the width based on whether or
> not the column is hidden. Yet, I read the post below:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> that seems to imply there is no way to do this under the current release.
> Has anyone found a work around?
> - Jim
> --
> Jim Campbell
> FujiFilm eSystems
>
> "Antoon" wrote:
> > You have to put the colum width to 0 (in the properties window)
> >
> > "Jim Campbell" wrote:
> >
> > > I have written a comprehensive report that is used as a subreport for many
> > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > hiding of columns or rows.
> > >
> > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > where the column is hidden.
> > > A C D
> > > +=====+ (hidden column) +=====+==========+
> > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > >
> > > ...
> > >
> > > Is there any way to make column A move to live where the hidden column "B"
> > > would have been had it not been hidden?
> > >
> > > Thanks.
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems|||But I am pretty sure that u can't write an expression in the width property.
U can just enter numeric values..
am I wrong ?
"Antoon" wrote:
> You probably have a condition that you use to hide the column; try setting
> the colum width to =iif(condition;0;20)
> "Jim Campbell" wrote:
> > If only it were that simple... I need to set the width based on whether or
> > not the column is hidden. Yet, I read the post below:
> >
> > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> >
> > that seems to imply there is no way to do this under the current release.
> > Has anyone found a work around?
> >
> > - Jim
> > --
> > Jim Campbell
> > FujiFilm eSystems
> >
> >
> > "Antoon" wrote:
> >
> > > You have to put the colum width to 0 (in the properties window)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > I have written a comprehensive report that is used as a subreport for many
> > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > hiding of columns or rows.
> > > >
> > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > where the column is hidden.
> > > > A C D
> > > > +=====+ (hidden column) +=====+==========+
> > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > >
> > > > ...
> > > >
> > > > Is there any way to make column A move to live where the hidden column "B"
> > > > would have been had it not been hidden?
> > > >
> > > > Thanks.
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems|||Yeah you're rigth, sorry about that.
Now I rember how to do it. Put the width value to zero and flag the
properties/"can increase to accomadate length" .
When visible the colomn width will be fine, when invisible it will
be...truly invisible
"Jerome" wrote:
> But I am pretty sure that u can't write an expression in the width property.
> U can just enter numeric values..
> am I wrong ?
> "Antoon" wrote:
> > You probably have a condition that you use to hide the column; try setting
> > the colum width to =iif(condition;0;20)
> >
> > "Jim Campbell" wrote:
> >
> > > If only it were that simple... I need to set the width based on whether or
> > > not the column is hidden. Yet, I read the post below:
> > >
> > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > >
> > > that seems to imply there is no way to do this under the current release.
> > > Has anyone found a work around?
> > >
> > > - Jim
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > You have to put the colum width to 0 (in the properties window)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > hiding of columns or rows.
> > > > >
> > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > where the column is hidden.
> > > > > A C D
> > > > > +=====+ (hidden column) +=====+==========+
> > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > >
> > > > > ...
> > > > >
> > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > would have been had it not been hidden?
> > > > >
> > > > > Thanks.
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems|||Hi,
Access to the Reporting Service site at Microsoft appears to be
hanging, so I'll reply using Google Groups...
Can you tell me where to find this 'can increase to accommodate length'
option? The only thing I see that is like this is "can increase to
accommodate height."
Can this width option be used if there is a textbox in the column? Can
a textbox widen to accommodate contents, too?
Thanks,
Jim Campbell
Antoon wrote:
> Yeah you're rigth, sorry about that.
> Now I rember how to do it. Put the width value to zero and flag the
> properties/"can increase to accomadate length" .
> When visible the colomn width will be fine, when invisible it will
> be...truly invisible
> "Jerome" wrote:
> > But I am pretty sure that u can't write an expression in the width property.
> > U can just enter numeric values..
> > am I wrong ?
> >
> > "Antoon" wrote:
> >
> > > You probably have a condition that you use to hide the column; try setting
> > > the colum width to =iif(condition;0;20)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > If only it were that simple... I need to set the width based on whether or
> > > > not the column is hidden. Yet, I read the post below:
> > > >
> > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > >
> > > > that seems to imply there is no way to do this under the current release.
> > > > Has anyone found a work around?
> > > >
> > > > - Jim
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > You have to put the colum width to 0 (in the properties window)
> > > > >
> > > > > "Jim Campbell" wrote:
> > > > >
> > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > hiding of columns or rows.
> > > > > >
> > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > where the column is hidden.
> > > > > > A C D
> > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > >
> > > > > > ...
> > > > > >
> > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > would have been had it not been hidden?
> > > > > >
> > > > > > Thanks.
> > > > > > --
> > > > > > Jim Campbell
> > > > > > FujiFilm eSystems|||I did my own research here, and I see that the "CanGrow" feature of
textboxes applies only to height and not width in this particular
version of MS Reporting Services. I do not see any way to do what I am
asking.
To illustrate what I have today:
col1--col2--col3--data1--data2
X Y Z
where col1 may or may not show field X if the user groups by field X,
col2 may or may not show field Y if the user groups by field Y, etc...
This means that if, for example, the user chooses not to group by field
Y, the report will appear like this
col1 col3--data1--data2
X Z
I suppose that if I found a way to have true dynmaic grouping, e.g., by
having col3 group by X, Y, or Z, rather than have each column represent
one field toggled on or off with grouping, I could it make it all
better, but I would still have the problem of spaces to the left of the
report, representing all the non-grouped columns.
I guess we'll have to put this problem down to the limitations inherent
in this "1.0-version" piece of software.
- Jim
jtk6204@.gmail.com wrote:
> Hi,
> Access to the Reporting Service site at Microsoft appears to be
> hanging, so I'll reply using Google Groups...
> Can you tell me where to find this 'can increase to accommodate length'
> option? The only thing I see that is like this is "can increase to
> accommodate height."
> Can this width option be used if there is a textbox in the column? Can
> a textbox widen to accommodate contents, too?
> Thanks,
> Jim Campbell
> Antoon wrote:
> > Yeah you're rigth, sorry about that.
> > Now I rember how to do it. Put the width value to zero and flag the
> > properties/"can increase to accomadate length" .
> > When visible the colomn width will be fine, when invisible it will
> > be...truly invisible
> >
> > "Jerome" wrote:
> >
> > > But I am pretty sure that u can't write an expression in the width property.
> > > U can just enter numeric values..
> > > am I wrong ?
> > >
> > > "Antoon" wrote:
> > >
> > > > You probably have a condition that you use to hide the column; try setting
> > > > the colum width to =iif(condition;0;20)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > If only it were that simple... I need to set the width based on whether or
> > > > > not the column is hidden. Yet, I read the post below:
> > > > >
> > > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > > >
> > > > > that seems to imply there is no way to do this under the current release.
> > > > > Has anyone found a work around?
> > > > >
> > > > > - Jim
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems
> > > > >
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > You have to put the colum width to 0 (in the properties window)
> > > > > >
> > > > > > "Jim Campbell" wrote:
> > > > > >
> > > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > > hiding of columns or rows.
> > > > > > >
> > > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > > where the column is hidden.
> > > > > > > A C D
> > > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > > >
> > > > > > > ...
> > > > > > >
> > > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > > would have been had it not been hidden?
> > > > > > >
> > > > > > > Thanks.
> > > > > > > --
> > > > > > > Jim Campbell
> > > > > > > FujiFilm eSystems

Monday, March 12, 2012

removing spaces from an nvarchar column

Hello,
This is a simple question, hopefully with a simple answer. I have
an nvarchar column of length 255. In one of the rows I have the
following sentance - 'See the brown ball bounce'. Is it possible to
use a command to remove all of the spaces in that sentance, so that
the sentance reads 'Seethebrownballbounce'? As you can see, I am not
just interested in getting rid of the trailing and leading spaces.

Thanks,
BillyYou can use REPLACE:

SELECT REPLACE(N'See the brown ball bounce', N' ', N'')

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0411021606.9bbc6d5@.posting.google.co m...
> Hello,
> This is a simple question, hopefully with a simple answer. I have
> an nvarchar column of length 255. In one of the rows I have the
> following sentance - 'See the brown ball bounce'. Is it possible to
> use a command to remove all of the spaces in that sentance, so that
> the sentance reads 'Seethebrownballbounce'? As you can see, I am not
> just interested in getting rid of the trailing and leading spaces.
> Thanks,
> Billy

Removing rows on subscribers, but not on publishers

Hello.
I'm looking for a way to remove data from a merge replicated database
that would be filtered out if it was synchronized for the first time
today. In addition, I don't want the new rows to be deleted from the
publisher's database.
Let me explain. We have several tables that look similar to the
following:
TableA:
Id uniqueidentifier
Value varchar(50)
TimeStamp datetime
We have a merge replication publication with filters that specify that
data with a TimeStamp older than 14 days should not be synchronized,
similar to the following:
exec sp_addmergefilter @.publication = N'MyPub', @.article = N'TableA',
@.filtername = N'TableA_TableB', @.join_articlename = N'TableB',
@.join_filterclause = N'{snipped}... and TableA.TimeStamp > ( GETDATE()
- 14 )', @.join_unique_key = 0
What ends up happening is that new rows with a recent TimeStamp is
synchronized to the client as desired. If the client syncs again after
14 days, however, this now "stale" data is still present on the client
-- it does not become deleted.
If we manually try to remove the stale data by running a query such as
"DELETE from TableA where TimeStamp > (GETDATE() - 30)", the data is
deleted. But the next time the database is synchronized, the data is
also deleted from the publishing database. This is not acceptable in
our application.
Some of our clients use SQL CE on Pocket PC's with limitted storage
capacity and thus will not be able to continue to store this old data.
Any ideas on how to delete the old data from the SQL CE subscriber's
databases without having the side effect of removing the data from the
publishing database?
Thanks in advance for your time.
- Eli Tucker
I think what you want to do is to manipulate your agents so that they change
to download only (-ExchangeType download) when you delete rows on your
subscriber, and then both (-ExchangeType both) under normal operations.
This will only work if the deletes occur in isolation. For instance if the
only activity occuring on your subscriber was the delete, this would work.
If there were deletes and updates and inserts occuring on your subcriber
with the ExchangeType of download the inserts and deletes would not make it.
"Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
news:1105046696.732620.46610@.f14g2000cwb.googlegro ups.com...
> Hello.
> I'm looking for a way to remove data from a merge replicated database
> that would be filtered out if it was synchronized for the first time
> today. In addition, I don't want the new rows to be deleted from the
> publisher's database.
> Let me explain. We have several tables that look similar to the
> following:
> TableA:
> Id uniqueidentifier
> Value varchar(50)
> TimeStamp datetime
> We have a merge replication publication with filters that specify that
> data with a TimeStamp older than 14 days should not be synchronized,
> similar to the following:
> exec sp_addmergefilter @.publication = N'MyPub', @.article = N'TableA',
> @.filtername = N'TableA_TableB', @.join_articlename = N'TableB',
> @.join_filterclause = N'{snipped}... and TableA.TimeStamp > ( GETDATE()
> - 14 )', @.join_unique_key = 0
> What ends up happening is that new rows with a recent TimeStamp is
> synchronized to the client as desired. If the client syncs again after
> 14 days, however, this now "stale" data is still present on the client
> -- it does not become deleted.
> If we manually try to remove the stale data by running a query such as
> "DELETE from TableA where TimeStamp > (GETDATE() - 30)", the data is
> deleted. But the next time the database is synchronized, the data is
> also deleted from the publishing database. This is not acceptable in
> our application.
> Some of our clients use SQL CE on Pocket PC's with limitted storage
> capacity and thus will not be able to continue to store this old data.
>
> Any ideas on how to delete the old data from the SQL CE subscriber's
> databases without having the side effect of removing the data from the
> publishing database?
> Thanks in advance for your time.
> - Eli Tucker
>
|||Hilary, thanks for the info. I'll keep this tip in mind, but it isn't
ideal for us since the client application both adds and updates new
rows between syncs.
Does anyone have any other ideas of how to do this?
|||Eli,
Instead of synchronizing from your subscriber code, if you *reinitialize*
the subscription
with the option to upload your latest changes before refreshing the
subscription tables,
your subscriptions will always contain only the 14 days of data your
publication filter dictates.
Take a look at the boolean value that ReinitializeSubscription takes...
rep.ReinitializeSubscription(True)
-- Darren Shaffer
"Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
news:1105409204.644780.269050@.c13g2000cwb.googlegr oups.com...
> Hilary, thanks for the info. I'll keep this tip in mind, but it isn't
> ideal for us since the client application both adds and updates new
> rows between syncs.
> Does anyone have any other ideas of how to do this?
>
|||Thanks, Darren. I haven't looked at the ReinitializeSubscription
option before -- it very well could work for us.
Is it basically equivalent to the following?
1. Synchronizing upload only
2. Deleting the SDF file
3. Performing an initial sync
Or is less data passed when using the ReinitializeSubscription(true)
method?
Thanks again!
- Eli
Darren Shaffer wrote:
> Eli,
> Instead of synchronizing from your subscriber code, if you
*reinitialize*
> the subscription
> with the option to upload your latest changes before refreshing the
> subscription tables,
> your subscriptions will always contain only the 14 days of data your
> publication filter dictates.
> Take a look at the boolean value that ReinitializeSubscription
takes...[vbcol=seagreen]
> rep.ReinitializeSubscription(True)
> -- Darren Shaffer
>
> "Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
> news:1105409204.644780.269050@.c13g2000cwb.googlegr oups.com...
isn't[vbcol=seagreen]

Removing rows from very large table with indices

Hello,
I've got a table with a few million rows in it. This table has one clustere
d index and four other indices. When I try to delete records from it on a d
ay-by-day basis (it has a date column), it typically takes between 2 and 5 m
inutes to remove just one d
ay worth. I need to purge about 90 days worth of data ...
Is there a way to shut off index recomputation or whatever it is that takes
so long for the delete to occur? This is an operational data table that rea
lly only needs about 7 days worth of data in it (not 90).
Thanks
-- JakeHi Jake.
How are you performing the delete statement - row by row or by a single
statement supported by a where clause?
It's important to understand that the indexes will actually help the delete
to identify the rows that meet the criteria for deletion, so "shutting" down
the index during deletion would cause the unenviable effect that a table
scan would be required to identify the rows for deletion...
Regards,
Greg Linwood
SQL Server MVP
"javatopia" <anonymous@.discussions.microsoft.com> wrote in message
news:8D297AF5-9D9A-438F-9A76-971F9DF2D0C1@.microsoft.com...
> Hello,
> I've got a table with a few million rows in it. This table has one
clustered index and four other indices. When I try to delete records from
it on a day-by-day basis (it has a date column), it typically takes between
2 and 5 minutes to remove just one day worth. I need to purge about 90 days
worth of data ...
> Is there a way to shut off index recomputation or whatever it is that
takes so long for the delete to occur? This is an operational data table
that really only needs about 7 days worth of data in it (not 90).
> Thanks
> -- Jake
>|||Hi,
I don't recommend you to delete indexes because proper indexes will help you
to locate the records that you want to delete more quickly . (You have to
find the records first before you delete them.)
If you want to delete records in a single transaction (and if you are deleti
ng large amount of data) your log file will grow fast no matter what you're
recovery model is.
I'll prefer to set rowcount and delete records in a while loop until @.@.rowco
unt = 0. This will delete you're records in smaller transactions.
Also selecting the records that you like to keep in a new table and dropping
the old table and renaming the new table is generally less expensive then d
elete. (Depends the percentage of tha data that you want to keep and you wan
t to delete.)
And partitioned tables are very helpfull for archiving and deleting..
Regards..
Umut Nazl?ca, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)|||I like the suggestion of selecting INTO a new table, dropping the old table,
and then renaming the new table to the old one. Seems like that could be d
one in an automated manner as well, right? Is it possible to put the DB int
o single-user mode via a st
ored procedure, then do all of this (select into, drop old, rename table)?
Your help is very much appreciated!!
-- Jake|||Hi,
Yes you can put db into single user mode using alter database and terminatio
n options like:
ALTER DATABASE XXX
SET SINGLE_USER WITH ROLLBACK AFTER X (See Books Online ..)
and you can schedule a job to automate it.
My suggestion is first script your table then create your new table using th
is script (at this point do not create the indexes on new table), transfer
data, create indexes on new table and so on.
But test first to see if it works the way you want..
Regards..

Removing rows from a SqlDataSource before it is bound to a GridView?

I have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this?

(if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data)

Thanks.

HI~ There's no such flexibility for SqlDataSource yet. I'm afraid you should databind in code to perform this~ Like return a DataTable and remove the first 2 rows and then bind it to some control|||HI~ There's no such flexibility for SqlDataSource yet. I'm afraid you should databind in code to perform this~ Like return a DataTable and remove the first 3 rows and then bind it to some control|||

If I can avoid rebuilding all of these at this point, I'm going to try...

Is there a way for me to just filter out the first n records of a GridView every time it is loaded?

|||

Well , try the following

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowIndex == 0)
{
e.Row.Visible = false;
}
}

When an element is Visible = false, it's not rendered at all~

|||

Beauty, thanks. I've been staring at this so long I didn't even think of that route.

One other question for you while I've got someone staring at this thread...if you don't mind.

Part of these same GridViews, the SqlDataSource UPDATE requires that one of the UpdateParameters be passed in as an XML string. Now, I have an XML UpdateParameter setup, it's just a question of how do I get my GridView editable fields for a given row, formatted in to the XML string I need to pass and then bound to the UpdateParameter?

I guess this is a two part question:

1) What event handler could I trap before the UPDATE, to cycle through the GridView's fields, format them in code into the XML string, and then...

2) How do I bind this new string to the UpdateParameter itself? Store it in session and bind the UpdateParameter to that? Not the most ideal...but just throwing ideas out there.

Thanks in advance, really appreciate it.

|||

Hi~ You have one filed in GrdiView row, and you wanna make some change to the value of this field before updating right?

I'm not quite sure, but you can try:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
e.NewValues["XXX"] = XmlFormat(e.NewValues["XXX"]);
}

|||

Hi there.

Not quite...one of the UpdateParameters for this particular GridView actually takes the values from 3 other fields in an edited row, combines them into an XML string, and then that value (the XML string that has values from 3 of the fields in it), is bound to the UpdateParameter.

Does that make better sense? :)

|||

That 3 fields should be in e.OldValues or e.NewValues collection, You may try both, depending on how you databind them (Eval() or Bind()), or neither.

For example:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
e.NewValues["XML"] = XmlFormat(e.OldValues["field1"], e.OldValues["field2"], e.OldValues["field3"]);
}

The above is not rwWord("guarantee"); guaranteed to work, usually I would use ObjectDataSource and my own BLL method / Or code behind to do this kind of complex operation... SqlDataSource is not good at this..

|||

Yup, I couldn't agree with you more on the ObjectDataSource/BLL route...unfortunately we have a *very* limited budget on this project, and we're inheriting some things from the previous vendor. With the timeframe and budget I'm trying to work with what's already there in the interest of just getting it done for the first go. Once we have more time available to us, we'll be able to do it right with the business layer, until then...workarounds and hacks. =/

Thanks for your help on this tonight, I'll let you know how it turns out.

Best.

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
> > I have a query which returns the following results. What i want to do
> > is remove any rows where col1 does not appear in col2. e.g. "107". As
> > well as this i want to remove any rows that are affected by this, e.g.
> > "109" as once 107 goes, 109 does not appear in col2.
> > Thanks in advance.
> > col1 col2
> > 1 108
> > 1 112
> > 1 115
> > 1 116
> > 1 117
> > 1 118
> > 107 109
> > 108 114
> > 109 110
> > 110 111
> > 112 113
> > 118 119
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1108
1112
1115
1116
1117
1118
107109
108114
109110
110111
112113
118119
On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp
|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select
1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

removing rows from a replicated table

Hi
I have a subscription which has a table and some rows which I cannot
delete - they don't have a rowguid value set for them - when I try and
delete I get a message about 'cannot insert a null value into column 'guid'.
How do I tidy tihss up ?
thanks
Is this a merge subscription? Or Immediate Updating?
It sounds like the triggers are not executing correctly.
The best way for you to solve this would be to reinitialize and regenerate
and distribute your snapshot.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
news:%23yyb9VNaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a subscription which has a table and some rows which I cannot
> delete - they don't have a rowguid value set for them - when I try and
> delete I get a message about 'cannot insert a null value into column
'guid'.
> How do I tidy tihss up ?
> thanks
>
|||Bruce,
presumably you are using merge, and the insert failure is the insertion of a
row into MSmerge_tombstone.
I don't know how these rows have managed to be entered without having a GUID
as this is a default value. Either they were entered with an explicit null
or they were already there.
To remove them, it depends on if they exist on other servers or not.
The easiest way is to use ALTER TABLE DISABLE TRIGGER deletetriggername then
remove the rows then reenable the trigger. Obviously you must ensure there
are no other changes going on while you do this type of manipulation.
HTH,
Paul Ibison
|||Great idea but it says I can't alter the table 'cause it is being published
for replication....
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:etvoroNaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Bruce,
> presumably you are using merge, and the insert failure is the insertion of
a
> row into MSmerge_tombstone.
> I don't know how these rows have managed to be entered without having a
GUID
> as this is a default value. Either they were entered with an explicit null
> or they were already there.
> To remove them, it depends on if they exist on other servers or not.
> The easiest way is to use ALTER TABLE DISABLE TRIGGER deletetriggername
then
> remove the rows then reenable the trigger. Obviously you must ensure there
> are no other changes going on while you do this type of manipulation.
> HTH,
> Paul Ibison
>
|||I altered the trigger so it didn't do anything, deleted the rows and altered
the trigger back to how it was. Thanks for the tip...
Bruce
|||Ah...then try editing the trigger to comment out the part that inserts into
the MSmerge_tombstone table. Can't test it here so please post back if there
are any problems.
Regards,
Paul Ibison

Wednesday, March 7, 2012

removing embedded duplicate rows

I have a table that contains a field with data like:
dog 1
dog 2
cat 1
cat 2
I only what to return one of the values. DISTINCT doesn't do it. Any
suggestions?
Thanks,
BobWhich value do you want? Distinct rolls up multiple values that are the
same. Your four values are all different, and thus cannot be rolled up.
Describe what you expect to see from the sample data you quoted.
More info, please.|||Assuming table below with 2 cols name & id
name id
-- --
dog 1
dog 2
cat 1
cat 2
-- following will get u the 1st of every grp with same name
select name, id
from t
where id in (select min(id) from t group by name)
Rakesh
"bday55" wrote:

> I have a table that contains a field with data like:
> dog 1
> dog 2
> cat 1
> cat 2
> I only what to return one of the values. DISTINCT doesn't do it. Any
> suggestions?
> Thanks,
> Bob
>|||On Wed, 10 Aug 2005 22:51:01 -0700, Rakesh wrote:

>Assuming table below with 2 cols name & id
>name id
>-- --
>dog 1
>dog 2
>cat 1
>cat 2
>-- following will get u the 1st of every grp with same name
>select name, id
>from t
>where id in (select min(id) from t group by name)
Hi Rakesh,
But that won't work if the rows are changed to

>name id
>-- --
>dog 1
>dog 2
>cat 2
>cat 3
Here's a better one:
SELECT name, id
FROM t AS t1
WHERE id = (SELECT MIN(id) FROM t AS t2 WHERE t1.name = t2.name)
Or, even simpler:
SELECT name, MIN(id)
FROM t
GROUP BY t
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Removing Duplicates rows from Inner Join

i have two tables, tab1 having N1 col and tab2 N2 col. now N1 is subset
of N2.
I need the information from tab2 (having N2) of all rows having the
matching entry in N1 in tab1.
For this i am using Inner Join on cols N1 and n2. But result is giving
duplicate rows. Can anyone suggest how do u i remove those duplicate
rows? or may be a better way to do the above work... Thanks
Sounds like you should be using EXISTS
rather than a join

SELECT * FROM tab2
WHERE EXISTS (SELECT * FROM tab1 WHERE tab1.N1=tab2.N2)|||thanks mark
but i have tables with large data and running this querry is taking a
lot of time.
can u suggest a better method or optimize this querry?|||Did you try to use DISTINCT to remove the duplicates?
SELECT DISTINCT tab2.* FROM tab2 INNER JOIN tab1 ON tab2.N2 = tab1.N1|||Can you post your DDL including indexes|||asgars (asgars@.gmail.com) writes:
> thanks mark
> but i have tables with large data and running this querry is taking a
> lot of time.
> can u suggest a better method or optimize this querry?

Which query? It's very difficult to suggest optimizations to a query
without seeing it, and without knowledge of the tables.

Please post:

o The query you are using now.
o CREATE TABLE and CREATE INDEX statements for the inolved tables.
o Some indication on number of rows in the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Removing duplicates

Can somebody help me by telling the best way to remove duplicate rows from a table.You can query a table with SELECT DISTINCT to cause duplicate data to be omitted.

You could query the table in this way into a temporary table, empty the old table and repopulate it from the temporary.|||This depends on the structure of your table and what rows will be deleted and which will be kept. Are the entire rows identical (no unique identifier) ? How many records are in the table ?

removing duplicates

I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie YaegerAh - you need a "tiebreaker". Basically another column that you can
include in your selectivity criteria to make the rows unique. For
example, if it's feasible to assume that the same acctnum/invnum is
associated with only a single address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you
one of the dups. MIN() will give you a different dup. TOP 1 would
arbitrarily give you any 1 of the dups. It may be necessary to include
all the columns in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you
want more info. Itzik Ben-Gan has recently written about them in SQLMag
(http://www.windowsitpro.com/Article...5235/45235.html) if
you're a SQL Mag subscriber. But I'm sure a google search and/or a
google groups search would return many hits regarding tiebreakers in SQL
queries.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Bernie Yaeger wrote:

>I have a table with 15 columns. Among these are 2 text columns - acctnum
>and invnum. If any rows are the same, I want them removed. Thus,
>acctnum invnum cprice amtpaid address
>1234 33688 5.99 12.97 22 Hope Street
>9876 33688 4.22 1.97 27 Hope Street
>1234 33688 14.69 4.36 26 Hope Street
>5407 33688 3.47 8.89 25 Hope Street
>1234 00921 8.11 1107 24 Hope Street
>2970 33688 10.05 125.97 23 Hope Street
>Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
>3 should remain, but not both (if doesn't matter which is removed). If I
>select distinct and use the 'select...into' clause, I don't get all the
>other columns of information.
>Can someone help me with this?
>Thanks for any help.
>Bernie Yaeger
>
>|||Hi,
You can do this by setting rowcount. Try the following code:
SET ROWCOUNT 1
SELECT a.* FROM [tablename] a, [tablename] b
WHERE a.acctnum = b.acctnum
AND a.invnum = b.invnum
Now, although there are two different records (row 1 and row 3), the query
will return only one row.
Finally do this,
SET ROWCOUNT 0
"Mike Hodgson" wrote:

> Ah - you need a "tiebreaker". Basically another column that you can
> include in your selectivity criteria to make the rows unique. For
> example, if it's feasible to assume that the same acctnum/invnum is
> associated with only a single address then you could say
> select acctnum, invnum, cprice, amtpaid, address
> from mytable as a
> where address =
> (select max(b.address) from mytable as b
> where a.acctnum = b.acctnum
> and a.invnum = b.invnum)
> Any subquery that returns one of the dups would do. MAX() will give you
> one of the dups. MIN() will give you a different dup. TOP 1 would
> arbitrarily give you any 1 of the dups. It may be necessary to include
> all the columns in the select list to get a single acctnum/invnum result.
> There has been quite a bit written on tie-breakers on the web if you
> want more info. Itzik Ben-Gan has recently written about them in SQLMag
> (http://www.windowsitpro.com/Article...5235/45235.html) if
> you're a SQL Mag subscriber. But I'm sure a google search and/or a
> google groups search would return many hits regarding tiebreakers in SQL
> queries.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Bernie Yaeger wrote:
>
>|||Hi Bernie Yaeger,
I am not suggesting that following method is correct, but it surely
works.
Step 1 :- Create a table with select statement having identity column
select identity (1,1) myid,* into myTable from BaseTable
Step 2 :- Delete whatever rows you wish
delete from MyTable where myid in ( whatever)
Step 3 :- Remove the Identity column
alter table taa drop column myid
Step 4 :- Remove all records from Base Table
truncate BaseTable
Step 5:- Insert all records from MYTable
insert into BaseTable select * from MyTable
With regards
Jatinder|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column
(identity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Deeraj" <Deeraj@.discussions.microsoft.com> wrote in message
news:BD5EE2B3-E11D-4713-AA5F-F2D38433FF61@.microsoft.com...
> Hi,
> You can do this by setting rowcount. Try the following code:
> SET ROWCOUNT 1
> SELECT a.* FROM [tablename] a, [tablename] b
> WHERE a.acctnum = b.acctnum
> AND a.invnum = b.invnum
> Now, although there are two different records (row 1 and row 3), the query
> will return only one row.
> Finally do this,
> SET ROWCOUNT 0
> "Mike Hodgson" wrote:
>|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger

removing duplicated rows

wrote in message

news:e3d0a7da-ac87-46f8-a9c5-008fbcb4cd8d@.discussions.microsoft.com...

> Hi,

> Need to remove the duplicated rows from a table which has

> text/ntext/image type columns. The table does not have any PK/Unique

> column. (I accept its a bad data model). But currently changing the data

> model is not possible. Hence doing changes in application.

> I couldn't do 'SELECT DISTINCT * from table', since the table has text

> columns. Though there is no PK constraint, If I know that col1 and col2

> are join PKs in the table, Is that possible to select the distinct rows

> from such a table.

> Please advise,

> Thanks,

> Smitha

removing duplicated records

Hi,

Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.

I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.

Please advise,

Thanks,
MiraJwhy dont u post the DDL of table,some describtion abt table|||maybe you could use group by and use max() function to exclude duplicates

select col1, col2, max(text_col3), max(text_col4)...
from t
group by col1, col2...|||why dont u post the DDL of table,some describtion abt table

Table :

create table test(col1 int, col2 int, col3 text, col4 image)|||it is quiet difficult to do, there are some options,
insert all records into temp table ,add unique column before transfering data to temp table

1.If data values are less
than 8000 characters, you can cast the values to VARCHAR(8000) and use DISTINCT or
GROUP by to get the unique row.

If data values are greater than
8000
characters you may have to use a cursor & loop through
the rows & delete the
rows. In some cases you may have to use
an identity column or a number table
( with a cross join
operation ) to easily distingush one row from another.
----------
This link may help u http://www.windowsitpro.com/SQLServer/Article/ArticleID/23234/23234.html

Removing Duplicate Rows Issue

I'm trying to remove duplicate rows from a table. tableA has 2 cols. acctid
and col1 When I run:
select count(distinct accid) from tableA
I get 10 as a result(I guess there are 10 distinct rows in the table,
correct?), but when I run:
select distinct acctid, col2 into #temp from tableA
(those are the only 2 columns in the table) I get 11 as the result, How
come? Shouldn't it say 10 rows affexted and not 11? any ideas?
thanksCan you show us the structure of tableA, and the INSERT statements required
to populate it with these 10 or 11 rows?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> I'm trying to remove duplicate rows from a table. tableA has 2 cols.
acctid
> and col1 When I run:
> select count(distinct accid) from tableA
> I get 10 as a result(I guess there are 10 distinct rows in the table,
> correct?), but when I run:
> select distinct acctid, col2 into #temp from tableA
> (those are the only 2 columns in the table) I get 11 as the result, How
> come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> thanks|||tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
when I do select count(distinct acctid) from tableA I get 10 rows as the
result, then when I select distinct aactid, col1 into #temp from tablea I get
11 rows, Why is this the case? Thank you
"Aaron [SQL Server MVP]" wrote:
> Can you show us the structure of tableA, and the INSERT statements required
> to populate it with these 10 or 11 rows?
> http://www.aspfaq.com/5006
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> acctid
> > and col1 When I run:
> > select count(distinct accid) from tableA
> > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > correct?), but when I run:
> > select distinct acctid, col2 into #temp from tableA
> > (those are the only 2 columns in the table) I get 11 as the result, How
> > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > thanks
>
>|||Because you added a column to your distinct clause in the second query.
select distinct aactid, col1 from tablea
vs
select distinct aactid from tablea
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> when I do select count(distinct acctid) from tableA I get 10 rows as the
> result, then when I select distinct aactid, col1 into #temp from tablea I
get
> 11 rows, Why is this the case? Thank you
> "Aaron [SQL Server MVP]" wrote:
> > Can you show us the structure of tableA, and the INSERT statements
required
> > to populate it with these 10 or 11 rows?
> > http://www.aspfaq.com/5006
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> > acctid
> > > and col1 When I run:
> > > select count(distinct accid) from tableA
> > > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > > correct?), but when I run:
> > > select distinct acctid, col2 into #temp from tableA
> > > (those are the only 2 columns in the table) I get 11 as the result,
How
> > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > thanks
> >
> >
> >|||that was an example, what I'm really trying to do is remove duplicate by
these statements
select distinct acctid, col1 into #temp from tableA
truncate table tableA
insert into tableA select * from #temp
the logic is in the previous posts!
"Jeff Dillon" wrote:
> Because you added a column to your distinct clause in the second query.
> select distinct aactid, col1 from tablea
> vs
> select distinct aactid from tablea
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> > tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> > when I do select count(distinct acctid) from tableA I get 10 rows as the
> > result, then when I select distinct aactid, col1 into #temp from tablea I
> get
> > 11 rows, Why is this the case? Thank you
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > Can you show us the structure of tableA, and the INSERT statements
> required
> > > to populate it with these 10 or 11 rows?
> > > http://www.aspfaq.com/5006
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> > > acctid
> > > > and col1 When I run:
> > > > select count(distinct accid) from tableA
> > > > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > > > correct?), but when I run:
> > > > select distinct acctid, col2 into #temp from tableA
> > > > (those are the only 2 columns in the table) I get 11 as the result,
> How
> > > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > > thanks
> > >
> > >
> > >
>
>|||Yes, your logic is wrong, per my response.
You are aware that distinct acctid, col1 means take (acctid, col1) together,
and find all distinct rows. Distinct doesn't only apply to the first column
before your comma
To find duplicates:
select count(id), id from a
group by id
having count(id) > 1
Jeff
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:2B455174-0A8A-47E3-BB71-AA26CBE37D0E@.microsoft.com...
> that was an example, what I'm really trying to do is remove duplicate by
> these statements
> select distinct acctid, col1 into #temp from tableA
> truncate table tableA
> insert into tableA select * from #temp
> the logic is in the previous posts!
> "Jeff Dillon" wrote:
> > Because you added a column to your distinct clause in the second query.
> >
> > select distinct aactid, col1 from tablea
> >
> > vs
> >
> > select distinct aactid from tablea
> >
> > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> > > tableA has 2 columns acctid and col1. lets say there are 20 rows of
data,
> > > when I do select count(distinct acctid) from tableA I get 10 rows as
the
> > > result, then when I select distinct aactid, col1 into #temp from
tablea I
> > get
> > > 11 rows, Why is this the case? Thank you
> > >
> > > "Aaron [SQL Server MVP]" wrote:
> > >
> > > > Can you show us the structure of tableA, and the INSERT statements
> > required
> > > > to populate it with these 10 or 11 rows?
> > > > http://www.aspfaq.com/5006
> > > >
> > > > --
> > > > http://www.aspfaq.com/
> > > > (Reverse address to reply.)
> > > >
> > > >
> > > >
> > > >
> > > > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > > > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > > > I'm trying to remove duplicate rows from a table. tableA has 2
cols.
> > > > acctid
> > > > > and col1 When I run:
> > > > > select count(distinct accid) from tableA
> > > > > I get 10 as a result(I guess there are 10 distinct rows in the
table,
> > > > > correct?), but when I run:
> > > > > select distinct acctid, col2 into #temp from tableA
> > > > > (those are the only 2 columns in the table) I get 11 as the
result,
> > How
> > > > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
> >

Wednesday, March 21, 2012

Rename Column in Very large table

Rename Column in Very large table

Rename Column in Very large table

Tuesday, March 20, 2012

Removing the spaces left by hidden columns or rows

Monday, March 12, 2012

removing spaces from an nvarchar column

Removing rows on subscribers, but not on publishers

Removing rows from very large table with indices

Removing rows from a SqlDataSource before it is bound to a GridView?

Removing rows from a result

Removing rows from a result

Removing rows from a result

removing rows from a replicated table

Wednesday, March 7, 2012

removing embedded duplicate rows

Removing Duplicates rows from Inner Join

Removing duplicates

removing duplicates

removing duplicated rows

removing duplicated records

Removing Duplicate Rows Issue

Removing Duplicate Rows Issue

Removing a server

Blog Archive

About Me