how is this query ordered
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[productimages](
[productimagesid] [int] IDENTITY(1,1) NOT NULL,
[productid] [int] NOT NULL,
[name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
select * from productimages where productid=15
select * from productimages where productid=14
I dont know how this is ordered
it is not ordered by 'name' or 'productimageid'
do not tell me how to order. I am using a content management system that is using this one query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what I meant to ask
is there anything in the productimages table that determines the order
productimages looks like a regular table to me
is there anything in the productimages table that determines the order
productimages looks like a regular table to me
No
I know you didn't ask for alternate solutions, (and I don't know much about CMS) but would it be possible for your content management system to access the data by calling a stored procedure rather than querying the table directly? That way you could have the sort order specified in the stored procedure.
ASKER
how could I check to see if this is a stored procedure
using sql server 2005 enterprise
using sql server 2005 enterprise
This is not a stored procedure, its table you created
I'm not sure I understand your question. I thought you had a table named productimages and from your CMS, you do queries against that table and you want to ensure the results come back in a particular order?
I was suggesting you could create a stored procedure that does the query for you and you'd just pass the parameter(s). For example:
I was suggesting you could create a stored procedure that does the query for you and you'd just pass the parameter(s). For example:
CREATE PROC usp_ProductImages(
@ProdID int
) AS
select * from productimages where productid=@ProdID
ORDER BY productid, name -- or change this line to whatever you want
-- So instead of doing:
select * from productimages where productid=15
-- Call the stored proc
EXEC usp_ProductImages 15ASKER
all led to my understanding that this query is not ordered
* Dave Pinal's blog describes how two very similar queries can show a different apparent order, because different indexes are used:
SELECT ContactID FROM Person.Contact
SELECT * FROM Person.Contact
* Conor Cunningham shows how the apparent order can change when the table gets larger (if the query optimizer decides to use a parallel execution plan).
* Hugo Kornelis proves that the apparent order is not always based on primary key. Here is his follow-up post with explanation.
Source: here