20 февраля 2012 г. 12:36
I have an table called Test1 with
ID Identity column Primary key
And some other columns
Assume that Now I have 30 records on the table so, if I execute the select * from Test1 query then first it has to show the ID=1 then Id=2 …..finally ID 30
But now its displaying Id 1 then id 20, 21,21….then 2,3,4…..
Why its showing like this?
Thanks In Advance, Jeyaseelan
20 февраля 2012 г. 13:10
Because there is no order per se in a set. You may get often the results returned in the physical order of your table, but this is not guaranteed.When you want a specific order, then you need to pass a request for it to SQL Server in the outer most relevant SQL statement. Take a look at the ORDER BY clause, e.g.:
SELECT * FROM Test1 ORDER BY Id ASC ;
20 февраля 2012 г. 13:14
is you Identity is a cluster index or non clustered index,
if your index is not clustered your data will be displayed as it stored in your server.
Check those Pics:
SO if you need it order you have to add order by to your query, or change your index to clustered index
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Iden]') AND name = N'PK_Iden') ALTER TABLE [dbo].[Iden] DROP CONSTRAINT [PK_Iden] GO USE [Tests] GO /****** Object: Index [PK_Iden] Script Date: 02/20/2012 16:10:37 ******/ ALTER TABLE [dbo].[Iden] ADD CONSTRAINT [PK_Iden] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
I hope this is helpful.
Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid