Create Index

I need to create an index or two indexes. I just don't know on what columns
to create this indexes. I have a stored procedure that I'm supposed to go by
deciding what indexes to create. Please take a look at this and give me your
recommendations. I appreciate your assistance. Thank you.
James
CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
/**********************************************************************************************************
--STEP-2
--Check for Duplicates within the file. If found remove them.
***********************************************************************************************************/
SET NOCOUNT ON
TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set BBR_Sequence_No = '';
/********************************************************************************
-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO TEMP_BBR_FILE_DUP
*********************************************************************************/
INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
SELECT
StateTops, CountyTops, CourtCode, CaseNo, CourtType,
DataSource, DType, DLastName,DFirstName, DMidName, DSuffix,
DStAddress,DAPARTMENT,DCity, DState,
DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,DAliasSuffix,
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTaxID,
PType, PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
ComplaintDate, MentalDate,SatisfiedDate,DismissalDate,PostedDate,
Amount, FilingType,
DispositionType,BBR_Sequence_No,bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox
FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
GROUP BY
StateTops, CountyTops, CourtCode, CaseNo, CourtType,
DataSource, DType, DLastName,DFirstName, DMidName, DSuffix,
DStAddress,DAPARTMENT,DCity, DState,
DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,DAliasSuffix,
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTaxID,
PType, PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
ComplaintDate, MentalDate,SatisfiedDate,DismissalDate,PostedDate,
Amount, FilingType,
DispositionType,BBR_Sequence_No,bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox
HAVING COUNT(*)>1
ORDER BY STATETOPS
/**********************************************************************/
-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO BBR_FILE_DUP
****************************************************************************************/
INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
SELECT
StateTops, CountyTops, CourtCode, CaseNo, CourtType,
DataSource, DType, DLastName,DFirstName, DMidName, DSuffix,
DStAddress,DAPARTMENT,DCity, DState,
DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,DAliasSuffix,
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTaxID,
PType, PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
ComplaintDate, MentalDate,SatisfiedDate,DismissalDate,PostedDate,
Amount, FilingType, DispositionType, BBR_Sequence_No,
bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
/*********************************************************************
-- DELETE FROM THE TABLE NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
ENTRIES
**********************************************************************/
DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A, NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
WHERE A.STATETOPS=B.STATETOPS
AND A.COUNTYTOPS=B.COUNTYTOPS
AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
AND ISNULL(A.DLASTNAME,'')=ISNULL(B.DLASTNAME,'')
AND ISNULL(A.DFIRSTNAME,'')=ISNULL(B.DFIRSTNAME,'')
AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
AND ISNULL(A.PLASTNAME,'')=ISNULL(B.PLASTNAME,'')
AND ISNULL(A.FILINGTYPE,'')=ISNULL(B.FILINGTYPE,'')
AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL(B.DISPOSITIONTYPE,'')
AND ISNULL(A.MENTALDATE,'')=ISNULL(B.MENTALDATE,'')
AND ISNULL(A.COURTCODE,'')=ISNULL(B.COURTCODE,'')
AND ISNULL(A.COURTTYPE,'')=ISNULL(B.COURTTYPE,'')
AND ISNULL(A.DATASOURCE,'')=ISNULL(B.DATASOURCE,'')
AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
AND ISNULL(A.DSTADDRESS,'')=ISNULL(B.DSTADDRESS,'')
AND ISNULL(A.DAPARTMENT,'')=ISNULL(B.DAPARTMENT,'')
AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
AND ISNULL(A.DALIASLASTNAME,'')=ISNULL(B.DALIASLASTNAME,'')
AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL(B.DALIASFIRSTNAME,'')
AND ISNULL(A.DALIASMIDNAME,'')=ISNULL(B.DALIASMIDNAME,'')
AND ISNULL(A.DALIASSUFFIX,'')=ISNULL(B.DALIASSUFFIX,'')
AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
AND ISNULL(A.CODLASTNAME,'')=ISNULL(B.CODLASTNAME,'')
AND ISNULL(A.CODFIRSTNAME,'')=ISNULL(B.CODFIRSTNAME,'')
AND ISNULL(A.CODMIDNAME,'')=ISNULL(B.CODMIDNAME,'')
AND ISNULL(A.CODSUFFIX,'')=ISNULL(B.CODSUFFIX,'')
AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
AND ISNULL(A.PFIRSTNAME,'')=ISNULL(B.PFIRSTNAME,'')
AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
AND ISNULL(A.PSTADDRESS,'')=ISNULL(B.PSTADDRESS,'')
AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
AND ISNULL(A.COMPLAINTDATE,'')=ISNULL(B.COMPLAINTDATE,'')
AND ISNULL(A.MENTALDATE,'')=ISNULL(B.MENTALDATE,'')
AND ISNULL(A.SATISFIEDDATE,'')=ISNULL(B.SATISFIEDDATE,'')
AND ISNULL(A.DISMISSALDATE,'')=ISNULL(B.DISMISSALDATE,'')
AND ISNULL(A.POSTEDDATE,'')=ISNULL(B.POSTEDDATE,'')
AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
AND ISNULL(A.RESEARCHERNO,'')=ISNULL(B.RESEARCHERNO,'')
AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
/**************************************************************************
--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO DBO.BBR_DAILY_FEED
***************************************************************************/
INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
/********************************************************************************
-- TRUNCATE THE TEMP TABLE
***********************************/
TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
/************************************************************
transfer of all unique records
*************************************************************/
INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops, countyTops, dlastName,
dfirstName, FilingType, Dispositiontype, mentalDate, amount)
SELECT distinct stateTops, countyTops, isnull(ltrim(rtrim(dlastName)),''),
isnull(ltrim(rtrim(dfirstName)),''), isnull(filingType,''),
isnull(Dispositiontype,''), substring(mentalDate, 1,6) as mentalDate,
ltrim(rtrim(amount)) from NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
/**************************************************************
Select records where just the amount are different
***************************************************************/
INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT (stateTops, countyTops,
DLastName, DFirstName, MentalDate, filingType, dispositionType)
SELECT stateTops, CountyTops, isnull(ltrim(rtrim(DLastName)),'') as
DLastName,isnull(ltrim(rtrim(DFirstName)),'') as DFirstName, mentalDate,
isnull(filingType,'') as FilingType, isnull(dispositionType,'') as
dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
GROUP BY stateTops, countyTops, isnull(ltrim(rtrim(DLastName)),''),
isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull(filingType,'') ,
isnull(dispositionType,'')
HAVING COUNT(*) > 1
/**************************************************************************************
Open a Cursor which will delete records from TEMP_BBR_FILE_DUP if that
record is a dup
***************************************************************************************/
exec REMOVE_DUPLICATE_AMOUNTS
/*********************************************************************************
update the remaining fields
**********************************************************************************/
UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
, COURTCODE = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
, DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
, PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
, MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
, COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
, DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
, DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
, DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
, DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
, DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
, DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
, DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
, DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
, DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
, DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLASTNAME
, DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIRSTNAME
, DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNAME
, DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
, CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
, CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
, CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
, CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
, CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
, CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
, PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
, PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
, PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
, PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
, PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
, PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
, PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
, COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDATE
, SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDATE
, DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDATE
, POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
, KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
, RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
, DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
, PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
ON
NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.STATETOPS -- STATETOPS HAS TO BE SAME
AND
NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COUNTYTOPS
AND
ISNULL(LTRIM(RTRIM(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL(LTRIM(RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'') --
HAS TO BE SAME (LOW ERRORS)
AND ISNULL(
LTRIM(RTRIM(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')=ISNULL(LTRIM(RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
TAKES CARE OF HUSBAND WIFE SCENARIOS
AND
ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')=ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'') -- HAS TO BE SAME
AND
ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')=ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') --
IN CASES WHERE THE COMPARISION IS BETWEEN A SATISFACTION STATUS
AND
ISNULL(SUBSTRING(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')=ISNULL(SUBSTRING(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') --
DATE OF THE WISE, HAS TO BE SAME
AND ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') = ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
/**********************************************************************************************************************
insert the rejected records into the reject table
***********************************************************************************************************************/
INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a where NOT EXISTS
(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
isnull(a.caseno,'') = isnull(b.caseno ,'')
and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
and isnull(a.DZIP,'')=isnull(b.DZIP,'')
and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
and isnull(a.DALIASLASTNAME,'')=isnull(b.DALIASLASTNAME,'')
and isnull(a.DALIASFIRSTNAME,'')=isnull(b.DALIASFIRSTNAME,'')
and isnull(a.DALIASMIDNAME,'')=isnull(b.DALIASMIDNAME,'')
and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
and isnull(a.PCITY,'')=isnull(b.PCITY,'')
and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
and isnull(a.PZIP,'')=isnull(b.PZIP,'')
and isnull(a.COMPLAINTDATE,'')=isnull(b.COMPLAINTDATE,'')
and isnull(a.SATISFIEDDATE,'')=isnull(b.SATISFIEDDATE,'')
and isnull(a.DISMISSALDATE,'')=isnull(b.DISMISSALDATE,'')
and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim(rtrim(b.DLASTNAME)),'')
AND
isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim(rtrim(b.DFIRSTNAME)),'')
AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL(b.DISPOSITIONTYPE,'')
)
/************************************************************************************************************************
remove from staging all the records that are not in DUP table
*************************************************************************************************************************/
DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT * FROM
NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
/***********************************************************************************************************************
clean up
************************************************************************************************************************/
truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
/************************************************************************************
COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG INTO TRANSACTION LOG
**************************************************************************************/
EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
GOHi James,
My first question is what kinda index are you trying to
creates, there are two types, clustered and non clustered,
clustered is faster, but your only allowed and per table.
As for the SQL well thanks but it doesn't really help. The
reason is that index's work best on SELECT statements, the
best canditates being the where clauses, which is not
included in your SQL.
Finally (and here comes the real downer) non clustered
indexes are not really recommended on a table that is
going to primary used as an insert table, the reason being
there is a lot of maths involved how an index works
internally.
However here is the good part, you need not do a thing,
SQL Server can work out which indexes it needs itself.
Here is how to do it.
When you installed SQL Server, you also installed
something called Profiler, start up profiler.
Click File -> New -> Trace and connect to the server where
the db is.
In Trace Name put anything you want.
In Template name put in SQLProfilerTuning
Click save to table and put in a database, table name to
save it to, don't worry about creating the table it will
do it automatically.
Then leave it running for about a week.
Then close it, and open up Enterprise Manager
Select the database the table resides in
Click on Wizards 'Index Tuning Wizards', the rest you can
work out for yourself ;)
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius
"That makes soooo knowledgable"
Peter The Spate
>--Original Message--
>I need to create an index or two indexes. I just don't
know on what columns
>to create this indexes. I have a stored procedure that
I'm supposed to go by
>deciding what indexes to create. Please take a look at
this and give me your
>recommendations. I appreciate your assistance. Thank
you.
>James
>CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
>/*********************************************************
*************************************************
>--STEP-2
>--Check for Duplicates within the file. If found remove
them.
>**********************************************************
*************************************************/
>SET NOCOUNT ON
>TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set
BBR_Sequence_No = '';
>/*********************************************************
***********************
>-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
TEMP_BBR_FILE_DUP
>**********************************************************
***********************/
>INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>SELECT
> StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> DataSource, DType, DLastName,DFirstName, DMidName,
DSuffix,
> DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> Amount, FilingType,
>DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
rNo,DPoBox,PPoBox
>FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>GROUP BY
> StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> DataSource, DType, DLastName,DFirstName, DMidName,
DSuffix,
> DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> Amount, FilingType,
>DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
rNo,DPoBox,PPoBox
>HAVING COUNT(*)>1
> ORDER BY STATETOPS
>
>/*********************************************************
*************/
>-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
BBR_FILE_DUP
>**********************************************************
******************************/
>INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
>SELECT
> StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> DataSource, DType, DLastName,DFirstName, DMidName,
DSuffix,
> DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> Amount, FilingType, DispositionType, BBR_Sequence_No,
>bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
>FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>
>/*********************************************************
************
>-- DELETE FROM THE TABLE
NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
>ENTRIES
>**********************************************************
************/
>DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A,
NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
> WHERE A.STATETOPS=B.STATETOPS
> AND A.COUNTYTOPS=B.COUNTYTOPS
> AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
> AND ISNULL(A.DLASTNAME,'')=ISNULL(B.DLASTNAME,'')
> AND ISNULL(A.DFIRSTNAME,'')=ISNULL
(B.DFIRSTNAME,'')
> AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
> AND ISNULL(A.PLASTNAME,'')=ISNULL(B.PLASTNAME,'')
> AND ISNULL(A.FILINGTYPE,'')=ISNULL
(B.FILINGTYPE,'')
> AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL
(B.DISPOSITIONTYPE,'')
> AND ISNULL(A.MENTALDATE,'')=ISNULL
(B.MENTALDATE,'')
> AND ISNULL(A.COURTCODE,'')=ISNULL(B.COURTCODE,'')
> AND ISNULL(A.COURTTYPE,'')=ISNULL(B.COURTTYPE,'')
> AND ISNULL(A.DATASOURCE,'')=ISNULL
(B.DATASOURCE,'')
> AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
> AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
> AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
> AND ISNULL(A.DSTADDRESS,'')=ISNULL
(B.DSTADDRESS,'')
> AND ISNULL(A.DAPARTMENT,'')=ISNULL
(B.DAPARTMENT,'')
> AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
> AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
> AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
> AND ISNULL(A.DALIASLASTNAME,'')=ISNULL
(B.DALIASLASTNAME,'')
> AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL
(B.DALIASFIRSTNAME,'')
> AND ISNULL(A.DALIASMIDNAME,'')=ISNULL
(B.DALIASMIDNAME,'')
> AND ISNULL(A.DALIASSUFFIX,'')=ISNULL
(B.DALIASSUFFIX,'')
> AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
> AND ISNULL(A.CODLASTNAME,'')=ISNULL
(B.CODLASTNAME,'')
> AND ISNULL(A.CODFIRSTNAME,'')=ISNULL
(B.CODFIRSTNAME,'')
> AND ISNULL(A.CODMIDNAME,'')=ISNULL
(B.CODMIDNAME,'')
> AND ISNULL(A.CODSUFFIX,'')=ISNULL(B.CODSUFFIX,'')
> AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
> AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
> AND ISNULL(A.PFIRSTNAME,'')=ISNULL
(B.PFIRSTNAME,'')
> AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
> AND ISNULL(A.PSTADDRESS,'')=ISNULL
(B.PSTADDRESS,'')
> AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
> AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
> AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
> AND ISNULL(A.COMPLAINTDATE,'')=ISNULL
(B.COMPLAINTDATE,'')
> AND ISNULL(A.MENTALDATE,'')=ISNULL
(B.MENTALDATE,'')
> AND ISNULL(A.SATISFIEDDATE,'')=ISNULL
(B.SATISFIEDDATE,'')
> AND ISNULL(A.DISMISSALDATE,'')=ISNULL
(B.DISMISSALDATE,'')
> AND ISNULL(A.POSTEDDATE,'')=ISNULL
(B.POSTEDDATE,'')
> AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
> AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
> AND ISNULL(A.RESEARCHERNO,'')=ISNULL
(B.RESEARCHERNO,'')
> AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
> AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
>/*********************************************************
*****************
>--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO
DBO.BBR_DAILY_FEED
>**********************************************************
*****************/
>INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>/*********************************************************
***********************
>-- TRUNCATE THE TEMP TABLE
>***********************************/
>TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>/*********************************************************
***
>transfer of all unique records
>**********************************************************
***/
>INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops,
countyTops, dlastName,
>dfirstName, FilingType, Dispositiontype, mentalDate,
amount)
>SELECT distinct stateTops, countyTops, isnull(ltrim(rtrim
(dlastName)),''),
>isnull(ltrim(rtrim(dfirstName)),''), isnull
(filingType,''),
>isnull(Dispositiontype,''), substring(mentalDate, 1,6) as
mentalDate,
>ltrim(rtrim(amount)) from
NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>/*********************************************************
*****
>Select records where just the amount are different
>**********************************************************
*****/
>INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT
(stateTops, countyTops,
>DLastName, DFirstName, MentalDate, filingType,
dispositionType)
>SELECT stateTops, CountyTops, isnull(ltrim(rtrim
(DLastName)),'') as
>DLastName,isnull(ltrim(rtrim(DFirstName)),'') as
DFirstName, mentalDate,
>isnull(filingType,'') as FilingType, isnull
(dispositionType,'') as
>dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>GROUP BY stateTops, countyTops, isnull(ltrim(rtrim
(DLastName)),''),
>isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull
(filingType,'') ,
>isnull(dispositionType,'')
>HAVING COUNT(*) > 1
>/*********************************************************
*****************************
>Open a Cursor which will delete records from
TEMP_BBR_FILE_DUP if that
>record is a dup
>**********************************************************
*****************************/
>exec REMOVE_DUPLICATE_AMOUNTS
>/*********************************************************
************************
>update the remaining fields
>**********************************************************
************************/
>UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
> , COURTCODE =NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
> , DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
> ,
PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
> ,
MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
> ,
COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
> ,
DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
> , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
> ,
DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
> ,
DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
> ,
DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
> ,
DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
> , DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
> , DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
> , DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
> ,
DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
NAME
> ,
DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
STNAME
> ,
DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
ME
> ,
DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
> ,
CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
> ,
CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
> ,
CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
> ,
CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
> ,
CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
> ,
CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
> , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
> ,
PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
> ,
PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
> ,
PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
> , PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
> , PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
> , PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
> ,
COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
TE
> ,
SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
TE
> ,
DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
TE
> ,
POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
> ,
KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
> ,
RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
> , DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
> , PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
>FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>ON
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
ILY_STAGE_BANDS.STATETOPS
-- STATETOPS HAS TO BE SAME
> AND
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
AILY_STAGE_BANDS.COUNTYTOPS
> AND
>ISNULL(LTRIM(RTRIM
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL(LTRIM
(RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
--
>HAS TO BE SAME (LOW ERRORS)
> AND ISNULL(
>LTRIM(RTRIM(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
=ISNULL(LTRIM(RTRIM
(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
>TAKES CARE OF HUSBAND WIFE SCENARIOS
> AND
>ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')=ISNULL
(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
-- HAS TO BE SAME
> AND
>ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
=ISNULL
(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') --
>IN CASES WHERE THE COMPARISION IS BETWEEN A SATISFACTION
STATUS
> AND
>ISNULL(SUBSTRING
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')=ISNULL
(SUBSTRING
(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') --
>DATE OF THE WISE, HAS TO BE SAME
> AND ISNULL
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') =>ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
>/*********************************************************
***********************************************************
**
>insert the rejected records into the reject table
>**********************************************************
***********************************************************
**/
>INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
>SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a
where NOT EXISTS
>(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
>isnull(a.caseno,'') = isnull(b.caseno ,'')
>and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
>and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
>and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
>and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
>and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
>and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
>and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
>and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
>and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
>and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
>and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
>and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
>and isnull(a.DZIP,'')=isnull(b.DZIP,'')
>and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
>and isnull(a.DALIASLASTNAME,'')=isnull
(b.DALIASLASTNAME,'')
>and isnull(a.DALIASFIRSTNAME,'')=isnull
(b.DALIASFIRSTNAME,'')
>and isnull(a.DALIASMIDNAME,'')=isnull(b.DALIASMIDNAME,'')
>and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
>and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
>and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
>and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
>and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
>and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
>and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
>and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
>and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
>and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
>and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
>and isnull(a.PCITY,'')=isnull(b.PCITY,'')
>and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
>and isnull(a.PZIP,'')=isnull(b.PZIP,'')
>and isnull(a.COMPLAINTDATE,'')=isnull(b.COMPLAINTDATE,'')
>and isnull(a.SATISFIEDDATE,'')=isnull(b.SATISFIEDDATE,'')
>and isnull(a.DISMISSALDATE,'')=isnull(b.DISMISSALDATE,'')
>and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
>and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
>and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
>and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
>and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
>and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
>and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
>AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
>AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim
(rtrim(b.DLASTNAME)),'')
>AND
>isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim(rtrim
(b.DFIRSTNAME)),'')
>AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
>AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL
(b.DISPOSITIONTYPE,'')
>)
>/*********************************************************
***********************************************************
****
>remove from staging all the records that are not in DUP
table
>**********************************************************
***********************************************************
****/
>DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
>INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT *
FROM
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
>/*********************************************************
***********************************************************
***
>clean up
>**********************************************************
***********************************************************
***/
>truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
>truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
>/*********************************************************
***************************
>COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG
INTO TRANSACTION LOG
>**********************************************************
****************************/
>EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
>GO
>.
>|||Peter - I'd love to know what info source you're using for some of the
things you say so that I can try to correct it.
Why do you think that clustered indexes are faster than non-clustered
indexes?
Non-clustered indexes do slow down insert operations (because a record has
to be inserted into the non-clustered index AND the base table), but even if
the table is primarily used for inserts, if you have one query that uses a
different predicate than the key of your clustered index, you're going to
want a non-clustered index on it. Given a sufficiently fast IO subsystem you
should be ok.
The ITW advice is correct.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:10a001c5144a$2ca9d430$a601280a@.phx.gbl...
> Hi James,
> My first question is what kinda index are you trying to
> creates, there are two types, clustered and non clustered,
> clustered is faster, but your only allowed and per table.
> As for the SQL well thanks but it doesn't really help. The
> reason is that index's work best on SELECT statements, the
> best canditates being the where clauses, which is not
> included in your SQL.
> Finally (and here comes the real downer) non clustered
> indexes are not really recommended on a table that is
> going to primary used as an insert table, the reason being
> there is a lot of maths involved how an index works
> internally.
> However here is the good part, you need not do a thing,
> SQL Server can work out which indexes it needs itself.
> Here is how to do it.
> When you installed SQL Server, you also installed
> something called Profiler, start up profiler.
> Click File -> New -> Trace and connect to the server where
> the db is.
> In Trace Name put anything you want.
> In Template name put in SQLProfilerTuning
> Click save to table and put in a database, table name to
> save it to, don't worry about creating the table it will
> do it automatically.
> Then leave it running for about a week.
> Then close it, and open up Enterprise Manager
> Select the database the table resides in
> Click on Wizards 'Index Tuning Wizards', the rest you can
> work out for yourself ;)
> Peter
> "Real knowledge is to know the extent of one's ignorance."
> Confucius
> "That makes soooo knowledgable"
> Peter The Spate
>
>
> >--Original Message--
> >I need to create an index or two indexes. I just don't
> know on what columns
> >to create this indexes. I have a stored procedure that
> I'm supposed to go by
> >deciding what indexes to create. Please take a look at
> this and give me your
> >recommendations. I appreciate your assistance. Thank
> you.
> >James
> >
> >CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
> >/*********************************************************
> *************************************************
> >--STEP-2
> >--Check for Duplicates within the file. If found remove
> them.
> >**********************************************************
> *************************************************/
> >
> >SET NOCOUNT ON
> >
> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set
> BBR_Sequence_No = '';
> >/*********************************************************
> ***********************
> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
> TEMP_BBR_FILE_DUP
> >**********************************************************
> ***********************/
> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >SELECT
> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> > DataSource, DType, DLastName,DFirstName, DMidName,
> DSuffix,
> > DStAddress,DAPARTMENT,DCity, DState,
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> AliasSuffix,
> >
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> xID,
> > PType,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> > ComplaintDate,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> > Amount, FilingType,
> >DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
> rNo,DPoBox,PPoBox
> >FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >GROUP BY
> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> > DataSource, DType, DLastName,DFirstName, DMidName,
> DSuffix,
> > DStAddress,DAPARTMENT,DCity, DState,
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> AliasSuffix,
> >
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> xID,
> > PType,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> > ComplaintDate,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> > Amount, FilingType,
> >DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
> rNo,DPoBox,PPoBox
> >HAVING COUNT(*)>1
> > ORDER BY STATETOPS
> >
> >
> >/*********************************************************
> *************/
> >
> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
> BBR_FILE_DUP
> >**********************************************************
> ******************************/
> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
> >SELECT
> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> > DataSource, DType, DLastName,DFirstName, DMidName,
> DSuffix,
> > DStAddress,DAPARTMENT,DCity, DState,
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> AliasSuffix,
> >
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> xID,
> > PType,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> > ComplaintDate,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> > Amount, FilingType, DispositionType, BBR_Sequence_No,
> >bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >
> >
> >/*********************************************************
> ************
> >-- DELETE FROM THE TABLE
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
> >ENTRIES
> >**********************************************************
> ************/
> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> > FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A,
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
> > WHERE A.STATETOPS=B.STATETOPS
> > AND A.COUNTYTOPS=B.COUNTYTOPS
> > AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
> > AND ISNULL(A.DLASTNAME,'')=ISNULL(B.DLASTNAME,'')
> > AND ISNULL(A.DFIRSTNAME,'')=ISNULL
> (B.DFIRSTNAME,'')
> > AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
> > AND ISNULL(A.PLASTNAME,'')=ISNULL(B.PLASTNAME,'')
> > AND ISNULL(A.FILINGTYPE,'')=ISNULL
> (B.FILINGTYPE,'')
> > AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL
> (B.DISPOSITIONTYPE,'')
> > AND ISNULL(A.MENTALDATE,'')=ISNULL
> (B.MENTALDATE,'')
> > AND ISNULL(A.COURTCODE,'')=ISNULL(B.COURTCODE,'')
> > AND ISNULL(A.COURTTYPE,'')=ISNULL(B.COURTTYPE,'')
> > AND ISNULL(A.DATASOURCE,'')=ISNULL
> (B.DATASOURCE,'')
> > AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
> > AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
> > AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
> > AND ISNULL(A.DSTADDRESS,'')=ISNULL
> (B.DSTADDRESS,'')
> > AND ISNULL(A.DAPARTMENT,'')=ISNULL
> (B.DAPARTMENT,'')
> > AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
> > AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
> > AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
> > AND ISNULL(A.DALIASLASTNAME,'')=ISNULL
> (B.DALIASLASTNAME,'')
> > AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL
> (B.DALIASFIRSTNAME,'')
> > AND ISNULL(A.DALIASMIDNAME,'')=ISNULL
> (B.DALIASMIDNAME,'')
> > AND ISNULL(A.DALIASSUFFIX,'')=ISNULL
> (B.DALIASSUFFIX,'')
> > AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
> > AND ISNULL(A.CODLASTNAME,'')=ISNULL
> (B.CODLASTNAME,'')
> > AND ISNULL(A.CODFIRSTNAME,'')=ISNULL
> (B.CODFIRSTNAME,'')
> > AND ISNULL(A.CODMIDNAME,'')=ISNULL
> (B.CODMIDNAME,'')
> > AND ISNULL(A.CODSUFFIX,'')=ISNULL(B.CODSUFFIX,'')
> > AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
> > AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
> > AND ISNULL(A.PFIRSTNAME,'')=ISNULL
> (B.PFIRSTNAME,'')
> > AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
> > AND ISNULL(A.PSTADDRESS,'')=ISNULL
> (B.PSTADDRESS,'')
> > AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
> > AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
> > AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
> > AND ISNULL(A.COMPLAINTDATE,'')=ISNULL
> (B.COMPLAINTDATE,'')
> > AND ISNULL(A.MENTALDATE,'')=ISNULL
> (B.MENTALDATE,'')
> > AND ISNULL(A.SATISFIEDDATE,'')=ISNULL
> (B.SATISFIEDDATE,'')
> > AND ISNULL(A.DISMISSALDATE,'')=ISNULL
> (B.DISMISSALDATE,'')
> > AND ISNULL(A.POSTEDDATE,'')=ISNULL
> (B.POSTEDDATE,'')
> > AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
> > AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
> > AND ISNULL(A.RESEARCHERNO,'')=ISNULL
> (B.RESEARCHERNO,'')
> > AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
> > AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
> >
> >/*********************************************************
> *****************
> >--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO
> DBO.BBR_DAILY_FEED
> >**********************************************************
> *****************/
> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> > SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >
> >/*********************************************************
> ***********************
> >-- TRUNCATE THE TEMP TABLE
> >***********************************/
> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >
> >/*********************************************************
> ***
> >transfer of all unique records
> >**********************************************************
> ***/
> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops,
> countyTops, dlastName,
> >dfirstName, FilingType, Dispositiontype, mentalDate,
> amount)
> >SELECT distinct stateTops, countyTops, isnull(ltrim(rtrim
> (dlastName)),''),
> >isnull(ltrim(rtrim(dfirstName)),''), isnull
> (filingType,''),
> >isnull(Dispositiontype,''), substring(mentalDate, 1,6) as
> mentalDate,
> >ltrim(rtrim(amount)) from
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >
> >/*********************************************************
> *****
> >Select records where just the amount are different
> >**********************************************************
> *****/
> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT
> (stateTops, countyTops,
> >DLastName, DFirstName, MentalDate, filingType,
> dispositionType)
> >SELECT stateTops, CountyTops, isnull(ltrim(rtrim
> (DLastName)),'') as
> >DLastName,isnull(ltrim(rtrim(DFirstName)),'') as
> DFirstName, mentalDate,
> >isnull(filingType,'') as FilingType, isnull
> (dispositionType,'') as
> >dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >GROUP BY stateTops, countyTops, isnull(ltrim(rtrim
> (DLastName)),''),
> >isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull
> (filingType,'') ,
> >isnull(dispositionType,'')
> >HAVING COUNT(*) > 1
> >
> >/*********************************************************
> *****************************
> >Open a Cursor which will delete records from
> TEMP_BBR_FILE_DUP if that
> >record is a dup
> >**********************************************************
> *****************************/
> >exec REMOVE_DUPLICATE_AMOUNTS
> >
> >/*********************************************************
> ************************
> >update the remaining fields
> >**********************************************************
> ************************/
> >
> >UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
> > , COURTCODE => NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
> > , DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
> > ,
> PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
> > ,
> MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
> > ,
> COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
> > ,
> DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
> > , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
> > ,
> DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
> > ,
> DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
> > ,
> DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
> > ,
> DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
> > , DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
> > , DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
> > , DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
> > ,
> DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
> NAME
> > ,
> DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
> STNAME
> > ,
> DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
> ME
> > ,
> DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
> > ,
> CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
> > ,
> CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
> > ,
> CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
> > ,
> CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
> > ,
> CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
> > ,
> CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
> > , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
> > ,
> PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
> > ,
> PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
> > ,
> PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
> > , PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
> > , PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
> > , PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
> > ,
> COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
> TE
> > ,
> SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
> TE
> > ,
> DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
> TE
> > ,
> POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
> > ,
> KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
> > ,
> RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
> > , DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
> > , PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >ON
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
> ILY_STAGE_BANDS.STATETOPS
> -- STATETOPS HAS TO BE SAME
> > AND
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
> AILY_STAGE_BANDS.COUNTYTOPS
> > AND
> >ISNULL(LTRIM(RTRIM
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL(LTRIM
> (RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
> --
> >HAS TO BE SAME (LOW ERRORS)
> > AND ISNULL(
> >LTRIM(RTRIM(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
> =ISNULL(LTRIM(RTRIM
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
> >TAKES CARE OF HUSBAND WIFE SCENARIOS
> > AND
> >ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')=ISNULL
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
> -- HAS TO BE SAME
> > AND
> >ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
> =ISNULL
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') --
> >IN CASES WHERE THE COMPARISION IS BETWEEN A SATISFACTION
> STATUS
> > AND
> >ISNULL(SUBSTRING
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')=ISNULL
> (SUBSTRING
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') --
> >DATE OF THE WISE, HAS TO BE SAME
> > AND ISNULL
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') => >ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
> >
> >/*********************************************************
> ***********************************************************
> **
> >insert the rejected records into the reject table
> >**********************************************************
> ***********************************************************
> **/
> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
> >SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a
> where NOT EXISTS
> >(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
> >isnull(a.caseno,'') = isnull(b.caseno ,'')
> >and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
> >and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
> >and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
> >and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
> >and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
> >and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
> >and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
> >and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
> >and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
> >and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
> >and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
> >and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
> >and isnull(a.DZIP,'')=isnull(b.DZIP,'')
> >and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
> >and isnull(a.DALIASLASTNAME,'')=isnull
> (b.DALIASLASTNAME,'')
> >and isnull(a.DALIASFIRSTNAME,'')=isnull
> (b.DALIASFIRSTNAME,'')
> >and isnull(a.DALIASMIDNAME,'')=isnull(b.DALIASMIDNAME,'')
> >and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
> >and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
> >and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
> >and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
> >and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
> >and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
> >and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
> >and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
> >and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
> >and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
> >and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
> >and isnull(a.PCITY,'')=isnull(b.PCITY,'')
> >and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
> >and isnull(a.PZIP,'')=isnull(b.PZIP,'')
> >and isnull(a.COMPLAINTDATE,'')=isnull(b.COMPLAINTDATE,'')
> >and isnull(a.SATISFIEDDATE,'')=isnull(b.SATISFIEDDATE,'')
> >and isnull(a.DISMISSALDATE,'')=isnull(b.DISMISSALDATE,'')
> >and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
> >and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
> >and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
> >and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
> >and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
> >and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
> >and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
> >AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
> >AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim
> (rtrim(b.DLASTNAME)),'')
> >AND
> >isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim(rtrim
> (b.DFIRSTNAME)),'')
> >AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
> >AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL
> (b.DISPOSITIONTYPE,'')
> >)
> >
> >/*********************************************************
> ***********************************************************
> ****
> >remove from staging all the records that are not in DUP
> table
> >**********************************************************
> ***********************************************************
> ****/
> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT *
> FROM
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
> >/*********************************************************
> ***********************************************************
> ***
> >clean up
> >**********************************************************
> ***********************************************************
> ***/
> >truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
> >truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
> >
> >/*********************************************************
> ***************************
> >COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG
> INTO TRANSACTION LOG
> >**********************************************************
> ****************************/
> >EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
> >GO
> >
> >.
> >|||The Book Inside Microsoft SQL Server by Karen Delany,
chapter 8, page 406 quite 'The query optimizer strongly
favours a clustered index because such an index allows the
data to be found directly at the leaf level'.
The Book SQL Server Performance tuning - Page 343
Quote 'The index itself (non clustered) is completely
sepatate from the data, like a book with an index at the
back'
Therefore am clusted index points directly to a page where
as a non clustered does not.
Now I have given you my source will you give yours ?
Peter
>--Original Message--
>Peter - I'd love to know what info source you're using
for some of the
>things you say so that I can try to correct it.
>Why do you think that clustered indexes are faster than
non-clustered
>indexes?
>Non-clustered indexes do slow down insert operations
(because a record has
>to be inserted into the non-clustered index AND the base
table), but even if
>the table is primarily used for inserts, if you have one
query that uses a
>different predicate than the key of your clustered index,
you're going to
>want a non-clustered index on it. Given a sufficiently
fast IO subsystem you
>should be ok.
>The ITW advice is correct.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:10a001c5144a$2ca9d430$a601280a@.phx.gbl...
>> Hi James,
>> My first question is what kinda index are you trying to
>> creates, there are two types, clustered and non
clustered,
>> clustered is faster, but your only allowed and per
table.
>> As for the SQL well thanks but it doesn't really help.
The
>> reason is that index's work best on SELECT statements,
the
>> best canditates being the where clauses, which is not
>> included in your SQL.
>> Finally (and here comes the real downer) non clustered
>> indexes are not really recommended on a table that is
>> going to primary used as an insert table, the reason
being
>> there is a lot of maths involved how an index works
>> internally.
>> However here is the good part, you need not do a thing,
>> SQL Server can work out which indexes it needs itself.
>> Here is how to do it.
>> When you installed SQL Server, you also installed
>> something called Profiler, start up profiler.
>> Click File -> New -> Trace and connect to the server
where
>> the db is.
>> In Trace Name put anything you want.
>> In Template name put in SQLProfilerTuning
>> Click save to table and put in a database, table name to
>> save it to, don't worry about creating the table it will
>> do it automatically.
>> Then leave it running for about a week.
>> Then close it, and open up Enterprise Manager
>> Select the database the table resides in
>> Click on Wizards 'Index Tuning Wizards', the rest you
can
>> work out for yourself ;)
>> Peter
>> "Real knowledge is to know the extent of one's
ignorance."
>> Confucius
>> "That makes soooo knowledgable"
>> Peter The Spate
>>
>>
>> >--Original Message--
>> >I need to create an index or two indexes. I just don't
>> know on what columns
>> >to create this indexes. I have a stored procedure that
>> I'm supposed to go by
>> >deciding what indexes to create. Please take a look at
>> this and give me your
>> >recommendations. I appreciate your assistance. Thank
>> you.
>> >James
>> >
>> >CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
>/*********************************************************
>> *************************************************
>> >--STEP-2
>> >--Check for Duplicates within the file. If found remove
>> them.
>**********************************************************
>> *************************************************/
>> >
>> >SET NOCOUNT ON
>> >
>> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set
>> BBR_Sequence_No = '';
>/*********************************************************
>> ***********************
>> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
>> TEMP_BBR_FILE_DUP
>**********************************************************
>> ***********************/
>> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >SELECT
>> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
>> > DataSource, DType, DLastName,DFirstName, DMidName,
>> DSuffix,
>> > DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
>> AliasSuffix,
>> >
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
>> xID,
>> > PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
>> > ComplaintDate,
>> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
>> > Amount, FilingType,
>DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
>> rNo,DPoBox,PPoBox
>> >FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> >GROUP BY
>> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
>> > DataSource, DType, DLastName,DFirstName, DMidName,
>> DSuffix,
>> > DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
>> AliasSuffix,
>> >
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
>> xID,
>> > PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
>> > ComplaintDate,
>> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
>> > Amount, FilingType,
>DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
>> rNo,DPoBox,PPoBox
>> >HAVING COUNT(*)>1
>> > ORDER BY STATETOPS
>> >
>> >
>/*********************************************************
>> *************/
>> >
>> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
>> BBR_FILE_DUP
>**********************************************************
>> ******************************/
>> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
>> >SELECT
>> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
>> > DataSource, DType, DLastName,DFirstName, DMidName,
>> DSuffix,
>> > DStAddress,DAPARTMENT,DCity, DState,
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
>> AliasSuffix,
>> >
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
>> xID,
>> > PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
>> > ComplaintDate,
>> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
>> > Amount, FilingType, DispositionType,
BBR_Sequence_No,
>> >bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
>> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >
>> >
>/*********************************************************
>> ************
>> >-- DELETE FROM THE TABLE
>> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
>> >ENTRIES
>**********************************************************
>> ************/
>> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> > FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A,
>> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
>> > WHERE A.STATETOPS=B.STATETOPS
>> > AND A.COUNTYTOPS=B.COUNTYTOPS
>> > AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
>> > AND ISNULL(A.DLASTNAME,'')=ISNULL
(B.DLASTNAME,'')
>> > AND ISNULL(A.DFIRSTNAME,'')=ISNULL
>> (B.DFIRSTNAME,'')
>> > AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
>> > AND ISNULL(A.PLASTNAME,'')=ISNULL
(B.PLASTNAME,'')
>> > AND ISNULL(A.FILINGTYPE,'')=ISNULL
>> (B.FILINGTYPE,'')
>> > AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL
>> (B.DISPOSITIONTYPE,'')
>> > AND ISNULL(A.MENTALDATE,'')=ISNULL
>> (B.MENTALDATE,'')
>> > AND ISNULL(A.COURTCODE,'')=ISNULL
(B.COURTCODE,'')
>> > AND ISNULL(A.COURTTYPE,'')=ISNULL
(B.COURTTYPE,'')
>> > AND ISNULL(A.DATASOURCE,'')=ISNULL
>> (B.DATASOURCE,'')
>> > AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
>> > AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
>> > AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
>> > AND ISNULL(A.DSTADDRESS,'')=ISNULL
>> (B.DSTADDRESS,'')
>> > AND ISNULL(A.DAPARTMENT,'')=ISNULL
>> (B.DAPARTMENT,'')
>> > AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
>> > AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
>> > AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
>> > AND ISNULL(A.DALIASLASTNAME,'')=ISNULL
>> (B.DALIASLASTNAME,'')
>> > AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL
>> (B.DALIASFIRSTNAME,'')
>> > AND ISNULL(A.DALIASMIDNAME,'')=ISNULL
>> (B.DALIASMIDNAME,'')
>> > AND ISNULL(A.DALIASSUFFIX,'')=ISNULL
>> (B.DALIASSUFFIX,'')
>> > AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
>> > AND ISNULL(A.CODLASTNAME,'')=ISNULL
>> (B.CODLASTNAME,'')
>> > AND ISNULL(A.CODFIRSTNAME,'')=ISNULL
>> (B.CODFIRSTNAME,'')
>> > AND ISNULL(A.CODMIDNAME,'')=ISNULL
>> (B.CODMIDNAME,'')
>> > AND ISNULL(A.CODSUFFIX,'')=ISNULL
(B.CODSUFFIX,'')
>> > AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
>> > AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
>> > AND ISNULL(A.PFIRSTNAME,'')=ISNULL
>> (B.PFIRSTNAME,'')
>> > AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
>> > AND ISNULL(A.PSTADDRESS,'')=ISNULL
>> (B.PSTADDRESS,'')
>> > AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
>> > AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
>> > AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
>> > AND ISNULL(A.COMPLAINTDATE,'')=ISNULL
>> (B.COMPLAINTDATE,'')
>> > AND ISNULL(A.MENTALDATE,'')=ISNULL
>> (B.MENTALDATE,'')
>> > AND ISNULL(A.SATISFIEDDATE,'')=ISNULL
>> (B.SATISFIEDDATE,'')
>> > AND ISNULL(A.DISMISSALDATE,'')=ISNULL
>> (B.DISMISSALDATE,'')
>> > AND ISNULL(A.POSTEDDATE,'')=ISNULL
>> (B.POSTEDDATE,'')
>> > AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
>> > AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
>> > AND ISNULL(A.RESEARCHERNO,'')=ISNULL
>> (B.RESEARCHERNO,'')
>> > AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
>> > AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
>> >
>/*********************************************************
>> *****************
>> >--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO
>> DBO.BBR_DAILY_FEED
>**********************************************************
>> *****************/
>> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> > SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >
>/*********************************************************
>> ***********************
>> >-- TRUNCATE THE TEMP TABLE
>> >***********************************/
>> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >
>/*********************************************************
>> ***
>> >transfer of all unique records
>**********************************************************
>> ***/
>> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops,
>> countyTops, dlastName,
>> >dfirstName, FilingType, Dispositiontype, mentalDate,
>> amount)
>> >SELECT distinct stateTops, countyTops, isnull(ltrim
(rtrim
>> (dlastName)),''),
>> >isnull(ltrim(rtrim(dfirstName)),''), isnull
>> (filingType,''),
>> >isnull(Dispositiontype,''), substring(mentalDate, 1,6)
as
>> mentalDate,
>> >ltrim(rtrim(amount)) from
>> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> >
>/*********************************************************
>> *****
>> >Select records where just the amount are different
>**********************************************************
>> *****/
>> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT
>> (stateTops, countyTops,
>> >DLastName, DFirstName, MentalDate, filingType,
>> dispositionType)
>> >SELECT stateTops, CountyTops, isnull(ltrim(rtrim
>> (DLastName)),'') as
>> >DLastName,isnull(ltrim(rtrim(DFirstName)),'') as
>> DFirstName, mentalDate,
>> >isnull(filingType,'') as FilingType, isnull
>> (dispositionType,'') as
>> >dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >GROUP BY stateTops, countyTops, isnull(ltrim(rtrim
>> (DLastName)),''),
>> >isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull
>> (filingType,'') ,
>> >isnull(dispositionType,'')
>> >HAVING COUNT(*) > 1
>> >
>/*********************************************************
>> *****************************
>> >Open a Cursor which will delete records from
>> TEMP_BBR_FILE_DUP if that
>> >record is a dup
>**********************************************************
>> *****************************/
>> >exec REMOVE_DUPLICATE_AMOUNTS
>> >
>/*********************************************************
>> ************************
>> >update the remaining fields
>**********************************************************
>> ************************/
>> >
>> >UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
>> > , COURTCODE =>> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
>> > , DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
>> > ,
>> PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
>> > ,
>> MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
>> > ,
>> COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
>> > ,
>> DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
>> > , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
>> > ,
>> DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
>> > ,
>> DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
>> > ,
>> DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
>> > ,
>> DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
>> > ,
DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
>> > , DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
>> > ,
DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
>> > ,
DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
>> NAME
>> > ,
DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
>> STNAME
>> > ,
DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
>> ME
>> > ,
DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
>> > ,
>> CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
>> > ,
CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
>> > ,
CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
>> > ,
>> CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
>> > ,
>> CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
>> > ,
>> CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
>> > , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
>> > ,
>> PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
>> > ,
>> PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
>> > ,
>> PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
>> > , PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
>> > ,
PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
>> > , PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
>> > ,
COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
>> TE
>> > ,
SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
>> TE
>> > ,
DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
>> TE
>> > ,
>> POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
>> > ,
>> KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
>> > ,
RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
>> > ,
DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
>> > ,
PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
>> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
>> >INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
>> >ON
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
>> ILY_STAGE_BANDS.STATETOPS
>> -- STATETOPS HAS TO BE SAME
>> > AND
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
>> AILY_STAGE_BANDS.COUNTYTOPS
>> > AND
>> >ISNULL(LTRIM(RTRIM
>> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL
(LTRIM
>> (RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
>> --
>> >HAS TO BE SAME (LOW ERRORS)
>> > AND ISNULL(
>> >LTRIM(RTRIM
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
>> =ISNULL(LTRIM(RTRIM
>> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
>> >TAKES CARE OF HUSBAND WIFE SCENARIOS
>> > AND
>> >ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')
=ISNULL
>> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
>> -- HAS TO BE SAME
>> > AND
>> >ISNULL
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
>> =ISNULL
>> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') -
-
>> >IN CASES WHERE THE COMPARISION IS BETWEEN A
SATISFACTION
>> STATUS
>> > AND
>> >ISNULL(SUBSTRING
>> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')
=ISNULL
>> (SUBSTRING
>> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') -
-
>> >DATE OF THE WISE, HAS TO BE SAME
>> > AND ISNULL
>> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') =>> >ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
>> >
>/*********************************************************
***********************************************************
>> **
>> >insert the rejected records into the reject table
>**********************************************************
***********************************************************
>> **/
>> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
>> >SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a
>> where NOT EXISTS
>> >(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
>> >isnull(a.caseno,'') = isnull(b.caseno ,'')
>> >and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
>> >and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
>> >and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
>> >and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
>> >and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
>> >and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
>> >and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
>> >and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
>> >and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
>> >and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
>> >and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
>> >and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
>> >and isnull(a.DZIP,'')=isnull(b.DZIP,'')
>> >and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
>> >and isnull(a.DALIASLASTNAME,'')=isnull
>> (b.DALIASLASTNAME,'')
>> >and isnull(a.DALIASFIRSTNAME,'')=isnull
>> (b.DALIASFIRSTNAME,'')
>> >and isnull(a.DALIASMIDNAME,'')=isnull
(b.DALIASMIDNAME,'')
>> >and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
>> >and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
>> >and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
>> >and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
>> >and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
>> >and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
>> >and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
>> >and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
>> >and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
>> >and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
>> >and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
>> >and isnull(a.PCITY,'')=isnull(b.PCITY,'')
>> >and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
>> >and isnull(a.PZIP,'')=isnull(b.PZIP,'')
>> >and isnull(a.COMPLAINTDATE,'')=isnull
(b.COMPLAINTDATE,'')
>> >and isnull(a.SATISFIEDDATE,'')=isnull
(b.SATISFIEDDATE,'')
>> >and isnull(a.DISMISSALDATE,'')=isnull
(b.DISMISSALDATE,'')
>> >and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
>> >and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
>> >and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
>> >and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
>> >and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
>> >and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
>> >and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
>> >AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
>> >AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim
>> (rtrim(b.DLASTNAME)),'')
>> >AND
>> >isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim
(rtrim
>> (b.DFIRSTNAME)),'')
>> >AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
>> >AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL
>> (b.DISPOSITIONTYPE,'')
>> >)
>> >
>/*********************************************************
***********************************************************
>> ****
>> >remove from staging all the records that are not in DUP
>> table
>**********************************************************
***********************************************************
>> ****/
>> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
>> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT *
>> FROM
>> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
>/*********************************************************
***********************************************************
>> ***
>> >clean up
>**********************************************************
***********************************************************
>> ***/
>> >truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
>> >truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
>> >
>/*********************************************************
>> ***************************
>> >COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG
>> INTO TRANSACTION LOG
>**********************************************************
>> ****************************/
>> >EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
>> >GO
>> >
>> >.
>> >
>
>.
>|||My source is the SQL Server Storage Engine code - look at my signature
below.
The context of Kalen's point implies that the clustered index is covering
the query you're doing. Non-clustered indexes exist to cover queries that
aren't covered by the clustered index. Whether one is faster than the other
depends on the query and the density of the index rows on the page. Bigger
rows imply a deeper index and so seeks to the leaf level will have to
traverse more intermediate levels of the index - there are many factors to
consider. Simply saying that a clustered index is faster than a
non-clustered index is very misleading.
The second quote is again very misleading taken out of context. Yes, a
non-clustered index is separate from the data - that's the whole point. Just
because the whole data row exists in the clustered index doesn't mean you
have to retrieve it every time you want some of the columns from it. If the
keys of a non-clustered index cover the query, there's no need to go to the
clustered index at all - basic index theory.
If all you want to do is select ranges of your data based on a single key, a
clustered index will be faster and always used by the optimizer. For
anything more complex, non-clustered indexes are usually involved - that's
why they exist. Ask any DBA or DB app designer.
Best regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d0801c514d5$8edf36f0$a401280a@.phx.gbl...
> The Book Inside Microsoft SQL Server by Karen Delany,
> chapter 8, page 406 quite 'The query optimizer strongly
> favours a clustered index because such an index allows the
> data to be found directly at the leaf level'.
> The Book SQL Server Performance tuning - Page 343
> Quote 'The index itself (non clustered) is completely
> sepatate from the data, like a book with an index at the
> back'
> Therefore am clusted index points directly to a page where
> as a non clustered does not.
> Now I have given you my source will you give yours ?
> Peter
>
> >--Original Message--
> >Peter - I'd love to know what info source you're using
> for some of the
> >things you say so that I can try to correct it.
> >
> >Why do you think that clustered indexes are faster than
> non-clustered
> >indexes?
> >
> >Non-clustered indexes do slow down insert operations
> (because a record has
> >to be inserted into the non-clustered index AND the base
> table), but even if
> >the table is primarily used for inserts, if you have one
> query that uses a
> >different predicate than the key of your clustered index,
> you're going to
> >want a non-clustered index on it. Given a sufficiently
> fast IO subsystem you
> >should be ok.
> >
> >The ITW advice is correct.
> >
> >Regards
> >
> >--
> >Paul Randal
> >Dev Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >"Peter The Spate" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:10a001c5144a$2ca9d430$a601280a@.phx.gbl...
> >> Hi James,
> >>
> >> My first question is what kinda index are you trying to
> >> creates, there are two types, clustered and non
> clustered,
> >> clustered is faster, but your only allowed and per
> table.
> >>
> >> As for the SQL well thanks but it doesn't really help.
> The
> >> reason is that index's work best on SELECT statements,
> the
> >> best canditates being the where clauses, which is not
> >> included in your SQL.
> >>
> >> Finally (and here comes the real downer) non clustered
> >> indexes are not really recommended on a table that is
> >> going to primary used as an insert table, the reason
> being
> >> there is a lot of maths involved how an index works
> >> internally.
> >>
> >> However here is the good part, you need not do a thing,
> >> SQL Server can work out which indexes it needs itself.
> >> Here is how to do it.
> >>
> >> When you installed SQL Server, you also installed
> >> something called Profiler, start up profiler.
> >>
> >> Click File -> New -> Trace and connect to the server
> where
> >> the db is.
> >>
> >> In Trace Name put anything you want.
> >> In Template name put in SQLProfilerTuning
> >> Click save to table and put in a database, table name to
> >> save it to, don't worry about creating the table it will
> >> do it automatically.
> >>
> >> Then leave it running for about a week.
> >>
> >> Then close it, and open up Enterprise Manager
> >> Select the database the table resides in
> >> Click on Wizards 'Index Tuning Wizards', the rest you
> can
> >> work out for yourself ;)
> >>
> >> Peter
> >>
> >> "Real knowledge is to know the extent of one's
> ignorance."
> >> Confucius
> >>
> >> "That makes soooo knowledgable"
> >> Peter The Spate
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I need to create an index or two indexes. I just don't
> >> know on what columns
> >> >to create this indexes. I have a stored procedure that
> >> I'm supposed to go by
> >> >deciding what indexes to create. Please take a look at
> >> this and give me your
> >> >recommendations. I appreciate your assistance. Thank
> >> you.
> >> >James
> >> >
> >> >CREATE PROCEDURE [DBO].[SP_BBR_DUPLICATE] AS
> >>
> >/*********************************************************
> >> *************************************************
> >> >--STEP-2
> >> >--Check for Duplicates within the file. If found remove
> >> them.
> >>
> >**********************************************************
> >> *************************************************/
> >> >
> >> >SET NOCOUNT ON
> >> >
> >> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >update NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS set
> >> BBR_Sequence_No = '';
> >>
> >/*********************************************************
> >> ***********************
> >> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
> >> TEMP_BBR_FILE_DUP
> >>
> >**********************************************************
> >> ***********************/
> >> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >SELECT
> >> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> >> > DataSource, DType, DLastName,DFirstName, DMidName,
> >> DSuffix,
> >> > DStAddress,DAPARTMENT,DCity, DState,
> >>
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> >> AliasSuffix,
> >> >
> >>
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> >> xID,
> >> > PType,
> >>
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> >> > ComplaintDate,
> >> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> >> > Amount, FilingType,
> >>
> >DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
> >> rNo,DPoBox,PPoBox
> >> >FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> >GROUP BY
> >> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> >> > DataSource, DType, DLastName,DFirstName, DMidName,
> >> DSuffix,
> >> > DStAddress,DAPARTMENT,DCity, DState,
> >>
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> >> AliasSuffix,
> >> >
> >>
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> >> xID,
> >> > PType,
> >>
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> >> > ComplaintDate,
> >> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> >> > Amount, FilingType,
> >>
> >DispositionType,BBR_Sequence_No,bExtract,KeyDate,Researche
> >> rNo,DPoBox,PPoBox
> >> >HAVING COUNT(*)>1
> >> > ORDER BY STATETOPS
> >> >
> >> >
> >>
> >/*********************************************************
> >> *************/
> >> >
> >> >-- INSERT DUPLICATE RECORDS WITHIN A FILE INTO
> >> BBR_FILE_DUP
> >>
> >**********************************************************
> >> ******************************/
> >> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
> >> >SELECT
> >> > StateTops, CountyTops, CourtCode, CaseNo, CourtType,
> >> > DataSource, DType, DLastName,DFirstName, DMidName,
> >> DSuffix,
> >> > DStAddress,DAPARTMENT,DCity, DState,
> >>
> >DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAliasMidName,D
> >> AliasSuffix,
> >> >
> >>
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuffix,CoDTa
> >> xID,
> >> > PType,
> >>
> PLastName,PFirstName,PMidName,PStAddress,PCity,PState,PZip,
> >> > ComplaintDate,
> >> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> >> > Amount, FilingType, DispositionType,
> BBR_Sequence_No,
> >> >bExtract,KeyDate,ResearcherNo,DPoBox,PPoBox,'FD'
> >> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >
> >> >
> >>
> >/*********************************************************
> >> ************
> >> >-- DELETE FROM THE TABLE
> >> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
> >> >ENTRIES
> >>
> >**********************************************************
> >> ************/
> >> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> > FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP A,
> >> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
> >> > WHERE A.STATETOPS=B.STATETOPS
> >> > AND A.COUNTYTOPS=B.COUNTYTOPS
> >> > AND ISNULL(A.CASENO,'')=ISNULL(B.CASENO,'')
> >> > AND ISNULL(A.DLASTNAME,'')=ISNULL
> (B.DLASTNAME,'')
> >> > AND ISNULL(A.DFIRSTNAME,'')=ISNULL
> >> (B.DFIRSTNAME,'')
> >> > AND LTRIM(RTRIM(A.DCITY))=LTRIM(RTRIM(B.DCITY))
> >> > AND ISNULL(A.PLASTNAME,'')=ISNULL
> (B.PLASTNAME,'')
> >> > AND ISNULL(A.FILINGTYPE,'')=ISNULL
> >> (B.FILINGTYPE,'')
> >> > AND ISNULL(A.DISPOSITIONTYPE,'')=ISNULL
> >> (B.DISPOSITIONTYPE,'')
> >> > AND ISNULL(A.MENTALDATE,'')=ISNULL
> >> (B.MENTALDATE,'')
> >> > AND ISNULL(A.COURTCODE,'')=ISNULL
> (B.COURTCODE,'')
> >> > AND ISNULL(A.COURTTYPE,'')=ISNULL
> (B.COURTTYPE,'')
> >> > AND ISNULL(A.DATASOURCE,'')=ISNULL
> >> (B.DATASOURCE,'')
> >> > AND ISNULL(A.DTYPE,'')=ISNULL(B.DTYPE,'')
> >> > AND ISNULL(A.DMIDNAME,'')=ISNULL(B.DMIDNAME,'')
> >> > AND ISNULL(A.DSUFFIX,'')=ISNULL(B.DSUFFIX,'')
> >> > AND ISNULL(A.DSTADDRESS,'')=ISNULL
> >> (B.DSTADDRESS,'')
> >> > AND ISNULL(A.DAPARTMENT,'')=ISNULL
> >> (B.DAPARTMENT,'')
> >> > AND ISNULL(A.DSTATE,'')=ISNULL(B.DSTATE,'')
> >> > AND ISNULL(A.DZIP,'')=ISNULL(B.DZIP,'')
> >> > AND ISNULL(A.DTAXID,'')=ISNULL(B.DTAXID,'')
> >> > AND ISNULL(A.DALIASLASTNAME,'')=ISNULL
> >> (B.DALIASLASTNAME,'')
> >> > AND ISNULL(A.DALIASFIRSTNAME,'')=ISNULL
> >> (B.DALIASFIRSTNAME,'')
> >> > AND ISNULL(A.DALIASMIDNAME,'')=ISNULL
> >> (B.DALIASMIDNAME,'')
> >> > AND ISNULL(A.DALIASSUFFIX,'')=ISNULL
> >> (B.DALIASSUFFIX,'')
> >> > AND ISNULL(A.CODTYPE,'')=ISNULL(B.CODTYPE,'')
> >> > AND ISNULL(A.CODLASTNAME,'')=ISNULL
> >> (B.CODLASTNAME,'')
> >> > AND ISNULL(A.CODFIRSTNAME,'')=ISNULL
> >> (B.CODFIRSTNAME,'')
> >> > AND ISNULL(A.CODMIDNAME,'')=ISNULL
> >> (B.CODMIDNAME,'')
> >> > AND ISNULL(A.CODSUFFIX,'')=ISNULL
> (B.CODSUFFIX,'')
> >> > AND ISNULL(A.CODTAXID,'')=ISNULL(B.CODTAXID,'')
> >> > AND ISNULL(A.PTYPE,'')=ISNULL(B.PTYPE,'')
> >> > AND ISNULL(A.PFIRSTNAME,'')=ISNULL
> >> (B.PFIRSTNAME,'')
> >> > AND ISNULL(A.PMIDNAME,'')=ISNULL(B.PMIDNAME,'')
> >> > AND ISNULL(A.PSTADDRESS,'')=ISNULL
> >> (B.PSTADDRESS,'')
> >> > AND ISNULL(A.PCITY,'')=ISNULL(B.PCITY,'')
> >> > AND ISNULL(A.PSTATE,'')=ISNULL(B.PSTATE,'')
> >> > AND ISNULL(A.PZIP,'')=ISNULL(B.PZIP,'')
> >> > AND ISNULL(A.COMPLAINTDATE,'')=ISNULL
> >> (B.COMPLAINTDATE,'')
> >> > AND ISNULL(A.MENTALDATE,'')=ISNULL
> >> (B.MENTALDATE,'')
> >> > AND ISNULL(A.SATISFIEDDATE,'')=ISNULL
> >> (B.SATISFIEDDATE,'')
> >> > AND ISNULL(A.DISMISSALDATE,'')=ISNULL
> >> (B.DISMISSALDATE,'')
> >> > AND ISNULL(A.POSTEDDATE,'')=ISNULL
> >> (B.POSTEDDATE,'')
> >> > AND ISNULL(A.AMOUNT,'')=ISNULL(B.AMOUNT,'')
> >> > AND ISNULL(A.KEYDATE,'')=ISNULL(B.KEYDATE,'')
> >> > AND ISNULL(A.RESEARCHERNO,'')=ISNULL
> >> (B.RESEARCHERNO,'')
> >> > AND ISNULL(A.DPOBOX,'')=ISNULL(B.DPOBOX,'')
> >> > AND ISNULL(A.PPOBOX,'')=ISNULL(B.PPOBOX,'')
> >> >
> >>
> >/*********************************************************
> >> *****************
> >> >--INSERT THE RECORDS FROM TEMP_FILE_DUP_STAGE BACK INTO
> >> DBO.BBR_DAILY_FEED
> >>
> >**********************************************************
> >> *****************/
> >> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> > SELECT * FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >
> >>
> >/*********************************************************
> >> ***********************
> >> >-- TRUNCATE THE TEMP TABLE
> >> >***********************************/
> >> >TRUNCATE TABLE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >
> >>
> >/*********************************************************
> >> ***
> >> >transfer of all unique records
> >>
> >**********************************************************
> >> ***/
> >> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP (stateTops,
> >> countyTops, dlastName,
> >> >dfirstName, FilingType, Dispositiontype, mentalDate,
> >> amount)
> >> >SELECT distinct stateTops, countyTops, isnull(ltrim
> (rtrim
> >> (dlastName)),''),
> >> >isnull(ltrim(rtrim(dfirstName)),''), isnull
> >> (filingType,''),
> >> >isnull(Dispositiontype,''), substring(mentalDate, 1,6)
> as
> >> mentalDate,
> >> >ltrim(rtrim(amount)) from
> >> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> >
> >>
> >/*********************************************************
> >> *****
> >> >Select records where just the amount are different
> >>
> >**********************************************************
> >> *****/
> >> >INSERT INTO NEWBOOK.DBO.TEMP_BBR_FILE_DUP_AMOUNT
> >> (stateTops, countyTops,
> >> >DLastName, DFirstName, MentalDate, filingType,
> >> dispositionType)
> >> >SELECT stateTops, CountyTops, isnull(ltrim(rtrim
> >> (DLastName)),'') as
> >> >DLastName,isnull(ltrim(rtrim(DFirstName)),'') as
> >> DFirstName, mentalDate,
> >> >isnull(filingType,'') as FilingType, isnull
> >> (dispositionType,'') as
> >> >dispositionType FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >GROUP BY stateTops, countyTops, isnull(ltrim(rtrim
> >> (DLastName)),''),
> >> >isnull(ltrim(rtrim(DFirstName)),''), mentaldate, isnull
> >> (filingType,'') ,
> >> >isnull(dispositionType,'')
> >> >HAVING COUNT(*) > 1
> >> >
> >>
> >/*********************************************************
> >> *****************************
> >> >Open a Cursor which will delete records from
> >> TEMP_BBR_FILE_DUP if that
> >> >record is a dup
> >>
> >**********************************************************
> >> *****************************/
> >> >exec REMOVE_DUPLICATE_AMOUNTS
> >> >
> >>
> >/*********************************************************
> >> ************************
> >> >update the remaining fields
> >>
> >**********************************************************
> >> ************************/
> >> >
> >> >UPDATE NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >SET CASENO = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CASENO
> >> > , COURTCODE => >> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
> >> > , DCITY = NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DCITY
> >> > ,
> >> PLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PLASTNAME
> >> > ,
> >> MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE
> >> > ,
> >> COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTTYPE
> >> > ,
> >> DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATASOURCE
> >> > , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
> >> > ,
> >> DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAME
> >> > ,
> >> DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
> >> > ,
> >> DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTADDRESS
> >> > ,
> >> DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPARTMENT
> >> > ,
> DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
> >> > , DZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DZIP
> >> > ,
> DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
> >> > ,
> >>
> DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
> >> NAME
> >> > ,
> >>
> DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
> >> STNAME
> >> > ,
> >>
> DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
> >> ME
> >> > ,
> >>
> DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
> >> > ,
> >> CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
> >> > ,
> >>
> CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
> >> > ,
> >>
> CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
> >> > ,
> >> CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMIDNAME
> >> > ,
> >> CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUFFIX
> >> > ,
> >> CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXID
> >> > , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
> >> > ,
> >> PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
> >> > ,
> >> PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
> >> > ,
> >> PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
> >> > , PCITY=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PCITY
> >> > ,
> PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
> >> > , PZIP=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PZIP
> >> > ,
> >>
> COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
> >> TE
> >> > ,
> >>
> SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
> >> TE
> >> > ,
> >>
> DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
> >> TE
> >> > ,
> >> POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTEDDATE
> >> > ,
> >> KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
> >> > ,
> >>
> RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
> >> > ,
> DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
> >> > ,
> PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
> >> >FROM NEWBOOK.DBO.TEMP_BBR_FILE_DUP
> >> >INNER JOIN NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> >> >ON
> >>
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
> >> ILY_STAGE_BANDS.STATETOPS
> >> -- STATETOPS HAS TO BE SAME
> >> > AND
> >>
> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
> >> AILY_STAGE_BANDS.COUNTYTOPS
> >> > AND
> >> >ISNULL(LTRIM(RTRIM
> >> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL
> (LTRIM
> >> (RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
> >> --
> >> >HAS TO BE SAME (LOW ERRORS)
> >> > AND ISNULL(
> >> >LTRIM(RTRIM
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
> >> =ISNULL(LTRIM(RTRIM
> >> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
> >> >TAKES CARE OF HUSBAND WIFE SCENARIOS
> >> > AND
> >> >ISNULL(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.FILINGTYPE,'')
> =ISNULL
> >> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
> >> -- HAS TO BE SAME
> >> > AND
> >> >ISNULL
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
> >> =ISNULL
> >> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') -
> -
> >> >IN CASES WHERE THE COMPARISION IS BETWEEN A
> SATISFACTION
> >> STATUS
> >> > AND
> >> >ISNULL(SUBSTRING
> >> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')
> =ISNULL
> >> (SUBSTRING
> >> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') -
> -
> >> >DATE OF THE WISE, HAS TO BE SAME
> >> > AND ISNULL
> >> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') => >> >ISNULL(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.amount,'')
> >> >
> >>
> >/*********************************************************
> >>
> ***********************************************************
> >> **
> >> >insert the rejected records into the reject table
> >>
> >**********************************************************
> >>
> ***********************************************************
> >> **/
> >> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_REJECTS
> >> >SELECT *,'FD' FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS a
> >> where NOT EXISTS
> >> >(select * from NEWBOOK.DBO.TEMP_BBR_FILE_DUP b where
> >> >isnull(a.caseno,'') = isnull(b.caseno ,'')
> >> >and isnull(a.COURTCODE,'') = isnull(b.COURTCODE ,'')
> >> >and isnull(a.DCITY,'') = isnull(b.DCITY ,'')
> >> >and isnull(a.PLASTNAME,'') = isnull(b.PLASTNAME,'')
> >> >and isnull(a.MENTALDATE,'') = isnull(b.MENTALDATE,'')
> >> >and isnull(a.COURTTYPE,'') = isnull(b.COURTTYPE,'')
> >> >and isnull(a.DATASOURCE,'') = isnull(b.DATASOURCE,'')
> >> >and isnull(a.DTYPE,'') = isnull(b.DTYPE,'')
> >> >and isnull(a.DMIDNAME,'')= isnull(b.DMIDNAME,'')
> >> >and isnull(a.DSUFFIX,'') = isnull(b.DSUFFIX,'')
> >> >and isnull(a.DSTADDRESS,'')= isnull(b.DSTADDRESS,'')
> >> >and isnull(a.DAPARTMENT,'')=isnull(b.DAPARTMENT,'')
> >> >and isnull(a.DSTATE,'')=isnull(b.DSTATE,'')
> >> >and isnull(a.DZIP,'')=isnull(b.DZIP,'')
> >> >and isnull(a.DTAXID,'')=isnull(b.DTAXID,'')
> >> >and isnull(a.DALIASLASTNAME,'')=isnull
> >> (b.DALIASLASTNAME,'')
> >> >and isnull(a.DALIASFIRSTNAME,'')=isnull
> >> (b.DALIASFIRSTNAME,'')
> >> >and isnull(a.DALIASMIDNAME,'')=isnull
> (b.DALIASMIDNAME,'')
> >> >and isnull(a.DALIASSUFFIX,'')=isnull(b.DALIASSUFFIX,'')
> >> >and isnull(a.CODTYPE,'')=isnull(b.CODTYPE,'')
> >> >and isnull(a.CODLASTNAME,'')=isnull(b.CODLASTNAME,'')
> >> >and isnull(a.CODFIRSTNAME,'')=isnull(b.CODFIRSTNAME,'')
> >> >and isnull(a.CODMIDNAME,'')=isnull(b.CODMIDNAME,'')
> >> >and isnull(a.CODSUFFIX,'')=isnull(b.CODSUFFIX,'')
> >> >and isnull(a.CODTAXID,'')=isnull(b.CODTAXID,'')
> >> >and isnull(a.PTYPE,'')=isnull(b.PTYPE,'')
> >> >and isnull(a.PFIRSTNAME,'')=isnull(b.PFIRSTNAME,'')
> >> >and isnull(a.PMIDNAME,'')=isnull(b.PMIDNAME,'')
> >> >and isnull(a.PSTADDRESS,'')=isnull(b.PSTADDRESS,'')
> >> >and isnull(a.PCITY,'')=isnull(b.PCITY,'')
> >> >and isnull(a.PSTATE,'')=isnull(b.PSTATE,'')
> >> >and isnull(a.PZIP,'')=isnull(b.PZIP,'')
> >> >and isnull(a.COMPLAINTDATE,'')=isnull
> (b.COMPLAINTDATE,'')
> >> >and isnull(a.SATISFIEDDATE,'')=isnull
> (b.SATISFIEDDATE,'')
> >> >and isnull(a.DISMISSALDATE,'')=isnull
> (b.DISMISSALDATE,'')
> >> >and isnull(a.POSTEDDATE,'')=isnull(b.POSTEDDATE,'')
> >> >and isnull(a.AMOUNT,'')=isnull(b.AMOUNT,'')
> >> >and isnull(a.KEYDATE,'')=isnull(b.KEYDATE,'')
> >> >and isnull(a.RESEARCHERNO,'')=isnull(b.RESEARCHERNO,'')
> >> >and isnull(a.DPOBOX,'')=isnull(b.DPOBOX,'')
> >> >and isnull(a.PPOBOX,'')=isnull(b.PPOBOX,'')
> >> >and isnull(a.STATETOPS,'')=isnull(b.STATETOPS,'')
> >>
> >> >AND isnull(a.COUNTYTOPS,'')=isnull(b.COUNTYTOPS,'')
> >> >AND isnull(ltrim(rtrim(a.DLASTNAME)),'')=isnull(ltrim
> >> (rtrim(b.DLASTNAME)),'')
> >> >AND
> >> >isnull(ltrim(rtrim(a.DFIRSTNAME)),'')=isnull(ltrim
> (rtrim
> >> (b.DFIRSTNAME)),'')
> >> >AND ISNULL(a.FILINGTYPE,'')=ISNULL(b.FILINGTYPE,'')
> >>
> >> >AND ISNULL(a.DISPOSITIONTYPE,'')=ISNULL
> >> (b.DISPOSITIONTYPE,'')
> >> >)
> >> >
> >>
> >/*********************************************************
> >>
> ***********************************************************
> >> ****
> >> >remove from staging all the records that are not in DUP
> >> table
> >>
> >**********************************************************
> >>
> ***********************************************************
> >> ****/
> >> >DELETE FROM NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS;
> >> >INSERT INTO NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS SELECT *
> >> FROM
> >> >NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
> >>
> >/*********************************************************
> >>
> ***********************************************************
> >> ***
> >> >clean up
> >>
> >**********************************************************
> >>
> ***********************************************************
> >> ***/
> >> >truncate table NEWBOOK.dbo.TEMP_BBR_FILE_DUP_AMOUNT;
> >> >truncate table NEWBOOK.DBO.TEMP_BBR_FILE_DUP;
> >> >
> >>
> >/*********************************************************
> >> ***************************
> >> >COUNT THE FILE REJECTS FROM TEMP_BBR_FILE_DUP AND LOG
> >> INTO TRANSACTION LOG
> >>
> >**********************************************************
> >> ****************************/
> >> >EXEC [DBO].[SP_BBR_DAILY_TRANS_LOG_3]
> >> >GO
> >> >
> >> >.
> >> >
> >
> >
> >.
> >