Michael Maddison
mmaddison at optusnet.com.au
Sun Jun 13 09:56:57 CDT 2004
jwcolby wrote On 6/10/2004 9:33 AM: >Can anyone explain the difference between a view and a query? Views use a >query, plus the view keyword. I have a couple of books that I have read the >chapter on Views, but I so far haven't managed to "get" why you wouldn't >just use the query itself instead of turning it into a view. > > A query is a request for an Access Database, however for Sql Server you would either use a View or Stored Procedure to return the data you wanted... you are also able to use dynamic SQL to retrieve the information you need. ANY request given to the SQL Server engine is managed by the engine, unless you are running Remote servers (iirc). In Sql Server, it is TABOO, nay, GENERALLY bad practice to use dynamic sql because of the implication of SQL INJECTION attacks, this poses a "real" security threat to your database. and your server. another reason to use a VIEW over dynamic sql is that it is pre-optimized by the SQL Server Engine and thus runs faster and more efficient. >>>Sorry, this is not true. SQL creates an execution plan the 1st time a SQL statement is run, whether it is a view, sproc or dynamic makes no difference. Additionally if you use Dynamic SQL then your individual users who access the server will need EXPLICIT "SELECT" permissions by you, which is another 'bad' practice. >>>Not if you use roles, now if you don't use roles thats just making work for the dba. You could give select permissions and deny insert + update + delete. In SQL Server you make data available to your users via VIEWs and Stored Procedures or some other secure way in order to protect your tables and it's data. >>>I would lose little sleep over developers executing sql in a compiled application. I'd be more concerned if it was a web app because of the injection threat, but even then you can take measures to defeat attacks (or so I believe, I don't do much SQL + WWW). Sometimes you have to use dynamic SQL, be extra careful on the web, use a sproc so you can test the string passed or even better build the string inside the sproc. There is no performance penalty... cheers Michael M PS In case I've given the wrong impression I favour using sprocs where possible, views are all but useless IMO. Use them to present joined tables for reports etc. ya get wot I mean? -- -Francisco _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com