Re: View or Not Views

no, an indexed view take the space of the index + related content.
and an indexed view is usefull when there is group by clause.
Like storing sum of sales by month and store, this indexed view will takes few Kb or Mb (while the source table contains day, product, promotion etc... info)

So an indexed view will take less space then the original tables.

if your indexed view as the same size of you original tables its because your view contains the same level of detail as your source table, then there is no advantage of using indexed views.

a view as the performance of the source tables and the indexes of these table. SQL server create a "table" in memory only when the query use order by or group by clause. SQL Server use the tempdb database to store temporary results and do the required calculations, but its a per-session "table", and you can't access or use these result directly (only the internal SQL cache will helps you). Simple queries don't required the usage of the tempdb database.
Badly designed SQL statement can produce a huge tempdb usage, so take the time to create good queries.

For your reporting side, I recommend to use your cube as the source of the report instead-of the database. The performance advantage will be very high.
If you really want to access the database in your reports and you always access aggregated data, then create pre-aggregated tables to store the result and use these tables instead of you 500Gb table.

"MIB" <MIB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:AF27FDCA-4468-4A3D-A096-CE0DFF75ABD7@xxxxxxxxxxxxxxxx
Thank you for your answer.
If I create an indexed view, i will have a stored set of data so i wil
increase the disk space needs. If my fact table is 500Go, i will need 500Go
for my view, Is it true?

"amish" wrote:


MIB wrote:
> In my project we are using OLAP for some reports and just SQL with > others
> tools for operational reporting.
> To create my cube, i'm using view. But for my operationl report should > i use
> view or not. I'm wondering concerning performance of the view.
> When i'm using a view, SQL Server create a physical table somewhere in
> memory, so for a fact table i'will use a lot of storage space for > nothing.
> What are you advice ??


Unless indexed, a view does not exist as a stored set of data values in
a database.
When you reference it , it just execute like other SQL Qeury. SQL
Server is not creating any physical table and handle it like other TSQL
Statement. So, you can use views in your reports.

Regards
Amish Shah
http://shahamishm.tripod.com


.

Relevant Pages

  • Re: Faircom c-Tree Server vs. Relational DBMS
    ... Our products were very reliable in terms of how the database ... SQL was used for building and upgrading the ... to process reports that take several hours to run. ... what database tools to use) that are dictated by management ...
    (comp.databases)
  • Re: How do i move an SQLDatabase to another location?
    ... I went to my clients and installed SQL Server ... Express and copied my database to the same location it was in while i was ... knowing that i can bring a database with me and install ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: New to Windows CE Development - Have some questions
    ... validating against the main database, or querying the database during data ... iterate through the SQL CE table and create new entries in the ... SQL Server database that correspond. ... >>> not connected to the Enterprise. ...
    (microsoft.public.windowsce.app.development)
  • A little OT - Quite a rant on microsoft.public.sqlserver.olap
    ... SQL authentication doesn't work for SQL Server to anywhere. ... reports in ADP; but somehow it's not getting traction. ... thru in order to create forms and reports against a database. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)