Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>sql

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John B
Hi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||One of the ways:
http://support.microsoft.com/default...b;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>> SQL 2K
>> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
>> NAME,
>> SUPVR, MOD_DATE
>> I want to retain just one row for unique (ID, DPTID) combination, doesn't
>> matter with values of rest of the columns.
>> Thanks In Advance
>> John B
>>
>>
>>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
--
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> > Hi
> >
> > Maybe if mod_date is unique for each id,dptid then
> >
> > DELETE FROM DptAct
> > WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> > DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> >
> > John
> >
> > "MS User" wrote:
> >
> >> SQL 2K
> >>
> >> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
> >> NAME,
> >> SUPVR, MOD_DATE
> >>
> >> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> >> matter with values of rest of the columns.
> >>
> >> Thanks In Advance
> >>
> >> John B
> >>
> >>
> >>
> >>
> >>
> >>
>
>

Delete Data without impacting Tempdb

This is a SQL 2005 production server.

I have to delete around 51 million rows from a table which has 149 million rows.

Can't use truncate option as the other rows in the table are still needed.

How can I delete the rows without filling up the tempdb ?

If the tempdb fills up I can't bounce the server.Set your recovery mode to Simple so you don't log all the deletes. Consider deleting your data in smaller batches.|||I've been doing this exact thing for the past week, and did exactly that.

Just remember to defrag/rebuild indexes following this.|||tempdb? maybe you need to check whether you are trying to support snapshot isolation. In that case, the deleted rows will end up in tempdb prior to the commit.|||The recovery mode is "simple". Its still putting all the transaction in the log file.

The Tempdb detail was an error from my side.|||in that case, simply break the delete down into smaller chunks. The entire delete will always be logged. So instead of one big delete, make it 10 smaller deletes.|||thanks for all the responses.
The only solution seems to delete in small batches and then truncate the log.|||You should not need to truncate the log in simple recovery mode.|||Somehow it seems that some of the transaction log keeps getting held after large transactions. I find running checkpoint in the affected database brings things back in line. Checkpoint is also a side effect of truncating the log.|||Mcrowley,
That is exactly the issue I have. I have to truncate the log after doing the large delete inspite of recovery mode simple.|||After your done truncate with no_log and dbcc shrinkfile it back down.|||Bear in mind if you are performing this delete operation during normal day time it will have stress on tempdb too in addition to user database's transaction log.

So better to perform this operation during less traffic hours and also perform CHECKPOINT after couple of thousand rows to keep up the log size.

delete all rows from a specific partition in SQL 2005?

Hi,
does it possible to truncate a specific partition on a partitionned table?
I have a large table where each partition contains 50 million of rows, I
have to truncate 1 partition (which is 1 year) and then refill it.
its a data warehouse, so there is no concurrent usage, its during my loading
process.
does the delete statement is good enough?
thanks.
Jerome."Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
>
ALTER TABLE ... SWITCH can be used to switch the partition with an empty
table, which can then be truncated.
David|||interesting...
for the moment this command works fine:
alter table ProdTable switch partition 6 to DummyTable
this "truncate" my partition 6 of my prodtable, which is exactly what I
want.
This is my scenario...
1 I load new content to an empty partitionned table. when the loading is
completed; I move the current production data of the targeted partition to
my dummy table; Now the prodtable doesn't contains the year I want to load;
I move the data from the temporary partitionned table to the prodtable;
now my prodtable contain the new content.
This reduce the downtime for my users and, in case of failure, preserve the
current content because I move the data only at the end.
does it a good scenario?
but this cause to fill a partitionned table because I can switch only data
from 2 partitionned tables, I can't switch from non-partitionned to a
partitionned one.
now a question... does filling a partitionned table using SSIS cause an
overhead in the insert statement?
thanks.
Jerome.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O99MI3OiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> ALTER TABLE ... SWITCH can be used to switch the partition with an empty
> table, which can then be truncated.
> David
>|||Use the SWITCH operator to move the partition to a staging table and then
truncate the staging table.
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
> thanks.
> Jerome.
>|||"Jeje" <willgart@.hotmail.com> wrote in message
news:eijqvmQiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> interesting...
> for the moment this command works fine:
> alter table ProdTable switch partition 6 to DummyTable
> this "truncate" my partition 6 of my prodtable, which is exactly what I
> want.
> This is my scenario...
> 1 I load new content to an empty partitionned table. when the loading is
> completed; I move the current production data of the targeted partition to
> my dummy table; Now the prodtable doesn't contains the year I want to
> load; I move the data from the temporary partitionned table to the
> prodtable;
> now my prodtable contain the new content.
> This reduce the downtime for my users and, in case of failure, preserve
> the current content because I move the data only at the end.
> does it a good scenario?
Yes.

> but this cause to fill a partitionned table because I can switch only data
> from 2 partitionned tables, I can't switch from non-partitionned to a
> partitionned one.
Sure you can. The non-partitioned table must be on the same file group as
the target partition, and it must have a check constraint on the
partitioning columns that guarantees that all the rows in the table are
belong in that partition. But it is often easier to use a partitioned
staging table for loading, switching and truncating.

> now a question... does filling a partitionned table using SSIS cause an
> overhead in the insert statement?
>
Some, perhaps. It depends on the distributation of the data across
partitions, I suppose.
David

delete all rows from a specific partition in SQL 2005?

Hi,
does it possible to truncate a specific partition on a partitionned table?
I have a large table where each partition contains 50 million of rows, I
have to truncate 1 partition (which is 1 year) and then refill it.
its a data warehouse, so there is no concurrent usage, its during my loading
process.
does the delete statement is good enough?
thanks.
Jerome."Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
>
ALTER TABLE ... SWITCH can be used to switch the partition with an empty
table, which can then be truncated.
David|||interesting...
for the moment this command works fine:
alter table ProdTable switch partition 6 to DummyTable
this "truncate" my partition 6 of my prodtable, which is exactly what I
want.
This is my scenario...
1 I load new content to an empty partitionned table. when the loading is
completed; I move the current production data of the targeted partition to
my dummy table; Now the prodtable doesn't contains the year I want to load;
I move the data from the temporary partitionned table to the prodtable;
now my prodtable contain the new content.
This reduce the downtime for my users and, in case of failure, preserve the
current content because I move the data only at the end.
does it a good scenario?
but this cause to fill a partitionned table because I can switch only data
from 2 partitionned tables, I can't switch from non-partitionned to a
partitionned one.
now a question... does filling a partitionned table using SSIS cause an
overhead in the insert statement?
thanks.
Jerome.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O99MI3OiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> does it possible to truncate a specific partition on a partitionned
>> table?
>> I have a large table where each partition contains 50 million of rows, I
>> have to truncate 1 partition (which is 1 year) and then refill it.
>> its a data warehouse, so there is no concurrent usage, its during my
>> loading process.
>> does the delete statement is good enough?
> ALTER TABLE ... SWITCH can be used to switch the partition with an empty
> table, which can then be truncated.
> David
>|||Use the SWITCH operator to move the partition to a staging table and then
truncate the staging table.
--
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
> thanks.
> Jerome.
>|||"Jeje" <willgart@.hotmail.com> wrote in message
news:eijqvmQiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> interesting...
> for the moment this command works fine:
> alter table ProdTable switch partition 6 to DummyTable
> this "truncate" my partition 6 of my prodtable, which is exactly what I
> want.
> This is my scenario...
> 1 I load new content to an empty partitionned table. when the loading is
> completed; I move the current production data of the targeted partition to
> my dummy table; Now the prodtable doesn't contains the year I want to
> load; I move the data from the temporary partitionned table to the
> prodtable;
> now my prodtable contain the new content.
> This reduce the downtime for my users and, in case of failure, preserve
> the current content because I move the data only at the end.
> does it a good scenario?
Yes.
> but this cause to fill a partitionned table because I can switch only data
> from 2 partitionned tables, I can't switch from non-partitionned to a
> partitionned one.
Sure you can. The non-partitioned table must be on the same file group as
the target partition, and it must have a check constraint on the
partitioning columns that guarantees that all the rows in the table are
belong in that partition. But it is often easier to use a partitioned
staging table for loading, switching and truncating.
> now a question... does filling a partitionned table using SSIS cause an
> overhead in the insert statement?
>
Some, perhaps. It depends on the distributation of the data across
partitions, I suppose.
Davidsql

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

Delete + Log file

A) When i run a delete against a table that has 50 million records based
upon a where clause , what entries does the T log file hold ? Does it log 50
million delete statements along with 50 million inserts just incase it needs
to rollback.
B) Also if there is a clustered index on the coulimn thats part of the where
clause, what does the Tlog file hold ?
C) If there was a clustered index but not part of the column in the where
clause, what does the Tlog contain ?
D) During the time the delete is occuring, does it go ahead and start
deleting entries from the data pages in the data files or does it first log
entries in the Log file and then deletes ?
E) Finally if i did a backup log during the time the delete is occuring (
Say i noticed that the table with (nolock) option was decrementing ) and
then restored the logs on another database , will part of the deletes be
reflected on the other database if i specify the (nolock) option since the
delete did not complete
I am just trying to understand what entries the TLog contains.. I would
appreciate if you could provide answers to all the 5 parts. I am using SQL
2000
Thank youIf you want to see what is in the tran log you can run
this before you do a log backup:
select * from ::fn_dblog(null,null)
I will attempt to answer your questions
a) It logs 50 million delete statements (if you need to
delete everything out of a table and don't need the
ability to rollback, you might use truncate table as it is
faster because it is minimally logged)
b)I don't believe it matters if there is a clustered index
or not in regards to the T-log
c)I don't believe it matters if there is a clustered index
or not in regards to the T-log
d) Everything hits the log first, it hits the data when
the T-log does a checkpoint
e)That one I'm not sure on, would have to test that one or
maybe someone has tried this before that reads these
newsgroups, I would guess that if you tried backing up the
log during this transaction, it would wait until the tran
was finished, selecting from it with a nolog would only
show you, not the T-log, the data while in the transaction.
Again, not sure on that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
>--Original Message--
>A) When i run a delete against a table that has 50
million records based
>upon a where clause , what entries does the T log file
hold ? Does it log 50
>million delete statements along with 50 million inserts
just incase it needs
>to rollback.
>
>B) Also if there is a clustered index on the coulimn
thats part of the where
>clause, what does the Tlog file hold ?
>C) If there was a clustered index but not part of the
column in the where
>clause, what does the Tlog contain ?
>D) During the time the delete is occuring, does it go
ahead and start
>deleting entries from the data pages in the data files or
does it first log
>entries in the Log file and then deletes ?
>E) Finally if i did a backup log during the time the
delete is occuring (
>Say i noticed that the table with (nolock) option was
decrementing ) and
>then restored the logs on another database , will part of
the deletes be
>reflected on the other database if i specify the (nolock)
option since the
>delete did not complete
>I am just trying to understand what entries the TLog
contains.. I would
>appreciate if you could provide answers to all the 5
parts. I am using SQL
>2000
>Thank you
>
>.
>|||--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> A) When i run a delete against a table that has 50 million records based
> upon a where clause , what entries does the T log file hold ? Does it log
50
> million delete statements along with 50 million inserts just incase it
needs
> to rollback.
>
The log holds a copy of the record which was deleted...
> B) Also if there is a clustered index on the coulimn thats part of the
where
> clause, what does the Tlog file hold ?
NO change, the log has the copy of the deleted record.
> C) If there was a clustered index but not part of the column in the where
> clause, what does the Tlog contain ?
no change.
> D) During the time the delete is occuring, does it go ahead and start
> deleting entries from the data pages in the data files or does it first
log
> entries in the Log file and then deletes ?
>
Logging occurs first.
> E) Finally if i did a backup log during the time the delete is occuring (
> Say i noticed that the table with (nolock) option was decrementing ) and
> then restored the logs on another database , will part of the deletes be
> reflected on the other database if i specify the (nolock) option since the
> delete did not complete
>
After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction.
> I am just trying to understand what entries the TLog contains.. I would
> appreciate if you could provide answers to all the 5 parts. I am using SQL
> 2000
> Thank you
>
>|||Thanks so to answer 5, where you said
"After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction."
What if i restored log with standby mode so users can read from this standby
database, will i see some deletes in effect with (nolock)
Thanks once again to you all
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eTPtkTCWDHA.2008@.TK2MSFTNGP11.phx.gbl...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> > A) When i run a delete against a table that has 50 million records based
> > upon a where clause , what entries does the T log file hold ? Does it
log
> 50
> > million delete statements along with 50 million inserts just incase it
> needs
> > to rollback.
> >
> The log holds a copy of the record which was deleted...
> >
> > B) Also if there is a clustered index on the coulimn thats part of the
> where
> > clause, what does the Tlog file hold ?
> NO change, the log has the copy of the deleted record.
>
> >
> > C) If there was a clustered index but not part of the column in the
where
> > clause, what does the Tlog contain ?
> no change.
> >
> > D) During the time the delete is occuring, does it go ahead and start
> > deleting entries from the data pages in the data files or does it first
> log
> > entries in the Log file and then deletes ?
> >
> Logging occurs first.
> > E) Finally if i did a backup log during the time the delete is occuring
(
> > Say i noticed that the table with (nolock) option was decrementing ) and
> > then restored the logs on another database , will part of the deletes be
> > reflected on the other database if i specify the (nolock) option since
the
> > delete did not complete
> >
> After the restore, and recovery has run either all of the records will be
> present or none of them...Each statement is a transaction.
> > I am just trying to understand what entries the TLog contains.. I would
> > appreciate if you could provide answers to all the 5 parts. I am using
SQL
> > 2000
> >
> > Thank you
> >
> >
> >
>sql

Degenerate dimension vs synthetic lookup

I'm trying to get a handle on a modeling question of sorts. Basically I have a fact table with ~ 10 million rows. This table has a column called lot_id for each of several components (lets say componentn_lot_id to be generic). My componentn_lot_id has ~ 1.5 million distinct values. Theoretically speaking is it better to treat this attribute as a degenerate dimension of the fact table or would it be better to synthesize a lookup by creating a named query like:

select component1_lot_id from fact_table group by 1

and using this as a lookup table for a regular dimension? I have about 12 dimensions like this, so the simplicity of the degenerate dimension method is nice, but seems to have a pretty high price to pay when processing the cube (though the lookup method may also be costly). Are there other issues to keep in mind here?

I'm seeking advice as to rules of thumb (or techniques) that should be used when faced with this decision. I am currently using AS2005.

Thanks,

Keehan

Are you using ROLAP or MOLAP?

For MOLAP storage, very probably there's no difference regarding processing time.

You might want to take some measurements anyway, if only to make sure scalability and sizing requirements are met...

Hope this helps

default port of MSDE

I know default port of MSSQL is 1433, but how about MSDE? How to change the
port no (if any)?
Million Thanks
Hi,
The default port for MSDE is 1433. You could change the port number by
running "svrnetcn.exe"
Thanks
Hari
MCDBA
"Grey" <erickwyum@.i-cable.com> wrote in message
news:#taJDibhEHA.216@.tk2msftngp13.phx.gbl...
> I know default port of MSSQL is 1433, but how about MSDE? How to change
the
> port no (if any)?
> Million Thanks
>
|||I thought that it picked whatever port number wasn't in use whenever it
started up? Or is that only for named instances? I don't think mine is ever
configured to use 1433 by itself.
"Hari Prasad" wrote:

> Hi,
> The default port for MSDE is 1433. You could change the port number by
> running "svrnetcn.exe"
> Thanks
> Hari
> MCDBA
>
>
> "Grey" <erickwyum@.i-cable.com> wrote in message
> news:#taJDibhEHA.216@.tk2msftngp13.phx.gbl...
> the
>
>
|||Andrew,
MSDE uses port 1433 by default. This can be changed by server network
utility to use any port that you want on startup. Named instances TCP port
is dynamic. You can see what port it used by looking in the SQL Server
Error log of that particular instance when Instance is running.
-Tom
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.