[dba-SQLServer] Difference between views and queries

> jwcolby wrote:
>Can anyone explain the difference between a view and a query?
Are you talking about MS SQL 2000 views?
If yes, then here is what you can do with views and cannot with just
T-SQL queries:

- views can be indexed - these indexes use statistics, these statistics
could be updated - so with views you can fine tune performance of the
data querying;
- views may have triggers - you can use such (instead of insert/update)
triggers to insert into/update very tricky views, which can't be updated
using plain update queries;
- views can use WITH CHECK OPTION to prevernt inconsistent updates;
- views can use SCHEMABINDING option by thus preventing deletion of the
veiws/tables they are defined on before deletion of these queries;
- views can be partitioned within one of several data files/servers -
this allows advanced perfomance tuning...
....

Shamil

----- Original Message ----- 
From: "MartyConnelly" <martyconnelly at shaw.ca>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Monday, July 12, 2004 12:42 AM
Subject: Re: [dba-SQLServer] Difference between views and queries


> jwcolby wrote:
>
> >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.
> >
> >
> >
> Interestingly ADOX catalogs describes Access queries as Type  VIEW
>
> Sub testa()
> 'Declare a reference to an ADOX Catalog Object
> Dim cat1 As New ADOX.Catalog
> Dim cnn1 As ADODB.Connection
> Dim c As Object
> Dim cn As ADODB.Connection
> Set cnn1 = New ADODB.Connection
>
> cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>            "Data Source=C:\Program Files\Microsoft
> Office\Office\Samples\Northwind.mdb;"
>
> 'cnn1.Open "Provider=sqloledb;" & _
> '           "Data Source=(local);" & _
> '           "Initial Catalog=Pubs;" & _
> '           "User Id=sa;" & _
> '           "Password="
>
> 'Open an ADO Connection Object
> 'Assign the connection to the catalog
> cat1.ActiveConnection = cnn1
>
> 'Loop through the tables in the catalog
> Dim str1 As String
> Dim tbl1 As ADOX.Table
> For Each tbl1 In cat1.Tables
> Debug.Print tbl1.Type & "-"; tbl1.Name
>   If tbl1.Type = "TABLE" Then
>      str1 = str1 & tbl1.Name & vbCr
>
>   End If
> Next
> Debug.Print str1
>
> End Sub
>
>
> -- 
> Marty Connelly
> Victoria, B.C.
> Canada
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>