Index Question

Hello,

In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys be
disabled? If Indexes (PK's and UN Constraints) can be disabled what happens
if data is inserted while disable? Will the index be rebuilt when enabled?

Thanks,
Rob PanoshRob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> happens if data is inserted while disable? Will the index be rebuilt
> when enabled?

You can disable foreign-key constraints. When you re-enable them, SQL
Server verifies that the data comply to the constraint.

You cannot disable primary-key or unique constraints, nor indexes.

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

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

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns940E15C48E6CYazorman@.127.0.0.1...
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Unfortunately, SQL-Server does not automatically check the existing
table data after turning a constraint back on. IMO it is a Microsoft
mistake to allow a database to get corrupted this way.

Gert-Jan

Erland Sommarskog wrote:
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> Unfortunately, SQL-Server does not automatically check the existing
> table data after turning a constraint back on.

You're right, Gert-Jan. Thanks for correcting my mistake.

> IMO it is a Microsoft mistake to allow a database to get corrupted this
> way.

I can see situations where you may want this, but its deceivable that
the constraint is not rechecked. Not only it makes you think that you
have a sound table. If you use the column with a CHECK constraint in a
partitioned view, you will scratch your hair, trying to find out why
SQL Server accesses all tables after this operation.

It is possible to identify this situation though. The example is
augmented script from Books Online:

SET QUOTED_IDENTIFIER OFF
go
CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)
go
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
go
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
go
-- Returns 1, because constraint has been disabled.
select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')"

Interesting, I did not know this yet. Something for a standard script to
check the database...

Gert-Jan

Erland Sommarskog wrote:
> Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> > Unfortunately, SQL-Server does not automatically check the existing
> > table data after turning a constraint back on.
> You're right, Gert-Jan. Thanks for correcting my mistake.
> > IMO it is a Microsoft mistake to allow a database to get corrupted this
> > way.
> I can see situations where you may want this, but its deceivable that
> the constraint is not rechecked. Not only it makes you think that you
> have a sound table. If you use the column with a CHECK constraint in a
> partitioned view, you will scratch your hair, trying to find out why
> SQL Server accesses all tables after this operation.
> It is possible to identify this situation though. The example is
> augmented script from Books Online:
> SET QUOTED_IDENTIFIER OFF
> go
> CREATE TABLE cnst_example
> (id INT NOT NULL,
> name VARCHAR(10) NOT NULL,
> salary MONEY NOT NULL
> CONSTRAINT salary_cap CHECK (salary < 100000)
> )
> go
> -- Valid inserts
> INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
> INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
> go
> -- This insert violates the constraint.
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Disable the constraint and try again.
> ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Reenable the constraint and try another insert, will fail.
> ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (4,"Eric James",110000)
> go
> -- Returns 1, because constraint has been disabled.
> select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

index question

Hi,
I have two tables -- A & B, which have primary key and
content lots of records respectively. B table has
foreign key refer to A table. If I create a index for
this foreign key in table B, will it improve performance
when I join these two tables in my query? Any suggestion
to improve the performance during join?
Thank you.
YulingYes It may improve a performance
Also look at join hints on BOL.
"Yuling" <ytu@.creativelabs.com> wrote in message
news:044601c35ade$51d3cb20$a601280a@.phx.gbl...
> Hi,
> I have two tables -- A & B, which have primary key and
> content lots of records respectively. B table has
> foreign key refer to A table. If I create a index for
> this foreign key in table B, will it improve performance
> when I join these two tables in my query? Any suggestion
> to improve the performance during join?
> Thank you.
> Yuling|||You should generally index all primary and foreign keys when doing joins...
If there are where clauses, you may see performance improvements if you
create non-clustered index on one of the highly selective where clause
criteria.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Yuling" <ytu@.creativelabs.com> wrote in message
news:044601c35ade$51d3cb20$a601280a@.phx.gbl...
> Hi,
> I have two tables -- A & B, which have primary key and
> content lots of records respectively. B table has
> foreign key refer to A table. If I create a index for
> this foreign key in table B, will it improve performance
> when I join these two tables in my query? Any suggestion
> to improve the performance during join?
> Thank you.
> Yuling

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Julesa non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Julesa non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Jules
a non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>
|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Julesa non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough

Index Cluster Vs. Index No Cluster

Hi,
it is good practices to have in a table (Hotels) Index not to cluster on the
HotelID column and Index to cluster on the foreign column HotelID in a table
details?
Thank you
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ering/200509/1
As always it depends.
Firstly what is the key, how is the key determined and what is the insert
activity?
An ascending integer key is fine to use as a clustered index but only if the
inserts are made in ascending order (1,2,3,4....)
If it's 1, 9999, 45, 77, 888, 4345,... then pages will have to split
eventually to make room for keys as the inserts proceed. SQL will put as
many keys as it can in a page (fill factors restrict this of course) so when
later on you insert a low key number then the page must be split. In this
instance it is better to have only a non-clustered index on the key. This
table is called a "heap".
If the insert activity is low then of course you may be better with a
clustered index after all.
If the inserts are 1, 3, 5, 6, 7, then each page fills up as the insertes
proceed and there is no need for splits because there are no gaps. This is
fine for a clustered index.
If the key is not a numeric but a Name or character data then do not use
this as the clustered index. This is because other indexes you create have
the primary key in their index as the target of the index so you duplicate a
long key for every additional index. In this case ceated a identifier column
for your clustered index and create a unique index on your long key.
Nik Marshall-Blank MCSD/MCDBA
"CYanez via droptable.com" <forum@.droptable.com> wrote in message
news:5467849175759@.droptable.com...
> Hi,
> it is good practices to have in a table (Hotels) Index not to cluster on
> the
> HotelID column and Index to cluster on the foreign column HotelID in a
> table
> details?
> Thank you
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ering/200509/1
|||Thank you Nik
Nik Marshall-Blank wrote:[vbcol=seagreen]
>As always it depends.
>Firstly what is the key, how is the key determined and what is the insert
>activity?
>An ascending integer key is fine to use as a clustered index but only if the
>inserts are made in ascending order (1,2,3,4....)
>If it's 1, 9999, 45, 77, 888, 4345,... then pages will have to split
>eventually to make room for keys as the inserts proceed. SQL will put as
>many keys as it can in a page (fill factors restrict this of course) so when
>later on you insert a low key number then the page must be split. In this
>instance it is better to have only a non-clustered index on the key. This
>table is called a "heap".
>If the insert activity is low then of course you may be better with a
>clustered index after all.
>If the inserts are 1, 3, 5, 6, 7, then each page fills up as the insertes
>proceed and there is no need for splits because there are no gaps. This is
>fine for a clustered index.
>If the key is not a numeric but a Name or character data then do not use
>this as the clustered index. This is because other indexes you create have
>the primary key in their index as the target of the index so you duplicate a
>long key for every additional index. In this case ceated a identifier column
>for your clustered index and create a unique index on your long key.
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ering/200509/1