Create Index

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,DAliasFirstNa
me,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,P
ostedDate,
> 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,DAliasFirstNa
me,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,P
ostedDate,
> 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,DAliasFirstNa
me,DAliasMidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> ComplaintDate,
MentalDate,SatisfiedDate,DismissalDate,P
ostedDate,
> Amount, FilingType, DispositionType, BBR_Sequence_No,
> bExtract,KeyDate,ResearcherNo,DPoBox,PPo
Box,'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
>/ ****************************************
*****************
*****************************reen">
>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;
>/ ****************************************
*****************
***************************en">
>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...[vbcol=seagreen]
> 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
>
>
> know on what columns
> I'm supposed to go by
> this and give me your
> you.
> ****************************************
*********
> them.
> ****************************************
*********/
> BBR_Sequence_No = '';
> ***********************
> TEMP_BBR_FILE_DUP
> ***********************/
> DSuffix,
> AliasSuffix,
> CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
> xID,
> PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> MentalDate,SatisfiedDate,DismissalDate,P
ostedDate,
> rNo,DPoBox,PPoBox
> DSuffix,
> AliasSuffix,
> CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
> xID,
> PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> MentalDate,SatisfiedDate,DismissalDate,P
ostedDate,
> rNo,DPoBox,PPoBox
> *************/
> BBR_FILE_DUP
> ******************************/
> DSuffix,
> AliasSuffix,
> CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
> xID,
> PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> MentalDate,SatisfiedDate,DismissalDate,P
ostedDate,
> ************
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS ALL DUPLICATE
> ************/
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS B
> (B.DFIRSTNAME,'')
> (B.FILINGTYPE,'')
> (B.DISPOSITIONTYPE,'')
> (B.MENTALDATE,'')
> (B.DATASOURCE,'')
> (B.DSTADDRESS,'')
> (B.DAPARTMENT,'')
> (B.DALIASLASTNAME,'')
> (B.DALIASFIRSTNAME,'')
> (B.DALIASMIDNAME,'')
> (B.DALIASSUFFIX,'')
> (B.CODLASTNAME,'')
> (B.CODFIRSTNAME,'')
> (B.CODMIDNAME,'')
> (B.PFIRSTNAME,'')
> (B.PSTADDRESS,'')
> (B.COMPLAINTDATE,'')
> (B.MENTALDATE,'')
> (B.SATISFIEDDATE,'')
> (B.DISMISSALDATE,'')
> (B.POSTEDDATE,'')
> (B.RESEARCHERNO,'')
> *****************
> DBO.BBR_DAILY_FEED
> *****************/
> ***********************
> ***
> ***/
> countyTops, dlastName,
> amount)
> (dlastName)),''),
> (filingType,''),
> mentalDate,
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS
> *****
> *****/
> (stateTops, countyTops,
> dispositionType)
> (DLastName)),'') as
> DFirstName, mentalDate,
> (dispositionType,'') as
> (DLastName)),''),
> (filingType,'') ,
> *****************************
> TEMP_BBR_FILE_DUP if that
> *****************************/
> ************************
> ************************/
> NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTCODE
> 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
> 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
> 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
> PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRSTNAME
> PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAME
> PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTADDRESS
> 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
> ILY_STAGE_BANDS.STATETOPS
> -- STATETOPS HAS TO BE SAME
> AILY_STAGE_BANDS.COUNTYTOPS
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISNULL(LTRIM
> (RTRIM(NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DLASTNAME)),'')
> --
> =ISNULL(LTRIM(RTRIM
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DFIRSTNAME)),'') --
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.FILINGTYPE,'')
> -- HAS TO BE SAME
> =ISNULL
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISPOSITIONTYPE,'') --
> STATUS
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.MENTALDATE,1,6),'')=ISNULL
> (SUBSTRING
> (NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTALDATE,1,6),'') --
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.AMOUNT,'') =
> ****************************************
*******************
> **
> ****************************************
*******************
> **/
> where NOT EXISTS
> (b.DALIASLASTNAME,'')
> (b.DALIASFIRSTNAME,'')
>
> (rtrim(b.DLASTNAME)),'')
> (b.DFIRSTNAME)),'')
>
> (b.DISPOSITIONTYPE,'')
> ****************************************
*******************
> ****
> table
> ****************************************
*******************
> ****/
> FROM
> ****************************************
*******************
> ***
> ****************************************
*******************
> ***/
> ***************************
> INTO TRANSACTION LOG
> ****************************/|||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...
clustered,[vbcol=seagreen]
table.[vbcol=seagreen]
The[vbcol=seagreen]
the[vbcol=seagreen]
being[vbcol=seagreen]
where[vbcol=seagreen]
can[vbcol=seagreen]
ignorance."[vbcol=seagreen]
>/ ****************************************
*****************
> ****************************************
******************
>/ ****************************************
*****************
> ****************************************
******************
> DZip,DTaxID,DAliasLastNAme,DAliasFirstNa
me,DAliasMidName,D
CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa[vbcol=seagreen]
PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,[vbcol=seagreen]
> DispositionType,BBR_Sequence_No,bExtract
,KeyDate,Researche
> DZip,DTaxID,DAliasLastNAme,DAliasFirstNa
me,DAliasMidName,D
CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa[vbcol=seagreen]
PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,[vbcol=seagreen]
> DispositionType,BBR_Sequence_No,bExtract
,KeyDate,Researche
>/ ****************************************
*****************
> ****************************************
******************
> DZip,DTaxID,DAliasLastNAme,DAliasFirstNa
me,DAliasMidName,D
CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa[vbcol=seagreen]
PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,[vbcol=seagreen]
BBR_Sequence_No,[vbcol=seagreen]
>/ ****************************************
*****************
> ****************************************
******************
(B.DLASTNAME,'')[vbcol=seagreen]
(B.PLASTNAME,'')[vbcol=seagreen]
(B.COURTCODE,'')[vbcol=seagreen]
(B.COURTTYPE,'')[vbcol=seagreen]
(B.CODSUFFIX,'')[vbcol=seagreen]
>/ ****************************************
*****************
> ****************************************
******************
>/ ****************************************
*****************
>/ ****************************************
*****************
> ****************************************
******************
(rtrim[vbcol=seagreen]
as[vbcol=seagreen]
>/ ****************************************
*****************
> ****************************************
******************
>/ ****************************************
*****************
> ****************************************
******************
>/ ****************************************
*****************
> ****************************************
******************
DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE[vbcol=seagreen]
DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID[vbcol=seagreen]
DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST[vbcol=seagreen]
DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR[vbcol=seagreen]
DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA[vbcol=seagreen]
DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX[vbcol=seagreen]
CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME[vbcol=seagreen]
CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME[vbcol=seagreen]
PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE[vbcol=seagreen]
COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA[vbcol=seagreen]
SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA[vbcol=seagreen]
DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA[vbcol=seagreen]
RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO[vbcol=seagreen]
DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX[vbcol=seagreen]
PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX[vbcol=seagreen]
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.STATETOPS=NEWBOOK.DBO.BBR_DA
>NEWBOOK.DBO.TEMP_BBR_FILE_DUP.COUNTYTOPS=NEWBOOK.DBO.BBR_D
(LTRIM[vbcol=seagreen]
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')[vbcol=seagreen]
=ISNULL[vbcol=seagreen]
(NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')[vbcol=seagreen]
-[vbcol=seagreen]
SATISFACTION[vbcol=seagreen]
=ISNULL[vbcol=seagreen]
-[vbcol=seagreen]
>/ ****************************************
*****************
****************************************
*******************[vbcol=seagreen]
> ****************************************
******************
****************************************
*******************[vbcol=seagreen]
(b.DALIASMIDNAME,'')[vbcol=seagreen]
(b.COMPLAINTDATE,'')[vbcol=seagreen]
(b.SATISFIEDDATE,'')[vbcol=seagreen]
(b.DISMISSALDATE,'')[vbcol=seagreen]
(rtrim[vbcol=seagreen]
>/ ****************************************
*****************
****************************************
*******************[vbcol=seagreen]
> ****************************************
******************
****************************************
*******************[vbcol=seagreen]
>/ ****************************************
*****************
****************************************
*******************[vbcol=seagreen]
> ****************************************
******************
****************************************
*******************[vbcol=seagreen]
>/ ****************************************
*****************
> ****************************************
******************
>
>.
>|||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...[vbcol=seagreen]
> 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
>
> for some of the
> non-clustered
> (because a record has
> table), but even if
> query that uses a
> you're going to
> fast IO subsystem you
> confers no rights.
> wrote in message
> clustered,
> table.
> The
> the
> being
> where
> can
> ignorance."
> CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
> PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
> PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> CoDType,CoDLastName,CoDFirstName,CoDMidN
ame,CoDSuffix,CoDTa
> PLastName,PFirstName,PMidName,PStAddress
,PCity,PState,PZip,
> BBR_Sequence_No,
> (B.DLASTNAME,'')
> (B.PLASTNAME,'')
> (B.COURTCODE,'')
> (B.COURTTYPE,'')
> (B.CODSUFFIX,'')
> (rtrim
> as
> DSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTATE
> DTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTAXID
> DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASLAST
> DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASFIR
> DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASMIDNA
> DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DALIASSUFFIX
> CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODLASTNAME
> CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODFIRSTNAME
> PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
> COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COMPLAINTDA
> SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SATISFIEDDA
> DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DISMISSALDA
> RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RESEARCHERNO
> DPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DPOBOX
> PPOBOX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PPOBOX
> (LTRIM
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DFIRSTNAME)),'')
> =ISNULL
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DISPOSITIONTYPE,'')
> -
> SATISFACTION
> =ISNULL
> -
> ****************************************
*******************
> ****************************************
*******************
> (b.DALIASMIDNAME,'')
> (b.COMPLAINTDATE,'')
> (b.SATISFIEDDATE,'')
> (b.DISMISSALDATE,'')
> (rtrim
> ****************************************
*******************
> ****************************************
*******************
> ****************************************
*******************
> ****************************************
*******************