Hoe to Find Duplicate values?

I have a table with no index, When i try to create one to Check data
integrity on 3 column that suppose they are unique collection
(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
I know i may ignore duplicate values but i want to find it to delete it..
So How could i write query to find duplicate rows
Select Court_ID,Case_No,Case_Date from Case Where...'This script had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23FjhHd17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a table with no index, When i try to create one to Check data
>integrity on 3 column that suppose they are unique collection
>(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
>|||Islamegy wrote:
> I have a table with no index, When i try to create one to Check data
> integrity on 3 column that suppose they are unique collection
> (Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete
> it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
select Court_ID,Case_No,Case_Date, count(*)
group by Court_ID,Case_No,Case_Date
having count(*) > 1
Kind regards
robert

Hmm - unique id is in Binary data - how do I query against it?

Hi - I've got a table in SQL server that has its unique ID field as binary data. I have a gridview displaying data from this table and I have set the datakey of this gridview as that binary data field.

I have a 'Select' ciolumn in my gridview that, when selected, will display more details from the selected record. The problem is, how do I pass the selected id back to sql server bearing in mind that it's binary data? Here's what I'm doing at the moment:

I have a function in my data accsess class that queries the database:

Public Function getDetailsById(ByVal Id As System.Byte) As DataSet

Dim con As New SqlConnection(conStr)
Dim cmd As New SqlCommand("SELECT * FROM tableName WHERE Id=@.Id", con)

cmd.Parameters.Add("@.Id", SqlDbType.Binary)
cmd.Parameters("@.Id").Value = Id

Dim ds As New DataSet
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(ds)

Return ds

End Function

And then I call this function from my page with the gridview using:

Dim da As New myDataAccess
Dim ds As New DataSet
ds = da.getDetailsById(GridView1.SelectedDataKey.Value)

But I get the error:

Conversion from type 'Byte()' to type 'Byte' is not valid.

Any ideas where my data/code is going wrong? I'm not sure how to pass this sort of data around?

Thanks

loydall:

Hi - I've got a table in SQL server that has its unique ID field as binary data.

Not trying to be funny... but making your primary key a binary is where you are going wrong.

I know I've queried off of binary fields before, but I don't have any of that code infront of me now.

|||

Thanks but it's not my db so I have no control over how the ID is stored...

I've fixed it anyway - I just need to use system.byte() rather than system.byte as a parameter in my function - it obviously needed to treat the data as an array...

|||

Make sure you're getting the correct records back... I seem to remember that SQL will store a binary as 0x00000047 where .Net will see the same value as 0x47000000.

|||

You are using the wrong ADO.NET command object, you should use ExecuteScalar. Try the link below for complete sample code from Jeff Prosise Programming .NET. The code is in C# but it shows you the correct way to use ExecuteScalar. Hope this helps.

http://kurinjikumaravel.tripod.com/Asp.Net/AspWintellect.txt

High usage of RAM on SQL2k

Hi

I have an sql server with very high usage. The web which is connected to the sql server has approx. 1000 unique visitors every time.
The database is optimized and the site goes really fast now.

But a problem we've had since the beginning is that SQL Server constantly uses 1.8 GB of RAM. Never less, never more.

Do you have any idea on how to decrease the usage of RAM. I am sure that this high usage is necessary.check your logs,if they are more truncate them,
may be it helps you.|||But arent the log files truncated during the backup?|||Logs?

run sp_configure and tell me what the results are for min server memory.|||min server memory (MB) 0 2147483647 0 0