Information Schema Query Question

Hello All,
Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?
Thanks in Advance,
AkintoyeHere's an example:
SELECT TC.TABLE_SCHEMA AS TableOwner,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN
(
'PRIMARY KEY',
'UNIQUE'
)
ORDER BY TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME, KCU.COLUMN_NAME
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in message
news:4366775c$1@.ibixwebf.ibi.com...
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoye
>|||"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in
news:4366775c$1@.ibixwebf.ibi.com:

> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
You're looking for simething like this? (SQL Server 2005):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_schema + '.' + tc.table_name = <schema>.<table_name> )
I guess that would be approximately like this in SQL Server 2000 (Not
tested):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_name = <table_name> )
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9700F370E9A4Dolekristianbangaas@.
207.46.248.16:
(...)
Ooops, stayed a littlebit too long in my outbox :( Sorry folks.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

Information Schema Query Question

Hello All,

Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?

Thanks in Advance,

Akintoyeselect t.table_name, t.constraint_type, t.constraint_name,
c.column_name
from information_schema.TABLE_CONSTRAINTS t
join information_schema.CONSTRAINT_COLUMN_USAGE c ON t.constraint_name
= c.constraint_name
where constraint_type IN ('UNIQUE', 'PRIMARY KEY')
and t.table_name = <Table Name
Akintoye Olorode wrote:
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoyesql

Indx newbie: please help

Consider the following

--------------------
ASSIGNMENT
- Index : Integer, Unique, NotNull, Primary Key
- Start : DateTime, , NotNull,
- End : DateTime, , ,
- Other : ...

TASK
- Assignment : Integer, Unique, NotNull, Primary Foreign Key
- SubIndex : Integer, , NotNull, Primary Key
- Type : Char(3), , NotNull, Foreign Key
- Start : DateTime, , NotNull,
- End : DateTime, , ,
- Other : ...

TASK_TYPE
- Code : Char(3), Unique, NotNull, Primary Key
- Description : VarChar(20), , NotNull,
--------------------

As u can c the idea is simple...
...an assignment has an INDEX as PK, a start date, an end date and other fields;
...an assignment can have one or more tasks; the relationship is 1:N and is identifying (see next point)
...a task has a SUB INDEX inside the assignment; that is the PK is the assigment it belongs to (also a FK) and an index for that assignment
...a task has also a TYPE, which is a FK to the TASK_TYPE table

Consider also that...
...ASSIGNMENT contains > 1 millions rows
...TASK contains < 10 rows for each assignment (so an average of 5 millions rows)
...TASK_TYPE contains < 10 rows

As far as I know SQL-server creates a CLUSTERED INDEX for any PK, that is
ASSIGNMENT (Index)
TASK(Assigment, SubIndex)
TASK_TYPE(Code)

Do I have to add any other NON CLUSTERED INDEX? I would say I should add the following:
TASK(Assignment)
TASK(Type)

But of course TASK(Assignment) is already part of the CLUSTERED INDEX ASSIGNMENT(Assignment, SubIndex), so I shouldn't add it, right?
What about TASK(Type)?

Or maybe there's a completely different solution?

My main problem is due to the fact that TASK has a composite PK wher one field is also a FK.

Any advice is welcome.

thanks a lot :-)what, exactly, is your main problem? you didn't say what it was :)

i don't think you need any additional indexes, since TASK_TYPE should always be handled in memory (but then, i am not a DBA, eh ;))

by the way, it's not a good idea to name a column "index" as this is a reserved word in a lot of languages|||I'd simply like to know how where to put indexes :)|||okay, put an index on task.type

;)|||I would put the index in the database. Someone might need it, and it would be very embarrassing, if you left it at home.

Joking aside, is there a particular query that is giving problems? Remember that excessive indexing can hurt data modification processes.

Indexing question

We have SQL 2000 database with 4 heaviliy used tables. The primary table is
an orders table with FK's linking orders to the 3 other tables and all link
to the PK in those tables. We have several views that display data joined
on these FK to PK links. Are the FK's on the orders table speeding up views
if most of the criteria for search is based on columns in the orders table
or are they just extra overhead? Thanks.
DavidHi David
Foreign keys have nothing to do with speeding up views and
performance. The role of foreign key is to maintain data integrity.
For example if you have a foreign key between Orders and Customer
tables, then you won't be able to insert an order to a none existing
customer (or to delete a customer that has an order).
If you want to speed up the view you could try creating indexes on
the columns that create the foreign key. If we fallow the previous
example, an index in Order table on CustomerID column might help a view
that join Orders and Customers table.
Adi
On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
> We have SQL 2000 database with 4 heaviliy used tables. The primary table
is
> an orders table with FK's linking orders to the 3 other tables and all lin
k
> to the PK in those tables. We have several views that display data joined
> on these FK to PK links. Are the FK's on the orders table speeding up vie
ws
> if most of the criteria for search is based on columns in the orders table
> or are they just extra overhead? Thanks.
> David|||By default, defining a foreign key does not create any underlying indexes, s
o
there should not be any performance impact in the views. Depending on your
data distribution, it is possible that indexing the foreign key columns in
the child tables may increase performance on read operations.
I hope that this helps somehow.
"David" wrote:

> We have SQL 2000 database with 4 heaviliy used tables. The primary table
is
> an orders table with FK's linking orders to the 3 other tables and all lin
k
> to the PK in those tables. We have several views that display data joined
> on these FK to PK links. Are the FK's on the orders table speeding up vie
ws
> if most of the criteria for search is based on columns in the orders table
> or are they just extra overhead? Thanks.
> David
>
>|||Hello,
I do have indexes on the orders table for the FK that links the other tables
to their PK. I am having a problem with 2 of the tables (customer and
insurance) that are locking and preventing inserts and updates.
David
"Adi" <adicohn@.hotmail.com> wrote in message
news:1169737988.232529.120600@.q2g2000cwa.googlegroups.com...
> Hi David
> Foreign keys have nothing to do with speeding up views and
> performance. The role of foreign key is to maintain data integrity.
> For example if you have a foreign key between Orders and Customer
> tables, then you won't be able to insert an order to a none existing
> customer (or to delete a customer that has an order).
> If you want to speed up the view you could try creating indexes on
> the columns that create the foreign key. If we fallow the previous
> example, an index in Order table on CustomerID column might help a view
> that join Orders and Customers table.
> Adi
> On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
>|||Hi David
The insert to the Order table can be blocked if a table that is
referenced by Orders table is being locked. You should try and find
out why Customer and Insurance tables are locked for a long time. Do
you know which SQL Statement is locking those tables? If you know
which process is causing the blocking, then you can get the details
about the what the process is doing with dbcc inputbuffer and with
fn_get_sql() function.
Adi
On Jan 25, 9:36 pm, "David" <dlch...@.lifetimeinc.com> wrote:[vbcol=seagreen]
> Hello,
> I do have indexes on the orders table for the FK that links the other tabl
es
> to their PK. I am having a problem with 2 of the tables (customer and
> insurance) that are locking and preventing inserts and updates.
> David
> "Adi" <adic...@.hotmail.com> wrote in messagenews:1169737988.232529.120600@.
q2g2000cwa.googlegroups.com...
>
>
>
>
>
>
>

Indexing Question

Hi Gurus,
I have a table called Companies with int identity column as primary key and other fields. Also there is a Status column which can hold either 0 or 1. I use this status column in a join from some child table like where a.status = 1 along with other conditions.

Now, the question is should I create an index for this Status column? Will it improve the performance?

Thanks.I would say NO. One of the criteria for creating a good index is selectivity. So your index on a booleon column would not help the performance. In addition, it is just an overhead on the inserts.

- CB|||Post the query...because the answr is it depends...

If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2

I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||I agree with Brett. In the situation that he described (covered indexes), it could be helpful to tag that column at the end of the composite index to avoid another trip to get the data.

- CB
Originally posted by Brett Kaiser
Post the query...because the answr is it depends...

If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2

I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||Ok, here is a sample:

SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME

Hope this helps.|||In this situation, adding status to the index will not help, unless Brett thinks otherwise.

- CB
Originally posted by shekarnarayanan
Ok, here is a sample:

SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME

Hope this helps.|||Quick question,.. why don't you try it and see what happens? Worse comes to worst you can just delete it afterwards...|||Agreed, just try it. Set up a test/dev environment. Run query before index added, look at query execution plan, apply index and look once again at query execution plan. It will help.|||Well, I tried as suggested and the execution plan does not seem to use the new index at all! It just uses the clustered PK index. So I guess the answer is NO to the new index.

Thanks for all the suggestions.|||SELECT *...

No, No, No...

Do you really need all of the columns?

If so, list them out...

Only use SELECT * for testing, analysis...

What's the DDL for the 2 tables?

And the optimizer is making the right call in your case

How many rows of data are we talking about?|||You say your column only holds ones and zeros. If it is a bit field it cannot be indexed. Even if it is not a bit field, if the distribution of values for one and zero are about 50%, the optimizer might not get much out of using the index. In a binary tree it would only save 1 search ply.

blindman|||Originally posted by Brett Kaiser
SELECT *...

No, No, No...

Do you really need all of the columns?

If so, list them out...

Only use SELECT * for testing, analysis...

What's the DDL for the 2 tables?

And the optimizer is making the right call in your case

How many rows of data are we talking about?

Hi Brett,
Thank you for your concern. Yes I do list all the fields and never use the * from my programs. Number of records in the comp. table is around 500 and the orders table may be few thousands. I also filter by company.|||On such a small number of records, you will not see much of an improvement. Anytime you have so a limited distribution like yes/no, male/female ... the optimizer will normally chose a table scan over an index (so normally the recommendation is No Way). Unless your distribution is very high for 1 value and very low for the other value, an index will only help for the low value anyway. If the distribution of these values are remotely close to each the optimizer will probably perform a table scan anyway. Since these tables are small, sql will probably chose a table scan over an index even if your distribution is ripe for an index.

Indexing question

We have SQL 2000 database with 4 heaviliy used tables. The primary table is
an orders table with FK's linking orders to the 3 other tables and all link
to the PK in those tables. We have several views that display data joined
on these FK to PK links. Are the FK's on the orders table speeding up views
if most of the criteria for search is based on columns in the orders table
or are they just extra overhead? Thanks.
David
Hi David
Foreign keys have nothing to do with speeding up views and
performance. The role of foreign key is to maintain data integrity.
For example if you have a foreign key between Orders and Customer
tables, then you won't be able to insert an order to a none existing
customer (or to delete a customer that has an order).
If you want to speed up the view you could try creating indexes on
the columns that create the foreign key. If we fallow the previous
example, an index in Order table on CustomerID column might help a view
that join Orders and Customers table.
Adi
On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
> We have SQL 2000 database with 4 heaviliy used tables. The primary table is
> an orders table with FK's linking orders to the 3 other tables and all link
> to the PK in those tables. We have several views that display data joined
> on these FK to PK links. Are the FK's on the orders table speeding up views
> if most of the criteria for search is based on columns in the orders table
> or are they just extra overhead? Thanks.
> David
|||By default, defining a foreign key does not create any underlying indexes, so
there should not be any performance impact in the views. Depending on your
data distribution, it is possible that indexing the foreign key columns in
the child tables may increase performance on read operations.
I hope that this helps somehow.
"David" wrote:

> We have SQL 2000 database with 4 heaviliy used tables. The primary table is
> an orders table with FK's linking orders to the 3 other tables and all link
> to the PK in those tables. We have several views that display data joined
> on these FK to PK links. Are the FK's on the orders table speeding up views
> if most of the criteria for search is based on columns in the orders table
> or are they just extra overhead? Thanks.
> David
>
>
|||Hello,
I do have indexes on the orders table for the FK that links the other tables
to their PK. I am having a problem with 2 of the tables (customer and
insurance) that are locking and preventing inserts and updates.
David
"Adi" <adicohn@.hotmail.com> wrote in message
news:1169737988.232529.120600@.q2g2000cwa.googlegro ups.com...
> Hi David
> Foreign keys have nothing to do with speeding up views and
> performance. The role of foreign key is to maintain data integrity.
> For example if you have a foreign key between Orders and Customer
> tables, then you won't be able to insert an order to a none existing
> customer (or to delete a customer that has an order).
> If you want to speed up the view you could try creating indexes on
> the columns that create the foreign key. If we fallow the previous
> example, an index in Order table on CustomerID column might help a view
> that join Orders and Customers table.
> Adi
> On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
>
|||Hi David
The insert to the Order table can be blocked if a table that is
referenced by Orders table is being locked. You should try and find
out why Customer and Insurance tables are locked for a long time. Do
you know which SQL Statement is locking those tables? If you know
which process is causing the blocking, then you can get the details
about the what the process is doing with dbcc inputbuffer and with
fn_get_sql() function.
Adi
On Jan 25, 9:36 pm, "David" <dlch...@.lifetimeinc.com> wrote:[vbcol=seagreen]
> Hello,
> I do have indexes on the orders table for the FK that links the other tables
> to their PK. I am having a problem with 2 of the tables (customer and
> insurance) that are locking and preventing inserts and updates.
> David
> "Adi" <adic...@.hotmail.com> wrote in messagenews:1169737988.232529.120600@.q2g2000cwa.go oglegroups.com...
>
>
>
>

Indexing question

We have SQL 2000 database with 4 heaviliy used tables. The primary table is
an orders table with FK's linking orders to the 3 other tables and all link
to the PK in those tables. We have several views that display data joined
on these FK to PK links. Are the FK's on the orders table speeding up views
if most of the criteria for search is based on columns in the orders table
or are they just extra overhead? Thanks.
DavidHi David
Foreign keys have nothing to do with speeding up views and
performance. The role of foreign key is to maintain data integrity.
For example if you have a foreign key between Orders and Customer
tables, then you won't be able to insert an order to a none existing
customer (or to delete a customer that has an order).
If you want to speed up the view you could try creating indexes on
the columns that create the foreign key. If we fallow the previous
example, an index in Order table on CustomerID column might help a view
that join Orders and Customers table.
Adi
On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
> We have SQL 2000 database with 4 heaviliy used tables. The primary table is
> an orders table with FK's linking orders to the 3 other tables and all link
> to the PK in those tables. We have several views that display data joined
> on these FK to PK links. Are the FK's on the orders table speeding up views
> if most of the criteria for search is based on columns in the orders table
> or are they just extra overhead? Thanks.
> David|||By default, defining a foreign key does not create any underlying indexes, so
there should not be any performance impact in the views. Depending on your
data distribution, it is possible that indexing the foreign key columns in
the child tables may increase performance on read operations.
I hope that this helps somehow.
"David" wrote:
> We have SQL 2000 database with 4 heaviliy used tables. The primary table is
> an orders table with FK's linking orders to the 3 other tables and all link
> to the PK in those tables. We have several views that display data joined
> on these FK to PK links. Are the FK's on the orders table speeding up views
> if most of the criteria for search is based on columns in the orders table
> or are they just extra overhead? Thanks.
> David
>
>|||Hello,
I do have indexes on the orders table for the FK that links the other tables
to their PK. I am having a problem with 2 of the tables (customer and
insurance) that are locking and preventing inserts and updates.
David
"Adi" <adicohn@.hotmail.com> wrote in message
news:1169737988.232529.120600@.q2g2000cwa.googlegroups.com...
> Hi David
> Foreign keys have nothing to do with speeding up views and
> performance. The role of foreign key is to maintain data integrity.
> For example if you have a foreign key between Orders and Customer
> tables, then you won't be able to insert an order to a none existing
> customer (or to delete a customer that has an order).
> If you want to speed up the view you could try creating indexes on
> the columns that create the foreign key. If we fallow the previous
> example, an index in Order table on CustomerID column might help a view
> that join Orders and Customers table.
> Adi
> On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
>> We have SQL 2000 database with 4 heaviliy used tables. The primary table
>> is
>> an orders table with FK's linking orders to the 3 other tables and all
>> link
>> to the PK in those tables. We have several views that display data
>> joined
>> on these FK to PK links. Are the FK's on the orders table speeding up
>> views
>> if most of the criteria for search is based on columns in the orders
>> table
>> or are they just extra overhead? Thanks.
>> David
>|||Hi David
The insert to the Order table can be blocked if a table that is
referenced by Orders table is being locked. You should try and find
out why Customer and Insurance tables are locked for a long time. Do
you know which SQL Statement is locking those tables? If you know
which process is causing the blocking, then you can get the details
about the what the process is doing with dbcc inputbuffer and with
fn_get_sql() function.
Adi
On Jan 25, 9:36 pm, "David" <dlch...@.lifetimeinc.com> wrote:
> Hello,
> I do have indexes on the orders table for the FK that links the other tables
> to their PK. I am having a problem with 2 of the tables (customer and
> insurance) that are locking and preventing inserts and updates.
> David
> "Adi" <adic...@.hotmail.com> wrote in messagenews:1169737988.232529.120600@.q2g2000cwa.googlegroups.com...
>
> > Hi David
> > Foreign keys have nothing to do with speeding up views and
> > performance. The role of foreign key is to maintain data integrity.
> > For example if you have a foreign key between Orders and Customer
> > tables, then you won't be able to insert an order to a none existing
> > customer (or to delete a customer that has an order).
> > If you want to speed up the view you could try creating indexes on
> > the columns that create the foreign key. If we fallow the previous
> > example, an index in Order table on CustomerID column might help a view
> > that join Orders and Customers table.
> > Adi
> > On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
> >> We have SQL 2000 database with 4 heaviliy used tables. The primary table
> >> is
> >> an orders table with FK's linking orders to the 3 other tables and all
> >> link
> >> to the PK in those tables. We have several views that display data
> >> joined
> >> on these FK to PK links. Are the FK's on the orders table speeding up
> >> views
> >> if most of the criteria for search is based on columns in the orders
> >> table
> >> or are they just extra overhead? Thanks.
> >> David- Hide quoted text -- Show quoted text -

Indexing non-unique data

I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.

The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).

Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.|||I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.

The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).

Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
I think custured index should do the trick,its my opinion...I feel clustered index are best for low selectiviy columns,i.e. the column which have many duplicates values. But see what the gurus suggest...|||You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
But can't we make the foreign key column clustered index? I mean making the unique key not a clustered index...only a unique key column|||Hi Istaks

Welcome to the forum

Not a guru but some musings:

Well - a clustered index determines the physical order storage of data. So - it is useful if placed on an incrementing field as far as insertion of data is concerned as there will be no page splits based on insertion. It is also useful if you are likely to use >, < or between comparisons in a where condition on the clustered field.

The former is not the case. Inequality operators are rarely used on identities so Id go with no too :)|||So on table(B) create a clustered index on the unique key and a non-clustered index on the foreign key?

All selections from this table will be based on the foreign key.|||If ALL selects on this table will reference the foreign key and you will not be searching for individual records, then you would get a performance boost from using a clustered index on your foreign key.

Indexing in SQL Server Star Scheme Data Warehouse

Hi all,

Our star schema design has one fact table and 3 dimensions.

The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:

Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3

Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.

Is the above design good to start with?

Thanks,

VThe indexing looks fine, but as to whether this is a good design or not you only need to check my sig below to get my opinion...|||Thanks Blindman.

The one issue that we are encountering is, we didnt create a separate time dimension (a mistake in design). We have a smalldatetime field (72 distinct values only, one for each month, so 6 years in total) in the fact table.

We are not able to query this smalldate time field efficiently because we didnt index it (as it was not part of the dimension). We would like to change the design now.

We would like to create a time dimension using the following:

1. Create Time Dimension Table
2. Create new column Time_Key in Fact Table
3. Create Non clustered Index on smalldatetime field in fact table.
4. Join on smalldatetime fields in Time Dimension and Fact table to populate time_key in 2 from Time Dimension table.
5. Drop the index and column of smalldatetime field in fact and reassign non clustered index to Time_Key (FK)

Let me know how the above approach sounds to you guys.

V|||My gut feeling is that creating and dropping the temporary index on the smalldatetime column will take as long as doing a non-indexed join. Generally, indexes are only valuable because they are used more than once, so the investment involved in creating them is saved over each subsequent operation.
An index on a set of 72 discreet values may not even give you much performance boost across millions of records.|||I suppose the question I would pose to you, more than your design, would be, have you chosen the right granularity for your fact table? I haven't seen too many fact tables that stop at a monthly level unless they are being used for forecasting or budgeting purposes and even then they align to pre-existing warehouses, like a sales warehouse. My best recommendation would be to take some time and study warehousing and ensure you are providing a solution that isn't going to have to be reworked a couple of months down the road when the end users want to be able to drill down into details.

Indexing etiquette

I'm using Idera's SQL Diagnostic Manager and its showing me my index is
using the File Group "Primary" (which I recently read is bad) and I
have 3 index levels with some Data values having in excess of 700K
rows.
Is this bad and should I be worried? Is there some housekeeping I
should do in these situations?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> I'm using Idera's SQL Diagnostic Manager and its showing me my index is
> using the File Group "Primary" (which I recently read is bad)

There are situations where you can split up databases on several
file groups, and for instance have non-clustered index on a separate
volume. Note that if you relocate the clustered index, you relocate
the data as well.

But this should only be done if you have a clear understand of what you
win. None of our customer's databases have more than the two files
each database is born with. (And thus only one file group.)

> and I have 3 index levels with some Data values having in excess of 700K
> rows.
> Is this bad and should I be worried? Is there some housekeeping I
> should do in these situations?

It's a good idea to run DBCC DBREINDEX on your tables, if they tend
to fragment. Whether they fragment, can be concluded by using
DBCC SHOWCONTIG.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In books online there is an example which checks for fragmentation
above a level of 30% and runs the reindex function. Is this a good
number ot use or is it one of those "depends" kind of things?|||pb648174 (google@.webpaul.net) writes:
> In books online there is an example which checks for fragmentation
> above a level of 30% and runs the reindex function. Is this a good
> number ot use or is it one of those "depends" kind of things?

It's not a bad number. We ship our maintenance job that uses the output
from DBCC SHOWCONTIG, and if a table is fragmented enough, we run DBCC
DBREINDEX. And the bar where we reindex is, as far as I recall, precisely
30%...

What we have adding recently, and me and our admin-kind-of-guy has not
really arrived on the best strategy for, is to run UPDATE STASTISTICS
WITH FULLSCAN on table we don't reindex. Table that don't get defragmented
despite heavy insertion traffic, probably has a monotonic clustered
index, so statistics will be inaccurate after a while.

Then as always there are cases where you may want to deviate. For instance,
clustered index an guids is often said to be recipe for quick fragmentation.
However, SQL Server MVP Greg Linwood pointed out to me, that this can
be used to your advantage. You define the index with a relatively low
fill factor, say 50%. What will happen now is that insertion will happen
all over the place, but page splits will be rare, since all pages have
room to spare. So with design, framgmenation actually decreases as time
goes. Up to a certain point that is, once you are starting to fill up
more and more pages, page split will rage here and there. The idea is
that you monitor the state of the database closely, and that you have a
maintenance window where you again can reindex to 50%.

It goes without saying that this strategy is nothing for the left-hand
DBA, but requires thorough understanding and most of all, daily
monitoring of the state of the database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do you have an example of that maintenance job somewhere? We don't look
at the database daily and have many installations so it needs to be
scripted and run on its own without any supervision or intervention.

I'm right handed and not a DBA...

Indexing Columns

If you have a table with 3 columns,

ID (Primary Key)

Col1

Col2

And you have to perform the following query frequently

Code Snippet

Select ID where Col1='SomeValue' and Col2='SomeOtherValue'

Is it a bad idea to define a non clustered index on "Col1, Col2, ID" or am I better off just having the default indexing on the the primary key "ID"

I have never had to define an index that included all the columns in a table before so I am not sure if this is a bad idea

If you are using SQL 2005, and this is a frequent or common query, you may wish to explore using the new 'INCLUDE' option.

You could create an INDEX on Col1, Col2, and include [ID].

Something like this:

CREATE NONCLUSTERED INDEX ix_MyTable_Col1Col2
ON MySchema.MyTable( Col1, Col2 )
INCLUDE ( [ID] );

This is a 'covered' index. The entire query is satisfied by the index.

|||Thanks Arnie.... I have to support both SQL 2005 and SQL 2000 for this application at the moment.

|||

For SQL 2000, if you use this query frequently, index all three columns.

|||

you can not define ID as non clustered since it is a PK.

ID can be clustered index and check the unique checkbox.

you could define col1 and col2 as non clustered and ID as included column but beware of space used by index.

|||

You should create clustered primary indexes based off the 80/20 rule. If you are accessing this table 80% of the time by col1 and col2, then create a clustered primary index over col1, col2, ID. Creating an index over just the ID column will almost always cause bookup lookups. Create primary key indexes based off of usage, not how fast can I load data.

|||Chances are that your ID column is part of the automatically created clustered index since it's the primary key.

If that's the case, remember that all columns in the clustered index are appended to all non-clustered indexes for that table.

So there is no reason to add ID to your non-clustered index since it will be there already.

I think that SQL Server is smart enough to just ignore the ID column in the index definition since it knows that it's part of the clustered index, but I'm not sure on that one.

Having all three columns part of an index (ID in the clustered, and Col1 and Col2 in the non-clustered) creates, as somebody else mentioned, a "covering" index.

Basically, a covering index is an index which includes all references columns in your query (from the SELECT, JOIN, and WHERE clauses) so that no bookmark loops are necessary to return all the data. This data can come entirely from indexes... which is much faster than having to go read additional data pages to snag the original row from the table.

|||<P align=left><FONT face=Arial size=2>Hi,</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;We could arrive at a decision of using the index on&nbsp;columns based on the recommendation of the SQL Profiler utility. The input for the profiler would be a database&nbsp;trace file. This trace file will capture the usage of the table by the users and using this profiler will decide whether to use index. Also in this scenario, the table has only 3 columns and all the three columns are accessed by the user frequently. So the choice would be going for the covering index where all the three columns will be covered under index.</P>
<P align=left>&nbsp;</P>
<P align=left>Thanks.</P>

Indexing And Physical Storage Of Data

1> How is the data stored physically when there is now primary key as well as any index defined in the table.....?
2> How is the data stored physically when there is just a primary key defined in one of the column of the table? No INDEX defined.
Thanks,
Rahul JhaHi Rahul

As per Tom's answer in the last thread again you need to read up on this subject. You can't get an understanding of indexes by asking questions like this on a forum - or at least not without asking hundreds of questions.

Read up on this and you'll learn that indexes <> primary keys but SQL Server (and most RDMSs) enforce primary keys through unique, non-null indexes. As such, senario 2 is impossible. Also - a primary key does not determine the physical characteristics of a table. The nature of (or lack of) the indexes does.

indexes.

Hi.
I have 2 queries that are similiar
They involve 2 tables,
1) CDR which has a primary key of cdrid and has half a million rows
2) MODULE which has a primary key of moduleid and has only 240000 rows
Neither of the two tables have indexes in it aside from the clustered index
produced by the primary key constraint
the two queries are as follows
Query #1) select cdr.cdrid from cdr, module where cdr.cdrid <> module.cdrid
Query #2) select cdr.cdrid from cdr, module where cdr.cdrid = module.cdrid
Query #1 takes over 40 minutes to run
Query #2 takes 8 seconds to run.
Currently, I am making an indexed view across the module and cdr tables
hoping that it will speed up the performance of query #2, but in the
meantime, I don't understand why Query #2 is so significantly different in
performance than query #1. Can anyone please explain this to me?
Thanks,
-- Jasonlooks like query #1 is almost a cross join:
a true cross join:
select cdr.cdrid from cdr, module
would return 500K*250K rows, that's a lot. the criteria cdr.cdrid <>
module.cdrid
does not filter out many riows, right?|||The queries take a long time because you are using a cross join, or cartesia
n
product of the two tables.
1. select cdr.cdrid from cdr, module
this produces a result set that is 500,000 x 240,000 = 120,000,000 rows,
which is then reduced by your WHERE clause. Instead, try
SELECT cdr.cdrid FROM cdr WHERE cdr.cdrid NOT IN (SELECT cdrid FROM module)
or
SELECT a.cdrid FROM cdr a
WHERE NOT EXISTS (SELECT b.cdrid FROM module b WHERE a.cdrid=b.cdrid)
Similarly, for #2, try
SELECT cdr.cdrid FROM cdr WHERE cdr.cdrid IN (SELECT cdrid FROM module)
"Jason" wrote:

> Hi.
> I have 2 queries that are similiar
> They involve 2 tables,
> 1) CDR which has a primary key of cdrid and has half a million rows
> 2) MODULE which has a primary key of moduleid and has only 240000 rows
> Neither of the two tables have indexes in it aside from the clustered inde
x
> produced by the primary key constraint
> the two queries are as follows
> Query #1) select cdr.cdrid from cdr, module where cdr.cdrid <> module.cdri
d
> Query #2) select cdr.cdrid from cdr, module where cdr.cdrid = module.cdrid
> Query #1 takes over 40 minutes to run
> Query #2 takes 8 seconds to run.
> Currently, I am making an indexed view across the module and cdr tables
> hoping that it will speed up the performance of query #2, but in the
> meantime, I don't understand why Query #2 is so significantly different i
n
> performance than query #1. Can anyone please explain this to me?
> Thanks,
> -- Jason
>
>|||Alex, you are correct.
What I am really going for is something more like this:
select cdrid from cdr where cdrid not in (select cdrid from module)
In order to put this into an indexed view, I need to get rid of the subquery
though.
In a case like this, I think I need to write it as a left outer join (i
think).
so it would be like this:
select cdr.cdrid from cdr
left outer join module on cdr.cdrid <> module.cdrid
I think that that is still incorrect though because it returns far too many
results. Can someone please point out my error?
Thanks,
-- Jason|||Since you only care about matching records shouldn't you be using an INNER
join?
select cdr.cdrid from cdr
inner join module on cdr.cdrid <> module.cdrid
John
"Jason" <jason@.acd.net> wrote in message
news:DrCdnSaeYOOKoDXe4p2dnA@.giganews.com...
> Alex, you are correct.
> What I am really going for is something more like this:
> select cdrid from cdr where cdrid not in (select cdrid from module)
> In order to put this into an indexed view, I need to get rid of the
> subquery though.
> In a case like this, I think I need to write it as a left outer join (i
> think).
> so it would be like this:
> select cdr.cdrid from cdr
> left outer join module on cdr.cdrid <> module.cdrid
> I think that that is still incorrect though because it returns far too
> many results. Can someone please point out my error?
> Thanks,
> -- Jason
>|||I think I figured it out...
On the contrary, I only care about not-matching records... In this case, I
matched up the CDR records to the MODULE records using a left outer join...
and then used the where clause for filtering out the ones that had actual
matches... as in the following:
select cdr.cdrid, module.cdrid from cdr
left outer join module on cdr.cdrid = module.cdrid
where module.cdrid is null
seems to work, but because it has an outer join, I cant put it into an
indexed view. *sigh*
"John Kendrick" <jkendrick@.DONTneo.SPAMrr.com> wrote in message
news:eQaorzYBGHA.3396@.tk2msftngp13.phx.gbl...
> Since you only care about matching records shouldn't you be using an INNER
> join?
> select cdr.cdrid from cdr
> inner join module on cdr.cdrid <> module.cdrid
> John
> "Jason" <jason@.acd.net> wrote in message
> news:DrCdnSaeYOOKoDXe4p2dnA@.giganews.com...
>|||On Tue, 20 Dec 2005 16:06:36 -0500, Jason wrote:

>I think I figured it out...
>On the contrary, I only care about not-matching records... In this case, I
>matched up the CDR records to the MODULE records using a left outer join...
>and then used the where clause for filtering out the ones that had actual
>matches... as in the following:
>select cdr.cdrid, module.cdrid from cdr
> left outer join module on cdr.cdrid = module.cdrid
>where module.cdrid is null
>seems to work, but because it has an outer join, I cant put it into an
>indexed view. *sigh*
Hi Jason,
Any specific reason why you want this in an indexed view?
From the preceeding post, I gather that this is only in an attempt to
increase performance. Why not try the easier options first? Such as
creating a nonclustered index on module.cdrid?
CREATE INDEX NameGoesHere ON module (cdrid)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Indexes, primary keys constraint type from sysconstraints -table?

Where I can found information that what indexes are primary keys,
and where is the information of constraints type in sysconstarint table?
I readed drom help that field sysconstraints.status should tell it, but I
have mystique values in this field,
such as 3105, 2069, 133141 etc... Data type is bitmap, how I can compare
bitmaps and integers?
Hi
Don't query system tables directly.
Use the Information Schema Views, together with OBJECTPROPERTY and
COLUMNPROPERTY
All the information is easily usable then.
Look those up in BOL.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Major" wrote:

> Where I can found information that what indexes are primary keys,
> and where is the information of constraints type in sysconstarint table?
> I readed drom help that field sysconstraints.status should tell it, but I
> have mystique values in this field,
> such as 3105, 2069, 133141 etc... Data type is bitmap, how I can compare
> bitmaps and integers?
>
>
|||hi
i think you can find it by using this query
select * from information_schema.table_constraints
Siddharth
"Major" wrote:

> Where I can found information that what indexes are primary keys,
> and where is the information of constraints type in sysconstarint table?
> I readed drom help that field sysconstraints.status should tell it, but I
> have mystique values in this field,
> such as 3105, 2069, 133141 etc... Data type is bitmap, how I can compare
> bitmaps and integers?
>
>

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have available
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexes,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Please
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.

> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have availabl
e
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexe
s,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Pleas
e
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
Message posted via http://www.droptable.com"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.

> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have available
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexes,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Please
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.
> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes being replicated

I have set up transactional replication between two SQL Server 2000 boxes. I
am replicating a table. On the publisher this table has a primary key, a
clustered index, number of non clustered indexes and a check constraint.
I want to replicate only the table structure and data, but none of the other
objects associated with the table.
When I use Enterprise Manager to set the snapshot options I want to uncheck
"Include declared referential integrity", "Clustered Indexes" and
"Nonclustered indexes" in the "Copy objects to destination" section .
However, the "Nonclustered indexes" checkbox is greyed out and ticked. If I
check "Include declared referential integrity" then I can uncheck
"Nonclustered indexes". So far so good, but then when I uncheck "Include
declared referential integrity" the "Nonclustered indexes" box gets
autmoatically checked and greyed out.
Why is it not possible to uncheck both "Include declared referential
integrity" and "Nonclustered indexes"?
Is there something I'm misunderstanding here?
Thanks
Stephen
Stephen,
only a few of the possible permutations are covered in this dialog box. For
a more fine control, you can set the @.schema_option argument of
sp_addarticle.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Indexes and keys are not synced w/ snapshot publication

Hello,
I am having trouble getting the indexes and primary keys copied to the
subscriber in a snapshot publication. I created a snapshot publication and
added an article to it using the wizard. Then I used the SQL Management
Studio to configure the properties of the article and set the following
options to true.
Copy primary key
Copy clustered index
Copy nonclustered indexes
Copy check constraints
Copy foreign keys
I generated the SQL script and got the code listed below to re-create the
article. When I use this script to create the article, the settings mentioned
above are "true" as expected. However, when the snapshot is applied to the
subscriber, none of the keys or indexes are transferred eventough everything
suceeds.
Does anyone know what else I can do to make this work? Did I not configure
it correctly?
Here is the code to recreate the article.
exec sp_addarticle
@.publication = @.PUBLICATON_NAME,
@.article = @.TABLE_NAME,
@.source_owner = N'dbo',
@.source_object = @.TABLE_NAME,
@.type = N'logbased',
@.description = null,
@.creation_script = null,
@.pre_creation_cmd = N'drop',
@.schema_option = 0x00000000080350DD,
@.identityrangemanagementoption = N'none',
@.destination_table = @.DESTINATION_TABLE_NAME,
@.destination_owner = N'dbo',
@.vertical_partition = N'false'
P.S - FYI, the destination table name is different than the source table
name as per our biz requirements. Could this be why?
Thanks!
Johnny
UPDATE: I just did some additional testing and found that if the destination
table stays the same as the source, the PKs and the indexes are copied to the
subscriber as they should be. It seems like this problem only occurs when the
destination table is different.
Does anyone know if and how I can work around this or force it to include
them even though the destination table is different?
Johnny
"Johnny" wrote:

> Hello,
> I am having trouble getting the indexes and primary keys copied to the
> subscriber in a snapshot publication. I created a snapshot publication and
> added an article to it using the wizard. Then I used the SQL Management
> Studio to configure the properties of the article and set the following
> options to true.
> Copy primary key
> Copy clustered index
> Copy nonclustered indexes
> Copy check constraints
> Copy foreign keys
>
> I generated the SQL script and got the code listed below to re-create the
> article. When I use this script to create the article, the settings mentioned
> above are "true" as expected. However, when the snapshot is applied to the
> subscriber, none of the keys or indexes are transferred eventough everything
> suceeds.
> Does anyone know what else I can do to make this work? Did I not configure
> it correctly?
>
> Here is the code to recreate the article.
> ----
> exec sp_addarticle
> @.publication = @.PUBLICATON_NAME,
> @.article = @.TABLE_NAME,
> @.source_owner = N'dbo',
> @.source_object = @.TABLE_NAME,
> @.type = N'logbased',
> @.description = null,
> @.creation_script = null,
> @.pre_creation_cmd = N'drop',
> @.schema_option = 0x00000000080350DD,
> @.identityrangemanagementoption = N'none',
> @.destination_table = @.DESTINATION_TABLE_NAME,
> @.destination_owner = N'dbo',
> @.vertical_partition = N'false'
>
> P.S - FYI, the destination table name is different than the source table
> name as per our biz requirements. Could this be why?
> Thanks!
> Johnny
|||Hi Johnny,
The indexes, constraints should be copied to the subscriber even though the
destination object name is different than source object name. I suspect that
you probably still have the "old" table with the source table name and the
same constraints at the subscriber. What happens in this case is that since
constraint names have to be unique across all tables, the distribution agent
simply cannot create a constraint on the "new" table with the same name as
one on the "old" table.
-Raymond
"Johnny" wrote:
[vbcol=seagreen]
> UPDATE: I just did some additional testing and found that if the destination
> table stays the same as the source, the PKs and the indexes are copied to the
> subscriber as they should be. It seems like this problem only occurs when the
> destination table is different.
> Does anyone know if and how I can work around this or force it to include
> them even though the destination table is different?
> Johnny
>
> "Johnny" wrote:
|||Wow!!!!! You hit it right on the money. That was the problem - I had the old
table with the same index names. After dropping this table, I was able to
successfully distribute the snapshot with a different destination name and
all indexes, primary keys, etc. were successfully transferred.
Thanks Raymond.
- Johnny
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Johnny,
> The indexes, constraints should be copied to the subscriber even though the
> destination object name is different than source object name. I suspect that
> you probably still have the "old" table with the source table name and the
> same constraints at the subscriber. What happens in this case is that since
> constraint names have to be unique across all tables, the distribution agent
> simply cannot create a constraint on the "new" table with the same name as
> one on the "old" table.
> -Raymond
> "Johnny" wrote:

Indexes

I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to crea
te a
> non-clustered index on acct_key.
... unless you do it to cover queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is n
o reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered ind
ex.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I no
w see was the case,
re-reading the OP). I was thrown off a bit by this:

I have difficulties understanding what "extract single values from this fiel
d" means. My thinking
was that the NC index could cover queries where the restriction is for some
other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>