interface supported: unique

Showing posts with label unique. Show all posts

Monday, March 26, 2012

Normalization Question....

Hi,
I have a table of entities. Each of which has a unique ID. For a certain
subset of those entities, a particular set of values can apply to a pair of
entities.
Let's call the entity ID's E1 and E2 and the associated values x, y and z.
E1 and E2 are GUID's and x, y and z are floats.
My temptation is to create a table that has E1 and E2 as the primary key and
have columns for x, y and z. However, since E1 and E2 both represent an ID
column that relate to the same table, I feel I am breaking the first normal
form.
Essentially what needs to happen is that a pair of entity ID's needs to be
related to x, y and z values and I want the table normalized. Any advice
would be appreciated. Thanks in advance.
-PetePete Wittig wrote:
> Hi,
> I have a table of entities. Each of which has a unique ID. For a certain
> subset of those entities, a particular set of values can apply to a pair o
f
> entities.
> Let's call the entity ID's E1 and E2 and the associated values x, y and z.
> E1 and E2 are GUID's and x, y and z are floats.
> My temptation is to create a table that has E1 and E2 as the primary key a
nd
> have columns for x, y and z. However, since E1 and E2 both represent an I
D
> column that relate to the same table, I feel I am breaking the first norma
l
> form.
> Essentially what needs to happen is that a pair of entity ID's needs to be
> related to x, y and z values and I want the table normalized. Any advice
> would be appreciated. Thanks in advance.
> -Pete
It is extremely difficult to give goosd advise on such questions
without the opportunity to analyse your requirements in detail. Here
are some ideas. If you are saying that (x,y,z) uniquely defines a set
of entities then you can implement a cascading foreign key:
CREATE TABLE foo (x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL,
PRIMARY KEY (x,y,z));
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL, FOREIGN KEY (x,y,z)
REFERENCES foo (x,y,z) ON UPDATE CASCADE);
Perhaps more likely though is that your set is defined by some other
attribute(s) you haven't mentioned (foo_key in this example):
CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
*/ INTEGER NOT NULL REFERENCES foo (foo_key));
Your choice of datatypes would make me want to review this design. I've
almost never used FLOAT in tables. Although it certainly has legitimate
uses, most of the time the exact types (INTEGER or DECIMAL for example)
are much more useful. FLOAT probably isn't a good choice for a key
because of its imprecise nature. I have difficulty making sense of your
business requirement if you don't have the additional key I proposed
for the "foo" table but then I don't know your business...
Also, UNIQUEIDENTIFIER, while it may be used as a PK isn't generally
good as the only key of a table. Certainly it shouldn't be so if its
purpose is an artificial surrogate.
David Portas
SQL Server MVP
--|||Hi David,
Thank you for the response. Allow me to clarify: I have a table that
describes my entities (call it Table E). E contains 1...n entries.
CREATE TABLE E (E_key UNIQUEIDENTIFIER PRIMARY KEY, Name nvarchar(100) NOT
NULL);
The other table I was tempted to create would have looked like this:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER, E2 UNIQUEIDENTIFIER, X FLOAT NOT
NULL, Y FLOAT NOT NULL, Z FLOAT NOT NULL,
PRIMARY KEY (E1, E2))
Where E1 and E2 would have been entries from Table E. So certain pairs of
entities (i.e. entry 5 and 2, entry 19 and 20) have parameters X, Y and Z.
X, Y and Z are dependent on the pair of entities.
If I understand you correctly, I think this method would work for me best:
"CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
*/ INTEGER NOT NULL REFERENCES foo (foo_key));"
However, allow me ask a few questions for clarification. In this case,
foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
Thanks again.
BTW: My reasoning for using floats is because I am storing math/scientific
data so I need a high level of precision.
-Pete
"David Portas" wrote:

> Pete Wittig wrote:
> It is extremely difficult to give goosd advise on such questions
> without the opportunity to analyse your requirements in detail. Here
> are some ideas. If you are saying that (x,y,z) uniquely defines a set
> of entities then you can implement a cascading foreign key:
> CREATE TABLE foo (x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL,
> PRIMARY KEY (x,y,z));
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL, FOREIGN KEY (x,y,z)
> REFERENCES foo (x,y,z) ON UPDATE CASCADE);
> Perhaps more likely though is that your set is defined by some other
> attribute(s) you haven't mentioned (foo_key in this example):
> CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));
> Your choice of datatypes would make me want to review this design. I've
> almost never used FLOAT in tables. Although it certainly has legitimate
> uses, most of the time the exact types (INTEGER or DECIMAL for example)
> are much more useful. FLOAT probably isn't a good choice for a key
> because of its imprecise nature. I have difficulty making sense of your
> business requirement if you don't have the additional key I proposed
> for the "foo" table but then I don't know your business...
> Also, UNIQUEIDENTIFIER, while it may be used as a PK isn't generally
> good as the only key of a table. Certainly it shouldn't be so if its
> purpose is an artificial surrogate.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:
> Hi David,
> Thank you for the response. Allow me to clarify: I have a table that
> describes my entities (call it Table E). E contains 1...n entries.
> CREATE TABLE E (E_key UNIQUEIDENTIFIER PRIMARY KEY, Name nvarchar(100) NOT
> NULL);
> The other table I was tempted to create would have looked like this:
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER, E2 UNIQUEIDENTIFIER, X FLOAT NOT
> NULL, Y FLOAT NOT NULL, Z FLOAT NOT NULL,
> PRIMARY KEY (E1, E2))
> Where E1 and E2 would have been entries from Table E. So certain pairs of
> entities (i.e. entry 5 and 2, entry 19 and 20) have parameters X, Y and Z.
> X, Y and Z are dependent on the pair of entities.
> If I understand you correctly, I think this method would work for me best:
> "CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));"
> However, allow me ask a few questions for clarification. In this case,
> foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
> Thanks again.
> BTW: My reasoning for using floats is because I am storing math/scientific
> data so I need a high level of precision.
> -Pete
>
Your choice of keys in the Params table looks very suspect to me. If E1
or E2 can appear multiple times then apparently these are groups, not
pairs. I had undestood that E1 determined (x,y,z) and that E2
determined the SAME (x,yz). If so, your table creates redundancy
because you allow E1 or E2 to be added multiple times with different
values of (x,y,z). Based on your original post I would have guessed:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL)
but now I have my doubts as to whether I understood you.
Design-by-newsgroup is a recipe for these kinds of misunderstandings
unfortunately.

> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));"
> However, allow me ask a few questions for clarification. In this case,
> foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
I doubt it, but you tell me. I was guessing there might be some
meaningful attribute that relates pairs or sets of entities together.
You wouldn't (shouldn't) need to use an artificial key unless (x,y,z)
are unique in foo.
David Portas
SQL Server MVP
--|||Thanks for the reply. Let me try and put this into context. In my
application, I want to look up an entity. That entity has certain attribute
s
which are returned such as its name. Another type of information is its
interaction parameters, how it interacts with other entities. This is the
data that I am trying to store.
So for a entity 1, I would return its name. Then I would want to return all
the ID's of the other entities it interacts with as well as the x, y and z
parameters associated with each particular pair/interaction (so potentially,
entity 1 & entity 2 and their associated x, y and z, entity 1 & entity 5 and
their associated x, y and z...).
Additionally, I will have a tool in which I can enter two entities and
return their x, y and z parameters.
In the application I will have the ID for entity 1, what I need to get is
all entity ID's that have an interaction with entity 1 and the associated x,
y and z parameters for that interaction.
I think you are correct in saying the key in my table Params looks suspect.
I agree. Since the x, y and z parameters would be the same for the pairs
entity1 & entity 2 and entity 2 & entity 1.
Does this help to clarify?
"David Portas" wrote:

> Pete Wittig wrote:
> Your choice of keys in the Params table looks very suspect to me. If E1
> or E2 can appear multiple times then apparently these are groups, not
> pairs. I had undestood that E1 determined (x,y,z) and that E2
> determined the SAME (x,yz). If so, your table creates redundancy
> because you allow E1 or E2 to be added multiple times with different
> values of (x,y,z). Based on your original post I would have guessed:
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
> REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
> (e_key), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL)
> but now I have my doubts as to whether I understood you.
> Design-by-newsgroup is a recipe for these kinds of misunderstandings
> unfortunately.
>
> I doubt it, but you tell me. I was guessing there might be some
> meaningful attribute that relates pairs or sets of entities together.
> You wouldn't (shouldn't) need to use an artificial key unless (x,y,z)
> are unique in foo.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:

> Thanks for the reply. Let me try and put this into context. In my
> application, I want to look up an entity. That entity has certain attribu
tes
> which are returned such as its name. Another type of information is its
> interaction parameters, how it interacts with other entities. This is the
> data that I am trying to store.
> So for a entity 1, I would return its name. Then I would want to return a
ll
> the ID's of the other entities it interacts with as well as the x, y and z
> parameters associated with each particular pair/interaction (so potentiall
y,
> entity 1 & entity 2 and their associated x, y and z, entity 1 & entity 5 a
nd
> their associated x, y and z...).
> Additionally, I will have a tool in which I can enter two entities and
> return their x, y and z parameters.
> In the application I will have the ID for entity 1, what I need to get is
> all entity ID's that have an interaction with entity 1 and the associated
x,
> y and z parameters for that interaction.
> I think you are correct in saying the key in my table Params looks suspect
.
> I agree. Since the x, y and z parameters would be the same for the pairs
> entity1 & entity 2 and entity 2 & entity 1.
> Does this help to clarify?
>
>
If we are only talking about pairs of entities then I think we're
nearly there. Just add the constraint E1<E2 so that you can never get
redundant pairs (A-B and B-A):
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
NOT NULL);
BTW I have left out the constraint names for brevity. You should always
give constraints your own names rather than let the system generate
them. It makes them much easier to maintain.
David Portas
SQL Server MVP
--|||Thanks for the reply.
In regards to this table:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
NOT NULL);
If I read this table correctly, it has only E1 as the primary key. I
believe that the primary key will have to be E1 and E2 since I could have
values like this (I've substituted ints for unique identifiers for
convenience in this example):
E1 E2 x y z
-- -- -- -- --
1 5 0.1 0.2 0.3
1 6 0.4 0.5 0.6
9 1 0.7 0.8 0.9
If I make the addition of including E2 in the primary key, will that effect
the "CHECK (e1 < e2)"?
I still have a question with regards to normalization. Since there is an E1
and E2 column in the Params table, does this mean it violates the 1NF? If
not, could you please explain why?
Thanks again.
"David Portas" wrote:

> Pete Wittig wrote:
>
> If we are only talking about pairs of entities then I think we're
> nearly there. Just add the constraint E1<E2 so that you can never get
> redundant pairs (A-B and B-A):
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
> REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
> (e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
> NOT NULL);
> BTW I have left out the constraint names for brevity. You should always
> give constraints your own names rather than let the system generate
> them. It makes them much easier to maintain.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:
> E1 E2 x y z
> -- -- -- -- --
> 1 5 0.1 0.2 0.3
> 1 6 0.4 0.5 0.6
> 9 1 0.7 0.8 0.9
> If I make the addition of including E2 in the primary key, will that effec
t
> the "CHECK (e1 < e2)"?
Still looks OK to me.

> I still have a question with regards to normalization. Since there is an
E1
> and E2 column in the Params table, does this mean it violates the 1NF? If
> not, could you please explain why?
>
First Normal Form is an elusive concept. Strictly speaking any table
conforms to 1NF if it has a candidate key and doesn't permit nulls. In
practice we look out for attributes that contain more than one domain
of values or have several columns representing the same attribute or
contain data structures encoded in strings. Loosely speaking we say
that these are a violation of 1NF. These are all subjective notions.
Sometimes it seems "obvious" when the spirit of 1NF is being violated.
Sometimes it is more tricky and the judgement will come down to one's
knowledge and experience of what works and what doesn't.
In your case we can say that using a pair of foreign keys in a single
table to represent a many-to-many relationship is a proven design that
appears frequently and that almost every database architect must have
used it. There are alternatives but the major benefit of your design is
that it's very easy to apply the necessary constraints for the business
rules. Your table is in 1NF and I don't see a better design.
David Portas
SQL Server MVP
--|||Thanks very much David. I appreciate the advice.
"David Portas" wrote:

> Pete Wittig wrote:
> Still looks OK to me.
>
> First Normal Form is an elusive concept. Strictly speaking any table
> conforms to 1NF if it has a candidate key and doesn't permit nulls. In
> practice we look out for attributes that contain more than one domain
> of values or have several columns representing the same attribute or
> contain data structures encoded in strings. Loosely speaking we say
> that these are a violation of 1NF. These are all subjective notions.
> Sometimes it seems "obvious" when the spirit of 1NF is being violated.
> Sometimes it is more tricky and the judgement will come down to one's
> knowledge and experience of what works and what doesn't.
> In your case we can say that using a pair of foreign keys in a single
> table to represent a many-to-many relationship is a proven design that
> appears frequently and that almost every database architect must have
> used it. There are alternatives but the major benefit of your design is
> that it's very easy to apply the necessary constraints for the business
> rules. Your table is in 1NF and I don't see a better design.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for your help David. I appreciate it.
"David Portas" wrote:

> Pete Wittig wrote:
> Still looks OK to me.
>
> First Normal Form is an elusive concept. Strictly speaking any table
> conforms to 1NF if it has a candidate key and doesn't permit nulls. In
> practice we look out for attributes that contain more than one domain
> of values or have several columns representing the same attribute or
> contain data structures encoded in strings. Loosely speaking we say
> that these are a violation of 1NF. These are all subjective notions.
> Sometimes it seems "obvious" when the spirit of 1NF is being violated.
> Sometimes it is more tricky and the judgement will come down to one's
> knowledge and experience of what works and what doesn't.
> In your case we can say that using a pair of foreign keys in a single
> table to represent a many-to-many relationship is a proven design that
> appears frequently and that almost every database architect must have
> used it. There are alternatives but the major benefit of your design is
> that it's very easy to apply the necessary constraints for the business
> rules. Your table is in 1NF and I don't see a better design.
> --
> David Portas
> SQL Server MVP
> --
>

Tuesday, March 20, 2012

NON-DETERMINISTIC?

I am trying to create a unique constraint on a computed column. I've tried
unique index, too, but they both fail w/this error:
Server: Msg 1933, Level 16, State 1, Line 2
Cannot create index because the key column 'msgID' is non-deterministic or
imprecise.
ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had to
use REPLACE in order to get rid of the date characters like MM/DD/YY. the
formula for MsgID is: (rtrim([endpoint]) +
replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
BOL says 1) all functions referenced by the expression are deterministic and
precise. 2) all columns referenced in the expression come from the table
containing the computed column and 3) no column reference pulls data from
multiple rows.
All of which I believe I'm good on. Each of these are SET ON:
ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
and NUMERIC_ROUNDABORT is SET OFF.
can somebody help me find what I'm missing?
-- LynnCan you give your table structure and some sample data?
http://www.aspfaq.com/5006
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Also, if you create a primary key or unique constraint on the three base
columns, do you really need the computed column to be explicitly unique
(since it should be unique by definition anyway)? I am often amazed at this
desire to store computed values when you don't have to; views and queries
could easily construct this value on select instead of storing redundant
data...
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Lynn,
The dateformat "1" uses a cutoff year which makes it imprecise.
The following example shows the problem and the solution.
create table #t(mydate datetime not null
,displaydate as convert(varchar(8),mydate,1)
)
create unique index someindex on #t(displaydate)
create table #t2(mydate datetime not null
,displaydate as
substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(10)
,mydate,101),9,2)
)
create unique index someindex2 on #t2(displaydate)
drop table #t
drop table #t2
Gert-Jan
Lynn wrote:
> I am trying to create a unique constraint on a computed column. I've trie
d
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had t
o
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic a
nd
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Gert-Jan, doing that, my value becomes this: 08/26/05
I need this MMDDYY, w/out the forward slashes in there.
is there no way to do that w/out becoming imprecise?
-- Lynn
"Gert-Jan Strik" wrote:

> Lynn,
> The dateformat "1" uses a cutoff year which makes it imprecise.
> The following example shows the problem and the solution.
> create table #t(mydate datetime not null
> ,displaydate as convert(varchar(8),mydate,1)
> )
> create unique index someindex on #t(displaydate)
> create table #t2(mydate datetime not null
> ,displaydate as
> substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(1
0),mydate,101),9,2)
> )
> create unique index someindex2 on #t2(displaydate)
> drop table #t
> drop table #t2
>
> Gert-Jan
>
> Lynn wrote:
>|||Yes, Aaron, unfortunately I do need it, as the composite PK of the three
columns invites duplicates. Meaning, it's endpoint+YYYY-MM-DD
HH:MM:MS:000+orderno. As weird as it may sound, the presence of the time
along w/the date is not desirable because like i said, it invites dupes.
Yes, I know the date w/out the time would seem as though it would do the sam
e
-- but it's something a little native to us, I suppose. So anyway, the
composite pk/constraint won't do, unless there's some way that I am unaware
of that will allow me to strip the time from the datestamp in the
constraint/pk.
can I do that? create a uniqe constraint and/or pk and/or unique index
(ideally, the constraint) on the three columns, but strip the time from the
exectime column?
exectime+endpoint+orderno
-- Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also, if you create a primary key or unique constraint on the three base
> columns, do you really need the computed column to be explicitly unique
> (since it should be unique by definition anyway)? I am often amazed at th
is
> desire to store computed values when you don't have to; views and queries
> could easily construct this value on select instead of storing redundant
> data...
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>
>|||What do you hope to gain from this particular format that cannot also be
accomplished using one of the standard ones. For constraint purposes, the
format of the date is not important. Does the computed column need to be in
this format for visual purposes? If so, why not create a 2nd column for
constraint purposes only.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
> Gert-Jan, doing that, my value becomes this: 08/26/05
> I need this MMDDYY, w/out the forward slashes in there.
> is there no way to do that w/out becoming imprecise?
>
> -- Lynn
>
> "Gert-Jan Strik" wrote:
>|||This particular format is our uniqueID. W/the time, however, it is invalid
for business/application reasons. I started down this path hoping to do a
PK, but learned I could not do a PK on a computed column. Hence, I am tryin
g
both the unique constraint or the unique index, both of which fail w/the
non-deterministic problem. So, possibly for constraint purposes the format
of the date is not important. But it is for our purposes. I am hopeful
that I am in error or possibly missing something quite obvious, but I need
the time stripped from the datetime stamp in the value, whether constraint,
computed or otherwise.
-- Lynn
"Scott Morris" wrote:

> What do you hope to gain from this particular format that cannot also be
> accomplished using one of the standard ones. For constraint purposes, the
> format of the date is not important. Does the computed column need to be
in
> this format for visual purposes? If so, why not create a 2nd column for
> constraint purposes only.
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
>
>|||On Fri, 26 Aug 2005 12:49:04 -0400, "Scott Morris" <bogus@.bogus.com>
wrote:
>What do you hope to gain from this particular format that cannot also be
>accomplished using one of the standard ones. For constraint purposes, the
>format of the date is not important. Does the computed column need to be i
n
>this format for visual purposes? If so, why not create a 2nd column for
>constraint purposes only.
What he said.
J.

Nonclustered UNIQUE INDEX

Hi
I have read about "Nonclustered UNIQUE INDEX" on BOL and
it am not clear what exactly is Nonclustered Unique Index!
Plese note i do understand the difference between the
Clustered and Nonclustered Index!
What i don't understand is the "Nonclustered UNIQUE
INDEX"? What does it mean when you create a "Nonclustered
UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
CONSRAINT and it allows duplicate values.
I appreciate if you could also tell me which columns are
usually good candidates for a Nonclustered UNIQUE INDEX.
Thank you,
Mitra> What i don't understand is the "Nonclustered UNIQUE
> INDEX"? What does it mean when you create a "Nonclustered
> UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
> CONSRAINT and it allows duplicate values.
You've made some assumptions here. If you create a UNIQUE INDEX then it
will not allow duplicate values. Just because you didn't explicitly create
a constraint doesn't mean one isn't implicitly created for you. Try it!
CREATE TABLE blat(foo INT)
CREATE UNIQUE INDEX splunge ON blat(foo)
GO
INSERT blat SELECT 1
INSERT blat SELECT 2
INSERT blat SELECT 3
SELECT foo FROM blat
GO
-- you will see 1, 2, 3 in the resultset.
-- however, when you try this:
INSERT blat SELECT 1
-- you will get:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'blat' with unique index
'splunge'.
The statement has been terminated.
So, essentially, a UNIQUE INDEX creates an index *and* it is enforced like a
unique constraint (though no constraint will show up using sp_help or
sp_helpconstraint).
Note that unless you include the word CLUSTERED the index will be
non-clustered.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Aaron,thank you for the clarification!
I guess I was confused because when I open the Design
Table window for a table in SQL Server 2000, in the
Properties dialog box there are two options for Create
UNIQUE setting: Constraint, and Index. Why is that?
Thanks,
Mitra
>--Original Message--
>> What i don't understand is the "Nonclustered UNIQUE
>> INDEX"? What does it mean when you create
a "Nonclustered
>> UNIQUE INDEX" on a column that it's NOT defined as
UNIQUE
>> CONSRAINT and it allows duplicate values.
>You've made some assumptions here. If you create a
UNIQUE INDEX then it
>will not allow duplicate values. Just because you
didn't explicitly create
>a constraint doesn't mean one isn't implicitly created
for you. Try it!
>
>CREATE TABLE blat(foo INT)
>CREATE UNIQUE INDEX splunge ON blat(foo)
>GO
>INSERT blat SELECT 1
>INSERT blat SELECT 2
>INSERT blat SELECT 3
>SELECT foo FROM blat
>GO
>-- you will see 1, 2, 3 in the resultset.
>-- however, when you try this:
>INSERT blat SELECT 1
>-- you will get:
>Server: Msg 2601, Level 14, State 3, Line 1
>Cannot insert duplicate key row in object 'blat' with
unique index
>'splunge'.
>The statement has been terminated.
>
>So, essentially, a UNIQUE INDEX creates an index *and*
it is enforced like a
>unique constraint (though no constraint will show up
using sp_help or
>sp_helpconstraint).
>Note that unless you include the word CLUSTERED the
index will be
>non-clustered.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||A constraint doesn't add an index, it just enforces uniqueness. You might
want to have 30 constraints on a table but you will be very unlikely to have
30 indexes that are helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
> Aaron,thank you for the clarification!
> I guess I was confused because when I open the Design
> Table window for a table in SQL Server 2000, in the
> Properties dialog box there are two options for Create
> UNIQUE setting: Constraint, and Index. Why is that?
> Thanks,
> Mitra
>>--Original Message--
>> What i don't understand is the "Nonclustered UNIQUE
>> INDEX"? What does it mean when you create
> a "Nonclustered
>> UNIQUE INDEX" on a column that it's NOT defined as
> UNIQUE
>> CONSRAINT and it allows duplicate values.
>>You've made some assumptions here. If you create a
> UNIQUE INDEX then it
>>will not allow duplicate values. Just because you
> didn't explicitly create
>>a constraint doesn't mean one isn't implicitly created
> for you. Try it!
>>
>>CREATE TABLE blat(foo INT)
>>CREATE UNIQUE INDEX splunge ON blat(foo)
>>GO
>>INSERT blat SELECT 1
>>INSERT blat SELECT 2
>>INSERT blat SELECT 3
>>SELECT foo FROM blat
>>GO
>>-- you will see 1, 2, 3 in the resultset.
>>-- however, when you try this:
>>INSERT blat SELECT 1
>>-- you will get:
>>Server: Msg 2601, Level 14, State 3, Line 1
>>Cannot insert duplicate key row in object 'blat' with
> unique index
>>'splunge'.
>>The statement has been terminated.
>>
>>So, essentially, a UNIQUE INDEX creates an index *and*
> it is enforced like a
>>unique constraint (though no constraint will show up
> using sp_help or
>>sp_helpconstraint).
>>Note that unless you include the word CLUSTERED the
> index will be
>>non-clustered.
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>http://www.aspfaq.com/
>>
>>.|||Uhm, Aaron?
A UNIQUE constraint always automatically adds an index, that's the only way
in SQL server you can implement it.
Mitra,
A constraint is part of your logical database design, an index is physical
construct. The effect of them is the same. It is best practice however to
enforce uniqueness via constraints, as it is an element of your logical
design, just like foreign keys for example. The only good reason to
implement a unique index without a unique constraint is if a subset of the
columns in the unique index is already covered by a unique constraint. One
example is when you have two columns with a unique constraint on it and for
performance reasons you also want an index with the columns in the opposite
order.
--
Jacco Schalkwijk
SQL Server MVP
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%233kkbVIOEHA.128@.TK2MSFTNGP12.phx.gbl...
> A constraint doesn't add an index, it just enforces uniqueness. You might
> want to have 30 constraints on a table but you will be very unlikely to
have
> 30 indexes that are helpful.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
> > Aaron,thank you for the clarification!
> >
> > I guess I was confused because when I open the Design
> > Table window for a table in SQL Server 2000, in the
> > Properties dialog box there are two options for Create
> > UNIQUE setting: Constraint, and Index. Why is that?
> >
> > Thanks,
> >
> > Mitra
> >
> >>--Original Message--
> >> What i don't understand is the "Nonclustered UNIQUE
> >> INDEX"? What does it mean when you create
> > a "Nonclustered
> >> UNIQUE INDEX" on a column that it's NOT defined as
> > UNIQUE
> >> CONSRAINT and it allows duplicate values.
> >>
> >>You've made some assumptions here. If you create a
> > UNIQUE INDEX then it
> >>will not allow duplicate values. Just because you
> > didn't explicitly create
> >>a constraint doesn't mean one isn't implicitly created
> > for you. Try it!
> >>
> >>
> >>CREATE TABLE blat(foo INT)
> >>CREATE UNIQUE INDEX splunge ON blat(foo)
> >>GO
> >>
> >>INSERT blat SELECT 1
> >>INSERT blat SELECT 2
> >>INSERT blat SELECT 3
> >>SELECT foo FROM blat
> >>GO
> >>
> >>-- you will see 1, 2, 3 in the resultset.
> >>-- however, when you try this:
> >>
> >>INSERT blat SELECT 1
> >>
> >>-- you will get:
> >>
> >>Server: Msg 2601, Level 14, State 3, Line 1
> >>Cannot insert duplicate key row in object 'blat' with
> > unique index
> >>'splunge'.
> >>The statement has been terminated.
> >>
> >>
> >>So, essentially, a UNIQUE INDEX creates an index *and*
> > it is enforced like a
> >>unique constraint (though no constraint will show up
> > using sp_help or
> >>sp_helpconstraint).
> >>
> >>Note that unless you include the word CLUSTERED the
> > index will be
> >>non-clustered.
> >>
> >>--
> >>Aaron Bertrand
> >>SQL Server MVP
> >>http://www.aspfaq.com/
> >>
> >>
> >>.
> >>
>|||Yes, much better explanation, sorry...
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OqBXTyMOEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Uhm, Aaron?
> A UNIQUE constraint always automatically adds an index, that's the only
way
> in SQL server you can implement it.
> Mitra,
> A constraint is part of your logical database design, an index is physical
> construct. The effect of them is the same. It is best practice however to
> enforce uniqueness via constraints, as it is an element of your logical
> design, just like foreign keys for example. The only good reason to
> implement a unique index without a unique constraint is if a subset of the
> columns in the unique index is already covered by a unique constraint. One
> example is when you have two columns with a unique constraint on it and
for
> performance reasons you also want an index with the columns in the
opposite
> order.

Monday, March 12, 2012

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
Thanks,
Jon A
If you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
David Gugick
Imceda Software
www.imceda.com
|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas

"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size it
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:
|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>
|||My sources: http://www.sql-server-performance.co...ed_indexes.asp
say that the uniqueifier used in a non-unique clustered index is a 4 byte value, as opposed to a (16 byte) GUID. Is there any other support either way? I can't find any in BOL.
jg

Quote:

...Originally posted by Anthony Thomas
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique...

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
--
Thanks,
Jon AIf you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
--
David Gugick
Imceda Software
www.imceda.com|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size it
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
--
Thanks,
Jon AIf you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
David Gugick
Imceda Software
www.imceda.com|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue> ). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18)
.
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(1
8)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue> ). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)reen">
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size i
t
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>

Monday, March 26, 2012

Normalization Question....

Tuesday, March 20, 2012

NON-DETERMINISTIC?

Nonclustered UNIQUE INDEX

Nonclustered UNIQUE INDEX

Nonclustered UNIQUE INDEX

Monday, March 12, 2012

Non unique Clustered index

Non unique Clustered index

Non unique Clustered index

interface supported

Blog Archive

About Me