Search results matching tags 'SQL Server 2008' and 'books'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2008,books&orTags=0Search results matching tags 'SQL Server 2008' and 'books'en-USCommunityServer 2.1 SP2 (Build: 61129.1)SSIS Design Patterns, the Bookhttp://sqlblog.com/blogs/andy_leonard/archive/2012/08/06/ssis-design-patterns-the-book.aspxMon, 06 Aug 2012 16:37:43 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44587andyleonard<p>For the past two years, I have had the honor and privilege or authoring <a href="http://www.amazon.com/SSIS-Design-Patterns-Matt-Masson/dp/1430237716" target="_blank">SSIS Design Patterns</a> alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date. </p> <p>I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:</p> <blockquote> <p><em>Deliver quality late, no one remembers. <br />Deliver junk on time, no one forgets.</em></p> </blockquote> <p>The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal. </p> <p>:{&gt;</p>Geek City: Too Many Indexes!http://sqlblog.com/blogs/kalen_delaney/archive/2009/01/18/too-many-indexes.aspxSun, 18 Jan 2009 20:37:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:11244Kalen Delaney<P>I wrote about <A href="http://sqlblog.com/blogs/kalen_delaney/archive/2008/09/20/too-many-columns.aspx" target=_blank>"Too Many Columns"</A> last September, and along with changes in SQL Server 2008 that allow lots of columns, there is also the ability to create more than the old maximum of 249 nonclustered indexes on a table. I knew this fact, but somehow I overlooked it when updating the chapter on table structures in my new book. My omission also got past all the editors, and I just realized my mistake when reviewing the Indexes chapter, which was being updated for SQL 2008 by Kimberly Tripp.&nbsp; </P>
<P>I found that Books Online has not been completely updated (and I have filed a doc bug on this already). This page still says the limit is 249&nbsp; nonclustered indexes per table:</P>
<P><A title=http://msdn.microsoft.com/en-us/library/ms190197.aspx href="http://msdn.microsoft.com/en-us/library/ms190197.aspx">http://msdn.microsoft.com/en-us/library/ms190197.aspx</A></P>
<P>However, the page for CREATE TABLE at <A title=http://msdn.microsoft.com/en-us/library/ms174979.aspx href="http://msdn.microsoft.com/en-us/library/ms174979.aspx">http://msdn.microsoft.com/en-us/library/ms174979.aspx</A> does say:</P>
<BLOCKQUOTE>
<P>Each table can contain a maximum of 999 nonclustered indexes, and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.</P></BLOCKQUOTE>
<P>You can take my script to create a table with lots of columns, and edit it to create a UNIQUE constraint on every column.
<P><FONT face="Courier New" size=2>DECLARE @create varchar(max);<BR>DECLARE @tabname sysname;<BR>DECLARE @numcols int; <BR>DECLARE @col int;<BR>SELECT @numcols = 900;<BR>SELECT @tabname = 'wide' + CONVERT(varchar, @numcols); </FONT>
<P><FONT face="Courier New" size=2>SELECT @create = 'CREATE TABLE ' + @tabname + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' (ID int IDENTITY, '; </FONT>
<P><FONT face="Courier New" size=2>SELECT @col = 1;<BR>WHILE @col &lt; @numcols BEGIN<BR>&nbsp;&nbsp;&nbsp; IF (@col % 3) = 0 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' int UNIQUE,';<BR>&nbsp;&nbsp;&nbsp; IF (@col % 3) = 1&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' char(5) UNIQUE,';<BR>&nbsp;&nbsp;&nbsp;&nbsp; IF (@col % 3) = 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' varchar(25) UNIQUE,';<BR>SELECT @col = @col + 1;<BR>END;<BR>SELECT @create = @create + 'col' + CONVERT(varchar, @col) + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' int default 0);'<BR>PRINT @create&nbsp; <BR>EXECUTE (@create) </FONT>
<P>After the table is created, you can examine <EM>sys.indexes</EM>:
<P><FONT face="Courier New" size=2>SELECT name, index_id, type_desc<BR>FROM sys.indexes<BR>WHERE object_id = object_id('wide900');</FONT>
<P>You'll definitely see more than 249 indexes! In addition, you might note that there is a gap in the <EM>index_id</EM> values. The values 251 - 255 are not used, and this is for backward compatibility. In much older versions, <EM>index_id</EM> (or <EM>indid</EM>) 255 was reserved for the LOB data belonging to a table, and the values from 251 - 254 were reserved. So that range is still not used. But you can see that I have <EM>index_id</EM> values from 256 - 905 in this table.
<P>So, thanks Kim~
<P>One more place that Books Online could use a little tweaking is in the section on Programmability Enhancements:
<P><A title=http://msdn.microsoft.com/en-us/library/cc645577.aspx href="http://msdn.microsoft.com/en-us/library/cc645577.aspx">http://msdn.microsoft.com/en-us/library/cc645577.aspx</A>
<P>The section on "Wide Tables" seems to imply that you need to have an XML column set in order to get 1000 indexes. It doesn't actually say that, so it's not really a bug, but it does seem to imply it:
<BLOCKQUOTE>
<P>Wide tables are tables that contain one or more column sets. A wide table can contain up to 30000 columns, 1000 indexes, and 30000 statistics.</P></BLOCKQUOTE>
<P>Because you can only reach the 30000 column limit with a wide table, it seems to imply that the 1000 index limit is only for wide tables. But as my script shows, it is not. You can have up to 1024 columns in a non-wide table, and you can now have indexes on every one of those columns.
<P>But do you want that many? That's for another post, another time...
<P>Have fun!
<P><FONT color=#ff00ff size=4>~Kalen</FONT></P>Did You Know? I just submitted my last chapter!http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/05/my-last-chapter.aspxFri, 05 Dec 2008 18:38:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10317Kalen Delaney<P>Less than an hour ago, I sent my last chapter of my next book "SQL Server 2008 Internals" to my editor!</P>
<P>Note that I said "my" last chapter, not "the" next chapter, as two of my co-authors still need to complete their chapters. But I've finished my chapters! Now this is still more to do, in addition to the waiting for the other two chapters to arrive. There are author reviews, and some rewrites, and Introduction, etc, to write, but after all these years, I know that stuff is minor.</P>
<P>So it's really going to happen... one more book! </P>
<P>Although <A href="http://www.amazon.com/gp/product/0735626243?ie=UTF8&amp;tag=insqse-20&amp;link_code=as3&amp;camp=211189&amp;creative=373489&amp;creativeASIN=0735626243" target=_blank>Amazon</A> indicates it will be available February 18, it probably will be out in mid-March.</P>
<P>Thanks for all your support.</P>
<P><FONT color=#ff00ff size=4>~Kalen</FONT></P>