Stored Porcs in TempDB

Last Post 11 May 2006 10:58 AM by eramgarden_SQL. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
eramgarden_SQL
New Member
New Member

--
11 May 2006 06:25 AM
Ok, this is my 3rd day on this new job and this is what my manager just said to me:

" the companty we do business with has a lot of stored procedures etc in tempdb AND DO A LOT OF THINGS THAT ARE NOT CONSIDERED GOOD PRACTICE. We are kind of stuck in that, I read up and from my reading CLUSTER for primary key is always a good idea and there are no downsides. As soon as I did that, the app quite working".

What should my response be?? Who puts stored procs in TempDB? What are the side effects down the road? If ONE client is doing that..should I suggest that we leave that client alone but for any new clients, we need to change the schema??

any advice would be great.
eramgarden_SQL
New Member
New Member

--
11 May 2006 10:26 AM

You know, I looked at some of the tables and there are no PK on them...

my question before I go back to him: can we have clustered primary keys when
there's no PK column? (I need to dig deeper..maybe he created them on tables that have PKs)
eramgarden_SQL
New Member
New Member

--
11 May 2006 10:58 AM
I need to go back and refresh my memory about the PK stuff but for now..

Yes, each table can have one PKey.
1. "You dont need Pkey or create a column for pkey if table doesnt have pkey column": But isnt it better for performance to HAVE a Pkey? To reindex it and stuff for performance?

2. Yes, I mean can we have a clustered PK when there's no Pkey: so there has to be a PKey to be able to create a clustered one. I dont know why he says the app broke when he added clustered PKey. What side effect does it have to have clustered PKey??
nosepicker
New Member
New Member

--
11 May 2006 02:11 PM
Eram, you need to get your terminology and concepts straight.

There's no such thing as a "clustered primary key", only a clustered index. By default, when you define and create a primary key on a table, SQL Server will automatically create a clustered index on that particular column (although you can turn off that automation if you want).

You can create a clustered index on any column. It doesn't have to be just on the primary key column (but like it was stated earlier, you can only have one clustered index per table).

There are many times when putting a clustered index on the primary key column is not the best situation, which is why you shouldn't always trust SQL Server to put a clustered index on the primary key column. It really depends on how you are going to use the table. For example, if you table has a date column, and a lot of your queries against that table use dates or date ranges, putting a clustered index on that date column might be a good idea, even if that date column cannot be considered a primary key. Therefore, in regards to your app, you'll have to look at the big picture (the types of queries hitting the table, does the table receive a lot of inserts, updates, or deletes, etc.). Choosing your primary keys and your indexes needs to be done with care. It'll do you well to read up on indexes to understand how they are structured and how they work.

Like rm said, a primary key is ony for referential integrity, not performance. Defining primary keys and foreign keys helps to eliminate things like orphaned or childless records.
eramgarden_SQL
New Member
New Member

--
11 May 2006 02:26 PM

Thanks. I need to readup and refresh my memory. Will come back if my light bulb is not brighter.

thanks again.


Acceptable Use Policy
---