Geometry Data type in SQL Server 2005

Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the develop
ment of a new workflow, to access data, analyze and map data stored in a dat
abase.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenan
ce of geo-spatial information, each vendor has chosen a fundamentally differ
ent approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the
underlying Oracle 9i kernel. Oracle allows users to interact directly with t
he database using SQL language. In addition, Oracle has published the struct
ure of its geometry, making it an open specification. Any GIS software is ca
pable accessing the data stored in Oracle. In addition, all business rules f
or data and geo-spatial data integrity is built into this model, and any acc
ess, input, edits or other interaction must adhere to the rules that are des
igned in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Eac
h GIS vendor is therefore responsible for developing a method for storing GI
S information in this database. Thus, Intergraph, ESRI, and MapInfo have dev
eloped their own binary geometry structures to allow them to store geo-spati
al information in SQL Server. This approach makes the geo-spatial informatio
n dependent on the GIS software that is used.
Can anyone please help me!docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the devel
opment of a new workflow, to access data, analyze and map data stored in a d
atabase.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and mainten
ance of geo-spatial information, each vendor has chosen a fundamentally diff
erent approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underl
ying Oracle 9i kernel. Oracle allows users to interact directly with the database us
ing SQL language. In addition, Oracle has published the structure of its geometry, m
aki
ng it an open specification. Any GIS software is capable accessing the data
stored in Oracle. In addition, all business rules for data and geo-spatial d
ata integrity is built into this model, and any access, input, edits or othe
r interaction must adhere t
o the rules that are designed in the database no matter the application accessing the databa
se.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS
vendor is therefore responsible for developing a method for storing GIS information
in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binar
y g
eometry structures to allow them to store geo-spatial information in SQL Server. This approa
ch makes the geo-spatial information dependent on the GIS software that is used.een">
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/0...arcsde_sql.html

Geometry Data type in SQL Server 2005

Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, making it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere to the rules that are designed in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary geometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
Can anyone please help me!
docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, maki
ng it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere t
o the rules that are designed in the database no matter the application accessing the database.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary g
eometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/06...rcsde_sql.html
sql

Generating values as part of a compound key

BEGINNER QUESTION

I have a table which has a compound primary key consisting of two columns.

One of these columns is a foreign key which is generated in another table by
an identity.

I want to be able to generate the other primary key column value
automatically when an insert occurs but assume that I cannot use an identity
because it would have to be unique for this table.

There will be potentially more than one user accessing this table so I want
to avoid generating the key on the client side.

How can I do this? Will it require some hardcore T-SQL?

I hope this is clear (I suspect it isn't) I'd be happy to supply more info.
I would be extremely grateful for any help!

Mark.Mark wrote:
> BEGINNER QUESTION
> I have a table which has a compound primary key consisting of two
> columns.
> One of these columns is a foreign key which is generated in another
> table by an identity.

But with "generated" you don't mean it's also inserted into the table with
the compound key at the same time, do you?

> I want to be able to generate the other primary key column value
> automatically when an insert occurs but assume that I cannot use an
> identity because it would have to be unique for this table.

I don't see a problem here because identity *is* unique to your compound
key table.

> There will be potentially more than one user accessing this table so
> I want to avoid generating the key on the client side.

Yes, of course.

> How can I do this? Will it require some hardcore T-SQL?
> I hope this is clear (I suspect it isn't) I'd be happy to supply more
> info. I would be extremely grateful for any help!

Not fully to be honest. Maybe you post some DDL so we can see the table
layout. Also, it's not 100% clear to me when inserts in your main table
occur.

Kind regards

robert|||I hope it may be clearer if I outline what the tables are for:

I'm basically writing an application that stores information about
'behaviour incidents' at a school. The table in question is the 'incidents'
table which is used to record information about individual incidents of
negative behaviour (ok - let's call it being naughty).

The primary key for the 'incidents' table is made up of an 'incidentID' and
'pupilID'. The pupilID indicates the pupil(s) who were involved in the
incident and is itself a foreign key into a 'pupils' table.

This is to reflect the possibility that more than one pupil can be involved
in the same incident. In this case, there may be for example three rows with
the same 'incidentID' - each having a unique pupilID to reflect one incident
in which three different pupils were involved.

My question really revolves around how to generate the 'incidentID' that is
unique at the time of insertion but allows duplicates if more than one pupil
is involved.

Can I insert the first row and retrieve the identity with a scope_identity
and then just insert the rest of the rows with the same incidentID? Wouldn't
that return an error as the identity column wouldn't contain all unique
values.

I hope this is clearer.

Thanks for your time!

Mark.

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:3nj2krF1nuhtU1@.individual.net...
> Mark wrote:
>> BEGINNER QUESTION
>>
>> I have a table which has a compound primary key consisting of two
>> columns.
>>
>> One of these columns is a foreign key which is generated in another
>> table by an identity.
> But with "generated" you don't mean it's also inserted into the table with
> the compound key at the same time, do you?
>> I want to be able to generate the other primary key column value
>> automatically when an insert occurs but assume that I cannot use an
>> identity because it would have to be unique for this table.
> I don't see a problem here because identity *is* unique to your compound
> key table.
>> There will be potentially more than one user accessing this table so
>> I want to avoid generating the key on the client side.
> Yes, of course.
>> How can I do this? Will it require some hardcore T-SQL?
>>
>> I hope this is clear (I suspect it isn't) I'd be happy to supply more
>> info. I would be extremely grateful for any help!
> Not fully to be honest. Maybe you post some DDL so we can see the table
> layout. Also, it's not 100% clear to me when inserts in your main table
> occur.
> Kind regards
> robert|||Mark wrote:
> I hope it may be clearer if I outline what the tables are for:
> I'm basically writing an application that stores information about
> 'behaviour incidents' at a school. The table in question is the
> 'incidents' table which is used to record information about
> individual incidents of negative behaviour (ok - let's call it being
> naughty).
> The primary key for the 'incidents' table is made up of an
> 'incidentID' and 'pupilID'. The pupilID indicates the pupil(s) who
> were involved in the incident and is itself a foreign key into a
> 'pupils' table.
> This is to reflect the possibility that more than one pupil can be
> involved in the same incident. In this case, there may be for example
> three rows with the same 'incidentID' - each having a unique pupilID
> to reflect one incident in which three different pupils were involved.
> My question really revolves around how to generate the 'incidentID'
> that is unique at the time of insertion but allows duplicates if more
> than one pupil is involved.
> Can I insert the first row and retrieve the identity with a
> scope_identity and then just insert the rest of the rows with the
> same incidentID? Wouldn't that return an error as the identity column
> wouldn't contain all unique values.

You are right, this table layout would not work with identity. However, I
figure your table layout may not be optimal because you really have a n-m
relationship between incidents and pupils. And as far as I can see
there's no place to store information where there is just one piece per
incident (for example date and time). With all that I know ATM I would
have it as follows:

table incidents with date, time, location whatever and incidentid
(identity)
table pupils with pupilid (identity), name, day of birth - whatever
table pupils_in_incidend with incidentid, pupilid (both foreign keys)

This seems the most normalized approach here.

Kind regards

robert|||> You are right, this table layout would not work with identity. However, I
> figure your table layout may not be optimal because you really have a n-m
> relationship between incidents and pupils. And as far as I can see
> there's no place to store information where there is just one piece per
> incident (for example date and time). With all that I know ATM I would
> have it as follows:
> table incidents with date, time, location whatever and incidentid
> (identity)
> table pupils with pupilid (identity), name, day of birth - whatever
> table pupils_in_incidend with incidentid, pupilid (both foreign keys)
> This seems the most normalized approach here.
> Kind regards
> robert

OF COURSE! I should have seen that it would be silly to duplicate all of the
incident information for every pupil involved in a given incident.

Thank you immensely for your help!

Mark.

Generating Surrogate Keys

I've been playing with a couple of ways of doing this, based on the ideas in
the article "The Cost of GUID's as Primary Keys" :
http://www.informit.com/articles/ar...2&seqNum=1&rl=1 and a
couple of other online sources
and I've put together the following:
create view builtins as
select getdate() the_date, newid() the_id
go
create function comb_id() returns uniqueidentifier
as
begin
declare @.out uniqueidentifier
select @.out = CAST(CAST(builtins.the_id AS BINARY(10))
+ CAST(builtins.the_date AS BINARY(6)) AS UNIQUEIDENTIFIER)
from builtins
return @.out
end
GO
create table x(a int, b uniqueidentifier DEFAULT ([dbo].[comb_id]()))
GO
Now, inserts to x result in a new "comb guid" being generated, which I can
trap in an insert trigger from the inserted table. I can also, if I wish,
generate ready-made IDs in the application, by reimplementing the same
algorithm.
Are there any "gotchas" of which I should be aware when using this approach?
Enviroment is SQL 2000, no immediate plans to upgrade.
Any observations welcome,
GCWhy not just use an auto-incrementing identity column?
"GC" <reply_to_group@.please> wrote in message
news:O8RKZr9XFHA.1044@.TK2MSFTNGP10.phx.gbl...
> I've been playing with a couple of ways of doing this, based on the ideas
in
> the article "The Cost of GUID's as Primary Keys" :
> http://www.informit.com/articles/ar...2&seqNum=1&rl=1 and a
> couple of other online sources
> and I've put together the following:
> create view builtins as
> select getdate() the_date, newid() the_id
> go
> create function comb_id() returns uniqueidentifier
> as
> begin
> declare @.out uniqueidentifier
> select @.out = CAST(CAST(builtins.the_id AS BINARY(10))
> + CAST(builtins.the_date AS BINARY(6)) AS UNIQUEIDENTIFIER)
> from builtins
> return @.out
> end
> GO
> create table x(a int, b uniqueidentifier DEFAULT ([dbo].[comb_id]()))
> GO
> Now, inserts to x result in a new "comb guid" being generated, which I can
> trap in an insert trigger from the inserted table. I can also, if I wish,
> generate ready-made IDs in the application, by reimplementing the same
> algorithm.
> Are there any "gotchas" of which I should be aware when using this
approach?
> Enviroment is SQL 2000, no immediate plans to upgrade.
> Any observations welcome,
> GC
>|||Why not create use a generic function to which you pass the current DateTime
and
a new Id. Like so:
Create Function dbo.CombID(@.SeedId UniqueIdentifier, @.CurDate DateTime)
As
Begin
Return Cast(Cast(@.SeedId As Binary(10))
+ Cast(@.CurDate As Binary(6)) As UniqueIdentifier)
End
Thus, you would do something like so in a table:
Create Table TableName
(
AColumn UniqueIdentifier Default (dbo.CombId(NewId(), Current_Timestamp))
)
Beyond the general gotchas of using a Guid, the only gotcha to this approach
would be that you are sacrificing a little "uniqueness" for better indexing
performance. That means that the odds are higher of a collision. Realistical
ly,
the odds are incredibly low even using the full eight bytes of the current d
ate
time.
Thomas
"GC" <reply_to_group@.please> wrote in message
news:O8RKZr9XFHA.1044@.TK2MSFTNGP10.phx.gbl...
> I've been playing with a couple of ways of doing this, based on the ideas
in
> the article "The Cost of GUID's as Primary Keys" :
> http://www.informit.com/articles/ar...2&seqNum=1&rl=1 and a
> couple of other online sources
> and I've put together the following:
> create view builtins as
> select getdate() the_date, newid() the_id
> go
> create function comb_id() returns uniqueidentifier
> as
> begin
> declare @.out uniqueidentifier
> select @.out = CAST(CAST(builtins.the_id AS BINARY(10))
> + CAST(builtins.the_date AS BINARY(6)) AS UNIQUEIDENTIFIER)
> from builtins
> return @.out
> end
> GO
> create table x(a int, b uniqueidentifier DEFAULT ([dbo].[comb_id]()))
> GO
> Now, inserts to x result in a new "comb guid" being generated, which I can
> trap in an insert trigger from the inserted table. I can also, if I wish,
> generate ready-made IDs in the application, by reimplementing the same
> algorithm.
> Are there any "gotchas" of which I should be aware when using this approac
h?
> Enviroment is SQL 2000, no immediate plans to upgrade.
> Any observations welcome,
> GC
>|||A couple of reasons, really. I figured a GUID-type is more "meaningless",
and less likely to be the subject of hard-coded assumptions in the future,
and I also quite liked the idea of being able to generate the GUID in the
application if I wanted to.
"JT" <someone@.microsoft.com> wrote in message
news:%23Ubds49XFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Why not just use an auto-incrementing identity column?
> "GC" <reply_to_group@.please> wrote in message
> news:O8RKZr9XFHA.1044@.TK2MSFTNGP10.phx.gbl...
> in
> approach?
>|||Thomas Coleman wrote:
> Why not create use a generic function to which you pass the current
> DateTime and a new Id. Like so:
Just because, for no particularly good reason, I felt the function should be
niladic.

> Beyond the general gotchas of using a Guid, the only gotcha to this
> approach would be that you are sacrificing a little "uniqueness" for
> better indexing performance. That means that the odds are higher of a
> collision. Realistically, the odds are incredibly low even using the
> full eight bytes of the current date time.
I guess I could test for this in @.@.ERROR and try again if there's been a
collision.
Thanks for your responses,
GC|||>> Why not create use a generic function to which you pass the current
> Just because, for no particularly good reason, I felt the function should
be
> niladic.
Understand. But wouldn't every call need to populate your little temp table
with
a guid and a datetime? Otherwise, you'll get duplicate results.

> I guess I could test for this in @.@.ERROR and try again if there's been a
> collision.
If the column is the primary key, of course this will not be necessary. If t
he
column in question is not the primary key, then you can simply create a uniq
ue
index on that column to ensure uniqueness.
Thomas|||Thomas Coleman wrote:
> Understand. But wouldn't every call need to populate your little temp
> table with a guid and a datetime? Otherwise, you'll get duplicate
> results.
It was a view, so newid() and getdate() would be called each time it was
selected from.

>
> If the column is the primary key, of course this will not be
> necessary. If the column in question is not the primary key, then you
> can simply create a unique index on that column to ensure uniqueness.
>
I was thinking about what to do in the (unlikely) event of the algorithm
giving rise to a collision.
If @.@.ERROR is 2627 (PK violation), then try again, on the basis that you'd
have to be seriously
unlucky to get two collisions in a row.

>
> Thomas|||An Integer is only 4 bytes long. GUIDs are 36 bytes long, and even a self
generated GUID would be at least twice as long as an integer. Not very
efficient for storage or performance reasons. Why is it important to
generate the IDs by the application, is this some sort of offline
application that periodically merges data to the database?
"GC" <reply_to_group@.please> wrote in message
news:ujmllH%23XFHA.2684@.TK2MSFTNGP09.phx.gbl...
> A couple of reasons, really. I figured a GUID-type is more "meaningless",
> and less likely to be the subject of hard-coded assumptions in the future,
> and I also quite liked the idea of being able to generate the GUID in the
> application if I wanted to.
> "JT" <someone@.microsoft.com> wrote in message
> news:%23Ubds49XFHA.3712@.TK2MSFTNGP09.phx.gbl...
ideas
a
wish,
>|||First of all this is not a surrogate key accordignt o Dr. Codd's
definition. It is an artifacial key. And it happens to be long,
unverifiable and a XXXXX to validate.
Here is an implementation of the additive congruential method of
generating values in pseudo-random order and is due to Roy Hann of
Rational Commerce Limited, a CA-Ingres consulting firm. It is based on
a shift-register and an XOR-gate, and it has its origins in
cryptography. While there are other ways to do this, this code is nice
because:
1) The algorithm can be written in C or another low level language for
speed. But math is fairly simple even in base ten.
2) The algorithm tends to generate successive values that are (usually)
"far apart", which is handy for improving the performance of tree
indexes. You will tend to put data on separate physical data pages in
storage.
3) The algorithm does not cycle until it has generated every possible
value, so we don't have to worry about duplicates. Just count how many
calls have been made to the generator.
4) The algorithm produces uniformly distributed values, which is a nice
mathematical property to have. It also does not include zero.
Generalizing the algorithm to arbitrary binary word sizes, and
therefore longer number sequences, is not as easy as you might think.
Finding the "tap" positions where bits are extracted for feedback
varies according to the word-size in an extremely non-obvious way.
Choosing incorrect tap positions results in an incomplete and usually
very short cycle, which is unusable. If you want the details and tap
positions for words of one to 100 bits, see E. J. Watson, "Primitive
Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962,
p.368-369.
We need to tap bits 0 and 3 to construct the 31-bit random-order
generated value Generator (which is the one most people would want to
use in practice):
UPDATE Generator31
SET keyval =
keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2)*2^30;
Or if you prefer, the algorithm in C:
int Generator31 ()
{static int n = 1;
n = n >> 1 | ((n^n >> 3) & 1) << 30;
return n;
}
Do not forget to add a check digit.|||Hello, JT
A GUID is stored on 16 bytes, not 36 bytes.
Sometimes it's better to generate the IDs by the application to avoid
the round trips (necessary to execute "SELECT SCOPE_IDENTITY()").
Sometimes a "less than perfect" environment (like Microsoft Access
ADP-s) is used to develop the application, that has "SELECT @.@.IDENTITY"
hardcoded (instead of "SELECT SCOPE_IDENTITY()"). Sometimes GUID-s are
needed for replication. Sometimes...
Regarding the performance reasons, did you read the article quoted by
GC in the first post? The author suggests that there is only a 10%
performance impact due to the 4 times increase of size (the bigger
impact in that test scenario is due to the randomness of GUIDs, and
this impact can be minimized with the converting of current date trick,
according to the article).
Razvan

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
ThxA common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
Thx
A common method in the SLQ Server world is to define the PK column with the IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx
|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
ThxA common method in the SLQ Server world is to define the PK column with the IDENTITY attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thxsql

Generating a primary key

I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.

What are my other options? How can I generate a primary key for every new row?

Any advice is appreciated.

Regards

Sara

I personally prefer generating my key values inside the pipeline. For your scheme you would need to write some bespoke code to generate the incrementing value. Use a Script Component to host this code, and output a new column with the value.

Here is an example of this pattern in use - http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/, just tweak teh code to give you your values.

|||

Wow, thank you very much, I followed your example (ignoring the letters, trying with incremental numbers only), it works and it works well!

Just have to play around to make it work with letters too.

Thanx again!

|||

Sara,

Primary keys should never contain logic within them. Why do you need the letters? Using integers, you can achieve the correct order of SCD changes for a given business key, so I don't see the need for letters at all.

|||What do you exactly mean by 'the correct order of SCD changes for a given business key'?|||

Sara4 wrote:

What do you exactly mean by 'the correct order of SCD changes for a given business key'?

I was just trying to make an assumption as to why you'd want letters in your key. With integers, you could select a business key and order by the primary key to see the order of the changes as they occurred. Granted, you could do that with your approach as well, but you're actually going to use more storage using your method (based on your example above) versus using a 4-byte integer field.

Generate custom primary key fields

Hi all,

I've recently dabbled into the world of Stored procedures and Triggers but am having some issues trying to implement some functionality.

What I want to accomplish is to either create a SP or Trigger (I'm still trying to figure out the prime differece between them) that takes values in, concatenates them, adds an increment and then saves it into the assigned column.

Say for example I have a company name, and a region
Name:'Traders Inc"
Region:KEN (Kenya).

What I want my sp to do is take the first four letters of the name, add a dash, concat the region and add an increment starting from 001 thereby giving me TRAD-KEN-001. Incase I put in another entry, it should do the same and start it at 001 as well. Only when the first two variables are the same should the number be incremented. I.e if we have another entry
Name: Tradine Jewelers",
Region: Kenya;

It should calculate and give me a value TRAD-KEN-002. This is what I want my sp to do.

I can experiment and get the concats etc to work but my confusion kicks in on how I integrate 'If' statements and how SQL actually manipulates vinputted values and where (and how) I get the value of the last increment.

Values will be passed in from my Business Logic Layer but I am totally lost after that i.e. Should I use a SP or Trigger, how do I get the values into one column, how do I integrate the IF statemnets to get the result that I am getting. The only part I understand is the Insert but thats about it.

Any help on flow, functions and commands would be wonderful with where the GO and EXECs should go in and how it will store variables temporarily.

Thanks in Advance.

I'd just like someone to set me off in the right direction.

Thanks.

|||

Here is one idea, perhaps still 'half baked', so take it as a seed, not a fully grown solution.

Column for Name
Column for Region
Column for Sequence

Column for Key Value (Concatenated fields); this column will have a UNIQUE INDEX -which will serve 'almost' as good as a Primary Key -but will allow the initial INSERT to succeed even with NULL values.

Use a Trigger (the difference between a Trigger and a Stored Procedure is that a Stored Procedure executes when called, whereas a Trigger automatically executes upon change to data in a table.)

Create an FOR INSERT Trigger. When a new row is inserted, search for the max(Sequence) WHERE MyTable.Name = inserted.Name AND MyTable.Region = Inserted.Region. Add one to that value, put it in the Sequence Column, and then concatenate the three columns together to make your key value. Store in the Key value column. (It may be necessary to use an INSTEAD OF Trigger rather than a FOR INSERT Trigger.)

I hope that this helps point you in a direction that will work for you.

|||Hi,

Assuming the table structure is like
create table table1 (AreaName varchar(100), Region varchar(100), CodeGenerated varchar(100))

You can use something like this or you can convert it into trigger.

CREATE PROCEDURE InsertData
@.AreaName VARCHAR(100),
@.Region VARCHAR(100)
AS
BEGIN
DECLARE @.CodeGenerated VARCHAR(100), @.Counter varchar(10)

SET @.CodeGenerated = substring(@.Areaname, 1, 4) + '-' + substring(@.Region,1,3) + '-'

SELECT @.Counter = ISNULL(MAX(REPLACE(CodeGenerated,@.CodeGenerated, '')),0)+1 FROM Table1
WHERE AreaName = @.AreaName AND Region = @.region

SET @.CodeGenerated = UPPER(@.CodeGenerated) + REPLICATE('0',3-len(@.Counter)) + convert(VARCHAR,@.Counter)

INSERT table1 VALUES(@.AreaName, @.Region, @.CodeGenerated)
END

-- Code to execute stored procedure
EXEC InsertData @.AreaName = 'Traders Inc', @.Region = 'KEN'

SELECT * FROM table1

--Neeraj--|||

Awesome. Thanks alot for your responses.

Neeraj, by looking at the code and my newbie databases status, you just saved me 1 or 2 days of trial and error with this one.Code works perfect. Thanks a million!

I need to get my hands on some good sql books. Any book recommendations or links which I can start off with?

|||Hi kundalani,

Good to know that your problem is solved.

As per forums guidelines you should close the topic if it is able to solve your problem :)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17861&SiteID=1

Generate AutoNumber

Hi, I have a question, I have created a table and with a primary key called "ID". However, I want the "ID" be auto increment as well. when inserting new record into the database.I'm using vb.net. how can I do in the following format: "1", "2", "3", ......... etc. I've the code below but it's not working in the right way, what's wrong with my code? PrivateSub BtnAdd_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnAdd.Click Dim ssqlAsString Dim ItemidAsInteger Dim updcmdAs SqlClient.SqlCommand Itemid = 0 mysqladap =New SqlClient.SqlDataAdapter("select MAX(Item_id) From auction where item_type= '" & (Image1.ImageUrl) & "'", mySqlConn) Itemid = (Itemid) + 1 ssql = "insert into auction (item_id,owner_name,owner_mail,owner_mobile,owner_phone,owner_ext,item_type,item_name,item_image,item_desc,item_cost,start_date) values ('" & (Itemid) & "','" & Trim(ownertxt.Text) & "' ,'" & Trim(emailtxt.Text) & "', '" & Trim(mobiletxt.Text) & "', '" & Trim(phonetxt.Text) & "','" & Trim(exttxt.Text) & "','" & Trim(DropDownList1.SelectedValue) & "','" & Trim(itemtxt.Text) & "','" & Trim(Image1.ImageUrl) & "','" & Trim(desctxt.Text) & "','" & Trim(costtxt.Text) & "','" & Trim(Today.Date) & "')" updcmd =New SqlClient.SqlCommand(ssql, mySqlConn) updcmd.ExecuteNonQuery() lblmsg.Visible =True EndSub Anyone can help me? Thanks.

As I replied elsewhere where you asked the exact same question, use an IDENTITY column. Then you will not need to insert the value for the column, it will populate itself.|||

First of all thanks for your reply,
Can you please explain more?
Can you write the statement for me or the modification on my given code?
Thank you

|||You have to set the Identity column in the datatable table properties. You dont do that via code. In Sql Enterprise manager, when you open the table in design time, you can set the Identity property of that column to "yes" at the bottom of the screen.|||Thank you sooooooo much, and after that, delete the code I've written? and don't inset that column in the insert statement of sql?|||What's meant by identity seed? it's set to 1, shall I change it?|||

I've inserted 6 records and they are given the numbers 1,2,3,4,5, and 6, then I deleted them all and add new record, it has given the number 7, WHY?
why it isn't set to 1 again?

|||the numbers will not reset unless you recreate the table, or truncate table.