InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 05:51:05 GMT20http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]sanjays-735734 (6/7/2010)[/b][hr]Hi David I would like to add something in Your "simple example"...IF EXISTS(SELECT 1 FROM ... WHERE...)Instead of this Use following...IF EXISTS(SELECT TOP(1) 1 FROM ... WHERE...)[/quote]Sanjays, I've found that the IF EXISTS in nearly all cases generates the same execution plan.You can get away with SELECT * however I've always hammered in the disciplines of not using SELECT * so I don't confuse developers with the cases where it doesn't matter.Mon, 07 Jun 2010 11:40:20 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]sanjays-735734 (6/7/2010)[/b][hr]Hi David I would like to add something in Your "simple example"...IF EXISTS(SELECT 1 FROM ... WHERE...)Instead of this Use following...IF EXISTS(SELECT TOP(1) 1 FROM ... WHERE...)[/quote]Why?Mon, 07 Jun 2010 09:15:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxHi David I would like to add something in Your "simple example"...IF EXISTS(SELECT 1 FROM ... WHERE...)Instead of this Use following...IF EXISTS(SELECT TOP(1) 1 FROM ... WHERE...)Mon, 07 Jun 2010 08:56:12 GMTsanjays-735734http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxTrue, but COUNT(NULL) and COUNT(1/NULL) are not mentioned in the T-SQL COUNT() reference. Since the results of SELECT (1/NULL) is NULL, this would appear to be a way to pass the NULL value to COUNT(), but BOL doesn't mention it, nor is there any explanation of why the result of COUNT(1/NULL) would be zero. If I were to take a stab, I would guess that supplying NULL to the COUNT() function satisfies the parameter condition but does not count any rows. There may be some mathematical explanation of the behavior, but I wouldn't know what it is.Tue, 02 Mar 2010 12:12:33 GMTrwoods-871939http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxfrom bol 2008COUNT(ALL expression) evaluates expression for each row in a group and returns the number of [b]nonnull [u][/u][/b]values.COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, [b]nonnull [u][/u][/b]values.This will also apply to count(whatever you put in here)Tue, 02 Mar 2010 11:52:30 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxSELECT (1/NULL) will return NULL. SELECT COUNT(1/NULL) will return 0. SELECT NULL will obviously return NULL, SELECT COUNT(NULL) will return an error. If this is documented anywhere in BOL, it is not in the T-SQL reference section.Tue, 02 Mar 2010 10:38:55 GMTrwoods-871939http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxwell 1/ null will surele give null if not an error.also count(null) has a different behavior than you might expect... please read bol for the full details.Tue, 02 Mar 2010 10:22:54 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]dan.forest (3/2/2010)[/b][hr]I found that select count(*) and select count(1/NULL) both showed the same execution plan and took the same amount of time.Dan[/quote]Apparently they do, but they don't produce the same result. Select count(1/NULL) appears to always return zero, regardless of the actual row count.Tue, 02 Mar 2010 10:16:00 GMTrwoods-871939http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxI found that select count(*) and select count(1/NULL) both showed the same execution plan and took the same amount of time.DanTue, 02 Mar 2010 09:11:36 GMTdan.foresthttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxAs with all things DBA related it depends.If you have a very large product set then adding in an extra column for the Top 'n' products wastes storage though in SQL2008 you have the SPARSE columns facility for NULLable values. I'm old fashioned, I regard NULL values as an occasionally necessary evil. If the Top 'n' products rarely change, or at least are not in a state of constant flux then it may be worth sticking an indexed view over the top of the two tables so you are getting the best of both worlds.The other option is to denormalise the Top 'n' products table and copy the main fields you require into that table from the main products table. Obviously you have to plan for updates in two places if you do this.Mon, 15 Feb 2010 11:32:58 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxAn excellent article. Thanx 4 the bomb list. ;-)Just want someone to clear my concept regarding section: A Top 'n' products solution.If v use this 1 to 1 relationship (e.g. dbo.Product and dbo.TopProducts in this case) then in most cases v need product information e.g. name, price of top products. For this purpose v have to make a join of dbo.Product and dbo.TopProducts on basis of ProductID.Is this join less painful then using TopProductPosition field in original product table itself ??Mon, 15 Feb 2010 08:06:59 GMTM. Furqanhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]Scott Abrants (2/12/2010)[/b][hr]Very nicely done! All too often I hear development folks and management say that the DB guys are the bottlenecks and shared resources are a problem yet when they try and go it alone you often see "Time Bomb Code". There is a reason why if your on a plane and the pilot is late they do not come over the loudspeaker and ask if anyone sitting in coach can fly the plane. This is because the pilot has had countless hours of training and has seen many different situations that even training alone cannot prepare someone for. This is very true in database land. Databases design and coding is as much of an art form as it is a science.Well done...[/quote]Scott, you described my situation perfectly. I have a couple of years worth of work to get rid of these time bombs, RBAR, and cursors cleaned up. But thanks to all you folks at SSC, things will be easier.Fri, 12 Feb 2010 09:20:32 GMTJon Russellhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxVery nicely done! All too often I hear development folks and management say that the DB guys are the bottlenecks and shared resources are a problem yet when they try and go it alone you often see "Time Bomb Code". There is a reason why if your on a plane and the pilot is late they do not come over the loudspeaker and ask if anyone sitting in coach can fly the plane. This is because the pilot has had countless hours of training and has seen many different situations that even training alone cannot prepare someone for. This is very true in database land. Databases design and coding is as much of an art form as it is a science.Well done...Fri, 12 Feb 2010 06:12:58 GMTScott Abrantshttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]David.Poole (2/11/2010)[/b][hr]Jeff, there is a short and illuminating article on www.infoq.com http://www.infoq.com/vendorcontent/show.action?vcr=847 regarding technical debt in agile projects that echoes what you say.Basically a project team is given a fixed immutable deadline to deliver a project and the only way to deliver on time is to compromise on quality.Another team comes along to do the next project and because of the technical debt accrued in itteration one they work slower. Inevitably the comment is made that team two is less productive than team one and pressure is applied. Again the only way of getting the project "on track" is to take short-cuts and bodge the quality.....etcWhen done properly an agile project is a joy to work on. It is so easy to talk the talk with with agile but not walk the walk![/quote]Now I have 3 things to thank you for... your article, the link, and the feedback. Thanks, David.Thu, 11 Feb 2010 18:51:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]george sibbald (2/11/2010)[/b][hr][quote][b]Jeff Moden (2/10/2010)[/b][hr]It makes it through QA, they put the "Approved" stamp on it, it get's shipped to the customer, and the customer tries to use the code against a couple of million rows of data. The customer spends the next week trying to figure what went wrong, sends it back to the folks that sold it to them, they spend a week trying to figure out what's wrong, finally find the problem, spend another week rewritting the code, a week in QA, and finally getting it back out to one very ticked off customer.[/quote]all too often that last bit where they re-write the code and send it back doesn't happen. You get told to wait for the next upgrade when it will be magically fixed or (I kid you not) - delete some of your data!Some BIG companies do this, I put it down to not having SQL coders in their employ.[/quote]How true, how true!Thu, 11 Feb 2010 18:49:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]Jeff Gray (2/10/2010)[/b][hr]I think you are the first person I've bumped into that gets this little "bit" of MSSQL trivia.[/quote]LOL! Thanks Jeff, that little quip made my day. :)Thu, 11 Feb 2010 09:15:51 GMTLamprey13http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]David.Poole (2/10/2010)[/b][hr]Perhaps someone could confirm this for me but on the subject of right-sizing datatypes I gather that shrinking fixed length datatypes after creation makes a metadata change but not a physical storage change.For example if you have a DECIMAL(10,2) field it will take 9 bytes of storage and can store values in the 10s of millions.If you shrink this down to a DECIMAL(9,2) field then it still takes 9 bytes of storage even though it would take 5 bytes had the table been created with this specification originally. The field will only allow data that complies with the DECIMAL(9,2) specification.I also seem to remember that BIT fields weren't part of the ANSI SQL Standard though this might have changed by now.[/quote]Test it and see what happens. I haven't tried doing this with SQL 2005 or SQL 2008, but with SQL 2000 I would expect the following behavior:1) If any data exists that requires DECIMAL(10,2) you will get a truncation error and the operation will fail.2) If all data passes the first test, then the additional bytes of storage will be deallocated and the operation will take quite some time if there are many rows.3) De-allocated space will not shrink the mdf/ndf files, although it should increase the amount of free space in those files. Even more free space may show up after the next index rebuild.4) Shrinking and re-orgarnizing the the data files may reveal more free space. This is a very costly and time-consuming operation. On large tables it will take hours and make the database unavailable. It is usually better to leave it alone and allow SQL Server to write new data into the recently de-allocated space.Please anyone with direct experience correct me if any of these assumptions is incorrect. I'd also like to add that column schema changes should always be tested for performance in a staging environment first, should always be done during maintenance windows, and should always be performed using T-SQL statements. In SQL 2000, if you change a column definition using Enterprise Manager, SQL Server will build a new table, load all of the existing table's data into it, drop the original table, and rename the new table. I don't think I have to describe how costly an operation that is. The T-SQL statement will merely change the schema and allocate/de-allocate space to match the new definition. I have found that space de-allocations are more time-consuming. In both methods, the operation will fail in the case of data truncations and incompatible data types.Thu, 11 Feb 2010 07:51:29 GMTrwoods-871939http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxGreat article! I wish I'd read this a long time ago!This is definitely being added to my briefcase!Thu, 11 Feb 2010 07:50:36 GMTRay Khttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]Malcolm Daughtree (2/10/2010)[/b][hr]Hey this is NOT a good practice "DBCC FREEPROCCACHE" frees ALL the cache inforamtion for the SERVER. Do this on a OLTP Database and LTUR (Leave town and Update resume)I wish people would read and understand these "titbits" offered in forums and view them as road to further reading and research, NOT the entire answer.[/quote]That is true. I certainly hope that anyone reading these descriptions of testing statements for performance understands that these should be done in development and staging environments and not in production environments. DBCC FREEPROCCACHE will empty all execution plans and DBCC DROPCLEANBUFFERS will empty the buffer of all table results already loaded into memory. Analyzing queries for performance metrics in any production server is not a good idea, OLTP especially. Normally I would assume that this is understood.Thu, 11 Feb 2010 07:18:33 GMTrwoods-871939http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]Jeff Moden (2/10/2010)[/b][hr]It makes it through QA, they put the "Approved" stamp on it, it get's shipped to the customer, and the customer tries to use the code against a couple of million rows of data. The customer spends the next week trying to figure what went wrong, sends it back to the folks that sold it to them, they spend a week trying to figure out what's wrong, finally find the problem, spend another week rewritting the code, a week in QA, and finally getting it back out to one very ticked off customer.[/quote]all too often that last bit where they re-write the code and send it back doesn't happen. You get told to wait for the next upgrade when it will be magically fixed or (I kid you not) - delete some of your data!Some BIG companies do this, I put it down to not having SQL coders in their employ.Thu, 11 Feb 2010 07:13:45 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxWonderful article covering so many BOMBs ....Thu, 11 Feb 2010 04:13:19 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxJeff, there is a short and illuminating article on www.infoq.com http://www.infoq.com/vendorcontent/show.action?vcr=847 regarding technical debt in agile projects that echoes what you say.Basically a project team is given a fixed immutable deadline to deliver a project and the only way to deliver on time is to compromise on quality.Another team comes along to do the next project and because of the technical debt accrued in itteration one they work slower. Inevitably the comment is made that team two is less productive than team one and pressure is applied. Again the only way of getting the project "on track" is to take short-cuts and bodge the quality.....etcWhen done properly an agile project is a joy to work on. It is so easy to talk the talk with with agile but not walk the walk!Thu, 11 Feb 2010 01:40:48 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxLike Remi said, "Where's the 10 Star button?" Outstanding article, David. Definitely a classic and a keeper.One of my personal favorite time bombs is when people write, ummmm.... "performance challenged" code because there's some sort of guarantee that there will never be more than some small number of rows in the table, some ridiculous schedule must be met, and misinformed folks believe that it's easier to write RBAR (or some other "shortcut") instead of solid, set based, scalable code. Of course, the code works great on the small number of rows in the table for the current project and everyone gets a pat on the back.Then, a new project starts and a requirement comes up where a developer (or someone else) says, "Hey, we did that on the last project" and quickly rounds up a copy of the code. Of course, the code is usually devoid of any reasonable documentation never mind a warning about how it was designed to handle only small sets of rows. They add the code to the new project and test. Because it's a new project and the test data is usually very small, it works a treat. It makes it through QA, they put the "Approved" stamp on it, it get's shipped to the customer, and the customer tries to use the code against a couple of million rows of data. The customer spends the next week trying to figure what went wrong, sends it back to the folks that sold it to them, they spend a week trying to figure out what's wrong, finally find the problem, spend another week rewritting the code, a week in QA, and finally getting it back out to one very ticked off customer.The fix for that financially and reputationally very expensive time bomb is "Never justify bad or performanced challenged code with a small rowcount... someone WILL use it for something bigger".For all those that claim that schedule justifies such a thing... take a look at the scenario above. It may have helped meet schedule on the first project but does the customer remember that you met schedule on the first project or do they remember that you sold them code that blew up in their faces? What do you suppose that customer's CEO is going to say to his/her CEO buddies in the elevator or on the golf course? Bad news travels fast and, many times, it's the only news that travels. If you don't think so, look at what Toyota is going through right now.Wed, 10 Feb 2010 20:50:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]Ryan C. Price (2/10/2010)[/b][hr][quote][b]nicholasw (2/10/2010)[/b][hr]Instead of:IF EXISTS(SELECT 1 FROM ......WHERE....)Would the following be quicker still?IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)[/quote]No, and it doesn't really make sense - TOP implies 'importance' i.e. TOP 1 means you're looking for the '1' most 'important' row. When using 'EXISTS', there is no concept of importance - there is data or there isn't. Also, doesn't TOP get lonely without an 'ORDER BY' clause ?[/quote]No... it's the other way around and only in sub-queries/CTE's or views.Wed, 10 Feb 2010 19:59:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]nicholasw (2/10/2010)[/b][hr]Instead of:IF EXISTS(SELECT 1 FROM ......WHERE....)Would the following be quicker still?IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)[/quote]Why not just test it to be sure or not? ;-)Wed, 10 Feb 2010 19:57:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx@Lamprey13:The problem existed in SQL 2005 as well. The query plan generator treats the literal 1 as an integer, and implicitly converts the in-row data to integer. This is evident by inspecting the output of the show plan.This bug resulted in the widespread misbelief that SQL doesn't use indexes on bits. I think you are the first person I've bumped into that gets this little "bit" of MSSQL trivia.Wed, 10 Feb 2010 18:51:10 GMTJeff Grayhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxExcellent article. Phil's article on Simple-Talk was good too.If I am sure the table will always be small, sometimes I will allow these tables structures just to avoid battles with the developers. When they think I am off my rocker, I will send them to these articles.Wed, 10 Feb 2010 18:08:25 GMTJon Russellhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxHey this is NOT a good practice "DBCC FREEPROCCACHE" frees ALL the cache inforamtion for the SERVER. Do this on a OLTP Database and LTUR (Leave town and Update resume)I wish people would read and understand these "titbits" offered in forums and view them as road to further reading and research, NOT the entire answer.Wed, 10 Feb 2010 17:21:48 GMTMalcolm Daughtreehttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxPerhaps someone could confirm this for me but on the subject of right-sizing datatypes I gather that shrinking fixed length datatypes after creation makes a metadata change but not a physical storage change.For example if you have a DECIMAL(10,2) field it will take 9 bytes of storage and can store values in the 10s of millions.If you shrink this down to a DECIMAL(9,2) field then it still takes 9 bytes of storage even though it would take 5 bytes had the table been created with this specification originally. The field will only allow data that complies with the DECIMAL(9,2) specification.I also seem to remember that BIT fields weren't part of the ANSI SQL Standard though this might have changed by now.Wed, 10 Feb 2010 15:22:05 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxI think there's some misunderstanding about what the TOP statement does (on the part of some readers, not the author). Unless TOP is used with a PERCENT value or an ORDER BY clause, ordering is arbitrary. The first value found is the first value fetched. Much like with the EXISTS statment, the operation returns a value once the first value satisfying the condition is found (it achieves this differently, though, as EXISTS uses a constant scan). In each of the following scenarios:1) SELECT 1 FROM [TABLE] WHERE [INDEXED_COLUMN] = 'QUERY_VALUE'2) SELECT TOP 1 * FROM TABLE WHERE [INDEXED_COLUMN] = 'QUERY_VALUE'3) SELECT TOP 1 [INDEXED COLUMN] FROM [TABLE] WHERE [INDEXED_COLUMN] = 'QUERY_VALUE'4) SELECT TOP 1 1 FROM [TABLE] WHERE [INDEXED_COLUMN] = 'QUERY_VALUE'The first operation for all of these is an index seek. For item #1, there are no other operations (other than SELECT itself, obviously). For item #2, there will also be a Bookmark Lookup to identify the table row so that the other columns can be fetched and then the TOP operation, which returns the first hit. For item #3, the next operation is the TOP operation. For item #4, the reported operations are identical to those for item #3.Operationally, item #1 is simplest, but the cost estimate of the TOP operation is 0%, and the execution plan doesn't tell the whole story. This is why you should also perform runs with STATISTICS IO and STATISTICS TIME on. As has been mentioned earlier, you will want to run DBCC FREEPROCCACHE between each run to clear the execution plan, but you will also want to run DBCC DROPCLEANBUFFERS to clear the results from the buffer cache in memory.I should first mention that you should be careful here. If you just run the SELECT statements as above, you WILL NOT get the same results as you will if the SELECT statements are wrapped in an EXISTS statement. The reason for this should be obvious. Item #2 must return all the columns for the row found and item #1 must return a 1 for every row that matches the WHERE condition, whether this is one row or 500,000 rows. If EXISTS is used, only the existence of a value is queried, and the operation stops and returns its results. If you only run the SELECT statements by themselves, then item #2 will perform a massive number of logical reads and item #4 will perform a large number of logical reads.Wrapped in an EXISTS statement, however, all of the above perform the same number of reads. Differences in CPU time and run-time are minute and inconsistent. Real distinctions can probably only be found running several iterations with different tested values. As always, test these assumptions against real-word results. Remember that my examples are all comparisons of a single value against a single indexed column. If you CAST the value being compared (in order to perform calculations on it, for example), you will get different results.Wed, 10 Feb 2010 14:54:12 GMTrwoods-871939http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxThanks David, I appreciate your follow up comments and great article.Wed, 10 Feb 2010 13:18:10 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxExcellent article. I have run into all of these things. Another poor practice is using bloated datatypes. On a large database, using the smallest reasonable integer, date, and money types can save a lot of space. This translates to less reading to get the needed information. The problem is compounded when the bloated field is a primary key and used as a foreign key in a number of other tables. A related matter is using a composite primary key, and then using that as a foreign key in multiple child tables. Using a right-sized integer field for a surrogate PK saves lots of space in the child tables.I'm saving this article as a favorite. Thanks again!!Wed, 10 Feb 2010 13:05:17 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxIf I remember correctly SQL2000 and previous had "issues" with BIT columns. I also was dealing with 100+ Million row tables that had many BIT columns and the simple act of CASTing the literal to a BIT had incredible speed increases. For example:SELECT *FROM FooWHERE BitColums = CAST(1 AS BIT)Wed, 10 Feb 2010 13:03:27 GMTLamprey13http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote]Given SQL 2005/2008 would you recommend the same solution, use a filtered index, or something else?[/quote]I think I would still stick with the queue table for the reasons mentioned earlier.My thoughts on filtered indexes are that they would be useful where an application needed to record many different possible values in a field but would only need to search on a few values. I'm thinking of a workflow thing, I want to see records below a certain status but beyond that I have no interest.The key thing is that data generated in a backend system can be a different structure to the data in the front end application which in turn could and should be different from the structure in a reporting environment. Indexing strategies in each environment can be very different. For that matter referential integrity can be very different in each environment. Data in a reporting environment may not need referential integrity because the data is created somewhere else where DRI is enforced. Before anyone jumps in, yes, I do know that the query optimiser makes use of the various constraints to produce an execution plan.I'm working on an agile project at the moment where we might use a filtered index in the short term but the longer term plan is to split the table into a queue table and a longer term table.Wed, 10 Feb 2010 12:30:26 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspx[quote][b]David.Poole (2/10/2010)[/b][hr]I didn't use a filtered or nonclustered index on the base table for two reasons.1. The original solution was on SQL2000.[/quote]Given SQL 2005/2008 would you recommend the same solution, use a filtered index, or something else?Wed, 10 Feb 2010 12:04:37 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxI missed the SQL 2000 limitation. Also, too many times we as DBAs need to solve these performance problems and that changing the client app is not an option. [quote][b]David.Poole (2/10/2010)[/b][hr]My career started with certain disciplines hammered into you.[/quote]Ain't that the truth! :-)Wed, 10 Feb 2010 11:16:35 GMTjjensen-892196http://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxI didn't use a filtered or nonclustered index on the base table for two reasons.1. The original solution was on SQL2000.2. The main table had incredibly heavy read activity on it.The separate queue table allowed me to have the job quietly polling that very small table while the main read activity hit the large table doing a nice index seek in each case.I'm not sure that, given the choice, I would put bit flags in the main table in any case. Once you get into the 10s of millions of records even the storage of bit fields costs something. My career started with certain disciplines hammered into you.1. A stitch in time saves nine.2. Keep it simple stupid. KISS.3. Attention to detail in all things4. Store only what is necessary, when it is necessary.Some of the latest thoughts on technical debt in agile development are bringing these ancient thoughts back into the forefront of developer thinking. Long may it last.Wed, 10 Feb 2010 10:59:21 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxI'm adding this to my repository of excellent SQL articles. Very nice job!Wed, 10 Feb 2010 10:56:54 GMTjminettehttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxFantastic article, thank you. Related to your very first example, one should always think about scaling when writing code. Sometimes, especially when making prototypes, the deliberate plan is to rewrite for scaling and optimization later, but there should always be a consciousness of the need to plan for scaling.Wed, 10 Feb 2010 10:21:53 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic862916-60-1.aspxWOW David !This should be required reading for all System Architects, Database Designers and SQL Programmers. It is clear and succinct in its explanation of what we collectively know as what being a DBA is all about. Thanks !Wed, 10 Feb 2010 09:59:51 GMTrudy - Doctor "X"