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