http://sqlblog.com/blogs/jamie_thomson/default.aspxThis is the blog of Jamie Thomson, a freelance SQL Server developer in London enCommunityServer 2.1 SP2 (Build: 61129.1)http://feedproxy.google.com/~r/jamiet/~3/Gyrfmh7qqXU/adventureworks-on-azure-now-hosted-by-red-gate-software.aspxMon, 20 May 2013 21:48:39 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49130jamiet0http://sqlblog.com/blogs/jamie_thomson/comments/49130.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=49130http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=49130<p>In <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/27/adventureworks2012-now-available-to-all-on-sql-azure.aspx" target="_blank">March 2013 I launched an initiative called AdventureWorks on Azure</a> in which I hosted the AdventureWorks2012 sample database on Azure so that the SQL Server community at large could use it to get familiar with what <strike>SQL Azure</strike> Windows Azure SQL Database had to offer. I asked the SQL community to support the initiative by donating money to keep it afloat and that community responded in spades to the point where <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2013/04/07/adventureworks-on-azure-donates-gbp351-49-to-war-child-thank-you.aspx" target="_blank">last month I was able to make a donation to the War Child charity</a> on behalf of that community. In the interim period I <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/04/03/big-adventureworks2012.aspx" target="_blank">added more sample data to the pot</a>, <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/04/10/northwind-now-available-on-sql-azure.aspx" target="_blank">made Northwind available</a>, and also data related to <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/11/30/olympics-data-available-for-all-on-windows-azure-sql-database.aspx" target="_blank">Olympics medal tables down the years</a>. </p> <p>Today AdventureWorks on Azure takes on a new guise, Steve Jones of SQL Server Central fame <a href="http://www.sqlservercentral.com/articles/Adventureworks/99290/" target="_blank">announced via a press release</a> that hosting of the database is now to be taken on by his employer <a href="http://www.red-gate.com/" target="_blank">Red Gate Software</a>. Here’s what Steve had to say in today’s announcement:</p> <blockquote> <p><em>One of the goals of Red Gate Software is that we not only sell products to the SQL Server community, but that we also participate in the community. </em><em>In line with our goals of participating in the community, we have agreed to host the AdventureWorks 2012 sample database on the Azure platform. <br /></em><em>Red Gate has worked to transition this set of databases to our own subscription and will assume the financial responsibility for making this available for the community to use. <br /></em><a title="http://www.sqlservercentral.com/articles/Adventureworks/99290/" href="http://www.sqlservercentral.com/articles/Adventureworks/99290/"><em>http://www.sqlservercentral.com/articles/Adventureworks/99290/</em></a></p> </blockquote> <p>This is great news. I no longer have to worry about finding the funds to keep the initiative afloat and the community can feel comfortable that this will be around for the foreseeable future, so thank you to Steve and red gate for this undertaking. Special thanks must also go to David Atkinson at Red Gate who first suggested that they take this on.</p> <p>I have high hopes that Steve &amp; Red Gate will build upon this with more offerings for the community and equally I also hope I can stay involved somehow. Watch this space!</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=49130" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/Gyrfmh7qqXU" height="1" width="1"/>AdventureWorks on Azuresql serverhttp://sqlblog.com/blogs/jamie_thomson/archive/2013/05/20/adventureworks-on-azure-now-hosted-by-red-gate-software.aspxhttp://feedproxy.google.com/~r/jamiet/~3/rCSH300posg/reflections-on-sqlbits-xi.aspxThu, 16 May 2013 22:03:50 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49089jamiet2http://sqlblog.com/blogs/jamie_thomson/comments/49089.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=49089http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=49089<p>Its been a couple of weeks since SQLBits XI happened in Nottingham and I thought I’d jot down a few thoughts for posterity.</p> <p>First the venue. I think its fair to say that the overall consensus was that the <a href="http://www.nottinghamconferences.co.uk/emcc/" target="_blank">East Midlands Conference Centre</a> was the best SQLBits venue these has been so far – I’ve been to more than half of them and based on my experience I would agree with that sentiment. The hotel especially was top quality – I was pleased with my room and the breakfasts were way better than the Travelodge standards I’ve become accustomed to at such events. Perhaps the great weather over the weekend helped lift the spirits but I’d say the bar has been set high, I hope future SQLBits conferences are at similar standard venues.</p> <p>You may have noticed that many of my blog posts over the past year have been related to SSDT and my SQLBits sessions this time around followed that trend. I delivered a session on the Friday, jointly with <a href="http://twitter.com/Craig_Ottley" target="_blank">Craig Ottley-Thistlethwaite</a>, entitled “Real World SDDT” and also delivered my first ever day-long pre-conference seminar on the Thursday entitled “SSDT Database Projects from the ground up”. Delivering a pre-con was slightly petrifying and I spent a large part of the three months previous preparing for it; I hope it was worth it. I had 30 attendees which I was delighted with (especially given this was my first pre-con) and we established that the furthest anyone travelled was from Romania, though I’ll assume it wasn’t just so he could come to my pre-con <img class="wlEmoticon wlEmoticon-smile" style="border-top-style:none;border-left-style:none;border-bottom-style:none;border-right-style:none;" alt="Smile" src="http://sqlblog.com/blogs/jamie_thomson/wlEmoticon-smile_597D8138.png" />. I had some pleasant feedback via Twitter afterwards from some of the attendees and I’m crossing my fingers that the official feedback is in a similar vein.</p> <p>The session I did with Craig went fairly well I thought. I did the first 30minutes where I covered the basics of SSDT deployment from a high level before Craig ratcheted the complexity up a few notches by demoing some interaction between SSDT, MSBuild &amp; Git – really great stuff for those who like to get into the nuts and bolts of this stuff. I was delighted that Craig was willing to do the session with me (in fact it was his idea) as this was his debut public speaking gig and I’m hoping its given him the desire to do more in the future.</p> <p>On the Saturday my wife’s uncle, John Milne, came to the conference. John has been working in customer service for years but of late has decided that he wants a change of career and to that end has been studying an Open University course in IT. He told me he had particularly enjoyed the database-focused modules of his course and hence I suggested he come to the free Saturday of SQLBits to try and get a flavour of what the industry is all about and perhaps learn about some real-world experiences to add to his academic travails. By the end of the day John told me he’s had a fantastic time, learned a lot, and was hooked. Mission accomplished I’d say. John lives in Leeds so I introduced him to Chris Testa-O’Neill who helps to run the Leeds user group and John should be going along to some user group events in the near future – if you happen to meet him there please welcome him into the fold!</p> <p>All that remains for me to say is a massive thank you to the SQLBits committee who do such an amazing job, all voluntarily, in putting this all together. Thank you Simon Sabin, James Rowland-Jones, Chris Webb, Darren Green, Allan Mitchell, Tim Kent, Chris Testa-O’Neill &amp; Martin Bell. I also want to thank all of the volunteer SQLBits helpers that worked tirelessly on the weekend to make sure the whole thing ran smoothly.</p> <p>Bring on SQLBits XII!</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=49089" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/rCSH300posg" height="1" width="1"/>SQLBitshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/05/17/reflections-on-sqlbits-xi.aspxhttp://feedproxy.google.com/~r/jamiet/~3/nlbegYT2sUs/get-the-sqlbits-agenda-on-your-phone-now-and-forever.aspxThu, 25 Apr 2013 20:02:33 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48873jamiet1http://sqlblog.com/blogs/jamie_thomson/comments/48873.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48873http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48873<p>Regular readers of my blog might have realised that I am a huge advocate of subscribable calendars and the data format that underpins them – <a href="http://sqlblog.com/blogs/jamie_thomson/archive/tags/iCalendar/default.aspx" target="_blank">iCalendar</a>. On 8th Feb 2012 I wrote a blog post entitled <a title="http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/08/sqlbits-now-publishing-the-sqlbits-agenda-as-an-icalendar.aspx" href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/08/sqlbits-now-publishing-the-sqlbits-agenda-as-an-icalendar.aspx" target="_blank">SQLBits now publishing all SQLBits agendas as an iCalendar</a> where I told how the SQLBits committee had published the agenda of the forthcoming SQLBits conference in iCalendar format allow with instructions of how one could view the agenda on their phone. Back then I said:</p> <blockquote> <p><em>…any changes to the SQLBits agenda (e.g. a room change) will automatically flow to your own calendar service and if you have that calendar service (e.g. Hotmail Calendar, Google Calendar) synced to your phone then the changes will automatically show up there too … That new SQLBits subscribable calendar lives at </em><a href="http://sqlbits.com/information/SQLBitsCalendar.ashx"><em>http://sqlbits.com/information/SQLBitsCalendar.ashx</em></a><em>; note how it is not specific to a particular conference - subscribe to (don't import) that calendar and the agenda for future SQLBits conferences will automatically flow to you too.</em></p> </blockquote> <p>Sure enough I took a look at the calendar on my phone today and saw this:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_52062B4A.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_7D6A4C44.png" width="288" height="508" /></a></p> <p><em>(That “Real world SSDT” session at 14:40 is being presented by <a href="http://www.sqlbits.com/Speakers/Craig_Ottley-Thistlethwaite" target="_blank">Craig Ottley-Thistlethwaite</a> and myself by the way. Hope to see you there!)</em></p> </blockquote> <p>This is the value of <em>subscribing </em>as opposed to <em>importing</em>. The agenda for next week’s conference has already flowed to my phone without my having to do anything. This isn’t the same phone that I had a year ago either, by subscribing to it in my Outlook.com (nee Hotmail) Calendar those subscriptions are stored and flow onto any new phone as soon as I type in my credentials.</p> <p>I have stated before that I believe subscribable calendars to be a transformative technology and this is why, I only had to subscribe to the calendar once and data that didn’t even exist back then simply flows into my calendar and thus onto my phone. If this interests you then maybe read how I think the same technology could be used to deliver BI data too at <a title="http://sqlblog.com/blogs/jamie_thomson/archive/2010/06/03/thinking-differently-about-bi-delivery.aspx" href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/06/03/thinking-differently-about-bi-delivery.aspx" target="_blank">Thinking differently about BI delivery</a>. And if you want to subscribe to the calendar yourself go and read the aforementioned blog post, that link again: <a title="http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/08/sqlbits-now-publishing-the-sqlbits-agenda-as-an-icalendar.aspx" href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/08/sqlbits-now-publishing-the-sqlbits-agenda-as-an-icalendar.aspx" target="_blank">SQLBits now publishing all SQLBits agendas as an iCalendar</a>.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48873" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/nlbegYT2sUs" height="1" width="1"/>iCalendarSQLBitshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/25/get-the-sqlbits-agenda-on-your-phone-now-and-forever.aspxhttp://feedproxy.google.com/~r/jamiet/~3/1ZWLtw0Lz6k/creating-your-own-sql-snippets-in-ssdt.aspxMon, 22 Apr 2013 21:33:35 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48822jamiet1http://sqlblog.com/blogs/jamie_thomson/comments/48822.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48822http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48822<p>SQL Server Data Tools (SSDT) has a neat feature where you can add snippets into your scripts via the right-click context menu:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_32A460AD.png"><img title="image" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_238D81D3.png" width="597" height="125" /></a></p> <p>I’m finding it very useful indeed. The same feature exists in SQL Server Management Studio (SSMS) as well by the way:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2968256C.png"><img title="image" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6F78E27F.png" width="601" height="108" /></a></p> <p>One thing I really wanted to be able to do was create my own snippets for SSDT and I ventured to the <a href="http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads" target="_blank">SSDT forum</a> to ask if it was possible. Turns out the answer is “yes” and Gert Drapers <a href="http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/a351955d-c33b-4ff1-afca-94cabdeb2aa5" target="_blank">replied to my thread by providing an excellent run through of how to do it</a>. What Gert’s post didn’t quite clarify is that if you follow his instructions then you have to manually edit the .snippet file that you created so that the language is set to SQL_SSDT:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_47663360.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_78856E00.png" width="620" height="155" /></a></p> <p>(well, I had to do that anyway)</p> <p>Once you do that you’ll be able to import the snippet into Visual Studio (Gert’s post shows you how) and thereafter your snippets will show up in the snippets menu:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5072BEE1.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_04A6E828.png" width="504" height="108" /></a></p> <p>Very handy indeed.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48822" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/1ZWLtw0Lz6k" height="1" width="1"/>SQL Server Data ToolsSSDThttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/22/creating-your-own-sql-snippets-in-ssdt.aspxhttp://feedproxy.google.com/~r/jamiet/~3/Lcta46jPA6M/data-warehouse-modelling-deliberations-foreign-keys-and-unknown-members.aspxTue, 16 Apr 2013 21:36:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48738jamiet8http://sqlblog.com/blogs/jamie_thomson/comments/48738.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48738http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48738<p>Earlier today I posted the following question on Twitter:</p> <blockquote> <p><a target="_blank" href="https://twitter.com/jamiet/status/324215986729385986"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="536" height="288" src="http://sqlblog.com/blogs/jamie_thomson/image_7211381A.png"></a></p> <p>Foreign keys in a data warehouse. Yes or no? This discussion is looming at work, i know my position but am interested in what others think. <br>(<a target="_blank" href="https://twitter.com/jamiet/status/324215986729385986">https://twitter.com/jamiet/status/324215986729385986</a>)</p> </blockquote> <p>Specifically, I wanted to know if people were in favour of creating foreign keys from their fact to to their dimension tables or not.</p> <p>To say it prompted a good response is a bit of an understatement, 38 responses so far and counting. Here are some of those responses:</p> <blockquote> <p><a target="_blank" href="https://twitter.com/AdamMachanic/status/324216208721330176"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="410" height="77" src="http://sqlblog.com/blogs/jamie_thomson/image_69AD62C3.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/dykesa/status/324217098333216769"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="412" height="77" src="http://sqlblog.com/blogs/jamie_thomson/image_1ACC9D64.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/sqlslacker/status/324217752577536000"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="412" height="77" src="http://sqlblog.com/blogs/jamie_thomson/image_40C24DBA.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/MarkGStacey/status/324218026532671488"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="413" height="91" src="http://sqlblog.com/blogs/jamie_thomson/image_14A550C9.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/datachick/status/324218360768393216"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="410" height="77" src="http://sqlblog.com/blogs/jamie_thomson/image_77BC2BF3.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/CalvinFerns/status/324219759438749697"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="409" height="77" src="http://sqlblog.com/blogs/jamie_thomson/image_4B9F2F02.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/JakeSmillie/status/324224224019509248"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="410" height="90" src="http://sqlblog.com/blogs/jamie_thomson/image_2AABBC5B.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/snack00/status/324225626850607105"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="409" height="90" src="http://sqlblog.com/blogs/jamie_thomson/image_3BAFEA3E.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/knight_devin/status/324236580464230400"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="410" height="77" src="http://sqlblog.com/blogs/jamie_thomson/image_61A59A94.png"></a></p> </blockquote> <blockquote> <p><a target="_blank" href="https://twitter.com/markiehill/status/324242405341659136"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="411" height="77" src="http://sqlblog.com/blogs/jamie_thomson/image_12C4D535.png"></a></p> <p><a target="_blank" href="https://twitter.com/EyeOfSoreRon/status/324268874054914050"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" width="409" height="90" src="http://sqlblog.com/blogs/jamie_thomson/image_58D59248.png"></a></p> </blockquote> <p>It certainly seems to be an emotive subject and its clear (to me) that there’s no correct answer, just lots of opinions. That’s a good thing. The majority of responders appeared to be of the opinion that a data warehouse <i>should</i> contain foreign keys and that is my position too. In this blog post I want to outline <i>why</i> I believe that one should create foreign keys from a fact table to its dimension tables:</p> <h3>Maintain integrity</h3> <p>Of course, this is the main reason why foreign keys exist – to protect the integrity of your data. I see no reason not to use them for this purpose in a data warehouse. The main argument that I see going against is that with a sufficiently robust ETL solution it shouldn’t be necessary. That is true but I would counter with “how do you know that your ETL solution is suitably robust?” I don’t think its possible to anticipate every eventuality that may arise and for that reason I like the safety net that foreign keys provide. I liked <a target="_blank" href="https://twitter.com/knight_devin/status/324236580464230400">Devin Knight’s response</a> here, foreign keys breed confidence.</p> <h3>Communicate business logic</h3> <p>When I join a project that has a database in place the first thing I do is try and understand the data model – to do that I go and look at the foreign keys in that database. Understanding the dependencies between entities is crucial in any data model and the best means of communicating those is via foreign keys. If I encounter a database that is bereft of foreign keys then my heart sinks a little.</p> <h3>Performance</h3> <p>Foreign keys can, in some circumstances, be beneficial in improving query performance. Take a read of <a target="_blank" href="http://www.sqlservercentral.com/articles/Performance+Tuning/71264/">A Check and Foreign Key Constraint Improves Query Performance</a> by Sarvesh Singh or <a title="http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/" target="_blank" href="http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/">Do Foreign Key Constraints Help Performance?</a> by Grant Fritchey.</p> <h3>Code Generation</h3> <p>I am a big fan of generating ETL code where possible and foreign keys can be invaluable when doing so.</p> <p>&nbsp;</p> <p>As I said there is no correct answer here so if you have any opinions, either agreeing or disagreeing, I look forward to reading your thoughts in the comments below.</p> <hr> <p><a target="_blank" href="https://twitter.com/MarkGStacey/status/324218026532671488">Mark Stacey’s comment</a> prompted an interesting digression into talking about surrogate keys for denoting unknown members and this is something I have strong opinions on too:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ce05180_2CB89557.png"><img title="SNAGHTML1ce05180" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="SNAGHTML1ce05180" width="365" height="720" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ce05180_thumb_5C270423.png"></a></p> <p>In short, I don’t like the practice of using “–1” as the surrogate key for an unknown member. My reasoning is simple, I don’t like giving meaning to something that is supposed to be meaningless. How then should we indicate which is the unknown member? I propose a single-row table that includes a column for each dimension table, each with a foreign key to the unknown member in the respective dimension table.</p> <p>Moreover I don’t like the practice of starting surrogate key counters from 1; <a target="_blank" href="http://msdn.microsoft.com/en-gb/library/ms187745.aspx">the first value available for the integer datatype in SQL Server is –2147483648</a>&nbsp; so why not start from that?</p> <p>I discuss both of these issues in much more depth at <a target="_blank" href="http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/14/considering-surrogate-keys-for-unknown-members.aspx">Considering surrogate keys for Unknown members</a>.</p> <p>Again if you have any thoughts on these subjects please put them in the comments. If nothing else I find it both fun and educational to debate this stuff.</p> <p><a target="_blank" href="http://twitter.com/jamiet">@Jamiet</a></p><p>Update, Chris Adkin posted a comment below that contained a link to Microsoft's own guidance on building datawarehouses where it is stated:</p><p>"<span style="font-family:Verdana, Arial, sans-serif;font-size:11px;"><i>Many physical designs for data warehouses follow the star schema but do not completely specify the relationships between the fact and dimension tables, as mentioned earlier for foreign key constraints, for instance. Without the foreign key constraints explicitly specified, SQL Server must depend on heuristics to detect star schema query patterns.</i></span><span style="font-size:10pt;">"</span></p><p><span style="font-size:10pt;">Chris' take on this:</span></p><p><span style="font-size:10pt;">"</span><span style="font-family:Verdana, Arial, sans-serif;font-size:11px;">So, assuming we are talking about a Kimball DW, there is most definitely value in using foreign key contstraints as this provides a fail safe for the heuristics getting it wrong.</span><span style="font-size:10pt;">"</span></p><p><span style="font-size:10pt;">Thanks Chris.&nbsp;</span></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48738" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/Lcta46jPA6M" height="1" width="1"/>data modellingdata warehousingsurrogate keyshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/16/data-warehouse-modelling-deliberations-foreign-keys-and-unknown-members.aspxhttp://feedproxy.google.com/~r/jamiet/~3/ZZFD8PBOh_o/geoflow-is-that-it-microsoft.aspxSun, 14 Apr 2013 15:42:21 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48673jamiet57http://sqlblog.com/blogs/jamie_thomson/comments/48673.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48673http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48673<p>I didn’t travel to the <a href="http://baconference.sqlpass.org/" target="_blank">SQL Pass Business Analytics conference</a> this week but I keenly followed what was going on via the <a href="https://twitter.com/search?q=%23passbac" target="_blank">#passbac</a> hashtag on Twitter. Seemingly the big announcement was Geoflow Preview for Excel 2013, an add-in for Excel that visualises data over space and time (read more at <a href="http://blogs.office.com/b/microsoft-excel/archive/2013/04/11/public-preview-of-geoflow-for-excel-delivers-3d-data-visualization-and-storytelling.aspx" target="_blank">Public preview of project codename “GeoFlow” for Excel delivers 3D data visualization and storytelling</a>). Geoflow certainly looks compelling at first glance though I must say I found it rather strange that it got top billing given that Microsoft were <a href="http://blogs.codes-sources.com/patricg/archive/2012/11/16/spc12-spc258-geoflow-for-excel-2013-a-new-way-of-exploring-geospatial-data-and-sharing-insights.aspx" target="_blank">talking about it at some SharePoint conference five months ago</a> but nonetheless <a href="http://cwebbbi.wordpress.com/2013/04/11/geoflow-public-preview-available/" target="_blank">the keynote demo was apparently very impressive indeed</a>. Here’s a screenshot of Geoflow:</p> <p><img src="http://blogs.office.com/cfs-filesystemfile.ashx/__key/CommunityServer-Components-PostAttachments/00-00-03-67-64/GeoFlowPublicBeta_5F00_large-version.png" width="832" height="502" /></p> <p>I think Geoflow looks great, I really do; the questions I immediately had about it were:</p> <ul> <li>Can I share my Geoflow’d Excel workbooks and have people view them on SharePoint? Answer: No, there’s no SharePoint collaboration story.</li> <li>Is Geoflow part of Power View? Answer: No, its a separate installation.</li> <li>OK so I have to install it. I presume then that its available in the <a href="http://office.microsoft.com/en-us/store/apps-for-excel-FX102804981.aspx" target="_blank">Office App Store</a> given that’s the new model for distributing Excel add-ins? Answer: No, you have to download it from <a href="http://www.microsoft.com/en-us/download/details.aspx?id=38395" target="_blank">Microsoft’s download site</a>.</li> </ul> <p>So the big reveal from Microsoft at this conference was an Excel add-in that does something very very cool but had already been announced, is only a preview, doesn’t fit with Microsoft’s BI collaboration strategy, doesn’t use their modern distribution platform and isn’t part of their Excel-based data visualisation tool. Well thank god I didn’t stump up the cost of travel, accommodation, loss of income and time away from the family for that! Doubtless there were a lot of other good reasons to go to the conference but I would have been going with high expectations of news from Microsoft that is going to be compelling and help me sell Microsoft’s BI offering to my clients – Geoflow doesn’t do that, not by a long chalk.</p> <hr /> <p>What I was hoping for, nay expecting, was a concrete announcement regarding Microsoft’s mobile BI strategy. We first saw <a href="http://www.youtube.com/watch?v=0vLLAdjGtrU" target="_blank">Power View demonstrated on an iPad</a> at the PASS 2011 conference and I assumed that in the intervening eighteen months they might have built something we could actually install and play around with. Apparently not.</p> <p>Microsoft are getting killed in this area. At my current client all the management folk walk around with iPads glued to their hands – this is the tool on which they consume information and Microsoft doesn’t have anything for them. I was working for a client two years ago that had just invested in a product called <a href="http://www.roambi.com/" target="_blank">RoamBI</a>&#160; because it enabled them to view Reporting Services reports on an iPad. <em>Two years ago</em> for pity’s sake, and Microsoft haven’t released anything mobile-BI-related since!</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48673" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/ZZFD8PBOh_o" height="1" width="1"/>Business IntelligenceMicrosofthttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/14/geoflow-is-that-it-microsoft.aspxhttp://feedproxy.google.com/~r/jamiet/~3/9778LzdtRuM/ssis-gotcha-regional-settings-can-affect-your-expressions.aspxTue, 09 Apr 2013 09:05:33 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48586jamiet7http://sqlblog.com/blogs/jamie_thomson/comments/48586.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48586http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48586<p>I recently stumbled across a nuance of the SSIS expression language which, when you think about, kinda make sense – but it does help to be aware of it. Its concerned with casting of datetime values using the SSIS expression language</p> <p>Take the following expression:</p> <blockquote> <p><font face="Consolas">(DT_WSTR,30) @[System::ContainerStartTime]</font></p> </blockquote> <p>That expression casts a datetime value into a string value. If I evaluate that with my OS Regional Settings set to English (United Kingdom) I see this:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5618F800.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2073BD87.png" width="509" height="181" /></a> </p> </blockquote> <p>If I set my OS Regional Settings to English (United States) I see this:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_115CDEAD.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5E20A543.png" width="508" height="174" /></a> </p> </blockquote> <p>Note how that simple change to the regional settings has caused the result of my expression to change. This could have dangerous consequences; for example, if you are using the result of this expression in a dynamically built SQL statement (as I was) then one of two things will happen, either you will get the wrong result or you’ll get an error. Observe how, n my dynamically built SQL statement, I’m CONVERTing a string literal (which is constructed using the above expression) to a datetime value:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_7CF71921.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_74FF76BF.png" width="420" height="110" /></a>&#160;</p> </blockquote> <p>however with a simple change of my regional settings to English (United States) I see this:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_4CECC7A0.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_52C76B39.png" width="418" height="123" /></a> </p> </blockquote> <p>and when you run that particular SQL statement in SSMS:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2AB4BC1A.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_498B2FF8.png" width="544" height="130" /></a> </p> </blockquote> <p>it blows up!</p> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <p>Definitely one to be aware of! Watch those Regional Settings and their affect on casting of dates in the SSIS expression language!</p> <h3>What should you do instead?</h3> <p>If you need a failsafe way of constructing a date that doesn’t rely on Regional settings then consider something like the following:</p> <blockquote> <p><font face="Consolas">(DT_WSTR,4)YEAR( @[System::ContainerStartTime] ) + &quot;-&quot; + <br />RIGHT(&quot;0&quot; + (DT_WSTR,2)MONTH( @[System::ContainerStartTime] ), 2) + &quot;-&quot; + <br />RIGHT(&quot;0&quot; + (DT_WSTR,2)DAY(@[System::ContainerStartTime] ), 2)</font> </p> </blockquote> <p>That expression will build a date string with format YYYY-MM-DD (which is <a href="http://xkcd.com/1179/" target="_blank">the ISO-ratified unambiguous way of representing a date</a>) regardless of Regional Settings:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0EE91966.png"><img title="image" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_62CC1C74.png" width="530" height="199" /></a> </p> </blockquote> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48586" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/9778LzdtRuM" height="1" width="1"/>expressionssql server integration servicesssishttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspxhttp://feedproxy.google.com/~r/jamiet/~3/jFuuxFhMD6E/adventureworks-on-azure-donates-gbp351-49-to-war-child-thank-you.aspxSun, 07 Apr 2013 21:02:35 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48563jamiet1http://sqlblog.com/blogs/jamie_thomson/comments/48563.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48563http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48563<p>Just over 12 months ago I published a blog post entitled <a title="http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/27/adventureworks2012-now-available-to-all-on-sql-azure.aspx" href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/27/adventureworks2012-now-available-to-all-on-sql-azure.aspx" target="_blank">AdventureWorks2012 now available for all on SQL Azure</a>. In it I explained that I had set up a Windows Azure SQL Database (then known as SQL Azure database) for the SQL Server community to use and hence familiarise themselves with the SQL Azure offering – I called this initiative “AdventureWorks on Azure”. Judging by the comments that were left in the table that I set up for that very purpose it seems that a number of people were in favour and made use of it:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5562FC2B.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_0B184186.png" width="844" height="452" /></a></p> <blockquote> <p><em>The credentials to enable you to connect up and leave your own comment are in that </em><a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/27/adventureworks2012-now-available-to-all-on-sql-azure.aspx" target="_blank"><em>same blog post</em></a><em>!</em></p> </blockquote> <p>Running a SQL Azure database is of course not free hence I asked for the community to support the initiative by <a href="http://www.paypal.com/cgi-bin/webscr?cmd=_xclick&amp;business=adventureworksazure@hotmail.co.uk&amp;item_name=Supporting%20the%20SQL%20community" target="_blank">contributing via PayPal</a> to its upkeep. I hoped that there might be enough left over to make a charitable donation and as you have probably gathered from the title of this blog post I’m delighted to be able to say that that did indeed happen. A few minutes ago I made a contribution to <a href="http://www.warchild.org.uk/" target="_blank">War Child</a> (the same charity that the <a href="http://manning.com/nielsen/" target="_blank">SQL Server MVP Deep Dives book</a> donated to) of £351.49</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_218AE00D.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_607C60A8.png" width="431" height="349" /></a></p> <p>What do War Child do? In their own words:</p> <blockquote> <h3><font color="#c0504d">We look forward to a world in which children's lives aren't torn apart by war.</font></h3> <p><font color="#c0504d">There's 27 of us in an old false-teeth factory in north London. <br />We're trying to change the world.</font></p> <p><font color="#c0504d">You could say we're a small charity with big ambitions. </font></p> <p><font color="#c0504d">We're directly transforming the lives of tens of thousands of children. And we're campaigning to improve the lives of millions more. </font></p> </blockquote> <p>Thank you to everyone that made a donation especially to the extremely generous person (who shall remain nameless – you know who you are) that contributed $105.</p> <p> <hr />I would like to keep AdventureWorks on Azure going for another year at least but to do that I need more donations. If you would like to support this initiative for another year take a read of the instructions below that I have copied from last year’s blog post:</p> <blockquote> <p>AdventureWorks on Azure is being provided for the SQL Server community to use and so I am hoping that that same community will rally around to support this effort by making a voluntary donation to support the upkeep which, going on <a href="https://www.windowsazure.com/en-us/pricing/details/#database/?WT.mc_id=cmp_pst001_blg_post0055pri">current pricing</a>, is going to be $119.88 per year. If you would like to contribute to keep AdventureWorks on Azure up and running for that full year please <a href="http://www.paypal.com/cgi-bin/webscr?cmd=_xclick&amp;business=adventureworksazure@hotmail.co.uk&amp;item_name=Supporting%20the%20SQL%20community">donate via PayPal</a> to adventureworksazure@hotmail.co.uk:</p> <p><a href="http://www.paypal.com/cgi-bin/webscr?cmd=_xclick&amp;business=adventureworksazure@hotmail.co.uk&amp;item_name=Supporting%20the%20SQL%20community"><img src="http://jamiekt.files.wordpress.com/2012/03/paypal_sendmoney.png" /></a></p> <p>Any amount, no matter how small, will help.</p> </blockquote> <p>Thank you once again to everyone that donated!</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48563" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/jFuuxFhMD6E" height="1" width="1"/>AdventureWorks on AzureAdventureWorksAzuresql serverhttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/07/adventureworks-on-azure-donates-gbp351-49-to-war-child-thank-you.aspxhttp://feedproxy.google.com/~r/jamiet/~3/NP6Hmsve9OM/calendar-as-a-canvas-for-getting-things-done.aspxThu, 04 Apr 2013 12:27:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48528jamiet0http://sqlblog.com/blogs/jamie_thomson/comments/48528.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48528http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48528<p>In the same way that a map is a natural choice for displaying data with a geography element to it I’ve long opined that a calendar is a natural choice for displaying data with a time element to it, my main output on this topic is at <a target="_blank" href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/06/03/thinking-differently-about-bi-delivery.aspx">Thinking differently about BI delivery</a>.</p> <p>With that in mind I recently watched an interesting talk by James Whittaker entitled <b>A New Era of Computing</b> where he opined that that the era of <i>searching and browsing</i> for information is dying and we are now moving into an era of <i>doing</i>; its an interesting talk and if you have an hour to spare it might well be worth watching, you can find a video of the talk here: <a href="http://channel9.msdn.com/Events/ALM-Summit/ALM-Summit-3/A-New-Era-of-Computing">http://channel9.msdn.com/Events/ALM-Summit/ALM-Summit-3/A-New-Era-of-Computing</a></p> <p>James gave an example of using a calendar as a canvas for booking a holiday and it really struck a chord with me. In this hypothetical example the steps of finding an appropriate time for a holiday, clearing the decks of all other appointments, finding flights and activities….they were all done within the context of a calendar. There were no 3rd party apps involved, no web pages – the calendar was the canvas upon which all of these tasks were done. To someone like me who strongly believes that calendars are massively underutilized as a means for displaying information the notion that a calendar could also be used to <i>get stuff done</i> was both liberating and illuminating.</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_178BCDAB.png"><img title="image" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="image" width="513" height="290" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_156ECEE2.png"></a> </p> <p>If, like <a target="_blank" href="http://dilbert.com/blog/entry/calendar_as_filter/">Scott Adams</a> and I, you agree that calendars are criminally undervalued in an era of information discovery then you could do a lot worse than spend an hour watching James’ presentation. Really thought-provoking stuff.</p> <p><a target="_blank" href="http://twitter.com/jamiet">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48528" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/NP6Hmsve9OM" height="1" width="1"/>Calendarhttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/04/calendar-as-a-canvas-for-getting-things-done.aspxhttp://feedproxy.google.com/~r/jamiet/~3/NG5g2NQNbzo/detecting-database-drift-using-ssdt.aspxThu, 04 Apr 2013 09:31:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48526jamiet2http://sqlblog.com/blogs/jamie_thomson/comments/48526.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48526http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48526<p>One of the nice things about doing user group presentations is that when you’re putting the presentation together you invariably learn about features that were previously unbeknown to you; so it proved as I stumbled upon SSDT’s database drift detection features while researching material for my forthcoming pre-conference seminar <a target="_blank" href="http://sqlbits.com/information/Event11/SSDT_Database_projects_from_the_ground-up1/TrainingDetails.aspx">SSDT from the ground up</a>.</p> <h3>What is database drift?</h3> <p>You have probably experienced database drift, you just didn’t happen to refer to it as that. More likely you might have spluttered the following, perhaps sprinkled with a few expletives:</p> <ul> <li>“Who put these tables in my database?” </li> <li>“Who changed this view definition?” </li> <li>“Why is this guy in db_owner?” </li> <li>“Where has my stored procedure gone?”</li> </ul> <p>In other words database drift can loosely be described as</p> <blockquote> <p><i>stuff that appears, gets removed, or gets modified in your production databases that perhaps shouldn’t be</i></p> </blockquote> <h3>Detecting database drift using SSDT</h3> <p>If you’re using SSDT to manage your database schema then you probably consider the source code in your SSDT projects to be “the truth” and hence anything that appears in your databases that is not in your source code would be considered database drift.</p> <p>In order to detect database drift using SSDT you must ensure that your database is registered as a Data-Tier Application. This can be done when you publish your database project (i.e. dacpac) by selecting “Register as a Data-tier Application”:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML450c41_2A02E493.png"><img title="SNAGHTML450c41" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML450c41" width="495" height="254" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML450c41_thumb_4800F287.png"></a></p> <p>Thereafter you can check for database drift on subsequent publishes by selecting “Block publish when database has drifted from registered version”:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML46b2db_7BC8E8D8.png"><img title="SNAGHTML46b2db" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML46b2db" width="496" height="254" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML46b2db_thumb_7A844FF9.png"></a></p> <p>If you check that box and database drift has occurred then the publish operation will fail and you see an appropriate message in the Data Tools Operations pane, “Publish stopped. The target database has drifted from the registered version.”:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML4bd20f_5271A0DA.png"><img title="SNAGHTML4bd20f" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML4bd20f" width="461" height="267" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML4bd20f_thumb_78675130.png"></a></p> <p>Clicking the <font color="#0080c0">View Report</font> hyperlink displays the Drift Report which is represented in an XML file:</p> <blockquote> <pre style="font-size:13px;font-family:consolas;background:white;color:black;"><span style="color:blue;">&lt;?</span><span style="color:#a31515;">xml</span><span style="color:blue;">&nbsp;</span><span style="color:red;">version</span><span style="color:blue;">=</span>"<span style="color:blue;">1.0</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">encoding</span><span style="color:blue;">=</span>"<span style="color:blue;">utf-8</span>"<span style="color:blue;">?&gt;</span> <span style="color:blue;">&lt;</span><span style="color:#a31515;">DriftReport</span><span style="color:blue;">&nbsp;</span><span style="color:red;">xmlns</span><span style="color:blue;">=</span>"<span style="color:blue;">http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02</span>"<span style="color:blue;">&gt;</span> <span style="color:blue;">&nbsp; &lt;</span><span style="color:#a31515;">Additions</span><span style="color:blue;">&gt;</span> <span style="color:blue;">&nbsp;&nbsp;&nbsp; &lt;</span><span style="color:#a31515;">Object</span><span style="color:blue;">&nbsp;</span><span style="color:red;">Name</span><span style="color:blue;">=</span>"<span style="color:blue;">[View_1]</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">Parent</span><span style="color:blue;">=</span>"<span style="color:blue;">[dbo]</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">Type</span><span style="color:blue;">=</span>"<span style="color:blue;">SqlView</span>"<span style="color:blue;"> /&gt;</span> <span style="color:blue;">&nbsp;&nbsp;&nbsp; &lt;</span><span style="color:#a31515;">ExtendedProperty</span><span style="color:blue;">&nbsp;</span><span style="color:red;">HostName</span><span style="color:blue;">=</span>"<span style="color:blue;">[View_1]</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">HostParent</span><span style="color:blue;">=</span>"<span style="color:blue;">[dbo]</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">HostType</span><span style="color:blue;">=</span>"<span style="color:blue;">SqlView</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">Count</span><span style="color:blue;">=</span>"<span style="color:blue;">2</span>"<span style="color:blue;"> /&gt;</span> <span style="color:blue;">&nbsp; &lt;/</span><span style="color:#a31515;">Additions</span><span style="color:blue;">&gt;</span> <span style="color:blue;">&nbsp; &lt;</span><span style="color:#a31515;">Removals</span><span style="color:blue;"> /&gt;</span> <span style="color:blue;">&nbsp; &lt;</span><span style="color:#a31515;">Modifications</span><span style="color:blue;"> /&gt;</span> <span style="color:blue;">&lt;/</span><span style="color:#a31515;">DriftReport</span><span style="color:blue;">&gt;</span></pre> </blockquote> <p>In this case a view called [dbo].[View_1] has been added to the target database. That view did not exist in the dacpac that was most recently deployed against the database thus the publish operation fails. Keeping one’s deployed databases as “clean” as possible is something that I am all in favour of so personally I think this is a pretty cool feature.</p> <h3>Generating a drift report from the command-line</h3> <p>The drift report can be generated by the command-line tool <a target="_blank" href="http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx">sqlpackage.exe</a>. To do so you need to define:</p> <ul> <li>the action to be <font face="cons">DriftReport</font></li> <li>a target server &amp; database</li> <li>an output file</li> </ul> <p>&gt;SqlPackage.exe /A:<b>DriftReport</b> /tsn:"<b>(localdb)\Projects</b>" /tdn:"<b>Database1</b>" /op:<b>DriftReport.xml</b></p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML57a506_024C429C.png"><img title="SNAGHTML57a506" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML57a506" width="521" height="127" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML57a506_thumb_361438ED.png"></a></p> <p>As far as I know there is no support for generating a drift report from SQL Server Management Studio (SSMS). I’m hoping that changes so that this feature gets more visibility.</p> <p>&nbsp;</p> <p>If you have any comments stick them in the comments section below!</p> <p><a target="_blank" href="http://twitter.com/jamiet">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48526" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/NG5g2NQNbzo" height="1" width="1"/>Database DriftSQL Server Data ToolsSSDThttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/04/detecting-database-drift-using-ssdt.aspxhttp://feedproxy.google.com/~r/jamiet/~3/kHOVM_5XQZc/ssdt-naming-confusion-cleared-up-somewhat.aspxWed, 03 Apr 2013 07:49:30 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48507jamiet3http://sqlblog.com/blogs/jamie_thomson/comments/48507.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48507http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48507<p>In March 2012 I published <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/04/03/ssdt-what-s-in-a-name.aspx" target="_blank">SSDT - What's in a name?</a> where I lamented the mistakes that Microsoft made in the naming of SQL Server Data Tools.</p> <blockquote> <p><em>…official documentation stating that SSDT includes all the stuff for building SSIS/SSAS/SSRS solutions (this is confirmed in the installer, remember) yet someone from Microsoft tells him &quot;SSDT doesn't include any BIDs components&quot;.</em></p> <p><em>I have been close to this for a long time (all the way through the CTPs) so I can kind of understand where the confusion stems from. To my understanding SSDT was originally the name of the database dev stuff but eventually that got expanded to include all of the dev tools - I guess not everyone in Microsoft got the memo.</em></p> </blockquote> <p>Since then I’ve seen lots of questions pertaining to SSIS/SSAS/SSRS being posted on the <a href="http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads" target="_blank">SSDT forum on MSDN</a> which, frankly, is the wrong place for them.</p> <p>With the release of the <a href="http://www.microsoft.com/en-us/download/details.aspx?id=36843" target="_blank">SSIS/SSAS/SSRS project templates for Visual Studio 2012</a> Microsoft have attempted to clear up the confusion. Matt Masson from the SSIS product team attempts to explain in his usual jovial way:</p> <blockquote> <p><em>note that the component was renamed – we added “Business Intelligence” to the end to distinguish it from the </em><a href="http://msdn.microsoft.com/en-us/data/tools.aspx"><em>SQL Server Data Tools</em></a><em> (Juneau). We now refer to it as SSDTBI, rather than “SSDT, no, not that one, the other one – you know, the one that comes with the SQL installation media, not the one you download”. <br /><a title="http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/" href="http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/">http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/</a></em></p> </blockquote> <p>So to clarify, its now:</p> <ul> <li>SSDT – for building databases</li> <li>SSDTBI – for building SSIS/SSAS/SSRS solutions</li> </ul> <p>Got it? Good!</p> <p>That may all seem slightly confusing but its a darn sight clearer than it was SQL Server 2012 was released over a year ago. And if nothing else you have to be amused with Microsoft’s penchant for ever-lengthening acronyms, I can’t recall ever seeing a six-letter-acronym before, can you?</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48507" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/kHOVM_5XQZc" height="1" width="1"/>SQL Server Data ToolsSSDThttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/03/ssdt-naming-confusion-cleared-up-somewhat.aspxhttp://feedproxy.google.com/~r/jamiet/~3/LPLRBPPqRX0/ssis-2012-deep-dive-presentation.aspxFri, 22 Mar 2013 15:55:10 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48352jamiet1http://sqlblog.com/blogs/jamie_thomson/comments/48352.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48352http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48352<p><em>This is something I’ve been meaning to blog about for ages but it kept slipping my mind, sorry about that!</em></p> <p>Wee Hyong Tok from the SSIS product team has built a slide deck that covers some of the deep secrets about SSIS2012 including:</p> <ul> <li>Catalog deep dive</li> <li>Security</li> <li>Low level monitoring and troubleshooting</li> </ul> <p>The slide deck is available to view online at <a href="https://skydrive.live.com/redir?resid=BB8E1FF2CE0CD545!252&amp;authkey=!AMFavRXK0aVq314">https://skydrive.live.com/redir?resid=BB8E1FF2CE0CD545!252&amp;authkey=!AMFavRXK0aVq314</a>.</p> <blockquote> <p><a href="https://skydrive.live.com/redir?resid=BB8E1FF2CE0CD545!252&amp;authkey=!AMFavRXK0aVq314" target="_blank"><img title="image" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_4BB906A8.png" width="527" height="298" /></a> </p> </blockquote> <p>As its a slide deck its not quite as good as hearing from the presenter himself however there’s still some really useful information in here. If the internals of SSIS float your boat then <a href="https://skydrive.live.com/redir?resid=BB8E1FF2CE0CD545!252&amp;authkey=!AMFavRXK0aVq314" target="_blank">click through</a> and take a look (there are only 22 slides).</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48352" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/LPLRBPPqRX0" height="1" width="1"/>sql server integration servicesssishttp://sqlblog.com/blogs/jamie_thomson/archive/2013/03/22/ssis-2012-deep-dive-presentation.aspxhttp://feedproxy.google.com/~r/jamiet/~3/VACKBiiUpWY/considerations-when-starting-a-new-ssdt-database-project.aspxThu, 21 Mar 2013 10:58:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48327jamiet5http://sqlblog.com/blogs/jamie_thomson/comments/48327.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48327http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48327<p>As you may have realised from much of my blogging over the past year or so I’m an advocate of using SSDT database projects for building database solutions on SQL Server. I have been using SSDT database projects a lot in that time and have come up with a checklist of things to consider when starting a new SSDT database project and I’ll be detailing that checklist below. I strongly advise you to consider making decisions about these items before you even write a scrap of code as invariably it will be more difficult to change later, especially if you have already deployed your database.</p> <hr /> <p>In no particular order here is my checklist:</p> <h3>Folder Structure</h3> <p>By default SSDT will not provide a folder structure for new projects in which to store all your script files so you might want to consider setting one up yourself. I recommend not trying to create a large hierarchy of folders to start with as this will evolve as you go about building your database. Here’s a simple starter for ten:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2ADBA6CA.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_174E4729.png" width="212" height="138" /></a></p> </blockquote> <h3>Filegroups</h3> <p>If you don’t understand the importance of filegroups then read Thomas Larock’s recent blog post <a href="http://thomaslarock.com/2013/01/database-filegroups-just-like-seatbelts-but-with-less-chafing/">DATABASE FILEGROUPS: JUST LIKE SEATBELTS BUT WITH LESS CHAFING</a>. Quite often your organisation will stipulate policies for what filegroups you should be using and I highly recommend that you set up filegroups in your database projects to match those policies sooner rather than later. If your organisation does not have any such policies then perhaps think about defining them yourself, Thomas lists a few considerations that may influence your decisions:</p> <ul> <li>separation of system data from user data </li> <li>larger indexes may benefit from their own filegroup </li> <li>archival of data can benefit from using dedicated filegroups as this will reduce backup maintenance tasks </li> </ul> <p>If you follow my suggested folder structure above then put your filegroups into the “Storage” folder:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2852750C.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_75826E97.png" width="183" height="173" /></a> </p> </blockquote> <p>Above all, specify your default filegroup especially as many DBAs won’t be happy about you putting objects into the PRIMARY filegroup (which is the default). This is done by right-clicking on the project and selecting “Properties..” When there hit “Database Settings…” and set your default filegroup on the “Operational” tab:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ebb40b8_7FD392F7.png"><img title="SNAGHTML1ebb40b8" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="SNAGHTML1ebb40b8" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ebb40b8_thumb_25C9434E.png" width="405" height="146" /></a></p> </blockquote> <p>If you intend to use the filestream feature of SQL Server then you can set the default filestream filegroup here too.</p> <h3>Collation</h3> <p>If you take only one piece of advice from this blog post make it this: <strong>set your collation before you write a single line of code</strong> (just trust me on this, OK). You’ll find this in Project Properties-&gt;Project Settings-&gt;Database Settings-&gt;Common</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ebc6e3e_4BBEF3A4.png"><img title="SNAGHTML1ebc6e3e" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="SNAGHTML1ebc6e3e" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ebc6e3e_thumb_71B4A3FA.png" width="415" height="160" /></a></p> </blockquote> <p>Again, find out if your organisation has any guidance on database collation.</p> <h3>Specify Target platform</h3> <p>SSDT allows you to deploy a project to SQL Server 2005, 2008, 2012 or Azure so it stands to reason that you should specify which you are intending to deploy to. You’ll find this in Project Properties-&gt;Project Settings:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_778F4793.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5AA622BE.png" width="399" height="128" /></a> </p> </blockquote> <p></p> <h3>Default Schema</h3> <p>If you follow the premise that “anything that SQL Server picks as the default is probably wrong” then you should probably think about changing the default schema which, by default, will be [dbo]. SSDT will create all new objects in the default schema (unless otherwise specified).</p> <p>Generally I’m of the opinion that not accepting SQL Server’s defaults is a good idea if only because it forces you to think about these things and be aware of them; in the case of schemas it forces you to think about security and who should have GRANT or DENY permissions and on what.</p> <p>Specify the default schema in Project properties-&gt;Project Settings:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_158D5588.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_791063A7.png" width="428" height="71" /></a> </p> </blockquote> <h3>Review other database settings</h3> <p>I’ve mentioned the most important database settings that you should look to change however you should also glance over the rest of the defaults that SSDT chooses for you to verify that they are valid for your project. Again, these are in Project Properties-&gt;Project Settings-&gt;Database Settings. a few I’d call out as being especially worthy of your attention are:</p> <ul> <li>Auto update stats (on by default) </li> <li>Recovery model (FULL by default) </li> <li>Transaction Isolation (RCSI off by default) </li> <li>Service broker (disabled by default) </li> </ul> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_18530A7B.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6C360D89.png" width="244" height="240" /></a> <br /><a href="http://sqlblog.com/blogs/jamie_thomson/image_5D1F2EAF.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_42DEC58B.png" width="395" height="404" /></a> <br /><a href="http://sqlblog.com/blogs/jamie_thomson/image_289E5C67.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_12684115.png" width="396" height="403" /></a> </p> </blockquote> <h3>Code Analysis</h3> <p>SSDT always checks the syntax of your database code, that’s one of the main justifications for using SSDT database projects. However what it does not do (not by default anyway) is check whether the code you write might be considered <em>good </em>code. That’s what Code Analysis is for, your code gets checked to see if it adheres to well-understood good practices for SQL Server database development. For example, Code Analysis will check for use of “SELECT*” which is generally regarded as a bad thing.</p> <p>Code analysis is turned off by default. I recommend that you head into Project Properties-&gt;Code Analysis and turn it on. Moreover I recommend that you check all the boxes under “Treat Warning as Error” – this forces developers to address issues that get raised by Code Analysis.</p> <p></p> <p></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0D195A64.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2B839B4D.png" width="437" height="255" /></a> </p> </blockquote> <h3>Big Labels</h3> <p>Add a banner to your Pre-Deployment script as I describe in <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/09/26/big-label-generator.aspx" target="_blank">Big label generator</a>. This may seem rather unnecessary but it takes about 30 seconds and believe me, at some point you’ll be glad you did!</p> <blockquote> <p><img src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2A99351D.png" width="619" height="334" /></p> </blockquote> <p>&#160;</p> <p>The demo project that I took some of the above screenshots from is downloadable from <a title="http://sdrv.ms/ZW0gNt" href="http://sdrv.ms/ZW0gNt">http://sdrv.ms/ZW0gNt</a> and might provide a useful template for your own SSDT database projects.</p> <hr /> <p>If you have any more suggestions for my checklist please add them to the comments below!</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48327" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/VACKBiiUpWY" height="1" width="1"/>DatadudeSQL Server Data ToolsSSDThttp://sqlblog.com/blogs/jamie_thomson/archive/2013/03/21/considerations-when-starting-a-new-ssdt-database-project.aspxhttp://feedproxy.google.com/~r/jamiet/~3/J5u4PbbvSQk/connected-development-in-ssdt-versus-ssms.aspxTue, 19 Mar 2013 16:28:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48314jamiet11http://sqlblog.com/blogs/jamie_thomson/comments/48314.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48314http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48314<p>When you install the database projects template of SSDT you get SQL Server Object Explorer (SSOX) installed as well. SSOX is a pane within Visual Studio and is the main enabler of the Connected Development experience that the SSDT team have attempted to provide.</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML15dc3f62_18DB391E.png"><img title="SNAGHTML15dc3f62" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML15dc3f62" width="335" height="118" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML15dc3f62_thumb_0C6D15F5.png"></a></p> <p>SSOX provides some really cool capabilities that are not in SQL Server Management Studio (I hope to blog about them in the near future). In theory these capabilities make it possible for a database developer to spend all their time in SSDT (i.e. Visual Studio) thus making SSMS a pureplay DBA tool (this does of course depend on your definition of both a database developer and a DBA, but I’m not getting into that debate here).</p> <p>With that in mind I have spent a few days trying to work without SSMS, preferring to live wholly inside Visual Studio instead. By and large I was able to do everything I needed to do from within Visual Studio however there were a few nuances about the experience that kept pushing me back to SSMS, I detail those nuances below.</p> <p>&nbsp;</p> <hr> <h3>Server groups</h3> <p>SSOX combines the functions of SSMS’s Object Explorer and Registered Servers pane. I don’t mind either way of working but it does mean that there is no ability to group servers in SSOX like you can in the Registered Servers pane</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_568820DA.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" width="244" height="97" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_4E907E78.png"></a>&nbsp;<a href="http://sqlblog.com/blogs/jamie_thomson/image_0977B142.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" width="244" height="230" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6C8E8C6C.png"></a> </p> <h3>F6</h3> <p>In SSMS I regularly use the F6 keyboard shortcut to jump between the query, results &amp; messages panes of a query window. No such keyboard shortcut exists in SSDT and they’ve already canned <a target="_blank" href="https://connect.microsoft.com/sqlserver/feedback/details/780990/ssdt-f6-to-move-between-panes-in-a-query-window#tabs">my request on Connect to get this fixed</a> (even though it laughably has status “closed as fixed”).</p> <p>&nbsp;<i>UPDATE: See the comments below where Brett Gerhardi informed me of a different keyboard shortcut that does the same thing as F6. Actually its not quite the same, if you have multiple resultsets in your results pane then the behaviour is slightly different to F6 in SSMS - but that's not an issue you'll hot frequently.</i></p> <h3>Change Connection</h3> <p>The context menu in SSMS provides the ability to change a connection as well as connect and disconnect:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_72693005.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" width="546" height="115" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_11ABD6D9.png"></a> </p> <p>SSDT doesn’t have change connection and believe me, you don’t know how much you use a feature until its not there:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_09B43477.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" width="438" height="58" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_28F6DB4A.png"></a> </p> <p>There’s also no hotkey to jump to “Connection” on the context menu like there is in SSMS (“C”) and I find that annoying too.</p> <p>&nbsp;</p><hr>Those were the main annoyances that forced me back to SSMS. The lack of F6 was a major bugbear for me as I am a big keyboard shortcut junkie. If such things don’t bother you then you may be able to live in Visual Studio quite happily. If you have any similar experiences to share I’d be keen to read them.<p>&nbsp;</p> <p><a target="_blank" href="http://twitter.com/jamiet">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48314" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/J5u4PbbvSQk" height="1" width="1"/>sql serverSQL Server 2012SQL Server Data ToolsSQL Server Object ExplorerSSDTSSOXVisual Studiohttp://sqlblog.com/blogs/jamie_thomson/archive/2013/03/19/connected-development-in-ssdt-versus-ssms.aspxhttp://feedproxy.google.com/~r/jamiet/~3/t973gXAU6O8/sp-ssiscatalog-v1-0-3-0-available-now-ssis.aspxFri, 15 Mar 2013 22:19:07 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48269jamiet0http://sqlblog.com/blogs/jamie_thomson/comments/48269.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48269http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48269<p>I’ve just put out a new version of sp_ssiscatalog. The main change is requesting a list of executions using:</p> <blockquote> <pre style="list-style-type:disc;font-family:;background:white;color:;text-align:left;"><font face="Consolas"><span style="color:;"><font color="#0000ff" size="4"><strong>exec</strong></font></span><font size="4"><strong> sp_ssiscatalog<span style="color:;"><font color="#0000ff">&#160;</font></span>@operation_type<span style="color:;"><font color="#808080">=</font></span><span style="color:;"><font color="#ff0000">'execs'</font></span></strong></font> </font></pre> </blockquote> <p>returns a new field called relative_duration:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_6D51F050.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_60E3CD27.png" width="709" height="330" /></a></p> </blockquote> <p>This length of the “bar” in that field represents the duration of that execution relative to all the other executions in the resultset. Put more simply, its an attempt to simply visualise which executions took the longest to complete.</p> <p>The other change in this release is the addition of a new parameter called @ot which is simply a short form of @operation_type. Its there because I basically got fed up of typing @operation_type every time I wanted a list of executions. hence the statement above can now be written as:</p> <blockquote> <p><font face="Consolas"><span style="color:;"><font color="#0000ff" size="4"><strong>exec</strong></font></span><font size="4"><strong> sp_ssiscatalog<span style="color:;"><font color="#0000ff">&#160;</font></span>@ot<span style="color:;"><font color="#808080">=</font></span><span style="color:;"><font color="#ff0000">'execs'</font></span></strong></font> </font></p> </blockquote> <p>That’s it, you can download the latest bits from <a title="http://ssisreportingpack.codeplex.com/releases/view/103452" href="http://ssisreportingpack.codeplex.com/releases/view/103452">http://ssisreportingpack.codeplex.com/releases/view/103452</a>. Installation instructions are in my previous blog post on sp_ssiscatalog <a title="http://sqlblog.com/blogs/jamie_thomson/archive/2013/03/12/sp-ssiscatalog-v1-0-2-0-now-available-for-download.aspx" href="http://sqlblog.com/blogs/jamie_thomson/archive/2013/03/12/sp-ssiscatalog-v1-0-2-0-now-available-for-download.aspx" target="_blank">sp_ssiscatalog v1.0.2.0 now available for download [SSIS]</a>.</p> <p>Look for more updates coming soon, I’m hoping to pick up the pace of iterations on this.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48269" width="1" height="1"><img src="http://feeds.feedburner.com/~r/jamiet/~4/t973gXAU6O8" height="1" width="1"/>sp_ssiscatalogssishttp://sqlblog.com/blogs/jamie_thomson/archive/2013/03/16/sp-ssiscatalog-v1-0-3-0-available-now-ssis.aspx