Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
Ed
You can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed

Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would b
e
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed

Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Edsql

alter table MyTable with check check constraint all

Hi

Is it correct to use the following statement :

alter table MyTable with check check constraint all

to reenable all the foreign keys and check constraints on a table after a bulk load ?

best regards,

Thibaut Barrère

(follow-up from this on the ssis forum, but it's more a tsql question, hence my post here).Here goes nothing...

-- Disable all constraints for a given table
ALTER TABLE [Some_Table] NOCHECK CONSTRAINT ALL

-- Enable all constraints for a given table
ALTER TABLE [Some_Table] CHECK CONSTRAINT ALL

-- Reseed the identity of a table back to zero (next record will have auto ident set to 1)
DBCC CHECKIDENT ([Some_Table], RESEED, 0)

-- "Run" table constraints on a table to verify that everything is ok
DBCC CHECKCONSTRAINTS ('[Some_Table]')|||

yes.. You can do this..

Advantage for disabling the constraint on bulk load is to increase the performance..

But if any data conflicts with your constraint you can't reenable it again..

|||Hi

the SQL server destination in SSIS disables the constraints itself when the check constraints checkbox is disabled.

Running the alter with check check all seems to work just fine, and properly detects any error.

Thanks for your answers!

Thibautsql

alter identity property of a column to NOT FOR REPLICATION

i need to alter all foreign keys in my database and uncheck the
"Enforce relationship for replication" check box. Using the EM, I
extracted the code snippet below. unfortunately, when i run this test
from query analyzer, then go back into the EM, the box is still
checked.

can anyone tell me what i am missing? any advice on unsetting this
attribute globally would be appreciated!

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
) NOT FOR REPLICATION

GO
COMMIT

thanks!!dayong (reedmb89@.yahoo.com) writes:
> i need to alter all foreign keys in my database and uncheck the
> "Enforce relationship for replication" check box. Using the EM, I
> extracted the code snippet below. unfortunately, when i run this test
> from query analyzer, then go back into the EM, the box is still
> checked.

It's not simply a refresh issue? I was not able to reproduce this, of
the simple reason that I was not able find where you poke with FKs in
Enterprise Manager. I prefer to work exclusively with SQL statements
for DDL statements.

You can use "sp_helpconstraint" in Query Analyzer to verify the status
of the constraint.

> can anyone tell me what i am missing? any advice on unsetting this
> attribute globally would be appreciated!

As long as you know which the foreign keys are, going like the code you
included should not be a problem.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
first, obviously, i'm new to sql server. thanks for your advice so far.
you are correct, it was a refresh issue. unfortunately, i cannot find a
simple way to find the foreign keys that are set for replication. i
looked at the stored procedure you advised (sp_helpconstraint). it
appears to create a temp table and then query and join info and
eventually has a boolean value where if true is_for_replication and
false not_for_replication.

this code is greek to me in my early stages of sql server
administration. is there a simpler way to locate the keys and columns
that are set is_for_replication?

thanks in advance for any advice!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Michael Reed (anonymous@.anonymous.com) writes:
> first, obviously, i'm new to sql server.

If you find out how get the commands that EM runs, and run them
in Query Analyzer, you have come a long way compared to many other
SQL Server newbies!

> this code is greek to me in my early stages of sql server
> administration. is there a simpler way to locate the keys and columns
> that are set is_for_replication?

This SELECT lists all foreign key constraints that are set for replication,
and the parent table:

select tbl = object_name(parent_obj), fk_name = name
from sysobjects
where xtype = 'F' and objectproperty(id, 'CnstIsNotRepl') = 0
order by tbl, fk_name

I don't know how many constraints you have. If you have only a handful,
you might be able to the rest manually. If you have hundreds of table,
you probably want a list of the columns in each FK. Since I'm lazy, and
I don't have a query ready for that right now, I don't include one. :-)

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

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

please re-post your last response. i can only see the summary. when i
click on the link, your post is nowhere to be found.

please re-post.

thanks!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

all indexes lost

I have recently had a a database where all indexes (over 300) have been lost
for no apparent reason. - in addition to this some primary keys were also
lost.
a review of the t-log archives shows no sign of DDL statements being
executed and the only conclusion i can come to is that rows from sysindexes
have been lost.
the indexes were lost during normal working hours, no reindexes or defrags
were taking place and the objects were lost in a matter of seconds.
we also suspected a user may have downloaded some sort of optimisation tool
and tried to "optimise" the database without knowledge, however no-one with
those permissions was logged in at the time.
has anyone else experienced this or can they point me at a KB articleshadowswiss wrote:
> I have recently had a a database where all indexes (over 300) have
> been lost for no apparent reason. - in addition to this some primary
> keys were also lost.
> a review of the t-log archives shows no sign of DDL statements being
> executed and the only conclusion i can come to is that rows from
> sysindexes have been lost.
> the indexes were lost during normal working hours, no reindexes or
> defrags were taking place and the objects were lost in a matter of
> seconds.
> we also suspected a user may have downloaded some sort of
> optimisation tool and tried to "optimise" the database without
> knowledge, however no-one with those permissions was logged in at the
> time.
> has anyone else experienced this or can they point me at a KB article
If someone ran the index tuning wizard, they could have instructed it to
kill old indexes infavor of the recommended list of new indexes. I doubt
this option would have killed any constraints, unless they were just
being enforces using indexes as opposed to the declarative approach.
I've personally never seen this reported before as a bug in SQL Server.
This type of problem is normally someone executing some T-SQL to to
this. I would immediately secure the SQL Server by removing all users
from the admin group that really don't require admin rights and changing
existing passwords for those admins that require those rights.
Although, I would expect to see these drop statements in the t-log. I
assume you are using a 3rd-party log reader to review the t-logs.
Have you checked database integrity using DBCC CHECKDB to see if any
errors are reported?
David Gugick
Quest Software
www.imceda.com
www.quest.com

all indexes lost

I have recently had a a database where all indexes (over 300) have been lost
for no apparent reason. - in addition to this some primary keys were also
lost.
a review of the t-log archives shows no sign of DDL statements being
executed and the only conclusion i can come to is that rows from sysindexes
have been lost.
the indexes were lost during normal working hours, no reindexes or defrags
were taking place and the objects were lost in a matter of seconds.
we also suspected a user may have downloaded some sort of optimisation tool
and tried to "optimise" the database without knowledge, however no-one with
those permissions was logged in at the time.
has anyone else experienced this or can they point me at a KB article
shadowswiss wrote:
> I have recently had a a database where all indexes (over 300) have
> been lost for no apparent reason. - in addition to this some primary
> keys were also lost.
> a review of the t-log archives shows no sign of DDL statements being
> executed and the only conclusion i can come to is that rows from
> sysindexes have been lost.
> the indexes were lost during normal working hours, no reindexes or
> defrags were taking place and the objects were lost in a matter of
> seconds.
> we also suspected a user may have downloaded some sort of
> optimisation tool and tried to "optimise" the database without
> knowledge, however no-one with those permissions was logged in at the
> time.
> has anyone else experienced this or can they point me at a KB article
If someone ran the index tuning wizard, they could have instructed it to
kill old indexes infavor of the recommended list of new indexes. I doubt
this option would have killed any constraints, unless they were just
being enforces using indexes as opposed to the declarative approach.
I've personally never seen this reported before as a bug in SQL Server.
This type of problem is normally someone executing some T-SQL to to
this. I would immediately secure the SQL Server by removing all users
from the admin group that really don't require admin rights and changing
existing passwords for those admins that require those rights.
Although, I would expect to see these drop statements in the t-log. I
assume you are using a 3rd-party log reader to review the t-logs.
Have you checked database integrity using DBCC CHECKDB to see if any
errors are reported?
David Gugick
Quest Software
www.imceda.com
www.quest.com

all indexes lost

I have recently had a a database where all indexes (over 300) have been lost
for no apparent reason. - in addition to this some primary keys were also
lost.
a review of the t-log archives shows no sign of DDL statements being
executed and the only conclusion i can come to is that rows from sysindexes
have been lost.
the indexes were lost during normal working hours, no reindexes or defrags
were taking place and the objects were lost in a matter of seconds.
we also suspected a user may have downloaded some sort of optimisation tool
and tried to "optimise" the database without knowledge, however no-one with
those permissions was logged in at the time.
has anyone else experienced this or can they point me at a KB articleshadowswiss wrote:
> I have recently had a a database where all indexes (over 300) have
> been lost for no apparent reason. - in addition to this some primary
> keys were also lost.
> a review of the t-log archives shows no sign of DDL statements being
> executed and the only conclusion i can come to is that rows from
> sysindexes have been lost.
> the indexes were lost during normal working hours, no reindexes or
> defrags were taking place and the objects were lost in a matter of
> seconds.
> we also suspected a user may have downloaded some sort of
> optimisation tool and tried to "optimise" the database without
> knowledge, however no-one with those permissions was logged in at the
> time.
> has anyone else experienced this or can they point me at a KB article
If someone ran the index tuning wizard, they could have instructed it to
kill old indexes infavor of the recommended list of new indexes. I doubt
this option would have killed any constraints, unless they were just
being enforces using indexes as opposed to the declarative approach.
I've personally never seen this reported before as a bug in SQL Server.
This type of problem is normally someone executing some T-SQL to to
this. I would immediately secure the SQL Server by removing all users
from the admin group that really don't require admin rights and changing
existing passwords for those admins that require those rights.
Although, I would expect to see these drop statements in the t-log. I
assume you are using a 3rd-party log reader to review the t-logs.
Have you checked database integrity using DBCC CHECKDB to see if any
errors are reported?
David Gugick
Quest Software
www.imceda.com
www.quest.com