Sql-server – Weird WHERE Clause Behavior. Why does this return a row

sql server

In SQL Server 2008, why does this return the row, even when I add a whitespace, or two, or more, to the end of the where clause? shouldn't zero records be found in the following example?

WITH SRC AS (SELECT cast('12345' as varchar) DEMO)
SELECT * FROM SRC WHERE DEMO='12345  '

What if one needs to query to find '1234 ' but not '1234'

Best Answer

What is happening is that SQL pads spaces to the end of strings so that they are of the same length. So if you try do something like:

SELECT 1 WHERE '' = ' '

You will actually get 1 back even though they are not the same. However, if you do something like:

SELECT 1 WHERE 'a' = ' a'

It will not return anything as it would be comparing 'a ' to ' a' which do not match.

However, this would return 1:

SELECT 1 WHERE 'a' = 'a '

As it is comparing 'a ' to 'a '

Related Solutions

Sql-server – SQL Server Query: Inefficient where clause

If you don't need the other data from the NETWORK_STATUS table, how about:

select *
from dbo.NETWORK AS n
inner join dbo.vwNETWORK_KEYMSTN AS km 
  on n.Network_ID = km.Network_ID
inner join dbo.vwAPPROVAL_LATEST AS a 
  on n.Network_ID = a.Network_ID
inner join dbo.APPROVAL_VINTAGE  AS av 
  on a.Approval_ID = av.Approval_ID 
  and km.Milestone_Type_ID = av.Milestone_Type_ID
inner join dbo.NETWORK_MILESTONE AS m 
  on A.Approval_ID = m.Approval_ID 
  and km.Milestone_Type_ID = m.Milestone_Type_ID
inner join dbo.REF_MILESTONE AS rm 
  on km.Milestone_Type_ID = rm.Milestone_Type_ID
WHERE EXISTS
(
  SELECT 1 FROM dbo.NETWORK_STATUS 
    WHERE Network_ID = n.Network_ID
    and Status_Type_ID = 2
    --and Status_Type_ID = rm.Status_Type_ID
);

Sql-server – Why is the ORDER BY clause in a view ignored as soon as it’s called with a WHERE claue

The query you posted is not valid for creating a view; running CREATE VIEW xy AS for this query will result in an error. Are you using a TOP clause?

A view, being a table expression (a set), can't have the order defined, since that would be against the principles of a relational model (there is no order for rows in a relational table - a set is an unordered collection of tuples). Same goes for other table expressions - derived tables, CTEs etc.

From BOL article about the ORDER BY clause:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Long story short: Use the ORDER BY clause in the outer query that references the view. Do not use it in a view. Even using it with TOP(100) PERCENT (or on SQL Server 2012, the OFFSET-FETCH equivalent) does not guarantee presentation order, it just means you'll get the top 100% of the rows, in any order.

Best Answer

Related Solutions

Sql-server – SQL Server Query: Inefficient where clause

Sql-server – Why is the ORDER BY clause in a view ignored as soon as it’s called with a WHERE claue

Related Question