[dba-SQLServer] Difference between views and queries

Just a followup to Shamil's concise list of cool things about views.
Indexed views provide pretty dramatic performance increases, but not
without cost. Inserts and updates are slower, so they are best used with
tables that are updated/added to relatively rarely. This does not mean
that they should be used only with lookup tables. Some transaction
applications rarely update data once it's in, and don't do huge volumes
of inserts either. Best to check performance on a copy of the real
database first, just to see what the cost is going to be.

I just wrote a piece about using Instead Of triggers to make
un-updatable views updatable. It should appear soon in a e-zine near you
:)

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Monday, July 12, 2004 2:56 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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
>

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com