Mirrored Records?

Hi I need some SQL script help. Need script to delete all table rows that
are duplicates in mirror image. Table has 2 columns, ColumnA and ColumnB.
Row1: ColumnA = x, ColumnB = y
Row2: ColumnA = y, ColumnB = x
Those 2 rows are exactly the same for me. Need a script that will delete
Row2 and all other rows where they're mirrored duplicates.
Thanks DarenDaren,
Once you do this, you should consider putting a constraint on the
table to enforce ColumnA <= ColumnB so this doesn't happen again.
It's a bad model if there are two ways of representing the same facts.
delete from T as T1
where ColumnA > ColumnB
and exists (
select * from T as T2
where T2.ColumnA = T1.ColumnB
and T2.ColumnB = T1.ColumnA
)
If you want to delete only these "mirror duplicates"
when they exist for the same customer/transaction/whatever,
you will need something like
delete from T as T1
where ColumnA > ColumnB
and exists (
select * from T as T2
where T2.customer = T1.customer
and T2.ColumnA = T1.ColumnB
and T2.ColumnB = T1.ColumnA
)
Steve Kass
Drew University
Daren Hawes wrote:

>Hi I need some SQL script help. Need script to delete all table rows that
>are duplicates in mirror image. Table has 2 columns, ColumnA and ColumnB.
>Row1: ColumnA = x, ColumnB = y
>Row2: ColumnA = y, ColumnB = x
>Those 2 rows are exactly the same for me. Need a script that will delete
>Row2 and all other rows where they're mirrored duplicates.
>Thanks Daren
>
>|||Thanks. I am a little with T and T2
Do I need to create a new Table? I have added the actual names below.
Column a = FromID ; Column b = ToID
CREATE TABLE [dbo].[tbl_Matrix] (
[FareID] [int] IDENTITY (1, 1) NOT NULL ,
[FromID] [int] NULL ,
[ToID] [int] NULL ,
[PriceCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_Matrix] ADD
CONSTRAINT [PK_tbl_Matrix] PRIMARY KEY CLUSTERED
(
[FareID]
) ON [PRIMARY]
GO
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23eKPQBxZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Daren,
> Once you do this, you should consider putting a constraint on the
> table to enforce ColumnA <= ColumnB so this doesn't happen again.
> It's a bad model if there are two ways of representing the same facts.
> delete from T as T1
> where ColumnA > ColumnB
> and exists (
> select * from T as T2
> where T2.ColumnA = T1.ColumnB
> and T2.ColumnB = T1.ColumnA
> )
> If you want to delete only these "mirror duplicates"
> when they exist for the same customer/transaction/whatever,
> you will need something like
> delete from T as T1
> where ColumnA > ColumnB
> and exists (
> select * from T as T2
> where T2.customer = T1.customer
> and T2.ColumnA = T1.ColumnB
> and T2.ColumnB = T1.ColumnA
> )
> Steve Kass
> Drew University
>
> Daren Hawes wrote:
>|||Try the following untested DELETE statement:
DELETE FROM tbl_Matrix
WHERE EXISTS
(SELECT *
FROM tbl_Matrix AS T
WHERE T.fromid = tbl_Matrix.fromid
AND T.toid = tbl_Matrix.toid
AND T.fareid < tbl_Matrix.fareid)
Now make FromID and ToID not nullable and add a unqiue constraint on those
two columns.
David Portas
SQL Server MVP
--|||Thanks for the reply Dave (I'm working with Daren),
Tryed that 1, deleted nothing, modified it to this...
DELETE FROM tbl_Matrix
WHERE EXISTS
(SELECT *
FROM tbl_Matrix AS T
WHERE T.fromid = tbl_Matrix.toid
AND T.toid = tbl_Matrix.fromid)
...and it deleted everything.
This is a copy of the script we've used to create the table's data, perhaps
if we wrote this script a bit better, wouldn't need another script to clean
it up...
declare @.Counter int
declare @.Counter2 int
select @.Counter=1
select @.Counter2=1
while @.Counter < 136
begin
while @.Counter2 < 136
Begin
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
set @.Counter2 = @.Counter2 + 1
End
set @.Counter = @.Counter + 1
set @.Counter2 = 1
end
Regards,
Offal Eater
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
> Try the following untested DELETE statement:
> DELETE FROM tbl_Matrix
> WHERE EXISTS
> (SELECT *
> FROM tbl_Matrix AS T
> WHERE T.fromid = tbl_Matrix.fromid
> AND T.toid = tbl_Matrix.toid
> AND T.fareid < tbl_Matrix.fareid)
> Now make FromID and ToID not nullable and add a unqiue constraint on those
> two columns.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for the reply Dave (I'm working with Daren),
Not having any luck with these scripts, I've included the script we've used
to create the table. Perhaps if we wrote this differently, wouldn't have
need for the 'clean-up' script.
declare @.Counter int
declare @.Counter2 int
select @.Counter=1
select @.Counter2=1
while @.Counter < 136
begin
while @.Counter2 < 136
Begin
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
set @.Counter2 = @.Counter2 + 1
End
set @.Counter = @.Counter + 1
set @.Counter2 = 1
end
Any feedback would be great.
Thx,
Offal Eater
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
> Try the following untested DELETE statement:
> DELETE FROM tbl_Matrix
> WHERE EXISTS
> (SELECT *
> FROM tbl_Matrix AS T
> WHERE T.fromid = tbl_Matrix.fromid
> AND T.toid = tbl_Matrix.toid
> AND T.fareid < tbl_Matrix.fareid)
> Now make FromID and ToID not nullable and add a unqiue constraint on those
> two columns.
> --
> David Portas
> SQL Server MVP
> --
>|||T1 and T2 are table aliases. You need to compare each
row of T with the rest of T, and to distinguish the "this row"
table and the "rest of" table, you need the aliases:
Since FareID is the primary key, this will do
delete from tbl_Matrix as T1
where ToID > FromID
and exists (
select * from tbl_Matrix as T2
where T2.FromID = T1.ToID
and T2.ToID = T1.FromID
)
This will arbitrarily remove the one of the two mirror duplicates
for which ToID < FromID. If you want to remove, say, the
one with the smaller FareID, you could do this instead:
delete from tbl_Matrix as T1
where exists (
select * from tbl_Matrix as T2
where T2.FromID = T1.ToID
and T2.ToID = T1.FromID
and T2.FareID > T1.FareID
)
Seeing your table now, I'd recommend you change the primary
key to (FromID, ToID), and add a table constraint to enforce
FromID < ToID, or at least put a UNIQUE constraint on
(FromID, ToID), and make those two columns NOT NULL (if
not all columns). I don't see much use to the column FareID,
actually, but removing it might mess up other thing you rely on.
There could be other problems, too. You could have many rows
with the same FromID, ToID pair in the same order, but with
different FareID values. So you might want to do this:
delete from tbl_Matrix as T1
where exists (
select * from tbl_Matrix as T2
where (
T2.FromID = T1.ToID
and T2.ToID = T1.FromID
) or (
T2.FromID = T1.FromID
and T2.ToID = T1.ToID
)
and T2.FareID > T1.FareID
)
If you have rows where FromID = ToID, you can delete them
separately. I assume those would make no sense, but you have
nothing to prevent that from occurring right now.
SK
Daren Hawes wrote:

>Thanks. I am a little with T and T2
>Do I need to create a new Table? I have added the actual names below.
>Column a = FromID ; Column b = ToID
>CREATE TABLE [dbo].[tbl_Matrix] (
> [FareID] [int] IDENTITY (1, 1) NOT NULL ,
> [FromID] [int] NULL ,
> [ToID] [int] NULL ,
> [PriceCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[tbl_Matrix] ADD
> CONSTRAINT [PK_tbl_Matrix] PRIMARY KEY CLUSTERED
> (
> [FareID]
> ) ON [PRIMARY]
>GO
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23eKPQBxZFHA.2212@.TK2MSFTNGP14.phx.gbl...
>
>
>|||> Tryed that 1, deleted nothing, modified it to this...
I ran your script and it didn't generate any duplicates so there is nothing
to delete - the combination of (FromID, ToID) is already unique (18225
rows). If you have something different in your data then please post a few
INSERT statements to generate some sample data that we can use to test it
out.

> This is a copy of the script we've used to create the table's data,
> perhaps if we wrote this script a bit better, wouldn't need another script
> to clean it up...
I posted a different solution to this in reply to Daren's post earlier
today.
David Portas
SQL Server MVP
--|||Here's a much better way to create the table. It uses
just one insert and no loops, and has much less chance of
off-by-one errors:
-- create a temporary table full of integers
declare @.Ints table (
IntVal int primary key
)
insert into @.Ints
select OrderID-10247
from Northwind..Orders
where OrderID-10247 between 1 and 136
-- Insert everything at once:
insert into dbo.tbl_Matrix(FromID, ToID)
select N1.IntVal, N2.IntVal
from @.Ints as N1 join @.Ints as N2
on N1.IntVal between 1 and 136
and N2.IntVal between N1.IntVal and 136
-- or N2.IntVal between N1.IntVal+1 and 136
If you want loops, but no mirror duplicates, replace
set @.Counter2 = 1
with
set @.Counter2 = @.Counter
This will continue to give you rows where FromID = ToID. If you don't
want those, start @.Counter2 one higher. Here's a guess, cleaned up
a bit, but I'd never trust this as much as the first suggestion I gave.
declare @.Counter int
declare @.Counter2 int
select @.Counter=0
while @.Counter < 135
begin
set @.Counter = @.Counter + 1
set @.Counter2 = @.Counter
while @.Counter2 < 135
Begin
set @.Counter2 = @.Counter2 + 1
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
End
end
SK
Offal Eater wrote:

>Thanks for the reply Dave (I'm working with Daren),
>Not having any luck with these scripts, I've included the script we've used
>to create the table. Perhaps if we wrote this differently, wouldn't have
>need for the 'clean-up' script.
>declare @.Counter int
>declare @.Counter2 int
>select @.Counter=1
>select @.Counter2=1
>while @.Counter < 136
>begin
> while @.Counter2 < 136
> Begin
> Insert into dbo.tbl_Matrix (FromID,ToID)
> Values (@.Counter,@.Counter2)
> set @.Counter2 = @.Counter2 + 1
> End
>set @.Counter = @.Counter + 1
>set @.Counter2 = 1
>end
>Any feedback would be great.
>Thx,
>Offal Eater
>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
>
>
>|||David,
I don't think the OP wants both (a, b) and (b, a). The 18225 rows
from the script are all (a,b) where a and b are between 1 and 135.
One way to eliminate the "mirror duplicates" is to generate only pairs
where a <= b (or perhaps a < b). There should be either 9180 or
9045 rows, depending on whether = is included.
SK
David Portas wrote:

>I ran your script and it didn't generate any duplicates so there is nothing
>to delete - the combination of (FromID, ToID) is already unique (18225
>rows). If you have something different in your data then please post a few
>INSERT statements to generate some sample data that we can use to test it
>out.
>
>
>I posted a different solution to this in reply to Daren's post earlier
>today.
>
>

minute count query

I need a query that gives me the sum of every rows (time column) with lower 'rownr'

the result:
rownr time timesum
1 10 0
2 10 10
3 10 20
4 10 30
5 10 40
6 10 50
7 10 60
8 10 70

current table looks like this:
rownr time
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10

and i want the 'timesum' column to be in format hhhh:mm
current format is rownr=int, time=datetime

thx for all help

//MrDo you have the URL or a PDF for this assignment?

-PatP|||:( no im making a database to store my divelogs in and this is the accumulated time im trying to calculate...|||I have no idea if this is what you are looking for. Nevertheless, to try and guess what the solution to your problem might be I went ahead and created a test database called MiscTests.

Now, I created a table similar to yours but instead of having the time column as datetime I've changed it to an INT. Here's the table code:

USE [MiscTests]
GO
/****** Object: Table [dbo].[Times] Script Date: 08/22/2007 17:50:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Times](
[rownr] [int] IDENTITY(1,1) NOT NULL,
[time] [int] NOT NULL CONSTRAINT [DF_Times_time] DEFAULT ((0)),
CONSTRAINT [PK_Times] PRIMARY KEY CLUSTERED
(
[rownr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

With this table in place I've made a small scalar-valued function to parse the time column. The code of the SVF is as follows:

USE [MiscTests]
GO
/****** Object: UserDefinedFunction [dbo].[parseTime] Script Date: 08/22/2007 17:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[parseTime]
(
@.Time AS Int
)
RETURNS VarChar(10)
AS
BEGIN
DECLARE @.hours AS Int
, @.parsedTime AS VarChar(10);

SET @.hours = 0;

WHILE (@.Time >= 60)
BEGIN
SET @.hours = (@.hours + 1);

SET @.Time = (@.Time - 60);
END

IF (@.hours = 0)
BEGIN
SET @.parsedTime = '0';
END
ELSE
BEGIN
SET @.parsedTime = CAST(@.hours AS VarChar(7));
END

SET @.parsedTime = @.parsedTime + ':';

IF (@.Time < 10)
BEGIN
SET @.parsedTime = @.parsedTime + '0'
END

SET @.parsedTime = @.parsedTime + CAST(@.Time AS VarChar(2));

return @.parsedTime;
END
GO

With this done I went ahead and filled the dbo.Times table with a couple of datarows. After that I ran a simple query to return the desired output. Here's the query SQL

SELECT
t.rownr AS rownr
, t.time AS [time]
, dbo.parseTime((
SELECT
sum(st.time)
FROM
dbo.Times st
WHERE
st.rownr <= t.rownr
)) as timesum
FROM
dbo.Times t
ORDER BY
t.rownr ASC;

The resultset should be something like this:
1 0 0:00
2 10 0:10
3 10 0:20
4 10 0:30
5 10 0:40
6 10 0:50
7 10 1:00
8 10 1:10
9 10 1:20
10 10 1:30
11 10 1:40
12 10 1:50
13 10 2:00
14 10 2:10
15 10 2:20

Hope this helps ;)|||declare @.sample table (rownr int, time int)

insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10

select s1.rownr,
s1.time,
convert(varchar(5), dateadd(minute, sum(coalesce(s2.time, 0)), 0), 108) as timesum
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,
s1.time|||Peso, your solution is insufficiently complex. How do you ever expect to make a living at this with such concise solutions?
Obfuscate! Obfuscate! Obfuscate!|||thanks alot for all the effort you guys put into this, but i can't solve this. Its my experience that fails here... i really tryed Diabolic's solutions which im sure works but again, i can't apply that solutiuons at my database...

is it possible to make a query that gives me the result i want? or do i need a temp table or similar to accomplish my goal?

//Mr|||It appears that outer theta joins are too elementary for blindman now... :rolleyes:

Not to mention the use of temporal functions which, according to Pat are rather risky, but he's under NDA so can't tell us why...|||MRPCGuy, Peso's method is the standard solution to this common problem, and should work fine.|||declare @.sample table (rownr int, time int)

insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 500 union all
select 5, 10 union all
select 6, 10 union all
select 7, 4000000 union all
select 8, 10

select s1.rownr,
s1.time,
convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,
s1.time
order by s1.rownr,
s1.time|||would somebody -- and i volunteer mrpcguy for this -- please post the DDL to create a table using rownr=int, time=datetime as specified in post #1

it's fine and dandy to set up test cases using ints, but mrpcguy says he's using datetimes|||im learning, im leraning, alright? Thx Peso for your help...|||don't get this... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?|||don't get this... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?with a query, similar to the ones you've seen in this thread

any chance you could give us some real data, not fake data? i.e. data with datetimes, not integers|||thing is that i dont have any real data yet, im building this database and gona fill it with divelogs later on. But this function to be able to count accumulated time is important. i only have fake data.
My idea was to have datetime at that columt because logg gonna look like "00:30" for 30min. Maybe its better to use int and put in "30" for 30min...
How can i achive what i wont with my first post and use int instead of datetime?|||For int you just have to log the number of mins (int), but that will turn innacurate soon enough. Since you're storing the number of minutes in integer you will soon realise that if your dive was 2.5mins you'll have to round it.

To store the time as an int it's prolly better to store the number of seconds.|||your correct about that, but since i always round it up to whole minutes i don't need seconds...|||you should be able to use the solutions presented here, namely peso's one, if you're going to use int.|||Ok,
I got this:
current table looks like this:
rownr time
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10

and I want this:
rownr time timesum
1 10 0
2 10 10
3 10 20
4 10 30
5 10 40
6 10 50
7 10 60
8 10 70

I run this:
declare @.sample table (rownr int, time int)
insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10
select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time

What if I got a table with 10000 rows… that’s many select statements

I’m very aware that it’s my skills is the biggest problem here, so plz help…|||no, it's still only one SELECT statement, no matter how many rows|||well in my test table i have 50 rows and the output from above gives me 8 rows?!|||maybe not correct forum but anyway... is this doable in ms access? thinking of put this db in access instead? (don't want to run sql local)|||is this doable in ms access? certainly is|||maybe not correct forum but anyway... is this doable in ms access? thinking of put this db in access instead? (don't want to run sql local)

of for the love of....|||...pancakes?|||well in my test table i have 50 rows and the output from above gives me 8 rows?!

You are getting 8 rows because you are using the wrong query. If I didn't missunderstand your post, you are using the following SQL to retrieve the desired results:

declare @.sample table (rownr int, time int)
insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10
select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time

Well, it's only natural that you are only getting 8 rows. If you look closely you'll see that you are selecting the results from the temporary table called @.sample. To retrieve your real data you should use this sql statement:

select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from <MyTableName> as s1
left join <MyTableName> as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time

Please, remember to replace <MyTableName> with the actual name of your dive logs table.|||Please, remember to replace <MyTableName> with the actual name of your dive logs table.and you will also have to change this nonsense --convert(varchar, sum(coalesce(s2.time, 0)) / 60)
+ ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)into the equivalent ms access nonsense

:)|||thx alot it works fine... I'l take your query to another forum and get it translated into access when im have gone nuts trying to translate it myself...|||Good Lord.

It's only 12:08 PM and I am now exhausted.

and the thread is only two pages :)|||and the sarcasm and humiliation at this forum is extraordinary... so what's ur problem beside being exhausted?|||and the sarcasm and humiliation at this forum is extraordinary... so what's ur problem beside being exhausted?My problems are numerous and well documented, but are primarily out of scope in the current discussion. One of those I would consider IN scope, however, is my inability to grasp the usefulness of the term "ur" within the realm of an otherwise reasonably well-crafted sentence.

Sarcasm? Guilty as charged.
Humiliation? Nah, I've seen not one virtual wedgie applied here.

Lighten up. If you think this forum is bad, it's the first one you've been to.|||... my inability to grasp the usefulness of the term "ur" within the realm of an otherwise reasonably well-crafted sentence.then you will just have to make another visit to http://icanhascheezburger.com/

it will make sense eventually

:)|||and the sarcasm and humiliation at this forum is extraordinary...
LOL.. Thats why I keep coming back!|||http://icanhascheezburger.com/

lol @. that site :beer:|||then you will just have to make another visit to http://icanhascheezburger.com/

it will make sense eventually

:)
I must admit, I only looked at the first couple pages then got busy with *coff* work *coff* and forwarded the link to myself at home. I will catch up with the rest of the class over the weekend.

million rows user table and growing

For our web site, we now have a million users and we have one user table
right now..
We would like to consider scaling this out as we grow to 5 -10 million users
and not have it in one table.
Every time a user logs on or changes profile,etc, we dont want to cause
contention or blocking on this table.. so whats the best way to go about
this ?
If i partition the table, whats the best way to go about partitioning it ?
Thanks> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
As long as you have appropriate indexing, I wouldn't expect performance or
concurrency problems regardless of table size. A few million rows really
isn't that large nowadays and, in my option, doesn't warrant partitioning.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks|||Perhaps your company should consider hiring an experienced DBA' One that
can guide you proactively instead of reactively and successfully get you to
the level you wish to achieve.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks|||Focus on indexing strategy , statistics , as 1 million rows is not that
much.
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks