INSERT INTO on CHAR fieldtype with auto increment?

Danny

unread,

Dec 22, 2009, 1:41:43 PM12/22/09

to

Is it possible to do an INSERT INTO statement on a field with an auto increment on and as fieldtype CHARACTER? Am using SQL server 2000.

The current situation:
Table:
dba.Equipment

Related field from this table:
EqmId (CHAR (14)/ PRIMARY KEY/ auto increment)

I cannot do an INSERT with an auto increment on a field based on a CHAR fieldtype.
When doing te INSERT INTO statement, the value of EqmId needs to raise with +1.

Can I solve this problem and how? I'd appreciate it if you have the answer, giving
me an explanation in clear language and not too complicated.

In advance a lot of thanks!!

Danny

--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 11
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

Plamen Ratchev

unread,

Dec 22, 2009, 2:11:44 PM12/22/09

to

You can add an IDENTITY column and then create the EqmId column as computed column based on the IDENTITY column, casted
to CHAR. However, not sure why you need it as CHAR. Also, maybe you can use another attribute to be the primary key, if
one exists to identify each piece of equipment.

--
Plamen Ratchev
http://www.SQLStudio.com

Danny

unread,

Dec 22, 2009, 2:50:23 PM12/22/09

to

Do I set the EqmId column as an IDENTITY column?

The EqmId had always the CHAR fieldtype. It used to be possible for users
to change the primary key or define one by themselfes. Later it was changed
to auto increment as from a specific number. So there is already CHAR based
data in this column and not only numbers. Is a CAST working in this case?

Is it possible to give me the example of the query it should be?

Thx :)

Plamen Ratchev

unread,

Dec 22, 2009, 3:08:58 PM12/22/09

to

You are casting the INT value to CHAR, so yes, it works. Here is example:

CREATE TABLE Equipment (
foo_id INT IDENTITY(1, 1) NOT NULL,
equipment_id AS CAST(foo_id AS CHAR(14)) PERSISTED NOT NULL PRIMARY KEY);

INSERT INTO Equipment DEFAULT VALUES;
INSERT INTO Equipment DEFAULT VALUES;

SELECT foo_id, equipment_id
FROM Equipment;

/*

foo_id equipment_id
----------- --------------
1 1
2 2

*/

Danny

unread,

Dec 22, 2009, 4:07:10 PM12/22/09

to

I see a new column (foo_id) is created with IDENTITY. The thing is, that
EqmId has already IDENTITY, although it's a CHAR fieldtype.

We also use different contexts on Equipment. Different departments use the
same Equipment table.

Danny

unread,

Dec 22, 2009, 4:07:10 PM12/22/09

to

Plamen Ratchev

unread,

Dec 22, 2009, 6:08:05 PM12/22/09

to

Are you sure you are using SQL Server? In SQL Server if you try to create a CHAR data type column with the IDENTITY
property you will get something like this:

Identity column 'foo_id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0...

It very clearly states what the data types of the IDENTITY column can be.

Maybe best to post in forum related to your database product.

Bob Barrows

unread,

Dec 23, 2009, 11:04:12 AM12/23/09

to

Danny wrote:
> Is it possible to do an INSERT INTO statement on a field with an auto
> increment on and as fieldtype CHARACTER? Am using SQL server 2000.
>
> The current situation:
> Table:
> dba.Equipment
>
> Related field from this table:
> EqmId (CHAR (14)/ PRIMARY KEY/ auto increment)
>

Do us a favor: Use Enterprise Manager to generate the CREATE TABLE
script for this table (right-click the table and choose All
Tasks>Generate SQL Script) and show it to us. It is not possible to have
a CHAR IDENTITY column in SQL Server.

--
HTH,
Bob Barrows

--CELKO--

unread,

Dec 23, 2009, 4:49:15 PM12/23/09

to

>> Is it possible to do an INSERT INTO statement on a field [sic: columns are not fields] with an auto increment [sic: SQL is set oriented and not sequential] on and as fieldtype [sic: what is this? we have data types with constraints in SQL] CHARACTER? Am using SQL server 2000. <<

SQL is a set-oriented language. You insert SETS of rows into tables;
the set goes in all at once. There is no ordering, so there can be no
incrementing.

You have to find non-relational and/or proprietary kludges; IDENTITY
is the most common one in T-SQL. Of course there is no way to verify
or validate the pseudo identifier, so you have no data integrity. The
numbers are the count of the attempted PHYSICAL disk insertions -- not
the successes, so you get gaps. It is non-deterministic.

But if you take the time to actually design the equipment
identification codes, then you can verify and validate the equipment
identifier. Do you know what a check digit is? Did you research
industry standards?

I do not give quick and dirty answers ..

Tony Rogerson

unread,

Dec 24, 2009, 1:25:08 AM12/24/09

to

> SQL is a set-oriented language. You insert SETS of rows into tables;
> the set goes in all at once. There is no ordering, so there can be no
> incrementing.

It was a long while back; but I understood your Nested Sets model that you
popularise relies on ordering?

Shame - could that be true? Calling one thing yet doing something entirely
different yourself?

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:4b617f9c-cc32-42e7...@j24g2000yqa.googlegroups.com...

--CELKO--

unread,

Dec 25, 2009, 5:18:41 PM12/25/09

to

>> It was a long while back; but I understood your Nested Sets model that you popularize relies on ordering?m <<

No. Where did you get that idea? The (lft, rgt) pairs are a
hierarchical co-ordinate system. It is no more ordered that
(longitude, latitude) pairs or HTM numbering in geographical data.
Here is a skeleton organizational chart as a Nested Sets.

CREATE TABLE OrgChart
(emp_name CHAR(10) NOT NULL PRIMARY KEY,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt));

Here is one possible insertion. Mix up the rows anyway you want;
insert each row one at a time. It is still the same tree.

INSERT INTO OrgChart (emp_name, lft, rgt)
VALUES ('Albert', 1, 12),
('Bert', 2, 3),
('Chuck', 4, 11),
('Donna', 5, 6),
('Eddie', 7, 8),
('Fred', 9, 10);

Tony Rogerson

unread,

Dec 26, 2009, 4:00:55 AM12/26/09

to

> No. Where did you get that idea? The (lft, rgt) pairs are a
> hierarchical co-ordinate system. It is no more ordered that
> (longitude, latitude) pairs or HTM numbering in geographical data.
> Here is a skeleton organizational chart as a Nested Sets.

Lol, longtitude is ordered, as is latitude - just check ANY atlas; you
certainly dont see reading right to left, 1, 2, 7, 3, 4, 22, 10 etc...; its
1,2,3,4,5,6,7 etc... -- ie. ORDERED.

You rely on ordering as I rely on the kettle being boiled to make a cup of
tea.

Yes - you allow gaps, but sure as the sun rises every day in the East you
rely on ordering - even your example is - ordered!

Just as the original poster wanted auto-increment; your solution benefits
from auto-increment; if your left and right columns are related to the data
they model then they would be stable and never changing - as Codd defines.

Yet, you allow that to change.

Shall we continue? Did you ever fix this problem with your published code?
http://www.dbdebunk.com/page/page/1490837.htm

--ROGGIE---

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:dfa156aa-0e95-4f39...@j19g2000yqk.googlegroups.com...

--CELKO--

unread,

Dec 26, 2009, 10:22:41 AM12/26/09

to

>>Lol, longitude is ordered, as is latitude <<

But (longitude, latitude) has no linear ordering. Same thing with
(x,y) co-ordinates. Remember Cantor's diagonal in your graduate math
course on sets?

Get the (longitude, latitude) location of London. What is the
successor? What is the predecessor? Get the HTM location of London.
What is the successor? What is the predecessor? It makes no sense;
the most you do is talk about a neighborhood and a distance function
in such systems.

Do you need to have a predecessor (longitude, latitude) before I can
enter a new location by applying the successor function ? Nope. Each
(longitude, latitude) is independent of a successor function. Sorting
them left to right has nothing to do with an ordering.

The successor function is increment in the case of natural numbers
(remember Peano's postulates?). I can use Cantor's diagonal to order
rational numbers. But I cannot write a list of all the Reals.

>> You rely on ordering as I rely on the kettle being boiled to make a cup of tea. <<

No, nested sets rely on a 2-D model like the HTM grids. I look at the
(lft, rgt) pair and know the size of the subtree at that root node; I
know its location relative the whole tree (1, n).

>> Just as the original poster wanted auto-increment; your solution benefits from auto-increment; if your left and right columns are related to the data they model then they would be stable and never changing - as Codd defines. <<

If the fact that it is handy to use sequential numbers to make the
math easier for subtree size, then go back and use any numbers
(positive, negative, decimal, etc.) you want as long as the (lft <
rgt) and containment proprieties are preserved.

Obviously you never read anything on Nested Sets and the super-simple
example I posted confused you. The Nested Sets model is a
relationship, not entities; this is why the nodes are not in the same
table as the tree structure.

A BOM and an organization could both have the same tree structure. In
fact, that is somebody's observation that a product has the same
structure as the organization that created it.

Using your logic, motion would be impossible because (longitude,
latitude) could change. The (lft, rgt) pair has to be unique but that
does not make it a key for the nodes. It is a key for the tree
structure with constraints about overlaps, dups, and other tree
properties.

>> Yet, you allow that to change. <<

Which change? If I re-do the organizational structure, then I change
the tree table (it is now a new tree); if I change personnel, I change
the nodes table. I probably should have shown a Personnel table
refre3nced by an emp_id instead of assuming you knew the model.

>> Shall we continue? Did you ever fix this problem with your published code? http://www.dbdebunk.com/page/page/1490837.htm <<

I did fix it; I said "Oh shit, you're right!" as noted. Amazing how
you have time to Google a six year old typo but cannot learn
Nested Sets. Google up Nested Intervals for a version of this co-
ordinate system using rational numbers instead of integers. On a
slightly different note, I heard a rumor that Fabian had died, but
have no confirmation or denial.

How is your Sodoku puzzle coming?

Erland Sommarskog

unread,

Dec 26, 2009, 12:59:16 PM12/26/09

to

--CELKO-- (jcel...@earthlink.net) writes:
> Get the (longitude, latitude) location of London. What is the
> successor? What is the predecessor? Get the HTM location of London.
> What is the successor? What is the predecessor? It makes no sense;
> the most you do is talk about a neighborhood and a distance function
> in such systems.

So you are saying that having a single IDENTITY column in a table is
bad, but if we have two it's OK, because then there is no ordering?

Nested sets is certainly a clever solution that can be useful in some
situations, but it builds on a completely artificial number that has no
relation to the data. Not that there is anything wrong with it in my book.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Tony Rogerson

unread,

Dec 26, 2009, 1:31:58 PM12/26/09

to

> So you are saying that having a single IDENTITY column in a table is
> bad, but if we have two it's OK, because then there is no ordering?

No Erland, that is not what I'm saying at all.

I am saying that the Nested Sets that Cleko popularises relies on ordering.

> but it builds on a completely artificial number that has no
> relation to the data.

But that is against everything Ceklo states - that a key needs to be
verifiable etc...

How can left and right be verified back to the natural key of the hierarchy
it is modeling - it can't because a) the number given is both random and
relies on ordering and b) that it changes.

Tony.

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CEDC134A...@127.0.0.1...

Tony Rogerson

unread,

Dec 26, 2009, 1:55:18 PM12/26/09

to

Would appear you were replying to a celko post that I can't see on NNTP.

Hopefully google groups will catch up and replicate.

Tony

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CEDC134A...@127.0.0.1...

Tony Rogerson

unread,

Dec 26, 2009, 2:11:09 PM12/26/09

to

Right - google groups haven't replicated your original post, so I'll reply
anyway quoting your original post....

>> But (longitude, latitude) has no linear ordering. Same thing with
>> (x,y) co-ordinates. Remember Cantor's diagonal in your graduate math
>> course on sets?

Err - I never combined the two as a locator - you just did.

I stated "Longtiude is ordered", "as is latitude"

That is fact and it doesn't matter how much you worm your way around
deflecting the fact you are yet again wrong and caught out.

Longtiude works incrementally across the globe.

Latitude works incrementally across the globe.

The combination of the two give a fixed and "stable" location, London's
long/latitude is 'x,y' that will always be the case; storage of x,y will
never change - ever.

Now, in nested sets - is that the case?

NO.

You even admit that your left/right pairing is not stable against the
natural key of the relationship it is suppose to be modeling.

If you have no gaps left, you HAVE to increment the siblings in the node and
sub-nodes (recurse) because your left/right pairing is not verifiable to the
original data.

(remember Peano's postulates?). I can use Cantor's diagonal to order
rational numbers. But I cannot write a list of all the Reals.

>> No, nested sets rely on a 2-D model like the HTM grids. I look at the
>> (lft, rgt) pair and know the size of the subtree at that root node; I
>> know its location relative the whole tree (1, n).

You rely on ordering - that is fact.

If you have no gaps, you have to increment in order to fit the structure in.

What about all those systems that have used the left/right pairing to log
the "facts" in the database? Precisely, because you have changed the
left/right values all those systems are now wrong and worse still actually
relate the originally stored information incorrectly to an entirely
different "fact" within the database.

I won't comment on the rest because its rambing rubbish.

I notice you never commented on the database design aspects of the nested
sets model you popularise.

What about "facts"?? That is thrown right out of the window because the
left/right pair is not stable.

Now, if you are saying that its ok for the left/right pair (locator) to
change because an insert into the hierarchy means a new hierarhcy than I
remind you....

a) Facts in the database should be stable and verifiable - if left/right
pair change then that is not happening therefore you are breaking that
fundemental database design rule.
b) How on earth can you cascade the changes to left/right to other
systems/tables so that the fact you are modeling remains constant and
stable? You can't without triggers, we all know your view on that!
c) Modifying the hierarchy does not scale because inserting or removing a
node renders the entire structure useless and in need of updating - that
updating requires locks; locks prevents scalability because concurrency is
affected.

Need I go on?

In terms of suduko - you skuttled back under your rock as soon as I asked
you which email I stated I'd write an entirely new solution in 5 minutes ( I
have all the emails still! ), but, frankly - there are plenty of examples on
the internet - see sqlblog.com for a good source as to how to properly do a
sudoku without 50 odd inner joins and cursors/while loops - something I
obviously need to remind you that you keep telling people not to use!!!

--ROGGIE--

--CELKO--

unread,

Dec 26, 2009, 9:52:36 PM12/26/09

to

>> So you are saying that having a single IDENTITY column [sic: it is a table level property, not a column] in a table is bad, but if we have two it's OK, because then there is no ordering? <<

Since IDENTITY in SQL Server dialect is a table level counter of
attempted, not successful, PHYSICAL insertions it would be impossible
to have more than one by definition. But I don't want anything that
PHYSICAL at all!

Now, ROW-NUMBER() is a function so you can have multiple calls to it.
It is based on von Neuman's definition of ordinal numbers and falls
into a set-oriented, non-physical model.

>> Nested sets is certainly a clever solution that can be useful in some situations, but it builds on a completely artificial number that has no relation to the data. Not that there is anything wrong with it in my book. <<

Same with HTM and (longitude, latitude), algebraic chess and Go
notation, etc. Some co-ordinate systems make some computations easier
so we like them.

--CELKO--

unread,

Dec 26, 2009, 9:56:44 PM12/26/09

to

>> How can left and right be verified back to the natural key of the hierarchy it is modeling - it can't because a) the number given is both random and relies on ordering and b) that it changes. <<

The (lft, rgt) pair IS the natural key of a particular tree. Like
(longitude, latitude) on the globe, it tells you where you are in that
2D

--CELKO--

unread,

Dec 27, 2009, 12:09:13 AM12/27/09

to

>> I stated "Longitude is ordered", "as is latitude" <<

So what? Longitude without latitude locates nothing. Latitude without
longitude locates nothing. Remember complex numbers? There is no
ordering in the complex plane. Tony, this is basic sophomore college
math.

But without going to complex planes, how do you think about area
(length by length turned into a new unit of measure)?

>> The combination of the two give a fixed and "stable" location, London's long/latitude is 'x,y' that will always be the case; storage of x,y will never change - ever. <<

Do you have a GPS on your cell phone? Do you ever leave your
village? There is a difference between a location in a co-ordinate
system and the entity at that location. London just moves slower than
you do :)

There are (at least) two kinds of system; those that resolve to points
((longitude, latitude) or (x,y,z) or a complex plane) and those that
resolve to n-dimensional regions (HTM, cell phone systems, 3D space
models, etc).

>> You even admit that your left/right pairing is not stable against the natural key of the relationship it is suppose to be modeling. <<

UNH? The space in which I am locating an entity (node) is a tree.
This tree modeled with (lft, rgt) regions. If you have eight nodes,
you can arrange them into 28 different UNDIRECTED trees. Now pick one
of the eight node as the root. These are all different - repeat,
different - spaces.

>> If you have no gaps left, you HAVE to increment the siblings in the node and sub-nodes (recurse) because your left/right pairing is not verifiable to the original data. <<

Tony, the (lft, rgt) are like (longitude, latitude)! When I have an
organizational chart, I fill positions with nodes from Personnel. If
I add a new position to the organizational chart, this is a new space
(tree). It is like change the shape of the Earth. Okay, it is much
easier, but the same concept.

I wish you had a better math background. Get a book from Dover
Publications called "Taxicab Geometry: An Adventure in Non-Euclidean
Geometry" by Eugene F. Krause and expand your mind a bit.

>> What about "facts"?? That is thrown right out of the window because the left/right pair is not stable. <<

Such as a newsgroup posting hierarchy like this :)? I thought I
touched on the fixed node (postings) and dynamic tree problem in
enough detail in TREES & HIERARCHIES. If I need more, tell and I will
add it to the next edition with credit.

>> Now, if you are saying that its OK for the left/right pair (locator) to change because an insert into the hierarchy means a new hierarchy than I remind you....

>> a) Facts in the database should be stable and verifiable - if left/right pair change then that is not happening therefore you are breaking that fundamental database design rule.

b) How on earth can you cascade the changes to left/right to other
systems/tables so that the fact you are modeling remains constant and
stable? You can't without triggers, we all know your view on that!

c) Modifying the hierarchy does not scale because inserting or
removing a node renders the entire structure useless and in need of
updating - that updating requires locks; locks prevents scalability
because concurrency is affected.

>> Need I go on? <<

Not until you get a bit more math and data modeling education :) The
location is not the entity. Wasn't that one of the fundamentals of
data modeling, AKA The law of Identity (A is A)? Wrongly attributed
to Aristotle? Etc?

>> there are plenty of examples on the internet - see sqlblog.com for a good source as to how to properly do a sudoku without 50 odd inner joins and cursors/while loops - something I obviously need to remind you that you keep telling people not to use! <<

Romely's answer was one SIMPLE query -- without 50 odd inner joins and
cursors/while loops -- only a long WHERE clause that implemented the
basic rules.

I will try to get the emails you sent to Richard about his solution in
which you asked him to produce all solutions with 0-9 on the top row
and then berated him for the physically impossible set of all
answers.

I would guess the PROLOG will do it better since it is all constraint
programming. Most of the internet solvers use back tracking.

Tony Rogerson

unread,

Dec 27, 2009, 2:49:11 AM12/27/09

to

Yet again you deflect and try and distract from the point you don't want to
accept, you try and take the conversation into pure math when this is a
database design issue.

I notice you entirely missed out this....

>> Longtiude works incrementally across the globe.
>> Latitude works incrementally across the globe.

Remember we were talking about ordering; you stated Longitude and Latitude
had no ordering (Erland understood what I was talking about)

Now, without a long post I'll relate to your example....

INSERT INTO OrgChart (emp_name, lft, rgt)
VALUES ('Albert', 1, 12),
('Bert', 2, 3),
('Chuck', 4, 11),
('Donna', 5, 6),
('Eddie', 7, 8),
('Fred', 9, 10);

On Day 1; Albert has left/right of 1, 12.
On Day 2; Albert has left/right of something entirely different

That needs to be reproducible in the database as you well know.

Are you advocating that every time you have a change you store the entire
hierarchy in a history table?

Given the sale of an item in an electrical store, we want to know what
department/section and sub-section that electrical item was in to check
profitability etc... - that requires history on your hierarchy; in a
materialised view its easy, with nested sets you have to store the entire
hierarchy in a history table on each change.

Also, here is the FROM clause from the sudoku solver you keep talking
about....

from #t as t11, #t as t12, #t as t13, #t as t14, #t as t15, #t as t16,
#t as t17, #t as t18, #t as t19,
#t as t21, #t as t22, #t as t23, #t as t24, #t as t25, #t as t26,
#t as t27, #t as t28, #t as t29,
#t as t31, #t as t32, #t as t33, #t as t34, #t as t35, #t as t36,
#t as t37, #t as t38, #t as t39,
#t as t41, #t as t42, #t as t43, #t as t44, #t as t45, #t as t46,
#t as t47, #t as t48, #t as t49,
#t as t51, #t as t52, #t as t53, #t as t54, #t as t55, #t as t56,
#t as t57, #t as t58, #t as t59,
#t as t61, #t as t62, #t as t63, #t as t64, #t as t65, #t as t66,
#t as t67, #t as t68, #t as t69,
#t as t71, #t as t72, #t as t73, #t as t74, #t as t75, #t as t76,
#t as t77, #t as t78, #t as t79,
#t as t81, #t as t82, #t as t83, #t as t84, #t as t85, #t as t86,
#t as t87, #t as t88, #t as t89,
#t as t91, #t as t92, #t as t93, #t as t94, #t as t95, #t as t96,
#t as t97, #t as t98, #t as t99

Yes, the join is done in the WHERE clause; remember - I have Richard's code
still so you misrepresenting what I said in private emails publically will
not wash cleko.

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:fe7388bc-f81e-4ec9...@j4g2000yqe.googlegroups.com...

Uri Dimant

unread,

Dec 27, 2009, 4:48:22 AM12/27/09

to

CELKO
> (emp_name CHAR(10)

Are you kidding?

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:dfa156aa-0e95-4f39...@j19g2000yqk.googlegroups.com...

Tony Rogerson

unread,

Dec 27, 2009, 5:34:24 AM12/27/09

to

Of course Uri!!

There is only one person that matters in celko's little world that
is --CELKO-- which is 9 characters and given the exclamation mark he thinks
is there gives 10.

Tony.

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:#Ijq7lth...@TK2MSFTNGP02.phx.gbl...

Erland Sommarskog

unread,

Dec 27, 2009, 5:53:16 AM12/27/09

to

--CELKO-- (jcel...@earthlink.net) writes:
>>> So you are saying that having a single IDENTITY column [sic: it is a
>table level property, not a column] in a table is bad, but if we have two
>it's OK, because then there is no ordering? <<
>
> Since IDENTITY in SQL Server dialect is a table level counter of
> attempted, not successful, PHYSICAL insertions it would be impossible
> to have more than one by definition. But I don't want anything that
> PHYSICAL at all!

It is correct that you can only have one IDENTITY column on a table
in SQL Server, but then again how the number is generated is irrelevant.
Or do you seriously mean that if I run my own counter that is good,
but if I use IDENTITY that is bad?

Erland Sommarskog

unread,

Dec 27, 2009, 5:56:11 AM12/27/09

to

--CELKO-- (jcel...@earthlink.net) writes:
> The (lft, rgt) pair IS the natural key of a particular tree.

In a single word: howwash!

Uri Dimant

unread,

Dec 27, 2009, 6:36:36 AM12/27/09

to

Well said Tony:-)

"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:6576A166-EF3C-46E1...@microsoft.com...

--CELKO--

unread,

Dec 30, 2009, 7:28:59 PM12/30/09

to

>> [The (lft, rgt) pair IS the natural key of a particular tree] In a single word: hog wash! <<

Have you had a course in Graph Theory? Lots of graphs have names --
The graphs K(5) and K(3,3) are important for proofs of planarity.
Even if you don't know the names, you might know the K(3,3) graph as a
famous puzzle involving connecting three houses and three utilities
(http://mathforum.org/dr.math/faq/faq.3utilities.html)

I know some trees DO have names based on their structure -- "completed
n-nary tree of depth (k)" and so forth. Their properties are
important for indexes -- B+ trees for example.

Okay, what is the natural key of a general tree? For example, there
are 28 ways to arrange 8 nodes in an undirected tree. I do not know
of any naming convention for them.

Do you know a tree naming convention? You must or you would not have
said "hog wash" as if my error was obvious.

As far as I know, a general tree structure has to be described because
there is no simple naming convention. The system I use with Nested
Sets is (lft, rgt) co-ordinates.

--CELKO--

unread,

Dec 30, 2009, 7:49:12 PM12/30/09

to

>> It is correct that you can only have one IDENTITY column [sic: columns are not a physical property of a particular table in a particular schema in a particular version of a particular SQL product on a particular piece of hardware] on a table in SQL Server, .. <<

Spell out the rest of the IDENTITY property, so we can discuss it in
relational and data modeling terms.

>> .. but then again how the number is generated is irrelevant. Or do you seriously mean that if I run my own counter that is good, but if I use IDENTITY that is bad? <<

HOW an identifier is generated is VITAL, not irrelevant. It goes to
the key issues of validation and verification of data. I have written
tens of thousands of words on validation and verification, as well as
book or two on the design of encoding schemes.

If I use IDENTITY for vehicles in a motor pool it is not the same as
if I use VIN for vehicles in a motor pool. Duh!

Erland Sommarskog

unread,

Dec 31, 2009, 2:59:54 PM12/31/09

to

And what is the natural key when someone hands out numbers by pressing a
ticker, and if some people change their mind and don't take a number,
he just leaves a gap?

There is nothing wrong with using artificial keys like adding numbers
to an organisational tree that bears no relation to the organisation.
But when you start to call it natural keys to cover your rear parts,
because you've taken an unreasonable position in other contexts, it's
nothing but hogwash.

(By the way, "hogwash" gives 764000 hits on Google, while "hog wash"
only gives 46000, and Googles asks if I by chance meant "hogwash".)

--CELKO--

unread,

Dec 31, 2009, 7:22:16 PM12/31/09

to

>> I notice you entirely missed out this.... Longitude works incrementally across the globe. Latitude works incrementally the globe. <<
I did deal with it, but you don't know the Principle of Composition.
It is a general principle in measurement theory and other places.
Here is a simple version”
1)'Mohammed' is the most common first name on Earth.
2)'Wang' is the most common last name on Earth.
3)Therefore, the most common name on Earth is clearly “Mohammed
Wang”
You can get a laugh out of that pseudo-logic without any math
background or understanding of measurement theory. The composition is
not always anything like the parts. Just like the x-coordinate and
the y-coordinate are not like the (x,y) point in a 2D space.
>> Now, without a long post I'll relate to your [skeleton] example....

INSERT INTO OrgChart (emp_name, lft, rgt)
VALUES ('Albert', 1, 12),
('Bert', 2, 3),
('Chuck', 4, 11),
('Donna', 5, 6),
('Eddie', 7, 8),
('Fred', 9, 10);
On Day 1; Albert has left/right of 1, 12.

On Day 2; Albert has left/right of something entirely different.
Are you advocating that every time you have a change [of
organizational structure or personnel?] you store the entire hierarchy
in a history table? <<

If Albert lost his job, I would put a termination date in his
Personnel file and change the reference in the employee column to his
replacement (or a dummy employee called “Vacant Position”). Again,
the personnel and the organization are different entities. Albert
leaving did not change the organizational structure, any more than
blowing London off the map would erase the (longitude, latitude) on
globe.

Now, if I re-organized the company, then I would put the the entire
hierarchy in a history table with just the organizational structure
and the effective dates. For legal entitles (corporation, co-op, non-
profit, etc) in the US, I have to keep dates of the legal
restructuring!

Tony Rogerson

unread,

Jan 1, 2010, 5:05:03 AM1/1/10

to

> I did deal with it, but you don't know the Principle of Composition.
> It is a general principle in measurement theory and other places.

Actually you didn't, you tried to distract the conversation (and are trying
to do it again) into math when this is a database design discussion, forum
and issue.

So you accept it now relies on ordering - good, we got there eventually,
your attempts at directing the conversation are proof enough for me that you
are burying your head in the sand on this one because you know full well
that the nested sets model of giving left and right values within the
database do not conform to what you keep pimping on this forum - something I
will now remind you of each time you diss anybody for using artificial keys.

The left and right pairing has NO relationship to the data it models, where
is the check digits?

The value 12, 1 - what does that even mean? It cannot be a natural key by
definition - there is nothing to validate to.

12, 1 on day one validates to 'x'

On day two or even later on day one 12, 1 can mean something entirely
different.

Why are you ignoring these facts and still trying to distract this into a
discussion on maths? This is a database design error you are making when
following your own advice you give in this forum and your books.

> If Albert lost his job, I would put a termination date in his
> Personnel file and change the reference in the employee column to his

> replacement (or a dummy employee called ï¿½Vacant Positionï¿½). Again,

> the personnel and the organization are different entities. Albert
> leaving did not change the organizational structure, any more than
> blowing London off the map would erase the (longitude, latitude) on
> globe.
>

In other words you would kludge it and lose the fact Albert had that
position in the hierarchy between two dates.

> Now, if I re-organized the company, then I would put the the entire
> hierarchy in a history table with just the organizational structure
> and the effective dates. For legal entitles (corporation, co-op, non-
> profit, etc) in the US, I have to keep dates of the legal
> restructuring!
>
>

Again, a poor kludge.

Because you have to change the "natural key" aka the coordinate you have no
way of tracking back individual changes - you are forced to store the entire
hierarchy on EACH CHANGE in a history table - that 10,000 row structure
being stored EACH TIME a change in structure is made - that is one hell of a
insert and a big transaction that will badly effect concurrency.

This is one of the biggest scalability problems in the nested sets model,
one of many btw, it does not lend itself to the real world where many things
change and often.

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:d32726a0-8a5e-43f4...@z41g2000yqz.googlegroups.com...

>>> I notice you entirely missed out this.... Longitude works incrementally
>>> across the globe. Latitude works incrementally the globe. <<
> I did deal with it, but you don't know the Principle of Composition.
> It is a general principle in measurement theory and other places.

> Here is a simple versionï¿½

> 1)'Mohammed' is the most common first name on Earth.
> 2)'Wang' is the most common last name on Earth.

> 3)Therefore, the most common name on Earth is clearly ï¿½Mohammed
> Wangï¿½

> You can get a laugh out of that pseudo-logic without any math
> background or understanding of measurement theory. The composition is
> not always anything like the parts. Just like the x-coordinate and
> the y-coordinate are not like the (x,y) point in a 2D space.
>>> Now, without a long post I'll relate to your [skeleton] example....
> INSERT INTO OrgChart (emp_name, lft, rgt)
> VALUES ('Albert', 1, 12),
> ('Bert', 2, 3),
> ('Chuck', 4, 11),
> ('Donna', 5, 6),
> ('Eddie', 7, 8),
> ('Fred', 9, 10);
> On Day 1; Albert has left/right of 1, 12.
> On Day 2; Albert has left/right of something entirely different.
> Are you advocating that every time you have a change [of
> organizational structure or personnel?] you store the entire hierarchy
> in a history table? <<
>
> If Albert lost his job, I would put a termination date in his
> Personnel file and change the reference in the employee column to his

> replacement (or a dummy employee called ï¿½Vacant Positionï¿½). Again,

Tony Rogerson

unread,

Jan 2, 2010, 4:48:22 AM1/2/10

to

> Spell out the rest of the IDENTITY property, so we can discuss it in
> relational and data modeling terms.

Traditionally SQL programmers use MAX ( x ) + 1 to get the next number to
insert eg...

SET @next_number = COALESCE( ( SELECT MAX( TransactionID )
FROM
Transactions
), 0 ) + 1

There are a number of problems with this method, here are a couple....

a) Inserts have to be serialised
b) The coder needs to be fully conversant with the concurrency model of
the database its running on
c) Usually people go for a seperate table to hold the next number, that
presents a RI problem because the next number table and the base table in my
example Transactions may get out of sync
d) If using the next number table and UPDATE the SQL programmer has to be
fully aware of the order in which the engine performs i) the query, ii) the
lock to prevent any other connection getting the same value.

This means people resort to using proprietary table hints for locking and in
doing so often destroy concurrency and again leave yourself with serialised
inserts.

All vendor products have a proprietary method of generating an incremental
number, whilst subject to gaps, once generated it is stable and will not and
cannot be changed; it is stable across new hardware if the database is
backed up and restored somewhere else.

Your comment about the IDENTITY property being a physical property of a
particular table [etc..], a particular piece of hardware - that is all wrong
(I do wish you would read up on how it actually works).

Given a) a database and b) the SQL to insert 50 rows and finally c) single
user.

Those 50 rows will ALWAYS have the same value from the IDENTITY property
regardless of state of the hardware, type of hardware, schema its in,
whatever....

That is fact and reproducible time and time again.

You seem to believe that the value from IDENTITY is generated by querying
rather than on the INSERT itself; let me educate you - on the INSERT
statement and per row the column with the IDENTITY property is calculated
when the actual rows are inserted, that is why the IDENTITY property is not
available in the INSTEAD OF trigger but in the AFTER trigger, we can
determine which values were generated by using the OUTPUT clause from the
INSERT statement which is useful if you need to prevent gaps.

Gaps occur infrequently in the real world; they are systemic of failed
INSERTS, the failure being user defined ie ROLLBACK or system defined ie a
deadlock.

> If I use IDENTITY for vehicles in a motor pool it is not the same as
> if I use VIN for vehicles in a motor pool. Duh!

Ok - lets get to this.

VIN is composed of a number of entities like manufacturer, when manufactured
etc...

In modeling terms these ought to be stored separately so they can be easily
verified using SQL's own RI ie foreign keys rather than having to using
SUBSTRING to split the single column up in order to verify it.

To the industry it would be displayed as a single value, but - that is
display and DISPLAY IS DONE IN THE FRONT END and NEVER in the database as we
both know!

It would make no sense at all using IDENTITY for the incremental part of the
VIN because the plant would be producing different models which would all
have their own incremental seed (starting point).

It would make sense however to use the IDENTITY property for a surrogate key
because that is not shown to the user but can be coded by the developer to
make applications quicker to write, more maintainable, more scalable etc...

All the above is discussed on various posts on my blog if you are so
inclined.

Also - I would suggest you go back and read Date.

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:c7dc7125-030c-400d...@a21g2000yqc.googlegroups.com...

vijendrasingh

unread,

Jan 24, 2010, 1:22:11 AM1/24/10

to

Ques: I want to auto increment a value like R001, which should automaticaly gets converted into R002 when a new row is inserted. I can do it with the help of programing but i want the result through sql query ?

Bob Barrows wrote:

Danny wrote:Do us a favor: Use Enterprise Manager to generate the CREATE
23-Dec-09

Danny wrote:
Do us a favor: Use Enterprise Manager to generate the CREATE TABLE
script for this table (right-click the table and choose All
Tasks>Generate SQL Script) and show it to us. It is not possible to have
a CHAR IDENTITY column in SQL Server.