T-sql 2012 sort

Transact-SQL
1

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?

2

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

3

You really have to do the sort when SELECTing from the view for the sort to be guaranteed to work:

SELECT *
FROM dbo.vDirectCe
ORDER BY mailAddress

1 Like
4

This is troubling me ... could not the data be cleaned up so it doesn't contain spaces like this?

5

The columns are very likely empty, rather than containing a single space.

Either way, it's better to write the WHERE lke this:

WHERE mailAddress = space(0) or city = space(0) or state = space(0) or zip=space(0)

6

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))

Maybe that doesn't matter though?