knowing the 'result' of a, INSERT/UPDATE/DELETE

Hi,
I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.
for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.
Is there any possiblity of doing this?
Thanks a lot,
Pieter
The return parameter id will tell you the autonumber id created here and if
it executed
/* Stored Procedure Insert tblDocuLijn*/
CREATE PROCEDURE spInserttblDocuLijn
@.ID bigint output,
-- FK tblDocument.DOCID
@.doclDOCID int,
@.doclInhoud varchar(2000),
@.doclPrijs float
As Insert INTO tblDocuLijn
(doclDOCID,
doclInhoud,
doclPrijs
)
VALUES
(
@.doclDOCID,
@.doclInhoud,
@.doclPrijs
)
SET @.ID = SCOPE_IDENTITY()
GO
for your update you will need a double stored proc
first select @.output = count(*) from blabla where your condition
then your update
delete the same
hope it helps
eric
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>
|||Check out @.@.ROWCOUNT and @.@.ERROR in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
Hi,
I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.
for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.
Is there any possiblity of doing this?
Thanks a lot,
Pieter
|||> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
The SqlCommand.ExecuteNonQuery method will return the number of rows
affected by an INSERT, UPDATE or DELETE. If execution fails, a
SqlException is thrown and you can catch it as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>
|||Don't forget that both SQLCommand Objects and SqlDataAdapters have a variety
of events that will let you know all sorts of status of queries etc...
-CJ
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>
|||"DraguVaso" <pietercoucke@.hotmail.com> schrieb
> I'm writing a VB.NET application who has to insert/update and delete
> a whole bunch of records from a File into a Sql Server Database. But
> I want to be able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
> there were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
The ExecuteNonQuery method is a function returning the number of affected
records.
Armin
How to quote and why:
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html
|||When your code calls ExecuteNonQuery to INSERT, UPDATE or DELETE - the
number of rows affected is returned. Here is an example that captures the
number of rows affected into a variable.
Dim recordsAffected As Integer = cmd.ExecuteNonQuery()
As far as knowing if "things went well" - exceptions will be raised. To
handle exceptions wrap your SQL INSERT, DELETE, and UPDATE calls in a
Try..Catch block and the SqlServerException class to find out what errors
occurred.
Try
....
Catch ex as System.Data.SqlException
... handle and/or report error
Finally
... clean up
End Try
Mike
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
When you call Update
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>
|||Hi Pieter,
I find it nice to have my name too in this nice group of people.
If you need more answer, feel free to ask.
Now we wait all for Herfried.
:-)))))
Cor
|||Thanks guys!! works great!!
"Mike McIntyre [MVP]" <mikemc@.dotnetshowandtell.com> wrote in message
news:uHVvStGKEHA.892@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> When your code calls ExecuteNonQuery to INSERT, UPDATE or DELETE - the
> number of rows affected is returned. Here is an example that captures the
> number of rows affected into a variable.
> Dim recordsAffected As Integer = cmd.ExecuteNonQuery()
> As far as knowing if "things went well" - exceptions will be raised. To
> handle exceptions wrap your SQL INSERT, DELETE, and UPDATE calls in a
> Try..Catch block and the SqlServerException class to find out what errors
> occurred.
> Try
> ...
> Catch ex as System.Data.SqlException
> ... handle and/or report error
> Finally
> ... clean up
> End Try
>
> --
> Mike
> Mike McIntyre
> Visual Basic MVP
> www.getdotnetcode.com
>
> When you call Update
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> whole
be
> there
>
|||Hehe hi Cor!
It was indeed a nice conference here in this topic with everybody all
together :-)
Pieter
"Cor Ligthert" <notfirstname@.planet.nl> wrote in message
news:%23TvQDHHKEHA.1000@.TK2MSFTNGP11.phx.gbl...
> Hi Pieter,
> I find it nice to have my name too in this nice group of people.
> If you need more answer, feel free to ask.
> Now we wait all for Herfried.
> :-)))))
> Cor
>

knowing the 'result' of a, INSERT/UPDATE/DELETE

Hi,
I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.
for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.
Is there any possiblity of doing this?
Thanks a lot,
PieterThe return parameter id will tell you the autonumber id created here and if
it executed
/* Stored Procedure Insert tblDocuLijn*/
CREATE PROCEDURE spInserttblDocuLijn
@.ID bigint output,
-- FK tblDocument.DOCID
@.doclDOCID int,
@.doclInhoud varchar(2000),
@.doclPrijs float
As Insert INTO tblDocuLijn
(doclDOCID,
doclInhoud,
doclPrijs
)
VALUES
(
@.doclDOCID,
@.doclInhoud,
@.doclPrijs
)
SET @.ID = SCOPE_IDENTITY()
GO
for your update you will need a double stored proc
first select @.output = count(*) from blabla where your condition
then your update
delete the same
hope it helps
eric
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>|||Check out @.@.ROWCOUNT and @.@.ERROR in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
Hi,
I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.
for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.
Is there any possiblity of doing this?
Thanks a lot,
Pieter|||> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
The SqlCommand.ExecuteNonQuery method will return the number of rows
affected by an INSERT, UPDATE or DELETE. If execution fails, a
SqlException is thrown and you can catch it as desired.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>|||Don't forget that both SQLCommand Objects and SqlDataAdapters have a variety
of events that will let you know all sorts of status of queries etc...
-CJ
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>|||"DraguVaso" <pietercoucke@.hotmail.com> schrieb
> I'm writing a VB.NET application who has to insert/update and delete
> a whole bunch of records from a File into a Sql Server Database. But
> I want to be able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
> there were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
The ExecuteNonQuery method is a function returning the number of affected
records.
Armin
How to quote and why:
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html|||When your code calls ExecuteNonQuery to INSERT, UPDATE or DELETE - the
number of rows affected is returned. Here is an example that captures the
number of rows affected into a variable.
Dim recordsAffected As Integer = cmd.ExecuteNonQuery()
As far as knowing if "things went well" - exceptions will be raised. To
handle exceptions wrap your SQL INSERT, DELETE, and UPDATE calls in a
Try..Catch block and the SqlServerException class to find out what errors
occurred.
Try
...
Catch ex as System.Data.SqlException
... handle and/or report error
Finally
... clean up
End Try
Mike
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
When you call Update
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>|||Hi Pieter,
I find it nice to have my name too in this nice group of people.
If you need more answer, feel free to ask.
Now we wait all for Herfried.
:-)))))
Cor|||Thanks guys!! works great!!
"Mike McIntyre [MVP]" <mikemc@.dotnetshowandtell.com> wrote in message
news:uHVvStGKEHA.892@.TK2MSFTNGP09.phx.gbl...
> When your code calls ExecuteNonQuery to INSERT, UPDATE or DELETE - the
> number of rows affected is returned. Here is an example that captures the
> number of rows affected into a variable.
> Dim recordsAffected As Integer = cmd.ExecuteNonQuery()
> As far as knowing if "things went well" - exceptions will be raised. To
> handle exceptions wrap your SQL INSERT, DELETE, and UPDATE calls in a
> Try..Catch block and the SqlServerException class to find out what errors
> occurred.
> Try
> ...
> Catch ex as System.Data.SqlException
> ... handle and/or report error
> Finally
> ... clean up
> End Try
>
> --
> Mike
> Mike McIntyre
> Visual Basic MVP
> www.getdotnetcode.com
>
> When you call Update
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm writing a VB.NET application who has to insert/update and delete a
> whole
> > bunch of records from a File into a Sql Server Database. But I want to
be
> > able to knwo the 'result' of my ctions.
> >
> > for exemple:
> > - after an INSERT: knowing if this happened well or not
> > - after an UPDATE: knowing wich number of records were updated (or if
> there
> > were records udpated or not)
> > - after a DELETE: knwoing the number of deleted recrods.
> >
> > Is there any possiblity of doing this?
> >
> > Thanks a lot,
> >
> > Pieter
> >
> >
>|||Hehe hi Cor!
It was indeed a nice conference here in this topic with everybody all
together :-)
Pieter
"Cor Ligthert" <notfirstname@.planet.nl> wrote in message
news:%23TvQDHHKEHA.1000@.TK2MSFTNGP11.phx.gbl...
> Hi Pieter,
> I find it nice to have my name too in this nice group of people.
> If you need more answer, feel free to ask.
> Now we wait all for Herfried.
> :-)))))
> Cor
>

knowing the 'result' of a, INSERT/UPDATE/DELETE

Hi,
I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.
for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.
Is there any possiblity of doing this?
Thanks a lot,
PieterThe return parameter id will tell you the autonumber id created here and if
it executed
/* Stored Procedure Insert tblDocuLijn*/
CREATE PROCEDURE spInserttblDocuLijn
@.ID bigint output,
-- FK tblDocument.DOCID
@.doclDOCID int,
@.doclInhoud varchar(2000),
@.doclPrijs float
As Insert INTO tblDocuLijn
(doclDOCID,
doclInhoud,
doclPrijs
)
VALUES
(
@.doclDOCID,
@.doclInhoud,
@.doclPrijs
)
SET @.ID = SCOPE_IDENTITY()
GO
for your update you will need a double stored proc
first select @.output = count(*) from blabla where your condition
then your update
delete the same
hope it helps
eric
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>|||Check out @.@.ROWCOUNT and @.@.ERROR in the BOL.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
Hi,
I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.
for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
- after a DELETE: knwoing the number of deleted recrods.
Is there any possiblity of doing this?
Thanks a lot,
Pieter|||> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
The SqlCommand.ExecuteNonQuery method will return the number of rows
affected by an INSERT, UPDATE or DELETE. If execution fails, a
SqlException is thrown and you can catch it as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>|||Don't forget that both SQLCommand Objects and SqlDataAdapters have a variety
of events that will let you know all sorts of status of queries etc...
-CJ
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>|||"DraguVaso" <pietercoucke@.hotmail.com> schrieb
> I'm writing a VB.NET application who has to insert/update and delete
> a whole bunch of records from a File into a Sql Server Database. But
> I want to be able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
> there were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
The ExecuteNonQuery method is a function returning the number of affected
records.
Armin
How to quote and why:
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html|||When your code calls ExecuteNonQuery to INSERT, UPDATE or DELETE - the
number of rows affected is returned. Here is an example that captures the
number of rows affected into a variable.
Dim recordsAffected As Integer = cmd.ExecuteNonQuery()
As far as knowing if "things went well" - exceptions will be raised. To
handle exceptions wrap your SQL INSERT, DELETE, and UPDATE calls in a
Try..Catch block and the SqlServerException class to find out what errors
occurred.
Try
...
Catch ex as System.Data.SqlException
... handle and/or report error
Finally
... clean up
End Try
Mike
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
When you call Update
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm writing a VB.NET application who has to insert/update and delete a
whole
> bunch of records from a File into a Sql Server Database. But I want to be
> able to knwo the 'result' of my ctions.
> for exemple:
> - after an INSERT: knowing if this happened well or not
> - after an UPDATE: knowing wich number of records were updated (or if
there
> were records udpated or not)
> - after a DELETE: knwoing the number of deleted recrods.
> Is there any possiblity of doing this?
> Thanks a lot,
> Pieter
>|||Hi Pieter,
I find it nice to have my name too in this nice group of people.
If you need more answer, feel free to ask.
Now we wait all for Herfried.
:-)))))
Cor|||Thanks guys!! works great!!
"Mike McIntyre [MVP]" <mikemc@.dotnetshowandtell.com> wrote in message
news:uHVvStGKEHA.892@.TK2MSFTNGP09.phx.gbl...
> When your code calls ExecuteNonQuery to INSERT, UPDATE or DELETE - the
> number of rows affected is returned. Here is an example that captures the
> number of rows affected into a variable.
> Dim recordsAffected As Integer = cmd.ExecuteNonQuery()
> As far as knowing if "things went well" - exceptions will be raised. To
> handle exceptions wrap your SQL INSERT, DELETE, and UPDATE calls in a
> Try..Catch block and the SqlServerException class to find out what errors
> occurred.
> Try
> ...
> Catch ex as System.Data.SqlException
> ... handle and/or report error
> Finally
> ... clean up
> End Try
>
> --
> Mike
> Mike McIntyre
> Visual Basic MVP
> www.getdotnetcode.com
>
> When you call Update
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:%23q93JYGKEHA.1132@.TK2MSFTNGP12.phx.gbl...
> whole
be[vbcol=seagreen]
> there
>|||Hehe hi Cor!
It was indeed a nice conference here in this topic with everybody all
together :-)
Pieter
"Cor Ligthert" <notfirstname@.planet.nl> wrote in message
news:%23TvQDHHKEHA.1000@.TK2MSFTNGP11.phx.gbl...
> Hi Pieter,
> I find it nice to have my name too in this nice group of people.
> If you need more answer, feel free to ask.
> Now we wait all for Herfried.
> :-)))))
> Cor
>sql

Killed/Rollback process hogging ALL CPU resources.

I have a test database for the end users to test their select queries for reports.
One of my users is writing queries that cause locking in the database. I killed the process last evening and they are in Killed/Rollback status but are still hogging 90% of the CPU resources for the past 12 hrs. I tried killing them several times but no go.

I know that the best way to clear of these processes is by restarting SQL Server. If that is not an option is there is any other way we can clean these processes?

Also the user running these queries has a read only and create view access to the database. From my experience processes that go into Kill/Rollback state after you kill them are processes associated with some update transaction. Since the user as far as i know is running Select commands would an infinite loop cause this ?

thanks
ninaWhat a good time to talk about execute only authorit to stored procedures...

Your rool back can take up 2 twice as long as the original process...maybe longer...

I doubt it was select only...any chance a work table was involved with millions of rows and they did a delete to clear it out?

Guess you don't have the opportunity to do a code review...

If you stop and restart the server, it'll just pick up from where it left off.

What version is this?

Is this a dev or production box?

I know I saw someone once who discussed this...but it was messy

Before you issue a kill, you should find out what the spid was doing...did you do sp_who to see how much I/O and CPU it was using?

Do you monitor the developers with profiler?

What login Id did the developer login with?|||Hello Brett
thanks for responding. This is a development box and that is probably the only good thing about this entire mess.
And no i killed the process without actually looking into the query that it was running. It is SQL Server 2000 box and the user has a SQL Server account and he uses query analyzer to write/test his queries.
The user has create view rights and belongs to db_datareader role for just the one test database on the server.
Would a query running into an infinite loop cause this problem ?
Before killing the process it was using about 70% of CPU but it kept hogging more resources through the night after i killed it and this morning everything on the server came to a standstill as it hogged 99% of CPU|||Put the user in the pillory, until the rollback is complete. They should learn after that ;-)|||The rollback ran through the night and ate up all our server resources and still did not complete. I just went in and restarted the server. Since this is a development environment it was not that much of a problem.

What i would like to know is that was restarting the SQLServer the only option that we have in such a situation ? And also would a select query every cause a rollback ?|||My guess is that if the restart worked then it wasn't rolling back...

Did you check and see if to spids where deadlocked?|||Yes i did check for deadlocks and there were none in the system.|||OK, Try this next time

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

That will throw everyone out without having to issue a kill

btw did you kill all spids?|||Thanks will keep the Alter statement for future reference. And yes i did try to kill all the processes accociated with that user. And since there were only 4-5 processes for that user i know i got them all.

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database

> before daily restore job in DR machine.

>

> For testing, the script is getting the spid from sysprocesses and

> sysdatabases and store the specific spid which accessing the user DB

> into cursor and sp_who spid.

You're making this way harder than it has to be:

USE master

GO

ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

keyword color problem

Hi guys,
HELP!! I uninstalled yukon and somehow when I'm writing tsql on query
analyzer
on sql 2000 server, everything is in black color. usually if I do single
quote then it would be red color. Do you know what get corrupted and how to
fix it?
thanks
KevinKevin,
You can change the font colour in Tools -> Options -> Fonts.
Are these set correctly?
Barry|||yes... I even reset it,
I don't think that's the problem.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127924531.874569.144420@.g47g2000cwa.googlegroups.com...
> Kevin,
> You can change the font colour in Tools -> Options -> Fonts.
> Are these set correctly?
> Barry
>|||Here is the solution!
Try registering sqllex.dll. Use regsvr32 "%programfiles%\Microsoft SQL
Server\80\Tools\Binn\sqllex.dll".
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23oV7goExFHA.3588@.tk2msftngp13.phx.gbl...
> yes... I even reset it,
> I don't think that's the problem.
> "Barry" <barry.oconnor@.singers.co.im> wrote in message
> news:1127924531.874569.144420@.g47g2000cwa.googlegroups.com...
>|||Really!? How strange - how did you find that out?
Interesting to know though...

Key/Index Questions

I have some simple questions about database design in MSSQL2005. My background
is primarily writing code, but I do understand databases, however I'm (fairly)
new to MSSQL.
My database is to be a single primary table (we`ll call it P) with other tables
(say, about 10-15) being related like this: P <-->> X
Every table has an Identity column which is also the defined Primary Key for
that table.
It's possible that Table P could have up to a million rows, and each Table X
could have up to between 3-10 rows each related to a single row in Table P.
It's a simple database really, but could get large in scope.
My question has to do with Keys and Indexes on these tables, and I guess I'm
having difficulty (in MSSQL terms) understanding what is a "rule" of the
database and what is a "part" of the database. I'm confused when declaring a
KEY (CONSTRAINT) - does that actually creates a "key/index"?
It seems when I declare a Primary Key constraint, then an Index gets created
automatically. But I'm not sure about declaring a Foreign Key on a child table
- does that create an Index or not?
I also want to design it best for performance too<g>. Each and every row will
be INSERTed and/or UPDATEd individually and very few (if any) DELETES will take
place.
Do I absolutely need to specify a FOREIGN KEY on every child table? My
understanding is that if I do have a FOREGN KEY, then any UPDATE on a child row
will cause a referential check to be made on the parent...maybe causing a
performance issue if the tables were large. Will the database perform well, evn
if I do not specify Foreign Keys?
Whether or not, I specify FOREIGN KEYS on tables X, do I need INDEXes on the
columns that relate to the parent table P? I am pretty sure I do, especially if
I execute a SELECT statement like this:
SELECT columns FROM TableX where FK_Column = 'value'
Thanks BrianBrian, see inline
Brian Staff wrote:
> I have some simple questions about database design in MSSQL2005. My background
> is primarily writing code, but I do understand databases, however I'm (fairly)
> new to MSSQL.
> My database is to be a single primary table (we`ll call it P) with other tables
> (say, about 10-15) being related like this: P <-->> X
> Every table has an Identity column which is also the defined Primary Key for
> that table.
Yuck. Different people have different opinions about this. My opinion is
that an Identity column should not be added out of convention. IMO, it
should be added if no other (proper) key is available, which is highly
unlikely to be the case for all your 10-15 tables...
> It's possible that Table P could have up to a million rows, and each Table X
> could have up to between 3-10 rows each related to a single row in Table P.
> It's a simple database really, but could get large in scope.
> My question has to do with Keys and Indexes on these tables, and I guess I'm
> having difficulty (in MSSQL terms) understanding what is a "rule" of the
> database and what is a "part" of the database. I'm confused when declaring a
> KEY (CONSTRAINT) - does that actually creates a "key/index"?
A "rule" is a type of generic check constraint that can be tagged to any
table. Rules are deprecated and in my experience nobody uses them
(because they are a hassle). Best to forget about them.
I have no idea what "part" refers to. I don't think there is an MSSQL
concept called "part".
> It seems when I declare a Primary Key constraint, then an Index gets created
> automatically. But I'm not sure about declaring a Foreign Key on a child table
> - does that create an Index or not?
Only a Primary Key constraint and a Unique constraint will automatically
create a corresponding (unique) index. If you want your Foreign Key
indexed, then you have to add such an index yourself.
> I also want to design it best for performance too<g>. Each and every row will
> be INSERTed and/or UPDATEd individually and very few (if any) DELETES will take
> place.
> Do I absolutely need to specify a FOREIGN KEY on every child table?
I don't know. If you want to ensure data integrity, then you need it. If
you don't care, or think all data modifications will be done through
your application, and you handle the data integrity in your application,
then you can choose to omit the foreign key constraint - at your own
risk.
> My
> understanding is that if I do have a FOREGN KEY, then any UPDATE on a child row
> will cause a referential check to be made on the parent...
Correct.
> maybe causing a performance issue if the tables were large.
Possible, but unlikely. Although you will see more reads (and therefore
potential I/O) and more locking/blocking.
> Will the database perform well, evn if I do not specify Foreign Keys?
Foreign Keys are not primarily for performance. In most cases they don't
help SELECT performance, but they never hinder either. They mostly
affect the performance of data modifications (negatively).
> Whether or not, I specify FOREIGN KEYS on tables X, do I need INDEXes on the
> columns that relate to the parent table P? I am pretty sure I do, especially if
> I execute a SELECT statement like this:
> SELECT columns FROM TableX where FK_Column = 'value'
If this FK_Column is not the first column of the Primary Key, then such
a query would definitely benefit from an index.
Also, such an index helps if you have the Foreign Key in place, and you
delete a row from the referenced table (in your case table P).
HTH,
Gert-Jan
> Thanks Brian
>|||Nice reply Gert-Jan, after reading yours, I deleted mine.
One note: DOUBLE & TRIPLE YUCK on making all PK's IDENTITY.
Besides what Gert-Jan said (which I agree with), if you ever decide to use
Distributed Partition Views across multiple nodes of a cluster, you won't be
able to select all relevant data in a select from a single server - and
performance will suck.
The only time I even consider IDENTITY for a PK on something other than a
parent table is when I'm getting into many part compound PK's - and even
then I look real hard for something better.
Jay
PS. Sorry, pet peeve.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46C9ECDD.A2606551@.toomuchspamalready.nl...
> Brian, see inline
> Brian Staff wrote:
>> I have some simple questions about database design in MSSQL2005. My
>> background
>> is primarily writing code, but I do understand databases, however I'm
>> (fairly)
>> new to MSSQL.
>> My database is to be a single primary table (we`ll call it P) with other
>> tables
>> (say, about 10-15) being related like this: P <-->> X
>> Every table has an Identity column which is also the defined Primary Key
>> for
>> that table.
> Yuck. Different people have different opinions about this. My opinion is
> that an Identity column should not be added out of convention. IMO, it
> should be added if no other (proper) key is available, which is highly
> unlikely to be the case for all your 10-15 tables...
>> It's possible that Table P could have up to a million rows, and each
>> Table X
>> could have up to between 3-10 rows each related to a single row in Table
>> P.
>> It's a simple database really, but could get large in scope.
>> My question has to do with Keys and Indexes on these tables, and I guess
>> I'm
>> having difficulty (in MSSQL terms) understanding what is a "rule" of the
>> database and what is a "part" of the database. I'm confused when
>> declaring a
>> KEY (CONSTRAINT) - does that actually creates a "key/index"?
> A "rule" is a type of generic check constraint that can be tagged to any
> table. Rules are deprecated and in my experience nobody uses them
> (because they are a hassle). Best to forget about them.
> I have no idea what "part" refers to. I don't think there is an MSSQL
> concept called "part".
>> It seems when I declare a Primary Key constraint, then an Index gets
>> created
>> automatically. But I'm not sure about declaring a Foreign Key on a child
>> table
>> - does that create an Index or not?
> Only a Primary Key constraint and a Unique constraint will automatically
> create a corresponding (unique) index. If you want your Foreign Key
> indexed, then you have to add such an index yourself.
>> I also want to design it best for performance too<g>. Each and every row
>> will
>> be INSERTed and/or UPDATEd individually and very few (if any) DELETES
>> will take
>> place.
>> Do I absolutely need to specify a FOREIGN KEY on every child table?
> I don't know. If you want to ensure data integrity, then you need it. If
> you don't care, or think all data modifications will be done through
> your application, and you handle the data integrity in your application,
> then you can choose to omit the foreign key constraint - at your own
> risk.
>> My
>> understanding is that if I do have a FOREGN KEY, then any UPDATE on a
>> child row
>> will cause a referential check to be made on the parent...
> Correct.
>> maybe causing a performance issue if the tables were large.
> Possible, but unlikely. Although you will see more reads (and therefore
> potential I/O) and more locking/blocking.
>> Will the database perform well, evn if I do not specify Foreign Keys?
> Foreign Keys are not primarily for performance. In most cases they don't
> help SELECT performance, but they never hinder either. They mostly
> affect the performance of data modifications (negatively).
>> Whether or not, I specify FOREIGN KEYS on tables X, do I need INDEXes on
>> the
>> columns that relate to the parent table P? I am pretty sure I do,
>> especially if
>> I execute a SELECT statement like this:
>> SELECT columns FROM TableX where FK_Column = 'value'
> If this FK_Column is not the first column of the Primary Key, then such
> a query would definitely benefit from an index.
> Also, such an index helps if you have the Foreign Key in place, and you
> delete a row from the referenced table (in your case table P).
> HTH,
> Gert-Jan
>> Thanks Brian|||> Yuck. Different people have different opinions about this. My opinion is
> that an Identity column should not be added out of convention. IMO, it
> should be added if no other (proper) key is available, which is highly
> unlikely to be the case for all your 10-15 tables...
Hmmm! I see your YUCK was "raised"...twice by Jay
That's taken the wind out of my sails. I thought I was doing well in my design
especially with that part<g>. My theory on Primary Keys is that they should be
preferably be one column and numeric, since searching by alpha and/or multiple
columns would undoubtedly be slower.
Obviously a PK needs to be unique, so I'll re-examine all of the child tables
again, but I have to say that advice does not sit well with my understanding of how
databases work. One question...why is having the identity column be the PK such a
bad idea? - apart from just "yuck!"
BTW - thanks on all of the other advice - that helps a lot.
Brian|||Brian Staff wrote:
> > Yuck. Different people have different opinions about this. My opinion is
> > that an Identity column should not be added out of convention. IMO, it
> > should be added if no other (proper) key is available, which is highly
> > unlikely to be the case for all your 10-15 tables...
> Hmmm! I see your YUCK was "raised"...twice by Jay
> That's taken the wind out of my sails. I thought I was doing well in my design
> especially with that part<g>. My theory on Primary Keys is that they should be
> preferably be one column and numeric, since searching by alpha and/or multiple
> columns would undoubtedly be slower.
> Obviously a PK needs to be unique, so I'll re-examine all of the child tables
> again, but I have to say that advice does not sit well with my understanding of how
> databases work. One question...why is having the identity column be the PK such a
> bad idea? - apart from just "yuck!"
> BTW - thanks on all of the other advice - that helps a lot.
> Brian
There are reasons why many (including me) prefer a natural key over a
surrogate key. IMO, an Identity is a good choice for a surrogate key. If
you google natural vs surrogate key you will probably find a whole lot
of information on that debate.
So I am not saying that having an Identity column as the Primary Key is
not bad per se. However, a table with a foreign key very often has the
meaning of a relation table. If you take the classic example of the
table that describes which book was written by which author. This table
would have a foreign key to the Authors table, and a foreign key to the
Books table. You want the combination of author-book to be unique, so
what simpler choice that to make (author, book) the primary key of this
table? No need to add a surrogate key, very useful index on the primary
key, no need to add another constraint to make the combination
author-book unique, etc.
Now you might argue that in the example above, the primary key of the
BookAuthors table might have been an Identity column and that that key
is narrower and therefore better for performance. But in practice that
is not the case. Because you would join to this table most of the time
(or rather: almost all of the time), and the join requires access to
either/both column author_id and/or book_id. In this example, the
Identity primary key would only perform better if you were to query the
exact Primary Key value.
So if you are going to join a lot of your 10-15 tables to your table P,
then the choice of your primary key and/or the indexes on your foreign
keys is very important.
Gert-Jan|||Gert-Jan,
Thanks for your explanation. I will look carefully at my table data and
re-evaluate the PK choice.
Brian|||> There are reasons why many (including me) prefer a natural key over a
> surrogate key. IMO, an Identity is a good choice for a surrogate key. If
> you google natural vs surrogate key you will probably find a whole lot
> of information on that debate.
> So I am not saying that having an Identity column as the Primary Key is
> not bad per se. However, a table with a foreign key very often has the
> meaning of a relation table. If you take the classic example of the
> table that describes which book was written by which author. This table
> would have a foreign key to the Authors table, and a foreign key to the
> Books table. You want the combination of author-book to be unique, so
> what simpler choice that to make (author, book) the primary key of this
> table? No need to add a surrogate key, very useful index on the primary
> key, no need to add another constraint to make the combination
> author-book unique, etc.
> Now you might argue that in the example above, the primary key of the
> BookAuthors table might have been an Identity column and that that key
> is narrower and therefore better for performance. But in practice that
> is not the case. Because you would join to this table most of the time
> (or rather: almost all of the time), and the join requires access to
> either/both column author_id and/or book_id. In this example, the
> Identity primary key would only perform better if you were to query the
> exact Primary Key value.
> So if you are going to join a lot of your 10-15 tables to your table P,
> then the choice of your primary key and/or the indexes on your foreign
> keys is very important.
> Gert-Jan
My primary objection is that an IDENTITY PK usually does not describe the
data in the column, that function is taken up by indexes and non-identifying
FK's. In Gert-Jan's example a row in BookAuthors is described by the
combination of book_id and author_id, not some arbitrary IDENTITY column.
This BTW, is in large part, the definition of 3NF.
For a good description on database normilaztion:
http://www.datamodel.org/NormalizationRules.html
Jay|||This model that I was working on, as well as another one that I work
on daily, are the reasons that I have come to be wary of the surrogate
key.
create table dbo.items (
item_num varchar(16) not null,
item_desc varchar(32) not null,
constraint pk_items
primary key(item_num),
constraint u_nc_item_desc
unique(item_desc))
create table dbo.customers (
cust_name varchar(32) not null,
constraint pk_customers
primary key(cust_name))
create table dbo.cust_items (
cust_item_num varchar(16) not null,
cust_name varchar(32) not null,
constraint pk_cust_items
primary key(cust_item_num, cust_name),
constraint fk_cust_items_customers
foreign key(cust_name)
references customers(cust_name)
on update cascade)
create table dbo.cust_item_cross_ref (
cust_item_num varchar(16) not null,
cust_name varchar(32) not null,
item_num varchar(16) not null,
constraint pk_cust_item_cross_ref
primary key(cust_name, cust_item_num),
constraint u_nc_item_customer
unique(cust_name, item_num),
constraint fk_cust_item_cross_ref_items
foreign key(item_num)
references dbo.items(item_num)
on update cascade,
constraint fk_cust_item_cross_ref_cust_items
foreign key(cust_item_num, cust_name)
references dbo.cust_items(cust_item_num, cust_name)
on update cascade)
The cust_item_cross_ref table was the source of my difficulty.
The business rules surrounding this table are simply this:
1. Each of our items can be referenced by a customer once.
- covered by u_nc_item_customer
2. Each customer_item can be referenced once.
- covered by pk_cust_item_cross_ref
3. Each item can be referenced to multiple customer_items
If I had used a surrogate key in the cust_items table then I would
have a very interesting time in trying to enforce my business rules at
the cross_ref table. Example below:
create table dbo.cust_items_id (
cust_item_id int identity(1,1),
cust_item_num varchar(16) not null,
cust_name varchar(32) not null,
constraint pk_cust_items
primary key (cust_item_id),
constraint nk_cust_items
primary key(cust_item_num, cust_name),
constraint fk_cust_items_customers
foreign key(cust_name)
references dbo.customers(cust_name)
on update cascade)
create table dbo.cust_item_cross_ref_id (
cust_item_id int not null,
item_num varchar(16) not null,
constraint pk_cust_item_cross_ref
primary key(cust_item_id),
constraint fk_cust_item_cross_ref_items
foreign key(item_num)
references dbo.items(item_num)
on update cascade,
constraint fk_cust_item_cross_ref_cust_items
foreign key(cust_item_id)
references dbo.cust_items(cust_item_id)
on update cascade)
This does not allow me to enforce my second requirement. See the
sample data.
insert into items (item_num, item_desc) values ('Item A', 'Item A')
insert into items (item_num, item_desc) values ('Item B', 'Item B')
insert into customers (cust_name) values ('Cust A')
insert into cust_items (cust_item_num, cust_name) values ('AA', 'Cust
A')
insert into cust_items (cust_item_num, cust_name) values ('AB', 'Cust
A')
insert into cust_items_id (cust_item_num, cust_name) values ('AA',
'Cust A') -- id = 1
insert into cust_items_id (cust_item_num, cust_name) values ('AB',
'Cust A') -- id = 2
In this example I have two items and one customer who has two items.
Both methods prevent me from assigning a customer item to two of my
items. That rule is well enforced. However, in the surrogate key
method, I cannot enforce the other rule. I can freely assign 'Item A'
to cust_id = 1 and to cust_id = 2.
The method that I came up with to solve the problem was either a
trigger (bad...) or to create an indexed view.
create view dbo.bandaid_view with schemabinding
as
select
ci.cust_name,
cr.item_num
from
dbo.cust_item_cross_ref_id as cr
inner join dbo.cust_items_id as ci
on cr.cust_item_id = ci.cust_item_id
go
create unique clustered index u_bandaid on dbo.bandaid_view(cust_name,
item_num)
That solution worked, and would could be used in future. Since I'm
not sold on the performance issue of natural keys vs surrogate keys,
I'll have to stick with the natural keys for now. That may change in
the future, but it will stay this way for now.
Cheers,
Jason Lepack
On Aug 20, 9:34 pm, "JayKon" <s...@.nospam.org> wrote:
> > There are reasons why many (including me) prefer a natural key over a
> > surrogate key. IMO, an Identity is a good choice for a surrogate key. If
> > you google natural vs surrogate key you will probably find a whole lot
> > of information on that debate.
> > So I am not saying that having an Identity column as the Primary Key is
> > not bad per se. However, a table with a foreign key very often has the
> > meaning of a relation table. If you take the classic example of the
> > table that describes which book was written by which author. This table
> > would have a foreign key to the Authors table, and a foreign key to the
> > Books table. You want the combination of author-book to be unique, so
> > what simpler choice that to make (author, book) the primary key of this
> > table? No need to add a surrogate key, very useful index on the primary
> > key, no need to add another constraint to make the combination
> > author-book unique, etc.
> > Now you might argue that in the example above, the primary key of the
> > BookAuthors table might have been an Identity column and that that key
> > is narrower and therefore better for performance. But in practice that
> > is not the case. Because you would join to this table most of the time
> > (or rather: almost all of the time), and the join requires access to
> > either/both column author_id and/or book_id. In this example, the
> > Identity primary key would only perform better if you were to query the
> > exact Primary Key value.
> > So if you are going to join a lot of your 10-15 tables to your table P,
> > then the choice of your primary key and/or the indexes on your foreign
> > keys is very important.
> > Gert-Jan
> My primary objection is that an IDENTITY PK usually does not describe the
> data in the column, that function is taken up by indexes and non-identifying
> FK's. In Gert-Jan's example a row in BookAuthors is described by the
> combination of book_id and author_id, not some arbitrary IDENTITY column.
> This BTW, is in large part, the definition of 3NF.
> For a good description on database normilaztion:
> http://www.datamodel.org/NormalizationRules.html
> Jay- Hide quoted text -
> - Show quoted text -