Creation of aligned partitioned indexes

Hi All,

In the manual I find the following comment for creating indexes.

"If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table."

We are creating some dimensional models in SQL Server with about 100M rows in the largest fact tables.

What I have created are base tables which were on 'primary' and then created a clustered index over a partitioning scheme. I did this as I figured the data will go into the clustered index in any case.

However, when I then create indexes on these tables they do not look like they are petitioned....at least not as far as I can tell.

Q1. Is there some way to tell if the index was partitioned properly?

Q2. If the index is not partitioned is there any simply way to alter the table to the partitioning scheme? My reading of the manual tells me I have to unload, truncate, alter and then reload the table. Is there a better way?

Thanks in advance for your assistance.

Best Regards

Peter

Hi All,

since I did the work to figure this out I thought I would share.....

If you go into the sql studio and look at the properties of the index you can see if it is partitions and if the partitioning column is there.

There is no way I have found to alter the table to the partitioning scheme. However, if you create the index and make the partitioning column the first element of the index and use the partitioning scheme for the table it will have exactly the same effect as partitioning the table and then providing no on clause in the create index.

So you have a choice of specifiying the on clause in the index create or letting sql server default and do this for you......I guess it is a personal opinion as to whether one wants explicit control or default control over creating indexes.....also, even if the table is partitioned if you use an on clause in the index you can partition the index and place it onto the file groups you want to....

Oh the joy of setting up partitioned indexes etc.. :-)

Peter

|||

And now I have one more question on this topic....

In oracle it is possible to partition indexes and have them be 'local' in that they are only related to the partition to which they point.

The reason for doing this is when loading the data the local bit mapped indexes are dropped and once the days transactions/summaries are loaded the local bit mapped indexes can be put back...so you are only every rebuilding the indexes for the data that changed and it is only one partition that you are pointing to for that bit map rebuild...this is is really handy because of the cost of index rebuilds. So you can easily put a few hundred million rows into a partition with little difficulty when doing it this way...

I wondered if SQL Sever had the abiity yet to remove just the portion of a partitioned index pointing to the current partition to have rows inserted/updated and then allowed that index to be rebuilt or updated after updates table place...What I want to avoid is the dropping and recreation of all the query indexes on the table because that would rebuild the index for all partitions and not just the one...

Can this be done? Can anyone point me to the relevant documentation on how to do this?

Thanks

Peter

|||

Hi All,

as we move forward with testing we had one other question we thought someone here might have seen before we test it out ourselves.

We have created aligned partitioning indexes for each table with each index containing the partitioning column and one dimension table key.

We have placed these indexes onto the same partitioning scheme as the table and therefore the same file groups and the table inside the clustered index.

We suspect that performance will be improved if we place the indexes onto their own file groups....but we wonder 'by how much' and 'is it worth it'.

Has anyone else out there performed testing on partitioned tables and aligned indexes both on the same file groups and on different file groups and have an idea of the difference in performance?

Thanks

Peter

Creating Unique Indexes on Partition table..

In partition tables, SQL Server forces to include the Partition column as part of unique index/unique constraint or primary key. Is there a way to over-ride this option.

We have a table, which is partitioned on a Calculated Column (generated using custom logic). Also we need to enforce uniqueness based on few other columns. But SQL Server does not allow indexes/contraints on these columns without including the partition column.

Thanks for your help.

Well, you can create a non-aligned unique index on a partitioned table, but doing that will in essence not allow meta-data-only partition switching. Depending on your needs, that may be ok or not. You could also use a DML trigger to enforce uniqueness instead of a unique index. Or, if your environment allows for the use of them, you could always create an indexed view on the table and create a unique index on that view that will in essence enforce the uniqueness on the corresponding table as well.

For more information, see the following topic in SQL 2005 BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5af648b1-8454-4c17-a47e-f9656572440b.htm

HTH,

Creating temp table in SP - what about indexes ?

I am creating temporary tables in a Stored Procedure, to create paging for a
web frontend.
I do this by creating a temp table with a "identity" field, then copying all
relevant data into the temp table, and then in the end I select out the
actual "page" from the total temp table, ie. from record ID 100 to 150.
During this process, I was wondering if my temp table should have an index,
for optimal performance ?
If my temp table has ie. 5000 records, and I want to select and return only
records from 3500 to 3550, I select with a "where clause" specifying only
records from 3500 to 3550, using the identity field, which automatically
works as a "record counter" for my totalt recordset.
But should I create an index on the identity field, before filling the temp
table with records, and then selecting the actual page to return ?
I mean, if I query using a where clause specifying an column without an
index, wouldnt this create table scans ?
-
Regards,
Tony G.I would think creating the clustered index before filling the table would
give you optimal performance as the data is ordered on insert.
Test each scenario and then you'll be satisfied that you have the best metho
d.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Tony Godt" wrote:

> I am creating temporary tables in a Stored Procedure, to create paging for
a
> web frontend.
> I do this by creating a temp table with a "identity" field, then copying a
ll
> relevant data into the temp table, and then in the end I select out the
> actual "page" from the total temp table, ie. from record ID 100 to 150.
> During this process, I was wondering if my temp table should have an index
,
> for optimal performance ?
> If my temp table has ie. 5000 records, and I want to select and return onl
y
> records from 3500 to 3550, I select with a "where clause" specifying only
> records from 3500 to 3550, using the identity field, which automatically
> works as a "record counter" for my totalt recordset.
> But should I create an index on the identity field, before filling the tem
p
> table with records, and then selecting the actual page to return ?
> I mean, if I query using a where clause specifying an column without an
> index, wouldnt this create table scans ?
>
> -
> Regards,
> Tony G.|||> I mean, if I query using a where clause specifying an column without an
> index, wouldn't this create table scans ?
Correct.
I'm a bit through. You say that you create a temp table in the proc
. But how do you re-use
this temp table? I hope that you don't create the temp table, populate it an
d then select from it
each time a user want to display a page?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Godt" <TonyGodt@.discussions.microsoft.com> wrote in message
news:1EFE7BC9-C4A4-4902-84C3-0B79B78A58FA@.microsoft.com...
>I am creating temporary tables in a Stored Procedure, to create paging for
a
> web frontend.
> I do this by creating a temp table with a "identity" field, then copying a
ll
> relevant data into the temp table, and then in the end I select out the
> actual "page" from the total temp table, ie. from record ID 100 to 150.
> During this process, I was wondering if my temp table should have an index
,
> for optimal performance ?
> If my temp table has ie. 5000 records, and I want to select and return onl
y
> records from 3500 to 3550, I select with a "where clause" specifying only
> records from 3500 to 3550, using the identity field, which automatically
> works as a "record counter" for my totalt recordset.
> But should I create an index on the identity field, before filling the tem
p
> table with records, and then selecting the actual page to return ?
> I mean, if I query using a where clause specifying an column without an
> index, wouldnt this create table scans ?
>
> -
> Regards,
> Tony G.

Creating Table Trigger to maintain FuzzyLookup Index

Hi,

I've created initial indexes for my table for the fuzzylookup process. I clicked on "Maintained index" but I don't see any triggers created on the reference table.

Do I create the triggers to maintain indexes myself?

Does anybody know how to create these triggers in terms of schema_name, Data_Modification_Statements etc.?

Would it be "Alter index <index name> REBUILD command?

Appreciate the help.

Gulden

Perhaps you don't have permissions to create the triggers? The "indexes" are index tables, not regular indexes. I'm pretty sure you want it to create the triggers itself. I see there is a sp_FuzzyLookupTableMaintenanceInstall procedure in the master database that you might try. The sp_FuzzyLookupTableMaintenanceUnInstall procedure is documented for removing the triggers, so I'd bet the other one creates them.
|||

Thank you for your reply.

I realized that I was pointing to the development database when I created fuzzy lookup indexes.

I re-created them in the staging database and I can see triggers and index files.

But I cannot run any type of maintenance on them.

I tried running sp_FuzzyLookupTableMaintenanceUnInstall sp_FuzzyLookupTableMaintenanceInvoke

But I get the following error on a recently created index.

A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

Now I am concern that in case of an index corruption in the future I won't be able to uninstall them.

Do you think Just deleting indexes and triggers would work?

Thanks,

|||I don't know what sp_FuzzyLookupTableMaintenanceInvoke does. Do you? Does the UnInstall procedure give that error too? If so, it wouldn't seem that you have any choice but to delete the triggers and index manually. The docs warn that if you delete the table before the triggers, any statements against the reference table will fail until the triggers are removed.
|||

Turned out that I don't have permissions to run those stored procs.

My database admin ran them and got successfully uninstall the triggers.

I had to uninstall them because in the managed code it generated

InsertNonMatchedToClientMap Exception:A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.

I think I need to change my database connection strings but we will install in production soon.

So I am back to "Create Index" option on the fuzzy lookup transforms.

Thank you very much for you replies and support....

|||

I receive the following error when selecting the "Maintain Index" option within my SSIS package.

Error: 0xC0202009 at Data Flow Task, Fuzzy Lookup [645]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException: Unable to parse token counts in Error Tolerant Index metadata. The index is probably corrupt.

I am running as "sa" but still don't seem to have permission to execute the SPs. I receive the the following error when executing sp_FuzzyLookupTableMaintenanceInstall from within SQL Management Studio:

Msg 6522, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInstall, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.RaiseErrorId(SqlCommand cmd, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.ReportErrors(SqlCommand cmd, ExceptionType Type, String ErrorMessage, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity, SqlErrorCollection errors)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c)

.

Any ideas?

Creating Table Trigger to maintain FuzzyLookup Index

Hi,

I've created initial indexes for my table for the fuzzylookup process. I clicked on "Maintained index" but I don't see any triggers created on the reference table.

Do I create the triggers to maintain indexes myself?

Does anybody know how to create these triggers in terms of schema_name, Data_Modification_Statements etc.?

Would it be "Alter index <index name> REBUILD command?

Appreciate the help.

Gulden

Perhaps you don't have permissions to create the triggers? The "indexes" are index tables, not regular indexes. I'm pretty sure you want it to create the triggers itself. I see there is a sp_FuzzyLookupTableMaintenanceInstall procedure in the master database that you might try. The sp_FuzzyLookupTableMaintenanceUnInstall procedure is documented for removing the triggers, so I'd bet the other one creates them.
|||

Thank you for your reply.

I realized that I was pointing to the development database when I created fuzzy lookup indexes.

I re-created them in the staging database and I can see triggers and index files.

But I cannot run any type of maintenance on them.

I tried running sp_FuzzyLookupTableMaintenanceUnInstall sp_FuzzyLookupTableMaintenanceInvoke

But I get the following error on a recently created index.

A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

Now I am concern that in case of an index corruption in the future I won't be able to uninstall them.

Do you think Just deleting indexes and triggers would work?

Thanks,

|||I don't know what sp_FuzzyLookupTableMaintenanceInvoke does. Do you? Does the UnInstall procedure give that error too? If so, it wouldn't seem that you have any choice but to delete the triggers and index manually. The docs warn that if you delete the table before the triggers, any statements against the reference table will fail until the triggers are removed.
|||

Turned out that I don't have permissions to run those stored procs.

My database admin ran them and got successfully uninstall the triggers.

I had to uninstall them because in the managed code it generated

InsertNonMatchedToClientMap Exception:A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.

I think I need to change my database connection strings but we will install in production soon.

So I am back to "Create Index" option on the fuzzy lookup transforms.

Thank you very much for you replies and support....

|||

I receive the following error when selecting the "Maintain Index" option within my SSIS package.

Error: 0xC0202009 at Data Flow Task, Fuzzy Lookup [645]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException: Unable to parse token counts in Error Tolerant Index metadata. The index is probably corrupt.

I am running as "sa" but still don't seem to have permission to execute the SPs. I receive the the following error when executing sp_FuzzyLookupTableMaintenanceInstall from within SQL Management Studio:

Msg 6522, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInstall, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.RaiseErrorId(SqlCommand cmd, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.ReportErrors(SqlCommand cmd, ExceptionType Type, String ErrorMessage, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity, SqlErrorCollection errors)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c)

.

Any ideas?

Creating schemabound views on linked servers

Hi,
I need to create indexes and use index hints on views created
across two servers (in the linked server model). Assuming that it is
possible to do so, I first need to create an unique clustered index on
the view. This in turn mandates that the underlying view is a
schemabound view that takes only a two part name (dbo.objectname).
However, a view created on a linked server has four parts in its name
(linkedserver_name,db_name,owner_nameobj
ect_name). This causes the sql
parser to reject my request to create a schema bound linked server
view. Can anybody tell me if there is a work around for this?
Thanks
/SudhaAn indexed view cannot store data from a table that resides in another datab
ase or another server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<srajagop@.genesyslab.com> wrote in message
news:1163034549.484430.255030@.i42g2000cwa.googlegroups.com...
> Hi,
> I need to create indexes and use index hints on views created
> across two servers (in the linked server model). Assuming that it is
> possible to do so, I first need to create an unique clustered index on
> the view. This in turn mandates that the underlying view is a
> schemabound view that takes only a two part name (dbo.objectname).
> However, a view created on a linked server has four parts in its name
> (linkedserver_name,db_name,owner_nameobj
ect_name). This causes the sql
> parser to reject my request to create a schema bound linked server
> view. Can anybody tell me if there is a work around for this?
> Thanks
> /Sudha
>

Creating schemabound views on linked servers

Hi,
I need to create indexes and use index hints on views created
across two servers (in the linked server model). Assuming that it is
possible to do so, I first need to create an unique clustered index on
the view. This in turn mandates that the underlying view is a
schemabound view that takes only a two part name (dbo.objectname).
However, a view created on a linked server has four parts in its name
(linkedserver_name,db_name,owner_nameobject_name). This causes the sql
parser to reject my request to create a schema bound linked server
view. Can anybody tell me if there is a work around for this?
Thanks
/SudhaAn indexed view cannot store data from a table that resides in another database or another server...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<srajagop@.genesyslab.com> wrote in message
news:1163034549.484430.255030@.i42g2000cwa.googlegroups.com...
> Hi,
> I need to create indexes and use index hints on views created
> across two servers (in the linked server model). Assuming that it is
> possible to do so, I first need to create an unique clustered index on
> the view. This in turn mandates that the underlying view is a
> schemabound view that takes only a two part name (dbo.objectname).
> However, a view created on a linked server has four parts in its name
> (linkedserver_name,db_name,owner_nameobject_name). This causes the sql
> parser to reject my request to create a schema bound linked server
> view. Can anybody tell me if there is a work around for this?
> Thanks
> /Sudha
>

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
WillieWillie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>

Creating new indexes

Hello all,
Just a quickie. If i add new indexes to tables, do I have to reanalyse /
reindex / do anything or is this done automatically by SQL Server? We're
running SQL Server 2000.
Thanks,
JonYes correct, Whenever you Create/Rebuild/Drop n Create a index its
pages are Organized, and there is nothing much you can do on it.
But yes if your senario is highly intensive transactional (Lots of
Inserts n Deletes), Then u maye want to check back on the Index
Fragmentations.
On Jan 22, 1:36 pm, Jon <J...@.discussions.microsoft.com> wrote:
> Hello all,
> Just a quickie. If i add new indexes to tables, do I have to reanalyse /
> reindex / do anything or is this done automatically by SQL Server? We're
> running SQL Server 2000.
> Thanks,
> Jon|||Hello,
If you add a clustered index automatically all the non clustered index
associated with that table will be recreated and its statistics will be
updated. If it is non clustered index
you may need to reindex all the other indexes individually or use DBCC
DBREINDEX('Tablename')
Thanks
Hari
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:F2A8A2B8-C395-49A6-8751-0D9592FF3D38@.microsoft.com...
> Hello all,
> Just a quickie. If i add new indexes to tables, do I have to reanalyse /
> reindex / do anything or is this done automatically by SQL Server? We're
> running SQL Server 2000.
> Thanks,
> Jon

Creating new indexes

Hello all,
Just a quickie. If i add new indexes to tables, do I have to reanalyse /
reindex / do anything or is this done automatically by SQL Server? We're
running SQL Server 2000.
Thanks,
Jon
Yes correct, Whenever you Create/Rebuild/Drop n Create a index its
pages are Organized, and there is nothing much you can do on it.
But yes if your senario is highly intensive transactional (Lots of
Inserts n Deletes), Then u maye want to check back on the Index
Fragmentations.
On Jan 22, 1:36 pm, Jon <J...@.discussions.microsoft.com> wrote:
> Hello all,
> Just a quickie. If i add new indexes to tables, do I have to reanalyse /
> reindex / do anything or is this done automatically by SQL Server? We're
> running SQL Server 2000.
> Thanks,
> Jon
|||Hello,
If you add a clustered index automatically all the non clustered index
associated with that table will be recreated and its statistics will be
updated. If it is non clustered index
you may need to reindex all the other indexes individually or use DBCC
DBREINDEX('Tablename')
Thanks
Hari
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:F2A8A2B8-C395-49A6-8751-0D9592FF3D38@.microsoft.com...
> Hello all,
> Just a quickie. If i add new indexes to tables, do I have to reanalyse /
> reindex / do anything or is this done automatically by SQL Server? We're
> running SQL Server 2000.
> Thanks,
> Jon

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
Willie
Willie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>
|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller
|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>
|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
WillieWillie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>>I have a table that I need to update and the update is killing everything
>>else because the column I need to update appears to not be indexed. Now, I
>>thought I could go into the Design Table screen and add an index, but that
>>doesn't seem to have helped me. So, how then do I check what indexes
>>and/or keys exist on a given table? Thanks.
>> Willie
>

Creating indexes with ONLINE = ON - failing

Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
AlinCan you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of D
ML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo]
.[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].
[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[fi
eld5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP
_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possi
ble conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%
23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||We also have a clustered index on that table, we also tried to recreate that
index with ONLINE = ON, BOL stating that if a clustered index is created wi
th ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%
23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLSer...=29128
8
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLSer...=291
288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me , I don't know. I will further test it tomorrow, but as
of today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A

Creating indexes with ONLINE = ON - failing

Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of DML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[field5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possible conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||We also have a clustered index on that table, we also tried to recreate that index with ONLINE = ON, BOL stating that if a clustered index is created with ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>
|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin
|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me , I don't know. I will further test it tomorrow, but as
of today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>
|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A
|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A

Creating Indexes on Views

Hi,
I have a view which uses 2 other views.I need to create an index on this
view. But one of the two other views has Union statements. Is there any way
that an index can be created on this final view ( which is made of the other
2 views)?
Thanks
pmudIndexed views cannot reference other views, and a view definition cannot
include a UNION. More here:
http://msdn.microsoft.com/library/d...>
_06_9jnb.asp
ML
http://milambda.blogspot.com/|||Yes, I read that article, but i thought maybe someone ahs faced the same
problem as me and has a workaround for this. :(
--
pmud
"ML" wrote:

> Indexed views cannot reference other views, and a view definition cannot
> include a UNION. More here:
> http://msdn.microsoft.com/library/d...
es_06_9jnb.asp
>
> ML
> --
> http://milambda.blogspot.com/|||I was once in a similar situation, but found out that I could avoid it by
slightly adapting the data model. Maybe there's something else that would
help you achieve your goal, but you'd have to post DDL (and maybe some sampl
e
data).
ML
http://milambda.blogspot.com/|||I don't know your real problem (because you haven't told it), or your
experience with SQL Server. It sounds like you are asking for a kludge.
The first step would be to add index(es) to the base table(s). In many
cases this will increase performance sufficiently, and other queries
(queries that do not use your view) will also benefit from these
indexes.
If this is not enough, you can consider indexing the view. But as you
have noticed there are many limitations to that, and you need Enterprise
Edition (or always use the appropriate view hints).
Gert-Jan
pmud wrote:
> Hi,
> I have a view which uses 2 other views.I need to create an index on this
> view. But one of the two other views has Union statements. Is there any wa
y
> that an index can be created on this final view ( which is made of the oth
er
> 2 views)?
> Thanks
> --
> pmud

Creating indexes on temp tables in stored procedures

Help,

I have a complex stored procedure (>1000 lines) that uses multiple temp tables with thousands of rows. These temp tables are joined together, and selected from many times.

I tried to improve the performance of the procedure by createing the recommended indexes on my temp tables, but the query plan ignores the indexes and continues to use table scans.

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Developer Edition (Build 2600: Service Pack 2)

Can you provide some samples of the queries your issuing against these tables?
And maybe some examples of the tables...

|||

The temp tables are used in a cursor (trust me, there's no other way). So these tables of upwards of 200,000 rows each need to be joined, to pull out just a few rows at a time. The tables are fairly simple, around 10 columns each. My issue is that SQL Server doesn't pay attention to indexes created on temp tables because the execution plan is predetermined (I think) and it doesn't take into account an index that doesn't exist at compile-time.

I may have solved my issue by creating another stored procedure that I call from the first. this sub-stored procedure creates indexes on the temp tables (that's all it does) and it looks like the execution plan is no longer doing table scans to join two tables together... it appears to be using the indexes I've created. If you have an easier way to get SQL to use a just-created index, I'm all ears.

FYI: pet peeve of mine

your = something you own (posessive)

you're = conjunction form of "you are"

|||

First off, if you are going to punish people for grammer (yes, that is grammar then you are going to be sorely dissappointed. First off no spell check. Second, no pay. Third, well, come on it is just a bit of help.

Second: the biggest pet peeve of them all around here is not posting your code and DDL so we can look at what you are doing.

Third: "The temp tables are used in a cursor (trust me, there's no other way)." This is rarely true. Almost any cursor can be dealt with in set based code. Some order based accumulations are faster in cursors (or so I have heard , but I haven't written a cursor for a non-system function in years.

You could try adding a hint to your query to force index use. That might work. Try the WITH RECOMPILE hint on the proc too. If nothing else, you can try declaring the cursor in a dynamic SQL call:

declare @.cursorDeclare varchar(max)

set @.cursorDeclare = 'declare bob cursor global for select ''hi'' as hi

open bob'

exec (@.cursorDeclare)

fetch next from bob

That might do the trick. Or, if there are just a few rows to be returned, this might work to spool the dynamic query into a temp table:

create table #tempper

(

value varchar(10)

)

insert into #tempper

exec ('select ''value''')

select *

from #tempper

|||Here, here to the cursor advice; Cursors Are Loathesome.

Creating indexes on tables from the Database Explorer

I would like to create an index on a table from the Database Explorer.

I am using Microsoft Visual C# 2005 Express Edition.

I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.

Thanks!

Are you sure you're looking at the right server and connection? Can you post a screenshot of the two environments?

Creating indexes on tables from the Database Explorer

I would like to create an index on a table from the Database Explorer.

I am using Microsoft Visual C# 2005 Express Edition.

I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.

Thanks!

Are you sure you're looking at the right server and connection? Can you post a screenshot of the two environments?

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
Griff
When indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
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
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
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
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>