PRACTICAL APPROACH to know On which columns of a t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

PRACTICAL APPROACH to know On which columns of a t

Author

Topic

bsethi24pt
Starting Member

1 Post

Posted - 2011-01-17 : 09:16:30

Dear All,

I know that by default Microsoft SQL creates Clustered Indexes on Primary Key fields of a table & if some performance issue is there to extract data from Table then we have to check on which field we can create Non-Clustered index & implement the same to improve the performance.

Above-mentioned paragraph is completely based on knowledge gained theoretically.

Now, my doubt is related with Practical working.

In Practical, how can we know that XYZ Table has performance issue & on this-this columns of XYZ table, we have to create indexes & in index definition which columns we have to define in INCLUDE COLUMN definition.

Can anyone suggest PRACTICAL Approach to know on which Columns of XYZ Table & when we have to apply Indexes?

For Example: -

I have a cursor to check file exists on server or not. If exists then updates IsExists field by TRUE else by FALSE.

/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/
Table used in this Cursor is XYZ TABLE & Structure of this Table: -

Column_name~~Type~~Length~~Nullable
SchoolCollegeCode~~varchar~~5~~no
SchoolCollegeName~~varchar~~100~~no
StntPkey~~int~~4~~no
Student_No~~varchar~~16~~no
SchoolCollege_Pkey~~int~~4~~no
ImageAddress~~varchar~~500~~yes
IsExists~~varchar~~10~~yes

I have no Index on this table.
/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

After run the below cursor for DUMMY Values, there is no row exists in any of the below mentioned DMVs used to Identify Missing Indexes: -

/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/
sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_columns
/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

The code of cursor: -

/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/
begin tran

declare @FileNamewithCompletePath varchar(500)
declare @StudentPkey int
declare @SchClgePkey int
declare @Check int
set @FileNamewithCompletePath = ''
set @StudentPkey = 0
set @SchClgePkey = 0
set @Check = 0

declare PhotoExistsPhysicallyorNot cursor for

select ImageAddress, StntPkey, SchoolCollege_Pkey from XYZ /*where SchoolCollege_Pkey = 1 and StntPkey<=500 */
order by SchoolCollege_Code, Student_No

open PhotoExistsPhysicallyorNot

fetch next from PhotoExistsPhysicallyorNot into @FileNamewithCompletePath, @StudentPkey, @SchClgePkey

WHILE @@FETCH_STATUS = 0

BEGIN

exec master.dbo.xp_fileexist @FileNamewithCompletePath, @Check output

If (isnull (@Check, 0)=1)
begin
Update XYZ set IsExists = 'True' where SchoolCollege_Pkey = @SchClgePkey and StntPkey = @StudentPkey
set @Check = 0
end
else
begin
Update XYZ set IsExists = 'False' where SchoolCollege_Pkey = @SchClgePkey and StntPkey = @StudentPkey
set @Check = 0
end

--
if(@@Error<>0)
begin
print 'Error on ' + convert(varchar,@FileNamewithCompletePath)
end

fetch next from PhotoExistsPhysicallyorNot into @FileNamewithCompletePath, @StudentPkey, @SchClgePkey
end
--select @PPkey

CLOSE PhotoExistsPhysicallyorNot
DEALLOCATE PhotoExistsPhysicallyorNot

rollback
commit
/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-17 : 09:33:24

Also asked here: http://www.sqlservercentral.com/Forums/Topic1048741-360-1.aspx

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources