December 7, 2016 at 10:59 am
Hi SSC,
I'm working on moving a table over to a partitioned table. In a nutshell, I have something like this:
create unique clustered index UQIXC__MyTable_MyIndex with (drop_existing = on, online = on) on ps__MyTable(id)
This is all well and good, but in a perfect world, I'd like to only run that if the index is NOT currently residing on ps__MyTable.
I looked in sys.indexes, sys.partitions, sys.dm_db_partition_stats and a few other places, but couldn't find anything that jumped out as a way to identify this information. Does anyone know if it can be done?
FWIW, my somewhat hacky solution is just to look at sys.partitions and see if there are any partitions with a partition_number > 1
In pseudocode, something like this:
if not exists (select 1 from sys.some_asyet_unknown_dmv
where indexName = 'UQIXC__MyTable_MyIndex'
and residing_partition_scheme = 'PS__MyTable')
/* ... create index with drop ...*/
Hopefully that makes some sort of sense.
December 7, 2016 at 11:11 am
Can you use something like this?
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')
--Do stuff
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 7, 2016 at 11:36 am
Well that's the first part of it, yes. But what I really want to check is if it's already on the partition scheme.
The table already has a clustered index which is just sitting on primary. I want to move it to a partition scheme, utilizing create with drop_existing = on. But ideally I don't want that to be re-built if the script gets run again, and the index has already been moved to the partition scheme.
So the script you provided is the first part, but what I'm looking for is a way to tell if it's on a particular partition scheme.
December 7, 2016 at 12:50 pm
I figured out a solution
if exists (select 1
from sys.indexes
where name = '<indexName, sysname, >'
and object_id = object_id('<objectName, sysname, >')
and not exists (select 1
from sys.indexes i
inner join sys.partition_schemes ps
on i.data_space_id = ps.data_space_id
and ps.name = '<partitionScheme, sysname, >'
where name = '<indexName, sysname, >'
and object_id = object_id('<objectName, sysname, >')
/* create index with drop_existing = on */
December 7, 2016 at 6:51 pm
Index name is not a really good indicator of index existence.
You may have many identical indexes, all with different names.
To check if an index with the same structure as you intend to create already exists you need to join sysindexes to sysindexkeys and then to syscolumns.
This way you may find if an index with the specified set of columns (name in syscolumns) in the specified order (keyno on sysindexkeys) already exists.
_____________
Code for TallyGenerator
December 7, 2016 at 7:24 pm
Sergiy (12/7/2016)
Index name is not a really good indicator of index existence.You may have many identical indexes, all with different names.
To check if an index with the same structure as you intend to create already exists you need to join sysindexes to sysindexkeys and then to syscolumns.
This way you may find if an index with the specified set of columns (name in syscolumns) in the specified order (keyno on sysindexkeys) already exists.
Two follow up questions:
1) Does my reference to the specific object id not already do that?
2) If it doesn't, can you point out why and if there's a specific reason to use legacy views?
December 13, 2016 at 9:45 pm
Xedni (12/7/2016)
Sergiy (12/7/2016)
Index name is not a really good indicator of index existence.You may have many identical indexes, all with different names.
To check if an index with the same structure as you intend to create already exists you need to join sysindexes to sysindexkeys and then to syscolumns.
This way you may find if an index with the specified set of columns (name in syscolumns) in the specified order (keyno on sysindexkeys) already exists.
Two follow up questions:
1) Does my reference to the specific object id not already do that?
2) If it doesn't, can you point out why and if there's a specific reason to use legacy views?
1) No, it does not.
2) Because it refers an index by name:
select 1
from sys.indexes
where name = '<indexName, sysname, >'
It won't find an index on the same column of the same table but with different name.
As for legacy views - forces of habits.
And I hate columns named like functions (e.g. object_id)
_____________
Code for TallyGenerator
December 13, 2016 at 10:04 pm
That's what you should be doing (using your beloved "non-legacy" system views this time 🙂 ):
SELECT OBJECT_NAME(ic.object_id), i.index_id, COUNT(*), COUNT(DesiredIndex.OrdPosition)
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN (SELECT 1 OrdPosition, 'C' ColName
UNION
SELECT 2, 'D') DesiredIndex ON DesiredIndex.OrdPosition = ic.key_ordinal AND DesiredIndex.ColName = c.name
WHERE OBJECT_NAME(ic.object_id) = 'T'
GROUP BY OBJECT_NAME(ic.object_id), i.index_id
HAVING COUNT(*) = COUNT(DesiredIndex.OrdPosition)
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply