SQL Server 2005; Unique Constraint, Covering Index Question
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.
We are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ?
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes
When I add a unique key constraint to column in SQL 6.5 why does it alsocreate an index. e.g. In the table subaccounts I added a unique keyconstraint for the column login and SQL creates an index with the nameUQ_SubAccounts_2__19 (UKC).Does this also mean that there is no need to create an index for thiscolumn?thxMansoor
I am creating a new SSIS package where there is a flat file (CSV) I am importing. Well, there will be an end-user using a web UI to initiate the import of the file. However, this flat file is generated from another company and then uploaded to our network.
This flat file has the potential to have duplicate rows that would have already been imported at a previous date. With the constraints in the table, we have guaranteed that there will not be duplicates added, but the SSIS package fails immediately upon attempting to insert a duplicate row - and the bulk update is rolled back.
What I need to be able to do is load all of the rows that are not duplicates into the table. I am guessing that with my current approach, this is not possible. I am using a Data Flow task that converts the data in the flat file and then performs a bulk copy to load the data into the table.
How can I either ignore duplicate rows, or otherwise gracefully handle this data import?
All,What's the difference between a unique contraint and unique?sementically, if you want a column contain unique values, it is acontraint. And an index is for searching/sort. The questions are:1. Does a unique constraint interally use unique index?2. If Yes to #1, I DO NOT need to create an index for search/sortpurpose, right?3. If Yes to #2, What's better?4. Also for Primary Key column, it is actually a special uniquecontraint. Not need to create index on PK column for searching/sorting,correct?5. Also for FK contraint, no need to create an index forsearching/sorting?ThanksJohn
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?
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
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.
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
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Is there a simple way to find properties of an index for a table? Specifically I am looking for the unique property. I currently have code to to do this, however I feel like there must be some easier way.
I am using .net 2005 and SQL 2005 coding in c#. Thanks for any help!
Code Snippet
_conn.Open(); SqlCommand cmd = new SqlCommand("sp_helpindex", _conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@objname", "TABLENAME")); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(results);
foreach (DataRow row in results.Tables[0].Rows)
{
ArrayList names = new ArrayList();
DataTable dtUnique = new DataTable();
SqlCommand cmd2 = this._conn.CreateCommand();
cmd2.CommandText = String.Format("SELECT INDEXPROPERTY(OBJECT_ID('TABLENAME'), 'INDEXNAME', 'IsUnique') AS isUnique");
i just got an error in my application when i tried to do an insert stating that there was a "unique key constraint". I have the database open with sql server management studio and i need to find out how to view the constraints?
How do i navigate to see the constraints on a table? Thanks
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error: A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle But the only PK on this table is RegTitleID.
The table structure is: [RegTitleID] [int] IDENTITY(1,1) NOT NULL, [RegTitleNumber] [int] NOT NULL, [RegTitleDescription] [varchar](200) NOT NULL, [FacilityTypeID] [int] NOT NULL, [Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number. Has anyone else experienced this?
hi team, .Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
What is the simplest way to add a unique constraint on a field of type varchar(7) that can allow any number of <NULL>'s?
I only want to ensure that when this field is updated, it is updated with a value that has not been used.
IF EXISTS (SELECT Project FROM tbProjects WHERE Project = @cProject) RAISERROR('Project number already used!',16,1) ELSE UPDATE tbProjects SET Project = @cProject WHERE ProjectID = @iProjectID GO
Also, I cannot allow the user to chante the project field value once it is set.
