Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Same SELECT with different order
Message
From
27/11/2009 16:28:00
Peter Wagner
Point Informática Ltda.
Limeira, Brazil
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
Other
Title:
Same SELECT with different order
Environment versions
SQL Server:
SQL Server 2008
Application:
Desktop
Miscellaneous
Thread ID:
01436778
Message ID:
01436778
Views:
109
Hi, a have a doubt about the SELECT Clause in SQL Server with unique key indexes and the result of data, and why a difrent result with the same SELECT clause.

I have 2 tables, the scrip follows:
1º table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Teste1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Teste1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[descricao] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_Teste1_ID] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

ALTER TABLE [dbo].[Teste1] DISABLE CHANGE_TRACKING 
GO
SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Teste1]') AND name = N'IX_Teste1')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Teste1] ON [dbo].[Teste1] ([descricao] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
2º table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Teste2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Teste2](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[descricao] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[tipo] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_Teste2] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Descricao] UNIQUE NONCLUSTERED 
(
	[descricao] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

ALTER TABLE [dbo].[Teste2] DISABLE CHANGE_TRACKING 
GO
SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Teste2]') AND name = N'IX_Tipo')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tipo] ON [dbo].[Teste2] 
(
	[tipo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
Now inserting 3 rows in each table:
-- 1ª table
insert into dbo.teste1 (descricao) values('C')
insert into dbo.teste1 (descricao) values('B')
insert into dbo.teste1 (descricao) values('A')

-- 2ª table
insert into dbo.teste2 (descricao, tipo) values('C', 'T1')
insert into dbo.teste2 (descricao, tipo) values('B', 'T2')
insert into dbo.teste2 (descricao, tipo) values('A', 'T3')
Select in both tables:

select * from dbo.Teste1
select * from dbo.Teste2

What I want to know is Why in the first table with the select clause the result was ordered bay the "descricao" column, and why in the second select the result of the same SELECT command was returned in order the data was inserted,(no order).

Any ideia?
Next
Reply
Map
View

Click here to load this message in the networking platform