CREATE TABLE MyTable ( ID INT, Data varchar(max) )
GO
INSERT INTO MyTable VALUES ( 4, 'This is stuff' ), ( 6, 'This is more stuff' )
GO
SELECT * FROM MyTable
GO

--------
-- Adding IDENTITY property
--------
SELECT * INTO #MyTempTable FROM MyTable
GO
TRUNCATE TABLE MyTable
GO
ALTER TABLE MyTable ADD ID_II INT IDENTITY(1,1)
GO
ALTER TABLE MyTable DROP COLUMN ID
GO
ALTER TABLE MyTable ALTER COLUMN ID_II INT NOT NULL
GO
EXEC sp_rename 'MyTable.ID_II', 'ID', 'COLUMN'
GO
SET IDENTITY_INSERT MyTable ON
GO
INSERT INTO MyTable ( ID, Data ) SELECT t.ID, t.Data FROM #MyTempTable t
GO
SET IDENTITY_INSERT MyTable OFF
GO
INSERT INTO MyTable ( Data ) VALUES ( 'This is the last stuff' )
GO
DROP TABLE #MyTempTable
GO

SELECT * FROM MyTable
GO
EXEC sp_help MyTable
GO

--------
-- Removing IDENTITY property
--------
-- Drop FKs/UKs/PKs/Indexes that reference the Column being changed.
ALTER TABLE MyTable ADD ID_II INT
GO
UPDATE MyTable SET ID_II = ID
GO
ALTER TABLE MyTable DROP COLUMN ID
GO
ALTER TABLE MyTable ALTER COLUMN ID_II INT NOT NULL
GO
EXEC sp_rename 'MyTable.ID_II', 'ID', 'COLUMN'
GO
-- Recreate FKs/UKs/PKs/Indexes that referenced the Column being changed.

SELECT * FROM MyTable
GO
EXEC sp_help MyTable
GO

--Adding IDENTITY property to a Column
DROP TABLE MyTable
GO

Declare @pSortParam varchar(35)

Select @pSortParam = 'BfsXpLogID';

with a as (
Select * from dbo.BfsXpLog a with (nolock)
)
Select * from (Select top 10 * from a where @pSortParam = 'CreateDate' order by CreateDate) as b
union all
Select * from (Select top 10 * from a where @pSortParam = 'BfsXpLogID' order by BfsXpLogID) as b