InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 05:54:53 GMT20http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxwhich usually grow large you do best by changing the ASC(ENDING) sort order to DESC(ENDING). By doing so you may greatly I am not sure why you say so, can you elaborate more?Fri, 31 Jul 2009 14:19:14 GMTjswong05http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxoh yes, you tweak them to get it right. do you have anything better? would you rather find out possible missing index manually?:hehe::cool::w00t:;-):-P:-D:-) If you look at missing index DMF, there is nothing notorious about it.Fri, 31 Jul 2009 14:16:36 GMTjswong05http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspx[quote][b]jswong05 (7/16/2009)[/b][hr]2005 has missing index DMVs and DMFs, I use them.:-P:cool:[/quote]But you test their results, right? Those things are notorious for suggesting poor choices on indexes. You need to exercise a lot of diligence when using that data.Thu, 30 Jul 2009 05:41:49 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxHi Andy,Very nice article, I share your opinion on this matter.One little note I want to add: when a primary key with unique clustered index is created from the EM or MS user interface it is always created with ASC(ENDING) sort order. When creating base tables (e.g.: customer, item, various lookups etc.) sort order usually is not an issue, but when it comes to creating transactional tables (e.g.: orders, registrations, invoices etc.) which usually grow large you do best by changing the ASC(ENDING) sort order to DESC(ENDING). By doing so you may greatly speed up queries to current data.Regards,Jaap Bregman.Thu, 30 Jul 2009 03:03:18 GMTJBregmanhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspx2005 has missing index DMVs and DMFs, I use them.:-P:cool:Thu, 16 Jul 2009 14:49:31 GMTjswong05http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxFact tables should have a clustered index on an ever-increasing identity column. That facilitates LOAD performance by eliminating page splits. It also reduces the need for free space, so you can use 100% fill factor and forget pad index. The end-result is a compact table that conserves disk space and minimizes I/O. The identity column needn't be designated as the primary key (surrogate key), but I see no reason not to make it so. FK's to dimension tables should also be indexed to optimize query performance. Since ad-hoc queries are the nature of the beast, generally most, if not all, dimension FK's should be indexed. Dimension tables should definitely have a surrogate key and an identity column is a good fit for that. In addition, the natural key should most certainly be indexed. I don't have a one-size-fits all argument about which of those indexes to cluster; it depends on a case-by-case analysis. In cases where the dimension is fairly static, clustering the the natural key index makes the most sense. The more inserts a dimension experiences, however, the more sense it makes to cluster the surrogate key.Sun, 09 Nov 2008 21:36:41 GMTDennis Q Millerhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxAndy, I would appreciate some consideration about this subject when we talk about indexing on Datawarehouse structured databases, where there are large search queries, very few updates, some deletes and a lot of inserts and truncates.In this kind of structure it's recomended the deletion and re-creation of indexes. In these cases what would be the advantages and disadvantages of using clustered indexes.Thanks in advanceMartin.Wed, 05 Nov 2008 05:46:30 GMTMartin Goebbelshttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxI totally agree with the fact primary keys and clustered index should be created on a table. Not only do they ensure data integrity (which saves you a lot of trouble in the long run) but they also make the tables easy to maintain.Mon, 16 Jun 2008 03:36:40 GMTneha.modihttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxEvery relation has a PRIMARY KEY and therefore, so must a table if we expect it to represent a relation. The purpose is to assure that each row is distinct and identifiable to the business. Considerations for a good primary key have absolutely nothing to do with performance; not having one is certainly a worst practice.Having said that, primary key is a logical constraint that can be implemented different ways in the physical deployment. Among the ways (and most commonly), we use a unique index, usually clustered and often on an identity column.The primary purpose of an index is query optimization. Clustering has additional implications with respect to fragmentation and insert optimization. The type of index can make a significant difference in performance, even to the point where an index can have an adverse performance benefit in some cases.Worst practices are: 1. to get in the habit of letting the indexing strategy dictate the primary key 2. to accept without question the indexing strategy that results by default from declaration of a primary keySaid another way, declaring a surrogate key, identity column, primary key is a reasonable solution 90% of the time. But, it's hardly a worst practice to do things a differently if the circumstances call for it. Quite the contrary--I think it is a worst practice to do it for every table (something I run into often in the SQL Server community) without question. Mon, 12 May 2008 16:37:50 GMTDennis Q Millerhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxDennis,Thank you so much for the explanation. As Professor Higgins said about Lize Doolittle, 'by george, I think she's got it'.Mattie.Mon, 12 May 2008 06:41:20 GMTMattieNHhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspx[quote][b]larry (5/9/2008)[/b][hr]There's one thing to be aware of - the database ALWAYS access the row by primary key, even if it doesn't have one. [/quote]In Sql server a table with a primary clustered index is sorts its pages by the clustering index. If I define the clustering index on a field other than my primary key then this field determines the page order not the primary key.If I have no clustering index the data is not organised in any order onto data pages and any non clustered index refers directly to data pages rather than index values in the clustering index had I created one. This is not very usefully for most on line transaction processing (OLTP) modes of access because we mostly retrieve data stored in some loose grouping associated to the order it was entered. Or rather our indexes grow at the end rather than the middle and over time and data remains grouped by the index fields it was when it was first inserted. (no need to page split old low level index pages on insert)When the majority of rows being inserted would need to be inserted in the middle of an index that is needed to optimise retrieval, (For example a list of the highest grossing movie releases) then putting a identity and clustering index on this list becomes an overhead because the order you enter the movies has nothing to do with the clustering desired for retrieving the top 10. As each movie inserted would end up on a different data page due to its order of insert (identity) anyway why maintain a clustered index ? the non clustered index used to retrieve the data is as likely to point to non-sequential data pages anyway.Sun, 11 May 2008 18:05:49 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxThe article is really good for newbies. They should remember that primary key is MUST. For them this mistake specially the clustered one is very common.:)Sun, 11 May 2008 09:32:17 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspx[quote][b]MattieNH (5/9/2008)[/b][hr]Steve,Thanks for responding, I probably should have opened a new topic. I'm not arguing about the need for a primary key, and I realize that it has to be unique. (I've read enough posts titled 'how do I remove duplicates?' to convince me of that:)). So the only time I don't have one is in temp tables, and that's usually because I can't figure out what makes an entry unique.I was wondering about the operational difference between a unique constraint, and a unique index, and why I might choose to have one rather than the other.Mattie[/quote]Mattie,I will try to explain. A unique constraint is a LOGICAL specification. It tells the database to assert a business rule: that the designated combination of columns is unique. Conceivably, there are many ways a database could implement a unique constraint. A unique index is a physical structure. Since it satisfies the requirements of a unique constraint, that's what the database uses to implement the constraint.In a practical sense, there are two differences:1. A unique index has performance implications that a unique constraint does not. As long as the unique constraint is implemented with a unique index, it's an academic issue, but we have no assurance that a future DB will implement the constraint the same way. 2. A unique constraint can be referenced by a foreign key. A unique index cannot. Sun, 11 May 2008 01:58:32 GMTDennis Q Millerhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxAndy - First - very good article.I pretty much agree on all points, with the exception of relatively small temp tables, or those generated by Inline/Multi-statement table functions - that, to me, seems like crossing the line into overkill.My only gripe with your piece, is that I do not think you really hammered home the great performance advantages (to other indexes) to be gained by ALWAYS including primary keys/clustered indexes on each and every persistent table.All non-clustered keys use the clustered index to point to the data rows: it really can make those additional indexes more efficient!Having clustered indexes built at the get-go, can also speed up rebuilds of/for other indexes; in addition to, saving DBA's on-going maintenance time, effort, and headaches.So, clustered indexes are a win-win situation: better query/app performance, better maintenance, better recovery, better service to the organization.Thanks, again.~REpicurus Sat, 10 May 2008 09:47:19 GMTrepicurushttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxI agree with having a primary key on every table, that is like DB 101 ( or even DB 001).However, we use a lot of Uniqueidentifier (MS SQL server) as primary keys, but because of the nature of the Clustered index we do NOT make the primary a clustered index. So we end with no clustered index. What are you suggestion for that.Ints are easy.. keep the index in the order of the number added... but for guids you don't want to do that... it will kill the index rebuild every time a new record is added.Your ideas?Sat, 10 May 2008 06:53:13 GMTGerhard Pretoriushttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxWhy not create a policy for this (must have clustered index and must have primary key) in SQL Server 2008, and really annoy your developer community and the less enlightened.Sat, 10 May 2008 00:39:08 GMTafryerhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxI don't disagree with you.Every primary key can be changed even if you thought It will never change at first.The case I remember is user id, social security number, book's bar code.Some user wants to change there user id for some reason - hiding from paparachi, not compatible because it's unicode character.We korean have social security number that is unique when we born. but some of people share same number.Book's bar code can be same even if the book is renewed and republished.When you work long time, you can find everything visible, displayable, meaning can be changed.I think meaninglesss auto incremental number is only unchangeable, and usable for unique primary key.Fri, 09 May 2008 19:09:33 GMTdoctorgu-606704http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxThere's one thing to be aware of - the database ALWAYS access the row by primary key, even if it doesn't have one. What do I mean? I mean that each row has an implicit identify, either being the offset within the table or the row number. So in cases where no application distinction is required, I say, why?What is the benefit?Data integrity? This would not come into question for a standalone or temporary table.But that said, it's not so bad to always use one either!Fri, 09 May 2008 18:40:46 GMTlarry-699201http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxAndy,I am curious as to what you would say to this:Our ERP vendor doesn't have any primary keys on trans_tables (journal load transaction transfer tables) thus eliminating the possibility of replication but that is another story.The trans table is simply a middle man table. It verifies when a flat file comes into the ERP that the flat file columns are the right size and data type via bcp process.Then the actual ERP program performs the load from the trans into a table with primary keys.The data remains in these trans tables no longer than 10 seconds and no data is ever stored into these tables.Would you say the vendor is lazy in db design on these tables or a true example why primary key and a clustered index is not needed?JTSFri, 09 May 2008 14:06:16 GMTjsheldonhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxInteresting that people don't like identity columns. It takes me back to a case some years ago where I added a seemingly useless identity column to a super-enormous table and made it the clustered index/PK. I took so much krp from people who said that I made the super-enormous table that much larger. They all were insisting that I use the existing "natural" key in the db, a compound of a varchar and a date. To shut up the screamers, I had to make a copy of the entire table for them, doing it their way and demonstrating the CLEAR VALUE OF A THIN (in this case, INT) PK. Most people (including dba's) don't get how db indexes work. I've seen no mention in this thread about the value of a thin PK.--Wayne Clifford - SeattleFri, 09 May 2008 10:48:08 GMTwayne.cliffordhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspx[quote][b]jthorpe (5/9/2008)[/b][hr]Does anybody know what the difference in performance is when doing DELETE or INSERT on a table when having / not having a primary key and clustered index? Especially as far as large tables are concerned.Thanks,JT[/quote]In a heap (table with no clustered index) - space isn't reused. So when you delete rows, and then insert, the delete frees up space inline, but the inserts happen to the end of the table. This will lead to increasing fragmented (and larger) tables. What makes it even worse is that short of copying the data into a new table, then dropping the original table, there's no way to defragment a heap.In the presence of a clustered index - the data is stored inline of the clustered key (i.e. the clustered key determines the physical order for storing data). while this might cause what is called "page splits" if you need to insert data in a spot with insufficient free space to accomodate it, rebuilding the clustered index would allow the data to get reorganized and would free the space back up for reuse without having to drop and recreate the entire table.Fri, 09 May 2008 10:19:18 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxDoes anybody know what the difference in performance is when doing DELETE or INSERT on a table when having / not having a primary key and clustered index? Especially as far as large tables are concerned.Thanks,JTFri, 09 May 2008 10:06:45 GMTjthorpehttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxI've just come across a situation where an update/insert on a linked server's table failed, as it didn't have a PK - so there's another reason to make sure you always have one...Fri, 09 May 2008 09:40:42 GMTbettername?http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxSomething missed as a valid use of tables without a primary key / clustered index (at least IMHO) are highly volatile staging tables. In our permanent relational stores we use surrogate Primary Keys (Not auto increments) but these are always created as a non clustered index, our natural keys are always indexed as Clustered unique. However, in the transactional portion of the application we shred an XML document into a permanent staging table using the SPID and a TranID GUID of the transaction as its primary identifier. The usefulness of this data is quite short lived (Existing usually for less than a second while the transaction is being processed). On this table the additional overhead of index maintenance alone doubled the time the transaction took in the production environment. It was faster to vet the data with validation code modeling the business rules as opposed to building said rules into the schema as is our normal practice. Granted, the developer does assume the responsibility in this case of maintaining this validation code, but that is what we are paid for.I guess my point is that there are no categorical best / worst practices, everything has an exception and being able to spot those exceptions and make the right decision is where a developer is truly worth their salt, anyone can follow a set of rules blindly, but that will always lead to problems in large enough systems...Regads,BillP.S. I didn't read through all 17 pages of replies so my points may have already been raised. If so I would be interested to pointers to those posts to see other peoples takes on it. Thanks...Fri, 09 May 2008 09:40:35 GMTZaphod42http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxDoes anybody know what the difference in performance is when doing DELETE or INSERT on a table when having / not having a primary key and clustered index? Especially as far as large tables are concerned.Thanks,JTFri, 09 May 2008 09:37:47 GMTjthorpehttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxSteve,Thanks for responding, I probably should have opened a new topic. I'm not arguing about the need for a primary key, and I realize that it has to be unique. (I've read enough posts titled 'how do I remove duplicates?' to convince me of that:)). So the only time I don't have one is in temp tables, and that's usually because I can't figure out what makes an entry unique.I was wondering about the operational difference between a unique constraint, and a unique index, and why I might choose to have one rather than the other.MattieFri, 09 May 2008 09:20:20 GMTMattieNHhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxMattie,You create a unique index when you set a PK. Setting the PK is the equivalent, but it does help you pass along information to others and even yourself about what is the column that defines uniqueness for that entity.SQL Server is more efficient with clustered indexes, and it's worth setting one. It doesn't hurt you and while you might get along without it, how are you sure it wouldn't run better with one?PKs are logical entities anyway. They build a unique index (clustered or non-clustered), which protects the data, but having it there allows that information about uniqueness to transfer to new people. It's a good idea. Required? No, but I think it's a bad practice to avoid it. There's no reason not to do it.Fri, 09 May 2008 09:06:06 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxFrom my experience your database design is dependant on your arogance. One database i was involved in designing uses some PK's however primarily it is controlled by a few logical pk's. this works for what we are doing with it. (it is also internal to my group and only accessed by a few people.)As for the other main db i support which ranges from 200gb to 2tb depenant on location. This database has no pk's, no clustered indexes and stores hundreds of thousands of new entries each day. It performs quite well returning results in less than 5ms in most cases. There are plenty of nonclustered indexes depending on the tables and hte way each db is utilized by the customer. Because of the far reaching diversity of this app i think it is beneficial to not utilize pk's in a physical sense.It truly depends on your purpose and how tight the controls on your app as well. If you are designing a db to be unleashed to developers and there are not good controls in place then yes... protect the db as hard as you can. This will help garbage in garbage out from being an issue. so basically my rambling states my opinion... Unless you can 100% say there is never an exception then you cannot use the word always. There are ALWAYS times that the rules do not completely apply.Fri, 09 May 2008 08:36:00 GMTRobert Hermsenhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxCheck out SqlTac (www.SqlTac.com), it detects and fixes many of the most common database design issues.Fri, 09 May 2008 06:47:04 GMTsmccabe-596458http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxAndy,I know you wrote this article light years ago, and my question is a little off your topic, but I'd like to ask anyway. I understand the concept of a primary key. But when I manage relationships to a table using EM, I also have the option of creating a UNIQUE CONSTRAINT, or a UNIQUE INDEX on a column. I usually select index, because that seems more intuitive to what I'm trying to accomplish (more direct access to the data in the column), but I've never been able to figure out why I would choose a unique constraint over a unique index. Can you explain why I might want to?Thanks,MattieFri, 09 May 2008 06:30:58 GMTMattieNHhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxGreat article Yoda: "you'll never wrong go by creating a primary key on a table, temp or not"Seriously though, I find the resistance to your thoughts that, with a VERY few exceptions, every table should have a clustered index, even if it's only on an identity column. It shows a clear lack of knowledge in our community for how data is stored and retrieved in SQL Server.Fri, 09 May 2008 05:21:22 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxRegarding clustered indices - I think 99.99999999999% of the time you're better off having one...It can help when you defragment that table.I think one example where clustered indices might not be worthwhile would be audit tables, that will most likely never be read but are a business requirement, but that's an area thats open for debate.:)MarkFri, 09 May 2008 05:17:34 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxHi, I am somehow amazed by the discussion. For me, as a database designer, using PK is a must for each and every table, no questions asked.You can't design a fully controlled database without having PK for all tables and without using [b]foreign keys [/b]that define your database integrity. Foreign keys were not mentioned in the article but I am sure that Andy will speak about foreign keys in one of his next articles. As we all know, to use a FK you must have PK predefined.I use Visio to show and print my database structure. With no primary keys and no foreign keys one cannot express the real database design.DavidFri, 09 May 2008 04:58:25 GMTd.rosenhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxINMHO the confusion comes from the clustered index term in SQL Server.In other DBMS's for example DB2 there is no concept of a primary clustered index because the primary key definition is synonymous with the primary clustering of the data pages (at least after each Re-Org). As to why you would choose to define a primary key to be anything but the natural data vector with highest select frequency and highest row selectivity is beyond me.However if there is no Majority search condition which is far above in frequency of execution and selectivity of data than the remainder of selects; for example Where many equally orthogonal search vectors are equally weighted, then there may be no benefit to add a identity as clustering index to the data. The data rows are likely to be an different pages (for any set of search or ordering vectors) and any clustering would add a small overhead which would show no efficiency improvement due to indexing on select. Many non-clustered (the term is misleading because the index itself is still internally clustered) indexes on each of these vectors could help if the data was not specifically volatile.The only argument for an identity as primary clustering index is where the rows insert rarely and frequently update to a situation causing the update row to force onto a different page. as only clustering index would need to change any not any of the non-clustered that reference it and not the data row. I don't have a feel for whether this is a good match for the OLTP access pattern.You almost need the uniqueness of a QUID across instances combined with the sequentiality of the identity combined with the ordering of the most selected natural key in order to optimise indexing for retrieval.Fri, 09 May 2008 02:10:51 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxHi all,So often we confuse logical requirements with those of physical design. I firmly believe that in logical design, every table must have a primary key. But that does not mean we are required to declare one in the physical structure.While the logical design must have a PK, there are any number of ways to assert it physically. Certainly the unique index resulting from a primary key declaration is the one we most commonly use, but the same thing can be accomplished with a trigger, DML discipline, and perhaps other ways. Case in point, consider a table limited by design to one row. Need that table have a unique index on the PK? Or consider a temp table that is a subset of another table with a PK--need we have a unique index on that? Or consider in-memory arrays: how many times do we see physical PK's implemented on tables of that sort? Seldom, I say. Instead, we rely on the devices of good programming practice to make sure the rows are uniquely addressable. Now think about the opposite situation: a database that has an intrisic rowid that is capable of uniquely identifying every row quite aside from any other columns. That's roughly the equivilent to the knee-jerk approach of adding an identity column to every table. (Actually, SQL Server does not assert uniqueness on identity columns, but I digress). The point is, we still need a logical key that conveys business meaning and business uniqueness on such a table. And we need that even the physical design already provides a unique key of sorts.So, is not having a primary key a worst practice? In logical design, absolutely. In physical design, more often than not, a primary key is a good idea--but the worst practice would be to always create one regardless of the impacts or without an understanding of why it is needed.I don't regard a clustered index on every table to be a best practice, much less absent mindedly making it the primary key. I've seen some compelling arguments for clustered indexes on identity columns to minimize fragmentation, reduce page splits, and otherwise benefit performance. I contend those are strictly physical considerations and have marginal bearing on what the logical primary key should be and whether that column should comprise it.Fri, 09 May 2008 01:52:46 GMTDennis Q Millerhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxHi all,So often we confuse logical requirements with those of physical design. I firmly believe that in logical design, every table must have a primary key. But that does not mean we are required to declare one in the physical structure.While the logical design must have a PK, there are any number of ways to assert it physically. Certainly the unique index resulting from a primary key declaration is the one we most commonly use, but the same thing can be accomplished with a trigger, DML discipline, and perhaps other ways. Case in point, consider a table limited by design to one row. Need that table have a unique index on the PK? Or consider a temp table that is a subset of another table with a PK--need we have a unique index on that? Or consider in-memory arrays: how many times do we see physical PK's implemented on tables of that sort? Seldom, I say. Instead, we rely on the devices of good programming practice to make sure the rows are uniquely addressable. Now think about the opposite situation: a database that has an intrisic rowid that is capable of uniquely identifying every row quite aside from any other columns. That's roughly the equivilent to the knee-jerk approach of adding an identity column to every table. (Actually, SQL Server does not assert uniqueness on identity columns, but I digress). The point is, we still need a logical key that conveys business meaning and business uniqueness on such a table. And we need that even the physical design already provides a unique key of sorts.So, do we need a primary key on every table? Yes, the logical design must have a primary key. No, the physical design needn't necessarily have one (in the sense which was meant here: a PK constraint from which a unique index is imbued).I don't regard a clustered index on every table to be a best practice, much less absent mindedly making it the primary key. I've seen some compelling arguments for clustered indexes on identity columns to minimize fragmentation, reduce page splits, and otherwise benefit performance. I contend those are strictly physical considerations and have marginal bearing on what the logical primary key should be and whether that column should comprise it. Fri, 09 May 2008 01:43:10 GMTDennis Q Millerhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxReally enjoyed the article. I thought it gave some good ideas, particularly for application developers, who often understand the basics of database design, but do not usually have enough experience of larger systems.I think you should do an article on Identity columns. Always an interesting debate, but I'd be interested to read your thoughts.Fri, 09 May 2008 01:33:56 GMTKarl Proctorhttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxPersonally I don’t like the use of surrogate keys as an advice to junior database developers.Once you tell them something like that many of them tend to use this without first thinking about searching for a natural key.For the rest I greatly appreciated the article.Greets,PeterFri, 09 May 2008 01:21:22 GMTPeter Bourlethttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxI wish there were more responses from data warehouse applications. When I first started in DW environemt it was a mantra (supported/promoted by our architect and MS consultant) no primary or foreign keys and be caustious using clustered indexes. Goal was to getting data in, scrubbing and isolating bad data. You certainly don't hear MS promoting "no PK/FK" position and in fact most tools work best (data source views in AS and RS report models) when primary keys and foreign keys are defined. In last year I have come around and now re-thinking our old practices.I have no problem using identity data types for PK and then having indexes for natural key - also feel comfortable preserving these identity data type primary key values and keeping identity in synch with natural key over time (that is why we have SET IDENTIY INSERT and DBCC CHECKIDENT).Some BP's I promote for [u][i]datamarts[/i][/u]:If full table refresh, truncate, drop all constraints, load table, add constraints and always use fillfactor =100.If large data volume and incremental loads, use partitioned tables, if possible design indexes to be created on partition and use fillfactor=100 (this way you take advantage of partitions and drop constraints before inserts).Avoid UPDATE.Don't forget about INCLUDE clause on indexes.All tables have an identity data columns.Persist calcualted columns.Thu, 08 May 2008 23:07:02 GMTIdea Deadbeathttp://www.sqlservercentral.com/Forums/Topic1364-29-1.aspxI strongly agree with Andy to always create primary keys. As identity seed columns, this helps prevent page splits. Too many splits can and will impede performance.Another reason is without a primary key the query optimizer will always do a table scan as it looks at the table as a heap.Thanks for this great article Andy!Tue, 27 Nov 2007 11:47:32 GMTsweiry23