Answer / pravati

ALTER TABLE Tablename
ADD oldcolumnname newcolumnname

Answer / sambashiva

Yes we can
1. Adding Identity Property to an existing column in a table.

How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action?

For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table.

However, there is an easy way to accomplish this action. It can be done through SSMS.

Are you finding my answer difficult to believe? Let me explain.

Let’s first see what SSMS does in backend when you add Identity property on an existing column in any table.

Now, let’s create an example table for better understanding.



This table is vacant, with no records, as you can see in the following screenshot.



Take a look at the design of this table in SSMS.



Now let us make eid, an Identity column.

This is very easy. All you have to do is just select Yes from the drop down list and you are done!



But before moving further let’s see what T-SQL SQL Server is using to make this change.

You will notice that T-SQL is used by SQL Server to make this change.

After you make the change for Identity property from No to Yes, on top in tools box, you will see Generate Change Script. This is the T-SQL Script that SQL Server will use to make this change.





Unfortunately, I cannot expand this dialogue box further to show you the complete script, so I have copied this script below.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_example1 ON
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_example1 OFF
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT

What is the difference between resultset and resultsetmetadata?

0 Answers  

---

Can we have more than one NULL in a column having unique constraint?

12 Answers   247Customer,

---

what are the main differences between ms access and sqlserver

1 Answers   C DAC, CDAC,

---

What happens to a trigger with multiple affected rows?

0 Answers  

---

How to enable/disable indexes?

0 Answers  

---

What are sub-queries? Give example? In which case sub-queries are not feasible?

4 Answers   Infosys,

---

What is entity data services?

0 Answers  

---

how to update a null value field in sql server eg a table contains 3 fields id,name,salary and 3 records salary of 1 record is null i want update the nullfield 111 arun 300 112 ddd 200 113 ttt null i want to update table with add 100 to every record include null after updation the recrds should be 111 arun 400 112 ddd 300 113 ttt 100

6 Answers   ABC, HCL,

---

How do you make a trace?

0 Answers  

---

What are the types of model in sql server and explain

2 Answers   TCS,

---

Explain identity in sql server?

0 Answers  

---

Explain Active/Active and Active/Passive cluster configurations?

1 Answers  

---