Distinct Vs. Unique Index In Stored Procedure

Jun 10, 2004

Hi All,

I'm struggling with this one.
We have a Street database that can contain multiple entries for one record in our main table. We don't wish to pass duplicate rows back to our end user so I wish to get distinct recno's (the unique identifier for the main table) back from our Street Table.

We have a stored procedure that currently uses Select distinct Recno but I think this is slower than if I used a unique index with ignore_dup_key. Problem is that when I alter the stored procedure it looks like the functionality of the LIKE function is somehow altered. For example Like 'King%' fails to return anything but Like 'King St%' does. I suspect it is the fact that the error "Server: Msg 3604, Level 16, State 1, Procedure" is being generated. How do I code around this server side.

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

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).

The statement has been terminated.

View 6 Replies View Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

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.

View 8 Replies View Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

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?

View 1 Replies View Related

Stored Procedure - Select Distinct And Inner Join

Nov 16, 2006

I've been trying to get this to work right.
The db table has 3 fields: id, vwr, and reqType.
I need all DISTINCT vwr's from the table. (vwr's can repeat)
This gives me all rows, not distinct...
 select distinct d.id, d.vwr, d.reqType from tblVWR AS dinner join tblVWR ton d.vwr = t.vwr
Any suggestions?
Thanks,
Zath

View 2 Replies View Related

Using Multiple Distinct Queries Inside Single Stored Procedure

Feb 21, 2008

Hello,

I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella.

For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So:

IF @QUERY_ID = 1
BEGIN
SELECT [whatever]
FROM [tbl1]
WHERE [conditions]
GROUP BY [something]
ORDER BY [somethingelse]
END
ELSE
IF @QUERY_ID = 2
BEGIN
SELECT [whatever]
FROM [tbl2]
WHERE [conditions]
GROUP BY [something]
ORDER BY [somethingelse]
END
END

I hope that makes sense. Thanks in advance.

View 7 Replies View Related

Catching Violation Of UNIQUE KEY Constraint In Stored Procedure

Dec 20, 2005

Dear All,

I have a stored procedure which bulk inserts records into a table based on a passed in variable that contains comma separated values of record Ids.

However I have a constraint on the table ensuring that value-pairs in 2 columns must be unique (as a person can not be twice on the same project)

Since I insert the passed in person Ids in a loop, I’d like to catch if this constraint has been violated and skip that specific cycle if it has but do commit the rest.

Not sure if this can be done, and if yes could someone let me know the SQL syntax and structure please?

Am I explaining this clearly?

Thanks in advance all comments are much appreciated!

View 2 Replies View Related

Variable Names Must Be Unique Within A Query Batch Or Stored Procedure.

May 4, 2007

hi ,

i got this error ... i have tried various scenarios.. nothing works for me... can any one gimme the proper guidance...



The variable name '@CCSID' has already been declared. Variable names must be unique within a query batch or stored procedure.

thanx in advance

raj

View 5 Replies View Related

Unique Index Vs Unique Constraint

Mar 7, 2001

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 ?

Which one do one use ?

thanks
sonali

View 4 Replies View Related

Unique Constraint Vs Unique Index

Jan 20, 2006

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?

View 2 Replies View Related

Unique Index Vs Unique Constraints

Mar 26, 2008



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 ?

View 12 Replies View Related

What 's Difference Between Unique Key And Unique Index

Nov 13, 2007

What 's difference between Unique key and unique index in SQL server 2005?

View 9 Replies View Related

Return An Unique Identifier To An ASP.NET Page To Send It As A Parameter Into Another Stored Procedure

May 1, 2007

Hi !
I have a problem with the unique identifier and  don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
Dim cmd As New SqlCommand
cmd.CommandText = "Insertar_Contacto"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
 Thanks!!
 

View 2 Replies View Related

Stored Procedure Won't Use Index

Jul 20, 2005

Hi,We're using SQL Server 2000.A very basic query on a 5 million row table would not work. If wehard coded the one input parameter, the query used the foreign keyindex and produced an instantaneous result. If, however, we pass theparameter in as a parameter, the query doesn't use the index and takesforever. E.g.Declare @ID intSET @ID = 17697Select top 1 AccountID from tblAccounts where GroupID = @IDWe have fixed the problem by using an index hint to force the query touse the index. However, my concern is - why is the index notautomatically used when we remove the hardcoded ID and repalce it witha parameter? Is it a problem with the index? We do not use indexhints as a coding standard, so will this happen to other storedprocedures in our DB?Any advice about how and why this occurs would be much appreciated.Trudie

View 3 Replies View Related

SQL DISTINCT UNIQUE QUERY

May 4, 2007

I have a problem with a SQL SELECT query. As far as my research goes i figured out that UNIQUE is used when you have one column that you whant unique and DISTINCT is used when you have more than one column that you want to all be unique. But i have a query where i want just some of the columns to be unique and some not. Here is the full query (It's in a stored procedure):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_ExecSum_Combined_4fields](@BondGroupID int)
AS SELECT DISTINCT
SUM(dbo.Bond.LoanAmount) AS SumOfBondValue, COUNT(dbo.Bond.BondID) AS CountOfBond, dbo.ProgressStepType.ProgressStepType
FROM dbo.BondGroup INNER JOIN
dbo.TransactionTable ON dbo.BondGroup.BondGroupID = dbo.TransactionTable.BondGroupID INNER JOIN
dbo.Bond ON dbo.TransactionTable.TransactionID = dbo.Bond.TransactionID INNER JOIN
dbo.ProgressStep ON dbo.TransactionTable.TransactionID = dbo.ProgressStep.TransactionID INNER JOIN
dbo.ProgressStepType ON dbo.ProgressStep.ProgressStepTypeID = dbo.ProgressStepType.ProgressStepTypeID
WHERE (dbo.BondGroup.BondGroupID = @BondGroupID)

GROUP BY dbo.ProgressStepType.ProgressStepType, dbo.ProgressStep.ProgressStepID
HAVING (dbo.ProgressStepType.ProgressStepType = 'AIP (Approval in Principle) received') OR
(dbo.ProgressStepType.ProgressStepType = 'Grants') OR
(dbo.ProgressStepType.ProgressStepType = 'Attorney') OR
(dbo.ProgressStepType.ProgressStepType = 'Feedback received from bank')
ORDER BY dbo.ProgressStepType.ProgressStepType

*********
With the DISTINCT it gives me a full set of unique values but what i want is this: Every CountOfBond may have more than one SumOfBondValue and they may be the same, but if there is more than one dbo.ProgressStepType.ProgressStepType the query should only read it once. In other words the dbo.ProgressStepType.ProgressStepType is a unique value and the rest not...

Please help, my boss is on my case :)

View 14 Replies View Related

Update ONLY When Unique Or Distinct

Nov 7, 2007

Im running updates on a table. I would like to only update the table with new records. Right now, Im running a query that will only update the records that were added yesterday. Here is my code:

FROM GRAB where BKDATE = convert(varchar(8), getdate()-1, 112)

I would like to Update like this (incorrect syntax):

FROM GRAB where FCN is distinct

I know the above is incorrect.... what would be correct?

Thanks again

View 15 Replies View Related

Stored Procedure For Relationship And Index

Dec 23, 2003

Can someone provide a sample of a stored procedure to add a 1-M relationship between two tables and how to add an index to a column?

Thanks for any help?

View 3 Replies View Related

Distinct Count Fall Under Unique ID

Feb 19, 2014

Aim- Need to count how many [FDMSAccountNo] fall under a unique parentid

This is my query

SELECT [FDMSAccountNo]
,ParentID
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878028271889'

Which produces the following, The number of fdmsaccountno under parentid is two. Its two because one of the fdmsaccountno is = to parentid

FDMSAccountNo ParentID
878028271889878028271889
878028272887878028271889
878028273885878028271889

Desired result

Parentid #_of_outlets
878028271889 2

View 5 Replies View Related

Distinct, Unique, Group By, 'Footing'?

Jun 13, 2007

I created an ouput text file that was perfect until specs were revised today to add quarterly tax and deduction summations for each employee. (Expected turnaround time for new spec: today!)

So my employee record now has 8 records (which I expected) because I had to join it to the pay_summary table. Like this:

SELECT
COALESCE(CONVERT(char(1),e.record_status),'')
+ COALESCE(CONVERT(char(4),'97 '),'')
and many more fields
from employees as e
JOIN emp_taxes as t on e.employee_no = t.employee_no
LEFT OUTER JOIN pay_summary AS p ON e.employee_no = p.employee_no

Where
dateadd(d, 0, datediff(d, 0, p.dated)) BETWEEN '20061231' AND '20070401'

and e.record_status not like 'D'
and t.tax_authority_type = 'F'
and e.company_no = '2' and e.employee_no = t.employee_no

order by e.employee_no

There are 6 or so fields I intend to sum as records to go into the
file. In another language, in order to get one record per employee_no, the syntax would be like this:

sort on employee_no
footing at employee_no
report field_a field_b field_c subtotal field_e field_f subtotal

in which case fields c and f are numeric and summed from the multiple pay_summary records.

What is the (most efficient) SQL command to give only one record per
employee while summing all the detail records?

I can't thank you guys enough for this forum.

Lisa

View 9 Replies View Related

How To Use Distinct To Select Unique Records But Without Sorting ?

May 30, 2008



Hi

I need to select unique records from a Table. I'm using Distinct Keyword for this purpose. But the result set is showing distinct records in sorted order. I do'nt want to sort records. I need the order in which they are created in table.

Please suggest me the solution for this problem.

Thanks

Regards

Avinash Vyas

View 4 Replies View Related

Transact SQL :: Creating A View Using DISTINCT And Not Getting Unique Results?

Sep 21, 2015

I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:

SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index
FROM dbo.csr_standards_cmsars
ORDER BY ARSFamily

 but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups).  The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?

View 2 Replies View Related

Unique Index

Mar 29, 2004

Just found out that creating a unique index does not create a unique constraint, but creating a unique constraint creates unique index.

But effectively they do the same thing.

View 3 Replies View Related

Unique Index

May 8, 2008

How to findout whether a Index is unique or not?

------------------------
I think, therefore I am - Rene Descartes

View 2 Replies View Related

Unique Index

Mar 26, 2007

if this question is inappropriate here, I apologize (it's at least obliquely related). I have been using ssno as a unique key in a datawarehouse I have been working on because all of the component systems have had it. I now have a database to add where ssno is not available. I have first, last address, city, state,zip and dob.

Question is, how to construct a unique identifier from those components. If not unique, then at least usable?

Again, if this post is wrong here, I apologize

Thanks for any input

Walter

View 1 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

Better Performance With Unique Index?

May 21, 2002

I have read that you get better performance with unique indexes rather than non-unique indexes.
I have experimented with this in SQL 2000. I have two identical tables (with about 250000 rows each) with a 12-character unique column. In one table I define it as a regular index and in the other I define it as a unique index. No matter what I try I get identical performance, and the query optimizer shows an identical plan.
I even tried clauses such as
WHERE 1 < (SELECT COUNT(*) FROM TheTable
WHERE key_column = OtherTable.key_column)
which should obviously return nothing if TheTable.key_column is unique. However the query still ran a long time no matter if the index is unique or not.
I have also tried a unique constraint instead of a unique index and got the same (non)results.
Can anyone come up with an example where creating a unique index actually makes a performance difference?

View 1 Replies View Related

UNIQUE INDEX If Not NULL

Dec 17, 2006

Hello !

for MS SQL 2000
how can i set an unique index on Serial column but only if Serial IS NOT NULL

CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY]

i can have 100 rows with a NULL Serial


thank you

View 9 Replies View Related

Unique Index On 2 Columns?

Apr 19, 2013

I find to be able to have multiple NULL entries in the following constraint:

Code:

CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;

But is not possible to check multiple columns in "WHERE".

I using SQL Server 2012 Express

View 3 Replies View Related

Unique Constraint/index

Feb 12, 2004

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

Thanks in advance

View 7 Replies View Related

Unique Constraint/Index

May 7, 2008

What is the difference between unqiue constraint and unique index? What are the pros and cons? Are they interchangable?

------------------------
I think, therefore I am - Rene Descartes

View 4 Replies View Related

Unique Index In Sql Server

Mar 4, 2008

What is unique index in sql server 2k?
plz some one help me out, thanx

Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View 2 Replies View Related

Creating A Unique Index

Jul 20, 2005

HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan

View 3 Replies View Related







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