Create INDEX WITH DROP_EXISTING

Hello,
Can I create an index ( which is not clustered) with DROP_EXISTING ON ?
Shall I still use on [PRIMARY]?
Create INDEX [name] ON [table name] ([column])
WITH DROP_EXISTING ON [PRIMARY]
Thank you,
Y
:rolleyes:clustered or not, you can use this.

Primary refers to the filegroup you would like to physically create the index on and it is an optional part of the create index statement.|||Thank you so much

'CREATE INDEX' Statement...Faster?

I am indexing a 32 millon row table with a unqine clustered index and
the run time for the create index statement is over 4 hours long. This
runs is too slow and may cost me a job If i cant find a faster way to
create index on large table.
Can anyone help.
Hi
What is the version of SQL Server?
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
|||i don't think it would be faster. Clustered Index is the way data is stored
physically in the hard disk.
bye!
"GreenHillCourt@.gmail.com" wrote:

> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
>
|||Setting the database to simple recovery should improve compared to full recovery. At least it will
cut down on the logging. But the data shuffling still has to occur of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:15C6F565-D67E-4912-8424-BFBDF0EC88A2@.microsoft.com...[vbcol=seagreen]
>i don't think it would be faster. Clustered Index is the way data is stored
> physically in the hard disk.
> bye!
> "GreenHillCourt@.gmail.com" wrote:
|||<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
As others have said, if you're creating the clustered index, not much you
can do other than perhaps look at a faster disk subsystem (say raid 10 vs
RAID 5, etc.)
If you mean you're creating a non-clustered index on a table that has an
existing clustered index, one thing that can certainly help is putting the
non-clustered index on its own set of disks.

> Can anyone help.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Have you tried creating the index without clustering? If I'm not mistaken,
it should then not have to physically re-order the data.
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
|||"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
- drop all unnecessary nonclustered indexes
- if possible, connect in single user mode
- add extra memory to the server, don't "pin" tables
- make sure you have enough I/O and bandwidth, both for your table, the
TempDB and the log file
- make sure you have enough free space before you start, at least 1.5
times the table size
- set the database recovery mode to simple
- choose an appropriate fillfactor. Any value below 70 is probably a bad
idea
4 hours seems excessive. How much space does the table occupy (before
you start)? What is your I/O system (number of disks, etc.)?
Gert-Jan
SQL Server MVP

'CREATE INDEX' Statement...Faster?

I am indexing a 32 millon row table with a unqine clustered index and
the run time for the create index statement is over 4 hours long. This
runs is too slow and may cost me a job If i cant find a faster way to
create index on large table.
Can anyone help.Hi
What is the version of SQL Server?
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.|||i don't think it would be faster. Clustered Index is the way data is stored
physically in the hard disk.
bye!
"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
>|||Setting the database to simple recovery should improve compared to full recovery. At least it will
cut down on the logging. But the data shuffling still has to occur of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:15C6F565-D67E-4912-8424-BFBDF0EC88A2@.microsoft.com...
>i don't think it would be faster. Clustered Index is the way data is stored
> physically in the hard disk.
> bye!
> "GreenHillCourt@.gmail.com" wrote:
>> I am indexing a 32 millon row table with a unqine clustered index and
>> the run time for the create index statement is over 4 hours long. This
>> runs is too slow and may cost me a job If i cant find a faster way to
>> create index on large table.
>> Can anyone help.|||<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
As others have said, if you're creating the clustered index, not much you
can do other than perhaps look at a faster disk subsystem (say raid 10 vs
RAID 5, etc.)
If you mean you're creating a non-clustered index on a table that has an
existing clustered index, one thing that can certainly help is putting the
non-clustered index on its own set of disks.
> Can anyone help.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Have you tried creating the index without clustering? If I'm not mistaken,
it should then not have to physically re-order the data.
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.|||"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
- drop all unnecessary nonclustered indexes
- if possible, connect in single user mode
- add extra memory to the server, don't "pin" tables
- make sure you have enough I/O and bandwidth, both for your table, the
TempDB and the log file
- make sure you have enough free space before you start, at least 1.5
times the table size
- set the database recovery mode to simple
- choose an appropriate fillfactor. Any value below 70 is probably a bad
idea
4 hours seems excessive. How much space does the table occupy (before
you start)? What is your I/O system (number of disks, etc.)?
--
Gert-Jan
SQL Server MVP

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,DAliasFirstName,DAlias MidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,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,DAlias MidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,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,DAlias MidName,D
AliasSuffix,
>
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa
xID,
> PType,
PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,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.PLASTN AME
>,
MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTA LDATE
> ,
COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTT YPE
> ,
DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATAS OURCE
> , DTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DTYPE
> ,
DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAM E
> ,
DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
> ,
DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTAD DRESS
> ,
DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPAR TMENT
> , 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.D ALIASLAST
NAME
> ,
DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS. DALIASFIR
STNAME
> ,
DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DA LIASMIDNA
ME
> ,
DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAL IASSUFFIX
> ,
CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
> ,
CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODL ASTNAME
> ,
CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COD FIRSTNAME
> ,
CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMI DNAME
> ,
CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUF FIX
> ,
CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXI D
> , PTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PTYPE
> ,
PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRS TNAME
> ,
PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAM E
> ,
PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTAD DRESS
> , 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.CO MPLAINTDA
TE
> ,
SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SA TISFIEDDA
TE
> ,
DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DI SMISSALDA
TE
> ,
POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTE DDATE
> ,
KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
> ,
RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RES EARCHERNO
> , 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.D BO.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)),'')=ISN ULL(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.DFIRSTN AME)),'')
=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.DISPOSITIONT YPE,'')
=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(ltri m(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...[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,CoDMidName,CoDSuf fix,CoDTa
> xID,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,PZip,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> rNo,DPoBox,PPoBox
> DSuffix,
> AliasSuffix,
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa
> xID,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,PZip,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> rNo,DPoBox,PPoBox
> *************/
> BBR_FILE_DUP
> ******************************/
> DSuffix,
> AliasSuffix,
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa
> xID,
> PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,PZip,
> MentalDate,SatisfiedDate,DismissalDate,PostedDate,
> ************
> 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.PLASTN AME
> MENTALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.MENTA LDATE
> COURTTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COURTT YPE
> DATASOURCE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DATAS OURCE
> DMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DMIDNAM E
> DSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSUFFIX
> DSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DSTAD DRESS
> DAPARTMENT=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAPAR TMENT
> DALIASLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.D ALIASLAST
> NAME
> DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS. DALIASFIR
> STNAME
> DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DA LIASMIDNA
> ME
> DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAL IASSUFFIX
> CODTYPE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTYPE
> CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODL ASTNAME
> CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COD FIRSTNAME
> CODMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODMI DNAME
> CODSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODSUF FIX
> CODTAXID=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODTAXI D
> PFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PFIRS TNAME
> PMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PMIDNAM E
> PSTADDRESS=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTAD DRESS
> COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CO MPLAINTDA
> TE
> SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SA TISFIEDDA
> TE
> DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DI SMISSALDA
> TE
> POSTEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.POSTE DDATE
> KEYDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.KEYDATE
> RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RES EARCHERNO
> ILY_STAGE_BANDS.STATETOPS
> -- STATETOPS HAS TO BE SAME
> AILY_STAGE_BANDS.COUNTYTOPS
> (NEWBOOK.DBO.TEMP_BBR_FILE_DUP.DLASTNAME)),'')=ISN ULL(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[vbcol=seagreen]
>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,DAliasFirstName,DAlias MidName,D
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa[vbcol=seagreen]
PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,PZip,[vbcol=seagreen]
>DispositionType,BBR_Sequence_No,bExtract,KeyDate, Researche
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAlias MidName,D
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa[vbcol=seagreen]
PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,PZip,[vbcol=seagreen]
>DispositionType,BBR_Sequence_No,bExtract,KeyDate, Researche
>/************************************************** *******
>************************************************* *********
>DZip,DTaxID,DAliasLastNAme,DAliasFirstName,DAlias MidName,D
CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa[vbcol=seagreen]
PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,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.D ALIASLAST[vbcol=seagreen]
DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS. DALIASFIR[vbcol=seagreen]
DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DA LIASMIDNA[vbcol=seagreen]
DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAL IASSUFFIX[vbcol=seagreen]
CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODL ASTNAME[vbcol=seagreen]
CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COD FIRSTNAME[vbcol=seagreen]
PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE[vbcol=seagreen]
COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CO MPLAINTDA[vbcol=seagreen]
SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SA TISFIEDDA[vbcol=seagreen]
DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DI SMISSALDA[vbcol=seagreen]
RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RES EARCHERNO[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.D BO.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]
>************************************************* *********
************************************************** *********
>/************************************************** *******
>************************************************* *********
>
>.
>
|||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,CoDMidName,CoDSuf fix,CoDTa
> PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,PZip,
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa
> PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,PZip,
> CoDType,CoDLastName,CoDFirstName,CoDMidName,CoDSuf fix,CoDTa
> PLastName,PFirstName,PMidName,PStAddress,PCity,PSt ate,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.D ALIASLAST
> DALIASFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS. DALIASFIR
> DALIASMIDNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DA LIASMIDNA
> DALIASSUFFIX=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DAL IASSUFFIX
> CODLASTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CODL ASTNAME
> CODFIRSTNAME=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.COD FIRSTNAME
> PSTATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.PSTATE
> COMPLAINTDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.CO MPLAINTDA
> SATISFIEDDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.SA TISFIEDDA
> DISMISSALDATE=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.DI SMISSALDA
> RESEARCHERNO=NEWBOOK.DBO.BBR_DAILY_STAGE_BANDS.RES EARCHERNO
> 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
> ************************************************** *********
> ************************************************** *********
> ************************************************** *********
> ************************************************** *********

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
> ****************************************
*******************
> ****************************************
*******************
> ****************************************
*******************
> ****************************************
*******************