my table :
CREATE TABLE dbo.tblDocument(
docId uniqueidentifier ROWGUIDCOL NOT NULL,
docOrgId int NOT NULL,
docOrgSrvId smallint NOT NULL,
docOwnerDepartmentId uniqueidentifier NOT NULL,
docOwnerPersonnelId uniqueidentifier NOT NULL,
docOwnerName nvarchar(150) NOT NULL,
docDescription nvarchar(max) NOT NULL,
docType smallint NOT NULL,
docNo nvarchar(40) NOT NULL,
docDate char(8) NOT NULL,
docSubject nvarchar(500) NULL,
docSecurityId tinyint NOT NULL,
docUrgencyId tinyint NOT NULL,
docArchiveTotal smallint NOT NULL,
docAttachTotal smallint NOT NULL,
docRegisterTotal smallint NOT NULL,
docRelationTotal smallint NOT NULL,
docSendTotal smallint NOT NULL,
docSignatureTotal smallint NOT NULL,
docTitleTotal smallint NOT NULL,
docDeleteOk bit NOT NULL,
docRegisterDate datetime NOT NULL,
docProperties nvarchar(max) NOT NULL,
docTemplate nvarchar(max) NOT NULL,
docKeyWord nvarchar(300) NOT NULL,
DocAttachType nvarchar(300) NULL,
docOrganizationId uniqueidentifier NOT NULL,
docRegNo varchar(50) NOT NULL,
docRegDate char(8) NOT NULL,
CONSTRAINT PK_tblDocument PRIMARY KEY CLUSTERED
(
docId ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
)



sample queries :

SELECT * FROM (
SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY docRegisterDate ) as ROW_NUM, DocId,typName, DocSubject,docRegNo,docNo,DocDate,docOwnerName,doc RegisterDate,docType
FROM dbo.tblDocument WITH (NOLOCK)
INNER JOIN (SELECT accDocId, accDepartmentId FROM tblAccess WITH (NOLOCK) GROUP BY accDocId, accDepartmentId) AS tblAccess
ON dbo.tblDocument.docId = tblAccess.accDocId
INNER JOIN IstgRef.dbo.tbrType with (nolock) on tblDocument.docType = tbrType.typId

WHERE docType <> 1002 AND tblAccess.accDepartmentId ='6FCFF50F-012D-436D-B7A1-D9991ADA417F' and docNo LIKE '%123%' and docDate between '13880713' and '13880813' and docSubject LIKE '%test%'
ORDER BY ROW_NUM
)
INNERSELECT
WHERE ROW_NUM > 10

and indexes are create on these columns: docDate,docSubject,docNo,docRegisterDate

pootle flump pootle flump is offline

"And I couldn't make sense of much mishaalsy said "

goodday to you too , yea it didnt make any sense to me either as least as it does to you .
without having data review, version of software, table structure , sample of a generally parse query . ................ it makes no sense to what so ever to me or to any one of what others say .


it will be much better if one would post msdn pages and fill this thread with copy right act material .

but now i have a table structure .. lets give it a try .

hey fara

why DocNo instead of docId uniqueidentifier ROWGUIDCOL NOT NULL, ??



Unfortuantly there isn't one simple answer. I would start with what you've suggested yourself, but then I'd load up the tables with appropriate quantities of data and kick them through profiler (SQL Server Query Execution Plan Analysis) and look at the generated execution plan. Benchmarking really is the only way to work it out.

If you get index seeks, then all is good: no more work really needed. If you have index scans, then see if the indexes can be tweaked to turn them into seeks. If you see any bookmark lookups in the execution plan, consider having other columns into the index so the index itself covers the query.

give me a couple of lines of the data you have ( insert statements);

execution plan :
http://www.freeimagehosting.net/uploads/69426d1da3.jpg

insert statements :

INSERT INTO tblDocument (docId ,docOrgId ,docOrgSrvId ,docOwnerDepartmentId ,docOwnerPersonnelId ,docOwnerName ,docDescription ,docType ,docNo,docDate ,docSubject ,docSecurityId ,docUrgencyId ,docArchiveTotal ,docAttachTotal ,docRegisterTotal ,docRelationTotal ,docSendTotal ,docSignatureTotal ,docTitleTotal ,docDeleteOk ,docRegisterDate ,docProperties ,docTemplate ,docKeyWord ,DocAttachType ,docOrganizationId ,docRegNo ,docRegDate )
VALUES(NEWID(),1,700,'0e3fdf44-9007-4f24-913f-be0d2351c10b','2d362963-a835-4597-9d71-1310848bf1ce','ME' , N'<P align=justify><FONT face="B Zar,Zar" size=4>باسلام<BR>&nbsp; احتراماً، عطف بنامه شماره 52652/502 مورخ 24/4/86 واصله از حوزه معاونت هماهنگی و برنامه ریزی، باستحضار میرساند: طراحی، ساخت، حمل و نصب تجهیزات آموزشی بوستان بازیافت توسط شركت ارگ سارگون به شماره قرارداد 1371/502 مورخ 14/1/86 در حال حاضر به اتمام رسیده و تحویل موقت آن صورت پذیرفته است</FONT></P>',
1001,500,'13880504','نامه شماره 92663' , 41,46,2,3,4,1,5,6,1,1,GETDATE(),'N',N'ddlPageType= 212|ddlNoAlign=200|ddlAttachAlign=200|ddlDateAlign =200|ddlTitr1Align=202|ddlTitr2Align=201|ddlCus1Al ign=201|ddlTitleAlign=202|ddlCus2Align=202|ddlSign Align=200|ddlFooterAlign=201|ddlCopyAlign=202|ddlS enderAlign=202|ddlSubjectAlign=202|ddlNoFont=218|d dlDateFont=218|ddlAttachFont=218|ddlTitr1Font=203| ddlTitr2Font=203|ddlCus1Font=218|ddlCus2Font=218|d dlTitleFont=218|ddlFooterFont=203|ddlCopyFont=218| ddlSubjectFont=218|ddlSenderFont=218|ddlSignTitrAl ign=200|ddlSignTitrFont=203|chkNoFI=False|chkNoFB= True|chkNoFU=False|chkDateFI=False|chkDateFB=True| chkDateFU=False|chkAttachFI=False|chkAttachFB=True |chkAttachFU=False|chkTitr1FI=False|chkTitr1FB=Tru e|chkTitr1FU=False|chkTitr2FI=True|chkTitr2FB=Fals e|chkTitr2FU=False|chkCus1=True|chkCus1FI=False|ch kCus1FU=False|chkCus2=True|chkCus2FI=False|chkCus2 FU=False|chkTitleFI=False|chkTitleFU=False|chkSend er=False|chkSenderFI=False|chkSubject=True|chkSend erFU=False|chkSubjectFI=False|chkSubjectFU=False|c hkCopyFI=False|chkCopyFU=False|chkFooterFI=False|c hkFooterFB=False|chkFooterFU=False|chkCus1FB=False |chkCus2FB=False|chkTitleFB=True|chkSenderFB=True| chkSubjectFB=True|chkCopyFB=False|chkNo=True|chkDa te=True|chkAttach=True|chkTitr1=False|chkTitr2=Fal se|chkImage=False|chkSign=True|chkFooter=False|chk Body=True|chkCopy=True|chkTitle=True|chkSignTitr=F alse|chkSignTitrFB=False|chkSignTitrFI=False|chkSi gnTitrFU=False|txtNoLeft=15|txtNoWidth=20|txtDateT op=21|txtDateLeft=15|txtDateWidth=20|txtAttachLeft =15|txtAttachWidth=20|txtTitr1Left=60|txtTitr1Widt h=100|txtTitr2Left=60|txtTitr2Width=100|txtImageLe ft=150|txtAttachTop=30|txtTitr1Top=0|txtTitr2Top=1 0|txtImageTop=5|txtDateFS=14|txtAttachFS=14|txtNoF S=14|txtTitr1FS=24|txtTitr2FS=10|txtCus1Top=35|txt Cus1Left=70|txtCus1Width=180|txtCus1FS=18|txtCus1T itr=بسمه تعالي|txtCus2Top=0|txtCus2Left=105|txtCus2Width=10 0|txtCus2FS=13|txtCus2Titr=آرزو و اميد همه ايرانيان "استقلال ملي ، عزت ملي و رفاه عمومي ملت" است|txtTitleTop=10|txtTitleLeft=65|txtTitleWidth=1 70|txtTitleFS=18|txtSenderTop=0|txtSubjectTop=3|tx tCopyTop=0|txtSenderLeft=50|txtSenderWidth=140|txt SenderFS=18|txtSubjectLeft=65|txtSubjectWidth=170| txtSubjectFS=18|txtCopyLeft=65|txtCopyWidth=170|tx tBodyTop=3|txtBodyLeft=80|txtBodyWidth=160|txtSign Top=5|txtSignLeft=80|txtSignWidth=170|txtCopyFS=18 |txtFooterFS=12|txtTitleTitr=به: |txtSenderTitr=از : |txtSubjectTitr=موضوع: |txtTitr1Text= |txtTitr2Text=? |txtFooterText= : - - ? 288 - - 8714355|txtSignTitrTop=10|txtSignTitrLeft=10|txtSi gnTitrWidth=180|txtSignTitrFS=12|txtSignTitrText= : |txtNoTitr= |txtDateTitr= |txtAttachTitr= |txtCus1Text= |txtCus2Text=و همه اين اهداف به برکت "ايمان اسلامي ، اتحاد کلمه ، اميد و عزم ملي ، استفاده صحيح از ظرفيتهاي کشور و تدبير و تحرک و تلاش " محقق خواهد شد. ....((مقام معظم رهبري))|txtNoTop=12|txtTemplateName=a4 منطقه 2-سازماني|txtTopMargin=15|txtBotMargin=20|txtPageHei ght=297|txtPageWidth=210|txtHeaderLeft=5|txtHeader Top=5|txtHeaderWidth=190|txtFooterLeft=10|txtFoote rTop=280|txtFooterWidth=190|txtCopyTitr=رونوشت:|Im ageId=|Direction=220|chkDefault=False|DefaultSubje ct=|datId=D1EBC56D-AF5D-45BB-AD50-498145B4974A|TemplateName=a4 منطقه 2-سازماني',
'key','fax','848244de-08e9-40fb-a538-0d3bfb620d01','a\21','13880504')

it is a query having join . tableaccess .

may i have any sample query which is based on this very table . tbldocument.

your longest and most hectic one .

and why is the index (docid asc ) is ASC .. what is the logic behind sorting asc
why it is not desc
why it have to be asc or desc . ( force seek)

Is that a serious question?

pootle flump ur codings are working excelent.

a random one .

i am habitual of speaking while thinking .
welcome back . its great to see you again

correct me if i am wrong and i pray i am wrong. generating docid by newid() . gives random value .
so here in this index we have two sets of values . one starting from a to z . second starting from 1 to (max) numerics.

order by asc /desc achieves what ?
does this cluster index not forcing full index SCAN of all the rows on every execution ? . in my test case it is forcing this .

while every new insert or bulk insert is tending it to rebuild again ( ? ) is it. ? (rebuild is my oracle terminology... recreating itself . )
in my test case it is .

usually, I have SELECT statement, and my criteria is over docNo,docSubject,Docdate.And this table usually join on docId,docOrganizationId,docOwnerDepartmentId,docPe rsonnelId


docId`s data type is uniqueidentifier , so does ASC or DESC make any difference ?

Indexes are ordered - period. As such, they are ascending or descending. They default to ascending and it is very rare you would set them to descending.
No - there is one set of values.

Do you mean it is not possible to seek on this index? In that case - no you are wrong.

No - not rebuilds, though I am using SQL Server terminology. Random inserts (as opposed to sequential ones) may cause page splits.

Note that NEWSEQUENTIALID() does generate sequential GUIDs, unlike NEWID() and would usually be used as the default for this sort of column.

pootle flump ur codings are working excelent.

ahhh ... see this is why i like mr.flump

it is possible to seek on this index . okey .

i have created 2 million records by his/her 1 insert statement.
when ever i even query "select * from tbldocment" . the cluster index scan the whole 2 million . given any query it is doing the same .
with where clause it is a lil bit more .............


so this index is perfectly fine ? ... or the reason for this index is compromised .

Clustered index: It’s a type of index where the data itself is arranged at the indexed column i.e. we have the actual data at the leaf nodes. For example, in a science definition pocket book, every word is arranged alphabetically and the definition of the word is given along with it. ...............

Advantage:

* Seeking operations are much faster than normal indexing.

Disadvantage:

* It will rearrange the whole data at every operation of UPDATE, INSERT or DELETE on key column / cluster index column, in order to keep ordering of the data in that column.


SQL Server Indexes ? Pros and Cons (Part 2) | SQL Lion

create a table and insert few values in it . ( columns id , name ,date )
insert values in it .
id
-----
1
11a
1aa
2a
2b
2x
11h
32fg
345ad

etc

and then select from it with order by clause asc/ desc

this is what is happening in the index scan ( not seek ) . as every condition of where clause is effecting by the pre-ordered scan conditions (naturally) and then again the ORDER BY clause.



provided -- i am just a lil small dba working in a lil tiny comapny having not so much more then 5k employees and just lil few bit of data from 5 countries .

with interval_calling
as
(select stn,rdate, (SELECT CAST(CALL_ENDTIME AS TIME)
FROM bcm_temp DD
WHERE rownum = 1
and D.STN = DD.STN
AND DD.rec_id > D.rec_id
AND DD.call_endtime > D.call_endtime) -
(SELECT CAST(CALL_ENDTIME AS TIME)
FROM bcm_temp DD
WHERE D.rec_id = DD.rec_id
AND D.STN = DD.STN
and D.call_endtime = DD.call_endtime) as interv,
SUM(SECONDS) totaltimeinsec
,(((sum(seconds))/28800)*100) avgworkingratio,

((28800-(sum(seconds)))/28800)*100 avgidleratio,

(sum(seconds))/(count(seconds)) AVGCALL_DURATION,

count(seconds) as totalcalls,
sum(case when seconds <=60 then 1 else 0 end) as total_lessmin1_Call,

sum(case when seconds between 61 and 180 then 1 else 0 end) as total_min3_call,

sum(case when seconds >180 then 1 else 0 end) as total_longcalls,

to_char(trunc(sum(seconds)/60/60),'09') ||':'||
to_char(trunc(mod(sum(seconds),3600)/60),'09') ||':'||
to_char(mod(mod(sum(seconds),3600),60),'09')
as "totallcall_in_HH:MM,SS"
from bcm_temp d
group by stn,rdate,rec_id,call_endtime
)


select stn,rdate,
case when interv< numtodsinterval(0,'second') then numtodsinterval(0,'second')
when interv > numtodsinterval(0,'second') then interv
else numtodsinterval(0,'second')
end
from interval_calling

if i put order by clause in the end ( order by stn )

the execution time leaps up from 0.007 sec to 89 sec . ( its underdeveloping)

tnx alot. I enjoyed the way you solve the problem,mishaalsy. can I ask where u r from?

Milshaay - I don't understand much of what you have written, however now you've posted that link I understand now why some of what you post is wrong. Delete that link and forget it ever existed - the information is not correct.

I recommend you read the sections in BoL on SQL Server indexes.

pootle flump ur codings are working excelent.

i am a canadian . ( so much a relief to flump )