Naming an index - 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 2000 Forums

SQL Server Development (2000)

Naming an index

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-04 : 08:48:48

When creating a clustered index on a table

CONSTRAINT [IDX_PageHitsCluster] UNIQUE CLUSTERED

Is there a way to avoid having to pick a unique name for the index? e.g. Get a randomized name?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-04 : 09:09:52

will this do?


create table Mytable (col1 int, col2 varchar(15))
go
declare @indexName varchar(10)
set @indexName = 'IDX_' + convert(varchar(10), convert(int, rand()*100000/3)) 
exec('CREATE INDEX ' + @indexName + 
' ON Mytable (col1)')
exec('DROP INDEX Mytable.' + @indexName)
drop table Mytable

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-04 : 09:35:49

You mean the way it generates a name for consrtaints?

I always thought of that as a negative.

Why would you want to do this?



USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 char(1))
GO

CREATE UNIQUE CLUSTERED INDEX myIndex99  ON myTable99(Col1)
GO

sp_Help myTable99
GO

DROP INDEX myTable99.myIndex99
DROP TABLE myTable99
GO

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2005-05-04 : 15:34:19

Its a pain when you want to drop a constraint - i.e. you have thousands of client's who have installed your database script, and now you want to change something, and they all have different names for the constraint!

We just use the Table name + Column names in a reasonably uniform manner, and usually (99.999% of the time) wind up with unique, repeatable, names)

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 15:38:43

Sam,

Do yourself a favor and create a naming standard for your objects. For clustered indexes, we use cidx_ColumnNameList. For non-clustered, it's idx_ColumnNameList. For unique constraints, uniq_ColumnNameList. For PKs, we use PK_TableName. ...

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-04 : 16:01:07

quote:
Originally posted by tduggan

Sam,

Do yourself a favor and create a naming standard for your objects. For clustered indexes, we use cidx_ColumnNameList. For non-clustered, it's idx_ColumnNameList. For unique constraints, uniq_ColumnNameList. For PKs, we use PK_TableName. ...

Tara

Say Amen....

Can I get an Amen?

AMEN!

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-05 : 07:58:12

I knew I'd get the big "Why", but I didn't count on getting religion...

This is a T E S T script... (I should have mentioned that. Now this forum believes I have no index naming convention). Run repeatedly, this test script copies a table to a temporary name, creates a new set of indexes, renames itself back to the original. Hey, it's a way of playing around with clustered indexes to optimize queries.

Index name conflicts in this test script have to be edited every time. I was surprised there was no way to randomly name an index, like the index names given when a column is declared UNIQUE. Seemed like I might be overlooking something.

To clear the air, I name my indexes as follows: If it's the first Tuesday of the month, start with a digit and finish with my main course from breakfast. Other days, I consult the sports column, pick a player's last name from a steroid scandel, and add the outside temperature to make it unique.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-05 : 08:04:14

so my post does nothing to you??

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-05 : 08:30:34

quote:
Originally posted by spirit1

so my post does nothing to you??

It'll work. I was incorrectly expecting that there would be a way to eliminate the name from the CONSTRAINT statement and SQL would provide a machine generated name.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-05 : 09:49:19

quote:
Originally posted by X002548

You mean the way it generates a name for consrtaints?

What'd I say?

No.

Brett

8-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-05 : 15:49:21

You can use a script to generate a unique name for the constraint:


EXEC ('
ALTER TABLE [dbo].[T_MY_TABLE]
	ADD CONSTRAINT [AK_T_MY_TABLE_'+REPLACE(NEWID(),'-','_')+']
	UNIQUE  CLUSTERED 
	( [YEAR_DAY_OF_YEAR] )
')

quote:
Originally posted by SamC

When creating a clustered index on a table

CONSTRAINT [IDX_PageHitsCluster] UNIQUE CLUSTERED

Is there a way to avoid having to pick a unique name for the index? e.g. Get a randomized name?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources