Index size.

Have a look at sp_spaceused. If you want to do this non-
programatically, you can use the taskpad view of tha
database.
Regards,
Paul Ibisonthanks..got it.

index scan and seek help...

hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.
moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert
|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.

> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be[vbcol=seagreen]
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution
>
|||How I can send u these infromation?

> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>
|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas

"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.

index scan and seek help...

hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.

> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution[vbcol=seagreen]
>|||How I can send u these infromation?

> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.sql

Index reorganization

With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I can
see witch index must be reorganized or reduilded (with
avg_fragmentation_in_perc).
Some indexes (clustured and nonclustered) are return many time, with a
different index_level and a different fragmentation for each level. Even
after a index reorganization, fragmentation stay high for a level and low for
others levels for this index.
I want to schedule a index maintenance, but in this case, some index are
always to reorganize ! Can I exclude these indexes by checking other fields
of sys.dm_db_index_physical_stats or anything else ?
Fillfactor for index is 90.
Thanks.Always go for the leaf level (visualize the index tree). I can't remember the value for that
reported by the DMV, but for a certain index it will be the one with most pages, like value 0. Also,
disregard indexes with less than say 100, 5000 or 1000 pages (MS recommendations is to not care if
fewer than 1000 pages). And remember when you have few pages, it is basically meaningless to talk
about fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"shwac" <shwac@.discussions.microsoft.com> wrote in message
news:2EC0728E-D3FE-4410-84CE-DA301D0FC6D0@.microsoft.com...
> With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I can
> see witch index must be reorganized or reduilded (with
> avg_fragmentation_in_perc).
> Some indexes (clustured and nonclustered) are return many time, with a
> different index_level and a different fragmentation for each level. Even
> after a index reorganization, fragmentation stay high for a level and low for
> others levels for this index.
> I want to schedule a index maintenance, but in this case, some index are
> always to reorganize ! Can I exclude these indexes by checking other fields
> of sys.dm_db_index_physical_stats or anything else ?
> Fillfactor for index is 90.
> Thanks.
>|||1) don't bother rebuilding/defragging stuff with < 1000 pages or so.
2) If the database doesn't have lots of contiguous free space in it,
defragging won't do any good because the pages can't be laid down in
sequential order --> remain fragmented. Double the size of your db and try
some defragging.
3) Not so important on 2005, but I still like to defrag clustered index
first (if necessary) and then do NC indexes.
4) You may want to control your defrag especially the first time after
making a lot of free space, since you could cause a huge tlog.
TheSQLGuru
President
Indicium Resources, Inc.
"shwac" <shwac@.discussions.microsoft.com> wrote in message
news:2EC0728E-D3FE-4410-84CE-DA301D0FC6D0@.microsoft.com...
> With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I can
> see witch index must be reorganized or reduilded (with
> avg_fragmentation_in_perc).
> Some indexes (clustured and nonclustered) are return many time, with a
> different index_level and a different fragmentation for each level. Even
> after a index reorganization, fragmentation stay high for a level and low
> for
> others levels for this index.
> I want to schedule a index maintenance, but in this case, some index are
> always to reorganize ! Can I exclude these indexes by checking other
> fields
> of sys.dm_db_index_physical_stats or anything else ?
> Fillfactor for index is 90.
> Thanks.
>|||Thanks for your help, all indexes with high fragmentation that I have are
less then 1000 pages. I will modify my script to take account of this
parameter.
"Tibor Karaszi" wrote:
> Always go for the leaf level (visualize the index tree). I can't remember the value for that
> reported by the DMV, but for a certain index it will be the one with most pages, like value 0. Also,
> disregard indexes with less than say 100, 5000 or 1000 pages (MS recommendations is to not care if
> fewer than 1000 pages). And remember when you have few pages, it is basically meaningless to talk
> about fragmentation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "shwac" <shwac@.discussions.microsoft.com> wrote in message
> news:2EC0728E-D3FE-4410-84CE-DA301D0FC6D0@.microsoft.com...
> > With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I can
> > see witch index must be reorganized or reduilded (with
> > avg_fragmentation_in_perc).
> >
> > Some indexes (clustured and nonclustered) are return many time, with a
> > different index_level and a different fragmentation for each level. Even
> > after a index reorganization, fragmentation stay high for a level and low for
> > others levels for this index.
> >
> > I want to schedule a index maintenance, but in this case, some index are
> > always to reorganize ! Can I exclude these indexes by checking other fields
> > of sys.dm_db_index_physical_stats or anything else ?
> >
> > Fillfactor for index is 90.
> >
> > Thanks.
> >
> >
>|||> 2) If the database doesn't have lots of contiguous free space in it,
> defragging won't do any good because the pages can't be laid down in
> sequential order --> remain fragmented. Double the size of your db and
> try some defragging.
Not true.
If you defrag the index, the pages will be ordered within the existing
extents allocated to the index - therefore allowing 8-page IOs during
readahead instead of single-page IOs. What you don't allow is 32-page IOs
during readahead because extents are not reordered to be contiguous.
If you rebuild the index, you'll at least get pages ordered within extents,
and the degree of extent contiguity will depend on how much contiguous free
space is available to be used in the various files in the database.
Adding extra space to a database does nothing for the defrag algorithm, only
for the rebuild algorithm.
Thanks
--
Paul S. Randal
Managing Director, www.SQLskills.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23hkmcUp5HHA.4436@.TK2MSFTNGP03.phx.gbl...
> 1) don't bother rebuilding/defragging stuff with < 1000 pages or so.
> 2) If the database doesn't have lots of contiguous free space in it,
> defragging won't do any good because the pages can't be laid down in
> sequential order --> remain fragmented. Double the size of your db and
> try some defragging.
> 3) Not so important on 2005, but I still like to defrag clustered index
> first (if necessary) and then do NC indexes.
> 4) You may want to control your defrag especially the first time after
> making a lot of free space, since you could cause a huge tlog.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "shwac" <shwac@.discussions.microsoft.com> wrote in message
> news:2EC0728E-D3FE-4410-84CE-DA301D0FC6D0@.microsoft.com...
>> With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I
>> can
>> see witch index must be reorganized or reduilded (with
>> avg_fragmentation_in_perc).
>> Some indexes (clustured and nonclustered) are return many time, with a
>> different index_level and a different fragmentation for each level. Even
>> after a index reorganization, fragmentation stay high for a level and low
>> for
>> others levels for this index.
>> I want to schedule a index maintenance, but in this case, some index are
>> always to reorganize ! Can I exclude these indexes by checking other
>> fields
>> of sys.dm_db_index_physical_stats or anything else ?
>> Fillfactor for index is 90.
>> Thanks.
>>
>

Index on View

Hi

I have a problem creating an index on a view. The view should return the record corresponding to the Maximum Obje_ID. This seems to work.

CREATE VIEW dbo.D_Object_View
WITH SCHEMABINDING
AS
SELECT
Policy_ID,
Obj_ID,
Environment_Code,
CoB,
Sub_CoB,
Policy_No,
Version_No,
Object_Type,
Item_Seq,

FROM dbo.D_Object
WHERE
(Obj_ID IN
(SELECT MAX(Obj_ID)
FROM dbo.d_object
GROUP BY Environment_Code, COB, Policy_No, SUB_COB, Object_Type, Item_Seq))

I create the index with the following statement :
CREATE UNIQUE CLUSTERED INDEX [IX_Object_ID] ON [dbo].[D_Object_View]([Obj_ID]) ON [PRIMARY]

but get the following error :
Cannot index the view 'DB.dbo.D_Object_View'. It contains one or more disallowed constructs.

I think it is because of the MAX statement but don't know of any other way to do it. :confused:Is Obj_ID part of an index in the parent tables? If so the index on the view may not buy you much performance improvement. How many rows are in each table and what's the execution plan look like for the view sql without the index?

Have you tried creating a non-unique index on the column?|||Yes Obj_ID is an index on the parent table and it cpontains aprox. 5 mil records but will increase as i need to add more data.

I have tried creating a non-unique one but get the following error :
Nonunique clustered index cannot be created on view 'D_Object_View' because only unique clustered indexes are allowed.|||Sorry, forgot about that I'm sure you've tried it but what about a non-clustered index? And does the optimizer utilize the existing index in the execution plan?|||A nonclustered gives me the following error:
Cannot create index on view 'D_Object_View'. It does not have a unique clustered index.

My knowledge of SQL is limited but if I understand correctly about the optimizer ... the estimated execution plan utilises a Index scan. This is good right ?

Index On View

Hi,
Can we define Index on view?
Thanks,
Rahul Jhayes, but there are some rules you have to follow.

http://msdn2.microsoft.com/en-US/library/ms191432.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx|||thnkx jezemine|||Hi,
Can we define Index on view?

Thanks,
Rahul Jha

Are you playing us?

I mean where do you come up with these questions

For example, wouldn't you be more worried about appropriate indexes on the tables first?|||Was worried about indexes on the table first Brett. And once done with that want to check with the indexed view. Who knows that might again improve the performance. But I was amazed that why are you asking this......

No, m not playing with any one Brett.|||I am here to help myself from the support of fellow members.|||Yes Rahul, but this information is easily found in Books Online or a simple google search. The forum does not exist to copy/paste the relevant documentation for you, or do your searches for you. Your first resource should always be Books Online. Your second resource should be a google search, or a search of existing threads on dbforums. Only after exhausting these resources should you post your question on the forum.|||Was worried about indexes on the table first Brett.
Not necessarily. As far as I understand the indexed views, SQL Server will use the indexes when the view is queried directly, bypassing the table. An indexed view in SQL Server seems to be very similar to a materialized view in Oracle. So I'd assume that an indexed view can be quite fast even if the underlying tables don't have any indexes defined.|||Never, ever had to create an indexed view

BOL

In SQL Server 2000, indexes also can be created on computed columns and views. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.

The UNIQUE or PRIMARY KEY may contain a computed column as long as it satisfies all conditions for indexing. Specifically, the computed column must be deterministic, precise, and must not contain text, ntext, or image columns. For more information about determinism, see Deterministic and Nondeterministic Functions.

Creation of an index on a computed column or view may cause the failure of an INSERT or UPDATE operation that previously worked. Such a failure may take place when the computed column results in arithmetic error. For example, although computed column c in the following table will result in an arithmetic error, the INSERT statement will work:

CREATE TABLE t1 (a int, b int, c AS a/b)
GO
INSERT INTO t1 VALUES ('1', '0')
GO

If, instead, after creating the table, you create an index on computed column c, the same INSERT statement now will fail.

CREATE TABLE t1 (a int, b int, c AS a/b)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
GO
INSERT INTO t1 VALUES ('1', '0')
GO

The result of a query using an index on a view defined with numeric or float expressions may be different from a similar query that does not use the index on the view. This difference may be the result of rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

To prevent SQL Server from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Also, setting any of the listed options incorrectly will prevent the optimizer from using the indexes on the views. For more information about the OPTION (EXPAND VIEWS) hint, see SELECT.

Restrictions on indexed views
The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords. It cannot have a subquery.

The SELECT list may not include asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(<expression>), computed columns from the base tables, and scalar aggregates.

Nonaggregate SELECT lists cannot have expressions. Aggregate SELECT list (queries that contain GROUP BY) may include SUM and COUNT_BIG(<expression>); it must contain COUNT_BIG(*). Other aggregate functions (MIN, MAX, STDEV,...) are not allowed.

Complex aggregation using AVG cannot participate in the SELECT list of the indexed view. However, if a query uses such aggregation, the optimizer is capable of using this indexed view to substitute AVG with a combination of simple aggregates SUM and COUNT_BIG.

A column resulting from an expression that either evaluates to a float data type or uses float expressions for its evaluation cannot be a key of an index in an indexed view or on a computed column in a table. Such columns are called nonprecise. Use the COLUMNPROPERTY function to determine if a particular computed column or a column in a view is precise.

Indexed views are subject to these additional restrictions:

The creator of the index must own the tables. All tables, the view, and the index, must be created in the same database.

The SELECT statement defining the indexed view may not contain views, rowset functions, inline functions, or derived tables. The same physical table may occur only once in the statement.

In any joined tables, no OUTER JOIN operations are allowed.

No subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.

If the view definition contains a GROUP BY clause, all grouping columns as well as the COUNT_BIG(*) expression must appear in the view's SELECT list. Also, these columns must be the only columns in the CREATE UNIQUE CLUSTERED INDEX clause.
The body of the definition of a view that can be indexed must be deterministic and precise, similar to the requirements on indexes on computed columns. See Creating Indexes on Computed Columns.

Permissions|||I have, occasionally. Its a handy way to implement unique constraints on nullable columns.|||I have, occasionally. Its a handy way to implement unique constraints on nullable columns.

ummmmmmmmm

what kind of constraints that can't be done at the table level?|||Column values must be unique or null. Allow multiple NULLs.|||it's the schema binding stuff i always found awkward and led me to shun indexed views.|||Allow multiple NULLs.

On a Unique INDEX?

You sure?

Post some sample code

Index on View

Is it possilbe to create an index on a view using a linked server table? I am using openquery and I know I can't use rowset functions if I want to index a view.

I just didn't know if there was any other way to access the linked server data in order to create an index.

thanks!!From the BOL:

The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

The view must not reference any other views, only base tables.

All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

My emphasis added. There are additional rules. Your mileage may vary. These actors are professionals; please do not try this at home. Warranty valid for a limited time only please consult your users manual for additional information and any other additional "fine print" as others may see fit to add.

Regards,

hmscott|||thanks for the quick reply and I apologize cause I am a rookie at this...here is what I have done per a previous post:

set to on:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YEILDS_NULL
QUOTED_IDENTIFIERS

Set to off:
NUMERIC_ROUNDABORT

Then I ran my create one table view (from an mysql ODBC linked server) with SCHEMABINDING:

CREATE VIEW mysql_dnc with SCHEMABINDING
AS
select phone from openquery(mysql_dnc,
'select phonefrom DNC')

this doesn't work because of the following issue:
Server: Msg 1054, Level 15, State 3, Procedure mysql_dnc, Line 5
Syntax 'Openrowset' is not allowed in schema-bound objects.

my question is can I access the linked server without using openquery so I can then create an index on it?

thanks much!!!sql

index on view

If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
The following link has some useful information on improving performance using indexed views:
http://msdn.microsoft.com/library/de...exedviews1.asp
However, to be specific to your scenario, you might consider running a workload through Index Tuning Wizard to see whether it suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:

> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA
|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
(Please reply only to the newsgroup)
|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.

index on view

If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The following link has some useful information on improving performance =using indexed views:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/dnsql2=
k/html/indexedviews1.asp
However, to be specific to your scenario, you might consider running a =workload through Index Tuning Wizard to see whether it suggests that you =create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message =news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance is slow, should we create indexes on views? What kind of indexes to create?
Thanks.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The following link has some useful information on =improving performance using indexed views:
http://msdn.microsoft.com/library/defau=lt.asp?url=3D/library/en-us/dnsql2k/html/indexedviews1.asp
However, to be specific to your scenario, you might =consider running a workload through Index Tuning Wizard to see whether it =suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" wrote in message news:143df01c444cd$=f6355010$a301280a@.phx.gbl...If an application uses a lot of views and the performance is slow, =should we create indexes on views? What kind of indexes to create?Thanks.

---

--=_NextPart_000_0008_01C44498.B5850D10--|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
--
(Please reply only to the newsgroup)|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.

Index on Computed column or Indexed View

I have a large nvarchar(2000) that need to be queried on often based on a
subset of the data (first 50 characters). The application creating and using
the data cannot be modified to capture a short and long column... I was
wondering if creating a computed column with the formula being
Left(longcolumn, 50) and creating an index based on this column could be a
good option? Or would it be preferable to create an indexed view?
Any other suggestion are welcomed
Thank you for your helpI would go with a computed column to start with.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> I have a large nvarchar(2000) that need to be queried on often based on a
> subset of the data (first 50 characters). The application creating and
using
> the data cannot be modified to capture a short and long column... I was
> wondering if creating a computed column with the formula being
> Left(longcolumn, 50) and creating an index based on this column could be a
> good option? Or would it be preferable to create an indexed view?
> Any other suggestion are welcomed
> Thank you for your help
>|||Thank you for help. It confirms my tests that I performed last night.
"Narayana Vyas Kondreddi" wrote:

> I would go with a computed column to start with.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in messag
e
> news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> using
>
>

index on a view

Hello,
I have a table (TAB) and A View with alias (VIEW)
Table cod varchar 3
descr carchar 60
my view cod alias COd1
descr alias DES
Now i need a index on view with key COD1
I can't create it.
Can you help me
Carlo
On Thu, 17 Feb 2005 15:48:06 GMT, cmarano wrote:

>Hello,
>I have a table (TAB) and A View with alias (VIEW)
>Table cod varchar 3
> descr carchar 60
>
>my view cod alias COd1
> descr alias DES
>Now i need a index on view with key COD1
>I can't create it.
>Can you help me
Hi Carlo,
I think I can help you, but first I need to get a better picture of what
you're trying to achieve.
What I read from your message is that you have a view that is simply the
same as your table, but with different column names, and that you are now
trying to index that view. I hope that I have misread you, though, as this
would simply result in the same data redundantly being stored at two
different locations in the database.
The best way to clarify your problem is to post:
* Actual table structure, as CREATE TABLE statements - please include all
constraints and all properties (see www.aspfaq.com/5006)
* Some rows of illustrative sample data to give me an idea of the kind of
data you're handling (posted as INSERT statements)
* The CREATE VIEW statement used to create the view you want to index
* The reason for wanting to index your view (in other words: what are you
hoping to achieve)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello,
I have two applications !
In one i have a tabel : TAB with 2 fields cod and descr , length 3 and 60
( varchar)
In the other application i have to read e write in this table , but i have
different field name :
cod1 and des (alias). I repair to this with a view .
Now i have to chain this table with key (cod1) this is the problem.
the create statment :
CREATE TABLE [CO_ZONE] (
[cod_zona] [varchar] (3) COLLATE Latin1_General_BIN NOT NULL ,
[des_zona] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[dat_obsoleto] [datetime] NULL ,
[prg_net] [timestamp] NOT NULL ,
CONSTRAINT [XPKCO_ZONE] PRIMARY KEY CLUSTERED
(
[cod_zona]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Table contents : IT Italy
US U.S.A.
.. ......
Creat view statments:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.TABCE05F
WITH SCHEMABINDING
AS
SELECT TOP 100 PERCENT cod_zona AS T5COAR, des_zona AS T5DEAR
FROM dbo.CO_ZONE
ORDER BY cod_zona
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Best regards, Carlo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> ha scritto nel messaggio
news:h6t911p41hpuus9vmnts2vfd073a7v1ffk@.4ax.com...
> On Thu, 17 Feb 2005 15:48:06 GMT, cmarano wrote:
>
> Hi Carlo,
> I think I can help you, but first I need to get a better picture of what
> you're trying to achieve.
> What I read from your message is that you have a view that is simply the
> same as your table, but with different column names, and that you are now
> trying to index that view. I hope that I have misread you, though, as this
> would simply result in the same data redundantly being stored at two
> different locations in the database.
> The best way to clarify your problem is to post:
> * Actual table structure, as CREATE TABLE statements - please include all
> constraints and all properties (see www.aspfaq.com/5006)
> * Some rows of illustrative sample data to give me an idea of the kind of
> data you're handling (posted as INSERT statements)
> * The CREATE VIEW statement used to create the view you want to index
> * The reason for wanting to index your view (in other words: what are you
> hoping to achieve)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 18 Feb 2005 14:58:31 GMT, cmarano wrote:

>Hello,
>I have two applications !
>In one i have a tabel : TAB with 2 fields cod and descr , length 3 and 60
>( varchar)
>In the other application i have to read e write in this table , but i have
>different field name :
>cod1 and des (alias). I repair to this with a view .
>Now i have to chain this table with key (cod1) this is the problem.
(snip)
Hi Carlo,
Thanks for posting your explanation and the CREATE TABLE and CREATE VIEW
statements.
In this case, there is no reason to index the view. In fact: if you do,
SQL Server will have to maintain a copy of all data in the table and
change that copy whenever the data in the table changes. You double the
storage space required, give SQL Server extra work to do on updates and
you gain nothing from it.
To be able to use your other application without changing it, you just
need a normal (non-indexed) view. In queries, SQL Server will substitute
the view's name with the view's definition (and since that is simple, it
comes at no extra cost). Similar, updates to the view will be translated
back into updates to the table - and again, at a performance price you
won't notice, since it's a very simple one-on-one translation.
You also don't need the TOP 100 PERCENT in the view (you'll get all rows
by default - only use TOP if you want less than all rows) and you should
remove the ORDER BY (it's not guaranteed to work anyway - it'll be only
used to determine which rows are or are not in the TOP 100 PERCENT, not to
determine the ordering of rows returned by the view).
CREATE VIEW dbo.TABCE05F
AS
SELECT cod_zona AS T5COAR, des_zona AS T5DEAR
FROM dbo.CO_ZONE
GO
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Index On a View

Does anyone know of a trick that allows you to apply an index on the following view? I would like to index the license_id column. When I try to apply a unique clustered index I get the following error - Index on view 'dbo.v_lic_status' cannot be created because function 'getdate' yields nondeterministic results.

VIEW DDL:

SELECT license_id,
CASE
WHEN (term_date < GETDATE()) THEN 'Terminated'
WHEN (suspend_date < GETDATE()) THEN 'Suspended'
WHEN (expiration_date < GETDATE()) THEN 'Expired'
WHEN (effective_date < GETDATE()) THEN 'Active'
ELSE 'Pending'
END AS lic_status
FROM dbo.license

INDEX DDL:

create unique clustered index ux_v_lic_status_01 on dbo.v_lic_status (
license_id
)
with
fillfactor= 90
goYou have to create the view with schemabinding and both ANSI_NULLS and QUOTED_IDENTIFIER option must be on. For detailed information check BOL under the subject 'Creating an Indexed View'.|||Hmm...I tried that and still doesn't work. Here's the create statement for the view.

/*================================================= =============*/
/* View: v_lic_status */
/*================================================= =============*/
create view dbo.v_lic_status with schemabinding as
SELECT license_id,
CASE
WHEN (term_date < GETDATE()) THEN 'Terminated'
WHEN (suspend_date < GETDATE()) THEN 'Suspended'
WHEN (expiration_date < GETDATE()) THEN 'Expired'
WHEN (effective_date < GETDATE()) THEN 'Active'
ELSE 'Pending'
END AS lic_status
FROM dbo.license
go|||Books OnLine (article name:Creating an Indexed View) has this to say under Requirements for an Indexed View:

All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports if a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.

As for how to work around this, I am not too sure. Looks like you would have to put a flag on a base table for when something goes expired, terminated, etc..|||Thanks for the reply, that's what I was afraid of. Didn't know if someone knew a trick or shortcut around this. It sux since I have an 'else' as a catch all in the case statement sql server doesn't consider this deterministic.|||An indexed view actually creates a persistent copy of the data in a virtual table. The data in the virtual table is automatically updated as data in the underlying tables is modified. So you see, creating an indexed view using a statement that references GETDATE would need to be updated continuously, hence it is not allowed.

I hope this help you understand some of the restrictions places upon indexed views.

Index Internals - Last time index was rebuilt?

I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt?

Thanks much.

Perhaps the information you seek is available here:

SELECT *

FROM sys.dm_index_usage_stats

|||

Arnie,

Thank you for your reply. I'm afraid that I have not seen the information I'm after in any of the documented columns of the dmvs. I've combed through the dmvs related to indexes and have been unable to find it. That's why I'm wondering if this metadata is stored elsewhere, and if so, where.

If, when looking at the dmv you mentioned, you saw a particular column you think contains the information I'm after, please let me know what it is.

Thanks!

Index in view

I 've 5 tables not indexed(must).
Now i create 5 views for these with indexing.
creating index only on views are possible, if so it can increase the
performance od query. Can u give me the soln ?Read about indexed views in Books Online. Note that only Enterprise Edition
will use such indexes
automatically. I would reconsider why you cannot create indexes on the base
tables. Why is that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JJFreds" <JJFreds@.discussions.microsoft.com> wrote in message
news:9A6FA327-D8F1-4A85-99F4-E521EEA400D8@.microsoft.com...
>I 've 5 tables not indexed(must).
> Now i create 5 views for these with indexing.
> creating index only on views are possible, if so it can increase the
> performance od query. Can u give me the soln ?

Index hints in a view.

Hi
SQL Server 2000 SP4
I am using view in my stored procedure. The view gets data from only one
table. I can not use table directly due to some reasons. I would like to add
an index hint to this table. Is View definition the only place for that? I
tried to add hint index to the view directly in the FROM clause of my SP but
i got a message that the hint is ignored.
Thanks in advance.For the view, you can do index hints on indexed views only. Otherwise, to
specify a specific index for a table in the view, you will need to modify th
e
view and supply the table hint for the index you want to use for the require
d
table using the WITH INDEX () hint.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Alexander Korol" wrote:

> Hi
> SQL Server 2000 SP4
> I am using view in my stored procedure. The view gets data from only one
> table. I can not use table directly due to some reasons. I would like to a
dd
> an index hint to this table. Is View definition the only place for that? I
> tried to add hint index to the view directly in the FROM clause of my SP b
ut
> i got a message that the hint is ignored.
> Thanks in advance.

Index and view

I have two very big tables t1 and t2, and a view:
alter view v1 as
select ... t1.c, ...
from t1 full outer join t2 on t1.a=t2.a and t1.b=t2.b
-- index exists for (a, b) and c and c is unique key of t1.
and i found it takes much longer time to execute
select ...
from v1
where c = 'xxxx'
than
select ... t1.c, ...
from t1 full outer join t2 on t1.a=t2.a and t1.b=t2.b
where c= 'xxxx'
Executive plan also shows SQL always do the joining, which takes a lot of
time, in the view first.
Anyway to force SQL Server to consider the where clause in the executive
plan of the one using the view? (I cannot write the "where c='xxxx'" into
view since it will be variable.)sorry the definition of the view is
alter view v1 as
select ... isnull(t1.c, t2.c) as c, ...
from t1 full outer join t2 on t1.a=t2.a and t1.b=t2.b
"nick" wrote:

> I have two very big tables t1 and t2, and a view:
> alter view v1 as
> select ... t1.c, ...
> from t1 full outer join t2 on t1.a=t2.a and t1.b=t2.b
> -- index exists for (a, b) and c and c is unique key of t1.
> and i found it takes much longer time to execute
> select ...
> from v1
> where c = 'xxxx'
> than
> select ... t1.c, ...
> from t1 full outer join t2 on t1.a=t2.a and t1.b=t2.b
> where c= 'xxxx'
> Executive plan also shows SQL always do the joining, which takes a lot of
> time, in the view first.
> Anyway to force SQL Server to consider the where clause in the executive
> plan of the one using the view? (I cannot write the "where c='xxxx'" into
> view since it will be variable.)|||nick
Do you have any indexes defined on the tables?
"nick" <nick@.discussions.microsoft.com> wrote in message
news:5141C943-B03F-4C95-87D3-4F278BBDF0AE@.microsoft.com...
>I have two very big tables t1 and t2, and a view:
> alter view v1 as
> select ... t1.c, ...
> from t1 full outer join t2 on t1.a=t2.a and t1.b=t2.b
> -- index exists for (a, b) and c and c is unique key of t1.
> and i found it takes much longer time to execute
> select ...
> from v1
> where c = 'xxxx'
> than
> select ... t1.c, ...
> from t1 full outer join t2 on t1.a=t2.a and t1.b=t2.b
> where c= 'xxxx'
> Executive plan also shows SQL always do the joining, which takes a lot of
> time, in the view first.
> Anyway to force SQL Server to consider the where clause in the executive
> plan of the one using the view? (I cannot write the "where c='xxxx'" into
> view since it will be variable.)

index and constraints

Dear,
I export the SQL server 2000 database setting
(table,sp,view,user..etc) to script file then import the
script(setting) to another database.
After import the setting to database, the index and
constraints was missing on the table.
How can i import the index and constriants to new
database ? (i have choice the index and constraints option
when i export the database setting)
Many Thanks
JohnHi John
If you've chosen the option to include them at the end of the script objects
wizard then you've done the right thing & it should have worked.
Another option is to use the Import / Export wizard, which also has an
option to copy objects along with indexes / constraints etc. You can choose
to include / exclude data using the import / export wizard.
Regards,
Greg Linwood
SQL Server MVP
"John" <acos3ltd1@.hotmail.com> wrote in message
news:020601c3c511$49c837c0$a401280a@.phx.gbl...
> Dear,
> I export the SQL server 2000 database setting
> (table,sp,view,user..etc) to script file then import the
> script(setting) to another database.
> After import the setting to database, the index and
> constraints was missing on the table.
> How can i import the index and constriants to new
> database ? (i have choice the index and constraints option
> when i export the database setting)
> Many Thanks
> John|||you could also try another tool. Try "DB Ghost" at
www.dbghost.com
>--Original Message--
>Dear,
>I export the SQL server 2000 database setting
>(table,sp,view,user..etc) to script file then import the
>script(setting) to another database.
>After import the setting to database, the index and
>constraints was missing on the table.
>How can i import the index and constriants to new
>database ? (i have choice the index and constraints
option
>when i export the database setting)
>Many Thanks
>John
>.
>

Index a view or a table

I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

The table is also involved in frequent non-simple select statements. It currently has about a million rows.

Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

ThanksOriginally posted by mattkrevs
I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

The table is also involved in frequent non-simple select statements. It currently has about a million rows.

Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

Thanks

If you use the six in your join and where clauses but you still need all 15, then you should go with indexes on the table. Otherwise, look at an indexed view.|||http://www.sqlteam.com/item.asp?ItemID=1015 for more information.|||Originally posted by Satya
http://www.sqlteam.com/item.asp?ItemID=1015 for more information.

nice article.
thanks|||I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset.

Plus, an indexed view will slow down your inserts.

Just add indexes to your table. Composite indexes may help specific querys as well. Have you run a showplan against your queries to see where delays are occuring?|||Originally posted by blindman
I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset. you don't see, blindman, that's right (pun intended). do you even know the rules of creating indexed views? aggregation? you can't aggregate an indexed view with anything other than count_big(*)!!! do you even know what you are talking about before making such suggestions?
Originally posted by blindman
Plus, an indexed view will slow down your inserts.
man, you just spit out guessing after guessing. how do you figure? so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?

and at the same time you're giving suggestions to "just add indexes to the table"?

i love this forum, unsupported ambitions are all over the place :rolleyes:|||holy flame war, batman

look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did

please, keep your personal bitterness out of your posts

refute the facts but please keep the vitriol to yourself

when you insult someone out of the blue like that, it makes you look like an idiot|||I thought that was "an ASS out of "U"and "ME"" ?

:D :D :D :D :D|||Originally posted by r937
holy flame war, batman

look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did

please, keep your personal bitterness out of your posts

refute the facts but please keep the vitriol to yourself

when you insult someone out of the blue like that, it makes you look like an idiot before jumping on me you should read his posts|||Originally posted by ms_sql_dba
before jumping on me you should read his posts I can't speak for anyone else, but I already did.

I'm with r937, dispute the facts if you will, but I'm not interested in what appears to be a vicious response to a civil posting. I don't care whether the original posting was technically accurate or not, that isn't relevant to this observation. I don't think that your response was appropriate.

-PatP|||Originally posted by ms_sql_dba
before jumping on me you should read his posts

i did, there's only one post of his in this thread, and it was friendly

if you are talking about other threads, they do not matter to this one

this thread will be found independently by people unaware of your own personal vendetta

please, be more civil|||ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:

using indexed views is more efficient than creating indexes on the underlying tables.

does this look better to you all? ;)|||Originally posted by ms_sql_dba
ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:

using indexed views is more efficient than creating indexes on the underlying tables.

does this look better to you all? ;) Way more gooder, yet even!

Now, all that one of you two needs to do is to come up with something (an URL, a reference to readily available printed material, etc) to support your opinions, then we'll all have something to discuss! ;)

Pretty quick I need to prepare to pig out! You guys go on and debate stuff without me, I'll join in later if I can waddle to the tube!

-PatP|||Let' start at the top...

"you can't aggregate an indexed view with anything other than count_big(*)!!!"
Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.

"do you even know what you are talking about before making such suggestions?"
Yes.

"so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
"You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."

"and at the same time you're giving suggestions to "just add indexes to the table"?"
Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.

"using indexed views is more efficient than creating indexes on the underlying tables."
Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.

'Nuff said.|||100 human (hu-man)
200 noun.
300 A carbon based device that allows the user to quickly and
400 efficiently repeat the same mistake 50,000 times.
500 goto 100|||Looks like spaghetti code to me!|||Originally posted by blindman
Let' start at the top...

"you can't aggregate an indexed view with anything other than count_big(*)!!!"
Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.

"do you even know what you are talking about before making such suggestions?"
Yes.

"so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
"You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."

"and at the same time you're giving suggestions to "just add indexes to the table"?"
Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.

"using indexed views is more efficient than creating indexes on the underlying tables."
Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.

'Nuff said. indexed views have just been introduced. what years?|||can you guys smell smoke? faintly reminicsent of when i used to play with matches as a kid.
you know that feeling? when your doing something that you know you shouldnt. like fighting|||"indexed views have just been introduced. what years?"

My post was:
"I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing."

Please read my posts more carefully before criticising them.|||i wasn't. and your answer is very christomatic. of course indexes speed up queries (if properly built) do you read my posts carefully?|||Originally posted by Ruprect
can you guys smell smoke? faintly reminicsent of when i used to play with matches as a kid.
you know that feeling? when your doing something that you know you shouldnt. like fighting At least from what I've seen in this thread, blindman has been both civil and technically correct. I'm willing to wait for ms_sql_dba to take a shot at validating their claims, but so far I can't fault blindman.

Maybe I'm missing something (I've been prone to do that lately), but I see this as a pretty one sided screaming match, with blindman presenting the civilized side. Please let me know if you disagree.

-PatP|||But I do like the word "christomatic". It's not on dictionary.com, but it should be! Very appropriate for the holiday!|||Originally posted by Pat Phelan
At least from what I've seen in this thread, blindman has been both civil and technically correct. I'm willing to wait for ms_sql_dba to take a shot at validating their claims, but so far I can't fault blindman.

Maybe I'm missing something (I've been prone to do that lately), but I see this as a pretty one sided screaming match, with blindman presenting the civilized side. Please let me know if you disagree.

-PatP ok, mr. judge, so you choose to ignore the technical stuff coming from me, and acknowledge only blindman's answers like "I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing." wow, it only takes that long to figure this one out :D

but this is why indexes exist, to improve performance of queries. please read carefully what the topic is all about, - indexed views vs. indexes on underlying tables (at least that's what it bottled down to)

...and thanks for stopping by. your arrival means it's time to go back to real life :p|||Originally posted by blindman
Looks like spaghetti code to me!

I'm sorry. Is this better? :)

CREATE TABLE thread(
thread_id VARCHAR(55),
thread_certain_participant_description VARCHAR(55),
thread_certain_participant_part_of_speech VARCHAR(55),
thread_certain_participant_definition VARCHAR(255))

GO

INSERT thread(
thread_id,
thread_certain_participant_description,
thread_certain_participant_part_of_speech,
thread_certain_participant_definition)

SELECT
'index a view or a table',
'human (hu-man)',
'noun',
'A carbon based device that allows the user to quickly and efficiently repeat the same mistake 50,000 times.'

GO

DECLARE @.int_counter INT

SELECT @.int_counter = 1

WHILE @.int_counter <= 50000
BEGIN

SELECT
thread_id,
thread_certain_participant_description,
thread_certain_participant_part_of_speech,
thread_certain_participant_definition
FROM
thread
WHERE
thread_id = 'index a view or a table'

SELECT @.int_counter = @.int_counter + 1
END

GO|||Originally posted by ms_sql_dba
ok, mr. judge, so you choose to ignore the technical stuff coming from me, and acknowledge only blindman's answers like "I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing." wow, it only takes that long to figure this one out :D

but this is why indexes exist, to improve performance of queries. please read carefully what the topic is all about, - indexed views vs. indexes on underlying tables (at least that's what it bottled down to)

...and thanks for stopping by. your arrival means it's time to go back to real life :p I contribute here because I like to help others when I've got a minute or two to spare. I try to behave civilly and professionally.

While you've offered some good technical content in other threads, I haven't seen that you've offered anything I value in this thread. I'm not sure what's wrong, whether it's my percepcion or your behavior.

Blindman has done a good job of defending his position. Your behavior struck me as poor, and your technical support of your position was minimal at best. Do you have anything technically useful to contribute?

-PatP|||when someone refuses to be civil, just add them to your ignore list (http://www.dbforums.com/misc.php?action=faq&page=1#buddy)

"Ignore lists are used for those people whose messages you wish not to read. By adding someone to your ignore list, those messages posted by these individuals will be hidden when you read a thread."

sounds tailor-made for certain people, don't it|||Except that then you may end up posting something somebody already has posted. I don't have anybody on my ignore list because it would be like having a conversation with four other people and only be able to hear one of them.

Do you have anybody on your ignore list, Rudy?

Rudy?

Hello? Can you hear me?

.
.
.

Hmmm.... :(|||don't it?

gotta be the Queens English...:D

Actually I find it all amusing...

I know that the net provides a huge amount of anonymity...

Makes you wonder how people actually behave in the real personal lives...

And yes it's all about the technology (if you can call it that)

MOO, of course...|||Originally posted by blindman
Except that then you may end up posting something somebody already has posted.
yeah, but if you do, your post will be seen by everybody else as coming from a reputable source, whereas the post from the ignored person, well, you wouldn't have put her on the ignore list for no good reason...|||Originally posted by blindman
Except that then you may end up posting something somebody already has posted.

Happens anyway, doesn't it?

Oh...[sniped]|||Is it just me .. or tempers on dbForums seem to be on a short fuse ?|||Like I said...I find it amusing...

Like it really matters...

lot of hard cash on the line.....|||Thats why I usually never get into Flame war ... (http://www.dbforums.com/showthread.php?threadid=972040)|||Ah yes, good old Sundial and his "Bob Cratchet" theory of employee loyalty. What the Dickens was he thinking?|||Hey...any job in this market is a good job...

And I can't believe that thread is 6 months old already...

Damn Mondays...|||Originally posted by Brett Kaiser
Damn Mondays... I refuse to let the "Monday-ness" of a day interfere with my enjoyment of the day! Somebody (John Ruskin?) once said that: "Monday is one H#)) of a way to spend one seventh of your life." I think that he "missed the boat" on this one, and that Monday really IS one heck of a way to spend one seventh of your life!

-PatP|||Originally posted by Pat Phelan
I refuse to let the "Monday-ness" of a day interfere with my enjoyment of the day! Somebody (John Ruskin?) once said that: "Monday is one H#)) of a way to spend one seventh of your life." I think that he "missed the boat" on this one, and that Monday really IS one heck of a way to spend one seventh of your life!

-PatP

Well I guess it's better than have 1/7 less....|||or 2/7 less

which is me, usually friday afternoon to sunday afternoon is a huge fog|||That's cause the leafs split at home...

Off to Ottowa...

Damn devils....|||on the contrary, i don't even know who's playing, i am so not a hockey fan|||picking myself off the floor

I though it was part of being allowed to live in Canada

Are you a transplant?

Has it stopped snowing yet?|||transplant? yes

the kid in the lederhosen is me in germany

however, i have lived in and around toronto for over 50 years

i useta, as we say, watch hockey when it was hockey, back when there were 6 teams...

last snow here was a month or so ago

today it's 9C and sunny|||You should post a more recent photo of you in Lederhosen.|||the only pics i'm willing to share are on my personal site ;)

der lederhosen guy is cute, isn't he?

i couldn't think of changing it, it's like a brand

did you buy the new coke when it came out?

neither did i