I've got a query that goes something similar to the next:

select <field list> 
from <table list>
where <join conditions>
and <condition list>
and PrimaryKey in (select PrimaryKey from <table list>
    where <join list> 
    and <condition list>)
and PrimaryKey not in (select PrimaryKey from <table list>
    where <join list>
    and <condition list>)

The sub-choose queries have multiple sub-choose queries that belongs to them that I am not showing so they won't clutter the statement.

Among the designers on my small team thinks a view could be better. I disagree for the reason that the SQL statement uses variables passed in through the program (in line with the user's login Id).

What are the solid rules on whenever a view ought to be used versus. utilizing a SQL statement? What type of performance gain issues exist in running SQL claims by themselves against regular tables versus. against sights. (Observe that all of the joins / where the weather is against indexed posts, to ensure that should not be an problem.)

EDIT for clarification...

Here's the query I am dealing with:

select obj_id
from object
where obj_id in( 
(select distinct(sec_id) 
		from security 
		where sec_type_id = 494
		and (
			(sec_usergroup_id = 3278 
			and sec_usergroup_type_id = 230)
			or
			(sec_usergroup_id in (select ug_gi_id 
			from user_group 
			where ug_ui_id = 3278)
			and sec_usergroup_type_id = 231)
		)
		and sec_obj_id in (
		select obj_id from object 
		where obj_ot_id in (select of_ot_id 
			from obj_form 
			left outer join obj_type 
			on ot_id = of_ot_id 
			where ot_app_id = 87
			and of_id in (select sec_obj_id 
				from security
				where sec_type_id = 493
				and (
					(sec_usergroup_id = 3278 
					and sec_usergroup_type_id = 230)
					or
					(sec_usergroup_id in (select ug_gi_id 
						from user_group 
						where ug_ui_id = 3278)
					and sec_usergroup_type_id = 231)
					)                
			)	
			and of_usage_type_id  = 131
		)
		)	
		)
)
or 
(obj_ot_id in (select of_ot_id 
		from obj_form
		left outer join obj_type 
		on ot_id = of_ot_id 
		where ot_app_id = 87
		and of_id in (select sec_obj_id 
			from security
			where sec_type_id = 493
			and (
				(sec_usergroup_id = 3278 
				and sec_usergroup_type_id = 230)
				or
				(sec_usergroup_id in (select ug_gi_id 
					from user_group 
					where ug_ui_id = 3278)
				and sec_usergroup_type_id = 231)
				)
		)
		and of_usage_type_id  = 131

	)
	and
	obj_id not in (select sec_obj_id 
		from security 
		where sec_type_id = 494)
)

With respect to the database vendor, generally, the execution of the query against a view combines the SQL defined within the View using the Where clause predicates and Order By clause sort expressions appended towards the sql that you simply pass from the View, to develop a combined complete SQL query to complete. Your particulars are often performed as if it had itself been passed to question processsor, so tthere shouldn't be difference.

Sights are an business tool, not really a performance enhancement tool.

From SQL Server View resolution

When an SQL statement references a nonindexed view, the parser and query optimizer evaluate the origin of both the SQL statement and also the view and then resolve them right into a single execution plan. There's not just one plan for that SQL statement along with a separate arrange for the vista.

Regular (non indexes / materialized) Sights are simply aliases they do not offer any performance advantages. Choosing from the View creates the identical query plan as choosing from the table.

Sights aside, aren't the PrimaryKey AND clauses redundant? When the PrimaryKey value is within a listing, would it not 't be Within the other list? I believe condensing individuals two clauses into you might boost performance.