Click here to Skip to main content
15,901,368 members
Home / Discussions / Database
   

Database

 
QuestionUpload File into SQL Server? Pin
DudleyDoorite13-Oct-03 4:34
DudleyDoorite13-Oct-03 4:34 
GeneralDataRow/DataGrid filter question. Pin
TravisMerkel12-Oct-03 13:06
TravisMerkel12-Oct-03 13:06 
GeneralRe: DataRow/DataGrid filter question. Pin
Armen Dalaliants16-Oct-03 4:02
Armen Dalaliants16-Oct-03 4:02 
GeneralSQL syncing Pin
Judah Gabriel Himango10-Oct-03 6:10
sponsorJudah Gabriel Himango10-Oct-03 6:10 
GeneralRe: SQL syncing Pin
Rob Graham10-Oct-03 12:32
Rob Graham10-Oct-03 12:32 
GeneralSQL syncing Pin
Guillermo Rivero13-Oct-03 3:34
Guillermo Rivero13-Oct-03 3:34 
GeneralIdentity Pin
Anonymous9-Oct-03 8:22
Anonymous9-Oct-03 8:22 
GeneralRe: Identity Pin
Mike Dimmick9-Oct-03 23:07
Mike Dimmick9-Oct-03 23:07 
OK, what happens if I have already inserted the following:
field1 field2 field3 field4
------ ------ ------ ------
 1      A      B      C
 2      A      D      E
 3      B      C      E
and I try to run your query setting @field2 to 'A'? I already have a row where field1 is 3. If there's a primary key (or a UNIQUE constraint) on field1, the INSERT will fail.

Do not use IDENTITY in cases where you need to manipulate values like this. It's just a simple way to get a unique identifier for a row which otherwise cannot be identified uniquely - if it needs some higher meaning, you need to think more closely about your data.

Also, you need to think about locking for this query. What happens if two processes execute this procedure simultaneously? If the situation happens as follows:
Process 1          Process 2
---------          ---------
 
SELECT - @rID = 4
 
                   SELECT - @rID = 4
                    
INSERT - success
 
                   INSERT - failure
one of your inserts will fail. This is even more likely if running on a multiprocessing-capable server (either a server with multiple processors, or potentially on a server with hyperthreading enabled).

If you need to impose an ordering on results, it's better to use ORDER BY in a SELECT. You can always order by multiple columns - for example, ORDER BY field2, field1 to get results in the order of insertion. IIRC, IDENTITY will always add new rows with higher identifiers than are currently present in the table, so long as you haven't reseeded the column with DBCC CHECKIDENT.
QuestionWay to monitor SQL Server programatically? Pin
darrinps8-Oct-03 6:19
darrinps8-Oct-03 6:19 
AnswerRe: Way to monitor SQL Server programatically? Pin
Blake Coverett8-Oct-03 15:40
Blake Coverett8-Oct-03 15:40 
GeneralRe: Way to monitor SQL Server programatically? Pin
darrinps9-Oct-03 2:37
darrinps9-Oct-03 2:37 
GeneralCDaoWorkspace Pin
OTVAC3058-Oct-03 0:26
OTVAC3058-Oct-03 0:26 
GeneralRe: CDaoWorkspace Pin
Hans-Georg Ulrich13-Oct-03 2:14
Hans-Georg Ulrich13-Oct-03 2:14 
GeneralRe: CDaoWorkspace Pin
OTVAC30513-Oct-03 2:22
OTVAC30513-Oct-03 2:22 
GeneralDatabase Connections in Sleeping State in SQL Pin
Anonymous7-Oct-03 1:32
Anonymous7-Oct-03 1:32 
GeneralRe: Database Connections in Sleeping State in SQL Pin
Mike Dimmick7-Oct-03 22:44
Mike Dimmick7-Oct-03 22:44 
GeneralRe: Database Connections in Sleeping State in SQL Pin
Anonymous8-Oct-03 23:55
Anonymous8-Oct-03 23:55 
GeneralGeneral Network Error Pin
yyf6-Oct-03 7:54
yyf6-Oct-03 7:54 
GeneralRe: General Network Error Pin
Mike Ellison6-Oct-03 17:46
Mike Ellison6-Oct-03 17:46 
GeneralRDBMS Pin
buggy846-Oct-03 4:11
buggy846-Oct-03 4:11 
GeneralRe: RDBMS Pin
Mike Dimmick6-Oct-03 4:26
Mike Dimmick6-Oct-03 4:26 
GeneralSQL/C# Pin
Harry20005-Oct-03 12:09
Harry20005-Oct-03 12:09 
GeneralRe: SQL/C# Pin
Mike Dimmick6-Oct-03 4:33
Mike Dimmick6-Oct-03 4:33 
GeneralSQLBindParameter error Pin
vishnusharma5-Oct-03 1:04
vishnusharma5-Oct-03 1:04 
Generalbooks in SQL server 2000 Pin
nevhile.net4-Oct-03 3:02
nevhile.net4-Oct-03 3:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.