Hello, I need some information about table creation.
1] Is it a good practice to create index on primary key.? (Does it effect on performance?)
2] I generally go to design view->Go to Index/key and add columns on which I have to index. Is there any other
good way to index a column.? Is there different types of indexes.?
3] Sometimes in my tables suppose AgentID could be or could not be. So should I set relationships between master table and child table (I set Enforce... option). Is it good or not? Will it affect on performance?
1 Yes & yes
2 You can create an index using an SQL Script, but if you use SSMS it just generates the script, and runs it, so it comes to the same thing (there is an potion / toolbar button to see the Script that SSMS will use, if you are interested to look - or to manually-adjust it)
"Different types"? Depends what your question is. Clustered, Unique, Filtered, Include ... not to mention XML and other flavours.
3 Yes & Yes (although others may have different views)
1 Like
Thanking You Kristen!
When should I use Clustered,Unique, Filtered...Index?
Meantime I will google it also.
It "depends" ...
-
When you create a primary key constraint, that is all you need to do. SQL will automatically create an index to enforce that constraint. So, in that sense, no, you don't "create index on primary key". You create either (1) a primary key constraint or (2) a unique index where all the columns are not null.
-
Yes. The only really good method is to use CREATE INDEX commands. You can script them from SSMS until you get familiar enough with them to write them yourself. It's much more difficult to manage indexes thru the GUI as you get into multiple databases, want to test out different index combinations or options, and so on. The GUI will take far too long and be far to inconsistent to be usable.
-
You have to use SQL system views that provide missing index and index usage info to determine that. There is no way to just look at table definitions and decide what the index(es) should be.