Foreign Key and Nullable column.

Hi Gurus,
This seems simple but not to me. Now I want to create a new table that
contains a column such as Product_Id. I want to create a foreign key
constraint on Product_Id pointing to a Product table. Sometimes the
Product_Id can be NULL but I don't want to make the column to be a nullable
column, because that can make query complex and slow. So I like to use 0 to
represent NULL. Now I also don't want to insert a Product_Id=0 to the Product
table, because I just worry that can hurt applications or some reports. My
dillema here is that I just can't create the forieign key to protect data
integrity because the 0 Product_Id may not be able to find a line in the
Product table. I also have other design choices like this.
Do you have similar experience? Can any guru here give me a good advice? Can
I avoid to insert a dummy line with Product_Id=0 and still have the
Non-nullable column and still can create the FK?
Thanks in advance.
JamesJames,
Could make the column NOT NULL with a DEFAULT constraint of 9999999 or some
number out-of-range for your products.
HTH
Jerry
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0829A5B5-7BCE-4508-96E9-24ADA978A88D@.microsoft.com...
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable
> column, because that can make query complex and slow. So I like to use 0
> to
> represent NULL. Now I also don't want to insert a Product_Id=0 to the
> Product
> table, because I just worry that can hurt applications or some reports.
> My
> dillema here is that I just can't create the forieign key to protect data
> integrity because the 0 Product_Id may not be able to find a line in the
> Product table. I also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good advice?
> Can
> I avoid to insert a dummy line with Product_Id=0 and still have the
> Non-nullable column and still can create the FK?
> Thanks in advance.
> James|||James Ma wrote:
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable column, because that can make query complex and slow. So I
> like to use 0 to represent NULL. Now I also don't want to insert a
> Product_Id=0 to the Product table, because I just worry that can hurt
> applications or some reports. My dillema here is that I just can't
> create the forieign key to protect data integrity because the 0
> Product_Id may not be able to find a line in the Product table. I
> also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good
> advice? Can I avoid to insert a dummy line with Product_Id=0 and
> still have the Non-nullable column and still can create the FK?
> Thanks in advance.
> James
If the FK column can be null then allow null values. From a design
standpoint that's the best option to maintain data integrity, despite
whatever complications this causes with queries.
You can create a view to query the table and replace the NULL value with
whatever you want to send to the application. If no users have rights to
query the table directly you have what you want and have the RI on the
back end that the database demands.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks a lot. I'll use nullable column.
However, I still appreciate if you can help me to clarify. I read many
articles or books that recommend to use a special not null value to represent
null value, since null's behaviour is wierd sometimes (My experiences also
confirm that). So, does that mean it is a better idea to use the dummy line
method if I am designing a database from scratch? When no applications and no
queries have ever been coded?
"David Gugick" wrote:
> James Ma wrote:
> > Hi Gurus,
> >
> > This seems simple but not to me. Now I want to create a new table that
> > contains a column such as Product_Id. I want to create a foreign key
> > constraint on Product_Id pointing to a Product table. Sometimes the
> > Product_Id can be NULL but I don't want to make the column to be a
> > nullable column, because that can make query complex and slow. So I
> > like to use 0 to represent NULL. Now I also don't want to insert a
> > Product_Id=0 to the Product table, because I just worry that can hurt
> > applications or some reports. My dillema here is that I just can't
> > create the forieign key to protect data integrity because the 0
> > Product_Id may not be able to find a line in the Product table. I
> > also have other design choices like this.
> >
> > Do you have similar experience? Can any guru here give me a good
> > advice? Can I avoid to insert a dummy line with Product_Id=0 and
> > still have the Non-nullable column and still can create the FK?
> >
> > Thanks in advance.
> >
> > James
> If the FK column can be null then allow null values. From a design
> standpoint that's the best option to maintain data integrity, despite
> whatever complications this causes with queries.
> You can create a view to query the table and replace the NULL value with
> whatever you want to send to the application. If no users have rights to
> query the table directly you have what you want and have the RI on the
> back end that the database demands.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||James Ma wrote:
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent null value, since null's behaviour is wierd sometimes (My
> experiences also confirm that). So, does that mean it is a better
> idea to use the dummy line method if I am designing a database from
> scratch? When no applications and no queries have ever been coded?
>
If there is a business case for this, and the value actually means
something, then you can do it. Otherwise, I wouldn't.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:9E1AF30B-B432-449D-9540-B541910D9F38@.microsoft.com...
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent
> null value, since null's behaviour is wierd sometimes (My experiences also
> confirm that). So, does that mean it is a better idea to use the dummy
> line
> method if I am designing a database from scratch? When no applications and
> no
> queries have ever been coded?
>
Opinions differ and there aren't necessarily absolute right or wrong answers
to design questions. In my opinion it does make perfect sense to minimise
the use of nulls wherever feasible. I dislike using nullable foreign keys
for some of the reasons you've mentioned. There is an easy alternative.
Create a new table that has a common primary key with your current one and
then only populate that table where you need to reference a product.
CREATE TABLE your_table (x INTEGER NOT NULL PRIMARY KEY, ....)
CREATE TABLE your_table_product (x INTEGER NOT NULL PRIMARY KEY REFERENCES
your_table (x) , ...., product_id INTEGER NOT NULL REFERENCES products
(product_id))
--
David Portas
SQL Server MVP
--

For the SQL Gurus out there, a question

I am not good at SQL - hence here goes

Consider this scenario of 2 tables X and Y with a many to many
relationship

Table X (name,weightage)
X1 2
X2 1
X3 5
X4 1

Table Y (name ,attrib)
Y1 attrib1
Y2 attrib2
Y3 attrib3
Y4 attrib4

Relationship table Z (id,id)
X1 Y1
X1 Y2
X2 Y1
X3 Y1
X3 Y2
X3 Y4
X4 Y4

So based on Z the distribution (count of Y/ Total Y ) is like
Y1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7
Now I want to factor in the weighatge column in X also

So
X1 Y1 2*1
X1 Y2 2*1
X2 Y1 1*1
X3 Y1 5*1
X3 Y2 5*1
X3 Y4 5*1
X4 Y4 1*1

Sum: 21

Given these tables ,How would I factor in the weights and get the
distribution in a single SQL query.

Desired Output of SQL Query

Id AttribDistrib
----------
Y1 attrib1 100*8/21
Y2 attrib2 100*7/21
Y3 attrib3 0
Y4 attrib4 100*6/21

Thx
JPOracle or SQLServer? This was tested on SQLServer 2000 but is ANSI-92 so I
think it should be OK on Oracle 9.

CREATE TABLE TableX (xcol CHAR(2) PRIMARY KEY, weightage INTEGER NOT NULL)

INSERT INTO TableX (xcol, weightage) VALUES ('X1',2)
INSERT INTO TableX (xcol, weightage) VALUES ('X2',1)
INSERT INTO TableX (xcol, weightage) VALUES ('X3',5)
INSERT INTO TableX (xcol, weightage) VALUES ('X4',1)

CREATE TABLE TableY (ycol CHAR(2) PRIMARY KEY, attrib VARCHAR(7) NOT NULL)

INSERT INTO TableY (ycol, attrib) VALUES ('Y1','attrib1')
INSERT INTO TableY (ycol, attrib) VALUES ('Y2','attrib2')
INSERT INTO TableY (ycol, attrib) VALUES ('Y3','attrib3')
INSERT INTO TableY (ycol, attrib) VALUES ('Y4','attrib4')

CREATE TABLE TableZ (xcol CHAR(2) REFERENCES TableX (xcol), ycol CHAR(2)
REFERENCES TableY (ycol), PRIMARY KEY (xcol,ycol))

INSERT INTO TableZ (xcol, ycol) VALUES ('X1','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X1','Y2')
INSERT INTO TableZ (xcol, ycol) VALUES ('X2','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y2')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y4')
INSERT INTO TableZ (xcol, ycol) VALUES ('X4','Y4')

SELECT Y.ycol, Y.attrib,
COALESCE(100*SUM(X.weightage)/
(SELECT CAST(SUM(X.weightage) AS REAL)
FROM TableX AS X
JOIN TableZ AS Z
ON X.xcol = Z.xcol),0)
FROM TableY AS Y
LEFT JOIN TableZ AS Z
ON Y.ycol = Z.ycol
LEFT JOIN TableX AS X
ON Z.xcol = X.xcol
GROUP BY Y.ycol, Y.attrib

--
David Portas
SQL Server MVP
--|||John Pifer wrote:

> I am not good at SQL - hence here goes
> Consider this scenario of 2 tables X and Y with a many to many
> relationship

Lets just stop right here!

The example is by definition an example of bad design. All many-to-many
relationships must be resolved when moving from a logical design to a
physical design.

Fix the design problem!

That is the only acceptable solution.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||Daniel,

Re-read John's post or check out the DDL I posted. John has a joining table
for the many-to-many relationship.

--
David Portas
SQL Server MVP
--|||>> All many-to-many relationships must be resolved when moving from a
logical design to a physical design. <<

Can you explain what you mean by this? What does a many-to-many relationship
between two entities, which is a logical issue, have to do with physical
design?

--
- Anith
( Please reply to newsgroups only )|||Anith Sen wrote:
>>>All many-to-many relationships must be resolved when moving from a
> logical design to a physical design. <<
> Can you explain what you mean by this? What does a many-to-many relationship
> between two entities, which is a logical issue, have to do with physical
> design?

A typical many to many relationship would be ... a student can be in
many classes and a class has many students. This is true in the
logical model but should never be implemented in a relational database
using two tables: Rather using three.

STUDENT ... PK = person_id
CLASS .... PK = class_id

STUDENT_CLASS (intersecting entity) ... PK person_id and class_id.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||Representation of data in a relational database is by definition, logical. I
am more interested in knowing why this schema, irrespective of the number of
tables, has anything to do with physical design.

--
- Anith
( Please reply to newsgroups only )|||Anith Sen wrote:
> Representation of data in a relational database is by definition, logical. I
> am more interested in knowing why this schema, irrespective of the number of
> tables, has anything to do with physical design.

We are talking here about Database 101. If you need a course in
relational theory and relational database architecture take it at a
local college or university. If you are not familiar with the work of
E.F. Cobb and Chris Date you have no business getting behind the keyboard.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||>>If you are not familiar with the work of
>>E.F. Cobb and Chris Date you have no business getting behind the keyboard.

If you lack social skills...you have no business getting behind the keyboard.

BZ

Daniel Morgan <damorgan@.x.washington.edu> wrote in message news:<1076311321.975718@.yasure>...
> Anith Sen wrote:
> > Representation of data in a relational database is by definition, logical. I
> > am more interested in knowing why this schema, irrespective of the number of
> > tables, has anything to do with physical design.
> We are talking here about Database 101. If you need a course in
> relational theory and relational database architecture take it at a
> local college or university. If you are not familiar with the work of
> E.F. Cobb and Chris Date you have no business getting behind the keyboard.|||xAvailx wrote:

>>>If you are not familiar with the work of
>>>E.F. Cobb and Chris Date you have no business getting behind the keyboard.
>
> If you lack social skills...you have no business getting behind the keyboard.
> BZ

So because you are unqualified to do the job you take money for you
think it appropriate to disparage others that are qualified and point
out the minimal qualifiecation set: Fascinating.

I presume you are not a hypocrite and when you need heart surgery some
day you will not go to a board certified cardiologist but rather will go
to some guy that has a great personality.

It is your heart ... it is not your database. Do your employer a favor
and either learn what you are doing or do the ethical thing and quit.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||>> We are talking here about Database 101. <<

Good. Since you are clear about the topic we are talking about, all I need
to provide some directives for you to either learn or recollect.

ISO/IEC document 2382 International Standard Database Vocabulary details the
three levels of discourses for fundamental data representation.
i) A conceptual level which captures an information model that can be
assimilated by humans.(business model)
ii) A logical level that represents, in an abstract form, some or all of a
conceptual model suitable for machine manipulation without regard to actual
media or physical devices (logical model)
iii) A physical level that maps the logical level to actual storage
structures and locations. This involves the actual
encodings of data on physical media or devices (the physical model)

Here is Pascal, clearing it up, with examples:
http://www.dbdebunk.com/page/page/622537.htm
http://www.inconcept.com/JCM/May2003/Pascal.html

Codd's Book RM V2 book, Chapter 29 (Fundamental Laws in Database Management)
has descriptions about the three levels of concept -- the user level,
logical/semantic level & storage oriented or physical levels. Similar
classification of data representation has given by C.J Date in the 2nd
Chapter of his Intro to DB book.

During the relational database design endeavor, the external predicate
(meaning understood by humans) in the conceptual model is translated to the
internal predicate (meaning understood by the DBMS) in the logical model as
values in relations. The physical model, which is transparent to the user,
encompasses the implementation specific details like the internal data
structure, file system, bytes on disk etc & DBMS-specific details like
access paths, indexes, clusters etc.

So, instead of making judgements, I suggest you rethink your statement, "All
many-to-many relationships must be resolved when moving from a logical
design to a physical design." since data representation in a relational
database is, by definition, logical & database design using relational
approach is by definition, logical design.

>> If you need a course in relational theory and relational database
architecture take it at a local college or university. <<

If this is your general attitude, I suggest you read the following link and
get a clue.
http://www.apa.org/journals/psp/psp7761121.html

>> If you are not familiar with the work of E.F. Cobb and Chris Date you
have no business getting behind the keyboard. <<

May I get on with my business?

--
- Anith
( Please reply to newsgroups only )|||
Daniel Morgan wrote:

> Anith Sen wrote:
>>>> All many-to-many relationships must be resolved when moving from a
>>
>>
>> logical design to a physical design. <<
>>
>> Can you explain what you mean by this? What does a many-to-many
>> relationship
>> between two entities, which is a logical issue, have to do with physical
>> design?
>
> A typical many to many relationship would be ... a student can be in
> many classes and a class has many students. This is true in the
> logical model but should never be implemented in a relational database
> using two tables: Rather using three.
> STUDENT ... PK = person_id
> CLASS .... PK = class_id
> STUDENT_CLASS (intersecting entity) ... PK person_id and class_id.

Yes. In the original post, I missed how his setup was different. It seems
he has exactly that, the two tables and an association table whose two
columns were the primary keys of the other two.
Joe|||> So because you are unqualified to do the job you take money for you
> think it appropriate to disparage others that are qualified and point
> out the minimal qualifiecation set: Fascinating.

I would say that Anith Sen is one of the most qualified people in this
newsgroup. Certainly helps more people out than you do in a polite
manner.

> It is your heart ... it is not your database. Do your employer a favor
> and either learn what you are doing or do the ethical thing and quit.

Disagreeing with your attitude has little to do with my
qualifications. Maybe you should to the ethical thing and show some
respect to others? Just because you are a professor(?) doesn't mean
everyone (outside of your courses) has to bend over for you.

BZ

Daniel Morgan <damorgan@.x.washington.edu> wrote in message news:<1076371954.930049@.yasure>...
> xAvailx wrote:
> >>>If you are not familiar with the work of
> >>>E.F. Cobb and Chris Date you have no business getting behind the keyboard.
> > If you lack social skills...you have no business getting behind the keyboard.
> > BZ
> So because you are unqualified to do the job you take money for you
> think it appropriate to disparage others that are qualified and point
> out the minimal qualifiecation set: Fascinating.
> I presume you are not a hypocrite and when you need heart surgery some
> day you will not go to a board certified cardiologist but rather will go
> to some guy that has a great personality.
> It is your heart ... it is not your database. Do your employer a favor
> and either learn what you are doing or do the ethical thing and quit.|||I really hate you table names which are almost impossible to
understand (blah, blah, sample data -- but let's make life easier for
people anyway). How about weighted scores in various contests as the
human-friendly version?

The lack of DDL is also a pain, since we have to make all kinds of
assumptions about NULLs and keys. But is this what you wanted to say?

CREATE TABLE Scores
(score_type CHAR(2) NOT NULL PRIMARY KEY,
wgt INTEGER NOT NULL CHECK (wgt > 0));

INSERT INTO Scores VALUES ('X1', 2);
INSERT INTO Scores VALUES ('X2', 1);
INSERT INTO Scores VALUES ('X3', 5);
INSERT INTO Scores VALUES ('X4', 1);

CREATE TABLE Contests
(contest_id CHAR(2) NOT NULL PRIMARY KEY,
contest_name CHAR(10) NOT NULL);

INSERT INTO Contests VALUES ('Y1', 'Contest-1');
INSERT INTO Contests VALUES ('Y2', 'Contest-2');
INSERT INTO Contests VALUES ('Y3', 'Contest-3');
INSERT INTO Contests VALUES ('Y4', 'Contest-4');

CREATE TABLE Standings
(score_type CHAR(2) NOT NULL REFERENCES Scores (score_type),
contest_id CHAR(2) NOT NULL REFERENCES Contests (contest_id),
PRIMARY KEY (score_type, contest_id));

INSERT INTO Standings VALUES ('X1', 'Y1');
INSERT INTO Standings VALUES ('X1', 'Y2');
INSERT INTO Standings VALUES ('X2', 'Y1');
INSERT INTO Standings VALUES ('X3', 'Y1');
INSERT INTO Standings VALUES ('X3', 'Y2');
INSERT INTO Standings VALUES ('X3', 'Y4');
INSERT INTO Standings VALUES ('X4', 'Y4');

>> So based on Z the distribution (count of Y/ Total Y) is like
Y1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7 <<

I am feeling pedantic, so let's do this in steps to show the reasoning
behind each part and then assemble the final query from them.

SELECT C1.contest_id, (1.0 * COUNT(s1.contest_id)/(SELECT 1.0 *
COUNT(*) FROM Standings)) AS distribution
FROM Contests AS C1
LEFT OUTER JOIN
Standings AS ST1
ON C1.contest_id = ST1.contest_id
GROUP BY C1.contest_id;

The 1.0 is to avoid integer math; it could have been a CAST()

>> Now I want to factor in the wgt column in Scores also <<

Here is where I get confused; you show a constant "wgt * 1" for each
row of the standings table, but if this table is all key, isn't the
one redundant? On that assumption:

SELECT ST1.score_type, ST1.contest_id, S1.wgt
FROM Scores AS S1,
Standings AS ST1
WHERE ST1.score_type = S1.score_type;

>> Sum: 21 <<

SELECT SUM(S1.wgt)
FROM Scores AS S1,
Standings AS ST1
WHERE ST1.score_type = S1.score_type;

>> Given these tables, How would I factor in the weights and get the
distribution in a single SQL query. <<

SELECT C1.contest_id, C1.contest_name,
SUM(S1.wgt)/
(1.0 * (SELECT SUM(S1.wgt)
FROM Scores AS S1, Standings AS ST1
WHERE ST1.score_type = S1.score_type)) AS wgt-distrib
FROM (Contests AS C1
LEFT OUTER JOIN
Standings AS ST1
ON ST1.contest_id = C1.contest_id)
LEFT OUTER JOIN
Scores AS S1
ON ST1.score_type = S1.score_type
GROUP BY C1.contest_id, C1.contest_name;

The optimizer ought to pick out the scalar subquery and do it once.
The big worry is preserving the Contests table via nested LEFT UTER
JOINs.

Results
contest_id contest_name wgt-distrib
=================================
Y1 Contest-1 .38095238095238
Y2 Contest-2 .33333333333333
Y3 Contest-3 NULL
Y4 Contest-4 .28571428571428

Notice I left a NULL in the answer. This means that **nobody** got a
score in contest 'Y3', while a zero implies that **somebody** got a
score of zero in contest 'Y3' -- big difference.

Does it help to explain thigns to this detail or do you need more?|||pwned by Anith Sen tbh.
What a storm over a small slip of symantics.|||WangKhar wrote:

> pwned by Anith Sen tbh.
> What a storm over a small slip of symantics.

I would suggest you not minimize it. The difference is significant.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||Daniel Morgan <damorgan@.x.washington.edu> wrote in message news:<1076532770.994229@.yasure>...
> WangKhar wrote:
> > pwned by Anith Sen tbh.
> > What a storm over a small slip of symantics.
> I would suggest you not minimize it. The difference is significant.

Well in that case I suggest you remember the difference in future, and
regard this as a learning event.|||bottom line: we should create designs with M-M relationships
implemented with less than three tables?
Good. You stay with that one. I'll stay with the
delivered, working systems.

I'd rather not learn from this one...
--
Cheers
Nuno Souto
wizofoz2k@.yahoo.com.au.nospam
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0402120748.4ae59054@.posting.google.c om...
> Well in that case I suggest you remember the difference in future, and
> regard this as a learning event.|||"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:KaYWb.64294$%72.34500@.twister.nyroc.rr.com...

> Au contraire, I think you're the one that needs to review here.
> Cobb would say that the whole point is that you don't NEED to worry about
> the physical design.
> The database could be stored via pieces of paper in a pigeon-hole desk for
> all the dba cares about.
> So again, what does the logical design have to do with the physical design?

It all depends on what one calls logical and physical, isn't it?
Using some nomenclatures, you only talk about tables in the physical design.

This , because it may well be that at this stage you may decide the best way
to implement a given data storage is for example to use a flat file.
As opposed to a r-table. It could even be an object in Oracle, for example.
Which is not exactly a table, although it approaches it. Physical design, all
of it, with that approach.

Logical is where you talk about entities, relationships, attributes. Or object
classes and methods. It doesn't matter if the relationship is 1-1, 1-M or M-M
at all, other than by design requirements. And how it is implemented is far
from being of any concern.

So yes in some methodologies tables *are* part of the physical design, while in
others they are exclusively part of the logical design.
As for how all that applies to the OP, I lost track of the whole thing
as well as losing interest when the slag started.
Forgive me.

--
Cheers
Nuno Souto
wizofoz2k@.yahoo.com.au.nospam|||Thanks much that helped - Sorry for the bad names :)

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0402101535.4f7b8af5@.posting.google.com>...
> I really hate you table names which are almost impossible to
> understand (blah, blah, sample data -- but let's make life easier for
> people anyway). How about weighted scores in various contests as the
> human-friendly version?
> The lack of DDL is also a pain, since we have to make all kinds of
> assumptions about NULLs and keys. But is this what you wanted to say?
> CREATE TABLE Scores
> (score_type CHAR(2) NOT NULL PRIMARY KEY,
> wgt INTEGER NOT NULL CHECK (wgt > 0));
> INSERT INTO Scores VALUES ('X1', 2);
> INSERT INTO Scores VALUES ('X2', 1);
> INSERT INTO Scores VALUES ('X3', 5);
> INSERT INTO Scores VALUES ('X4', 1);
> CREATE TABLE Contests
> (contest_id CHAR(2) NOT NULL PRIMARY KEY,
> contest_name CHAR(10) NOT NULL);
> INSERT INTO Contests VALUES ('Y1', 'Contest-1');
> INSERT INTO Contests VALUES ('Y2', 'Contest-2');
> INSERT INTO Contests VALUES ('Y3', 'Contest-3');
> INSERT INTO Contests VALUES ('Y4', 'Contest-4');
> CREATE TABLE Standings
> (score_type CHAR(2) NOT NULL REFERENCES Scores (score_type),
> contest_id CHAR(2) NOT NULL REFERENCES Contests (contest_id),
> PRIMARY KEY (score_type, contest_id));
> INSERT INTO Standings VALUES ('X1', 'Y1');
> INSERT INTO Standings VALUES ('X1', 'Y2');
> INSERT INTO Standings VALUES ('X2', 'Y1');
> INSERT INTO Standings VALUES ('X3', 'Y1');
> INSERT INTO Standings VALUES ('X3', 'Y2');
> INSERT INTO Standings VALUES ('X3', 'Y4');
> INSERT INTO Standings VALUES ('X4', 'Y4');
> >> So based on Z the distribution (count of Y/ Total Y) is like
> Y1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7 <<
> I am feeling pedantic, so let's do this in steps to show the reasoning
> behind each part and then assemble the final query from them.
> SELECT C1.contest_id, (1.0 * COUNT(s1.contest_id)/(SELECT 1.0 *
> COUNT(*) FROM Standings)) AS distribution
> FROM Contests AS C1
> LEFT OUTER JOIN
> Standings AS ST1
> ON C1.contest_id = ST1.contest_id
> GROUP BY C1.contest_id;
> The 1.0 is to avoid integer math; it could have been a CAST()
> >> Now I want to factor in the wgt column in Scores also <<
> Here is where I get confused; you show a constant "wgt * 1" for each
> row of the standings table, but if this table is all key, isn't the
> one redundant? On that assumption:
> SELECT ST1.score_type, ST1.contest_id, S1.wgt
> FROM Scores AS S1,
> Standings AS ST1
> WHERE ST1.score_type = S1.score_type;
> >> Sum: 21 <<
> SELECT SUM(S1.wgt)
> FROM Scores AS S1,
> Standings AS ST1
> WHERE ST1.score_type = S1.score_type;
> >> Given these tables, How would I factor in the weights and get the
> distribution in a single SQL query. <<
> SELECT C1.contest_id, C1.contest_name,
> SUM(S1.wgt)/
> (1.0 * (SELECT SUM(S1.wgt)
> FROM Scores AS S1, Standings AS ST1
> WHERE ST1.score_type = S1.score_type)) AS wgt-distrib
> FROM (Contests AS C1
> LEFT OUTER JOIN
> Standings AS ST1
> ON ST1.contest_id = C1.contest_id)
> LEFT OUTER JOIN
> Scores AS S1
> ON ST1.score_type = S1.score_type
> GROUP BY C1.contest_id, C1.contest_name;
> The optimizer ought to pick out the scalar subquery and do it once.
> The big worry is preserving the Contests table via nested LEFT UTER
> JOINs.
> Results
> contest_id contest_name wgt-distrib
> =================================
> Y1 Contest-1 .38095238095238
> Y2 Contest-2 .33333333333333
> Y3 Contest-3 NULL
> Y4 Contest-4 .28571428571428
> Notice I left a NULL in the answer. This means that **nobody** got a
> score in contest 'Y3', while a zero implies that **somebody** got a
> score of zero in contest 'Y3' -- big difference.
> Does it help to explain thigns to this detail or do you need more?

For Stored Procs Gurus

Hi,
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael Murphy
Please do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>

For Stored Procs Gurus

Hi,
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael MurphyPlease do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
NewishHi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:
> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and you
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly dropped
> when there are large waves that obscure the transmitter/receiver, and there
> can't be too many more demanding replication setups than this, yet the merge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
Newish
Hi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegro ups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>
|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegro ups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>
|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:

> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and you
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly dropped
> when there are large waves that obscure the transmitter/receiver, and there
> can't be too many more demanding replication setups than this, yet the merge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
NewishHi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:

> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and yo
u
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly droppe
d
> when there are large waves that obscure the transmitter/receiver, and ther
e
> can't be too many more demanding replication setups than this, yet the mer
ge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

For all MS SQL Gurus :( please advice

Hi guys,
we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.
the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again :( and i m very sad abt it all )..
please advice
Thanx in advance
ashish

There are a few ways of doing this ...
1) Go to your off-drive backups (tape, CDs, etc) and restore the latest version
2) Restore the Stored Procedures from your SCM, but deal with the data
3) Use a time machine to go back in time and prevent that from ever happening.
If all three do not work, then as you guessed, you are screwed. Let it be an expensive lesson in regular backups and SCM for your code. And investing in a time machine.

|||If this database is critical, immediately cease all activity on that server until you decide what you are going to do.

Personally I have no experience at all with such utilities, but you might consider something like this:

Recovery for SQL Server

And here's a review for the product at SQL Server Central (it's a freeregistration to read the article; great site, I recommend registering)

Review - MSSQLRecovery