Alter table new column and update

Hi

for MS SQL 2000/2005

I am having a table (an old database, not mine) with char value for the column [localisation]

Users
[name] [nvarchar] (100) NOT NULL ,
[localisation] [nvarchar] (100)NULL

Now i have created a table [Localisation]

Localisation
[id_Localisation] [int] NOT NULL,
[localisation] [nvarchar] (100) NOT NULL

I am adding a new column to Users

ALTER TABLE [Users] ADD
[id_Localisation] int NULL

and I want to update the Column [Users].[id_Localisation] before to drop the column [Users].[Localisation]

something like

UPDATE [Users] SET id_Localisation = (SELECT Localisation.id_Localisation
FROM Localisation FULL OUTER JOIN
Users ON Localisation.Localisation = Users.Localisation)

Users.Localisation can have a NULL value (then no id_localisation return)

but it doesnt work because it returns > 1 row

thank you

how can I do it ?update [Users]
set id_Localisation = t2.id_Localisation
from [Users] t1
inner
join Localisation t2
on t1.Localisation = t2.Localisation|||it works perfectly

thanks a lot

do you thing i have to add a contrainst to this new column ?|||it would be a good idea to declare Users.id_Localisation as a foreign key|||but 5 tables are using this id_Localisation, can i add a FK to each one ?
FK_FK_Users_Localisation
FK_job_Localisation
FK_groups_Localisation
.....

if so

5 times (for each tables)

ALTER TABLE [Users] ADD
id_Localisation int NULL

ALTER TABLE [Users] WITH NOCHECK ADD
CONSTRAINT [FK_Users_Localisation] FOREIGN KEY
(
[id_Localisation]
) REFERENCES [Localisation] (
[id_Localisation]
)

I dont want to apply ON DELETE CASCADE , but to give a Id_localisation = 0 or NULL if a Localisation is deleted, how can i do it
??

thanks again for helping|||I dont want to apply ON DELETE CASCADE, but to give a Id_localisation = 0 or NULL if a Localisation is deleted
You can use ON DELETE SET NULL for that purpose|||but 5 tables are using this id_Localisation, can i add a FK to each one ?yes . ;)|||You can use ON DELETE SET NULL for that purposeunfortunately, not in SQL Server 2000, only in SQL Server 2005|||unfortunately, not in SQL Server 2000, only in SQL Server 2005Ah, right. I checked the wrong manual ;)|||well, i wouldn't exactly call it wrong -- i'm sure it's the right one for SQL Server 2005!!|||thank you

this application must work on 2000 and 2005

Alter table impossible due to replication

Hi !
I can't alter a table because of an old problematical replication.
I've already tried to clean the replication informations with
sp_removedbreplication and sp_msunmarkreplinfo. I've already update
sysobjects to set replinfo to 0 for the table that I try to update. I've
also the database from the replication configuration.
When I try to alter the table, I receive this message in french :
table 'Espaces'
- Impossible de modifier la table.
Erreur ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]chec de ALTER
TABLE DROP COLUMN car 'Esp_Ceremonie' est actuellement rpliqu.
In english, I think it would be : Error ALTER TABLE DROP COLUMN because
'xxxx' is at the moment replicated.
I hope someone can help me !
Thanks in advance !!!!
Bernard
bernard.borsu@.odysseos.net
can you try this:
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use your_database_name
go
update sysobjects set replinfo = 0 where name = 'your_table_name'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
(thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bernard Borsu" <bernard.borsu@.odysseos.net> wrote in message
news:ulGiJaA%23EHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi !
> I can't alter a table because of an old problematical replication.
> I've already tried to clean the replication informations with
> sp_removedbreplication and sp_msunmarkreplinfo. I've already update
> sysobjects to set replinfo to 0 for the table that I try to update. I've
> also the database from the replication configuration.
> When I try to alter the table, I receive this message in french :
> table 'Espaces'
> - Impossible de modifier la table.
> Erreur ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]chec de
ALTER
> TABLE DROP COLUMN car 'Esp_Ceremonie' est actuellement rpliqu.
> In english, I think it would be : Error ALTER TABLE DROP COLUMN because
> 'xxxx' is at the moment replicated.
> I hope someone can help me !
> Thanks in advance !!!!
> --
> Bernard
> bernard.borsu@.odysseos.net
>
|||Thanks for your solution, but i've already tried this solution and the error
message is always the same.
"Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de news:
Odw1pwB%23EHA.1296@.TK2MSFTNGP10.phx.gbl...
> can you try this:
> EXEC sp_configure 'allow',1
> go
> reconfigure with override
> go
> use your_database_name
> go
> update sysobjects set replinfo = 0 where name = 'your_table_name'
> go
> EXEC sp_configure 'allow',0
> go
> reconfigure with override
> go
> (thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Bernard Borsu" <bernard.borsu@.odysseos.net> wrote in message
> news:ulGiJaA%23EHA.2680@.TK2MSFTNGP09.phx.gbl...
> ALTER
>

ALTER PARTITION FUNCTION and I/O

We have a sliding window scenario where every day we add a new day and trim
off an old day from our partition function and scheme.
I read somewhere that in this scenario it is better to keep Partition1 empty
and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
SWITCHing out Partition1 and then MERGEing Partition 1. Reason being that you
will incurr additional I/O if you do it this way.
Before I test this, can anyone confirm this. We have 30 million row
partitions and can't afford any I/O while we MERGE Partition1. I am of the
opinion that provided Partition1 is empty, the MERGE will always be a
metadata operation only.
-- switch out Partition1 then MERGE
boundary_id row_count
1 30000000
2 30000000
3 30000000
4 30000000
5 30000000
6 30000000
7 30000000
-- or switch out partition2 then MERGE
boundary_id row_count
1 0
2 30000000
3 30000000
4 30000000
5 30000000
6 30000000
7 30000000
which is less I/O? I dont want to be moving data around on disk.
-- cranfield, DBA
> I read somewhere that in this scenario it is better to keep Partition1
> empty
> and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
> SWITCHing out Partition1 and then MERGEing Partition 1. Reason being that
> you
> will incurr additional I/O if you do it this way.
The partition that includes the specified boundary must be empty in order to
avoid data movement during MERGE.

> -- switch out Partition1 then MERGE
> boundary_id row_count
> 1 30000000
> 2 30000000
> 3 30000000
> 4 30000000
> 5 30000000
> 6 30000000
> 7 30000000
No data movement will be needed if the function is RANGE LEFT (inclusive).
Data will need to be moved if RANGE RIGHT because the first boundary
includes data in the second partition.

> -- or switch out partition2 then MERGE
> boundary_id row_count
> 1 0
> 2 30000000
> 3 30000000
> 4 30000000
> 5 30000000
> 6 30000000
> 7 30000000
No data movement will be needed because both partitions 1 and 2 will be
empty during the MERGE. No data movement is needed in this case regardless
of LEFT or RIGHT.
Regarding general sliding window approaches, one usually wants to keep all
values for a given date in the same partition. The 2 basic techniques to
accomplish an efficient datetime based sliding window:
Method 1:
Specify RANGE LEFT with boundary values that includes the highest possible
datetime value (e.g. '2008-02-29T23:59:59.997'). To remove old data, switch
out partition 1 and MERGE the first partition.
Method 2:
Specify RANGE RIGHT with boundary values of the next date (e.g.
'2008-03-01T00:00:00'). To remove old data, switch out partition 2 and
MERGE the first partition. Partition 1 is always empty with this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:A75BDFAA-9492-455A-92BE-12F4FFBCCED9@.microsoft.com...
> We have a sliding window scenario where every day we add a new day and
> trim
> off an old day from our partition function and scheme.
> I read somewhere that in this scenario it is better to keep Partition1
> empty
> and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
> SWITCHing out Partition1 and then MERGEing Partition 1. Reason being that
> you
> will incurr additional I/O if you do it this way.
> Before I test this, can anyone confirm this. We have 30 million row
> partitions and can't afford any I/O while we MERGE Partition1. I am of the
> opinion that provided Partition1 is empty, the MERGE will always be a
> metadata operation only.
> -- switch out Partition1 then MERGE
> boundary_id row_count
> 1 30000000
> 2 30000000
> 3 30000000
> 4 30000000
> 5 30000000
> 6 30000000
> 7 30000000
> -- or switch out partition2 then MERGE
> boundary_id row_count
> 1 0
> 2 30000000
> 3 30000000
> 4 30000000
> 5 30000000
> 6 30000000
> 7 30000000
> which is less I/O? I dont want to be moving data around on disk.
> --
> -- cranfield, DBA
|||Hi Dan
Thankls for that. We've used RANGE RIGHT as we have the concept of a
Trading Day which is all data up to 21h30. Anything that comes in after that
goes to the next partition. In addition we have binary date representation as
our partition key. We will make sure we keep Partition1 empty and SWITCH OUT
Partition2 and then MERGE.
--e.g.
CREATE PARTITION FUNCTION [MyFunc](binary(16))
AS RANGE RIGHT FOR VALUES (
0x47966058000000000000000000000000,
0x4797B1D8000000000000000000000000,
0x47990358000000000000000000000000,
0x479A54D8000000000000000000000000,
0x479BA658000000000000000000000000,
0x479CF7D8000000000000000000000000,
0x479E4958000000000000000000000000,
0x479F9AD8000000000000000000000000,
0x47A0EC58000000000000000000000000,
0x47A23DD8000000000000000000000000,
0x47A38F58000000000000000000000000,
0x47A4E0D8000000000000000000000000,
0x47A63258000000000000000000000000,
0x47A783D8000000000000000000000000,
0x47A8D558000000000000000000000000,
0x47AA26D8000000000000000000000000,
0x47AB7858000000000000000000000000
)
-- equates to:
2008-01-22 21:30:00.000
2008-01-23 21:30:00.000
2008-01-24 21:30:00.000
2008-01-25 21:30:00.000
2008-01-26 21:30:00.000
2008-01-27 21:30:00.000
2008-01-28 21:30:00.000
2008-01-29 21:30:00.000
2008-01-30 21:30:00.000
2008-01-31 21:30:00.000
2008-02-01 21:30:00.000
2008-02-02 21:30:00.000
2008-02-03 21:30:00.000
2008-02-04 21:30:00.000
2008-02-05 21:30:00.000
-- cranfield, DBA
"Dan Guzman" wrote:

> The partition that includes the specified boundary must be empty in order to
> avoid data movement during MERGE.
>
> No data movement will be needed if the function is RANGE LEFT (inclusive).
> Data will need to be moved if RANGE RIGHT because the first boundary
> includes data in the second partition.
>
> No data movement will be needed because both partitions 1 and 2 will be
> empty during the MERGE. No data movement is needed in this case regardless
> of LEFT or RIGHT.
> Regarding general sliding window approaches, one usually wants to keep all
> values for a given date in the same partition. The 2 basic techniques to
> accomplish an efficient datetime based sliding window:
> Method 1:
> Specify RANGE LEFT with boundary values that includes the highest possible
> datetime value (e.g. '2008-02-29T23:59:59.997'). To remove old data, switch
> out partition 1 and MERGE the first partition.
> Method 2:
> Specify RANGE RIGHT with boundary values of the next date (e.g.
> '2008-03-01T00:00:00'). To remove old data, switch out partition 2 and
> MERGE the first partition. Partition 1 is always empty with this approach.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:A75BDFAA-9492-455A-92BE-12F4FFBCCED9@.microsoft.com...
>
|||> We will make sure we keep Partition1 empty and SWITCH OUT
> Partition2 and then MERGE.
It looks like you are in good shape then. I'm glad I was able to help out
out.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:34841B67-89A1-4AF0-8069-EE03F709B923@.microsoft.com...[vbcol=seagreen]
> Hi Dan
> Thankls for that. We've used RANGE RIGHT as we have the concept of a
> Trading Day which is all data up to 21h30. Anything that comes in after
> that
> goes to the next partition. In addition we have binary date representation
> as
> our partition key. We will make sure we keep Partition1 empty and SWITCH
> OUT
> Partition2 and then MERGE.
> --e.g.
> CREATE PARTITION FUNCTION [MyFunc](binary(16))
> AS RANGE RIGHT FOR VALUES (
> 0x47966058000000000000000000000000,
> 0x4797B1D8000000000000000000000000,
> 0x47990358000000000000000000000000,
> 0x479A54D8000000000000000000000000,
> 0x479BA658000000000000000000000000,
> 0x479CF7D8000000000000000000000000,
> 0x479E4958000000000000000000000000,
> 0x479F9AD8000000000000000000000000,
> 0x47A0EC58000000000000000000000000,
> 0x47A23DD8000000000000000000000000,
> 0x47A38F58000000000000000000000000,
> 0x47A4E0D8000000000000000000000000,
> 0x47A63258000000000000000000000000,
> 0x47A783D8000000000000000000000000,
> 0x47A8D558000000000000000000000000,
> 0x47AA26D8000000000000000000000000,
> 0x47AB7858000000000000000000000000
> )
>
> -- equates to:
> 2008-01-22 21:30:00.000
> 2008-01-23 21:30:00.000
> 2008-01-24 21:30:00.000
> 2008-01-25 21:30:00.000
> 2008-01-26 21:30:00.000
> 2008-01-27 21:30:00.000
> 2008-01-28 21:30:00.000
> 2008-01-29 21:30:00.000
> 2008-01-30 21:30:00.000
> 2008-01-31 21:30:00.000
> 2008-02-01 21:30:00.000
> 2008-02-02 21:30:00.000
> 2008-02-03 21:30:00.000
> 2008-02-04 21:30:00.000
> 2008-02-05 21:30:00.000
>
> --
> -- cranfield, DBA
>
> "Dan Guzman" wrote:

ALTER PARTITION FUNCTION and I/O

We have a sliding window scenario where every day we add a new day and trim
off an old day from our partition function and scheme.
I read somewhere that in this scenario it is better to keep Partition1 empty
and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
SWITCHing out Partition1 and then MERGEing Partition 1. Reason being that you
will incurr additional I/O if you do it this way.
Before I test this, can anyone confirm this. We have 30 million row
partitions and can't afford any I/O while we MERGE Partition1. I am of the
opinion that provided Partition1 is empty, the MERGE will always be a
metadata operation only.
-- switch out Partition1 then MERGE
boundary_id row_count
1 30000000
2 30000000
3 30000000
4 30000000
5 30000000
6 30000000
7 30000000
-- or switch out partition2 then MERGE
boundary_id row_count
1 0
2 30000000
3 30000000
4 30000000
5 30000000
6 30000000
7 30000000
which is less I/O? I dont want to be moving data around on disk.
--
-- cranfield, DBA> I read somewhere that in this scenario it is better to keep Partition1
> empty
> and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
> SWITCHing out Partition1 and then MERGEing Partition 1. Reason being that
> you
> will incurr additional I/O if you do it this way.
The partition that includes the specified boundary must be empty in order to
avoid data movement during MERGE.
> -- switch out Partition1 then MERGE
> boundary_id row_count
> 1 30000000
> 2 30000000
> 3 30000000
> 4 30000000
> 5 30000000
> 6 30000000
> 7 30000000
No data movement will be needed if the function is RANGE LEFT (inclusive).
Data will need to be moved if RANGE RIGHT because the first boundary
includes data in the second partition.
> -- or switch out partition2 then MERGE
> boundary_id row_count
> 1 0
> 2 30000000
> 3 30000000
> 4 30000000
> 5 30000000
> 6 30000000
> 7 30000000
No data movement will be needed because both partitions 1 and 2 will be
empty during the MERGE. No data movement is needed in this case regardless
of LEFT or RIGHT.
Regarding general sliding window approaches, one usually wants to keep all
values for a given date in the same partition. The 2 basic techniques to
accomplish an efficient datetime based sliding window:
Method 1:
Specify RANGE LEFT with boundary values that includes the highest possible
datetime value (e.g. '2008-02-29T23:59:59.997'). To remove old data, switch
out partition 1 and MERGE the first partition.
Method 2:
Specify RANGE RIGHT with boundary values of the next date (e.g.
'2008-03-01T00:00:00'). To remove old data, switch out partition 2 and
MERGE the first partition. Partition 1 is always empty with this approach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:A75BDFAA-9492-455A-92BE-12F4FFBCCED9@.microsoft.com...
> We have a sliding window scenario where every day we add a new day and
> trim
> off an old day from our partition function and scheme.
> I read somewhere that in this scenario it is better to keep Partition1
> empty
> and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
> SWITCHing out Partition1 and then MERGEing Partition 1. Reason being that
> you
> will incurr additional I/O if you do it this way.
> Before I test this, can anyone confirm this. We have 30 million row
> partitions and can't afford any I/O while we MERGE Partition1. I am of the
> opinion that provided Partition1 is empty, the MERGE will always be a
> metadata operation only.
> -- switch out Partition1 then MERGE
> boundary_id row_count
> 1 30000000
> 2 30000000
> 3 30000000
> 4 30000000
> 5 30000000
> 6 30000000
> 7 30000000
> -- or switch out partition2 then MERGE
> boundary_id row_count
> 1 0
> 2 30000000
> 3 30000000
> 4 30000000
> 5 30000000
> 6 30000000
> 7 30000000
> which is less I/O? I dont want to be moving data around on disk.
> --
> -- cranfield, DBA|||Hi Dan
Thankls for that. We've used RANGE RIGHT as we have the concept of a
Trading Day which is all data up to 21h30. Anything that comes in after that
goes to the next partition. In addition we have binary date representation as
our partition key. We will make sure we keep Partition1 empty and SWITCH OUT
Partition2 and then MERGE.
--e.g.
CREATE PARTITION FUNCTION [MyFunc](binary(16))
AS RANGE RIGHT FOR VALUES (
0x47966058000000000000000000000000,
0x4797B1D8000000000000000000000000,
0x47990358000000000000000000000000,
0x479A54D8000000000000000000000000,
0x479BA658000000000000000000000000,
0x479CF7D8000000000000000000000000,
0x479E4958000000000000000000000000,
0x479F9AD8000000000000000000000000,
0x47A0EC58000000000000000000000000,
0x47A23DD8000000000000000000000000,
0x47A38F58000000000000000000000000,
0x47A4E0D8000000000000000000000000,
0x47A63258000000000000000000000000,
0x47A783D8000000000000000000000000,
0x47A8D558000000000000000000000000,
0x47AA26D8000000000000000000000000,
0x47AB7858000000000000000000000000
)
-- equates to:
2008-01-22 21:30:00.000
2008-01-23 21:30:00.000
2008-01-24 21:30:00.000
2008-01-25 21:30:00.000
2008-01-26 21:30:00.000
2008-01-27 21:30:00.000
2008-01-28 21:30:00.000
2008-01-29 21:30:00.000
2008-01-30 21:30:00.000
2008-01-31 21:30:00.000
2008-02-01 21:30:00.000
2008-02-02 21:30:00.000
2008-02-03 21:30:00.000
2008-02-04 21:30:00.000
2008-02-05 21:30:00.000
-- cranfield, DBA
"Dan Guzman" wrote:
> > I read somewhere that in this scenario it is better to keep Partition1
> > empty
> > and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
> > SWITCHing out Partition1 and then MERGEing Partition 1. Reason being that
> > you
> > will incurr additional I/O if you do it this way.
> The partition that includes the specified boundary must be empty in order to
> avoid data movement during MERGE.
> > -- switch out Partition1 then MERGE
> > boundary_id row_count
> > 1 30000000
> > 2 30000000
> > 3 30000000
> > 4 30000000
> > 5 30000000
> > 6 30000000
> > 7 30000000
> No data movement will be needed if the function is RANGE LEFT (inclusive).
> Data will need to be moved if RANGE RIGHT because the first boundary
> includes data in the second partition.
> > -- or switch out partition2 then MERGE
> > boundary_id row_count
> > 1 0
> > 2 30000000
> > 3 30000000
> > 4 30000000
> > 5 30000000
> > 6 30000000
> > 7 30000000
> No data movement will be needed because both partitions 1 and 2 will be
> empty during the MERGE. No data movement is needed in this case regardless
> of LEFT or RIGHT.
> Regarding general sliding window approaches, one usually wants to keep all
> values for a given date in the same partition. The 2 basic techniques to
> accomplish an efficient datetime based sliding window:
> Method 1:
> Specify RANGE LEFT with boundary values that includes the highest possible
> datetime value (e.g. '2008-02-29T23:59:59.997'). To remove old data, switch
> out partition 1 and MERGE the first partition.
> Method 2:
> Specify RANGE RIGHT with boundary values of the next date (e.g.
> '2008-03-01T00:00:00'). To remove old data, switch out partition 2 and
> MERGE the first partition. Partition 1 is always empty with this approach.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:A75BDFAA-9492-455A-92BE-12F4FFBCCED9@.microsoft.com...
> > We have a sliding window scenario where every day we add a new day and
> > trim
> > off an old day from our partition function and scheme.
> >
> > I read somewhere that in this scenario it is better to keep Partition1
> > empty
> > and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
> > SWITCHing out Partition1 and then MERGEing Partition 1. Reason being that
> > you
> > will incurr additional I/O if you do it this way.
> >
> > Before I test this, can anyone confirm this. We have 30 million row
> > partitions and can't afford any I/O while we MERGE Partition1. I am of the
> > opinion that provided Partition1 is empty, the MERGE will always be a
> > metadata operation only.
> >
> > -- switch out Partition1 then MERGE
> > boundary_id row_count
> > 1 30000000
> > 2 30000000
> > 3 30000000
> > 4 30000000
> > 5 30000000
> > 6 30000000
> > 7 30000000
> >
> > -- or switch out partition2 then MERGE
> > boundary_id row_count
> > 1 0
> > 2 30000000
> > 3 30000000
> > 4 30000000
> > 5 30000000
> > 6 30000000
> > 7 30000000
> >
> > which is less I/O? I dont want to be moving data around on disk.
> > --
> > -- cranfield, DBA
>|||> We will make sure we keep Partition1 empty and SWITCH OUT
> Partition2 and then MERGE.
It looks like you are in good shape then. I'm glad I was able to help out
out.
--
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:34841B67-89A1-4AF0-8069-EE03F709B923@.microsoft.com...
> Hi Dan
> Thankls for that. We've used RANGE RIGHT as we have the concept of a
> Trading Day which is all data up to 21h30. Anything that comes in after
> that
> goes to the next partition. In addition we have binary date representation
> as
> our partition key. We will make sure we keep Partition1 empty and SWITCH
> OUT
> Partition2 and then MERGE.
> --e.g.
> CREATE PARTITION FUNCTION [MyFunc](binary(16))
> AS RANGE RIGHT FOR VALUES (
> 0x47966058000000000000000000000000,
> 0x4797B1D8000000000000000000000000,
> 0x47990358000000000000000000000000,
> 0x479A54D8000000000000000000000000,
> 0x479BA658000000000000000000000000,
> 0x479CF7D8000000000000000000000000,
> 0x479E4958000000000000000000000000,
> 0x479F9AD8000000000000000000000000,
> 0x47A0EC58000000000000000000000000,
> 0x47A23DD8000000000000000000000000,
> 0x47A38F58000000000000000000000000,
> 0x47A4E0D8000000000000000000000000,
> 0x47A63258000000000000000000000000,
> 0x47A783D8000000000000000000000000,
> 0x47A8D558000000000000000000000000,
> 0x47AA26D8000000000000000000000000,
> 0x47AB7858000000000000000000000000
> )
>
> -- equates to:
> 2008-01-22 21:30:00.000
> 2008-01-23 21:30:00.000
> 2008-01-24 21:30:00.000
> 2008-01-25 21:30:00.000
> 2008-01-26 21:30:00.000
> 2008-01-27 21:30:00.000
> 2008-01-28 21:30:00.000
> 2008-01-29 21:30:00.000
> 2008-01-30 21:30:00.000
> 2008-01-31 21:30:00.000
> 2008-02-01 21:30:00.000
> 2008-02-02 21:30:00.000
> 2008-02-03 21:30:00.000
> 2008-02-04 21:30:00.000
> 2008-02-05 21:30:00.000
>
> --
> -- cranfield, DBA
>
> "Dan Guzman" wrote:
>> > I read somewhere that in this scenario it is better to keep Partition1
>> > empty
>> > and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
>> > SWITCHing out Partition1 and then MERGEing Partition 1. Reason being
>> > that
>> > you
>> > will incurr additional I/O if you do it this way.
>> The partition that includes the specified boundary must be empty in order
>> to
>> avoid data movement during MERGE.
>> > -- switch out Partition1 then MERGE
>> > boundary_id row_count
>> > 1 30000000
>> > 2 30000000
>> > 3 30000000
>> > 4 30000000
>> > 5 30000000
>> > 6 30000000
>> > 7 30000000
>> No data movement will be needed if the function is RANGE LEFT
>> (inclusive).
>> Data will need to be moved if RANGE RIGHT because the first boundary
>> includes data in the second partition.
>> > -- or switch out partition2 then MERGE
>> > boundary_id row_count
>> > 1 0
>> > 2 30000000
>> > 3 30000000
>> > 4 30000000
>> > 5 30000000
>> > 6 30000000
>> > 7 30000000
>> No data movement will be needed because both partitions 1 and 2 will be
>> empty during the MERGE. No data movement is needed in this case
>> regardless
>> of LEFT or RIGHT.
>> Regarding general sliding window approaches, one usually wants to keep
>> all
>> values for a given date in the same partition. The 2 basic techniques to
>> accomplish an efficient datetime based sliding window:
>> Method 1:
>> Specify RANGE LEFT with boundary values that includes the highest
>> possible
>> datetime value (e.g. '2008-02-29T23:59:59.997'). To remove old data,
>> switch
>> out partition 1 and MERGE the first partition.
>> Method 2:
>> Specify RANGE RIGHT with boundary values of the next date (e.g.
>> '2008-03-01T00:00:00'). To remove old data, switch out partition 2 and
>> MERGE the first partition. Partition 1 is always empty with this
>> approach.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:A75BDFAA-9492-455A-92BE-12F4FFBCCED9@.microsoft.com...
>> > We have a sliding window scenario where every day we add a new day and
>> > trim
>> > off an old day from our partition function and scheme.
>> >
>> > I read somewhere that in this scenario it is better to keep Partition1
>> > empty
>> > and always SWITCH out Partition2 and then MERGE Partition 1. Instead of
>> > SWITCHing out Partition1 and then MERGEing Partition 1. Reason being
>> > that
>> > you
>> > will incurr additional I/O if you do it this way.
>> >
>> > Before I test this, can anyone confirm this. We have 30 million row
>> > partitions and can't afford any I/O while we MERGE Partition1. I am of
>> > the
>> > opinion that provided Partition1 is empty, the MERGE will always be a
>> > metadata operation only.
>> >
>> > -- switch out Partition1 then MERGE
>> > boundary_id row_count
>> > 1 30000000
>> > 2 30000000
>> > 3 30000000
>> > 4 30000000
>> > 5 30000000
>> > 6 30000000
>> > 7 30000000
>> >
>> > -- or switch out partition2 then MERGE
>> > boundary_id row_count
>> > 1 0
>> > 2 30000000
>> > 3 30000000
>> > 4 30000000
>> > 5 30000000
>> > 6 30000000
>> > 7 30000000
>> >
>> > which is less I/O? I dont want to be moving data around on disk.
>> > --
>> > -- cranfield, DBA

Alphanumeric Autonumber Primary Key

Hi there,
The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002

Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?the only sane way of doing it is to have an ordinary integer identity column, then produce the alphanumeric value in a view

create view myview as
select 'ABC'+right(cast(pkey as varchar(9)),4) as myalnumkey ...

Almost Replicated - I think

I have a new server, new instance of SQL on the network
with an old server,old SQL db. I attempted to set up
replication by creating a snapshot and making new server
(Win 2k3) a subscriber to publisher/distributor. I get
the following error:
Invalid column name ', '.
(Source: NewServer (Data source); Error number: 207)
Confused (1) because NewServer had nothing on it (only
standard SQL install db and (2) don't know where to go
next.
Any help, ideas?
TIA
Rob
Rob,
if you have been replicating a view then I have seen this before. This
problem occurs because the Snapshot Agent always sets the QUOTED_IDENTIFIER
option to ON, regardless of the actual setting. Therefore, if the stored
procedures or views use double quotation marks, the Distribution Agent or
the Merge Agent assumes the default behavior of using double quotation marks
for identifiers only. To get round this, you can change the object script to
refer to literals using single quotes, or use DTS to transfer the objects.
If this is not the issue, I came across this error in merge replication that
might be of use:
http://support.microsoft.com/default...b;en-us;821535
HTH,
Paul Ibison

All subscriptions will not run finish- status stays set to New

All of my subscriptions, old and new, will not run. They were working
last week, and nothing should have changed that would affect
subscriptions.
The status stays set to "New Subscription" and nothing gets logged in
the logs. I looked through the SQL Jobs and watched it as I made a
new subscription- this ran successfully (no sql errors and status was
"Succeeded"), yet nothing was written to the directory and the status
didn't change. I have tried various directories and I have also
verified that the timezone is correct.
What else can I check?
Thanks for your help.I was able to figure it. I noticed that the reportserverservice logs
were not being created. I restarted the reportserver windows service
and that fixed the problem.
On Jul 17, 10:28 am, Just Another Reporter <Crystal.War...@.gmail.com>
wrote:
> All of my subscriptions, old and new, will not run. They were working
> last week, and nothing should have changed that would affect
> subscriptions.
> The status stays set to "New Subscription" and nothing gets logged in
> the logs. I looked through the SQL Jobs and watched it as I made a
> new subscription- this ran successfully (no sql errors and status was
> "Succeeded"), yet nothing was written to the directory and the status
> didn't change. I have tried various directories and I have also
> verified that the timezone is correct.
> What else can I check?
> Thanks for your help.