Index on Table - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Index on Table

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-08 : 10:05:36

Vendor software does not supply primary keys on tables

There is a table

EMPLOYEE
Empl_ID This has create unique, and index selected.

As this is unique is this ok to set create as clustered so i get primary key defined.

How to change automatically on all tables that have this set.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-08 : 12:55:01

You should not modify vendor databases as that would violate your contract with them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 13:45:05

"As this is unique is this ok to set create as clustered so i get primary key defined."

PRIMARY KEY and CLUSTERED are not the same thing ...

... any (one) index can be clustered. It is common for the Primary Key to be the one which is clustered.

A PK has to be unique AND have no NULL values (a Unique index may have ONE NULL value)

Apart from the point Tara raises about modifying a vendor-database, it will probably be safe to modify a Unique Index to Primary key (provided no NULL value

) and I would definitely want a Clustered Index on the table. The choice of that needs a bit more thought, but in the absence of careful thought setting the Primary Key to be the clustered index is a reasonable starting point.

Backup first!

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-08 : 15:11:09

OK, they don't supply PK's, what about unique indexes?

Are all the id identity columns?

How much volume do you have

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-08 : 15:30:38

You never know what kind of impact will have to the app, should raise the issue to vendor and let they handle it.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-09 : 11:33:16

Understands all the violations....

EMPLOYEE
EMPL_ID (VEndor index has PI_EMP on EMPL_ID which is Unique non clustered)

If i add Primary Key
Keys
PK_EMPL
Indexes
PK_KEY (Clustered)
PI_KEY (Unique Non Clustered).

I should be safe

Just check the column does not allow nulls.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-09 : 12:15:44

Found this script that shows the column details for the indexes

-- Modified sp_helpindex SP to show all indexes for all tables
-- this was modified to handle object owned by dbo and other users

CREATE proc sp_helpindex3
--@objname nvarchar(776) -- the table to check for indexes
as
-- PRELIM
set nocount on

declare @objname nvarchar(776),
@objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid smallint, -- the filegroup id of an index
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@dbname sysname,
@usrname sysname

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

-- create temp table
create table #spindtab
(

usr_name sysname,
table_name sysname,
index_name sysname collate database_default,
stats int,
groupname sysname collate database_default,
index_keys nvarchar(2126) collate database_default -- see @keys above for length descr
)

-- OPEN CURSOR OVER TABLES (skip stats: bug shiloh_51196)
declare ms_crs_tab cursor local static for
select sysobjects.id, sysobjects.name, sysusers.name from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid where type = 'U'

open ms_crs_tab
fetch ms_crs_tab into @objid, @objname, @usrname

while @@fetch_status >= 0
begin

-- Check to see the the table exists and initialize @objid.
/*
select @objid = object_id(@objname)
if @objid is NULL
begin
select @dbname=db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
*/
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
declare ms_crs_ind cursor local static for
select indid, groupid, name, status from sysindexes
where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
open ms_crs_ind
fetch ms_crs_ind into @indid, @groupid, @indname, @status

-- IF NO INDEX, QUIT
--if @@fetch_status < 0
--begin
--deallocate ms_crs_ind
--raiserror(15472,-1,-1) --'Object does not have any indexes.'
--return (0)
--end

-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status >= 0
begin
-- First we'll figure out what the keys are.
declare @i int, @thiskey nvarchar(131) -- 128+3

select @keys = index_col(@usrname + '.' + @objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'

select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'

while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end

select @groupname = groupname from sysfilegroups where groupid = @groupid

-- INSERT ROW FOR INDEX
insert into #spindtab values (@usrname, @objname, @indname, @status, @groupname, @keys)

-- Next index
fetch ms_crs_ind into @indid, @groupid, @indname, @status
end
deallocate ms_crs_ind

fetch ms_crs_tab into @objid, @objname, @usrname
end
deallocate ms_crs_tab

-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @empty varchar(1) select @empty = ''
declare @des1 varchar(35), -- 35 matches spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216

-- DISPLAY THE RESULTS
select
'usr_name'=usr_name,
'table_name'=table_name,
'index_name' = index_name,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname),
'index_keys' = index_keys
from #spindtab
order by table_name, index_name

return (0) -- sp_helpindex

GO

Is there a way to get the (column with not null) aswell somewhere then i can really check
Thanks

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-09 : 14:02:38

Got the exact script thanks
select object_name(i.object_id) as TableName, i.name as IndexName, c.name as ColumnName,
c.is_nullable,
i.Type_Desc, ic.Is_Included_Column, key_ordinal
from sys.indexes i
join sys.Index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
--where c.is_nullable = '1'
order by object_name(i.object_id) asc, i.type_desc, i.name, Is_Included_Column asc, ic.key_ordinal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources