May 2, 2013 at 9:00 pm
Comments posted to this topic are about the item Identity Insert
May 2, 2013 at 11:16 pm
Easy one to the end of week 😛
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 2, 2013 at 11:16 pm
Most of people don't know about it because it's not in use in any real scenario. I never use this kind of table in production environment, But i knew it due to someone ask me about it earlier.
Anyway, thanks for question.:-)
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
May 2, 2013 at 11:24 pm
May 3, 2013 at 1:20 am
good one, thank you for the post. 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 3, 2013 at 2:33 am
Danny Ocean (5/2/2013)
Most of people don't know about it because it's not in use in any real scenario.
Sure it is, if the table contains other columns, all with defaults.
As a simplistic example:
CREATE TABLE TableA(ID INT IDENTITY (1,1),
LastUpdated datetime default (getdate()))
I don't use this technique often, but have used it in production systems before.
May 3, 2013 at 2:37 am
Toreador (5/3/2013)
Danny Ocean (5/2/2013)
Most of people don't know about it because it's not in use in any real scenario.Sure it is, if the table contains other columns, all with defaults.
As a simplistic example:
CREATE TABLE TableA(ID INT IDENTITY (1,1),LastUpdated datetime default (getdate()))
I don't use this technique often, but have used it in production systems before.
... what Mr. Ocean meant was to use the defaults with the combination of identity column.
(the example what you have given is very nice and we have that set up in the prod server for one database. it actually reduces couple of parameters while inserting - createdate or updating the date)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 3, 2013 at 2:45 am
Danny Ocean (5/2/2013)
Most of people don't know about it because it's not in use in any real scenario. I never use this kind of table in production environment, But i knew it due to someone ask me about it earlier.Anyway, thanks for question.:-)
A real (especially in old release of sqlserver) scenario is a TALLY table.
[sql]
INSERT INTO TableA DEFAULT VALUES
GO 10000
[/sql]
Another way to insert values in Table A is:
[sql]
SET IDENTITY_INSERT TableA ON
INSERT INTO TableA SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY A.object_id) FROM sys.objects A, sys.objects B
SET IDENTITY_INSERT TableA OFF
[/sql]
May 3, 2013 at 4:35 am
I've learned to read the discussions about all the questions because I learn things there as well. Did not know about adding a number to GO to get multiple executions. THANKS!
May 3, 2013 at 5:36 am
Raghavendra Mudugal (5/3/2013)
Toreador (5/3/2013)
Danny Ocean (5/2/2013)
Most of people don't know about it because it's not in use in any real scenario.Sure it is, if the table contains other columns, all with defaults.
As a simplistic example:
CREATE TABLE TableA(ID INT IDENTITY (1,1),LastUpdated datetime default (getdate()))
I don't use this technique often, but have used it in production systems before.
... what Mr. Ocean meant was to use the defaults with the combination of identity column.
(the example what you have given is very nice and we have that set up in the prod server for one database. it actually reduces couple of parameters while inserting - createdate or updating the date)
nice explanation... But i have another column in this table "Name" this column is not have default properties.
will this property allow to insert???:w00t:
Manik
You cannot get to the top by sitting on your bottom.
May 3, 2013 at 5:56 am
manik123 (5/3/2013)
Raghavendra Mudugal (5/3/2013)
Toreador (5/3/2013)
Danny Ocean (5/2/2013)
Most of people don't know about it because it's not in use in any real scenario.Sure it is, if the table contains other columns, all with defaults.
As a simplistic example:
CREATE TABLE TableA(ID INT IDENTITY (1,1),LastUpdated datetime default (getdate()))
I don't use this technique often, but have used it in production systems before.
... what Mr. Ocean meant was to use the defaults with the combination of identity column.
(the example what you have given is very nice and we have that set up in the prod server for one database. it actually reduces couple of parameters while inserting - createdate or updating the date)
nice explanation... But i have another column in this table "Name" this column is not have default properties.
will this property allow to insert???:w00t:
Does your NAME column allow null values? (Moved my question to the end where it belonged :-P)
May 3, 2013 at 6:13 am
Nice question. Thanks.
Anyone who reads SQLSkills articles written by Paul, sure knows about it. He uses it a lot in his examples.
May 3, 2013 at 6:18 am
Danny Ocean (5/2/2013)
Most of people don't know about it because it's not in use in any real scenario. I never use this kind of table in production environment, But i knew it due to someone ask me about it earlier.Anyway, thanks for question.:-)
Good question!
Sometime ago I learned this trick when I had to do something similar to what the new SEQUENCE does, but in SQL 2008 (before SQL2012 was released), so I created a dummy table just to generate new ID's exactly like this, with only an IDENTITY column.
Thanks for the Friday-easy-one!
May 3, 2013 at 7:05 am
Interesting, nice question...
May 3, 2013 at 7:28 am
Thanks for the easy one to end the week!
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply