September 16, 2013 at 3:46 am
Hi,
This one works;
;WITH Deleter AS (
SELECT
ID,
rn = ROW_NUMBER() OVER(ORDER BY Postcode,BuildingName,housenumber,SubBuilding,County,Town,
LocalName,PostOutCode,PostInCode,Throughname,Throughdesc,poboxno,BusinessName,locality)
FROM PostCodesAndAddressesBt
)
SELECT *
FROM Deleter
WHERE rn > 1
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 16, 2013 at 3:57 am
Thanks Steve, that did work. What exactly does this show?
September 16, 2013 at 4:14 am
This should give you a result set - that shows where the records are NOT unique - ie. Greater than One
So these will need to be made unique for your INDEX to be created.
Hope that helps
Steve
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 16, 2013 at 4:16 am
That's great Steve, thanks for getting back to me. It's returned 2 integer fields, I know the first one is the row number. I still can't get my head around the second set of numbers, what exactly do they mean?
September 16, 2013 at 4:27 am
Hi Mick,
Sorry - looks like the results show the ID and just a counter - I will have another look at this. Not sure what the scripts were going to show - but basically...
What are you after ?
Do you just need to identify the NON-Distinct records so that you can then make them UNIQUE ? for your "BIG Index!" ?
Cheers
Steve
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 16, 2013 at 4:34 am
Basically I've a table which I'm filling in with UK PostCodes, I've now realised I should have made the table unique to stop duplicates. The problem I faced was where some of the existing data has nulls where not every field is filled in. I can't now put primary keys on some of the fields. So I was wondering if there was another way of achieving a non duplicate database.
September 16, 2013 at 4:41 am
Right - so we could start by populating the "NULLS" with "Blanks" ?
This can be easy - just do ;
Update Table
set field1 = ' ' where field1 is null
you could end up with duplicates - one with NULLs and the other Blank - so this would tidy the table first.
Then its a case of identifying.
But looking back at earlier threads - you could create a new table ( with FULL index ) - then do a INSERT into with SELECT DISTINCT ?
Regards
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 16, 2013 at 4:45 am
Mick,
sorry - script for above .. ( well start of )
Update PostCodesAndAddressesBt
SET Postcode = 'N/A' Where Postcode is NULL
Update PostCodesAndAddressesBt
SET BuildingName = 'N/A' Where BuildingName is NULL
Update PostCodesAndAddressesBt
SET housenumber = 'N/A' Where housenumber is NULL
etc..
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 16, 2013 at 4:52 am
Since starting this thread I have actually changed the nulls into spaces, I'm intrigued by your suggestion "But looking back at earlier threads - you could create a new table ( with FULL index ) - then do a INSERT into with SELECT DISTINCT ?" how would I achieve that?
September 16, 2013 at 5:17 am
something like this -
insert into NEWPostCodesAndAddressesBt
(
Postcode varchar(10) ,
BuildingName varchar(200),
housenumber varchar(20) ,
SubBuilding varchar(100) ,
County varchar(100),
Town varchar(100) ,
LocalName varchar(100) ,
PostOutCode varchar(100),
PostInCode varchar(100),
Throughname varchar(100),
Throughdesc varchar(100),
poboxno varchar(100),
BusinessName varchar(200),
locality varchar(200)
)
select
DISTINCT
Postcode varchar(10) ,
BuildingName varchar(200),
housenumber varchar(20) ,
SubBuilding varchar(100) ,
County varchar(100),
Town varchar(100) ,
LocalName varchar(100) ,
PostOutCode varchar(100),
PostInCode varchar(100),
Throughname varchar(100),
Throughdesc varchar(100),
poboxno varchar(100),
BusinessName varchar(200),
locality varchar(200))
from OLDPostCodesAndAddressesBt
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 16, 2013 at 5:18 am
by doing the SELECT DISTINCT - you will only get the DISTINCT records returned from the SELECT statement.
Any Index violations would be eliminated by the SELECT.
Hope this helps ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 16, 2013 at 5:19 am
That is great, many thanks Steve you've been a tremendous help
September 16, 2013 at 5:25 am
No problem - glad to help.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 16, 2013 at 5:35 am
Sorry Steve, one last question. I've been reading up on Full Index, the only thing I can find via Google is Full Text Index, after reading up on it I can't see how that will apply to my needs, can you point me in the direction of where to read up on this?
September 16, 2013 at 6:38 am
Full Text Index is for "Text Fields" rather than "Full Index" -
you would have to define ALL columns that are in the table as an index key.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply