Hide database structure from users and competitors

Hi,

Is there any way to hide the database structure in SQL Server 2000, diagrams etc from my customers and potential competitors ?

Regards

No.

Thanks
Laurentiu

|||

Thank you but it′s a big problem no ?

If I document my software all people can read my structure.

|||

Yes, this problem was discussed on this forum before. See, for example: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1.

Thanks
Laurentiu

sql

Here's the Problem Again With Sample Data

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give
the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.
Thanks again for the help.
create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)
insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')
insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')
insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')
insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')
customerid lastpayment amount
1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100
======== Result => 2100select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> I want the sum of the last payments (amount) for all customers. The last
> payment is with one with most recent date. And if there are more than one
> payment on the most recent date then the one with the higher paymentid is
> the last payment. for example in the given data the insert statement that
> starts with capital I is the last payment of that customer. The correct
> answer should be 2100 as given below. both queries by Erland and Anith
give
> the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
> both queries since right now I want current sum (not till some date). So
> what should be the right query.
> Thanks again for the help.
> create table payments (
> paymentid int,
> customerid int,
> amount int,
> date datetime
> )
> insert payments values (1, 1, 100, '1/1/03')
> insert payments values (2, 1, 200, '2/28/03')
> Insert payments values (3, 1, 500, '5/15/03')
> insert payments values (4, 2, 400, '1/16/03')
> insert payments values (9, 2, 800, '4/30/03')
> insert payments values (5, 2, 200, '6/15/03')
> Insert payments values (6, 2, 900, '6/15/03')
> insert payments values (7, 3, 700, '3/1/03')
> insert payments values (10,3, 300, '7/10/03')
> Insert payments values (8, 3, 600, '9/1/03')
> insert payments values (11,4, 300, '8/1/03')
> insert payments values (12,4, 400, '9/10/03')
> Insert payments values (13,4, 100, '9/10/03')
>
> customerid lastpayment amount
> 1 3 (on 5/15/03) 500
> 2 6 (on 6/15/03) 900
> 3 8 (on 9/1/03) 600
> 4 13 (on 9/10/03) 100
> ========> Result => 2100
>
>|||Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating
a temporary table and then I run this query on the temporary table.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> select customerid,paymentid,[date],amount
> from payments p1
> where paymentid=(select top 1 paymentid from payments p2 where
> p2.customerid=p1.customerid order by [date] desc, paymentid desc)
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > I want the sum of the last payments (amount) for all customers. The last
> > payment is with one with most recent date. And if there are more than
one
> > payment on the most recent date then the one with the higher paymentid
is
> > the last payment. for example in the given data the insert statement
that
> > starts with capital I is the last payment of that customer. The correct
> > answer should be 2100 as given below. both queries by Erland and Anith
> give
> > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
from
> > both queries since right now I want current sum (not till some date). So
> > what should be the right query.
> >
> > Thanks again for the help.
> >
> > create table payments (
> > paymentid int,
> > customerid int,
> > amount int,
> > date datetime
> > )
> >
> > insert payments values (1, 1, 100, '1/1/03')
> > insert payments values (2, 1, 200, '2/28/03')
> > Insert payments values (3, 1, 500, '5/15/03')
> >
> > insert payments values (4, 2, 400, '1/16/03')
> > insert payments values (9, 2, 800, '4/30/03')
> > insert payments values (5, 2, 200, '6/15/03')
> > Insert payments values (6, 2, 900, '6/15/03')
> >
> > insert payments values (7, 3, 700, '3/1/03')
> > insert payments values (10,3, 300, '7/10/03')
> > Insert payments values (8, 3, 600, '9/1/03')
> >
> > insert payments values (11,4, 300, '8/1/03')
> > insert payments values (12,4, 400, '9/10/03')
> > Insert payments values (13,4, 100, '9/10/03')
> >
> >
> > customerid lastpayment amount
> >
> > 1 3 (on 5/15/03) 500
> > 2 6 (on 6/15/03) 900
> > 3 8 (on 9/1/03) 600
> > 4 13 (on 9/10/03) 100
> >
> > ========> > Result => 2100
> >
> >
> >
> >
>|||MAB (fkdfjdierkjflafdafa@.yahoo.com) writes:
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
> creating a temporary table and then I run this query on the temporary
> table.
You might need to review your indexes. It is difficult to test
performance on the small sample, but I would try somthing like:
CREATE CLUSTERED INDEX payments_index ON
payments(customerid, date DESC, paymentid DESC)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||As Erland has suggested you should visit your indexing strategy. There is a
cost for creating the temp table and inserting data into it. With proper
index, this should be a breeze.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"MAB" <fkdfjdierkjflafdafa@.yahoo.com> wrote in message
news:bjoghs$l7pi0$1@.ID-31123.news.uni-berlin.de...
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
creating
> a temporary table and then I run this query on the temporary table.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > select customerid,paymentid,[date],amount
> > from payments p1
> > where paymentid=(select top 1 paymentid from payments p2 where
> > p2.customerid=p1.customerid order by [date] desc, paymentid desc)
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> >
> > "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> > news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > > I want the sum of the last payments (amount) for all customers. The
last
> > > payment is with one with most recent date. And if there are more than
> one
> > > payment on the most recent date then the one with the higher paymentid
> is
> > > the last payment. for example in the given data the insert statement
> that
> > > starts with capital I is the last payment of that customer. The
correct
> > > answer should be 2100 as given below. both queries by Erland and Anith
> > give
> > > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
> from
> > > both queries since right now I want current sum (not till some date).
So
> > > what should be the right query.
> > >
> > > Thanks again for the help.
> > >
> > > create table payments (
> > > paymentid int,
> > > customerid int,
> > > amount int,
> > > date datetime
> > > )
> > >
> > > insert payments values (1, 1, 100, '1/1/03')
> > > insert payments values (2, 1, 200, '2/28/03')
> > > Insert payments values (3, 1, 500, '5/15/03')
> > >
> > > insert payments values (4, 2, 400, '1/16/03')
> > > insert payments values (9, 2, 800, '4/30/03')
> > > insert payments values (5, 2, 200, '6/15/03')
> > > Insert payments values (6, 2, 900, '6/15/03')
> > >
> > > insert payments values (7, 3, 700, '3/1/03')
> > > insert payments values (10,3, 300, '7/10/03')
> > > Insert payments values (8, 3, 600, '9/1/03')
> > >
> > > insert payments values (11,4, 300, '8/1/03')
> > > insert payments values (12,4, 400, '9/10/03')
> > > Insert payments values (13,4, 100, '9/10/03')
> > >
> > >
> > > customerid lastpayment amount
> > >
> > > 1 3 (on 5/15/03) 500
> > > 2 6 (on 6/15/03) 900
> > > 3 8 (on 9/1/03) 600
> > > 4 13 (on 9/10/03) 100
> > >
> > > ========> > > Result => 2100
> > >
> > >
> > >
> > >
> >
> >
>

Heres the Problem Again With Sample Data

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give
the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.

Thanks again for the help.

create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)

insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')

insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')

insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')

insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')

customerid lastpayment amount

1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100

========
Result => 2100select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> I want the sum of the last payments (amount) for all customers. The last
> payment is with one with most recent date. And if there are more than one
> payment on the most recent date then the one with the higher paymentid is
> the last payment. for example in the given data the insert statement that
> starts with capital I is the last payment of that customer. The correct
> answer should be 2100 as given below. both queries by Erland and Anith
give
> the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
> both queries since right now I want current sum (not till some date). So
> what should be the right query.
> Thanks again for the help.
> create table payments (
> paymentid int,
> customerid int,
> amount int,
> date datetime
> )
> insert payments values (1, 1, 100, '1/1/03')
> insert payments values (2, 1, 200, '2/28/03')
> Insert payments values (3, 1, 500, '5/15/03')
> insert payments values (4, 2, 400, '1/16/03')
> insert payments values (9, 2, 800, '4/30/03')
> insert payments values (5, 2, 200, '6/15/03')
> Insert payments values (6, 2, 900, '6/15/03')
> insert payments values (7, 3, 700, '3/1/03')
> insert payments values (10,3, 300, '7/10/03')
> Insert payments values (8, 3, 600, '9/1/03')
> insert payments values (11,4, 300, '8/1/03')
> insert payments values (12,4, 400, '9/10/03')
> Insert payments values (13,4, 100, '9/10/03')
>
> customerid lastpayment amount
> 1 3 (on 5/15/03) 500
> 2 6 (on 6/15/03) 900
> 3 8 (on 9/1/03) 600
> 4 13 (on 9/10/03) 100
> ========
> Result => 2100
>|||Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating
a temporary table and then I run this query on the temporary table.

"oj" <nospam_ojngo@.home.com> wrote in message
news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> select customerid,paymentid,[date],amount
> from payments p1
> where paymentid=(select top 1 paymentid from payments p2 where
> p2.customerid=p1.customerid order by [date] desc, paymentid desc)
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > I want the sum of the last payments (amount) for all customers. The last
> > payment is with one with most recent date. And if there are more than
one
> > payment on the most recent date then the one with the higher paymentid
is
> > the last payment. for example in the given data the insert statement
that
> > starts with capital I is the last payment of that customer. The correct
> > answer should be 2100 as given below. both queries by Erland and Anith
> give
> > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
from
> > both queries since right now I want current sum (not till some date). So
> > what should be the right query.
> > Thanks again for the help.
> > create table payments (
> > paymentid int,
> > customerid int,
> > amount int,
> > date datetime
> > )
> > insert payments values (1, 1, 100, '1/1/03')
> > insert payments values (2, 1, 200, '2/28/03')
> > Insert payments values (3, 1, 500, '5/15/03')
> > insert payments values (4, 2, 400, '1/16/03')
> > insert payments values (9, 2, 800, '4/30/03')
> > insert payments values (5, 2, 200, '6/15/03')
> > Insert payments values (6, 2, 900, '6/15/03')
> > insert payments values (7, 3, 700, '3/1/03')
> > insert payments values (10,3, 300, '7/10/03')
> > Insert payments values (8, 3, 600, '9/1/03')
> > insert payments values (11,4, 300, '8/1/03')
> > insert payments values (12,4, 400, '9/10/03')
> > Insert payments values (13,4, 100, '9/10/03')
> > customerid lastpayment amount
> > 1 3 (on 5/15/03) 500
> > 2 6 (on 6/15/03) 900
> > 3 8 (on 9/1/03) 600
> > 4 13 (on 9/10/03) 100
> > ========
> > Result => 2100|||MAB (fkdfjdierkjflafdafa@.yahoo.com) writes:
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
> creating a temporary table and then I run this query on the temporary
> table.

You might need to review your indexes. It is difficult to test
performance on the small sample, but I would try somthing like:

CREATE CLUSTERED INDEX payments_index ON
payments(customerid, date DESC, paymentid DESC)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As Erland has suggested you should visit your indexing strategy. There is a
cost for creating the temp table and inserting data into it. With proper
index, this should be a breeze.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <fkdfjdierkjflafdafa@.yahoo.com> wrote in message
news:bjoghs$l7pi0$1@.ID-31123.news.uni-berlin.de...
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
creating
> a temporary table and then I run this query on the temporary table.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > select customerid,paymentid,[date],amount
> > from payments p1
> > where paymentid=(select top 1 paymentid from payments p2 where
> > p2.customerid=p1.customerid order by [date] desc, paymentid desc)
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> > "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> > news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > > I want the sum of the last payments (amount) for all customers. The
last
> > > payment is with one with most recent date. And if there are more than
> one
> > > payment on the most recent date then the one with the higher paymentid
> is
> > > the last payment. for example in the given data the insert statement
> that
> > > starts with capital I is the last payment of that customer. The
correct
> > > answer should be 2100 as given below. both queries by Erland and Anith
> > give
> > > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
> from
> > > both queries since right now I want current sum (not till some date).
So
> > > what should be the right query.
> > > > Thanks again for the help.
> > > > create table payments (
> > > paymentid int,
> > > customerid int,
> > > amount int,
> > > date datetime
> > > )
> > > > insert payments values (1, 1, 100, '1/1/03')
> > > insert payments values (2, 1, 200, '2/28/03')
> > > Insert payments values (3, 1, 500, '5/15/03')
> > > > insert payments values (4, 2, 400, '1/16/03')
> > > insert payments values (9, 2, 800, '4/30/03')
> > > insert payments values (5, 2, 200, '6/15/03')
> > > Insert payments values (6, 2, 900, '6/15/03')
> > > > insert payments values (7, 3, 700, '3/1/03')
> > > insert payments values (10,3, 300, '7/10/03')
> > > Insert payments values (8, 3, 600, '9/1/03')
> > > > insert payments values (11,4, 300, '8/1/03')
> > > insert payments values (12,4, 400, '9/10/03')
> > > Insert payments values (13,4, 100, '9/10/03')
> > > > > customerid lastpayment amount
> > > > 1 3 (on 5/15/03) 500
> > > 2 6 (on 6/15/03) 900
> > > 3 8 (on 9/1/03) 600
> > > 4 13 (on 9/10/03) 100
> > > > ========
> > > Result => 2100
> > > >

Help_Creating Archive Table

I have a table called customers that store information about the particular customer. I would like to have a table called Archive so that when I delete a Customer that have not been active for a specific time then the deleted information will be automatically be inserted into the Archive table. Do I need to create the archive table with the same numbers of columns as exactly as the customer table?

I need some basic idea about how this should be implemented.

Any input will be appreciated.
Thanks in advanceDo you use foreign keys? If so - you have to move customer activity history to archive table too. Structure of table could be the same, unless you want to know when data were moved to archive or any other additional information. And do not use removed ids again....|||Thank you snail for the reply
I have just created the Archive table. The primary key of the Customer Table is a foreign key in the archive table. I have added three more columns in the Archive Table namely Comments,ArchiveDate and EmployeeName, these three columns are not in the customer Table.Can this be possible? as this colums are not in the Customer Table. These columns are important because when a customer is deleted I want to be able to enter the reason why a specific customer was deleted. What did u mean by ids, are you talking about Identity Seed or?
Again I have trouble formulating the query that will insert the deleted columns into the Archive Table. Need additional Info
Thanks for the help so far|||well, if you got two identical tables you could write a trigger that fires on a deleted record from the customers table you can use
INSETED OF DELETE trigger

or if you got two different tables you can write a store procedure for deleting so it can insert the required record into the archive table with
the supplied fields first you have to insert to the achive table
using
insert into achive(field1,field2)
select field1,field2 from customers where customerid = @.custID

then update the record in the achive using
update achive set field6 = @.employeeName ...
where customerid = @.custID

to it then delete the record from the customers table.|||Thank u elamor for the reply. I am going to use Store Procedure, the idea is clear, I think with these info I know where to start, definately I might be posting my query here for deburg if ?