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
ADVERTISEMENT
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
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
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
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
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
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
Aug 26, 2005
Is there any advantage to doing this:ALTER TABLE testtable ADDCONSTRAINT PK_sysUserPRIMARY KEY NONCLUSTERED (UserID)WITH FILLFACTOR = 100,CONSTRAINT IX_sysUserUNIQUE NONCLUSTERED (UserID)WITH FILLFACTOR = 100GOover just having the primary key? Does having both an index and aprimary key add anything?thankschris
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
