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.vDirectCeASselect * from dbo.DirectCe where mailAddress = space(1) or city = space(1) or state = space(1) or zip=space(1)UNION ALLselect * 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
You really have to do the sort when SELECTing from the view for the sort to be guaranteed to work:
SELECT *FROM dbo.vDirectCeORDER BY mailAddress
This is troubling me ... could not the data be cleaned up so it doesn't contain spaces like this?
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)
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?