Monday, August 10, 2009

Use Index and Cursor in SQL Server 2000

Using Index
================
select *from ZIPCodes where StateName = 'New York'

-- Create Index
create nonclustered index idxStateName on ZIPCodes(StateName)
create nonclustered index idxZIPType on ZIPCodes(ZIPType)

-- Use Index
select *from ZIPCodes with(INDEX(idxZIPType)) where ZIPType = 'S'

-- List of Indexes on Perticular Table
exec sp_helpindex 'ps_client_master'

-- Drop Index
drop index ps_client_master.ps_client_master_Index_1

Using Cursors
===============

DECLARE @ClientID char(11)

declare cl CURSOR FOR
select int_id from ps_client_master

open cl

FETCH NEXT FROM cl into @ClientID
while @@FETCH_STATUS = 0
begin
print @ClientID
fetch next from cl into @ClientID
end

close cl
DEALLOCATE cl

No comments: