Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
display order is differ

Beantwortet display order is differ

  • Monday, February 20, 2012 12:36 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Monday, February 20, 2012 1:10 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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 ;

     
  • Monday, February 20, 2012 1:14 PM
     
      Has Code
    Sign In to Vote
    0

    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