InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:08:14 GMT20http://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspx[quote][b]CELKO (1/5/2012)[/b][hr]This is only one reason why good SQL programmers never use this proprietary non-relational "feature" in their code. Back to foundation. SQL is a set-oriented language. When you do an INSERT you put in a set, all at once. That set can be zero or more rows. But IDENTITY is a sequential count of insertion attempts. PHYSICAL insertion attempts. Not a LOGICAL concept at all. So how do you number these rows? Please read a book on RDBMS; your mindset is still in magnetic tape files and not RDBMS yet.[/quote]This confuses logical design with physical implementation. Certainly, meaningless numeric 'tuple identifiers' are of little use in a purely relational logical design, and many would consider their use to violate some normal form or another. That's irrelevant when we come to implement the logical design though: physical identifiers are frequently an important physical optimization. Also, SQL Server is not a pure relational RDBMS, and nor is SQL a set-orientated language: it has bag semantics, not set semantics.Fri, 13 Jan 2012 21:31:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspx[quote][b]CELKO (1/5/2012)[/b][hr]This is only one reason why good SQL programmers never use this proprietary non-relational "feature" in their code. Back to foundation. SQL is a set-oriented language. When you do an INSERT you put in a set, all at once. That set can be zero or more rows. But IDENTITY is a sequential count of insertion attempts. PHYSICAL insertion attempts. Not a LOGICAL concept at all. So how do you number these rows? Please read a book on RDBMS; your mindset is still in magnetic tape files and not RDBMS yet.[/quote]Actually, good programmers use this feature all the time and for good reason. Please see the following video...[url]http://technet.microsoft.com/en-us/sqlserver/Video/gg508879[/url]Fri, 13 Jan 2012 21:22:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspx[quote][b]nigel. (1/13/2012)[/b][hr]And it looks he's succeeded! No sign of the OP :crying:[/quote]The simple question was answered long before Joe posted. It seems the questioner has his or her answer, but just hasn't returned to say so.Fri, 13 Jan 2012 21:17:54 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspx[quote][b]GilaMonster (1/12/2012)[/b][hr]... Personally I think it just drives them away ...[/quote]And it looks he's succeeded! No sign of the OP :crying:Fri, 13 Jan 2012 08:41:09 GMTnigel.http://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspxHa, ha, ha! "...warm and cuddly...". I like that.I was going to say "rude", but I choose the word "abrasive" instead.I am all for learning, and for helping others learn for themselves, as opposed to just providing answers, which is what a lot of the posters here want, but there's no need for insults. Like you, I feel that it would do more harm than good.But, hey...to each his own, right?!Thu, 12 Jan 2012 07:22:18 GMTCGSJohnsonhttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspx[quote][b]CGSJohnson (1/12/2012)[/b][hr]Man, kind of abrasive, no, Mr. Celko?! I thought that this is supposed to be a supportive forum. I can understand your frustrations, but we can direct people to do research without being so abrasive.[/quote]Hell, that's positively warm and cuddly compared to some of Joe's comments. Joe holds the opinion that if he insults newcomers it'll motivate them to study and learn to do things properly. Personally I think it just drives them away from possible improvement and results in them not learning and not seeking out learning.Joe's attitude is quite common on Oracle boards and forums, very rare on SQL Server ones.Thu, 12 Jan 2012 06:42:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspxMan, kind of abrasive, no, Mr. Celko?! I thought that this is supposed to be a supportive forum. I can understand your frustrations, but we can direct people to do research without being so abrasive.- ChrisThu, 12 Jan 2012 06:32:13 GMTCGSJohnsonhttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspx[quote][b]drew.allen (1/5/2012)[/b][hr]IDENTITY is a number that is unique for a given table. PERIOD.[/quote]Not without something to enforce that uniqueness it isn't. :-P[code="sql"]CREATE TABLE #T (id int IDENTITY(1,1) NOT NULL)INSERT #T DEFAULT VALUESSELECT * FROM #TDBCC CHECKIDENT(#T, RESEED, 0)INSERT #T DEFAULT VALUESSELECT * FROM #TSET IDENTITY_INSERT #T ONINSERT #T (id) VALUES (1)SET IDENTITY_INSERT #T OFFSELECT * FROM #TDROP TABLE #T[/code]Wed, 11 Jan 2012 14:15:02 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspx[quote][b]CELKO (1/5/2012)[/b][hr]But IDENTITY is a sequential count of insertion attempts. PHYSICAL insertion attempts. Not a LOGICAL concept at all. So how do you number these rows?[/quote]You're confusing what IDENTITY is with how it's generated. IDENTITY is a number that is unique for a given table. PERIOD. How it is generated is irrelevant. I don't know anyone that writes code that depends on how an identity column is generated.DrewThu, 05 Jan 2012 12:35:46 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspx[quote][b]CELKO (1/5/2012)[/b][hr]This is only one reason why good SQL programmers never use this proprietary non-relational "feature" in their code. [/quote]Blah, blah, blah.Thu, 05 Jan 2012 12:23:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspxThis is only one reason why good SQL programmers never use this proprietary non-relational "feature" in their code. Back to foundation. SQL is a set-oriented language. When you do an INSERT you put in a set, all at once. That set can be zero or more rows. But IDENTITY is a sequential count of insertion attempts. PHYSICAL insertion attempts. Not a LOGICAL concept at all. So how do you number these rows? Please read a book on RDBMS; your mindset is still in magnetic tape files and not RDBMS yet.Thu, 05 Jan 2012 11:57:00 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspxIdentity coloumn will be always auto gaenerated no need to insert the data for those column . Thu, 05 Jan 2012 05:07:11 GMTraghu-sqlhttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspxDo not include your identity column to you insert statement.insert into myTable(identitycol, col1, col2, coln) values(1, 'value 1', 'value 2', 'value n') ===>WRONG!insert into myTable(col1, col2, coln) values('value 1', 'value 2', 'value n') ===>RIGHT!Thu, 05 Jan 2012 01:28:41 GMTmhike2halehttp://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspxIt always helps if you include your table and the code you're using so we can see the actual issue. However, it appears that you are inserting into a table that has an identity column and you're trying specify a value. By default identity columns are automatically updated and you can't manually set the value unless you set the IDENTITY_INSERT on for the table you're trying to update.Wed, 04 Jan 2012 08:57:57 GMTRichard Moore-400646http://www.sqlservercentral.com/Forums/Topic1230002-392-1.aspxHi,I am having trouble inserting the multiple rows into a table with identity column. The error i get is...An explicit value for the identity column in table can only be specified when a column list is used and identity insert is ONWed, 04 Jan 2012 08:39:07 GMTdeep_kkumar