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
ADVERTISEMENT
Apr 2, 2007
Hi,
Trying to optimize a query but not sure what to do. I have this query on which I ran an exec plan,
SET NOCOUNT ON;
SELECT qaTestSuite.TestSuiteID, qaTestSuite.TestSuiteStart, qaTestSuite.TestInterface, qaTestSuite.TestVersion, qaTests.TestMachine, qaTestSuite.TestClientMachine, qaTests.TestLogin, qaTests.TestLabel,
qaTestSuite.TestCLPs, qaTestSuite.TestSuiteEnd, qaTests.TestID, qaTests.TestIDInternal, qaTests.TestStart,
qaTests.TestName, qaTests.TestTier, qaTests.TestNo, qaTests.TestWFBCalled, qaTests.TestWFBTime,
qaTests.TestSearches, qaTests.TestSearchesTime, qaTests.TestResult, qaTests.TestEnd, qaTestMssgs.TestMssgsID,
qaTestMssgs.TestMssgTime, qaTestMssgs.TestMssgType, qaTestMssgs.TestMessage, qaTestSuite.TestMode
FROM qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID INNER JOIN
qaTestMssgs with(NOLOCK) ON qaTests.TestID = qaTestMssgs.TestID
order by qaTestSuite.TestSuiteStart DESC
and it gives me the following results:
Use a Bookmark (RID or Clustering Key) to look up the corresponding row in the Table or Clustered Index.
Physical Op: Bookmark Lookup
Logical Op: Bookmark Lookup
Est. Row Count: 128
Est. Row Size: 4760
Est. I/O Cost: 0.368
Est. CPU Cost: 0.000141
Est. Execs: 1.0
Est. Cost: 0.368888(89%)
Est. Subtree Cost:.415
Argument:
BOOKMARK:([Bmk1004]), OBJECT:([QAMaster].[dbo].[qaTestMssgs]) WITH PREFETCH
I have no idea what to do with that. Anyone have any clues? What I found online was that I should make a Covering Index, but I didn't find
any patterns on how to do that. Any one have ideas of how to do this?
Thanks very much for your help!
--PhB
View 14 Replies
View Related
May 15, 2008
I have a table there is a query which has bad performance. This query normally can not use index because a lot of 'IS NULL','OR','LIKE' USED in where clause. I want to create a covering index for this query so it can use index scan only rather than Processor had to look up the row columns it needs
from a table or a clustered index.
current there are two index apply on that table:
CREATE UNIQUE CLUSTERED INDEX [Person_RecordID_UIX] ON Person
(
[RecordID] ASC
)
go
ALTER TABLE [Person] ADD CONSTRAINT Person_PK PRIMARY KEY NONCLUSTERED
(
[EntityID] ASC
)
How to add a covering index for the columns such as familyName,givenName, dob. All of these columns are not too much high selective.
Can I do like the following :
--create third index
CREATE NONCLUSTERED INDEX [Person_Cover_IX] ON [dbo].[Person]
(
[EntityID] ASC
)
INCLUDE ( [FamName],
[FirstName],
[DOB],
[Gender]
)
Or DROP Current index on entityid and recreate it with include clause.
What's the different between these two.
Thank you.
View 6 Replies
View Related
Dec 5, 2007
Hello,
i have a database with about 300.000 entries. The database gets about 30 new entries every day. The Database has an FulltextIndex on several columns. This FulltextIndex will be updated every night.
But now i have found out, that the fulltextsearch doesn't work anymore for all entries that where added after April 2006.
When i for example make following sql-statementSELECT id,date FROM MyTable WHERE (CONTAINS((columnA),' "mykeyword" '))
i only get results that have a date after April 2006 (although there are matching entries after that date).
What can the reason for that be?
According to Management Studio the last Update of the FulltextCatalog has been made on 1st of December 2007. Everything looks normal and I didn't find any logs that are saying that there has been any errors.
Where do I have to look to be sure if the FullTextIndex does work?
Specs:
SQL Server 2005
Microsoft SQL Server Management Studio 9.00.1399.00Microsoft Analysis Services-Clienttools 2005.090.1399.00Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)Microsoft MSXML 2.6 3.0 6.0 Microsoft Internet Explorer 6.0.3790.3959Microsoft .NET Framework 2.0.50727.832Operating System 5.2.3790
greetings
Klaus
View 3 Replies
View Related
Aug 9, 2006
Hi,
When I create a unique constraint, SQL Server automatically creates an index on this constraint. So when I run the following...
ALTER TABLE PersonsProjects
WITH NOCHECK ADD CONSTRAINT NoDupes UNIQUE NONCLUSTERED (PersonID, ProjectID)
...SQL Server will create a composite index on PersonsProjects called NoDupes on PersonIDand ProjectID. Thing is, I need this index to include a third column Status since most queries use this column in conjunction with PersonID and ProjectID. If there was no index on this table, I would have created it as follows:
CREATE UNIQUE INDEX NoDupes ON PersonsProjects (PersonID, ProjectID) INCLUDE (Status) WITH IGNORE_DUP_KEY
But this won't enforce the unique constraint on PersonID and ProjectID when performing inserts and updates. Is there any way of creating a unique constraint with an included column?
I would rather not have two indexes...
NoDupes: PersonID,ProjectID
New Index: PersonID,ProjectID INCLUDE Status
...so I'm trying to determine what other options that might be available...please advise.
Thanks much.
View 10 Replies
View Related
Apr 29, 2008
I have one query that looks like this.
DECLARE @propname
SET @propname = NULL -- can be Null or have wild card values
SELECT col1, col2, col3, propertyname FROM testtable where col1 = 1 and col2 = 2 and col3 = 3 and (@propname IS NULL OR propertyname LIKE @propname)
col1, col2, col3 are part of a clustered index
propertyname is a nonclustered index
This is the predicament. If the "@propname is NULL" is first in the OR statement then the query will use the clustered index for finding the record. If I put "@propname is NULL" last then it uses the propertyname index no matter what. So I either get full index scans if NULL is ever used on property names or I get consistent two second long searches on the clustered index. Any way to have the best of both worlds? Or do I have to divide up my query into more stored procedures?
THANKS
View 11 Replies
View Related
Mar 5, 2004
Hi,
I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.
I tried
alter table <table_name>
alter column <col_name> int Identity (1,1)
but it fails.
Ajay
WORD4LIFE
(http://www.word4life.com)
View 2 Replies
View Related
Jul 26, 2002
Can anyone help me retrieving that result.
take an example
EXEC-ID JOB-CODE
0001 20
0001 63
0001 03
i want result in the following format:
EXEC-ID JOB-CODE1 JOB-CODE2 JOB-CODE3
0001 20 63 03
View 1 Replies
View Related
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
Mar 4, 2008
I have a table which has single column like this.
REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT
I want to put this in a new table in individual columns
Col1 col2 col3 col4 col5 col6 col7 col8 col9
REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT
How to do this.
Thanks.
View 2 Replies
View Related
Aug 1, 2006
Hi,
I asked the similar question before but I have again some doubts about covering indexes.
Besides, tomorrow I have a Microsoft MCAD 70-229 exam so please help me.
In here,
SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate
we create composite nonclustered index for both OrderID and OrderDate column.
Leftmost is OrderID.
So
when our first research is happening(Where clause), the only
nonclustered index which is used is OrderID index. And then, when we
pass through the second search(ORDER BY clause), OrderDate index become activated.
So
we can say that the seleection of indexes in composite indexes is
determined according to the situation of the query at that time.
Hence, is this all correct ?
Best wishes,
Mert
View 1 Replies
View Related
Mar 12, 2008
I plan to use covering indexes as my core tables are really huge. I wanted to check if the new feature "Included Columns" for an index is beneficial in the following scenario:
If I have a non-clustered index within the limitations of 16 fields and under 900 bytes as opposed to having 1 field with 15 included fields for the same - what is the difference in performance? Is there any advantage in using the included columns?
View 17 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
Jan 16, 2008
I am working with a dynamically populated multi value parameter drop down list. In the case where there is only one value in the list, the horizontal scroll bar (due to the length of the value) covers up the first, and in this case only choice. The users cannot see it. This problem is only when running in report manager. It works ok in Visual Studio.
As a work around, I can add a dummy row, which then at least the users can see one row of data and then scroll down but, I would rather not do that, if there is another solution. Another option would be to set the default on the parameter so it is automatically selected but, in the case where there are many values, I do not want a default set.
Your help would be greatly appreciated.
Thanks!
View 5 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 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
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
