[dba-SQLServer] Difference between views and queries

Amen, brother! I couldn't have said that better myself.

:)

David


Arthur Fuller wrote:


	As usual, Shamil, you make excellenmt points. We are not arguing so much
	as complementing each other's position.
	
	IMO, (and that's all it is), everything the database can do the database
	should do. In my ideal SQL app, there would be almost no code in the FE
	except the code that decides which sproc to fire and what to pass as
	arguments. The more code that exists in the FE, the weaker I think the
	app is. Why? Because I don't want some "power user" to load Excel and
	f**k with the data!
	
	So when I build a SQL database, NO ONE (except me and my peers) can
	touch a table. Everyone else using the system hits a sproc or view or
	UDF. End of story. I, as godlike being, can touch tables. Mere mortals
	cannot. Period. No exceptions.
	
	In practice, this means that I examine the human roles, i.e. job specs,
	to see what the various humans in the organization are allowed to do. I
	build roles corresponding to job specs. I generate simple sprocs for
	each op (DISU = delete/insert/select/update) on each table. Could be
	that a single form hits a number of tables, but the hits always result
	in some combination of DISU -- there are no other ops. Having built the
	single-table DISU ops, it becomes trivial to write a sproc that hits say
	5 tables, inserting into 2, updating 1, and deleting from 2. Nothing to
	it, brain-dead simple once you have the atomic pieces in place.
	
	Ideally, I don't want any code that touches the database in the FE. Why?
	Because if we dump FE#1 (let's call it Access) and replace it with FE#2
	(let's call it vb.net), I don't want to rewrite any database-hitting
	code. I want to fire sprocs and/or call UDFS and be done with it. So if
	some "power user" fires up Excel and creates an ODBC hook to the
	database, he can't do anything but select (at most). Can't add, can't
	edit, can't insert, can't delete. Substitute POwerBuilder or Delphi or
	anything you want... If the only available access is via
	sprocs/views/udfs, then I'm confident that you can't trudge in and stomp
	all over my data.
	
	Every new UI inherits the smarts in the sprocs, and nothing needs to be
	duplicated except the calls to the sprocs/views/udfs. IMO, this way no
	code needs to be copied from one UI to another; you simply fire the
	sprocs, passing what they require. If there's a bug in the sproc you fix
	the sproc and all apps inherit the fix -- because the logic resides in
	precisely one place -- the db, where it belongs!
	
	Ok, I stated this a little bit fundamentalist. There are exceptions, and
	I accept them.