What is the difference between the following two definitions
CREATE TABLE Test (
TEST_ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
and
CREATE TABLE Test (
TEST_ID int IDENTITY(1,1) CONSTRAINT PK_TEST_ID PRIMARY KEY CLUSTERED
)
Generally what is the difference between explicitelly define the contraints
with the CONTRAINT keyword or not.
Regards
DimitrisThe latter gives you the name you define (PK_TEST_ID).
The former lets the system define the constraint name for you, and it is
usually much less meaningful.
Many people swear by naming your constraints in order to have control
(admittedly, it does lend to better self-documentation of the schema).
Personally, I'm indifferent.
"Dimitris Milonas" <gnout@.hotmail.com> wrote in message
news:ehWAU6HSGHA.5500@.TK2MSFTNGP12.phx.gbl...
> What is the difference between the following two definitions
> CREATE TABLE Test (
> TEST_ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
> )
> and
> CREATE TABLE Test (
> TEST_ID int IDENTITY(1,1) CONSTRAINT PK_TEST_ID PRIMARY KEY CLUSTERED
> )
> Generally what is the difference between explicitelly define the
> contraints
> with the CONTRAINT keyword or not.
> Regards
> Dimitris
>
Showing posts with label key. Show all posts
Showing posts with label key. Show all posts
Thursday, March 8, 2012
Contraints
Hi,
I have this (syntax not verified):
CREATE TABLE Colors (
Id INT PRIMARY KEY,
Color VARCHAR(20) NOT NULL
)
GO
INSERT INTO Colors VALUES (1, 'red')
GO
INSERT INTO Colors VALUES (2, 'green')
GO
INSERT INTO Colors VALUES (3, 'blue')
GO
CREATE TABLE Candy (
Id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
ColorId INT REFERENCES Colors(Id)
)
GO
INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
GO
INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
GO
INSERT INTO Candy VALUES (3, 'SweetMama', 2)
GO
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT REFERENCES Candy(Id)
)
Now, you can put all kinds of candy in a pack:
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 3)
GO
This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag 1.
Now, I want to make sure that no bag contains more than 2 candies of the
same kind and same color.
So the following should return ZERO records:
SELECT * FROM
CandyPacks P1, Candy C1, Colors CLR1,
CandyPacks P2, Candy C2, Colors CLR2
WHERE
C1.Id = P1.CandyId
AND CLR1.Id = C1.ColorId
AND C2.Id = P2.CandyId
AND CLR2.Id = C2.ColorId
AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
Now, perhaps this query sucks.. please correct, but my point should be
clear.
This is only an example for similar situations. Perhaps I could use primary
key constraints or something, but in other situations I have some 'complex
query' which should not yield results..
How can I turn a query like the above into a constraint on the CandyPacks
table?
LisaHi Lisa
Please consider using INSTEAD OF INSERT Trigger on the Table.
If the condition is satisified, insert the data
hope the problem is solved? If there are any more issues, do not hesitate to
revert back
thanks and regards
Chandra
"Lisa Pearlson" wrote:
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||I would think that the best way to accomplish this would be using a trigger.
I would also consider changing the structure of the tables such that a
CandyPacks record contians the packid, a colorid, and a candyid. You may
find it easier to enforce the constraint with the data structured this way.
If you leave it as you have it you will have to write a more complex query i
n
the trigger.
"Chandra" wrote:
> Hi Lisa
> Please consider using INSTEAD OF INSERT Trigger on the Table.
> If the condition is satisified, insert the data
> hope the problem is solved? If there are any more issues, do not hesitate
to
> revert back
> thanks and regards
> Chandra
>
> "Lisa Pearlson" wrote:
>|||Try,
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT not null REFERENCES Candy(Id),
quantity int not null default(1) check(quantity = 1 or quantity = 2),
constraint pk_CandyPacks primary key (PackId, CandyId)
)
AMB
"Lisa Pearlson" wrote:
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||You could use a check constraint to see wheter how many candy are in the
package:
CHECK(Your Select statement)
Further help, just raise a hand.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
> 1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use
> primary key constraints or something, but in other situations I have some
> 'complex query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>|||*raises hand*
My actual table is:
CREATE TABLE MatchResults
(
ParentId INT NOT NULL REFERENCES Bedrijven(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES Bedrijven(Id),
PRIMARY KEY(ParentId,DatMatch,Id),
Updated DATETIME DEFAULT GETDATE(),
Deleted BIT DEFAULT 0
)
And no data may be added to this table that would return any records in this
query:
SELECT COUNT(*)
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
So the above query should always yield 0.
So I want to add a contraint to the above table so that the query below
always is 0.
I don't want to use INSTEAD OF INSERT trigger...
How do I turn it into a table constraint?
Thanks,
Lisa
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ePt3i5%23SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> You could use a check constraint to see wheter how many candy are in the
> package:
> CHECK(Your Select statement)
> Further help, just raise a hand.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
> news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
>|||On Wed, 4 May 2005 02:50:10 +0200, Lisa Pearlson wrote:
>*raises hand*
>My actual table is:
>CREATE TABLE MatchResults
>(
>ParentId INT NOT NULL REFERENCES Bedrijven(Id),
>DatMatch DATETIME NOT NULL,
>Id INT NOT NULL REFERENCES Bedrijven(Id),
> PRIMARY KEY(ParentId,DatMatch,Id),
>Updated DATETIME DEFAULT GETDATE(),
>Deleted BIT DEFAULT 0
> )
>
>And no data may be added to this table that would return any records in thi
s
>query:
>SELECT COUNT(*)
>FROM MatchResults M, Bedrijven B1, Bedrijven B2
>WHERE M.Deleted!=1
>AND B1.Id = M.ParentId
>AND B2.Id = M.Id
>AND (B1.ProfielId!=3 OR B2.ProfielId=3)
>So the above query should always yield 0.
>So I want to add a contraint to the above table so that the query below
>always is 0.
>I don't want to use INSTEAD OF INSERT trigger...
>How do I turn it into a table constraint?
>Thanks,
>Lisa
Hi Lisa,
Impossible with your current design, since a CHECK constraint can't use
any other data than the data in the same row.
The workaround is to replace the Bedrijven table with two tables: one
for the bedrijven with profiel equal to 3 and one for the bedrijven with
profiel unequal to 3:
CREATE TABLE BedrijvenType3
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId = 3)
)
CREATE TABLE BedrijvenOverig
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId <> 3)
)
CREATE TABLE MatchResults
(ParentId INT NOT NULL REFERENCES BedrijvenType3(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES BedrijvenOverig(Id),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0
)
Note that I also included a Deleted column in the two bedrijven tables,
because the referential integrity can't be made dependant of the Deleted
column in the MatchResults. If you do want to really remove rows from
the bedrijven tables (or if I somehow misunderstood your requirements),
you'll have to use a different technique: make a redundant copy of the
ProfielId for both Id and ParentId (and possibly add a redundant UNIQUE
constraint to the Bedrijven table, so that the data stays synch'ed):
ALTER TABLE Bedrijven
ADD UNIQUE (Id, ProfielId)
CREATE TABLE MatchResults
(ParentId INT NOT NULL,
ParentIdProfiel INT NOT NULL,
FOREIGN KEY (ParentId, ParentIdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL,
IdProfiel INT NOT NULL,
FOREIGN KEY (Id, IdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0,
CHECK (Deleted = 1 OR (ParentIdProfiel = 3 AND IdProfiel <> 3)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why do you believe that every table has magical, universal column
called "id"? In an RDBMS, there are lots of kinds of identifiers,
not like a 1950's file system record number. And have you ever
researched for industry standards, like the Land color number or
Pantone numbers? Let's clean up the sample DDL:
CREATE TABLE Colors
(pantone_nbr INTEGER PRIMARY KEY,
pantone_description VARCHAR(20) NOT NULL);
CREATE TABLE Candies
(upc CHAR(13) NOT NULL PRIMARY KEY,
candy_name VARCHAR(20) NOT NULL,
pantone_nbr INTEGER REFERENCES Colors(pantone_nbr));
CREATE TABLE CandyPacks
(pack_upc CHAR(13) NOT NULL,
candy_upc CHAR(13) NOT NULL
REFERENCES Candies(upc),
PRIMARY KEY (pack_upc, candy_upc)
);
same kind AND same color. <<
The first condition is enforced by a PRIMARY KEY. In full SQL-92 you
can put this into a CHECK();
CHECK(1 = ALL (SELECT COUNT(C1.pantone_nbr)
FROM Candies AS C1
WHERE C1.upc = CandyPacks.candyupc))
In SQL server, you will need to use a trigger on the packages table.
I have this (syntax not verified):
CREATE TABLE Colors (
Id INT PRIMARY KEY,
Color VARCHAR(20) NOT NULL
)
GO
INSERT INTO Colors VALUES (1, 'red')
GO
INSERT INTO Colors VALUES (2, 'green')
GO
INSERT INTO Colors VALUES (3, 'blue')
GO
CREATE TABLE Candy (
Id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
ColorId INT REFERENCES Colors(Id)
)
GO
INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
GO
INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
GO
INSERT INTO Candy VALUES (3, 'SweetMama', 2)
GO
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT REFERENCES Candy(Id)
)
Now, you can put all kinds of candy in a pack:
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 3)
GO
This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag 1.
Now, I want to make sure that no bag contains more than 2 candies of the
same kind and same color.
So the following should return ZERO records:
SELECT * FROM
CandyPacks P1, Candy C1, Colors CLR1,
CandyPacks P2, Candy C2, Colors CLR2
WHERE
C1.Id = P1.CandyId
AND CLR1.Id = C1.ColorId
AND C2.Id = P2.CandyId
AND CLR2.Id = C2.ColorId
AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
Now, perhaps this query sucks.. please correct, but my point should be
clear.
This is only an example for similar situations. Perhaps I could use primary
key constraints or something, but in other situations I have some 'complex
query' which should not yield results..
How can I turn a query like the above into a constraint on the CandyPacks
table?
LisaHi Lisa
Please consider using INSTEAD OF INSERT Trigger on the Table.
If the condition is satisified, insert the data
hope the problem is solved? If there are any more issues, do not hesitate to
revert back
thanks and regards
Chandra
"Lisa Pearlson" wrote:
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||I would think that the best way to accomplish this would be using a trigger.
I would also consider changing the structure of the tables such that a
CandyPacks record contians the packid, a colorid, and a candyid. You may
find it easier to enforce the constraint with the data structured this way.
If you leave it as you have it you will have to write a more complex query i
n
the trigger.
"Chandra" wrote:
> Hi Lisa
> Please consider using INSTEAD OF INSERT Trigger on the Table.
> If the condition is satisified, insert the data
> hope the problem is solved? If there are any more issues, do not hesitate
to
> revert back
> thanks and regards
> Chandra
>
> "Lisa Pearlson" wrote:
>|||Try,
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT not null REFERENCES Candy(Id),
quantity int not null default(1) check(quantity = 1 or quantity = 2),
constraint pk_CandyPacks primary key (PackId, CandyId)
)
AMB
"Lisa Pearlson" wrote:
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||You could use a check constraint to see wheter how many candy are in the
package:
CHECK(Your Select statement)
Further help, just raise a hand.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
> 1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use
> primary key constraints or something, but in other situations I have some
> 'complex query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>|||*raises hand*
My actual table is:
CREATE TABLE MatchResults
(
ParentId INT NOT NULL REFERENCES Bedrijven(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES Bedrijven(Id),
PRIMARY KEY(ParentId,DatMatch,Id),
Updated DATETIME DEFAULT GETDATE(),
Deleted BIT DEFAULT 0
)
And no data may be added to this table that would return any records in this
query:
SELECT COUNT(*)
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
So the above query should always yield 0.
So I want to add a contraint to the above table so that the query below
always is 0.
I don't want to use INSTEAD OF INSERT trigger...
How do I turn it into a table constraint?
Thanks,
Lisa
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ePt3i5%23SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> You could use a check constraint to see wheter how many candy are in the
> package:
> CHECK(Your Select statement)
> Further help, just raise a hand.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
> news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
>|||On Wed, 4 May 2005 02:50:10 +0200, Lisa Pearlson wrote:
>*raises hand*
>My actual table is:
>CREATE TABLE MatchResults
>(
>ParentId INT NOT NULL REFERENCES Bedrijven(Id),
>DatMatch DATETIME NOT NULL,
>Id INT NOT NULL REFERENCES Bedrijven(Id),
> PRIMARY KEY(ParentId,DatMatch,Id),
>Updated DATETIME DEFAULT GETDATE(),
>Deleted BIT DEFAULT 0
> )
>
>And no data may be added to this table that would return any records in thi
s
>query:
>SELECT COUNT(*)
>FROM MatchResults M, Bedrijven B1, Bedrijven B2
>WHERE M.Deleted!=1
>AND B1.Id = M.ParentId
>AND B2.Id = M.Id
>AND (B1.ProfielId!=3 OR B2.ProfielId=3)
>So the above query should always yield 0.
>So I want to add a contraint to the above table so that the query below
>always is 0.
>I don't want to use INSTEAD OF INSERT trigger...
>How do I turn it into a table constraint?
>Thanks,
>Lisa
Hi Lisa,
Impossible with your current design, since a CHECK constraint can't use
any other data than the data in the same row.
The workaround is to replace the Bedrijven table with two tables: one
for the bedrijven with profiel equal to 3 and one for the bedrijven with
profiel unequal to 3:
CREATE TABLE BedrijvenType3
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId = 3)
)
CREATE TABLE BedrijvenOverig
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId <> 3)
)
CREATE TABLE MatchResults
(ParentId INT NOT NULL REFERENCES BedrijvenType3(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES BedrijvenOverig(Id),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0
)
Note that I also included a Deleted column in the two bedrijven tables,
because the referential integrity can't be made dependant of the Deleted
column in the MatchResults. If you do want to really remove rows from
the bedrijven tables (or if I somehow misunderstood your requirements),
you'll have to use a different technique: make a redundant copy of the
ProfielId for both Id and ParentId (and possibly add a redundant UNIQUE
constraint to the Bedrijven table, so that the data stays synch'ed):
ALTER TABLE Bedrijven
ADD UNIQUE (Id, ProfielId)
CREATE TABLE MatchResults
(ParentId INT NOT NULL,
ParentIdProfiel INT NOT NULL,
FOREIGN KEY (ParentId, ParentIdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL,
IdProfiel INT NOT NULL,
FOREIGN KEY (Id, IdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0,
CHECK (Deleted = 1 OR (ParentIdProfiel = 3 AND IdProfiel <> 3)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why do you believe that every table has magical, universal column
called "id"? In an RDBMS, there are lots of kinds of identifiers,
not like a 1950's file system record number. And have you ever
researched for industry standards, like the Land color number or
Pantone numbers? Let's clean up the sample DDL:
CREATE TABLE Colors
(pantone_nbr INTEGER PRIMARY KEY,
pantone_description VARCHAR(20) NOT NULL);
CREATE TABLE Candies
(upc CHAR(13) NOT NULL PRIMARY KEY,
candy_name VARCHAR(20) NOT NULL,
pantone_nbr INTEGER REFERENCES Colors(pantone_nbr));
CREATE TABLE CandyPacks
(pack_upc CHAR(13) NOT NULL,
candy_upc CHAR(13) NOT NULL
REFERENCES Candies(upc),
PRIMARY KEY (pack_upc, candy_upc)
);
same kind AND same color. <<
The first condition is enforced by a PRIMARY KEY. In full SQL-92 you
can put this into a CHECK();
CHECK(1 = ALL (SELECT COUNT(C1.pantone_nbr)
FROM Candies AS C1
WHERE C1.upc = CandyPacks.candyupc))
In SQL server, you will need to use a trigger on the packages table.
Wednesday, March 7, 2012
Continue INSERT after key violation?
Hi there.
I have a simple table with a UNIQUE constraint on one field. I wish to
populate this table using a stored procedure that returns the equivalent
results set like so:
INSERT INTO MyTable EXECUTE p_GetMyResultsSet
My stored proc returns unique, distinct results each time it's called
(unique within each call!). The problem I'm having though is that, if the
stored proc returns any record that already exists in the table, the whole
statement quits with a 'constraint violation'.
I would like the statement to continue inserting the results - only leaving
out records which fail the criteria - is this possible?
e.g: 1. p_GetMyResultsSet returns one row with a key value of '1' - record
is inserted into the table OK.
2. p_GetMyResultsSet returns 4 rows with key values of 1, 2, 3, 4.
What happens now: none of the records are inserted because '1' already exist
s.
What I'd like to hapen: records 2, 3, 4 to be inserted.Can't you change the proc so that it only returns the rows that don't
exist? For example:
SELECT x, ...
FROM foo
WHERE NOT EXISTS
(SELECT *
FROM MyTable
WHERE x = foo.x) ;
If not, you could insert the results of the proc to a temp table and
then to MyTable using the same WHERE NOT EXISTS logic.
David Portas
SQL Server MVP
--|||Yes - I think I'll have to use the temp table approach. I'm restricted from
using the 'not exists' because the stored proc needs to be available for use
by other apps and processes- the "MyTable" population is just one process o
f
many using the proc to generate data...
Thanks for the help!
"David Portas" wrote:
> Can't you change the proc so that it only returns the rows that don't
> exist? For example:
> SELECT x, ...
> FROM foo
> WHERE NOT EXISTS
> (SELECT *
> FROM MyTable
> WHERE x = foo.x) ;
> If not, you could insert the results of the proc to a temp table and
> then to MyTable using the same WHERE NOT EXISTS logic.
> --
> David Portas
> SQL Server MVP
> --
>
I have a simple table with a UNIQUE constraint on one field. I wish to
populate this table using a stored procedure that returns the equivalent
results set like so:
INSERT INTO MyTable EXECUTE p_GetMyResultsSet
My stored proc returns unique, distinct results each time it's called
(unique within each call!). The problem I'm having though is that, if the
stored proc returns any record that already exists in the table, the whole
statement quits with a 'constraint violation'.
I would like the statement to continue inserting the results - only leaving
out records which fail the criteria - is this possible?
e.g: 1. p_GetMyResultsSet returns one row with a key value of '1' - record
is inserted into the table OK.
2. p_GetMyResultsSet returns 4 rows with key values of 1, 2, 3, 4.
What happens now: none of the records are inserted because '1' already exist
s.
What I'd like to hapen: records 2, 3, 4 to be inserted.Can't you change the proc so that it only returns the rows that don't
exist? For example:
SELECT x, ...
FROM foo
WHERE NOT EXISTS
(SELECT *
FROM MyTable
WHERE x = foo.x) ;
If not, you could insert the results of the proc to a temp table and
then to MyTable using the same WHERE NOT EXISTS logic.
David Portas
SQL Server MVP
--|||Yes - I think I'll have to use the temp table approach. I'm restricted from
using the 'not exists' because the stored proc needs to be available for use
by other apps and processes- the "MyTable" population is just one process o
f
many using the proc to generate data...
Thanks for the help!
"David Portas" wrote:
> Can't you change the proc so that it only returns the rows that don't
> exist? For example:
> SELECT x, ...
> FROM foo
> WHERE NOT EXISTS
> (SELECT *
> FROM MyTable
> WHERE x = foo.x) ;
> If not, you could insert the results of the proc to a temp table and
> then to MyTable using the same WHERE NOT EXISTS logic.
> --
> David Portas
> SQL Server MVP
> --
>
Friday, February 24, 2012
CONTAINS
Hi,
I have a table like this:
CREATE TABLE T1(
C1 int identity(1,1) PRIMARY KEY,
C2 NVARCHAR(50))
INSERT T1(C2) VALUES('test')
INSERT T1(C2) VALUES('Xtest')
INSERT T1(C2) VALUES('testX')
Assuming that C2 is enabled for FTS, this query does not return "Xtest":
SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
It returns only "test" and "testX". How can I do that?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a table like this:
> CREATE TABLE T1(
> C1 int identity(1,1) PRIMARY KEY,
> C2 NVARCHAR(50))
> INSERT T1(C2) VALUES('test')
> INSERT T1(C2) VALUES('Xtest')
> INSERT T1(C2) VALUES('testX')
> Assuming that C2 is enabled for FTS, this query does not return "Xtest":
> SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
> It returns only "test" and "testX". How can I do that?
You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
searches, and so the leading * is ignored. Another option would be to fall
back to LIKE when a leading * is used.
Dan
|||I think you mean prefix (comes before) which SQL FTS does not support. SQL
FTS does support suffix (comes at the end) type searches when you use the
wildcard operator in the Contains predicate.
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
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:e1DACM3iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
> searches, and so the leading * is ignored. Another option would be to fall
> back to LIKE when a leading * is used.
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
>I think you mean prefix (comes before) which SQL FTS does not support. SQL
> FTS does support suffix (comes at the end) type searches when you use the
> wildcard operator in the Contains predicate.
Possibly, I can't remember which way around they go. If it refers to the
string part, it's suffix that isn't supported as the wildcard will be the
prefix. Thanks for pointing out my error though
Dan
|||Thank you all :-)
Then LIKE will be the only poosible way?
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
SQL[vbcol=seagreen]
the
> Possibly, I can't remember which way around they go. If it refers to the
> string part, it's suffix that isn't supported as the wildcard will be the
> prefix. Thanks for pointing out my error though
> Dan
>
|||Unfortunately so.
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
"Leila" <Leilas@.hotpop.com> wrote in message
news:ehN0lR%23iFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thank you all :-)
> Then LIKE will be the only poosible way?
>
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> SQL
> the
the
>
I have a table like this:
CREATE TABLE T1(
C1 int identity(1,1) PRIMARY KEY,
C2 NVARCHAR(50))
INSERT T1(C2) VALUES('test')
INSERT T1(C2) VALUES('Xtest')
INSERT T1(C2) VALUES('testX')
Assuming that C2 is enabled for FTS, this query does not return "Xtest":
SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
It returns only "test" and "testX". How can I do that?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a table like this:
> CREATE TABLE T1(
> C1 int identity(1,1) PRIMARY KEY,
> C2 NVARCHAR(50))
> INSERT T1(C2) VALUES('test')
> INSERT T1(C2) VALUES('Xtest')
> INSERT T1(C2) VALUES('testX')
> Assuming that C2 is enabled for FTS, this query does not return "Xtest":
> SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
> It returns only "test" and "testX". How can I do that?
You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
searches, and so the leading * is ignored. Another option would be to fall
back to LIKE when a leading * is used.
Dan
|||I think you mean prefix (comes before) which SQL FTS does not support. SQL
FTS does support suffix (comes at the end) type searches when you use the
wildcard operator in the Contains predicate.
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
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:e1DACM3iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
> searches, and so the leading * is ignored. Another option would be to fall
> back to LIKE when a leading * is used.
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
>I think you mean prefix (comes before) which SQL FTS does not support. SQL
> FTS does support suffix (comes at the end) type searches when you use the
> wildcard operator in the Contains predicate.
Possibly, I can't remember which way around they go. If it refers to the
string part, it's suffix that isn't supported as the wildcard will be the
prefix. Thanks for pointing out my error though
Dan
|||Thank you all :-)
Then LIKE will be the only poosible way?
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
SQL[vbcol=seagreen]
the
> Possibly, I can't remember which way around they go. If it refers to the
> string part, it's suffix that isn't supported as the wildcard will be the
> prefix. Thanks for pointing out my error though
> Dan
>
|||Unfortunately so.
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
"Leila" <Leilas@.hotpop.com> wrote in message
news:ehN0lR%23iFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thank you all :-)
> Then LIKE will be the only poosible way?
>
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> SQL
> the
the
>
Tuesday, February 14, 2012
Constraints and query plans
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny
It depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny
|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Actually, YES:
http://www.microsoft.com/technet/abo...ps_122104.mspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.
|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/abo...ps_122104.mspx
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny
It depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny
|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Actually, YES:
http://www.microsoft.com/technet/abo...ps_122104.mspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.
|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/abo...ps_122104.mspx
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>
Constraints and query plans
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
DannyIt depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Actually, YES:
http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_122104.mspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_122104.mspx
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
>> Does having enforced foreign key constraints between tables help query
>> performance?
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>> Does having enforced foreign key constraints between tables help query
>> performance? In our reporting database we have many views with multiple
>> joins so that report writing is easier. But with each additional join
>> the
>> optimizer generally scans or seeks the index on all joined tables whether
>> or not the query requests columns from the table. Currently there are no
>> foreign key constraints, by adding and enforcing them would the queries
>> produce better plans?
>> Thanks,
>> Danny
>
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
DannyIt depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Actually, YES:
http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_122104.mspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_122104.mspx
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
>> Does having enforced foreign key constraints between tables help query
>> performance?
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>> Does having enforced foreign key constraints between tables help query
>> performance? In our reporting database we have many views with multiple
>> joins so that report writing is easier. But with each additional join
>> the
>> optimizer generally scans or seeks the index on all joined tables whether
>> or not the query requests columns from the table. Currently there are no
>> foreign key constraints, by adding and enforcing them would the queries
>> produce better plans?
>> Thanks,
>> Danny
>
Constraints and query plans
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
DannyIt depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Actually, YES:
http://www.microsoft.com/technet/ab...04.mspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/ab...04.ms
px
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
DannyIt depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Actually, YES:
http://www.microsoft.com/technet/ab...04.mspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/ab...04.ms
px
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>
Constraints
Can any one tell me regarding the foreign key constraints like I wanna
findout which columns doesn't contain the foreign key constraint so that I
can create it...
Thanks in advance.
Hi Joh,
Why do you want create Foreign key constrain for all the columns in a table
, Probably i understood your question wrongly.
What you could do is all the constrains specificatins can be view in the
below information schema view. Have a look into it and analyze ur
requirement.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Can any one tell me regarding the foreign key constraints like I wanna
> findout which columns doesn't contain the foreign key constraint so that I
> can create it...
>
> Thanks in advance.
>
|||No you took my question wrongly... I want to make sure Primary Key &
Foreign Key constraints are there in my database... nothing would be missed
for instance...
Emp Dept
EmpID DeptID
DeptID DeptName
EmpName
According to the above situation I have to make sure in the emp table there
should be a foreign key constraint on DeptID column.. Is there any query
through which I can make sure about it... hope now you understand my
question.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi Joh,
> Why do you want create Foreign key constrain for all the columns in a
table[vbcol=seagreen]
>, Probably i understood your question wrongly.
> What you could do is all the constrains specificatins can be view in the
> below information schema view. Have a look into it and analyze ur
> requirement.
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
I
>
|||Hi,
I dont have a query for this with me. I personally recommend you to use MS
Visio / Erwin to do the logical / physical data model with all relationship.
But for validating you could use INFORMATION_SCHEMA.TABLE_CONSTRAINTS system
view.
Thanks
Hari
SQL Server MVP
can you please work with the system view
INFORMATION_SCHEMA.TABLE_CONSTRAINTS? This will help
"Joh" <joh@.mailcity.com> wrote in message
news:OAQTHygYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> No you took my question wrongly... I want to make sure Primary Key &
> Foreign Key constraints are there in my database... nothing would be
> missed
> for instance...
> Emp Dept
> EmpID DeptID
> DeptID DeptName
> EmpName
> According to the above situation I have to make sure in the emp table
> there
> should be a foreign key constraint on DeptID column.. Is there any query
> through which I can make sure about it... hope now you understand my
> question.
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> table
> I
>
findout which columns doesn't contain the foreign key constraint so that I
can create it...
Thanks in advance.
Hi Joh,
Why do you want create Foreign key constrain for all the columns in a table
, Probably i understood your question wrongly.What you could do is all the constrains specificatins can be view in the
below information schema view. Have a look into it and analyze ur
requirement.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Can any one tell me regarding the foreign key constraints like I wanna
> findout which columns doesn't contain the foreign key constraint so that I
> can create it...
>
> Thanks in advance.
>
|||No you took my question wrongly... I want to make sure Primary Key &
Foreign Key constraints are there in my database... nothing would be missed
for instance...
Emp Dept
EmpID DeptID
DeptID DeptName
EmpName
According to the above situation I have to make sure in the emp table there
should be a foreign key constraint on DeptID column.. Is there any query
through which I can make sure about it... hope now you understand my
question.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi Joh,
> Why do you want create Foreign key constrain for all the columns in a
table[vbcol=seagreen]
>
, Probably i understood your question wrongly.> What you could do is all the constrains specificatins can be view in the
> below information schema view. Have a look into it and analyze ur
> requirement.
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
I
>
|||Hi,
I dont have a query for this with me. I personally recommend you to use MS
Visio / Erwin to do the logical / physical data model with all relationship.
But for validating you could use INFORMATION_SCHEMA.TABLE_CONSTRAINTS system
view.
Thanks
Hari
SQL Server MVP
can you please work with the system view
INFORMATION_SCHEMA.TABLE_CONSTRAINTS? This will help
"Joh" <joh@.mailcity.com> wrote in message
news:OAQTHygYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> No you took my question wrongly... I want to make sure Primary Key &
> Foreign Key constraints are there in my database... nothing would be
> missed
> for instance...
> Emp Dept
> EmpID DeptID
> DeptID DeptName
> EmpName
> According to the above situation I have to make sure in the emp table
> there
> should be a foreign key constraint on DeptID column.. Is there any query
> through which I can make sure about it... hope now you understand my
> question.
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> table
> I
>
Sunday, February 12, 2012
constraint view
Hi,
I want to know which table my foreign is reference to
for instance I have this statement
ALTER TABLE BANK ADD CONSTRAINT BANKING_R_154 FOREIGN KEY (
COM_CODE ,
ACCOUNT_CODE ) references BANK_GLT
I want execute ine select * from information_schema.I_don't_know
to get the table BANK_GLT from constraint BANKING_R_154
How can I do this?
cheers,
Alessandro
Select * from INFORMATION_SCHEMA.Referential_Constraints RC
INNER JOIN INFORMATION_SCHEMA.Constraint_Column_usage CCU
ON RC.Constraint_Catalog = CCU.Constraint_Catalog AND
RC.Constraint_Schema = CCU.Constraint_Schema AND
RC.Constraint_Name = CCU.Constraint_Name
Where Table_Name = 'SomeTable' --for a table, use Constraint_Name for querying on Constraint Names
--
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Thanks,
but, I would like to know which table my tables is reference to and which columns as well
cheers,
Constraint using SQL query?
I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
....random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
Amit
Amit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
....random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
Amit
Amit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
Constraint using SQL query?
I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
.);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
.);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
Constraint using SQL query?
I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
Constraint Question
Is it possible to set an index of no duplicates on a column other than the primary key of a table? If yes, how is this done?
Yes, you can create unique index. Here is an example for creating one on a username column in Sql Server.
CREATE UNIQUE NONCLUSTERED INDEX [IX_USERNAME] ON [dbo].[USER]
(
[USERNAME] ASC
)
U can use unique index. But u should not forget that unique index allows null values which we can treat them as dup values
Thank u
Baba
Please remember to click "Mark as Answer" on this post if it helped you.
Constraint problem
I having problems figuring out how to use contraints.
I have three tables tbl_photos, tbl_customers, tbl_register.
tbl_register has a foreign key contraint from both tbl_photos and tbl_customers which are photo_id and cust_id.
I have the following code on a page where I am adding a customer to the tbl_customers but I also want to add a entry to the tbl_register showing that this customer has registered a particular photo_id. So I have to insert into the tbl_register the cust_id and the photo_id.
SQL_1 = "INSERT INTO tbl_customers(firstname, lastname, email, zipcode, optin) VALUES('"&firstname&"', '"&lastname&"', '"&email&"', '"&zipcode&"', '"&optin&"')"
SQL_2 = "SELECT cust_id, email FROM tbl_customers WHERE email = '"&email&"'"
SQL_3 = "INSERT INTO tbl_register(cust_id, photo_id) Values('"&new_cust_id&"', '"&photo_id&"')"
SQL_4 = "UPDATE tbl_photos SET registered = 'Yes'"
Set RS = conn.Execute(SQL_1)
Set RS = conn.Execute(SQL_2)
new_cust_id = RS.Fields("cust_id").Value
Set RS = conn.Execute(SQL_3)
Set RS = conn.Execute(SQL_4)
I am getting an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tbl_register_tbl_customers'. The conflict occurred in database 'photoctm_sql', table 'tbl_customers', column 'cust_id'.
I am sure there is a much better way of doing this!If it were me, I'd be using stored procedures. Create a procedure for your customer insert that uses the @.@.identity property to capture the cust_id that was used and return that value to your application as an output parameter.
Since my using the email address to look up the customer just added, you can't be certain that you're getting the right ID back unless you have a unique constraint on the email attribute. You're problem may be that there's already a customer with the given email address, although that should just register the photo with the wrong customer and not return an error.
However, the problem is most likely that you are building the SQL_3 string before you've assigned the value to new_cust_id. If you move your SQL3 = ... statement to just before it's executed, it should work...
If you're dead-set against using stored procedures, though, put a unique constraint on the email attribute at the very least.
I have three tables tbl_photos, tbl_customers, tbl_register.
tbl_register has a foreign key contraint from both tbl_photos and tbl_customers which are photo_id and cust_id.
I have the following code on a page where I am adding a customer to the tbl_customers but I also want to add a entry to the tbl_register showing that this customer has registered a particular photo_id. So I have to insert into the tbl_register the cust_id and the photo_id.
SQL_1 = "INSERT INTO tbl_customers(firstname, lastname, email, zipcode, optin) VALUES('"&firstname&"', '"&lastname&"', '"&email&"', '"&zipcode&"', '"&optin&"')"
SQL_2 = "SELECT cust_id, email FROM tbl_customers WHERE email = '"&email&"'"
SQL_3 = "INSERT INTO tbl_register(cust_id, photo_id) Values('"&new_cust_id&"', '"&photo_id&"')"
SQL_4 = "UPDATE tbl_photos SET registered = 'Yes'"
Set RS = conn.Execute(SQL_1)
Set RS = conn.Execute(SQL_2)
new_cust_id = RS.Fields("cust_id").Value
Set RS = conn.Execute(SQL_3)
Set RS = conn.Execute(SQL_4)
I am getting an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tbl_register_tbl_customers'. The conflict occurred in database 'photoctm_sql', table 'tbl_customers', column 'cust_id'.
I am sure there is a much better way of doing this!If it were me, I'd be using stored procedures. Create a procedure for your customer insert that uses the @.@.identity property to capture the cust_id that was used and return that value to your application as an output parameter.
Since my using the email address to look up the customer just added, you can't be certain that you're getting the right ID back unless you have a unique constraint on the email attribute. You're problem may be that there's already a customer with the given email address, although that should just register the photo with the wrong customer and not return an error.
However, the problem is most likely that you are building the SQL_3 string before you've assigned the value to new_cust_id. If you move your SQL3 = ... statement to just before it's executed, it should work...
If you're dead-set against using stored procedures, though, put a unique constraint on the email attribute at the very least.
constraint problem
Hi everybody
we have the following tables
1)
Country
Countryid CountyNAme
IN India
MY Malaysia
UK UnitedKingdom
here Countryid is the primary key.
2) MainDept
DeptID Deptname Countryid
CM CashManagement IN
CB ConsumerBanking MY
CS Customer Support IN
IB InternetBank IN
here deptid is the primary key
3) UserMaster
Uid Uname Deptid Countryid
001 Chris CM IN
002 Raja CS IN
003 Ram CB MY
here Uid is the primary key.
The problem is when i change the countryid from one country to another for a deptname.
THe change is not reflected in the usermaster table as it still shows the previous countryid.
For eg. user Chris belongs to dept Cash management which is situated in india.
Now if i change in mainDept table the cash management from india(IN) to say malaysia(MY).the corresponding change is not reflected in usermaster table.it still shows india. So when i query for chris in usermaster i get an error
as i am searching in india for cash mangement.
i tried using on update cascade but here it did not work as i have to make DeptID & countryID in MainDept table as composite key & use Deptid & countryid in usermaster as refernce key.
Since i have 20-25 tables also referencing the above 2 tables i have to set reference key in all these tables & these tables are in turn referenced elsewhere in other tables. Thus i end up creating a large no. of composite keys.
IS there any other way to solve this problem?
note : In sqlserver we can give on update cascade still it has the above problem
but in Oracle on update cascade is not possible
Can anybody suggest a solution for this in both sql server and in oracle
Thanks u verymuchhai saj,
How abt using a trigger in maindept table, which will update all the countryids in usermaster table for the corresponding deptid. ?
But in my opinion the countryid column in usermaster table looks like a redundant data. Since u have the deptid in usermaster table u can always get the countryid by the referencing the corresponding column in maindept (i am assuming that none of ur other tables r using ur usermaster table)
with regards
Sudar
we have the following tables
1)
Country
Countryid CountyNAme
IN India
MY Malaysia
UK UnitedKingdom
here Countryid is the primary key.
2) MainDept
DeptID Deptname Countryid
CM CashManagement IN
CB ConsumerBanking MY
CS Customer Support IN
IB InternetBank IN
here deptid is the primary key
3) UserMaster
Uid Uname Deptid Countryid
001 Chris CM IN
002 Raja CS IN
003 Ram CB MY
here Uid is the primary key.
The problem is when i change the countryid from one country to another for a deptname.
THe change is not reflected in the usermaster table as it still shows the previous countryid.
For eg. user Chris belongs to dept Cash management which is situated in india.
Now if i change in mainDept table the cash management from india(IN) to say malaysia(MY).the corresponding change is not reflected in usermaster table.it still shows india. So when i query for chris in usermaster i get an error
as i am searching in india for cash mangement.
i tried using on update cascade but here it did not work as i have to make DeptID & countryID in MainDept table as composite key & use Deptid & countryid in usermaster as refernce key.
Since i have 20-25 tables also referencing the above 2 tables i have to set reference key in all these tables & these tables are in turn referenced elsewhere in other tables. Thus i end up creating a large no. of composite keys.
IS there any other way to solve this problem?
note : In sqlserver we can give on update cascade still it has the above problem
but in Oracle on update cascade is not possible
Can anybody suggest a solution for this in both sql server and in oracle
Thanks u verymuchhai saj,
How abt using a trigger in maindept table, which will update all the countryids in usermaster table for the corresponding deptid. ?
But in my opinion the countryid column in usermaster table looks like a redundant data. Since u have the deptid in usermaster table u can always get the countryid by the referencing the corresponding column in maindept (i am assuming that none of ur other tables r using ur usermaster table)
with regards
Sudar
Subscribe to:
Posts (Atom)