In a t-sql 2012 view, I would like to sort the data by mailAddress. However a sort is not allowed in a view. Thus I am trying to determine an alternative. Here is what I have come up with so far:
CREATE VIEW dbo.vDirectCe
AS
select *
from dbo.DirectCe
where mailAddress = space(1) or city = space(1) or state = space(1) or zip=space(1)
UNION ALL
select *
from dbo.DirectCe
where (mailAddress <> space(1)) and (city <> space(1)) and (state <> space(1)) and (zip<>space(1))
GO
Would you show no other options on what I can do to solve my problem?
Microsoft recommendation is that you should sort in the final select. Nothing else is guaranteed to return a record set to you in any predictable order (even if it appears that sometimes you get a consistent ordering without specifying an order by clause in the final select). So in your case, you would sort in places where you are going to use the view. For example
SELECT col1, col2 from dbo.vDirectCe ORDER BY col2
Its look like a Table Scan I think, isn't it? as is the second UNION ALL:
where mailAddress = space(1) or city = space(1) or state = space(1) or zip=space(1)
...
where (mailAddress <> space(1)) and (city <> space(1)) and (state <> space(1)) and (zip<>space(1))