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 unique index and clustered index in sql server management studio

Hello,
I have run across a very strange situation.
If i use the management studio to change an index that is clustered is does
not actually change the index. It shows up in the gui as changed, but the
data is not changed when returning the data.
Also when you use the createto> when right clicking on the table, none of
the indexes or index constraints are recreated.
In order to make the changes work you have to manually create the
constraints:
ie
ALTER TABLE dbo._messages_initiated_from ADD CONSTRAINT
IX_key_messages_initiated_from UNIQUE CLUSTERED
(
key_messages_initiated_from
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_initiated_from ON dbo._messages_initiated_from
(
initiated_from
) ON [PRIMARY]
Then the changes work and the code is genereted in the scripts.
This used to work in enterprise manager in sql 2000.
Any ideas why this is not functioning right?
Thanks
gary
See in-line:
Andrew J. Kelly SQL MVP
"gary" <garyseven7@.nospam.nospam> wrote in message
news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have run across a very strange situation.
> If i use the management studio to change an index that is clustered is
> does not actually change the index. It shows up in the gui as changed,
> but the data is not changed when returning the data.
The order in which data is returned is never guarenteed unless you use an
ORDER BY clause. This has always been the case and is nothing new. Without
an ORDER BY clause SQL Server is free to return the rows in what ever order
it seems most appropriate. Often times when you have a clustered index on a
table and issue a SELECT without a WHERE clause it will return the data in
mostly clustered index order. But this has never been guarenteed and should
not rely on that behaviour.

> Also when you use the createto> when right clicking on the table, none of
> the indexes or index constraints are recreated.
> In order to make the changes work you have to manually create the
> constraints:
If you want all the Indexes, constraints etc. you should use the Generate
Scripts wizard found by right clicking on the DB under Tasks. There you can
pick and choose all objects or none.

> ie
> ALTER TABLE dbo._messages_initiated_from ADD CONSTRAINT
> IX_key_messages_initiated_from UNIQUE CLUSTERED
> (
> key_messages_initiated_from
> ) ON [PRIMARY]
> CREATE NONCLUSTERED INDEX IX_initiated_from ON
> dbo._messages_initiated_from
> (
> initiated_from
> ) ON [PRIMARY]
> Then the changes work and the code is genereted in the scripts.
> This used to work in enterprise manager in sql 2000.
> Any ideas why this is not functioning right?
>
> Thanks
> gary
>
|||This response does not completely answer the question. First off, if the
index is clustered, the data is returned in the order of the records, which
is the order of the cluster. That is the definition.
Second of all, if you right click on the table, and select createto> to
create the script, absolutely none of the indexes that you created manually
other than the primary index appear in the script.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||I mean, when you right click on a table and select "script table to -->
create to--> the code to create the other indexes is not created.
if you were to create the indexes as constraints as the previous version did
in the enterprise manager then the script to works.
Also, on clustered indexes created with a constraint the unique setting is
automatically yes and can't be changed (it is disabled). In the new sql
console you can create a clustered index and then mark it as non-unique,
which just does not make sense as the clustered index is what all the other
indexes point to.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||Ok, i guess i can assume the indexes are being created differently. But it
still does not make sense that when the tables are scripted that the script
to create the new indexes are not showing up.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||No, that's not the definition of a clustered index. The rows a STORED in
the order of the clustered index. They're returned in whatever order SQL
feels like if you don't specify an ORDER BY.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gary" <garyseven7@.nospam.nospam> wrote in message
news:eyTC0jYDGHA.740@.TK2MSFTNGP12.phx.gbl...
> This response does not completely answer the question. First off, if the
> index is clustered, the data is returned in the order of the records,
> which is the order of the cluster. That is the definition.
> Second of all, if you right click on the table, and select createto> to
> create the script, absolutely none of the indexes that you created
> manually other than the primary index appear in the script.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
|||gary (garyseven7@.nospam.nospam) writes:
> This response does not completely answer the question. First off, if
> the index is clustered, the data is returned in the order of the
> records, which is the order of the cluster. That is the definition.
NO!
The fact that index is clustered means that the data is at the leaf
pages of the index. Thus, data is physically sorted according to the
clustered index.
But SQL does not bother about physical storage. If you say:
SELECT ... FROM tbl WHERE ...
without an ORDER BY clause, the DB engine is free to return the rows
in any order. For instance, if the WHERE clause includes a column on
which there is an non-clustered index, it is likely that that the
output will follow that index. If you say "SELECT * FROM bigtable",
SQL Server may open parallel streams, and run from different parts of
the table, at the same time.
In a relational datbase, there is only one way get data sorted in a
certain order, and that is ORDER BY.

> Second of all, if you right click on the table, and select createto> to
> create the script, absolutely none of the indexes that you created
> manually other than the primary index appear in the script.
Yes. Andrew addressed this in his post:
[vbcol=seagreen]

> Also, on clustered indexes created with a constraint the unique setting
> is automatically yes and can't be changed (it is disabled). In the new
> sql console you can create a clustered index and then mark it as
> non-unique, which just does not make sense as the clustered index is
> what all the other indexes point to.
What happens when you define a clustered index as non-unique, is that
SQL Server adds a four-byte "uniquifier". It has been this way since
SQL 7. (And all SQL Server versions have permitted non-uniuqe clustered
indexes.)

> Ok, i guess i can assume the indexes are being created differently. But
> it still does not make sense that when the tables are scripted that the
> script to create the new indexes are not showing up.
On http://lab.msdn.microsoft.com/produc...k/default.aspx you
can enter suggestions for improvments in SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks for all your help.
It is kind of odd that the script table does not generate the indexes and
the script database that is in the wizard is capable of generating the
sciprts for the index (it doesn't generate them by default, you have to
select the script indexes to be true).
SQL 2000 GUI created the indexes by creating a constraint while the new SQL
2005 GUI creates them using the create index statement. If created as a
constraint the alter statement ins generated in the create table, if created
in a create index statement it is not.
This seems somewhat consistantly inconsistant so to speak.
Thanks for helping clarify the issue.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||Yes I agree that it is misleading and not a very good design that it leaves
out a bunch of objects. Its really only useful for a quick look at the DDL
of the table itself. Anything more should be done thru the Generate Scripts
task.
Andrew J. Kelly SQL MVP
"gary" <garyseven7@.nospam.nospam> wrote in message
news:OaEMUSaDGHA.4052@.TK2MSFTNGP10.phx.gbl...
> Thanks for all your help.
> It is kind of odd that the script table does not generate the indexes and
> the script database that is in the wizard is capable of generating the
> sciprts for the index (it doesn't generate them by default, you have to
> select the script indexes to be true).
> SQL 2000 GUI created the indexes by creating a constraint while the new
> SQL 2005 GUI creates them using the create index statement. If created
> as a constraint the alter statement ins generated in the create table, if
> created in a create index statement it is not.
> This seems somewhat consistantly inconsistant so to speak.
> Thanks for helping clarify the issue.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
sql

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 small index takes LONG time!

Hi- I am trying to add a simple, non clustered index, using Enterprise
Manager all default settings, to a table with about 30 columns and about 15K
rows of data. The index is on a smalldatetime column. For some reason, when
I
try to add this index, it takes FOREVER (let it go at least 6 hours, still
not completed). Killed process (which had very high processor count),
restarted SS, tried again, no go. No sign of any locks (in fact, no one even
connected to this database). Plenty of disk space.
Any ideas why this might occur? There are several other indexes on the
table, including primary key id column and other indexes referencing the
column I am trying to index as secondary, tertiary column, but never as
primary column. This is a large database with many tables (100+), lots of
data (4GB or so). But, this seems unusual to me...
I can access the table and the data in it from another instance of EM while
I am trying to create this index.
Any ideas?
Thanks,
ChrisEM is notorious for generating inefficient code. It's far better to script
it by hand because:
1) a skilled human will write better code
2) the script can be used elsewhere and be checked into source code control
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
Hi- I am trying to add a simple, non clustered index, using Enterprise
Manager all default settings, to a table with about 30 columns and about 15K
rows of data. The index is on a smalldatetime column. For some reason, when
I
try to add this index, it takes FOREVER (let it go at least 6 hours, still
not completed). Killed process (which had very high processor count),
restarted SS, tried again, no go. No sign of any locks (in fact, no one even
connected to this database). Plenty of disk space.
Any ideas why this might occur? There are several other indexes on the
table, including primary key id column and other indexes referencing the
column I am trying to index as secondary, tertiary column, but never as
primary column. This is a large database with many tables (100+), lots of
data (4GB or so). But, this seems unusual to me...
I can access the table and the data in it from another instance of EM while
I am trying to create this index.
Any ideas?
Thanks,
Chris|||Chris,
Either it is not building the index, or something is seriously wrong.
An index like you describe should be built in seconds.
When I just created an index using Enterprise Manager (within Design
Table) it did not start the process until I closed the Design Table
window, when it prompted me with the question asking if I wanted to
save changes. Did you reach that point?
Roy
On Mon, 27 Feb 2006 17:09:27 -0800, querylous
<querylous@.discussions.microsoft.com> wrote:

>Hi- I am trying to add a simple, non clustered index, using Enterprise
>Manager all default settings, to a table with about 30 columns and about 15
K
>rows of data. The index is on a smalldatetime column. For some reason, when
I
>try to add this index, it takes FOREVER (let it go at least 6 hours, still
>not completed). Killed process (which had very high processor count),
>restarted SS, tried again, no go. No sign of any locks (in fact, no one eve
n
>connected to this database). Plenty of disk space.
>Any ideas why this might occur? There are several other indexes on the
>table, including primary key id column and other indexes referencing the
>column I am trying to index as secondary, tertiary column, but never as
>primary column. This is a large database with many tables (100+), lots of
>data (4GB or so). But, this seems unusual to me...
>I can access the table and the data in it from another instance of EM while
>I am trying to create this index.
>Any ideas?
>Thanks,
>Chris|||Hmmm... looking at the process info, I can see that the query generated by
Enterprise Manager is
CREATE
INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
I can't imagine a much more efficient means of creating an index than that?
Thanks,
Chris
"Tom Moreau" wrote:

> EM is notorious for generating inefficient code. It's far better to scrip
t
> it by hand because:
> 1) a skilled human will write better code
> 2) the script can be used elsewhere and be checked into source code contro
l
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
> Hi- I am trying to add a simple, non clustered index, using Enterprise
> Manager all default settings, to a table with about 30 columns and about 1
5K
> rows of data. The index is on a smalldatetime column. For some reason, whe
n
> I
> try to add this index, it takes FOREVER (let it go at least 6 hours, still
> not completed). Killed process (which had very high processor count),
> restarted SS, tried again, no go. No sign of any locks (in fact, no one ev
en
> connected to this database). Plenty of disk space.
> Any ideas why this might occur? There are several other indexes on the
> table, including primary key id column and other indexes referencing the
> column I am trying to index as secondary, tertiary column, but never as
> primary column. This is a large database with many tables (100+), lots of
> data (4GB or so). But, this seems unusual to me...
> I can access the table and the data in it from another instance of EM whil
e
> I am trying to create this index.
> Any ideas?
> Thanks,
> Chris
>|||Are there any processes blocking it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
Hmmm... looking at the process info, I can see that the query generated by
Enterprise Manager is
CREATE
INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
I can't imagine a much more efficient means of creating an index than that?
Thanks,
Chris
"Tom Moreau" wrote:

> EM is notorious for generating inefficient code. It's far better to
> script
> it by hand because:
> 1) a skilled human will write better code
> 2) the script can be used elsewhere and be checked into source code
> control
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
> Hi- I am trying to add a simple, non clustered index, using Enterprise
> Manager all default settings, to a table with about 30 columns and about
> 15K
> rows of data. The index is on a smalldatetime column. For some reason,
> when
> I
> try to add this index, it takes FOREVER (let it go at least 6 hours, still
> not completed). Killed process (which had very high processor count),
> restarted SS, tried again, no go. No sign of any locks (in fact, no one
> even
> connected to this database). Plenty of disk space.
> Any ideas why this might occur? There are several other indexes on the
> table, including primary key id column and other indexes referencing the
> column I am trying to index as secondary, tertiary column, but never as
> primary column. This is a large database with many tables (100+), lots of
> data (4GB or so). But, this seems unusual to me...
> I can access the table and the data in it from another instance of EM
> while
> I am trying to create this index.
> Any ideas?
> Thanks,
> Chris
>|||Nope, neither blocking nor blocked by...
"Tom Moreau" wrote:

> Are there any processes blocking it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
> Hmmm... looking at the process info, I can see that the query generated by
> Enterprise Manager is
> CREATE
> INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
> I can't imagine a much more efficient means of creating an index than that
?
> Thanks,
> Chris
> "Tom Moreau" wrote:
>
>|||I'm actually using the "manage indexes" window; after specifying the index,
I
click ok, and then another window appears which implies that the index is
being built, and this is where the hang occurs. Also, I know the request has
been sent at that point b/c I can see it as a process. Yes, something is
definitely wrong; I am also used to indexes like this being built in
seconds...
"Roy Harvey" wrote:

> Chris,
> Either it is not building the index, or something is seriously wrong.
> An index like you describe should be built in seconds.
> When I just created an index using Enterprise Manager (within Design
> Table) it did not start the process until I closed the Design Table
> window, when it prompted me with the question asking if I wanted to
> save changes. Did you reach that point?
> Roy
>
> On Mon, 27 Feb 2006 17:09:27 -0800, querylous
> <querylous@.discussions.microsoft.com> wrote:
>
>|||On Mon, 27 Feb 2006 17:46:26 -0800, querylous
<querylous@.discussions.microsoft.com> wrote:

>I'm actually using the "manage indexes" window; after specifying the index,
I
>click ok, and then another window appears which implies that the index is
>being built, and this is where the hang occurs. Also, I know the request ha
s
>been sent at that point b/c I can see it as a process. Yes, something is
>definitely wrong; I am also used to indexes like this being built in
>seconds...
So what happens if you run the same CREATE INDEX in Query Analyzer?
Roy Harvey
Beacon Falls, CT|||Could you please give us the SQL edition, version number, hardware, etc.?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:90639871-203F-4402-ADC0-D5D583E5BAB0@.microsoft.com...
Nope, neither blocking nor blocked by...
"Tom Moreau" wrote:

> Are there any processes blocking it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
> Hmmm... looking at the process info, I can see that the query generated by
> Enterprise Manager is
> CREATE
> INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
> I can't imagine a much more efficient means of creating an index than
> that?
> Thanks,
> Chris
> "Tom Moreau" wrote:
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows N
T
5.0 (Build 2195: Service Pack 4)
Never had any trouble with it.
"Tom Moreau" wrote:

> Could you please give us the SQL edition, version number, hardware, etc.?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:90639871-203F-4402-ADC0-D5D583E5BAB0@.microsoft.com...
> Nope, neither blocking nor blocked by...
> "Tom Moreau" wrote:
>
>

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 nonclustered index without locking table

Is rebuilding a nonclustered index an online operation?
We have an OLTP system and we need to rebuild a nonclustered index that
is not in use, but I am unsure if this will lock out the whole table or
just the specific column. The table in question is the largest table in
the database and has several hundred records written to it every minute.
TIA,
-BenIf the index is on the table it is in use as it will be updated each time
the row is updated. DBCC DBREINDEX is a totally offline operation but DBCC
INDEXDEFRAG is online.
Andrew J. Kelly SQL MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:uFEyeocaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Is rebuilding a nonclustered index an online operation?
> We have an OLTP system and we need to rebuild a nonclustered index that
> is not in use, but I am unsure if this will lock out the whole table or
> just the specific column. The table in question is the largest table in
> the database and has several hundred records written to it every minute.
> TIA,
> -Ben|||I was afraid of that. We have an index that is corrupt, and it is
harder for us to stop the SQL agent, and do maintenance.
Thanks for the help.
-Ben
Andrew J. Kelly wrote:
> If the index is on the table it is in use as it will be updated each time
> the row is updated. DBCC DBREINDEX is a totally offline operation but DBC
C
> INDEXDEFRAG is online.
>|||Why would you need to stop Agent? If it is corrupt then you may be better
off using DBREINDEX juston that one index or CREATE INDEX with the DROP
Existing option. How large is the table?
Andrew J. Kelly SQL MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> I was afraid of that. We have an index that is corrupt, and it is
> harder for us to stop the SQL agent, and do maintenance.
> Thanks for the help.
> -Ben
> Andrew J. Kelly wrote:
time[vbcol=seagreen]
DBCC[vbcol=seagreen]|||Don't forget to find out why it got corrupt in the first place - checkout
the SQL Server errorlog and the NT event logs for hardware issues.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uv0ukDeaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Why would you need to stop Agent? If it is corrupt then you may be better
> off using DBREINDEX juston that one index or CREATE INDEX with the DROP
> Existing option. How large is the table?
> --
> Andrew J. Kelly SQL MVP
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> time
> DBCC
>|||Very good point.
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231Xb7MfaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Don't forget to find out why it got corrupt in the first place - checkout
> the SQL Server errorlog and the NT event logs for hardware issues.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uv0ukDeaEHA.2216@.TK2MSFTNGP10.phx.gbl...
better[vbcol=seagreen]
but[vbcol=seagreen]
>|||Not sure exactly how the corruption occurred, SQL logs only showed the
following error, as did the Event Logs.
2004-07-15 11:44:36.29 spid111 Error: 8646, Severity: 21, State: 1
2004-07-15 11:44:36.29 spid111 The index entry for row ID was not
found in index ID 3, of table 1285579618, in database 'dbname'..
I have run DBCC CHECKDB and found the errors.
I have dropped and recreated the above mentioned index, and ran DBCC
CHECKDB again and no errors were reported.
I also ran DBCC CHECKTABLE on the table where the index was located and
that returned no errors.
This is the only article we could find regarding this error.
http://support.microsoft.com/defaul...kb;en-us;822747
Andrew J. Kelly wrote:
> Very good point.
>|||Did you follow the instructions in the KB article to see if the scenario
described applies to you?
BTW, you don't need to run DBCC CHECKTABLE separately from DBCC CHECKDB.
DBCC CHECKDB runs the equivalent of DBCC CHECKALLOC and then the equivalent
of DBCC CHECKTABLE for all tables in the database.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <bmilliron@.gmail.com> wrote in message
news:#MsT0OoaEHA.2972@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Not sure exactly how the corruption occurred, SQL logs only showed the
> following error, as did the Event Logs.
> 2004-07-15 11:44:36.29 spid111 Error: 8646, Severity: 21, State: 1
> 2004-07-15 11:44:36.29 spid111 The index entry for row ID was not
> found in index ID 3, of table 1285579618, in database 'dbname'..
> I have run DBCC CHECKDB and found the errors.
> I have dropped and recreated the above mentioned index, and ran DBCC
> CHECKDB again and no errors were reported.
> I also ran DBCC CHECKTABLE on the table where the index was located and
> that returned no errors.
> This is the only article we could find regarding this error.
> http://support.microsoft.com/defaul...kb;en-us;822747
>
> Andrew J. Kelly wrote:

Creating nonclustered index without locking table

Is rebuilding a nonclustered index an online operation?
We have an OLTP system and we need to rebuild a nonclustered index that
is not in use, but I am unsure if this will lock out the whole table or
just the specific column. The table in question is the largest table in
the database and has several hundred records written to it every minute.
TIA,
-Ben
If the index is on the table it is in use as it will be updated each time
the row is updated. DBCC DBREINDEX is a totally offline operation but DBCC
INDEXDEFRAG is online.
Andrew J. Kelly SQL MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:uFEyeocaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Is rebuilding a nonclustered index an online operation?
> We have an OLTP system and we need to rebuild a nonclustered index that
> is not in use, but I am unsure if this will lock out the whole table or
> just the specific column. The table in question is the largest table in
> the database and has several hundred records written to it every minute.
> TIA,
> -Ben
|||I was afraid of that. We have an index that is corrupt, and it is
harder for us to stop the SQL agent, and do maintenance.
Thanks for the help.
-Ben
Andrew J. Kelly wrote:
> If the index is on the table it is in use as it will be updated each time
> the row is updated. DBCC DBREINDEX is a totally offline operation but DBCC
> INDEXDEFRAG is online.
>
|||Why would you need to stop Agent? If it is corrupt then you may be better
off using DBREINDEX juston that one index or CREATE INDEX with the DROP
Existing option. How large is the table?
Andrew J. Kelly SQL MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> I was afraid of that. We have an index that is corrupt, and it is
> harder for us to stop the SQL agent, and do maintenance.
> Thanks for the help.
> -Ben
> Andrew J. Kelly wrote:
time[vbcol=seagreen]
DBCC[vbcol=seagreen]
|||Don't forget to find out why it got corrupt in the first place - checkout
the SQL Server errorlog and the NT event logs for hardware issues.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uv0ukDeaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Why would you need to stop Agent? If it is corrupt then you may be better
> off using DBREINDEX juston that one index or CREATE INDEX with the DROP
> Existing option. How large is the table?
> --
> Andrew J. Kelly SQL MVP
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> time
> DBCC
>
|||Very good point.
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231Xb7MfaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Don't forget to find out why it got corrupt in the first place - checkout
> the SQL Server errorlog and the NT event logs for hardware issues.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uv0ukDeaEHA.2216@.TK2MSFTNGP10.phx.gbl...
better[vbcol=seagreen]
but
>
|||Not sure exactly how the corruption occurred, SQL logs only showed the
following error, as did the Event Logs.
2004-07-15 11:44:36.29 spid111 Error: 8646, Severity: 21, State: 1
2004-07-15 11:44:36.29 spid111 The index entry for row ID was not
found in index ID 3, of table 1285579618, in database 'dbname'..
I have run DBCC CHECKDB and found the errors.
I have dropped and recreated the above mentioned index, and ran DBCC
CHECKDB again and no errors were reported.
I also ran DBCC CHECKTABLE on the table where the index was located and
that returned no errors.
This is the only article we could find regarding this error.
http://support.microsoft.com/default...b;en-us;822747
Andrew J. Kelly wrote:
> Very good point.
>
|||Did you follow the instructions in the KB article to see if the scenario
described applies to you?
BTW, you don't need to run DBCC CHECKTABLE separately from DBCC CHECKDB.
DBCC CHECKDB runs the equivalent of DBCC CHECKALLOC and then the equivalent
of DBCC CHECKTABLE for all tables in the database.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <bmilliron@.gmail.com> wrote in message
news:#MsT0OoaEHA.2972@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Not sure exactly how the corruption occurred, SQL logs only showed the
> following error, as did the Event Logs.
> 2004-07-15 11:44:36.29 spid111 Error: 8646, Severity: 21, State: 1
> 2004-07-15 11:44:36.29 spid111 The index entry for row ID was not
> found in index ID 3, of table 1285579618, in database 'dbname'..
> I have run DBCC CHECKDB and found the errors.
> I have dropped and recreated the above mentioned index, and ran DBCC
> CHECKDB again and no errors were reported.
> I also ran DBCC CHECKTABLE on the table where the index was located and
> that returned no errors.
> This is the only article we could find regarding this error.
> http://support.microsoft.com/default...b;en-us;822747
>
> Andrew J. Kelly wrote:

Creating nonclustered index without locking table

Is rebuilding a nonclustered index an online operation?
We have an OLTP system and we need to rebuild a nonclustered index that
is not in use, but I am unsure if this will lock out the whole table or
just the specific column. The table in question is the largest table in
the database and has several hundred records written to it every minute.
TIA,
-BenIf the index is on the table it is in use as it will be updated each time
the row is updated. DBCC DBREINDEX is a totally offline operation but DBCC
INDEXDEFRAG is online.
--
Andrew J. Kelly SQL MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:uFEyeocaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Is rebuilding a nonclustered index an online operation?
> We have an OLTP system and we need to rebuild a nonclustered index that
> is not in use, but I am unsure if this will lock out the whole table or
> just the specific column. The table in question is the largest table in
> the database and has several hundred records written to it every minute.
> TIA,
> -Ben|||I was afraid of that. We have an index that is corrupt, and it is
harder for us to stop the SQL agent, and do maintenance.
Thanks for the help.
-Ben
Andrew J. Kelly wrote:
> If the index is on the table it is in use as it will be updated each time
> the row is updated. DBCC DBREINDEX is a totally offline operation but DBCC
> INDEXDEFRAG is online.
>|||Why would you need to stop Agent? If it is corrupt then you may be better
off using DBREINDEX juston that one index or CREATE INDEX with the DROP
Existing option. How large is the table?
--
Andrew J. Kelly SQL MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> I was afraid of that. We have an index that is corrupt, and it is
> harder for us to stop the SQL agent, and do maintenance.
> Thanks for the help.
> -Ben
> Andrew J. Kelly wrote:
> > If the index is on the table it is in use as it will be updated each
time
> > the row is updated. DBCC DBREINDEX is a totally offline operation but
DBCC
> > INDEXDEFRAG is online.
> >|||Don't forget to find out why it got corrupt in the first place - checkout
the SQL Server errorlog and the NT event logs for hardware issues.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uv0ukDeaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Why would you need to stop Agent? If it is corrupt then you may be better
> off using DBREINDEX juston that one index or CREATE INDEX with the DROP
> Existing option. How large is the table?
> --
> Andrew J. Kelly SQL MVP
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> > I was afraid of that. We have an index that is corrupt, and it is
> > harder for us to stop the SQL agent, and do maintenance.
> >
> > Thanks for the help.
> >
> > -Ben
> >
> > Andrew J. Kelly wrote:
> > > If the index is on the table it is in use as it will be updated each
> time
> > > the row is updated. DBCC DBREINDEX is a totally offline operation but
> DBCC
> > > INDEXDEFRAG is online.
> > >
>|||Very good point.
--
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231Xb7MfaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Don't forget to find out why it got corrupt in the first place - checkout
> the SQL Server errorlog and the NT event logs for hardware issues.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uv0ukDeaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > Why would you need to stop Agent? If it is corrupt then you may be
better
> > off using DBREINDEX juston that one index or CREATE INDEX with the DROP
> > Existing option. How large is the table?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Ben" <bmilliron@.gmail.com> wrote in message
> > news:O6LYicdaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> > > I was afraid of that. We have an index that is corrupt, and it is
> > > harder for us to stop the SQL agent, and do maintenance.
> > >
> > > Thanks for the help.
> > >
> > > -Ben
> > >
> > > Andrew J. Kelly wrote:
> > > > If the index is on the table it is in use as it will be updated each
> > time
> > > > the row is updated. DBCC DBREINDEX is a totally offline operation
but
> > DBCC
> > > > INDEXDEFRAG is online.
> > > >
> >
> >
>|||Not sure exactly how the corruption occurred, SQL logs only showed the
following error, as did the Event Logs.
2004-07-15 11:44:36.29 spid111 Error: 8646, Severity: 21, State: 1
2004-07-15 11:44:36.29 spid111 The index entry for row ID was not
found in index ID 3, of table 1285579618, in database 'dbname'..
I have run DBCC CHECKDB and found the errors.
I have dropped and recreated the above mentioned index, and ran DBCC
CHECKDB again and no errors were reported.
I also ran DBCC CHECKTABLE on the table where the index was located and
that returned no errors.
This is the only article we could find regarding this error.
http://support.microsoft.com/default.aspx?scid=kb;en-us;822747
Andrew J. Kelly wrote:
> Very good point.
>|||Did you follow the instructions in the KB article to see if the scenario
described applies to you?
BTW, you don't need to run DBCC CHECKTABLE separately from DBCC CHECKDB.
DBCC CHECKDB runs the equivalent of DBCC CHECKALLOC and then the equivalent
of DBCC CHECKTABLE for all tables in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <bmilliron@.gmail.com> wrote in message
news:#MsT0OoaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Not sure exactly how the corruption occurred, SQL logs only showed the
> following error, as did the Event Logs.
> 2004-07-15 11:44:36.29 spid111 Error: 8646, Severity: 21, State: 1
> 2004-07-15 11:44:36.29 spid111 The index entry for row ID was not
> found in index ID 3, of table 1285579618, in database 'dbname'..
> I have run DBCC CHECKDB and found the errors.
> I have dropped and recreated the above mentioned index, and ran DBCC
> CHECKDB again and no errors were reported.
> I also ran DBCC CHECKTABLE on the table where the index was located and
> that returned no errors.
> This is the only article we could find regarding this error.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;822747
>
> Andrew J. Kelly wrote:
> > Very good point.
> >

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 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
>

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
>

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Citizen is not important. Surname and Name are the only ones that are
"selective".
Would it be better to create two indexes - one on the Name column and
another on the Surname column, or would one index on both columns be
better?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Andy,
My hunch is that you should create the index on Surname, Firstname, but
why not try it out and find out how many logical reads are required in
each case? You will know for sure which is best then.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> Citizen is not important. Surname and Name are the only ones that are
> "selective".
> Would it be better to create two indexes - one on the Name column and
> another on the Surname column, or would one index on both columns be
> better?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:

>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Citizen is not important. Surname and Name are the only ones that are
"selective".
Would it be better to create two indexes - one on the Name column and
another on the Surname column, or would one index on both columns be
better?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Andy,
My hunch is that you should create the index on Surname, Firstname, but
why not try it out and find out how many logical reads are required in
each case? You will know for sure which is best then.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> Citizen is not important. Surname and Name are the only ones that are
> "selective".
> Would it be better to create two indexes - one on the Name column and
> another on the Surname column, or would one index on both columns be
> better?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:

>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
>UserID (PK),
>Name,
>Surname,
>DateOfBirth,
>Citizen,
>BranchCode,
>RegionCode,
>Division
>)
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
>SET NOCOUNT ON
>SELECT UserID, Name, Surname
>FROM Personal
>WHERE Citizen = 'Yes'
>ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:
>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
>)
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating index on table at subscriber

I have a database for a 3rd party application that I need to report from.
Since I can't do anything to the 3rd party database, I am replicating the
relevant tables to my own database and running the queries there.
One of the tables (Action table) has two fields (Datestamped, Timestamped),
which are critical to some complicated queries I'm running. However neither
of these fields have indexes in the 3rd party database (since they obviously
aren't important to the 3rd party application).
I want to create indexes at the subscriber, but am worried about the
following.
1) Can I do it without breaking the replication or the databases?
2) What will the maintenance be like for this? Will I need to recreate the
indexes every time I need to rebuild the replication?
Thanks in advance
Derek
sp_addscriptexec is only good for UNC type snapshots, ie it will not work
for snapshots delivered by FTP.
The recommendation to deploy this index via a post snapshot command by Paul
is good one.
You could also deliver a custom object script using sp_addarticle, where the
custom script would have the index in it; however you may find that this
will slow down your snapshot delivery.
Here is an example of this:
http://groups.google.com/groups?selm...&output=gplain
The downside of having additional indexes on the subscriber tables is that
it will slow down update activity on your subscriber. In general minimze
your indexes on your subscriber tables. You probably have no option in your
case.
For index maintenance, auto update stats always helps (again this may lead
to some performance degradation with heavy updates), so you might want to
consider nightly dbcc dbreindex or indexdefrag.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4a3001c490c0$9b72d050$a601280a@.phx.gbl...
> Derek,
> the easiest way to do this is to have a post-snapshot
> script to run which adds the indexes. If initialization
> has already taken place, you can add the index manually
> or using sp_addscriptexec (transactional and merge)
> without breaking the replication setup.
> HTH,
> Paul Ibison
|||I haven't been able to get this working yet, but haven't yet had the time to
investigate fully and am now knocking off for the day (in Australia).
However I'm having trouble keeping the replication going once I have changed
indexes. Today I have gotten this error:
The query processor could not produce a query plan from the optimizer
because a query cannot update a text, ntext, or image column and a clustering
key at the same time.
Is this related?
"Hilary Cotter" wrote:

> sp_addscriptexec is only good for UNC type snapshots, ie it will not work
> for snapshots delivered by FTP.
> The recommendation to deploy this index via a post snapshot command by Paul
> is good one.
> You could also deliver a custom object script using sp_addarticle, where the
> custom script would have the index in it; however you may find that this
> will slow down your snapshot delivery.
> Here is an example of this:
> http://groups.google.com/groups?selm...&output=gplain
> The downside of having additional indexes on the subscriber tables is that
> it will slow down update activity on your subscriber. In general minimze
> your indexes on your subscriber tables. You probably have no option in your
> case.
> For index maintenance, auto update stats always helps (again this may lead
> to some performance degradation with heavy updates), so you might want to
> consider nightly dbcc dbreindex or indexdefrag.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4a3001c490c0$9b72d050$a601280a@.phx.gbl...
>
>
|||Derek,
I found this whch is relevant for your situation:
http://groups.google.com/groups?hl=e...ngxa10.phx.gbl
From BOL for Update it states "If an update query could alter more than one
row while updating both the clustering key and one or more text, image, or
Unicode columns, the update operation fails and SQL Server returns an error
message."
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That seems to have worked.
What I have ended up doing is instead of deleting all the indexes and
creating my own, I have simply added some that I needed. Not quite as good,
but should do the job.
Derek
"Paul Ibison" wrote:

> Derek,
> I found this whch is relevant for your situation:
> http://groups.google.com/groups?hl=e...ngxa10.phx.gbl
> From BOL for Update it states "If an update query could alter more than one
> row while updating both the clustering key and one or more text, image, or
> Unicode columns, the update operation fails and SQL Server returns an error
> message."
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>