Conversion of IDENTITY to UNIQUEIDENTIFIER during Replication?

Hi,
I tried to find an answer to this via BOL and web but to no avail.
Consider this situation:
CREATE TABLE [dbo].[foodetail] (
[fooid] [int] IDENTITY (1, 1) NOT NULL ,
[footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[foofact] (
[fooid] [int] NOT NULL ,
[volume] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
(
[fooid]
) ON [PRIMARY]
GO
I.e. a fact and a detail table that are joined via fooid but there is no
foreign key defined.
Now, is it possible to replicate content of these two tables and have
fooid converted consistently to a UUID during replication? If not, is it
possible to do it if there is a FK defined?
Thanks a lot!
Kind regards
robertWhy would you want to convert your primary key to GUID? Merge replication
will add rowguid column when you set it up and replicate independently. You
could add your own guid column and populate it, but there's no 'during
replication'. It stays
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I tried to find an answer to this via BOL and web but to no avail.
> Consider this situation:
> CREATE TABLE [dbo].[foodetail] (
> [fooid] [int] IDENTITY (1, 1) NOT NULL ,
> [footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[foofact] (
> [fooid] [int] NOT NULL ,
> [volume] [float] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
> CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
> (
> [fooid]
> ) ON [PRIMARY]
> GO
>
> I.e. a fact and a detail table that are joined via fooid but there is no
> foreign key defined.
> Now, is it possible to replicate content of these two tables and have
> fooid converted consistently to a UUID during replication? If not, is it
> possible to do it if there is a FK defined?
> Thanks a lot!
> Kind regards
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
[vbcol=seagreen]
> Why would you want to convert your primary key to GUID? Merge
> replication will add rowguid column when you set it up and replicate
> independently. You could add your own guid column and populate it,
> but there's no 'during replication'. It stays
I want to get data from n databases to a single centralized DB. In oder
to minimize changes needed to be done to application code ideally I use
IDENTITY columns on local instances and have them converted to GUID
columns during replication because IDENTITY is not globally unique (in
fact likelyhood of collisions is extremely high :-)).
Now, in order to not having to change application code and join generation
on the centralized server ideally we would continue to use the same column
names ("fooid" in this example).
As far as I understand functionality of merge replication, every row in a
table gets a GUID to uniquely identify the row. This would work for the
detail table but not for the fact table as that contains other detail id
columns as well and in order to be able to join them properly we would
need all detail table's GUID's here.
Basically the product in question was never meant to support replication
and now I'm trying to find out whether there's a way to retrofit that
efficiently (meaning developer time as well as run time). :-)
Cheers
robert|||I believe you're better off with adding the column on each table with local
info and extend keys to include it.
Something like 'locationID'. PK in that case is on two columns.
Not very nice, but it could be good enough. Implementing it wouldnt take a
lot of time since you would implement same changes on all databases and all
'LocationID' values are the same for each database.
As far as I can see, alternative would be to add guid column and then update
all FK columns with values from PK and then start replication or something.
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
> MC wrote:
>
>
> I want to get data from n databases to a single centralized DB. In oder
> to minimize changes needed to be done to application code ideally I use
> IDENTITY columns on local instances and have them converted to GUID
> columns during replication because IDENTITY is not globally unique (in
> fact likelyhood of collisions is extremely high :-)).
> Now, in order to not having to change application code and join generation
> on the centralized server ideally we would continue to use the same column
> names ("fooid" in this example).
> As far as I understand functionality of merge replication, every row in a
> table gets a GUID to uniquely identify the row. This would work for the
> detail table but not for the fact table as that contains other detail id
> columns as well and in order to be able to join them properly we would
> need all detail table's GUID's here.
> Basically the product in question was never meant to support replication
> and now I'm trying to find out whether there's a way to retrofit that
> efficiently (meaning developer time as well as run time). :-)
> Cheers
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
[vbcol=seagreen]
> I believe you're better off with adding the column on each table with
> local info and extend keys to include it.
Unfortunately "each table" means all tables that have to be replicated.

> Something like 'locationID'. PK in that case is on two columns.
> Not very nice, but it could be good enough. Implementing it wouldnt
> take a lot of time since you would implement same changes on all
> databases and all 'LocationID' values are the same for each database.
This would mean that the number of columns in fact tables (large!) nearly
doubles. Plus, this would necessitate an application change to change SQL
query generation (joins!).

> As far as I can see, alternative would be to add guid column and then
> update all FK columns with values from PK and then start replication
> or something.
Hmm... I'll have to think about this a bit. Thanks for the valuable
feedback!
Kind regards
robert

Contraints

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
>

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
>

Consuming result set from trigger in T-SQL

If I have a trigger that returns a result set, is there a way to consume
that result set from T-SQL?
E.G.
CREATE TABLE T
( id identity(1,1),
data varchar(10) )
CREATE TRIGGER mytrigger
ON T
AFTER INSERT
AS
SELECT id FROM inserted
-- This doesn't work:
-- SELECT * FROM (INSERT T(data) SELECT otherData FROM AnotherTable)
-- I can do this and consume the result set at the client though...
INSERT T(data) SELECT otherData FROM AnotherTable
thank you.No, AFAIK thats not possible. If you really, really wanna do this you could
store the resultset in XML in an extra table and query for.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"jahyen" <jahyen@.> schrieb im Newsbeitrag
news:OwV7aQQSFHA.252@.TK2MSFTNGP12.phx.gbl...
> If I have a trigger that returns a result set, is there a way to consume
> that result set from T-SQL?
> E.G.
> CREATE TABLE T
> ( id identity(1,1),
> data varchar(10) )
> CREATE TRIGGER mytrigger
> ON T
> AFTER INSERT
> AS
> SELECT id FROM inserted
> -- This doesn't work:
> -- SELECT * FROM (INSERT T(data) SELECT otherData FROM AnotherTable)
> -- I can do this and consume the result set at the client though...
> INSERT T(data) SELECT otherData FROM AnotherTable
> thank you.
>
>|||No obvious way to do this. Why not just perform the INSERT and SELECT as
separate operations in a stored proc? That way you can handle the result any
way you choose.
David Portas
SQL Server MVP
--|||This is not the purpose of a trigger. A trigger is used to maintain
relationships within the schema, when you cannot use DRI actions. Do
the SELECT as a sepaarate statement.

Constraints in SQL server

i have a question about constraints in SQL server.

i have three tables:

Client
---
clientID
name
etc...

Bank
---
BankID (a virtual number, identity)
ClientID (FK: Client)
BankAccountNumber
BankName

Order
---
OrderID
ClientID
BankID

a Client an have one or more bankAccounts.. if he place an order, he can select wich bankaccount will be used to pay.

how do i have to set the constraints so that the bankAccount in Order, is allways of THAT client in order.?

Thanx...can someone give me some help? please...|||I'm not sure exactly what you're asking. It seems that if your application is going to create an order for a customer and then let the customer determine which bank account they're going to use for the order, then you have the appropriate information already. You have bank account # (or bankid) and you have customer information (clientid).

BTW, it appears that ClientID is redundant data in the Order table. Through BankID you can derive ClientID.|||It sounds like you might need a trigger. Check out this article and see if the information contained within might help:Enforcing Business Rules with Triggers.

Terri

constraint?

Hi,
I was wondering how i could set up a constraint if i want to prevent
duplicates.
For example, in a table where i have 4 columns:
-id (identity and primary)
-contactid
-type
-period
One can have values like:
id | contactid | type | period
1 c123 1 11
2 c355 | 2 | 11
Now what i would like to do is if someone inserts a record like id 1 and
the period is 12, then the constraint should apply. If someone inserts a
record like id 2 and the period is 12 then it's ok.
Is this possible?Somethinmg like this for example:
ALTER TABLE your_table
ADD CONSTRAINT ak_constraint_name
UNIQUE (contactid, period) ;
David Portas
SQL Server MVP
--|||"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>
I'm still not quite sure what you are after here. You can create a UNIQUE
constraint/index across multiple columns in a table.
For your first example, a UNIQUE constraint across (id, contactid, type)
would do the trick.
What is the difference with record id 2? Is it because the type column has
pipe symbols in it? I'm not following.
If it is more complex than the simple UNIQUE constraint, then you can either
apply your rules in a stored procedure, or with a trigger.
Rick Sawtell
MCT, MCSD, MCDBA|||Create a unique index, duplicate inserts will fail.
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>|||Jason
What is about type column? What is the purpose of this column?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>|||Rick Sawtell wrote:
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
>
> I'm still not quite sure what you are after here. You can create a UNIQU
E
> constraint/index across multiple columns in a table.
> For your first example, a UNIQUE constraint across (id, contactid, type)
> would do the trick.
> What is the difference with record id 2? Is it because the type column h
as
> pipe symbols in it? I'm not following.
> If it is more complex than the simple UNIQUE constraint, then you can eith
er
> apply your rules in a stored procedure, or with a trigger.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Hi Rick,
The pipes are nothing, it's a seperator.
The difference is, that a contact with type 1 may be inserted only once.
Type 2 can have several inserts.
So i only want to constrain contacts which has type 1.|||create table semi_unique(id int primary key, stype int, uq int, u1 as
case when stype=1 then null else id end)
-- uq must be unique for type 1 only
create unique index u1 on semi_unique(uq, u1)
insert into semi_unique(id, stype, uq) values(1,1,1)
-- should fail
insert into semi_unique(id, stype, uq) values(2,1,1)
-- should succeed
insert into semi_unique(id, stype, uq) values(3,2,1)
insert into semi_unique(id, stype, uq) values(4,2,1)
select * from semi_unique
drop table semi_unique|||Alexander Kuznetsov wrote:
> create table semi_unique(id int primary key, stype int, uq int, u1 as
> case when stype=1 then null else id end)
> -- uq must be unique for type 1 only
> create unique index u1 on semi_unique(uq, u1)
> insert into semi_unique(id, stype, uq) values(1,1,1)
> -- should fail
> insert into semi_unique(id, stype, uq) values(2,1,1)
> -- should succeed
> insert into semi_unique(id, stype, uq) values(3,2,1)
> insert into semi_unique(id, stype, uq) values(4,2,1)
> select * from semi_unique
> drop table semi_unique
>
Hi it worked, but what if i add another column to it.

Constraint/identity which allows duplicate null fields

hi,

I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?

Thank you.

Make your value a foreign key constraint you can take the IDENTITY property of another table make sure your table is UNION compatible, create an index on another column in the same table and add that column in it through the new feature called INDEX Column include turn on the IGNORE_DUP_KEY option and you have a duplicate proof column. Now let me explain it UNION is a SET operator that performs implicit distinct by eliminating duplicates, and by adding it to an index it can also use the physical IGNORE_DUP_KEY which also eliminates duplicates and your column can get the benefits of an index without actually being the index. Run a search for UNION,IGNORE_DUP_KEY and INDEX COLUMN include in the BOL(books online). I am assuming you know you have to spend time with Management Studio to create this, post again if you still have question. Hope this helps.|||

Can't you just check before you do an INSERT?

IF NOT EXISTS( SELECT * FROM YourTable WHERE LinkedItemID = @.somevalue)

BEGIN

-- do the insert

END

|||

ndinakar:

Can't you just check before you do an INSERT?

IF NOT EXISTS( SELECT * FROM YourTable WHERE LinkedItemID = @.somevalue)

BEGIN

-- do the insert

END

That will do what IGNORE_DUP_KEY will do but a UNION will also eliminate duplicates in a Query of more than one table, the main difference between UNION and UNION ALL. It is the reason UNION comes with more restriction than UNION ALL.

|||

I am confused why we need a UNION, foreign key constraint, INDEX COLUMN etc in this scenario. IGNORE_DUP_KEY will work if the index has already been created with that option.

|||

(My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question )

This was the original poster's need I just showed all can be achived with a combination of SET algebra and the physical. UNION will eliminate duplicates in a query with another column that will introduce duplicates in the result and it comes with compatibility requirement in the table definition, you are checking for duplicates only during insert. Index column include will let that column to be included in a Unique index which require NOT NULL by default, so use the IGNORE_DUP_KEY option and SQL Server just toss the duplicates and continue any insert because ADO.NET does more than one insert. One more thing foriegn key constraints are nullable by ANSI SQL definition.

http://msdn2.microsoft.com/en-us/library/ms190806.aspx

|||

hi,

thanks all of you for your assistance. I still think your solution is far too complicated - there must be some easier way? Why I don't do a check before INSERT - it's because there are about 10 stored procs in my database which work with this particular field and it would be so much easier to do this by some kind of constraint (or even trigger?!)...

|||

EDIT

The quick solution is Unique Constraint because it allows NULLs and the other easy option is through the index column include in a Unique index, you just include it and use the IGNORE_DUP_KEY option, it is not complicated. UNION is complicated to set up if it was not included during the database design. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191166.aspx

Constraint question

I'm constructing a menu in a SQL Server database.
Each menu can have sub menus. So my table looks like this:

CREATE TABLE menu
(
idINT NOT NULL IDENTITY PRIMARY KEY,
nameVARCHAR(30)NOT NULL,
parentID INTNOT NULL /*ID Of Parent Menu -1 If Root*/
)

IS there a way of placing a constraint on it so if one menu is deleted
all its sub menus get deleted automatically. A normal foreign key
causes a cicrcular problem. Any ideas?Hi

I guess you could have a loop in a trigger

WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM menu
WHERE parentID not in ( SELECT ID FROM menu)
AND ParentID <> 1
END

John

<wackyphill@.yahoo.com> wrote in message
news:1103232008.918252.175160@.f14g2000cwb.googlegr oups.com...
> I'm constructing a menu in a SQL Server database.
> Each menu can have sub menus. So my table looks like this:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
> )
>
> IS there a way of placing a constraint on it so if one menu is deleted
> all its sub menus get deleted automatically. A normal foreign key
> causes a cicrcular problem. Any ideas?|||(wackyphill@.yahoo.com) writes:
> I'm constructing a menu in a SQL Server database.
> Each menu can have sub menus. So my table looks like this:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
> )

Better to let parentID be NULL if root. If you go for -1 you cannot
have an fkey constraint anyway.

> IS there a way of placing a constraint on it so if one menu is deleted
> all its sub menus get deleted automatically. A normal foreign key
> causes a cicrcular problem. Any ideas?

You would have to write a trigger, and skip the constraint. Or simply
do the cascading in the stored procedure that removes a menu.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info guys.

> Better to let parentID be NULL if root. If you go for -1 you cannot
> have an fkey constraint anyway.
Yeah, good point. Although I was concidering allowing multiple root
menus which is why I did it. Each menu w/ -1 would begin another major
High Level Menu System/Section. And to get a list of all the sections
simply search for the menus w/ a -1 parentID. I thought if things
swelled I'd cut down on the amount of menus that need to be returned in
a query that way. (They will end up being displayed in a tree control
that shows all menu's in the current section).

> You would have to write a trigger, and skip the constraint. Or simply
> do the cascading in the stored procedure that removes a menu.

OK, I'm just learning SQL Server and didn't want to skip over a feature
that would do it for me if there was one. I'll probably go w/ the
stored procedure method. How best to set it up so a database can only
be accessed through its stored procedures, and stop adhoc SQL commands
that would not inforce the cascading?|||Although, now that I think about it I certainly could do the same thing
w/ NULLS as w/ -1s :) Sorry, Wasn't thinking that one thru far enough.|||(wackyphill@.yahoo.com) writes:
> OK, I'm just learning SQL Server and didn't want to skip over a feature
> that would do it for me if there was one. I'll probably go w/ the
> stored procedure method. How best to set it up so a database can only
> be accessed through its stored procedures, and stop adhoc SQL commands
> that would not inforce the cascading?

It is of course not possible to lock out ad-hoc statements completely
from Query Analyzer completely for people with admin privileges.
.. But with judicial use of constraints you can prevent bad things from
happening, at least by mistake.

But for application design, yes, it is a good idea make all access with
through stored procedures, and only grant users access to the stored
procedures, but not directly to the tables.

The advantage of doing the cascading in the stored procedure, is that
you can keep a table constraint that prohibits deletion.

Overall, while cascading referential integrity is available in SQL Server,
there are several situations where it is not possible to use it, the
usefulness of the feature is limited.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Constraint or Index?

Suppose I have a table called "Languages" with two fields. One field is an Identity field that acts as the Primary Key. The second field is "LanguageName" which would naturally be "English", "Spanish", etc. What I want to do is put a constraint on the LanguageName field so that someone cannot enter the same name twice.

Is it better to create an Index --> Create UNIQUE and use:
a) Constraint?
b) Index with Ignore duplicate key checked?

Is there any benefit of one over the other for my purpose? Thanks.The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. The underlying changes are actually the same :)|||Ah-ha. Thanks!|||I'd use a constraint because it better reflects what your intentions for that field are. An index will do the job of course but and index is more of an implementation thing than a data/business rule thing.|||One field is an Identity field that acts as the Primary Key. The second field is "LanguageName" which would naturally be "English", "Spanish", etc.

Isn't language name a lovely natural key?

Constraint

Hi,
we want to check the paystatusid in the table Treatement
CREATE TABLE [Treatement] (
[treatementid] int IDENTITY(1,1) NOT NULL ,
[customerid] int NOT NULL ,
[paystatusid] int NOT NULL , ....
and
CREATE TABLE [Paystatus] (
[paystatusid] int IDENTITY(1,1) NOT NULL ,
[customerid] int NOT NULL ,...
so that the Paystatus it refers to has the same customerid.
It is also accepted that paystatusid of the Treatement table is 0 and hence
dont refer to a Paystatus.
Olav"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:B72A4068-27AB-4C99-83CC-982ABB0DC79A@.microsoft.com...
> Hi,
> we want to check the paystatusid in the table Treatement
> CREATE TABLE [Treatement] (
> [treatementid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,
> [paystatusid] int NOT NULL , ....
> and
> CREATE TABLE [Paystatus] (
> [paystatusid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,...
> so that the Paystatus it refers to has the same customerid.
> It is also accepted that paystatusid of the Treatement table is 0 and
> hence
> dont refer to a Paystatus.
> Olav
>
You have a few choices here (listed in my order of preference)
1. Set up your FK constraint and include a 0 paystatusid in the PayStatus
table.
2. Use a trigger to handle your special case of a 0 paystatusid.
3. Use a stored procedure to perform these validations.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick,
i am temped by the FK alternative:
But there is an foreign key on the customerid referencing the customer
table. So in the Treatement table customerid will alway have a value but
paystatusid can be 0 meaning it is not yet connected to an Paystatus object.
Still possible? If still so please describe it.
Regards,
Olav
"Rick Sawtell" wrote:

> "Olav" <Olav@.discussions.microsoft.com> wrote in message
> news:B72A4068-27AB-4C99-83CC-982ABB0DC79A@.microsoft.com...
> You have a few choices here (listed in my order of preference)
> 1. Set up your FK constraint and include a 0 paystatusid in the PayStatus
> table.
> 2. Use a trigger to handle your special case of a 0 paystatusid.
> 3. Use a stored procedure to perform these validations.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Olav wrote:
> Hi,
> we want to check the paystatusid in the table Treatement
> CREATE TABLE [Treatement] (
> [treatementid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,
> [paystatusid] int NOT NULL , ....
> and
> CREATE TABLE [Paystatus] (
> [paystatusid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,...
> so that the Paystatus it refers to has the same customerid.
> It is also accepted that paystatusid of the Treatement table is 0 and henc
e
> dont refer to a Paystatus.
> Olav
There's very little information to go on here. As a minimum, please
include primary/unique key declarations with your DDL.
Why do paystatusid and customerid both appear in both tables? If you do
that you surely ought to have a foreign key in place.
David Portas
SQL Server MVP
--|||Okay here it comes, i will drop the nonerelevalnt columns:
CREATE TABLE [Treatement] (
[treatementid] int IDENTITY(1,1) NOT NULL ,
[customerid] int NOT NULL ,
[appbookid] int NOT NULL ,
[paystatusid] int NOT NULL ,
CONSTRAINT [PK_Treatement_treatementid] PRIMARY KEY NONCLUSTERED
([treatementid]),
CONSTRAINT [FK_Treatement_Appbook] FOREIGN KEY ([appbookid]) REFERENCES
[dbo].[Appbook] ([appbookid]),
CONSTRAINT [FK_Treatement_Customer] FOREIGN KEY ([customerid]) REFERENCES
[dbo].[Customer] ([customerid]))
CREATE TABLE [Paystatus] (
[paystatusid] int IDENTITY(1,1) NOT NULL ,
[customerid] int NOT NULL ,
[appbookid] int NOT NULL ,
CONSTRAINT [PK_Paystatus] PRIMARY KEY CLUSTERED ([paystatusid]),
CONSTRAINT [FK_Paystatus_Customer] FOREIGN KEY ([customerid]) REFERENCES
[dbo].[Customer] ([customerid]))
Olav
"David Portas" wrote:

> Olav wrote:
> There's very little information to go on here. As a minimum, please
> include primary/unique key declarations with your DDL.
> Why do paystatusid and customerid both appear in both tables? If you do
> that you surely ought to have a foreign key in place.
> --
> David Portas
> SQL Server MVP
> --
>

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas

"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff
choppertoo
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message555779.html

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:
> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:
> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaf
-
chopperto
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message555779.htm

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff