Key And Index On Same Column?

Aug 26, 2005

Is there any advantage to doing this:

ALTER TABLE testtable ADD
CONSTRAINT PK_sysUser
PRIMARY KEY NONCLUSTERED (UserID)
WITH FILLFACTOR = 100,

CONSTRAINT IX_sysUser
UNIQUE NONCLUSTERED (UserID)
WITH FILLFACTOR = 100
GO

over just having the primary key? Does having both an index and a
primary key add anything?

thanks
chris

View 6 Replies


ADVERTISEMENT

Clustered Index On Client_ID+ORderNO+OrdersubNo, If I Create 3 Noncluster Index On Said Column Will It Imporve Performance

Dec 5, 2007



Dear All.

We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.

table is >> Order_Dtl

Columns are:-

Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)

Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.

The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.

Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)

My Question are:-


Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?

Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?

Q3. I want to check what indexes has been used? on what search?

Q4. How can i check what table was populated when, or last date of update (DML)?

My Limitation is i Dont Create a Partioned table. I dont have permission to do it.



In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.


I am thank u to all who read or reply.

Arshad

Manager Database
Esoulconsultancy.com

(Teradata Master)
10g OCP










View 3 Replies View Related

Differnce Between A Column That S A Primary Key And A Column That S A Key/index With Isunique=true

Sep 13, 2007



Hi,

Please, What s the differnce between a column that s a primary key and a column that s a "key/index with Isunique=true"?

Thanks a lot.

View 4 Replies View Related

Multi-column Index Vs Single Column Indexes

Aug 14, 2007

Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.

View 1 Replies View Related

FK On A Column To A Table Where The Column Is Not The PK But A Unique Index

Jul 23, 2005

Hello everyone, I am new to ERWIN and I need helps from the experts outthere.We are using ERWin 4.1.2771 and have reversed engineered some MS SQLServer 2000 databases.The problem we are having is that we have a FK on a column to a tablewhere the PK of the referencing table is on another column (such as anidentity column). We have a unique index on the column in the PK tableand SQL Server allows you to build a FK reference even though thecolumn is not defined as the PK.Does anyone know how to create this type of FK within Erwin?Thank You

View 2 Replies View Related

Index On Bit Column

Nov 7, 2004

Hi,

MSSql2000: According to docs:
"Columns of type bit cannot have indexes on them"

Its impossible to define index on bit column using EM but ‘create index …’ command in QA is working and the index is created.
I understand why not to create index like this but … its valid or invalid operation to create index on BIT column ?

Thanks

View 2 Replies View Related

Which Column To Put Index

May 6, 2008

i've these following table and data

tblCamera
TransID | BranchID | X | Y | TransDate
---------------------------------------------
1 | 12 | 90 | 76 | 3/12/2007
2 | 9 | 45 | 79 | 3/12/2007
3 | 12 | 69 | 34 | 3/12/2007
4 | 12 | 78 | 56 | 3/12/2007
5 | 9 | 67 | 35 | 3/12/2007
6 | 12 | 78 | 56 | 3/12/2007
*TransID is a primary key

tblBranch
BranchID | BranchName
--------------------------
9 | Kota Raja
10 | Kota Alam
11 | Kota Lama
12 | Kota Tua
13 | Kota Hantu
*BranchID is a primary key

Which column need to put an index? Can anyone show me the best way?

View 3 Replies View Related

CI And NC Index On Same Column

May 5, 2014

If i have cluster and NC index on same column,does it degrade performance on DML statements ? any advantage on select statements.

Is it good to have both indexes on same column ?

View 2 Replies View Related

Can't Add Bit Column To Unique Index

Feb 6, 2007

This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.

View 5 Replies View Related

Index On Identity Column

Feb 18, 1999

I have an non-clustered index defined on an identity column(statusid) on a table containing 200k rows. The query optimizer chooses to do a table scan when doing the following select. Why would this be.

SELECT * FROM STATUS
WHERE statusid = 10000

View 2 Replies View Related

SQL 2012 :: CI And NC Index On Same Column?

May 5, 2014

I have a question regarding indexes.

If i have cluster and NC index on same column,does it degrade performance on DML statements ? any advantage on select statements.

Is it good to have both indexes on same column ?

View 3 Replies View Related

Multi Column Index

Apr 22, 2008

Hi there,

My 'where' clause contains the following...

startdate <= @Date AND enddate > @Date

Should I put one index on 'startDate' and another on 'endDate' or should I have one index that covers both columns?

I don't know what the difference would be.

Cheers, XF.

View 1 Replies View Related

Insert A Column At Particular Index

Nov 20, 2013

I am using SQL Server2012. Is it possible to insert a new_column to table at 10th of it's column_index ? Ofcourse I can select, but I wish to insert at particular index...Is it possible? Alter table Table1 add New_Column varchar(20) at 10 ??????

View 4 Replies View Related

Index Computed Column?

Jul 28, 2006

Can I create an index on a variation of a column that isn't actually inthe table?I have a ParcelNumber column, with values like123 AB-67012345ABC 000-00-04012-345-67AP34567890The blanks and non-alphanumeric characters cause problems with users,because sometimes they're there, and sometimes they aren't. So I wouldlike to create an index based on this column, with the non-alphanumericcharacters squeezed out. Of course I can add such a column to thetable and index it, but I'm wondering if it can be done withoutactually adding the column.Thanks,Jim

View 6 Replies View Related

Index On A Varchar Column?

Jul 20, 2005

In an effort to improve the speed of queries against my main table,I'll be indexing a column whose data type is varchar(50).Would I be better off (better performance) if I changed the column'sdata type to some numeric type? I would have to update the column'sdata to accomodate this, but I would do it if this offers aperformance gain.-- Bill

View 6 Replies View Related

DateTime Column Not Using Index

Jul 11, 2006

I'm having a hard time getting one of my tables to use a non clustered index that I have on a DateTime column.

A sample version of the table is something like this


CREATE TABLE Appointments
(
ID INT NOT NULL,
AppointmentDate DateTime NOT NULL
)

with a clustered primary key on ID and a nonclustered key on AppointmentDate.

This production table has over 1million rows and the problem I have is this:



If I do a SELECT * FROM Appointments where AppointmentDate >= '20060701' AND AppointmentDate < '20060702' the Non clustered index on the AppointmentDate column works fine. i.e. I'm returning all appointments for the 1st of July.



Now if I run the exact same query using datetime parameters, a Clustered index scan is performed instead of an index seek.

DECLARE @AppDate DateTime

SET @AppDate = '20060701'

SELECT * FROM Appointments WHERE AppointmentDate >= @AppDate and AppointmentDate < DATEADD(day, 1, @AppDate)





Any ideas why it would do this?

View 4 Replies View Related

Difference Between Index Seek &&amp; Index Scan &&amp; Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

Clustered Index On Nvarchar Column Or Int...

Jan 25, 2007

Users can approach their userprofile on my site using: www.mysite.com/name=peterName is a unique value within my database (db type: nvarchar(50))Now, I have created a clustered index on the username column.However, IMHO its faster to create a clustered index on the (also unique) usercode column since that is of type int.BUT since a user can approach my site based on username I feel that I HAVE to live with this setback in performance....Is that true or is there a better way to solve this issue?

View 1 Replies View Related

Select A Value From Column Which Is Named Index

Dec 17, 2011

I want to Select a value from a Column which is named "Index" - and I don't want to change the name naturally. Is there a way to get a value without mentioning the Column name itself, rather the column number? "Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax."

Occurs when I try to simply..
-> SELECT Index FROM Table WHERE Name like 'xyz' <-

View 2 Replies View Related

SQL 2012 :: Column Not Covered By Index?

Jan 15, 2015

We have a large table with many columns and many indexes. One poorly performing query is having to do a key lookup when the where clause includes a particular column with no covering index.

Are you generally better off adding a new index or adding the column to an existing index ( included columns )Column: LAST_STATE_RESPONSE_CODE

The Query Processor estimates that implementing the following index could improve the query cost by 88.9332%.

*/
/*
USE [ database name]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SERVICE_REQUEST] ([BUSINESS_PROCESS_STATUS],[[color=#F00]LAST_STATE_RESPONSE_CODE[size="3"][/size][/color]],[CONCRETE_TYPE])
INCLUDE ([LIENHOLDER_PERFORMING_LIEN_FILING_ID],[MAKE],[YEAR],[MANUFACTURER_ID],[CLIENT_ID])
GO

View 4 Replies View Related

SQL 2012 :: Column For Clustered Index

Apr 16, 2015

Is it always the best practice to have the partition column also as the column for clustered index?

View 2 Replies View Related

Making Covering Index Non-key To Key Column

Jan 2, 2015

I have created a covering Index like below,

----------------------------------------------------------------
CREATE NONCLUSTERED INDEX [INX_NONCLUS_COVERING_INVBAK] ON [dbo].[Invoices_Bak]
(
[SellerID] ASC, -- Key column
[BuyerID] ASC -- Key column
)
INCLUDE (
[ReceivedDate], --Non key column

[Code] ....

Above index has created on production environment, Now i would like alter above index that is from non key column to key column

You can find a non key column called [ReceivedDate] that is available in include part, i need to make this column to Key column.

How to do this proper way without affect production environment that is using unwanted disk size and more..

View 2 Replies View Related

How To Replace A 3 Column Composite Index

Mar 27, 2008

How do I improve a 3 column, composite clustered index on a large table when the developer insists there is no other way to achieve uniqueness? They say a uniqueindentifier column will not work.

View 5 Replies View Related

Composite Index And Column Order

Jun 9, 2006

Hi,I created a composite index (lastname, firstname). I know the followingqueries will use this index:WHERE lastname = ...WHERE lastname = ... AND firstname = ...Also this won't use the index:WHERE firstname = ...But how about: WHERE firstname = .. AND lastname = ...And why?Thanks a lot,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

View 13 Replies View Related

Passing Second Column Of Table As Index

Aug 1, 2007


We are working on C++ in eVC++ 3.0 environment (CE 3.0) with SQL CE 2.0.

We are trying with IRowsetIndex:: Seek method to access the data from the database.
We are facing problem when we try to access data from the table having composite index other than the first column.

For example:
The table ITEM contains the following columns:
1. ItemCode
2. PcNo
3. SubPcNo
4. BrandNo
5. DescText
6. ProductionKind
7. ProductionState
8. ClearingState
9. ST
10. TS

I€™ve two indexes for this table where the two indexes are
1. ItemKey1 on ITEM (ItemCode).
2. ItemKey2 on ITEM (PcNo, SubPcNo, BrandNo).

If I mention ItemKey1 as the index then the Seek method works perfectly.

But if I mention ItemKey2 as the index, then I€™m getting the error as €śDB_E_BADBINDINFO€?.

When we visted the MSDN for this problem we noted that:
1. When you use the Seek method on multiple fields, the Seek fields must be in the same order as the fields in the underlying table. If they are not, the Seek method fails.
2. When passing key values to an index rowset, the consumer performs these actions only for the number of key columns specified in the cKeyValues argument in IRowsetIndex:: Seek.

So we had the composite index such that columns of a composite index are physically aligned next to each other as in the table.
When the first column of the index is the first column of the table as in index ItemKey1 we found that the seek method works perfectly.
If we pass the index ItemKey2, we are again getting the error €śDB_E_BADBINDINFO€?.

What else have we missed out while passing the keys in IRowsetIndex:: Seek?

Thanks in advance.

Regards,
Sasi.

View 5 Replies View Related

Partitioned Index Column Order

Aug 22, 2007

We are using partitioned unique indexes on partitioned tables. When the Unique Index is built, should the column the index is partitioned by be the top (leftmost) column in the index? While this violates cardinality, it makes sense (at least to me) that the first thing the query execution would do is figure out which partition(s) contain the result set, then filter from there.

What do you guys think? Is there any documentation on optimizing partitioned indexes?

View 1 Replies View Related

Do You Include Clustered Index Column In Other Indexes?

Apr 3, 2002

I have a database where records are Inserted by an external process.
There is no updating or deleting of the data once inserted. The table in
question has a Clustered Index on the Machine_ID (integer) (data is from
manufacturing processes). Each record bears a start and end time. Most
queries involve the Machine, a time span (start time between to points in
time), the Downtime Cause, and the Running Mode.

I want to add an index on the Start Time, the Downtime Cause, and the
Runtime Mode.

My question is: should this new index also contain the Machine_id column
or does the existence of the Clustered Index already on that column negate
its need in the new index?

RC - Dedicated to only creating original mistakes!

View 2 Replies View Related

SELECT Columns By Column-index NOT By Columnname!

Nov 20, 2005

Hello all,

how can I select one or more columns from a table by column-index and NOT by columnname?

e.g.:

SELECT tbl1.[1], tbl1.[2], tbl1.[3] FROM Orders AS tbl1

and NOT like this:

SELECT tbl1.OrderNo, tbl1.ProductNo, tbl1.Price FROM Orders AS tbl1

Is that possible in MS-SQL 2000?

Thanks a lot in advance

kind regards

Otto

View 11 Replies View Related

SQL 2012 :: Column Store Index Refresh

Aug 20, 2015

When we use Partition switch and load the data to a table, can we refresh the indexes for specific partition, so that we don't need to rebuild / refresh for complete is this possible ?

View 1 Replies View Related

Creating Non Clustered Index On DateTime Column

Dec 24, 2014

I have 5 million rows of table, and going to create Non Clustered Index for Datetime values column. Creating Non clustered Index on Datetime value column will affect performance or not.

View 4 Replies View Related

Transact SQL :: Index Fragmentation For Identity Column?

Jun 26, 2015

In my database all columns have Identity Value as a PK.

Now today I check Index Fragmentation I saw that many cluster and Non cluster Index are avg.Fragmentation is around 99 % I thought that in Identity column record is always inserted at bottom so there is no fill factor assigned to it.

So in this case Do I need to set Fill factor for Cluster and Non Cluster Index?

If Yes Then For PK How much - 95 % or what? and same for Non cluster or It should around 85 to 90

View 4 Replies View Related

Composite Clustered Index - Column Order

May 29, 2007

Want to check my thinking with you folks...

I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.

Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000

C3 is effectively number of seconds since 01/01/1970.

The usage of the table is typically, insert a row, do something else, then update it.

Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.

My thinking is that this composite index is better ordered C1,C2,C3.

My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.

Welcome your thoughts.

View 3 Replies View Related

Transact SQL :: Tables With FK Column Without Non-clustered Index

Oct 22, 2014

I need to find out all the tables in database, which has FK columns and don’t have any Non-clustered index on them.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved