June 29, 2017 at 8:50 am
I have a table with 1 column (varchar) and when I create an identity column with autoincrement on it. It does not keep the order which the table is in. How do I create an identity column without loosing the existing order ?
June 29, 2017 at 9:20 am
I'm not defining the order. It's the natural order that the data is in. When I do a Select * from table...and returns the order it is in. When I create an identity column....the order changes when i view the data. Things are not coming up in the same order anymore.
June 29, 2017 at 9:31 am
GrassHopper - Thursday, June 29, 2017 9:20 AMI'm not defining the order. It's the natural order that the data is in.
There's no such thing as 'natural order' in a table. Tables are unordered sets of data.
When I do a Select * from table...and returns the order it is in. When I create an identity column....the order changes when i view the data. Things are not coming up in the same order anymore.
If you don't specify an order by clause, the order that the data is returned is undefined, and any order is as good as any other order. If you want your data back in a particular order, you must put an ORDER BY clause on the query that returns the data. It's the only way you are guaranteed to get data in any particular order
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2017 at 9:38 am
Then your data is completely unordered. You can't guarantee it, and you don't have a way of replicating it. As I said, data in SQL tables is unordered, and the order that you get the results in can change EVERY time you run a query, unless you define an ORDER BY clause.
You could try this, but there no way you can actually guarantee the order without defining an ORDER BY clause. Note I would suggest doing a backup of your table/database first, because this process WILL DELETE ALL THE DATA IN YOUR TABLE:--Create a temp table
CREATE TABLE #TempData (id int IDENTITY(1,1), YourColumn varchar(50));
GO
/*
HOPE everything goes in in the "right" order (not that you can guarantee it.)
This step is PURE LUCK
*/
INSERT INTO #TempData (YourColumn)
SELECT YourColumn
FROM YourTable;
GO
--Delete your existing data
DELETE FROM YourTable;
GO
--Add your new Column
ALTER TABLE YourTable ADD IDColumn int IDENTITY(1,1);
GO
--Allow Identity inserts
SET IDENTITY_INSERT YourTable ON;
GO
--Insert data
INSERT INTO YourTable (IDColumn, YourColumn)
SELECT id, YourColumn
FROM #TempData;
GO
--Disable Identity inserts
SET IDENTITY_INSERT YourTable OFF;
GO
--Clean up
DROP TABLE #TempData;
GO
--Check the data
SELECT *
FROM YourTable
ORDER BY IDColumn;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 29, 2017 at 9:43 am
Without the actual schema, and some sample data, it's a bit difficult to figure out exactly what may be going on.
I created a single column table with a varchar field, no keys or indexes, and filled it with 1000 rows.
SELECT * returned the data in the same order before and after I added an identity column.
Here's the code:
DROP TABLE dbo.WhatOrder
CREATE TABLE dbo.WhatOrder
(
Varchar_Field varchar(255) NOT NULL
)
INSERT INTO WhatOrder(Varchar_Field)
SELECT B.name + CONVERT(varchar(20), ROW_NUMBER () OVER (ORDER BY A.name))
FROM sys.objects A
CROSS APPLY sys.objects B
SELECT *
FROM WhatOrder
ALTER TABLE WhatOrder ADD WhatOrder_ID int IDENTITY(1,1)
SELECT *
FROM WhatOrder
My question is what does the order matter? If the data needs to be returned in a specific order, your query needs to specify that with an ORDER BY clause.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 29, 2017 at 9:50 am
This data was imported from another format. Someone else will be taking care of it before importing it into SQL. Thanks! I definitely learned something about the order of tables.
June 29, 2017 at 10:17 am
Michael L John - Thursday, June 29, 2017 9:43 AMWithout the actual schema, and some sample data, it's a bit difficult to figure out exactly what may be going on.I created a single column table with a varchar field, no keys or indexes, and filled it with 1000 rows.
SELECT * returned the data in the same order before and after I added an identity column.
Just pure luck then, tbh. I just created a table (went a bit excessive) with 99,999,999 rows, and a top 1 returned the value 28,942,738 row's value.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply