text: Q:
  
  SQL Server efficiently filter and sort Properties table
  
  I have a scenario that we need to filter and sort documents stored in the documents table by document properties. Each document can have an unknown number of Properties.
  The Documents table columns are something like:
  ID, Name, Modified, ...
  and the Properties table is:
  DocumentID, PropertyName, TextValue, DateValue
  All columns in the Properties table are indexed to allow fast retrieval.
  The first attempt was to write a query that looks like:
  SELECT DocumentID 
  From Properties 
  WHERE (PropertyName ='Status' and TextValue = ' Approved')
  
  This worked fine for simple filter scenario. For a scenario that returns 
  SELECT DocumentID 
  From Properties 
  WHERE ((PropertyName ='Status' and TextValue = ' Approved') and   
         (PropertyName ='Dept' and TextValue = 'IT'))
  
  Once there are more filter conditions nothing is returned. 
  After some research I tried a few options:
  
  Using PIVOT to convert rows to columns with dynamic sql. Very slow and still does not work. Documents can have unique properties
  Using Dynamic with left join
  Using subqueries 
  
  I have followed the answer provided Efficiently convert rows to columns in sql server.
  With mongodb that would be a much easier task but we need to achieve this same flexibility and speed with SQL Server.
  SQL PIVOT is way too slow for this scenario. Any suggestion to how can this be made fast?
  Update: After reading some of the suggestions and comments I have have tweaked the proposals to the following:
  SELECT DocumentID FROM Properties
  WHERE DocumentID in
      ( SELECT DocumentID  FROM Properties WHERE Name = 'Status' and TextValue = 'Approved')
      and | or DocumentID in( SELECT DocumentID  FROM Properties WHERE Name = 'Dept' and TextValue = 'IT')
  
  Next step is to test performance to large data sets > 5million 
  Any suggestions for the above pseudo code?
  
  A:
  
  The PropertyName can never have a value of both 'Status' and' Approved'.
  TextValue can never have a value of both ' and 'IT'.
  and is processed before or but ( ) before either, so you could skip the ( ) but typically leave it for clarity.
  SELECT DocumentID 
  From Properties 
  WHERE (PropertyName ='Status' and TextValue = ' Approved') 
     or (PropertyName ='Dept'   and TextValue = 'IT')
  
  