InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:57:58 GMT20http://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxThanks for tough question though I liked it alot since it covers the index topic and always an important concept to grasp.Fri, 17 Feb 2012 16:06:24 GMTzymoshttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxGreat question Hugo.I enjoyed doing the math, even though I got it wrong through a silly calculation error, but it was fun having a dive into the Math behind the Tables. :)Wed, 23 Nov 2011 05:17:59 GMTAndeavourhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxExcellent question, keep 'em coming.Tue, 22 Nov 2011 14:49:30 GMTSQLDCHhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxBrain hurts. I'm playing catchup a few days late so I didn't run into most of the concerns the others did. My math just sucks and I actually did a full rebuild of the problem and didn't look into all the angles.More please. :w00t:Mon, 21 Nov 2011 13:21:48 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxI like hard questions like this. Please submit others.Thu, 17 Nov 2011 14:02:02 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxKeep em coming. They are tough questions but that's how your learn.Thu, 17 Nov 2011 05:47:04 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxgood question hugo - looks like Steve has fixed it up.Thu, 17 Nov 2011 05:01:18 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Hugo Kornelis (11/16/2011)[/b][hr]My question is: would you like a few more questions of this kind, or would submitting the rest of the questions I was planning expose me to death threats, stale fruit, or lots of boo-ing and hissing?This is a serious question. If the majority feels that the QotD should not involve that much work, I will not submit the other similar questions I was planning.[/quote]It's a good question and I would like to see more of the same.Thanks.Thu, 17 Nov 2011 01:19:30 GMT440692 I am just a numberhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]rashton (11/16/2011)[/b][hr]Admittedly, getting it right may have biased my opinion, but I have to say that this is possibly the best QotD I've tried so far so please, more like this Hugo! :-)I would also mention that having done the calculations by hand, I double-checked them by running the code (I used the numbers 1 to 1000000 from a tally table in another DB I keep on hand for the PK column values) then using this query:[code="sql"]SELECT SUM(page_count)FROM sys.dm_db_index_physical_stats( DB_ID(), OBJECT_ID('DemoTable'), NULL, NULL, 'DETAILED');[/code]Since you specified SQL Server 2005 and up in the question, I figured it was a good way of highlighting how the Dynamic Management Views can help us work these things out.[/quote]Absolutely awesome method of getting the answer. I agree on the previous points, great question, great discussion, don't mind seeing more.I will say I got it wrong because I did a rough calculation, saw it was well over 1GB and just assumed you were testing knowledge that the reindex would require double the space. I certainly didn't mind getting it wrong though, and really enjoyed the answer and discussion.KennethKennethWed, 16 Nov 2011 15:35:56 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxEdit: Whatever...Wed, 16 Nov 2011 14:59:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxand I am sorry about your mater db :-)Wed, 16 Nov 2011 14:53:45 GMTIulian -207023http://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxThank you Hugo, now I understand better not only the sql exercise of the qotd but also the exercise of making a good question.Well done! I hope to see more questions like this one.IulianWed, 16 Nov 2011 14:49:55 GMTIulian -207023http://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]jeff.mason (11/16/2011)[/b][hr]Now, see, I didn't even check for the correct error. I played by the rules and pulled up the calculator and got row size and got your first set of numbers (page size per row) and figured out how big the table was, and looked for "error" and stopped. I didn't even check that it was the exact error you'd get. So that'd be one on me then![/quote]To clarify my previous reply, I think you (and everyone else who selected the error option) deserve the point. Not getting a point for a nitty gritty difference in the error message is, in my opinion, not in the spirit of the QotD. The question should test your understanding of SQLL Server, not your reading skills.FYI, I have sent Steve a PM requesting him to award points back to everyone who chose the error option, and to change the question to specify a 3.5 GB data file (so that now the error message is no longer correct).Wed, 16 Nov 2011 14:42:23 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Hugo Kornelis (11/16/2011)[/b][hr]However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows). [/quote]Now, see, I didn't even check for the correct error. I played by the rules and pulled up the calculator and got row size and got your first set of numbers (page size per row) and figured out how big the table was, and looked for "error" and stopped. I didn't even check that it was the exact error you'd get. So that'd be one on me then!Wed, 16 Nov 2011 14:36:49 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Iulian -207023 (11/16/2011)[/b][hr]I am not worried about the lost point, but is there anything I have missed?[/quote]No, that was my mistake. I added the index rebuild to the question to make sure there was no fragmentation, regardless of how the data was filled. But I forgot that rebuilding an index required SQL Server to temporarily duplicate it - so during the rebuild, you need twice as much space for the table. That's a little over 3 GB.I should have tested with the given amount of data. But I wanted to save time, so I tested with one tenth and checked that all the calculation and formulas I had in my spreadsheet exactly predicted the amount of pages used. I was then confident that my date size calculations would be correct for the full million rows as well. Which in fact they were - if I had specified the size of the data file as 3.5GB or so, there would not have been any problem.(Funny side story - just to be sure, I just now did run the tests for the full million rows. First with a 2GB data file, then with a 3.5GB data file. To my utter surprise, the index rebuild did NOT produce an error when I tested with the 2GB data file. It took me some time before I found the cause - a missing USE statement. I now have a very bloated master database... :Whistling:)Wed, 16 Nov 2011 14:25:12 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Hugo Kornelis (11/16/2011)[/b][hr][quote][b]SanDroid (11/16/2011)[/b][hr]So now I am confused again. Are the 47% that selected this answer to the question Right or Wrong? :-P[/quote]If I had the possibility to mark their answers correct and award back points, I would. But I don't have that option. And I don't think the software even caters for the possiblity of a question where two answers are marked correct (unless you require people to identify all correct answers).However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows). But given that there is only one answer in the "out of space" area and no "none of the above" option, I do believe that those 47% should have gotten their points.[/quote]I am certain Steve will correct me if I am wrong, but I think the "software" has the ability to do exactly that. Maybe you should ask him... :w00t:Wed, 16 Nov 2011 14:16:34 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]SanDroid (11/16/2011)[/b][hr]Obviously lots of people learned today, including you. Lets have some more. It could even be worth some of the stale fruit you might recieve. :hehe:[/quote]No stale fruit today, but a very interesting discussion where we all learned. I definitely took away some things to consider for my next questions! There might be errors in those as well, but I'll do my very best to make sure that they will at least be NEW errors! ;-)Wed, 16 Nov 2011 14:16:13 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]SanDroid (11/16/2011)[/b][hr]So now I am confused again. Are the 47% that selected this answer to the question Right or Wrong? :-P[/quote]If I had the possibility to mark their answers correct and award back points, I would. But I don't have that option. And I don't think the software even caters for the possiblity of a question where two answers are marked correct (unless you require people to identify all correct answers).However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows). But given that there is only one answer in the "out of space" area and no "none of the above" option, I do believe that those 47% should have gotten their points.Wed, 16 Nov 2011 14:14:00 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxAfter doing the calculation I was tempted to answer the queston but to be sure I ran the script and got the error:[code="sql"]The statement has been terminated.Msg 1101, Level 17, State 12, Line 2Could not allocate a new page for database 'QOTD' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.[/code]Now the script I ran isCreating the database:[code="sql"]CREATE DATABASE [QOTD] ON PRIMARY ( NAME = N'QOTD' , FILENAME = N'...\QOTD.mdf' , SIZE = 2097152KB , MAXSIZE = UNLIMITED , FILEGROWTH = 0) LOG ON ( NAME = N'QOTD_log' , FILENAME = N'...\QOTD_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE [QOTD] SET COMPATIBILITY_LEVEL = 100GO[/code]Then creating the table:[code="sql"]USE [QOTD]GO CREATE TABLE dbo.DemoTable (DemoTableKey char(10) NOT NULL, LargeNumber bigint NOT NULL, ShortDescription char(80) NOT NULL, LongDescription nchar(600) NULL, Price money NOT NULL, Counter1 int NOT NULL, Counter2 bigint NOT NULL, Counter3 smallint NOT NULL, Counter4 int NOT NULL, AddDate datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP), ChgDate datetime NULL, CONSTRAINT PK_DemoTable PRIMARY KEY (DemoTableKey), CONSTRAINT CK_LargeNumber CHECK (LargeNumber > 0) ); GO[/code]Now filling the 1 mil records: [code="sql"] ; WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 lines E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 lines E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 lines E5(N) AS (SELECT 1 FROM E4 a, E2 b) --10E+6 or 1,000,000 INSERT INTO [dbo].[DemoTable] ([DemoTableKey] ,[LargeNumber] ,[ShortDescription] ,[LongDescription] ,[Price] ,[Counter1] ,[Counter2] ,[Counter3] ,[Counter4] -- ,[AddDate] ,[ChgDate]) SELECT ROW_NUMBER() OVER (ORDER BY N) , 1 , 'NONE' , NULL , 1 , 1 , 1 , 1 , 1 -- , -- DEFAULT , NULL FROM E5 GO[/code]Everyting is OK so far, all ran successfuly.Finaly rebuilding the index: [code="sql"]ALTER INDEX ALL ON dbo.DemoTableREBUILD WITH (FILLFACTOR = 100, DATA_COMPRESSION = NONE, MAXDOP = 1);-- TRUNCATE TABLE dbo.DemoTable[/code]I am not worried about the lost point, but is there anything I have missed?One mention I did not get the error after the INSERT statement but after running the ALTER INDEX statement.The question is great, a strong question, I would like to see more like this.Thank you,IulianWed, 16 Nov 2011 13:51:07 GMTIulian -207023http://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Hugo Kornelis (11/16/2011)[/b][hr]My question is: would you like a few more questions of this kind, or would submitting the rest of the questions I was planning expose me to death threats, stale fruit, or lots of boo-ing and hissing?This is a serious question. If the majority feels that the QotD should not involve that much work, I will not submit the other similar questions I was planning.[/quote]Hugo, please more! Just please next time use the same numbers in the QOTD that you use when doing your reserach. ;-)Obviously lots of people learned today, including you. Lets have some more. It could even be worth some of the stale fruit you might recieve. :hehe:Wed, 16 Nov 2011 13:50:16 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]jeff.mason (11/16/2011)[/b][hr]What's nice is that no one got out of control, angry, or vindictive. A good exchange with a positive outcome is a good thing.[/quote]Jeff. That is very very true. And why this is now one of my favorite QOD discussions EVER!Thanks again HUGO. Your question might have been a little busted, but the learning and dicussion it inspired was definately not. :smooooth:Wed, 16 Nov 2011 13:43:27 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxWhat's nice is that no one got out of control, angry, or vindictive. A good exchange with a positive outcome is a good thing.Wed, 16 Nov 2011 13:40:27 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxI guess the bigest thing I learned from the QOD today is to make certain you have done your research and fully tested what you are saying[b] [i][u]BEFORE[/u][/i] [/b] telling someone they are wrong.Wed, 16 Nov 2011 13:37:38 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]jeff.mason (11/16/2011)[/b][hr][quote][b]SanDroid (11/16/2011)[/b][hr]Now if I could only determine why me, and others still see error 1105 when working with the table the way you mentioned. Could it be that all the databases on our new test server have a certain type of compression enabled by default?[/quote]If you are running the code with the given parameters, you'll get that error because of the reindex. That's why I missed this question when I answered it -- I chose "error" because if you calculate the size of the 1 million rows, they are bigger than 1 GB. And since a reindex makes a complete copy of the table for the reindex process (assuming you have a clustered index, which is why the clustering matters), that means you need more than 2 GB and the question fails as structured. Hugo did a good job of catching that early and stating his mistake.[/quote]Exactly what I thought. But since my 2008 R2 test system has row compression enabled as a default I was trying to determine if it was that or just the reindex alone that caused it.Thanks again Jeff! :cool:Wed, 16 Nov 2011 13:33:02 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]SanDroid (11/16/2011)[/b][hr]Now if I could only determine why me, and others still see error 1105 when working with the table the way you mentioned. Could it be that all the databases on our new test server have a certain type of compression enabled by default?[/quote]If you are running the code with the given parameters, you'll get that error because of the reindex. That's why I missed this question when I answered it -- I chose "error" because if you calculate the size of the 1 million rows, they are bigger than 1 GB. And since a reindex makes a complete copy of the table for the reindex process (assuming you have a clustered index, which is why the clustering matters), that means you need more than 2 GB and the question fails as structured. Hugo did a good job of catching that early and stating his mistake.Wed, 16 Nov 2011 13:30:33 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Hugo Kornelis (11/16/2011)[/b][hr][quote][b]fhanlon (11/16/2011)[/b][hr]I like the question but the answer says:Since 200,474 pages equates to about 1.53 GB, you will not get error 1105. I ran this code and I got error 1105 which I would not have received had I allocated more than 2G or allowed autogrowth but we were specifically told not to do this. The page count only goes down once the data is compressed.[/quote]Yes, you are right - but not for the reasons yoou mention.When I wrote the question, I was afraid that there would be discussion over fragmentation that occurs while filling the table. To prevent that, I added the index rebuild step - and completely forgot that during the rebuild, the index temporarily exists twice, doubling the space requirement. After the index rebuild is finished, the numbers are correct. And if I had specified a larger data file (3.5 GB or so), the error would not have occured.[/quote]So now I am confused again. Are the 47% that selected this answer to the question Right or Wrong? :-PWed, 16 Nov 2011 13:30:21 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Hugo Kornelis (11/16/2011)[/b][hr][quote][b]SanDroid (11/16/2011)[/b][hr]I was editing my original post while you read and resonded to this. The original post did not comunicate what I intended.[/quote]No problem, that can happen. I'll go back to the revised version of your message.[quote]The tables Primary Key Clustered Index is not at all related to the data stored in the table or to the 2 Million new rows being inserted into it.[/quote]That's just an assumption. The question contains no information to confirm or deny this theory.[/quote]Very true. That assumption was made after you told somone that asked you what values should be put into the Primary Key since that was missing from the QOTD when you mentioned the data that would be inserted. You mentioned the char column could be generated random values. I belived you meant generated random value by the insert statement code, not by the "business" prior to the insert. That makes a lot more sense and has the perfect level of detail. Thank you for clarifying.Wed, 16 Nov 2011 13:10:15 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Hugo Kornelis (11/16/2011)[/b][hr]Even after rereading the entire discussion, I fail to see wherer others point out errors that relate to the data page size. Can you elaborate, please?[/quote]I was refering to where you had 20,000 instead of 200,000 or whatever.[quote]Not at all! Just after the data insert, the information from this DMV would be highly skewed. In a real-world system (which this is not!), you could indeed get some useful information from the missing indexes DMVs - but only after letting it run for a while, or running a representative test. This view is added to when the optimizer compiles a query that could benefit from an index that doesn't exist. These suggestions are only for a single query. If you plan to use this DMV for real tuning, try to combine the various suggestions into a single index that helps lots of queries. And never forget that these suggestions give an indication of estimated saving for a single query, but do not take into account how the extra index might harm performance of inserts, updates, deletes, and merges.[/quote]You are absolutely right. Just trying to point out the DMV used to find the columns of a good NONCLUSTERED index. [quote]The Books Online article you link to does try to explain this, but does not really phrase this well. There is a much better explanation in the CREATE TABLE article at [url=http://msdn.microsoft.com/en-us/library/ms174979.aspx]http://msdn.microsoft.com/en-us/library/ms174979.aspx[/url], where it reads:"CLUSTERED | NONCLUSTERED [/quote]Completely agreed. Again someone else mentioned this to me. We both thought before today that when creating a Primary Key constaring the Index could only be CLUSTERED unless a clustered Index existed already. [quote]As far as I see, this is exactly what I say in my explanation - in the absence of an explicit CLUSTERED or NONCLUSTERED keyword, the index for the primary key defaults to clustered (unless another index is specified as clustered - an edge case that I chose not to include in the explanation as it does not apply here and seldom -if ever!- occurs in real world situations).[/quote]Apparently so! The edge case I have never seen in the real world is a table with a single nonclustered index on the primary key. I seriously thought this was not even possible. So much to still learn about what is good, and what is possible, and why.Thank you both Hugo and Jeff.Now if I could only determine why me, and others still see error 1105 when working with the table the way you mentioned. Could it be that all the databases on our new test server have a certain type of compression enabled by default?Wed, 16 Nov 2011 13:03:09 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]fhanlon (11/16/2011)[/b][hr]I like the question but the answer says:Since 200,474 pages equates to about 1.53 GB, you will not get error 1105. I ran this code and I got error 1105 which I would not have received had I allocated more than 2G or allowed autogrowth but we were specifically told not to do this. The page count only goes down once the data is compressed.[/quote]Yes, you are right - but not for the reasons yoou mention.When I wrote the question, I was afraid that there would be discussion over fragmentation that occurs while filling the table. To prevent that, I added the index rebuild step - and completely forgot that during the rebuild, the index temporarily exists twice, doubling the space requirement. After the index rebuild is finished, the numbers are correct. And if I had specified a larger data file (3.5 GB or so), the error would not have occured.Wed, 16 Nov 2011 12:54:40 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxHugo - great question.As for more questions like this - why not, though that means no cheap points!Wed, 16 Nov 2011 12:42:34 GMTpaul s-306273http://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]SanDroid (11/16/2011)[/b][hr]I was editing my original post while you read and resonded to this. The original post did not comunicate what I intended.[/quote]No problem, that can happen. I'll go back to the revised version of your message.[quote]Others have pointed out that some of your statement also contradict ones the articles they reference as do the numbers you have in your math. I can see around that though becuase a SQL data page has been the same size for a long time and your table rows fit.[/quote]Even after rereading the entire discussion, I fail to see wherer others point out errors that relate to the data page size. Can you elaborate, please?[quote]The primary thing this question is missing from a real world size calculations perspective (...)[/quote]You are completely right. But I never intended the question to be from a real world size calculations. In the real world, one would never calculate the data size to the exact page number. And one would never simply specify the log file size as "sufficiently large",The intention of my question was to test understanding of (an aspect of) how data is stored on disk. Your post mentions a lot of considerations that are all very relevant for estimating disk space requirements in a real-world scenario, but are not relevant for this QotD. I hope readers take note of what you say, but for this discussion, I won't respond to those points.[quote]a char row of random values for a primary key (instead of a finding a candidate key in the data)[/quote]The only place where I mention using random values for a primary key is in a prior message in this discussion, not in the question itself. The context of that remark was a calculation of the number of theoretically possible key values to show that it's easy to generate unique values for a million rows - so easy that even a random value would have an extreme low likelihood of producing a duplicate.Since the table and columns are completely fictional, there is no way to assess if the char(10) primary key is a surrogate key or a key from the business (though people who know me would know that I would never use a surrogate key without also adding a unique constraint on the business key!)FYI, there defintely are cases where a char(10) column from the business data is the perfect candidate key. This could be one of them.[quote]Not certain if Auto create Statistics was left on or off so I will believe it was the default or on.[/quote]Good point. I should probably have added in the question that statistics should not be included in the calculation. [quote]The tables Primary Key Clustered Index is not at all related to the data stored in the table or to the 2 Million new rows being inserted into it.[/quote]That's just an assumption. The question contains no information to confirm or deny this theory.[quote]The stats that would exist before and after the Index rebuild would require more disc space than a good Non-Clustered index.[/quote]I was unable to find information on the size of statistics, but since they contain a lot less information that indexes, I fail to see how any stats could ever require more disk space than any index.That being said, this also feels like comparing apples to oranges to me.[quote]A proper Table Primary Key created from two or more candidate key columns would also create a Clustered Index that could reduce the true space required by this table and all the data structures that it has.[/quote]Only if the total size of all columns in that primary key is less than 10 bytes. Otherwise, it'll take more size than the current clustered index. (And if the DemoTableKey is then declared as UNIQUE, which it should, the actual space usage will in fact go up instead of down!)[quote]The sys.dm_db_missing_index_details management view can be used after the initial data insert is completed to find out exactly what a good non-clustered index would need to be.[/quote]Not at all! Just after the data insert, the information from this DMV would be highly skewed. In a real-world system (which this is not!), you could indeed get some useful information from the missing indexes DMVs - but only after letting it run for a while, or running a representative test. This view is added to when the optimizer compiles a query that could benefit from an index that doesn't exist. These suggestions are only for a single query. If you plan to use this DMV for real tuning, try to combine the various suggestions into a single index that helps lots of queries. And never forget that these suggestions give an indication of estimated saving for a single query, but do not take into account how the extra index might harm performance of inserts, updates, deletes, and merges.[quote]With that being said. You can not create a Primary Key on a table without a CLUSTERED INDEX being creted for it.[/quote]You can. Just specify the optional NONCLUSTERED keyword when specifying the primary key. See the sample code posted by Jeff Mason, that will create a heap (table without clustered index), plus a nonclustered index to support the primary key.The Books Online article you link to does try to explain this, but does not really phrase this well. There is a much better explanation in the CREATE TABLE article at [url=http://msdn.microsoft.com/en-us/library/ms174979.aspx]http://msdn.microsoft.com/en-us/library/ms174979.aspx[/url], where it reads:"CLUSTERED | NONCLUSTERED Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED. In a CREATE TABLE statement, CLUSTERED can be specified for only one constraint. If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY defaults to NONCLUSTERED."As far as I see, this is exactly what I say in my explanation - in the absence of an explicit CLUSTERED or NONCLUSTERED keyword, the index for the primary key defaults to clustered (unless another index is specified as clustered - an edge case that I chose not to include in the explanation as it does not apply here and seldom -if ever!- occurs in real world situations).Wed, 16 Nov 2011 12:39:20 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]jeff.mason (11/16/2011)[/b][hr]If I understand your question, this should address it. Run the first two batches first, investigate the indexes, and then run the third batch. No errors......... So unless you are thinking of something else and I still misunderstand, this should settle that point.[/quote]I think it was me that had misunderstood. Since I have always created a table PK as a unique set of values and the first one I never thought that you could make a PK constraint and set it to noncluster unless a cluster index already existed. I see now that realy this is just how the defaults work for when you do not specify CLUSTERED or NONCLUSTERED.:w00t:Thanks for being patient and pointing that out. Awesome!Wed, 16 Nov 2011 12:31:02 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxIf I understand your question, this should address it. Run the first two batches first, investigate the indexes, and then run the third batch. No errors.Create table test1( test1 int primary key nonclustered identity (1,1),test1text varchar(255))GO insert test1 values ('test1')insert test1 values ('test2')insert test1 values ('test3')insert test1 values ('test4')insert test1 values ('test5')GOcreate clustered index test2 on test1 (test1text)GOIn other words, you can created a nonclustered primary key without a clustered index. Nothing stops a heap from having a primary key. The point is that if you start with a heap and don't specify what the PK should be, the default will be clustered. That was Hugo's point and the article you link agrees. So unless you are thinking of something else and I still misunderstand, this should settle that point.Wed, 16 Nov 2011 11:25:30 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]jeff.mason (11/16/2011)[/b][hr]SanDroid, you have me totally confused. Hugo said that if you don't specify NONCLUSTERED on creation of a Primary Key on a table that has has no clustered index, the index that supports the primary key will be clustered. The article you post says exactly the same thing. Why do you object? I don't even understand what your objection is, and Hugo's explanation of the creation of a clustered index on a primary key seemed basic and accurate to me.[/quote]What is in the referenced material:[quote]When you create a PRIMARY KEY constraint, a [b] unique clustered index on the column or columns is automatically created [/b] if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. [/quote]What was stated by Hugo:[quote]Since the PRIMARY KEY constraint is created without a CLUSTERED or NONCLUSTERED specification, the supporting index will be created as a clustered index[/quote] Creating a NONCLUSTERED Index for a Primary key constraint is not something that can be done just by specifying NONCLUSTERED in the initial table create statement of a table with only one index by design. I was asked today if creating a NONCLUSTERED index for a PRIMARY KEY on a table with no INDEXEs was possible after someone else read this.Wed, 16 Nov 2011 11:05:34 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxSanDroid, you have me totally confused. Hugo said that if you don't specify NONCLUSTERED on creation of a Primary Key on a table that has has no clustered index, the index that supports the primary key will be clustered. The article you post says exactly the same thing. Why do you object? I don't even understand what your objection is, and Hugo's explanation of the creation of a clustered index on a primary key seemed basic and accurate to me.Wed, 16 Nov 2011 10:54:32 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxReposting Edit as new post.[quote][b]Hugo Kornelis (11/16/2011)[/b][hr]Ouch, that is a very embarassing mistake. Can you please elaborate on which of my statements you mean, and which articles they contradict? I thought I checked everything very carefully; if I was wrong, I'd like to know before I start working on the next similar question.[/quote] You can not create a Primary Key on a table without a CLUSTERED INDEX being created for it. This is AutoMagic in SQL server. You allude in your explination that the Index is Clustered because it is not specified as NON-Clustered and that is not correct. There is a link to this information in your reference on primary key constraints. Here is the direct link to Uni[url]http://msdn.microsoft.com/en-us/library/ms175132.aspx[/url]Here is the quote:[quote]When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values. When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist. [/quote]I think your explination confuses what happens when you create a PRIMARY KEY constraint with what can happen if a UNIQUE constraint has been created first.This is important to consider when sizing new data tables as you should use a UNIQUE constraint until you find proper candidate for the PRIMARY KEY or create a set of normalized tables with Row_Id's as primary keys.Wed, 16 Nov 2011 10:41:08 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]fhanlon (11/16/2011)[/b][hr]I like the question but the answer says:Since 200,474 pages equates to about 1.53 GB, you will not get error 1105. I ran this code and I got error 1105 which I would not have received had I allocated more than 2G or allowed autogrowth but we were specifically told not to do this. The page count only goes down once the data is compressed.[/quote]I found this also. There are several other settings the question ignored that can change your outcome. :cool:Wed, 16 Nov 2011 10:27:17 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxI like the question but the answer says:Since 200,474 pages equates to about 1.53 GB, you will not get error 1105. I ran this code and I got error 1105 which I would not have received had I allocated more than 2G or allowed autogrowth but we were specifically told not to do this. The page count only goes down once the data is compressed.Wed, 16 Nov 2011 10:04:50 GMTfhanlonhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspx[quote][b]Hugo Kornelis (11/16/2011)[/b][hr]Again, thanks for all the nice and encouraging comments. I have now decided to continue with the series. Sorry for those who did not like it and would rather not see it continue; based on the feedback so far, you are in the minority.[quote][b]SanDroid (11/16/2011)[/b][hr][quote][b]The main thing this question is missing is if the DB was created with FULL or BULK as the option for logging.[/quote]How exactly is that relevant for the answer? No errors due to a full log are possible, as the log file is specified to be "sufficently large".[quote]Some of your statements contradict ones the articles they reference.[/quote]Ouch, that is a very embarassing mistake. Can you please elaborate on which of my statements you mean, and which articles they contradict? I thought I checked everything very carefully; if I was wrong, I'd like to know before I start working on the next similar question.[/quote]I was editing my original post while you read and resonded to this. The original post did not comunicate what I intended. EDIT: With that being said. You can not create a Primary Key on a table without a CLUSTERED INDEX being creted for it. This is AutoMagic in SQL server. You allude in your explination that the Index is Clustered because it is not specified as NON-Clustered. There is a link to this information in your reference on primary key constraints [url]http://msdn.microsoft.com/en-us/library/ms175132.aspx[/url]Wed, 16 Nov 2011 09:29:54 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1206403-1328-1.aspxAgain, thanks for all the nice and encouraging comments. I have now decided to continue with the series. Sorry for those who did not like it and would rather not see it continue; based on the feedback so far, you are in the minority.[quote][b]SanDroid (11/16/2011)[/b][hr][quote][b]The main thing this question is missing is if the DB was created with FULL or BULK as the option for logging.[/quote]How exactly is that relevant for the answer? No errors due to a full log are possible, as the log file is specified to be "sufficently large".[quote]Some of your statements contradict ones the articles they reference.[/quote]Ouch, that is a very embarassing mistake. Can you please elaborate on which of my statements you mean, and which articles they contradict? I thought I checked everything very carefully; if I was wrong, I'd like to know before I start working on the next similar question.Wed, 16 Nov 2011 09:01:47 GMTHugo Kornelis