Potential SQL Server Express Bug.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Potential SQL Server Express Bug.

Author

Topic

dedvalson
Starting Member

2 Posts

Posted - 2007-04-19 : 15:15:29

Hi all,

I have run into what I think is a bug in SQL Server Express version 2005.90.3042.0

To reproduce it, create a simple table as described below:

CREATE TABLE [dbo].[test](
	[priority] [tinyint] NOT NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
	[priority] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

OK, a simple table with one tinyint column. It doesn't actually matter if you create the primary key or not.

Then I populated the table with 10 entries, with the numbers 1 - 10 in the priority column.

So the query

SELECT     *
FROM         test

produces

priority
1
2
3
4
5
6
7
8
9
10

Now I created a view using the following SQL

CREATE VIEW [dbo].[showtestd]
AS
SELECT     TOP (100) PERCENT dbo.test.*
FROM         dbo.test
ORDER BY priority DESC

If then run the following

SELECT     *
FROM         showtestd

I would expect the see the result ordered by descending index. However, the DESC in the view is ignored, it comes out sorted ascending, So I still see 1-10.

So I am hoping someone can tell me if this is a real bug, or if I misunderstand views.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-19 : 15:34:16

You misunderstand views. Any SELECT that you execute needs to explicitly indicate the ordering, otherwise SQL Server will retrieve the rows and then return them in whichever way it determines is most efficient.

Views typically do not have ordering, and ordering is only important if a view does something like SELECT TOP x, in which case the ordering is used to determine which rows are returned. But for the order of the rows returning, that is always ultimately decided by the "outermost" SELECT in any SQL statement.

- Jeff
http://weblogs.sqlteam.com/JeffS

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2007-04-19 : 18:35:43

The behavior you describe is actually expected. The "Top 100...Order By" trick you're trying to use is not meant for the way your using it and has a rich debate surrounding it in the field. You can take a look at the comments from the SQL Server Query Optimizer team (and they should know) by reading their blog on this topic: http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

dedvalson
Starting Member

2 Posts

Posted - 2007-04-20 : 07:26:18

Thanks for the responses. It is actually good to know that I was wrong about this. This code did happen to work in SQLServer 2000, but I guess in reality it never should have worked.

Thanks for the great info.

Don

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-20 : 08:24:48

Great link, thanks Mike!

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources