automatic script with SQL Server 2000

Hello,
Newbie question:
How can you set a SQL Server 2000 script which will launch automaticly every
day ?
Thanks for your help!One way would be to create a SQL Server JOB, and designate the time to
execute. (That 'JOB' could be running a Stored Procedure -for example.)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Newbie" <newbie@.beginers.com> wrote in message
news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Newbie question:
> How can you set a SQL Server 2000 script which will launch automaticly
> every
> day ?
> Thanks for your help!
>
>|||Thanks Arnie,
The script is alredy written. How can I create the job which'll launch it ?
(NB : I'm a very beginner;-)
Thanks again
"Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
news:udsgS%23GpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> One way would be to create a SQL Server JOB, and designate the time to
> execute. (That 'JOB' could be running a Stored Procedure -for example.)
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Newbie" <newbie@.beginers.com> wrote in message
> news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> > Hello,
> > Newbie question:
> > How can you set a SQL Server 2000 script which will launch automaticly
> > every
> > day ?
> > Thanks for your help!
> >
> >
> >
>|||In Enterprise Mangler, expand down through your server until you get to SQL
Server Agent, and then Jobs.
Right click on the right pane, and select 'New Job'. Most of the rest will
be reasonably explanitory. You can look up SQL Agent Jobs in Books on Line.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Newbie" <newbie@.beginers.com> wrote in message
news:Oyd1ZGHpGHA.4188@.TK2MSFTNGP03.phx.gbl...
> Thanks Arnie,
> The script is alredy written. How can I create the job which'll launch it
> ?
> (NB : I'm a very beginner;-)
> Thanks again
> "Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
> news:udsgS%23GpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>> One way would be to create a SQL Server JOB, and designate the time to
>> execute. (That 'JOB' could be running a Stored Procedure -for example.)
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Newbie" <newbie@.beginers.com> wrote in message
>> news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
>> > Hello,
>> > Newbie question:
>> > How can you set a SQL Server 2000 script which will launch automaticly
>> > every
>> > day ?
>> > Thanks for your help!
>> >
>> >
>> >
>>
>|||Thanks a lot Arnie !
That's great !
Newbie
"Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
news:%23NBhNaHpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> In Enterprise Mangler, expand down through your server until you get to
SQL
> Server Agent, and then Jobs.
> Right click on the right pane, and select 'New Job'. Most of the rest will
> be reasonably explanitory. You can look up SQL Agent Jobs in Books on
Line.
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Newbie" <newbie@.beginers.com> wrote in message
> news:Oyd1ZGHpGHA.4188@.TK2MSFTNGP03.phx.gbl...
> > Thanks Arnie,
> > The script is alredy written. How can I create the job which'll launch
it
> > ?
> > (NB : I'm a very beginner;-)
> > Thanks again
> >
> > "Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
> > news:udsgS%23GpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> >> One way would be to create a SQL Server JOB, and designate the time to
> >> execute. (That 'JOB' could be running a Stored Procedure -for example.)
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "Newbie" <newbie@.beginers.com> wrote in message
> >> news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> >> > Hello,
> >> > Newbie question:
> >> > How can you set a SQL Server 2000 script which will launch
automaticly
> >> > every
> >> > day ?
> >> > Thanks for your help!
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Arnie,
In the Command section of the New job Step, how I said to launch the
ScriptXX ?
"Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
news:%23NBhNaHpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> In Enterprise Mangler, expand down through your server until you get to
SQL
> Server Agent, and then Jobs.
> Right click on the right pane, and select 'New Job'. Most of the rest will
> be reasonably explanitory. You can look up SQL Agent Jobs in Books on
Line.
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Newbie" <newbie@.beginers.com> wrote in message
> news:Oyd1ZGHpGHA.4188@.TK2MSFTNGP03.phx.gbl...
> > Thanks Arnie,
> > The script is alredy written. How can I create the job which'll launch
it
> > ?
> > (NB : I'm a very beginner;-)
> > Thanks again
> >
> > "Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
> > news:udsgS%23GpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> >> One way would be to create a SQL Server JOB, and designate the time to
> >> execute. (That 'JOB' could be running a Stored Procedure -for example.)
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "Newbie" <newbie@.beginers.com> wrote in message
> >> news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> >> > Hello,
> >> > Newbie question:
> >> > How can you set a SQL Server 2000 script which will launch
automaticly
> >> > every
> >> > day ?
> >> > Thanks for your help!
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||You can just copy and paste the script into the window.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Newbie" <newbie@.beginers.com> wrote in message
news:ONmdioHpGHA.1140@.TK2MSFTNGP05.phx.gbl...
> Arnie,
> In the Command section of the New job Step, how I said to launch the
> ScriptXX ?
> "Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
> news:%23NBhNaHpGHA.3564@.TK2MSFTNGP03.phx.gbl...
>> In Enterprise Mangler, expand down through your server until you get to
> SQL
>> Server Agent, and then Jobs.
>> Right click on the right pane, and select 'New Job'. Most of the rest
>> will
>> be reasonably explanitory. You can look up SQL Agent Jobs in Books on
> Line.
>>
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Newbie" <newbie@.beginers.com> wrote in message
>> news:Oyd1ZGHpGHA.4188@.TK2MSFTNGP03.phx.gbl...
>> > Thanks Arnie,
>> > The script is alredy written. How can I create the job which'll launch
> it
>> > ?
>> > (NB : I'm a very beginner;-)
>> > Thanks again
>> >
>> > "Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
>> > news:udsgS%23GpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>> >> One way would be to create a SQL Server JOB, and designate the time to
>> >> execute. (That 'JOB' could be running a Stored Procedure -for
>> >> example.)
>> >>
>> >> --
>> >> Arnie Rowland*
>> >> "To be successful, your heart must accompany your knowledge."
>> >>
>> >>
>> >>
>> >> "Newbie" <newbie@.beginers.com> wrote in message
>> >> news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
>> >> > Hello,
>> >> > Newbie question:
>> >> > How can you set a SQL Server 2000 script which will launch
> automaticly
>> >> > every
>> >> > day ?
>> >> > Thanks for your help!
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Arnie,
Thanks a lot !
"Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
news:Oz9bVGJpGHA.756@.TK2MSFTNGP05.phx.gbl...
> You can just copy and paste the script into the window.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Newbie" <newbie@.beginers.com> wrote in message
> news:ONmdioHpGHA.1140@.TK2MSFTNGP05.phx.gbl...
> > Arnie,
> > In the Command section of the New job Step, how I said to launch the
> > ScriptXX ?
> >
> > "Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
> > news:%23NBhNaHpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> >> In Enterprise Mangler, expand down through your server until you get to
> > SQL
> >> Server Agent, and then Jobs.
> >>
> >> Right click on the right pane, and select 'New Job'. Most of the rest
> >> will
> >> be reasonably explanitory. You can look up SQL Agent Jobs in Books on
> > Line.
> >>
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "Newbie" <newbie@.beginers.com> wrote in message
> >> news:Oyd1ZGHpGHA.4188@.TK2MSFTNGP03.phx.gbl...
> >> > Thanks Arnie,
> >> > The script is alredy written. How can I create the job which'll
launch
> > it
> >> > ?
> >> > (NB : I'm a very beginner;-)
> >> > Thanks again
> >> >
> >> > "Arnie Rowland" <arnie@.1568.com> a écrit dans le message de
> >> > news:udsgS%23GpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> >> >> One way would be to create a SQL Server JOB, and designate the time
to
> >> >> execute. (That 'JOB' could be running a Stored Procedure -for
> >> >> example.)
> >> >>
> >> >> --
> >> >> Arnie Rowland*
> >> >> "To be successful, your heart must accompany your knowledge."
> >> >>
> >> >>
> >> >>
> >> >> "Newbie" <newbie@.beginers.com> wrote in message
> >> >> news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> >> >> > Hello,
> >> >> > Newbie question:
> >> >> > How can you set a SQL Server 2000 script which will launch
> > automaticly
> >> >> > every
> >> >> > day ?
> >> >> > Thanks for your help!
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

automatic script with SQL Server 2000

Hello,
Newbie question:
How can you set a SQL Server 2000 script which will launch automaticly every
day ?
Thanks for your help!One way would be to create a SQL Server JOB, and designate the time to
execute. (That 'JOB' could be running a Stored Procedure -for example.)
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Newbie" <newbie@.beginers.com> wrote in message
news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Newbie question:
> How can you set a SQL Server 2000 script which will launch automaticly
> every
> day ?
> Thanks for your help!
>
>|||Thanks Arnie,
The script is alredy written. How can I create the job which'll launch it ?
(NB : I'm a very beginner;-)
Thanks again
"Arnie Rowland" <arnie@.1568.com> a crit dans le message de
news:udsgS%23GpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> One way would be to create a SQL Server JOB, and designate the time to
> execute. (That 'JOB' could be running a Stored Procedure -for example.)
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Newbie" <newbie@.beginers.com> wrote in message
> news:Of6IaxGpGHA.3600@.TK2MSFTNGP04.phx.gbl...
>|||In Enterprise Mangler, expand down through your server until you get to SQL
Server Agent, and then Jobs.
Right click on the right pane, and select 'New Job'. Most of the rest will
be reasonably explanitory. You can look up SQL Agent Jobs in Books on Line.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Newbie" <newbie@.beginers.com> wrote in message
news:Oyd1ZGHpGHA.4188@.TK2MSFTNGP03.phx.gbl...
> Thanks Arnie,
> The script is alredy written. How can I create the job which'll launch it
> ?
> (NB : I'm a very beginner;-)
> Thanks again
> "Arnie Rowland" <arnie@.1568.com> a crit dans le message de
> news:udsgS%23GpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>|||Thanks a lot Arnie !
That's great !
Newbie
"Arnie Rowland" <arnie@.1568.com> a crit dans le message de
news:%23NBhNaHpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> In Enterprise Mangler, expand down through your server until you get to
SQL
> Server Agent, and then Jobs.
> Right click on the right pane, and select 'New Job'. Most of the rest will
> be reasonably explanitory. You can look up SQL Agent Jobs in Books on
Line.
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Newbie" <newbie@.beginers.com> wrote in message
> news:Oyd1ZGHpGHA.4188@.TK2MSFTNGP03.phx.gbl...
it[vbcol=seagreen]
automaticly[vbcol=seagreen]
>|||Arnie,
In the Command section of the New job Step, how I said to launch the
ScriptXX ?
"Arnie Rowland" <arnie@.1568.com> a crit dans le message de
news:%23NBhNaHpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> In Enterprise Mangler, expand down through your server until you get to
SQL
> Server Agent, and then Jobs.
> Right click on the right pane, and select 'New Job'. Most of the rest will
> be reasonably explanitory. You can look up SQL Agent Jobs in Books on
Line.
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Newbie" <newbie@.beginers.com> wrote in message
> news:Oyd1ZGHpGHA.4188@.TK2MSFTNGP03.phx.gbl...
it[vbcol=seagreen]
automaticly[vbcol=seagreen]
>|||You can just copy and paste the script into the window.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Newbie" <newbie@.beginers.com> wrote in message
news:ONmdioHpGHA.1140@.TK2MSFTNGP05.phx.gbl...
> Arnie,
> In the Command section of the New job Step, how I said to launch the
> ScriptXX ?
> "Arnie Rowland" <arnie@.1568.com> a crit dans le message de
> news:%23NBhNaHpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> SQL
> Line.
> it
> automaticly
>|||Arnie,
Thanks a lot !
"Arnie Rowland" <arnie@.1568.com> a crit dans le message de
news:Oz9bVGJpGHA.756@.TK2MSFTNGP05.phx.gbl...
> You can just copy and paste the script into the window.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Newbie" <newbie@.beginers.com> wrote in message
> news:ONmdioHpGHA.1140@.TK2MSFTNGP05.phx.gbl...
launch[vbcol=seagreen]
to[vbcol=seagreen]
>

automatic identity range handling - newbie question

I am having problems when attempting to publish my database. When I try to
set the automatic identity range handler, the checkbox is dimmed out.
I am using SQL Server 2000 and my subscribers are using Windows CE. I have
set all the indentity fields to INT (2). Everything seems to be in order, but
the checkbox is still dimmed out.
All help will be greatly appreciated
Dale
Dale,
do you already have a subscription to this publication? If it is dimmed out
when looking at the publisher properties, you'll have to drop the
subscriptions, drop the article (apply) then readd the article and it will
then be possible to have automatic range management.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply.
I am very new to this, so could you please explain how I "drop" the
subscriptions, and "drop" the article.
thanks
Dale
"Paul Ibison" wrote:

> Dale,
> do you already have a subscription to this publication? If it is dimmed out
> when looking at the publisher properties, you'll have to drop the
> subscriptions, drop the article (apply) then readd the article and it will
> then be possible to have automatic range management.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Dale,
you can right-click on the publication and select the publisher properties.
On the subscriptions tab, delete each one. Apply. On hte articles tab,
uncheck the table in question. Apply. Next, check this article (table) and
on the article properties you'll be able to enable automatic range
management. After that, readd the subscriptions and run the snapshot agent
and merge agents.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your help Paul!
I found the problem, one of the identity fields was accidently set to
cluster. Once fixed the publication went smoothly.
BTW your website is great!
Dale
"Paul Ibison" wrote:

> Dale,
> you can right-click on the publication and select the publisher properties.
> On the subscriptions tab, delete each one. Apply. On hte articles tab,
> uncheck the table in question. Apply. Next, check this article (table) and
> on the article properties you'll be able to enable automatic range
> management. After that, readd the subscriptions and run the snapshot agent
> and merge agents.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Auto-increment fields when moving a base from Access to SQL server

Hello all,

I'm a total newbie with SQL Server 2000 and I have a little problem when
moving a database form Access 2000 to SQL Server 2000.
In the Access database, each table has an auto-increment field.
After importing the tables in SQL Server, all the auto-increment fields
are turned into "int" type fields.
Does anybody have an explanation for that mystery?

Thanks in advance,
YanYou might want to change the field type from int to identity. The identity
field type in SQL Server is analogous to the Autonumber field type in
Access.

good luck

CJ

"Yan Roosens" <yan.roosens@.skynet.be> wrote in message
news:3F7C4258.6C6B428E@.skynet.be...
> Hello all,
> I'm a total newbie with SQL Server 2000 and I have a little problem when
> moving a database form Access 2000 to SQL Server 2000.
> In the Access database, each table has an auto-increment field.
> After importing the tables in SQL Server, all the auto-increment fields
> are turned into "int" type fields.
> Does anybody have an explanation for that mystery?
> Thanks in advance,
> Yan|||Hi CJ,

> You might want to change the field type from int to identity. The identity
> field type in SQL Server is analogous to the Autonumber field type in
> Access.

Thank you, the error message related to that problem is no longer displayed, I
will now care about the next one :-(

Yan|||On Thu, 02 Oct 2003 17:20:57 +0200, Yan Roosens
<yan.roosens@.skynet.be> wrote:

>Hello all,
>I'm a total newbie with SQL Server 2000 and I have a little problem when
>moving a database form Access 2000 to SQL Server 2000.
>In the Access database, each table has an auto-increment field.
>After importing the tables in SQL Server, all the auto-increment fields
>are turned into "int" type fields.
>Does anybody have an explanation for that mystery?
>Thanks in advance,
>Yan
Because that isn't a data type. Look up identity for an explanation of
how sql server can provide that sort of functionality.

Auto ID column or Natural Primary Key?

Hi,
a newbie question:
i am creating a table on sql server 2005 and I am wondering what the best
practices are re the ID/PK column.
Is that good to create an AutoID column and set it as the PK?
Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE
+ FEE_ID
I am struggling to understand the benefits of using an Auto ID column
because it won't prevent me to insert duplicates, right?
Thanks for ur help,
Chris*If* you decide for a surrogate key, you still have to protect the natural k
ey (UNIQUE constraint).
Very important.
Having said that, there has been endless discussions regarding whether it is
a good idea to use
surrogate keys. For this newsserver, you will manly find them in .programmin
g.
One of the benefits of using surrogate key is that the referencing tables fo
reign keys will be
smaller (one columns typically an int) compared to several columns. This can
work the other way as
well, as if you do carry the natural key to the referencing table, you might
get rid of some joins.
But this can potentially be seen as a type of not-normalized database as you
store facts redundantly
(whether of not you want to call a natural key a fact or not is open for dis
cussion, partly related
to whether the value has business meaning or not).
Another benefit of surrogate keys is that when the natural key changes (whic
h they tend to do, being
natural keys), you don't have to deal with cascading operations.
I suggest you Google and read the newsgroup archives for arguments from both
sides.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best
practices are re the
> ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_COD
E + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column beca
use it won't prevent me
> to insert duplicates, right?
> Thanks for ur help,
> Chris
>|||As Tibor mentions, this is a highly debated subject. And there are many
good, strong arguments on both sides.
It boils down to 'purity' of theory against 'utility' of practice.
Many would argue that a SSN is a 'natural' key for employee records. That
all employees must have one, and that it must be unique. Others would argue
that SSN is nothing more than a surrogate key, having absolutely nothing to
do in directly describing the entity other than being an artifact (surrogate
key) from another database system And so it goes...
I'm with Tibor on the point that using a surrogate key, often an IDENTITY
field, allows greater concentrations of key=value pairs in indexes and tends
to increase performance.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBJPYXfzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> *If* you decide for a surrogate key, you still have to protect the natural
> key (UNIQUE constraint). Very important.
> Having said that, there has been endless discussions regarding whether it
> is a good idea to use surrogate keys. For this newsserver, you will manly
> find them in .programming.
> One of the benefits of using surrogate key is that the referencing tables
> foreign keys will be smaller (one columns typically an int) compared to
> several columns. This can work the other way as well, as if you do carry
> the natural key to the referencing table, you might get rid of some joins.
> But this can potentially be seen as a type of not-normalized database as
> you store facts redundantly (whether of not you want to call a natural key
> a fact or not is open for discussion, partly related to whether the value
> has business meaning or not).
> Another benefit of surrogate keys is that when the natural key changes
> (which they tend to do, being natural keys), you don't have to deal with
> cascading operations.
> I suggest you Google and read the newsgroup archives for arguments from
> both sides.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
> news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
>|||Christophe Leroquais wrote:
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
>
Spot on. But you'd be amazed how many people don't get it!
So if you do use an IDENTITY column (the proper name for an
auto-incrementing surrogate key in SQL Server) make sure you also
declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
constraint is semantically equivalent to UNIQUE NOT NULL so exactly
which key is declared as PRIMARY KEY is pretty unimportant. The crucial
point is that constraints exist for all the relevant keys.
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
--|||> A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant.
Just to emphasize the similarity between PK and UQ constraint: A foreign key
can refer to either a
PK column (combination) *or* s UQ column (combination). One could argue that
it is rather strange
that we have do decide that one of the candidate keys is ... more important
than the other candidate
keys and make it the PK, but that is how the SQL standard is defined.
Unfortunately, most tools expose the PK very clearly, while UQ constraints a
ren't as clearly exposed
in the tools. This can be one for the reasons why we so many times see table
s with identity columns
being the PK and no other key constraints declared. This, of course, doesn't
prevent duplicates of
the business entity at all.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1157142615.776514.268060@.m79g2000cwm.googlegroups.com...
> Christophe Leroquais wrote:
> Spot on. But you'd be amazed how many people don't get it!
> So if you do use an IDENTITY column (the proper name for an
> auto-incrementing surrogate key in SQL Server) make sure you also
> declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant. The crucial
> point is that constraints exist for all the relevant keys.
> --
> 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
> --
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> Just to emphasize the similarity between PK and UQ constraint: A foreign
> key can refer to either a PK column (combination) *or* s UQ column
> (combination). One could argue that it is rather strange that we have do
> decide that one of the candidate keys is ... more important than the other
> candidate keys and make it the PK, but that is how the SQL standard is
> defined.
Your sir are in danger of getting a headache from your own common sense
http://racster.blogspot.com|||> Your sir are in danger of getting a headache from your own common sense
LOL!
Now, whether to take that as a compliment or insult is still for the jury to
decide. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Dassin" <steve@.nospamrac4sql.net> wrote in message
news:OEVy4FszGHA.772@.TK2MSFTNGP05.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> Your sir are in danger of getting a headache from your own common sense
> http://racster.blogspot.com
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0IC0cszGHA.4204@.TK2MSFTNGP04.phx.gbl...
sense[vbcol=seagreen]
> LOL!
> Now, whether to take that as a compliment or insult is still for the jury
to decide. :-)
Trust your instincts.
I think your common sense is telling you that the big picture of
keys/indexes
is filled with non sense
Q: What is the scope of a key?
Q: What is the scope of an index?
Just doing my job
best,
steve|||See the following address for a related discussion...
http://forums.oracle.com/forums/thr...ssageID=1473614
"Christophe Leroquais" wrote:

> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best
> practices are re the ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_COD
E
> + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
> Thanks for ur help,
> Chris
>
>

Auto ID column or Natural Primary Key?

Hi,
a newbie question:
i am creating a table on sql server 2005 and I am wondering what the best
practices are re the ID/PK column.
Is that good to create an AutoID column and set it as the PK?
Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE
+ FEE_ID
I am struggling to understand the benefits of using an Auto ID column
because it won't prevent me to insert duplicates, right?
Thanks for ur help,
Chris*If* you decide for a surrogate key, you still have to protect the natural key (UNIQUE constraint).
Very important.
Having said that, there has been endless discussions regarding whether it is a good idea to use
surrogate keys. For this newsserver, you will manly find them in .programming.
One of the benefits of using surrogate key is that the referencing tables foreign keys will be
smaller (one columns typically an int) compared to several columns. This can work the other way as
well, as if you do carry the natural key to the referencing table, you might get rid of some joins.
But this can potentially be seen as a type of not-normalized database as you store facts redundantly
(whether of not you want to call a natural key a fact or not is open for discussion, partly related
to whether the value has business meaning or not).
Another benefit of surrogate keys is that when the natural key changes (which they tend to do, being
natural keys), you don't have to deal with cascading operations.
I suggest you Google and read the newsgroup archives for arguments from both sides.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best practices are re the
> ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column because it won't prevent me
> to insert duplicates, right?
> Thanks for ur help,
> Chris
>|||As Tibor mentions, this is a highly debated subject. And there are many
good, strong arguments on both sides.
It boils down to 'purity' of theory against 'utility' of practice.
Many would argue that a SSN is a 'natural' key for employee records. That
all employees must have one, and that it must be unique. Others would argue
that SSN is nothing more than a surrogate key, having absolutely nothing to
do in directly describing the entity other than being an artifact (surrogate
key) from another database system And so it goes...
I'm with Tibor on the point that using a surrogate key, often an IDENTITY
field, allows greater concentrations of key=value pairs in indexes and tends
to increase performance.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBJPYXfzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> *If* you decide for a surrogate key, you still have to protect the natural
> key (UNIQUE constraint). Very important.
> Having said that, there has been endless discussions regarding whether it
> is a good idea to use surrogate keys. For this newsserver, you will manly
> find them in .programming.
> One of the benefits of using surrogate key is that the referencing tables
> foreign keys will be smaller (one columns typically an int) compared to
> several columns. This can work the other way as well, as if you do carry
> the natural key to the referencing table, you might get rid of some joins.
> But this can potentially be seen as a type of not-normalized database as
> you store facts redundantly (whether of not you want to call a natural key
> a fact or not is open for discussion, partly related to whether the value
> has business meaning or not).
> Another benefit of surrogate keys is that when the natural key changes
> (which they tend to do, being natural keys), you don't have to deal with
> cascading operations.
> I suggest you Google and read the newsgroup archives for arguments from
> both sides.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
> news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
>> Hi,
>> a newbie question:
>> i am creating a table on sql server 2005 and I am wondering what the best
>> practices are re the ID/PK column.
>> Is that good to create an AutoID column and set it as the PK?
>> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID +
>> CLASSE_CODE + FEE_ID
>> I am struggling to understand the benefits of using an Auto ID column
>> because it won't prevent me to insert duplicates, right?
>> Thanks for ur help,
>> Chris
>|||Christophe Leroquais wrote:
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
>
Spot on. But you'd be amazed how many people don't get it!
So if you do use an IDENTITY column (the proper name for an
auto-incrementing surrogate key in SQL Server) make sure you also
declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
constraint is semantically equivalent to UNIQUE NOT NULL so exactly
which key is declared as PRIMARY KEY is pretty unimportant. The crucial
point is that constraints exist for all the relevant keys.
--
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
--|||> A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant.
Just to emphasize the similarity between PK and UQ constraint: A foreign key can refer to either a
PK column (combination) *or* s UQ column (combination). One could argue that it is rather strange
that we have do decide that one of the candidate keys is ... more important than the other candidate
keys and make it the PK, but that is how the SQL standard is defined.
Unfortunately, most tools expose the PK very clearly, while UQ constraints aren't as clearly exposed
in the tools. This can be one for the reasons why we so many times see tables with identity columns
being the PK and no other key constraints declared. This, of course, doesn't prevent duplicates of
the business entity at all.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1157142615.776514.268060@.m79g2000cwm.googlegroups.com...
> Christophe Leroquais wrote:
>> I am struggling to understand the benefits of using an Auto ID column
>> because it won't prevent me to insert duplicates, right?
> Spot on. But you'd be amazed how many people don't get it!
> So if you do use an IDENTITY column (the proper name for an
> auto-incrementing surrogate key in SQL Server) make sure you also
> declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant. The crucial
> point is that constraints exist for all the relevant keys.
> --
> 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
> --
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> Just to emphasize the similarity between PK and UQ constraint: A foreign
> key can refer to either a PK column (combination) *or* s UQ column
> (combination). One could argue that it is rather strange that we have do
> decide that one of the candidate keys is ... more important than the other
> candidate keys and make it the PK, but that is how the SQL standard is
> defined.
Your sir are in danger of getting a headache from your own common sense:)
http://racster.blogspot.com|||> Your sir are in danger of getting a headache from your own common sense:)
LOL!
Now, whether to take that as a compliment or insult is still for the jury to decide. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Dassin" <steve@.nospamrac4sql.net> wrote in message
news:OEVy4FszGHA.772@.TK2MSFTNGP05.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
>> Just to emphasize the similarity between PK and UQ constraint: A foreign key can refer to either
>> a PK column (combination) *or* s UQ column (combination). One could argue that it is rather
>> strange that we have do decide that one of the candidate keys is ... more important than the
>> other candidate keys and make it the PK, but that is how the SQL standard is defined.
> Your sir are in danger of getting a headache from your own common sense:)
> http://racster.blogspot.com
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0IC0cszGHA.4204@.TK2MSFTNGP04.phx.gbl...
> > Your sir are in danger of getting a headache from your own common
sense:)
> LOL!
> Now, whether to take that as a compliment or insult is still for the jury
to decide. :-)
Trust your instincts.
I think your common sense is telling you that the big picture of
keys/indexes
is filled with non sense:)
Q: What is the scope of a key?
Q: What is the scope of an index?
Just doing my job:)
best,
steve|||See the following address for a related discussion...
http://forums.oracle.com/forums/thread.jspa?messageID=1473614
"Christophe Leroquais" wrote:
> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best
> practices are re the ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE
> + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
> Thanks for ur help,
> Chris
>
>