Creating an Index with a calulated member in MDX ?

Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?

Ex:

Jan07: Actual = 32000 -> index = 114

Feb07: Actual = 34000 -> index = 121

Mar07: Actual = 28000 -> index = 100

Apr07: Actual = 20000 -> index = 71

It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||

If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:

Code Snippet

(

([Date].[Month].CurrentMember, [Measures].[Actual])

/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])

) * 100

This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.

Creating an Index with a calulated member in MDX ?

Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?

Ex:

Jan07: Actual = 32000 -> index = 114

Feb07: Actual = 34000 -> index = 121

Mar07: Actual = 28000 -> index = 100

Apr07: Actual = 20000 -> index = 71

It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||

If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:

Code Snippet

(

([Date].[Month].CurrentMember, [Measures].[Actual])

/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])

) * 100

This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.

Creating an Index timing out

I am creating an index on a table wit 35 million records but I get the error

'TT_ObjPerformance' table
- Unable to create index 'IX_TT_ObjPerformance_CACode'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How can I get the index created?

Thanks
SQL Server newbie

From where are you creating the index? via query analyzer/job/some front end( hopefully not)?

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book a
nd
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530
Is there a question here? You certainly can create an index on a Bit column
but the question is do you really want to? Most of the time the selectivity
is too low to be of use for an index. But under certain conditions it makes
sense.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:3339C46D-605A-4522-852D-E67DDA36B8DF@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>
|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

Creating a unique index on a table

Hi,
I'm trying to create an index on a newly created table. I want the index to
be on the field ref. I tried running:
CREATE INDEX ref ON U_segment (ref)
but it's just sitting there! The ref is unique anyway, and there are about
700,000 records.
Any help, or advice would be appreaciated.
Regards
Rob
Does it run and stop, or what?
I wonder if having the index name and the column name the same is the
issue...try
CREATE INDEX IX_ref ON U_segment (ref)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:9AF46DBF-1693-4DD3-A324-BB378FCEBB79@.microsoft.com...
> Hi,
> I'm trying to create an index on a newly created table. I want the index
> to
> be on the field ref. I tried running:
>
> CREATE INDEX ref ON U_segment (ref)
> but it's just sitting there! The ref is unique anyway, and there are about
> 700,000 records.
> Any help, or advice would be appreaciated.
> Regards
> Rob
|||Did you look to see if you are being blocked?
Andrew J. Kelly SQL MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:9AF46DBF-1693-4DD3-A324-BB378FCEBB79@.microsoft.com...
> Hi,
> I'm trying to create an index on a newly created table. I want the index
> to
> be on the field ref. I tried running:
>
> CREATE INDEX ref ON U_segment (ref)
> but it's just sitting there! The ref is unique anyway, and there are about
> 700,000 records.
> Any help, or advice would be appreaciated.
> Regards
> Rob
sql

Creating a Unique Index

Hi

I tried the following from the help file...

When you create or modify a unique index, you can set an option to
ignore duplicate keys. If this option is set and you attempt to create
duplicate keys by adding or updating data that affects multiple rows
(with the INSERT or UPDATE statement), the row that causes the
duplicates is not added or, in the case of an update, discarded.

For example, if you try to update "Smith" to "Jones" in a table where
"Jones" already exists, you end up with one "Jones" and no "Smith" in
the resulting table. The original "Smith" row is lost because an
UPDATE statement is actually a DELETE followed by an INSERT. "Smith"
was deleted and the attempt to insert an additional "Jones" failed.
The whole transaction cannot be rolled back because the purpose of
this option is to allow a transaction in spite of the presence of
duplicates.

But when I did it the original "Smith" row was not lost.

I am doing something wrong or is the help file incorrect.

DanThose paragraphs are referring to the IGNORE_DUP_KEYS option which is not
the default when creating an index. Did you specify the IGNORE_DUP_KEYS
option on your CREATE INDEX statement?

Why do you want to ignore duplicate keys in this way? Typically, it would be
better to put the code to ignore duplicates in your INSERT or UPDATE
statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
IGNORE_DUP_KEYS option is a little strange and very non-standard and
non-relational as this article explains.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

"Did you specify the IGNORE_DUP_KEYS"

Yes I did.

The issue I have is using a update statement with a table that has
IGNORE_DUP_KEYS index as the help file says --

"if you try to update "Smith" to "Jones" in a table where "Jones"
already exists, you end up with one "Jones" and no "Smith" in the
resulting table. The original "Smith" row is lost because an UPDATE
statement is actually a DELETE followed by an INSERT"

But when I try this, the original "Smith" row is not lost...

So am I doing something wrong or is the help file wrong.

Could you give it a try?

Thanks

Dan

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<lI-dnYK39q4-Y1yi4p2dnA@.giganews.com>...
> Those paragraphs are referring to the IGNORE_DUP_KEYS option which is not
> the default when creating an index. Did you specify the IGNORE_DUP_KEYS
> option on your CREATE INDEX statement?
> Why do you want to ignore duplicate keys in this way? Typically, it would be
> better to put the code to ignore duplicates in your INSERT or UPDATE
> statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
> IGNORE_DUP_KEYS option is a little strange and very non-standard and
> non-relational as this article explains.|||You're right. The UPDATE statement described should produce an error
("Cannot insert duplicate key"). The RTM version of Books Online is wrong
and that page has been changed in the latest version:

http://msdn.microsoft.com/library/e...uniqueindex.asp

--
David Portas
----
Please reply only to the newsgroup
--

Creating a unique constarint on a multiple null column

HI,

To create a unique constraint on a multiple nullable column, we need to create a view with not null column and and then create a unique index on that view.

Is this is the only way of doing ?

Thank you.

Yes.

Since a Primary Key CONSTRAINT requires NOT NULL values, a UNIQUE index is the best alternative method to force a constraint on columns that can contain NULL values.

Creating a primary key as a non clustered index

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

It appears to me from the code above that you actually created the clustered index on the Id field.|||

As rottengeek noticed, you are creating the clustered index on column [Id], but even if you create it on column [code], does not expect any specific order if you are not using the "order by" clause in your "select" statement. That is the only way to assure a specific order.

Quaere Verum - Clustered Index Scans - Part I

http://www.sqlmag.com/articles/index.cfm?articleid=92886&

Quaere Verum - Clustered Index Scans - Part II

http://www.sqlmag.com/articles/index.cfm?articleid=92887&

Quaere Verum - Clustered Index Scans - Part III

http://www.sqlmag.com/articles/index.cfm?articleid=92888&

AMB

|||

you are correct, so i have modified it to have the clustered index on the Code field.

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Code]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

Creating a Page Index and table of contents

This is the problem I am facing with the SQL server reporting services,
I am trying to create a report where in we have to
display Page index and the table of contents Along with the Page
Number. This report contains the list of products under a subcategory
which in turn are under particular Categories. The Page Index should
display the products names in the alphabetical order with the page
number where It falls, this is similar to the appendix at the end of
any textbook and the table of contents display the category and
its subcategories with page numbers Now, the problem is reading the
report dynamically to find out the page numbers where this product
falls and the categories falls . I want a solution for displaying the
Page index and the table of contents in SQL server reporting services
2005 version.
Waiting for quick sujjestions or help in this regardAre you using web service approach?
If so,you can always get page content before displaying it and by analyzing
the underlying HTML get all information you need -
page number, total number of pages, any internal error occurred, etc. Based
on that information you can build your own page header with a custom page
index.
"Aparna" <aparna.cirigiri@.gmail.com> wrote in message
news:1135255357.206805.159890@.g44g2000cwa.googlegroups.com...
> This is the problem I am facing with the SQL server reporting services,
>
> I am trying to create a report where in we have to
> display Page index and the table of contents Along with the Page
> Number. This report contains the list of products under a subcategory
> which in turn are under particular Categories. The Page Index should
> display the products names in the alphabetical order with the page
> number where It falls, this is similar to the appendix at the end of
> any textbook and the table of contents display the category and
> its subcategories with page numbers Now, the problem is reading the
> report dynamically to find out the page numbers where this product
> falls and the categories falls . I want a solution for displaying the
> Page index and the table of contents in SQL server reporting services
> 2005 version.
> Waiting for quick sujjestions or help in this regard
>

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailco
m@.207.46.248.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.
Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailcom@.207.46.2 48.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailcom@.207.46.248.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>

creating a clustered index - after the fact

Hello,
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thanks
tootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegr oups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>
|||thanks for the info
AlterEgo wrote:[vbcol=seagreen]
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegr oups.com...

creating a clustered index - after the fact

Hello,
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thankstootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>|||thanks for the info
AlterEgo wrote:[vbcol=seagreen]
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...

creating a clustered index - after the fact

Hello,
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thankstootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>|||thanks for the info
AlterEgo wrote:
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> > Hello,
> >
> > I have a few tables that I need to add clustered indexes to. However,
> > most of the table already have existing non-clustered indexes on them.
> > I understand that adding a clustered index after the other indexes is
> > not a good idea - but is this mostly a index creation performance issue
> > (in that the other indexes need to get rebuilt)? Or is there more to
> > the issue than this?
> >
> > I can do the create after hours, so performance is not an issue. But I
> > am wondering should I just drop all the indexes on the tables, and
> > recreate them from scratch, in proper order?
> >
> > Thanks
> >

Create View with schemabinding from other database table

Hello,
I am trying to create index view fore that I went to create view with
schemabinding .
If I try table in same database it work properly. But my database is
different from I went to create view.
syntax is this
CREATE VIEW VSTATE WITH SCHEMABINDING AS
SELECT STATE, ID, NAME
FROM [gujarat-villages].dbo.[FPT-GUJARAT]
WHERE (DISTRICT = '0')
it give me following error message :
Cannot schema bind view 'VSTATE' because name
'gujarat-villages.dbo.FPT-GUJARAT' is invalid for schema binding. Names must
be in two-part format and an object cannot reference itself.> If I try table in same database it work properly. But my database is
> different from I went to create view.
One of the requirements for schema binding is that the referenced objects
must be in the same database as the schemabound object. This is why a *two*
part rather than three part name is required.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"harshad" <harshad7_jp@.hotmail.com> wrote in message
news:0AE95685-811C-40EE-B740-8544C5F5D09E@.microsoft.com...
> Hello,
> I am trying to create index view fore that I went to create view with
> schemabinding .
> If I try table in same database it work properly. But my database is
> different from I went to create view.
> syntax is this
> CREATE VIEW VSTATE WITH SCHEMABINDING AS
> SELECT STATE, ID, NAME
> FROM [gujarat-villages].dbo.[FPT-GUJARAT]
> WHERE (DISTRICT = '0')
> it give me following error message :
> Cannot schema bind view 'VSTATE' because name
> 'gujarat-villages.dbo.FPT-GUJARAT' is invalid for schema binding. Names
> must be in two-part format and an object cannot reference itself.
>
>|||You can't use SCHEMABINDING if you refer to a able in a different database. Here's a quote from
Books Online (CREATE VIEW):
"SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the
base table or tables cannot be modified in a way that would affect the view definition. The view
definition itself must first be modified or dropped to remove dependencies on the table that is to
be modified. When you use SCHEMABINDING, the select_statement must include the two-part names
(schema.object) of tables, views, or user-defined functions that are referenced. All referenced
objects must be in the same database."
See the last sentence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"harshad" <harshad7_jp@.hotmail.com> wrote in message
news:0AE95685-811C-40EE-B740-8544C5F5D09E@.microsoft.com...
> Hello,
> I am trying to create index view fore that I went to create view with schemabinding .
> If I try table in same database it work properly. But my database is different from I went to
> create view.
> syntax is this
> CREATE VIEW VSTATE WITH SCHEMABINDING AS
> SELECT STATE, ID, NAME
> FROM [gujarat-villages].dbo.[FPT-GUJARAT]
> WHERE (DISTRICT = '0')
> it give me following error message :
> Cannot schema bind view 'VSTATE' because name 'gujarat-villages.dbo.FPT-GUJARAT' is invalid for
> schema binding. Names must be in two-part format and an object cannot reference itself.
>
>

Create View with associated Index

I have inherited the following code from someone before me:

create view [ABC ] as

select T.*

,rtrim( cast([COL_1] as char( 2))

+ cast ([COL_3] as char( 3)) ) [ROLL01]

from [ABC_TABLE] T;

ROLL01 is primary key index; however as primary key it is made up of COL_1, COL_2, COL_3

I take it the index view is a separate object even though the same name is used as the primary key index? Because I would think this would greatly affect performance. Of course I do not like this anyway because of the documentation confusion.

In this view the [ROLL01] is an alias column name for the column that is being constructed with

,rtrim( cast([COL_1] as char( 2))

+ cast ([COL_3] as char( 3)) )

and has no implications on the primary key defined on the table.

I believe the person that did this was just trying to see the "composite key" as one single item.

The view is listing the contents of ABC_TABLE, with this additional column appended to the end of each row.