none
display order is differ

    Вопрос

  • Hi all,

    I have an table called Test1 with

    ID Identity column Primary key

    Username Varchar

    Password Varchar

    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 г. 12:36

Ответы

  • 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 ;

    • Предложено в качестве ответа Manish_BI 20 февраля 2012 г. 13:15
    • Помечено в качестве ответа Stephanie Lv 27 февраля 2012 г. 7:12
    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 ;

    • Предложено в качестве ответа Manish_BI 20 февраля 2012 г. 13:15
    • Помечено в качестве ответа Stephanie Lv 27 февраля 2012 г. 7:12
    20 февраля 2012 г. 13:10
  • Hi,

    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.

    Elmozamil Elamir

    MyBlog


    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
    http://elmozamil.blogspot.com

    20 февраля 2012 г. 13:14