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
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
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.
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 RelatedHello 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 RelatedHi,
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
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?
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 ?
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.
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
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 ?
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.
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 RelatedCan 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 RelatedIn 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 RelatedI'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?
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
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 RelatedI 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' <-
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
Is it always the best practice to have the partition column also as the column for clustered index?
View 2 Replies View RelatedI 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..
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 RelatedHi,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
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.
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?
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!
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
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 RelatedI 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 RelatedIn 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
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.
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