tag:blogger.com,1999:blog-17894350946294749882024-02-19T14:32:20.794+00:00Musings on Data, Data Management and SQL ServerDave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.comBlogger43125tag:blogger.com,1999:blog-1789435094629474988.post-48392303343828952952017-09-12T21:19:00.000+01:002017-09-12T21:39:37.601+01:00Using PowerShell to drive ETL<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUhqyY4_ZlD340vRngZm7qg4yMO8SdKNfMxv31GFRIgzv7DbNVif5H22evIxxLQZGQ36_glh9kzDINy1DEk4nEw-uTDgpWaZkCgT3Ef1x8jwaZWiNP14exxI75yyqpw3hhEHXHahe48B56/s1600/TSQL2sDay150x150.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="150" data-original-width="150" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUhqyY4_ZlD340vRngZm7qg4yMO8SdKNfMxv31GFRIgzv7DbNVif5H22evIxxLQZGQ36_glh9kzDINy1DEk4nEw-uTDgpWaZkCgT3Ef1x8jwaZWiNP14exxI75yyqpw3hhEHXHahe48B56/s1600/TSQL2sDay150x150.jpg" /></a></div>
<div dir="ltr">
<span style="background-color: white; font-family: "trebuchet ms" , "trebuchet" , "verdana" , sans-serif; font-size: 13.2px;">You may be aware of T-SQL Tuesday; it's a concept conceived by Adam Machanic ( </span><a href="http://sqlblog.com/blogs/adam_machanic/default.aspx" style="background-color: white; font-family: "Trebuchet MS", Trebuchet, Verdana, sans-serif; font-size: 13.2px; text-decoration-line: none;" target="_blank">b</a><span style="background-color: white; font-family: "trebuchet ms" , "trebuchet" , "verdana" , sans-serif; font-size: 13.2px;"> | </span><a href="http://www.twitter.com/Adam_Machanic" style="background-color: white; font-family: "Trebuchet MS", Trebuchet, Verdana, sans-serif; font-size: 13.2px; text-decoration-line: none;" target="_blank">t</a><span style="background-color: white; font-family: "trebuchet ms" , "trebuchet" , "verdana" , sans-serif; font-size: 13.2px;"> ) some years ago, to encourage a number of posts on a different specific topic on a monthly basis. This month's topic, as hosted by Rob Sewell ( <a href="https://sqldbawithabeard.com/" target="_blank">b</a> | <a href="https://twitter.com/SQLDBAWithBeard" target="_blank">t</a> ), is <a href="https://sqldbawithabeard.com/2017/09/05/tsql2sday-94-lets-get-all-posh" style="color: #666666;" target="_blank">Let's All Get POSH</a></span><span style="background-color: white; color: #666666; font-family: "trebuchet ms" , "trebuchet" , "verdana" , sans-serif; font-size: 13.2px;">.</span></div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
For some time I've used SSIS to move data from one place to another, as part of a wider ETL strategy. Recently however, I've faced some challenges to this model, and have been using PowerShell as an alternative.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
So firstly, here's some of the challenges I've faced:</div>
<div dir="ltr">
<br /></div>
<h3>
Support for the multiple data formats I need to connect to</h3>
<div dir="ltr">
SSIS is a Microsoft product, and therefore focused on manipulating data within the Microsoft environment. It does attempt to interact with other systems, for example by use of ODBC to allow connection to read data, but this is not always as successful as might be hoped.</div>
<div dir="ltr">
Flat files, and Excel files etc. are great, as long as the file format remains the same. Increasingly however, the data files we are loading are changing in format, and incorporate factors such as multiple line fields which SSIS requires a lot of work to utilise properly.</div>
<div dir="ltr">
<br /></div>
<h3>
Programmability and support</h3>
<div dir="ltr">
SSIS uses VB.Net or C# programming to provide its interactivity, and this does provide a level of flexibility. That said, it can be tricky to get the right level of function whilst not making the solution so fragile that it is an active barrier to change and presents a problem for support teams. </div>
<div dir="ltr">
<br /></div>
<h3>
Testing</h3>
<div dir="ltr">
I like to know that what I'm deploying works as I expect it to. When making changes, I'd like to know I've not created any problems whilst I've been doing it. This means testing, and ideally in an automated way, so it's repeatable whenever a change to the system is required. However, I've not found a good way to unit test within SSIS, and this means that there is a testing overhead, or a larger risk to accept whenever there is a change to deploy.</div>
<div dir="ltr">
<br /></div>
<h3>
A possible replacement</h3>
<div dir="ltr">
Well, the modular approach of PowerShell can give an ability to reuse smaller aspects of the system to allow you to create re-usable building blocks, and assemble them in any order, in a similar way to the tools you might have used in SSIS to interact with different DBMS systems.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
PowerShell also has a unit testing framework (Pester) built in, which allows us to check each building block works as expected. </div>
<div dir="ltr">
PowerShell modules are developed by the community and benefit from the collective experiences of all those contributors. This also means that we have the things that those contributors wanted - and for ETL, that often means an element of automatic discovery of the structure of data sources, whilst allowing a manual specification where that is helpful.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
PowerShell is increasingly used in the Windows arena, lending to increased knowledge of the language for support purposes.</div>
<div dir="ltr">
<br /></div>
<h3>
A flexible way forwards</h3>
<div dir="ltr">
So - how can I use this to get a solution to tackle the above limitations, and generate a flexible solution?</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
Well, we have built a number of functions to read from, and write to, the specific DBMSs that we interact with most - Microsoft SQL Server, MariaDB, Excel and flat files. These allow a simple read from, and write to, of each of the DBMSs. To introduce flexibility, we pull in the query to run as a parameter to each function.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
These functions can then be called, in whatever order is required, to read a query from a configuration file, run it against a given DBMS to read data, and store it in a PowerShell object. Any necessary manipulation can be done at this point, before the file is then written into the target DBMS. The process can then read a second query from a configuration file and use it to perform any required transformation - for example to write into a destination file. This allows us to take either an ETL or ELT approach (transforming either after extract, or after load.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
This use of functions have allowed us to test each one works in isolation, and now we have a set of proven functions, we can then re-use these building blocks, to repeatedly move data, whether the data is in the same format, or needs manipulation between the systems.<br />
<br />
As an unintended benefit, the modular approach allowed a distribution of the development work among colleagues, to a defined way of working, providing a useful focus to colleagues learning to use PowerShell.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
From a support point of view, the use of a standardised language understood by other teams such as DevOps, as well as operation support teams, means that we have more confidence that issues can be resolved promptly should any issues occur.</div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-27505156776497803412017-07-30T11:01:00.001+01:002017-07-30T11:01:36.672+01:00Keeping track of databasesIn most production settings, you end up with a number of databases. Some conform to a naming scheme - perhaps a name of a well used application, perhaps according to customer, perhaps according to some internal identifier. However, in most cases, the current scheme has not always been in place, and there's a collection of unknown databases (or ones that simply don't fit the pattern), which don't cause much trouble, but simply sit there, waiting to trip up the person who has rarely if ever interacted with that database, but is charged with supporting it.<br />
<br />
As regulations such as the EU's <a href="https://ico.org.uk/for-organisations/data-protection-reform/overview-of-the-gdpr/" target="_blank">GDPR</a> come into focus, we are increasingly called to account for all the data we hold, particularly if it is personally identifiable. What this means, is we need to be able to identify what's in these 'miscellaneous' databases, and to what they relate. Frankly, it's a good practice anyway, so you can better manage the data that you hold. It's also helpful for maintenance work to understand the effect if you take a database instance offline; even if your organisation is a 24x7 operation, you will probably have databases that don't need to be online all the time - for example the payroll database (as the team who look at this only work in the day). You may have varying definitions of 'day', particularly if you cross timezones.<br />
<br />
The question then is, how to identify what these systems relate to. You may have a documentation source for each database (I often find ours to be regrettably out of date), but even if this is in date, it is designed to be looked at for a specific system, so this still means trawling lots of documentation to find out what we want to know in this case - and the documentation doesn't automatically update as databases move.<br />
<br />
The solution I've started to use is <a href="https://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx" target="_blank">extended properties on the database</a>. These are a place to put an <a href="https://docs.microsoft.com/en-us/sql/t-sql/data-types/sql-variant-transact-sql" target="_blank">sql variant</a> value (which can hold text or numbers for example) properties of an entity, and which forms part of that object and can be scripted out with it - so they can be source controlled. They move with the database, so as it is backed up / moved, the properties stay with the object to which they relate.<br />
<br />
So why doesn't everyone do this? Well, firstly, it's a schema change. This is a blocker in some organisations, or for some supplier-supported systems. I've found that asking, and explaining why, goes a long way here. Secondly, there's the question of how to use them.<br />
<br />
Extended properties can be viewed in the SQL Server Management Studio GUI, however this is clunky at best, and I tend to use scripts to manipulate them, to ensure consistency.<br />
<br />
Extended properties can be added, edited, or deleted using <a href="https://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx#Anchor_35" target="_blank">system stored procedures</a>. To read them, a function (<a href="https://technet.microsoft.com/en-us/library/ms179853(v=sql.105).aspx" target="_blank">fn_listextendedproperty</a>) is called, or a <a href="https://technet.microsoft.com/en-us/library/ms186989(v=sql.105).aspx" target="_blank">system view</a> is available. The key problem here is how to read from all the databases you have in an estate, both within an instance and across multiple instances.<br />
I use policy based management, with a check that looks for the specific extended property I use, to gather this information, but you could equally well query this from Powershell, <a href="https://blogs.msdn.microsoft.com/buckwoody/2010/07/20/powershell-and-extended-properties/" target="_blank">as a property on the database object</a> as outlined by Buck Woody.<br />
<br />
Using properties like this mean you can also tie to other data you might have - like who is responsible for which systems in the wider business - to give a list of people to notify about potential issues to database instances, which can be updated without updating lots of documents; this means it's more likely to be up to date, when you need it most.<br />
<br />
This list of databases, purposes and potentially business owners may also be helpful to your information assurance & governance teams, as they look to ensure they have a good understanding of what databases you hold, and why.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-646121737997255402015-10-23T06:14:00.000+01:002015-10-23T06:14:21.702+01:00Always check the basics - backupsAre your databases being backed up? Can you restore from them? Are you sure?<br />
<br />
SQL Server has a built in way to check when the database was last backed up. You can access this data in a couple of ways:<br />
<br />
Via T-SQL - query the sys.databases table. You can identify the last time the database had a full, differential or transaction log backup<br />
<br />
You can also use a <a href="http://blog.dgta.co.uk/2015/09/strategies-for-managing-enterprise.html">Policy Based Management</a> policy to do this - I use this to at least ensure that each database has a full or differential backup from within the last day, and as a prompt to act if it doesn't.<br />
<br />
You can of course use a number of means to back up, but the only way of testing the backup properly is to try restoring it. By running a restore you check the backup, and of course the backup chain in the case of transaction log backups.<br />
<br />
It's only by testing the restore that you can be sure that the backup is any use - and that your data is safe. So the follow on process is to ensure that you run consistency checks (<a href="https://msdn.microsoft.com/en-us/library/ms176064.aspx" target="_blank">DBCC CHECKDB</a> in SQL Server) on the restored version.<br />
<br />
Now we know:<br />
<br />
<ul>
<li>The database was backed up</li>
<li>It can be restored to when the backup was taken</li>
<li>The restored database is not corrupt</li>
<li>By implication, the original database wasn't corrupt when the backup was taken</li>
</ul>
<div>
This means it is a backup that you, and your business can rely on.</div>
<div>
<br /></div>
<div>
The next step is to work with your backup software to automate this process, so you can be sure that the backup meets this criteria each time it runs.</div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-63774401826465120482015-09-08T21:57:00.000+01:002015-09-08T21:57:01.709+01:00Strategies for managing an enterprise - Policy Based Management (T-SQL Tuesday)<div class="" style="clear: both; text-align: center;">
<a href="http://www.midnightdba.com/Jen/2015/09/time-for-t-sql-tuesday-70/" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;" target="_blank"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj62HHf49cMrqkG6sI2wQfrUs6PCf4BGRhvBSolGpc3Qdy5fMYJSHmsI8hiSZcdvlkyM36Zgh3_fHBLwgRBxBvNGbKChq7dYRd3vEFs-08ckXm45eMVOiH_pGm9K9936WT2_qN53Woo8CUR/s1600/TSQL2sDay150x150.jpg" /></a></div>
<div style="text-align: left;">
You may be aware of T-SQL Tuesday; it's a concept conceived by Adam Machanic ( <a href="http://sqlblog.com/blogs/adam_machanic/default.aspx" target="_blank">b</a> | <a href="http://www.twitter.com/Adam_Machanic" target="_blank">t</a> ) some years ago, to encourage a number of posts on a different specific topic on a monthly basis. This month's topic, as hosted by Jen,McCown, is <a href="http://www.midnightdba.com/Jen/2015/09/time-for-t-sql-tuesday-70/" target="_blank">Strategies for managing an enterprise</a>.<br />
<br />
A Strategy is <i>a plan of action designed to achieve a long term goal or aim</i>, so I thought I'd write about how I'd planned and achieved a solution to the problem of assuring myself and the business that our SQL servers met the policies and best practices we aspired to achieve.</div>
<br />
<div class="" style="clear: both; text-align: left;">
I had recently been faced with the need to gain confidence in the state of a set of SQL Servers whose management hitherto has at best been a part time activity, and not owned by any one person or group. This had meant that, understandably, consistency and best practice have suffered, to the extent that a base level of audit and assurance is required. There's a few ways to achieve this, and I needed to come up with a strategy that would hit several goals.</div>
<div class="" style="clear: both; text-align: left;">
<u><br /></u></div>
<div class="" style="clear: both; text-align: left;">
<u>Easy to implement</u></div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
Technically advanced technologies are great, but sometimes you just need basic checks and the ability to build on the basis they provide. In our case, we needed to use the existing skills base to achieve our goal.</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
<u>Automated</u></div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
I didn't have the luxury of assuming there will be a full time DBA in the organisation in the future, and in any case we are always pushed to do more with less resource, so it is likely that any solution that involves manual steps will not be carried out regularly. We therefore needed an automated solution.</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
<u>Visible</u></div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
For any audit tool to be effective, particularly a tactical one, we needed to be able to provide a level of reporting, to allow the business to perceive the health of the assets, and have confidence that issues are addressed.</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
<u>Comprehensive</u></div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
There's no point in having a solution that doesn't cover all the bases. We needed one that will allow us to expand on the 'out of the box' functions to do custom metrics.</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
<u>Cost effective</u></div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
There are a lot of valuable solutions out there that really do provide great monitoring for large server estates, allowing real-time automated resolution of issues, and confirmation of policy adherence. Our needs were more modest however, and it should be noted that the goal was to achieve the monitoring without spending out on licences; this is something we can aspire to once the base level of audit is in place.</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
So once these goals are defined, I looked at some options. Third party solutions like <a href="https://www.nagios.org/" target="_blank">Nagios</a>, which is a great monitoring tool I've used in the past, were not an option due to existing skills bases. I needed to use something that would use existing developer or administrative skillsets. I explored <a href="https://msdn.microsoft.com/en-us/library/bb510667.aspx" target="_blank">Policy Based Management</a> (PBM) in SQL server, and decided this was a good avenue because it allowed simple best practices to be easily monitored, in some cases bad practices prevented, and could be easily rolled out to the enterprise via a <a href="https://msdn.microsoft.com/en-us/library/bb934126.aspx" target="_blank">central management server</a>. </div>
<div class="" style="clear: both; text-align: left;">
<br />
Using PBM, you can write policies that prevent some behaviours, but due partly to existing codebases, and in some cases third party products that we can't modify, as well as a relatively relaxed regulatory framework, I've chosen to alert rather than prevent. I can write conditions that only target certain server classes, and have taken to using extended properties on databases to denote whether certain conditions apply (and on the master database to denote server environment characteristics, such as Production/UAT/Dev). This gives the flexibility to write one policy for a condition and have it apply wherever an applicable system is hosted in the enterprise.<br />
<br /></div>
<div class="" style="clear: both; text-align: left;">
This setup was still missing one part however - the visible bit. By default, when you have a non-compliant server, all you get is an icon in SQL Server Management Studio, and an error code fired which means you can alert via an event. This isn't even very visible if you know what you're looking for - and doesn't tell you what's wrong.<br />
<br />
The solution was to use the <a href="https://epmframework.codeplex.com/" target="_blank">Enterprise Policy Management Framework</a>, a combination of Powershell and SSRS to query an estate of servers and present the results in a report, which could be displayed centrally (think intranet, or screen in the management office), or scheduled and circulated like other SSRS reports, and is simple to visually check on a regular basis - a screen full of red is bad!</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
Did it work? Certainly, and it allowed identification of issues such as backups being missed off the schedules, security permissions which were against policy, orphaned users and the like. This allowed the strategy to achieve the aim of giving confidence in the level of servers across the enterprise.</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
There are some areas for further thought, not least in terms of the security permissions required to run reports, which are necessarily high - however any system which monitors areas such as sensitive configuration will require a comparatively high level of access. I'm also looking at modifying some of the reports to better fit our needs, but this is a minor change given the data is now in place.</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
I found that adopting a strategy of implementing a monitoring solution to check the basics are in place has given me the confidence that we have a firmer base to build on. The strategies and goals they aim to achieve will evolve, as they all should, as time is freed up and the needs of the business dictate. I've not given up on implementing a more integrated monitoring solution in the longer term, but this is a great way of assuring the business that the service we provide is fit for purpose, and can be relied upon.</div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-8123750785299127912015-07-20T19:31:00.002+01:002015-07-20T19:31:28.234+01:00Solving data problems cost effectivelyI believe that most bad data can be categorised as due to one of:<br />
<br />
<ul>
<li>Human data entry issues</li>
<li>Bad processing of data we hold</li>
<li>Corruption of data in storage</li>
</ul>
<div>
The last of these is a longer topic, and whilst there are mitigation strategies they are mostly a cost / spending exercise to achieve the required level of risk reduction. Entry issues have a few mitigation strategies - double entry can be used (principally used historically in finance audit systems) to prevent typos, but in an increasingly real-time data collection this is less practical. However what can be used are methods such as field validation rules, and repeating back to the user related data, to check the details. An example might be to look up the postcode a user entered and present a selection of valid addresses (assuming you have a source of these) for that post code. Thus, if an incorrect postcode has been entered the mistake should be obvious immediately, allowing it to be checked. Another method would be to indicate the location on a map. The method of checking will depend on the way that we have of communicating with the person from whom the data is collected. Part of the goal here is not only to ensure that our data is not erroneous, but that we do actually have the correct data. If you think of a scenario where we are collecting data from people in Plymouth, who will all have a post code starting PL... then if a users enters a post code starting PO.. (Portsmouth) then this is clearly incorrect - however it doesn't tell us what the correct post code actually is. If looking at questions of a more view based type, it may be possible to ask a similar question in two ways and cross check the answers.</div>
<div>
<br /></div>
<div>
Once we have the correct data then we need to ensure the correct processing of it, and it is here that the issues can get costly. The cost of getting data is small compared to the cost of getting it *again* if that data is lost - not only is there a monetary cost but also a cost to reputation. So this implies that we need to ensure our processing of data is valid. The best defence we have against the human quality of fallibility is testing, and more generally quality control. I would suggest that any software (most data processing is now done in software, but the principles can still be applied to manual processes) that is not meeting a basic quality standard should not be judged fit for use. Such standards, whilst defined to meet your project / organisational needs, should consider:</div>
<div>
<ul>
<li>Have we identified the requirement this fulfils?</li>
<li>Does the documentation explain how we fulfil it?</li>
<li>Does the testing check all aspects of the requirement?</li>
<li>Does the testing check for entry of unusual data?</li>
<li>Does the unit of code work well on it's own?</li>
<li>How about if we put it within the overall system?</li>
<li>Is it maintainable (i.e. according to policies, design standards, etc.)?</li>
<li>Has someone else code-reviewed it, and the documentation and test, to check for errors?</li>
</ul>
<div>
The last point is important because a test failure would simply imply that the results didn't match what the test expected - it is up to the requirements documentation (or business representative) to arbitrate as to which is incorrect, the test or the code. I believe that all of these are valid and should form your definition of when code is "done" however you develop your solution - a traditional waterfall approach, an Agile methodology, or a more ad-hoc process. By catching these issues before a product is released, and actively processing real data, we remove the costs associated with loss of data, or correcting for accuracy problems which result from our processing.</div>
</div>
<div>
<br /></div>
<div>
Whilst we have above covered some basics this is clearly a much broader topic; it amazes me that I still see organisations who do not have robust processes to ensure their data processing does not only meet the legal obligations such as the Data Protection Act (such as Principal 4, accuracy), but also avoid compiling information inaccurately and therefore leave themselves open to making decisions on a flawed set of data, potentially invalidating the decision, and risking significant financial and reputation damage.</div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-35895320814970206232015-04-14T19:41:00.000+01:002015-04-14T19:41:30.943+01:00Information Vs Data Vs SecurityA subject that has been close to my mind recently, is as to when data becomes information, and how the security of that asset is managed - and by whom.<br />
<br />
We have probably all seen the triangle:<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://upload.wikimedia.org/wikipedia/commons/0/06/DIKW_Pyramid.svg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="http://upload.wikimedia.org/wikipedia/commons/0/06/DIKW_Pyramid.svg" height="249" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">By Longlivetheux (Own work) [CC BY-SA 4.0 (http://creativecommons.org/licenses/by-sa/4.0)], via Wikimedia Commons</td></tr>
</tbody></table>
<br />
Which as you will gather, denotes that Information is comprised of (useful sets of) Data. There's also the concept, at least in UK law, of data, which comprises of Information:<br />
<br />
<blockquote class="tr_bq" style="box-sizing: border-box; font-family: Verdana, Helvetica, Arial, sans-serif; font-size: 15.1999998092651px; line-height: 21.2799987792969px; margin-bottom: 1em;">
<strong style="box-sizing: border-box; font-family: Verdana, Helvetica, Arial, sans-serif; font-size: 15.1999998092651px; line-height: 21.2799987792969px;">Data</strong><span style="font-family: Verdana, Helvetica, Arial, sans-serif; font-size: 15.1999998092651px; line-height: 21.2799987792969px;"> </span><span style="font-family: Verdana, Helvetica, Arial, sans-serif; font-size: 15.1999998092651px; line-height: 21.2799987792969px;">means information which –</span>(a) is being processed by means of equipment operating automatically in response to instructions given for that purpose,<br />
(b) is recorded with the intention that it should be processed by means of such equipment,<br />
(c) is recorded as part of a relevant filing system or with the intention that it should form part of a relevant filing system,<br />
(d) does not fall within paragraph (a), (b) or (c) but forms part of an accessible record as defined by section 68, or<br />
(e) is recorded information held by a public authority and does not fall within any of paragraphs (a) to (d).</blockquote>
<span style="font-size: x-small;"><a href="https://ico.org.uk/for-organisations/guide-to-data-protection/key-definitions/">https://ico.org.uk/for-organisations/guide-to-data-protection/key-definitions/</a></span><br />
<br />
So it is clear that these terms are not defined exclusively, and that the terminology alone may present a problem.<br />
<br />
The business often has a nominated <a href="https://www.gov.uk/service-manual/making-software/information-security.html#information-asset-owner" target="_blank">Information Asset Manager</a>, who looks after the types of information an organisation processes, and is responsible (perhaps to a <a href="https://www.gov.uk/service-manual/making-software/information-security.html#senior-information-risk-owner-siro" target="_blank">Senior Information Risk Officer, or SIRO</a>). The organisational policy may well give this person (delegated from the SIRO) the last word on policy - however this doesn't mean this person has the technical background to ensure that policy is implemented - or even practical.<br />
<br />
There may also be someone in the IT infrastructure whose remit is security - either a dedicated IT Security Officer, or perhaps someone who has this responsibility as part of their work. Again, this is quite a wide ranging remit, and this person may feel it is better left to subject matter experts in specific systems, and lay guidance as to the principles to be observed.<br />
<br />
This leaves the DBA with two potential places to go for security instructions, yet neither may understand the detailed technical processes necessary to actually undertake the remit, as their remit is either business focused or too wide for the focus on the specific technology of databases. That's where the DBA brings his or her expertise to bear, and as such brings value to the business.<br />
<br />
Often the DBA role is also to mediate where the tension exists between data security and information use. This is because they are the guardian of the security on the data - and so the first place that gets approached when technical permission is denied. This brings to light the difference between a technical prevention measure, and a business policy allowing, forbidding, or laying restrictions on a course of action.<br />
<br />
Whilst clearly data security will almost always win the balance between security and flexibility, the role of the data professional is increasingly to suggest a way to merge the data <i>once permission is secured</i> and <i>in accordance with best practice</i>. This is a difficult balancing act, which may not be fully understood by information users.<br />
<br />
So how can we alleviate the issues this generates? Firstly - publicity. Explain what is necessary, and have a ready to roll out example of why you wouldn't like your data to be misused. Perhaps an example might be of sensitive personal data being unexpectedly available to the world for misuse, or the potential for processing of data in a way it wasn't gathered for - both breaches of the <a href="https://www.gov.uk/data-protection/the-data-protection-act" target="_blank">DPA</a>, but still things that get requested disturbingly often.<br />
<br />
A second avenue is to engage with the other data professionals in a proactive way, and ensure that clear paths are laid down to get issues circulated for discussion - and engage with the business to resolve them. Generally, there's a good way to do things, and a bad way - for example, would anonymised data do for a task? Could the risk be mitigated somehow? Perhaps we can't do something with the data we have, but changing how it is captured to ensure the correct permissions are granted gives us the ability to do this in the future. If the policies don't exist (or haven't been reviewed recently) then these can be bolstered to ensure they are fit for purpose - and in line with recent changes to regulation.<br />
<br />
With these approaches, we lay down a safe way of working, that also keeps us within legal boundaries and hence providing value to the business with both of these points.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-91310537685856819522015-03-04T20:32:00.001+00:002015-03-04T20:32:14.808+00:00Justifying Unit Tests for DatabasesIt’s a shame that unit testing of databases is, relatively speaking, the poor cousin of application unit tests. After all, the same benefits of surety of code and ease of refactoring can be achieved by this simple means, and ultimately costs saved by ensuring that the (inevitable) bugs in an application and database are caught earlier in the product lifecycle.<br />
<br />
Whilst Unit testing is a key point in Agile software development, there are too many projects out there where the existing codebase is perceived to be too large to allow unit tests to be built up for a product. This isn’t necessarily the only way to tackle an existing codebase- I’d recommend starting with bug fixes, and building up from there.<br />
<br />
I can foresee that an idea that is common in microservice provision, to allow a development team to “adopt” or retain responsibility for a software product for the lifecycle of that product – from initial concept to final retirement, with all the rollout, upgrade and support in the middle – will be what finally convinces database developers that it’s not worth risking buggy code – or the chance of breaking something else when the inevitable support call does come. This merging of the traditional development and operations functions into one service-oriented team makes it in ones’ own interest to minimise total effort on issues – and a few other best practices like good documentation and minimising “just in case” code along the way.<br />
<br />
As a strong proponent of data integrity, I see unit testing as part of the armoury of tools necessary to develop and maintain a system to capture information in a robust format, and ensure that the data retains it’s integrity through its’ lifetime.<br />
<br />
If you want to find out more about how to justify unit testing for databases, I’ve recently published <a href="http://www.pluralsight.com/courses/unit-testing-database-code" target="_blank">a course on this very subject through Pluralsight</a>. Whilst Pluralsight isn't free to access, there is a free trial available, so set some time aside and watch it to help you build a business case for adopting this best practice.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-63073541990975889472014-10-20T17:10:00.000+01:002014-10-20T17:11:19.395+01:00What's it like to attend a community event like SQL Relay for the first time?<div dir="ltr">
<b>Following the recent <a href="http://www.sqlrelay.co.uk/" target="_blank">SQL Relay</a> event in Bristol, I had the opportunity to talk to a first time attendee, Claire, who has recently found herself in charge of the SQL Servers in her workplace. I asked her if she would mind sharing her thoughts on the event, so that others can get an insight into what it's like to attend for the first time.</b></div>
<div dir="ltr">
<b><br /></b></div>
<div dir="ltr">
<b>What in the day did you most enjoy, and why?</b></div>
<div dir="ltr">
Quite glad I got to see Alex Whittles' session [<i>Using PowerPivot and DAX to Predict and Win Fantasy F1</i>] because it was interesting to see how the power of Excel can be used, and seeing it applied in the way he did made it easier to understand.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>So the real world example helped?</b></div>
<div dir="ltr">
Yes, and quite a fun example too, and I do think it's something we could use as well.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>Anything that didn't work in the event?</b><br />
I don't know if it's because I arrived late to the start of the session [<i>Claire had been speaking to a sponsor at the time, and had run into the start of the following session</i>], but I found the BIML session quite hard to understand, as I felt I'd missed the introduction and found it quite hard to follow where he was going, but I can see it would be useful if you're trying to create multiple packages and don't want to run through and do the same thing again.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>Had you come across BIML before?</b><br />
No, although I had found something similar before, so I could recognise the concept, and it was nice to see you can build things in that way rather than just amending them.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>Did you realise that was an intermediate session?</b><br />
No, no I hadn't picked that up! I felt it was good, but I did feel a little behind.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>There's often one session like that - particularly if it's not something you can use at work, and get familiar with, but it's good to introduce yourself to new concepts.</b></div>
<div dir="ltr">
The NoSQL Session, Richard Munn's session - I was a bit confused by it I suppose, because everything we do is entrenched in SQL, seeing outside that was hard; and then seeing the three different pieces of software he was demonstrating, I was trying to see how that could be of relevance to what we do, and if it would be better, but I found I couldn't clearly see.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>So it's something you need a longer session to appreciate?</b><br />
Yes, I think so, but it's great to see how the concepts work, and it's good to see what else is out there.</div>
<div dir="ltr">
<b><br /></b></div>
<div dir="ltr">
<b>Yes, it's just a taster, isn't it..</b></div>
<div dir="ltr">
Yes. And his presentation style was funny, which helped - he did make me laugh!</div>
<div dir="ltr">
And the last session, the "fun" one [Dave Morrison's SQL Fun Time Hour], I wasn't really sure what to expect, because it didn't really describe what it was about before I went, but it was really interesting, really good, and I was hanging on his every word. There was so much in there that was really useful.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>So that's the one you'd like to see more of?</b></div>
<div dir="ltr">
Yes, it was all real life, little bits of every day stuff you do all day and don't necessarily appreciate what issues it throws up in the background. I did really appreciate that one.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>So would you go to more community events in the future?</b></div>
<div dir="ltr">
Yes, oh yes - well, if I'm allowed to!</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>Some of the events are at weekends, or evenings - would you make it in your own time?</b></div>
<div dir="ltr">
That's a different type of "Allowed To"!</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>It sounds like a positive experience then?</b></div>
<div dir="ltr">
Yeah, oh yeah, I'd definitely try to go again.</div>
<div dir="ltr">
It was just like a series of lectures, wasn't it, you just choose what you think might be most applicable and choose, and I like that, and it was friendly as well! I had thought it might be a bit more unfriendly, but it was fine.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>Did that worry you - that it might be aimed at people who had been doing the job longer?</b></div>
<div dir="ltr">
Yes, I know that some of the people I spoke to, the moment they spoke back to me I could only really smile as I didn't understand what they were saying, but I did also have conversations with people who I felt were in a similar position to me, which is good to know! And I was already expecting there would be less females there, so that wasn't a surprise.</div>
<div dir="ltr">
I don't know if I feel strongly about gender mix, but didn't wan't to feel people were talking over my head just because I was female.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>Did you find that?</b></div>
<div dir="ltr">
No, I didn't get that at all.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>I'm glad to hear that.</b></div>
<div dir="ltr">
Yes, a good day, but now I just feel I could sleep for a year - It's a lot to take in, and reflect upon. I just hope I've got enough notes to convince my employer to send me to another one!</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<b>Thanks for your time, Claire, and I hope to see you at future events.</b></div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-11854956358226025122014-06-08T23:05:00.000+01:002014-06-08T23:06:24.702+01:00Check constraints not what you thought they were?I was recently adding a check constraint to a table in SQL Server, and noted some behaviour which surprised me, so I thought I'd share it with you. I've tried this on SQL Server 2012 and 2008 R2.<br />
<br />
I added two fields, which either both needed to be null or both filled in. (Now, it is possible this requirement points to a normalisation requirement - but that's not the point at hand). So the check constraint is simply to ensure that either:<br />
<br />
<ul>
<li>Field A is null AND Field B is null</li>
</ul>
OR<br />
<ul>
<li>Field A is not null AND Field B is not null </li>
</ul>
<br />
So I used the following check constraint:<br />
<br />
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue;">USE </span><span style="color: black;">tempdb</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: blue;">CREATE TABLE </span><span style="color: black;">AllOrNothing<br /> </span><span style="color: grey;">(<br /> </span><span style="color: black;">id </span><span style="color: blue;">INT </span><span style="color: #434343;">IDENTITY</span><span style="color: grey;">(</span><span style="color: black;">1</span><span style="color: grey;">, </span><span style="color: black;">1</span><span style="color: grey;">) ,<br /> </span><span style="color: black;">FieldA </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">) ,<br /> </span><span style="color: black;">FieldB </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">)<br /> )</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: blue;">ALTER TABLE </span><span style="color: black;">AllOrNothing </span><span style="color: blue;">ADD CONSTRAINT </span><span style="color: black;">UK_AllOrNothing </span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"> CHECK </span><span style="color: grey;">(</span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"> (</span><span style="color: black;">FieldA </span><span style="color: blue;">IS </span><span style="color: grey;">NULL AND </span><span style="color: black;">FieldB </span><span style="color: blue;">IS </span><span style="color: grey;">NULL) </span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"> OR </span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"> (</span><span style="color: black;">FieldA </span><span style="color: blue;">IS </span><span style="color: grey;">NOT NULL AND </span><span style="color: black;">FieldB </span><span style="color: blue;">IS </span><span style="color: grey;">NOT NULL)</span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"> )</span></code><br />
<br />
<br />
Now, what surprised me was when I queried sys.constraints on this:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpL9BB_3QmPcEUV5QpgDyi1ko_8W4VcmGXhIhiA33cM9ZuJozpsHAWjmiDrw24uioTG9c5Ngc15aZ3XpUx2i31r4cy4r88MS6IqPixJ5WGqda5mWQTy5IBbOpAaIZTie3QSc4GrI5fvxyX/s1600/SystemTables.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpL9BB_3QmPcEUV5QpgDyi1ko_8W4VcmGXhIhiA33cM9ZuJozpsHAWjmiDrw24uioTG9c5Ngc15aZ3XpUx2i31r4cy4r88MS6IqPixJ5WGqda5mWQTy5IBbOpAaIZTie3QSc4GrI5fvxyX/s1600/SystemTables.PNG" height="82" width="400" /></a></div>
<br />
<br />
and again if I modify the constraint in SQL Server Management Studio:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCoLj-9G1V-p13anh_h83ETgrCXpx99Df-4WUAKubMvi-6PsMk1uUGG-k-JXqSCxd52VE_lKeWt5PIHSUzB-0nIxDEOCdF1W2rJtb5FU-Y_W8N_Y2yyOJDJvx6kYDY_31N4Wb5Nmb96uLE/s1600/ModifyConstraint.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCoLj-9G1V-p13anh_h83ETgrCXpx99Df-4WUAKubMvi-6PsMk1uUGG-k-JXqSCxd52VE_lKeWt5PIHSUzB-0nIxDEOCdF1W2rJtb5FU-Y_W8N_Y2yyOJDJvx6kYDY_31N4Wb5Nmb96uLE/s1600/ModifyConstraint.PNG" height="201" width="320" /></a></div>
<br />
<br />
The brackets have gone! Now, this isn't going to affect the functionality due to the <a href="http://technet.microsoft.com/en-us/library/ms186992(v=sql.105).aspx" target="_blank">precedence order of the logical operators</a>, but will still have a SQL Developer looking at it and wondering if the intended functionality is what's been put in place. (Of course, the best way to be sure is to unit test your code).<br />
<br />
I thought I'd blog about this, as whilst it doesn't change the function of what's been coded, it <b>does</b> change the <b>form</b> of it, which is unusual and was to me unexpected.<br />
<br />
<br />Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-84562856899934755042014-04-24T07:41:00.003+01:002014-04-24T07:41:24.958+01:00What do you use to version control your databases?Many developers version control (AKA source control) the databases upon which they work, to give them the same <a href="http://blog.pluralsight.com/unit-testing-databases" rel="" target="_blank">benefits as we receive when using source control for applications</a>, as well as the benefit that we have the two systems version controlled together. But a recent tweet from Red Gate got me thinking about those that don't use source control for databases.<br />
<br />
<blockquote class="twitter-tweet" lang="en">
24% of developers don't use database source control - make sure you aren't one of them. See the crew at <a href="https://twitter.com/search?q=%23techdaysNL&src=hash">#techdaysNL</a> to learn more<br />
— Red Gate (@redgate) <a href="https://twitter.com/redgate/statuses/456711379852881920">April 17, 2014</a></blockquote>
<script async="" charset="utf-8" src="//platform.twitter.com/widgets.js"></script><br />
<br />
I asked Red Gate about the source of their figures, and they helpfully supplied the results of the polls on <a href="http://sqlservercentral.com/">SQLServerCentral.com</a> upon which they had based this. The figures make interesting reading, and I wanted to draw out a couple of trends which I found interesting. Here's a plot of the results<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh24XmNzIBixsDTCtvNy4qeoE2FR8Dt-x5njb-j1GxglqzoRObxNZusinfvkEJ5-OBU-g_WN0i9UJEWVAd8FaK3QIg-JL2U5jgHAEPPU9tTV-IHHvrqTJdOKrjPmFqxpGjWoW0JNGaddmUi/s1600/Chart+of+VCS+use+over+time.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="A graph of the poll results supplied by Red Gate" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh24XmNzIBixsDTCtvNy4qeoE2FR8Dt-x5njb-j1GxglqzoRObxNZusinfvkEJ5-OBU-g_WN0i9UJEWVAd8FaK3QIg-JL2U5jgHAEPPU9tTV-IHHvrqTJdOKrjPmFqxpGjWoW0JNGaddmUi/s1600/Chart+of+VCS+use+over+time.PNG" height="183" title="Which Version Control System do you currently use to store you database scripts?" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">"Which Version Control System do you currently use to store your database scripts?"<br />Results from SQL Server Central.com</td></tr>
</tbody></table>
24% of developers not using database source control actually seemed a little low to me - my experience is that a great number of companies don't use source control for databases, but this may be due to a number of things, not least that as a consultant I tend to visit companies that need assistance! Looking at the figures however, I can see that it has varied between 31% and 21% of people don't use source control in each poll, and doesn't seem to be dropping dramatically. Now, this may be a different set of people each time, or it may be due to the self-selection nature of such polls, but it is clearly a not-inconsiderable amount of people.<br />
<br />
I've also noted with relief that the number of users of VSS has dropped dramatically since support for it was discontinued - if you still use VSS please consider migrating to a supported system. TFS is growing in use, probably due to the inclusion of licenses with MSDN subscriptions.<br />
<br />
The use of Git and Mercurial is also surprisingly low - perhaps the distributed nature of these systems do not lend themselves as naturally to database source control, but I have used Git with database scripts with no real issues in the past.<br />
<br />
I found these results interesting, and if you have not yet adopted source control for your databases, you may be interested to read my series of articles entitled <a href="http://www.sqlservercentral.com/stairway/109328/" target="_blank">Stairway to Database Source Control</a>, which are currently being published on SQL Server Central.com.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com1tag:blogger.com,1999:blog-1789435094629474988.post-16291377946861777532014-03-30T13:37:00.003+01:002014-04-22T17:51:15.855+01:00Why learn from Pluralsight?A little while ago, I recorded <a href="http://pluralsight.com/training/Courses/TableOfContents/unit-testing-t-sql-tsqlt" target="_blank">my first course</a> with <a href="http://pluralsight.com/" rel="nofollow" target="_blank">Pluralsight</a>, an online training provider.<br />
<br />
When discussing this with others, some have expressed reticence to try it, as access to the courses is sold on a subscription basis. In this post I want to tell you why I author <a href="http://pluralsight.com/training/Courses/TableOfContents/unit-testing-t-sql-tsqlt" target="_blank">courses</a> and <a href="http://blog.pluralsight.com/author/dave-green" target="_blank">blog posts</a> for Pluralsight, and share with you some ways that you can experience this training before paying, so you can ensure you like this way of learning before committing.<br />
<br />
I came across Pluralsight about a year ago, as a library of developer focussed learning. Since then, they've expanded and acquired other course libraries, covering the whole spectrum of IT learning, including Open Source and Cloud Computing, as well as commercial software. I still find them my go-to provider for the training I need (you should <a href="http://d-a-green.blogspot.co.uk/2013/01/get-your-learn-on-in-2013.html" target="_blank">regard continuing learning in your field as a best practice</a>). The on-line, modular model allows me to learn in the time I have - most courses have modules in 20-40 minute lengths, so they can be watched in a lunch break - and over several days you can cover a whole course. I find their approach to training to be conducive to learning, and the subscription model encourages you to learn and improve as much as you can, rather than each subject or area costing more money.<br />
<br />
So how can you try Pluralsight training, and see my course as well as the other great courses in the library, if you can't get budget approval for a subscription immediately?<br />
<br />
Well, firstly, you could sign up to a <a href="https://pluralsight.com/training/Subscribe/Step1?isTrial=True&failedCaptcha=False" rel="nofollow" target="_blank">10 day (200 minute) trial</a>. This is free of charge, giving you time to cancel if you don't like it before it converts to a monthly subscription. But there are other ways to get this sort of training for free that you may want to explore.<br />
<br />
Some courses are not charged for, and can therefore be accessed whether you have a subscription or not. For details of which, check <a href="http://blog.pluralsight.com/free-courses" target="_blank">these blog posts</a>. There are some <a href="http://pluralsight.com/training/kids" target="_blank">free courses for children</a>. But you may find that you already have a subscription which can help you get access to the full Pluralsight training library - such as Microsoft's <a href="http://blog.pluralsight.com/bizspark-pluralsight" target="_blank">BizSpark</a>, <a href="http://blog.pluralsight.com/dreamspark-pluralsight" target="_blank">Dreamspark+</a> and <a href="http://blog.pluralsight.com/websitespark-pluralsight" target="_blank">WebsiteSpark</a>, which each give you 90 days' access.<br />
<br />
Of course Pluralsight courses are not the only method of continuing your learning, and I recommend that you attend community events such as <a href="http://sqlsaturday.com/" rel="nofollow" target="_blank">SQL Saturdays</a> for in person learning, as well as your local SQL Server User Group.<br />
<br />
<b>UPDATE</b>: If you are a member of any of the following industry programmes, you can get a years' free Pluralsight training by <a href="http://blog.pluralsight.com/2014-vexperts-mvps-free-training" target="_blank">filling in this form</a> : Microsoft MVP, EMC Elect, Google Developers Expert, VMware vExpert, Citrix CTP, Salesforce MVP, ASPInsiders, Friends of Red Gate, JavaOne Rock Star, Cisco Champions.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-52127711601231352212014-02-26T22:44:00.001+00:002014-02-26T22:44:15.035+00:00Keeping tSQLt tests separate in SSDTUsers of <a href="http://www.red-gate.com/products/sql-development/sql-test/" rel="nofollow" target="_blank">SQL Test</a> and the <a href="http://tsqlt.org/" target="_blank">tSQLt framework</a> upon which it is based have <a href="http://sqltest.uservoice.com/forums/140716-sql-test-forum/suggestions/2421628-reduce-the-footprint" target="_blank">noted</a> in the past that because test objects are stored in the database, they can be difficult to differentiate from the objects under test. This has been a barrier to some users adopting tSQLt for unit testing, as it prevented separate management of tests and raises the potential danger of tests being accidentally deployed within a production environment.<br />
<br />
Recently I have been using tSQLt within SSDT (following <a href="http://kzhendev.wordpress.com/2014/01/08/setting-up-ssdt-database-projects-and-tsqlt/" target="_blank">this method by Ken Ross</a>), and it occurs to me that this method of controlling unit tests allows us to keep the code under test in a different project to our tests, and therefore overcome the difficulty of keeping our tests as database objects. When we have our tests in a separate project within the same solution, we can choose to deploy either the tests with our code under test or simply the code itself without the tests. By configuring the test project to ensure that it requires the code under test to also be deployed at the same time and into the same database, we can set up publish definitions for the tests to our development machine and also a publish definition for just the code under test. It's the latter which we would use to deploy outside of our development workstation, for example to UAT. It also means that if you deploy by DACPAC the tests have never been in that package, so it is ready to deploy to each environment without your needing to take any additional steps.<br />
<br />
Of course, a Continuous Integration engine has access to both projects within the solution from source control so it can either include unit tests or not, depending on your desired build action.<br />
<br />
By having both the code under test and the tests themselves within the same solution, we can use the same source control process allowing both the code under test and the tests themselves to be in one place, which prevents drift between the test and the code under test.<br />
<br />
When developing databases in SSDT, using this method gives me the best of both worlds; tests which to live with the database under development including within source control, and yet do not need to be removed from the database as part of or after the deployment process.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-31650223968469921622014-02-15T09:00:00.000+00:002014-02-17T12:56:50.191+00:00Book Review - Tribal SQL<a href="http://www.amazon.co.uk/gp/product/1906434808/ref=as_li_ss_il?ie=UTF8&camp=1634&creative=19450&creativeASIN=1906434808&linkCode=as2&tag=dl053-21" rel="nofollow" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="Tribal SQL" border="0" src="http://ws-eu.amazon-adsystem.com/widgets/q?_encoding=UTF8&ASIN=1906434808&Format=_SL110_&ID=AsinImage&MarketPlace=GB&ServiceVersion=20070822&WS=1&tag=dl053-21" title="" /></a><img alt="" border="0" src="http://ir-uk.amazon-adsystem.com/e/ir?t=dl053-21&l=as2&o=2&a=1906434808" height="1" style="border: none !important; margin: 0px !important;" width="1" /><br />
How many of you have written a book? Probably not many. Yet how people working with SQL Server have thought that they knew better, or that someone else would really appreciate how you do something? Probably a lot more.<br />
<br />
Trying to encourage new authors to share a small, manageable amount of knowledge on a subject, like the lightening talk of the book world, is one of the aims of the book I'm looking at in this post - Tribal SQL.<br />
<br />
It came about from a wish of<a href="https://twitter.com/MidnightDBA" target="_blank"> Jen and Sean McCown</a> (The Midnight DBAs) to let people read the insight and enthusiasm which is present in the less often heard corners of the SQL Community. I think they've done a good job in encouraging new voices.<br />
<br />
The subjects covered are as varied as the topics - and all are in an informal, insightful manor, which encourages this both as an introduction to areas into which you might not have previously strayed, and also as a reference material / revision of best practice for those who have.<br />
<br />
I'd strongly recommend this book to Accidental DBAs, who may find themselves suddenly confronted with a lot of new requirements and challenges, not the least of which is a distinct lack of training budget! (<i>I'd also encourage attendance at SQL Community events, like <a href="http://sqlsouthwest.co.uk/sqlsat269/" target="_blank">SQL Saturday Exeter</a> which is being held in March in the South West of England, and is free to attend</i>).<br />
<br />
This book doesn't restrict itself to the technical subjects (although it certainly does cover them, for example in <a href="http://downloads.red-gate.com/ebooks/SQL/sql-server-storage-internals-101.pdf" target="_blank">Storage Internals</a> and Data Compression) but also covers softer skills, such as project management skills, and how to get a stellar performance review. There's more basic introductory topics too, such as <a href="https://twitter.com/SteffLocke" target="_blank">Stephanie Locke</a>'s chapter on reports, and how to gather requirements, implement a good relationship with others in the organisation, and produce them a great report.<br />
<br />
I found the breadth really refreshing - the authors clearly want you to learn, and the fact that they've donated their royalties to <a href="http://computersforafrica.org.uk/" target="_blank">charity</a> really underlines their wish to help those around them.<br />
<br />
If you aren't convinced, Red Gate have currently got a sample chapter on their <a href="http://www.red-gate.com/community/books/tribal-sql">website</a> (UPDATE: as Melanie pointed out in the comments below, there are three sample chapters from the book on the Simple Talk website - <a href="https://www.simple-talk.com/sql/t-sql-programming/agile-database-development/" target="_blank">Agile Database Development</a>, <a href="https://www.simple-talk.com/sql/database-administration/guerrilla-project-management-for-dbas/" target="_blank">Guerrilla Project Management for DBAs</a> and <a href="https://www.simple-talk.com/sql/database-administration/sql-server-storage-internals-101/" target="_blank">SQL Server Storage Internals 101</a>), and you can <a href="http://www.amazon.co.uk/gp/product/1906434808/ref=as_li_ss_tl?ie=UTF8&camp=1634&creative=19450&creativeASIN=1906434808&linkCode=as2&tag=dl053-21" rel="nofollow">buy the book from Amazon</a><img alt="" border="0" src="http://ir-uk.amazon-adsystem.com/e/ir?t=dl053-21&l=as2&o=2&a=1906434808" height="1" style="border: none !important; margin: 0px !important;" width="1" /> - there's even an <a href="http://www.amazon.co.uk/gp/product/B00H3JP4R0/ref=as_li_ss_tl?ie=UTF8&camp=1634&creative=19450&creativeASIN=B00H3JP4R0&linkCode=as2&tag=dl053-21" rel="nofollow">e-book available</a>! The code samples are all available from the <a href="http://tribalsql.com/" target="_blank">Tribal SQL website</a>.<br />
<br />
<br />
<i><span style="font-size: x-small;">Disclosure: I was sent a review copy of this book </span></i><i><span style="font-size: x-small;">free of charge</span></i><i><span style="font-size: x-small;"> by Red Gate Publishing, but otherwise received no remuneration or reward for this review.</span></i>Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com2tag:blogger.com,1999:blog-1789435094629474988.post-88132442532804998342014-01-30T23:13:00.000+00:002014-02-07T19:03:18.804+00:00tSQLt adds Function Mocking to the unit testing armouryThe latest release of <a href="http://tsqlt.org/" target="_blank">tSQLt </a>(version V1.0.5137.39257) was published last week, and you can download this version in <a href="http://sourceforge.net/projects/tsqlt/files/latest/download" target="_blank">the usual place</a>. If you're using SQL Test with tSQLt, you can <a href="http://d-a-green.blogspot.co.uk/2013/02/upgrading-your-tsqlt-version-for-sql.html">upgrade SQL Test to the latest tSQLt release by following these instructions</a>.<br />
<br />
<i>(Note, if you've not come across tSQLt before, it's a database unit testing framework written in T-SQL, and there's a great article explaining why you might want to use it <a href="https://www.simple-talk.com/content/article.aspx?article=1542" target="_blank">here</a>. Red Gate have written <a href="http://www.red-gate.com/products/sql-development/sql-test/" target="_blank">SQL Test</a> to help integrate the power of tSQLt with the SQL Server Management Studio environment. This article assumes you're familiar with tSQLt, but if not I'd strongly encourage you to look at it for unit testing your databases.)</i><br />
<br />
I am pleased to see that this release adds function mocking, as well as a simpler way to rename classes than the previous work around, and checking for a specific error number to <a href="http://d-a-green.blogspot.co.uk/2013/10/testing-for-exceptions-with-tsqlt.html">tSQLt.ExpectException</a>.<br />
<br />
Let's look at that function mocking in a bit more detail. Consider a function which should add two numbers together. We would want to unit test it, and we can do that in the normal way, but a test on a stored procedure which needs to isolate from that function needs to call our new tSQLt method <a href="http://tsqlt.org/user-guide/isolating-dependencies/fakefunction/">tSQLt.FakeFunction</a>.<br />
<br />
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue;">CREATE FUNCTION </span><span style="color: black;">dbo.AddTogether </span><span style="color: grey;">(</span><span style="color: #434343;">@a </span><span style="color: blue;">INT</span><span style="color: grey;">, </span><span style="color: #434343;">@b </span><span style="color: blue;">INT</span><span style="color: grey;">)</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">RETURNS INT<br />AS <br />BEGIN<br /> RETURN </span><span style="color: #434343;">@a</span><span style="color: grey;">+</span><span style="color: #434343;">@a</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">END</span><span style="color: grey;">;</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">GO</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: blue;">CREATE PROC </span><span style="color: black;">Maths </span><span style="color: blue;">AS<br /> SELECT </span><span style="color: black;">dbo.AddTogether</span><span style="color: grey;">(</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">2</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">SumOfNumbers<br />GO</span></code><br />
<br />
Now, we can see there is a bug in the function above (and if we run the Stored procedure, we will get a value of 2 returned), but let's ignore that function and proceed to test our stored procedure. Remember, because we are isolating from our dependencies, we don't expect out stored procedure to fail its unit tests.<br />
<br />
The way that FakeFunction works is that you need to supply it with a stub function to use in place of your function to be isolated. I suggest you put them in the test class. So let's create a simple function that returns a static value - 3.<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">EXEC </span><span style="color: black;">tSQLt.NewTestClass </span><span style="color: #434343;">@ClassName </span><span style="color: blue;">= </span><span style="color: red;">N'MathsTests' </span><span style="color: green;">-- nvarchar(max)</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">GO</span></code><br />
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue;">CREATE FUNCTION </span><span style="color: black;">MathsTests.Fake </span><span style="color: grey;">(</span><span style="color: #434343;">@a </span><span style="color: blue;">INT</span><span style="color: grey;">, </span><span style="color: #434343;">@b </span><span style="color: blue;">INT</span><span style="color: grey;">) </span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">RETURNS INT</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">AS </span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">BEGIN</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"> RETURN </span><span style="color: black;">3</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">END</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">GO</span></code><br />
<br />
Now we are ready to create our test:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">CREATE PROC </span><span style="color: black;">MathsTests.[test I get a value of 3 returned when I add 1 and 2] </span></code><br />
<span style="color: blue; font-size: 12px;">AS</span><br />
<span style="color: green; font-size: 12px;">--Assemble</span><br />
<span style="color: blue; font-size: 12px;">EXEC </span><span style="color: black; font-size: 12px;">tSQLt.FakeFunction </span><span style="color: #434343; font-size: 12px;">@FunctionName </span><span style="color: blue; font-size: 12px;">= </span><span style="color: red; font-size: 12px;">N'dbo.Addtogether'</span><span style="color: grey; font-size: 12px;">, </span><span style="color: green; font-size: 12px;">-- nvarchar(max)</span><br />
<span style="color: green; font-size: 12px;"> </span><span style="color: #434343; font-size: 12px;">@FakeFunctionName </span><span style="color: blue; font-size: 12px;">= </span><span style="color: red; font-size: 12px;">N'MathsTests.Fake' </span><span style="color: green; font-size: 12px;">-- nvarchar(max)</span><br />
<span style="color: blue; font-size: 12px;"><br /></span>
<span style="color: blue; font-size: 12px;">CREATE TABLE </span><span style="color: black; font-size: 12px;">MathsTests.Expected </span><span style="color: grey; font-size: 12px;">(</span><span style="color: black; font-size: 12px;">SumOfNumbers </span><span style="color: blue; font-size: 12px;">INT</span><span style="color: grey; font-size: 12px;">)</span><br />
<span style="color: blue; font-size: 12px;">CREATE TABLE </span><span style="color: black; font-size: 12px;">MathsTests.Actual </span><span style="color: grey; font-size: 12px;">(</span><span style="color: black; font-size: 12px;">SumOfNumbers </span><span style="color: blue; font-size: 12px;">INT</span><span style="color: grey; font-size: 12px;">)</span><br />
<span style="color: blue; font-size: 12px;">INSERT </span><span style="color: black; font-size: 12px;">MathsTests.Expected </span><span style="color: grey; font-size: 12px;">(</span><span style="color: black; font-size: 12px;">SumOfNumbers</span><span style="color: grey; font-size: 12px;">)</span><br />
<span style="color: grey; font-size: 12px;"></span><span style="color: blue; font-size: 12px;">VALUES </span><span style="color: grey; font-size: 12px;">(</span><span style="color: black; font-size: 12px;">3</span><span style="color: grey; font-size: 12px;">)</span><br />
<span style="color: green; font-size: 12px;"><br /></span>
<span style="color: green; font-size: 12px;">--Act</span><br />
<span style="color: blue; font-size: 12px;">INSERT </span><span style="color: black; font-size: 12px;">MathsTests.Actual</span><br />
<span style="color: blue; font-size: 12px;">EXEC </span><span style="color: black; font-size: 12px;">tSQLt.ResultSetFilter 1</span><span style="color: grey; font-size: 12px;">,</span><span style="color: red; font-size: 12px;">'exec dbo.Maths'</span><br />
<span style="color: green; font-size: 12px;">--Assert</span><br />
<span style="color: blue; font-size: 12px;">EXEC </span><span style="color: black; font-size: 12px;">tSQLt.AssertEqualsTable </span><span style="color: #434343; font-size: 12px;">@Actual </span><span style="color: blue; font-size: 12px;">= </span><span style="color: red; font-size: 12px;">'MathsTests.Actual'</span><span style="color: grey; font-size: 12px;">, </span><br />
<span style="color: #434343; font-size: 12px;"> @Expected </span><span style="color: blue; font-size: 12px;">= </span><span style="color: red; font-size: 12px;">'MathsTests.Expected'</span><br />
<span style="font-size: 12px;">GO</span><br />
<br />
If we run this test, we get a successful test, because the stored procedure under test returns a row with a value of 3, the expectation, as our code under test (the stored procedure) is isolated from the function which has the bug in it. Of course, you would want to ensure that any module from which you isolate is properly tested, so that you catch issues such as this, but this somewhat contrived example allows you to see how the true cause of the failure can then be found more easily.<br />
<br />
The ability to fake functions in this way is a great addition to the unit test writer's armoury. Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com3tag:blogger.com,1999:blog-1789435094629474988.post-82690245935601529242013-12-13T11:29:00.000+00:002013-12-13T11:31:23.474+00:00Learn how to unit test your databases from the comfort of your desk!Following on from <a href="http://d-a-green.blogspot.co.uk/search/label/tSQLt" target="_blank">my previous posts on tSQLt</a>, I've recorded a <a href="http://pluralsight.com/training/Courses/TableOfContents/unit-testing-t-sql-tsqlt" target="_blank">training course</a> for Pluralsight which goes through how to use <a href="http://tsqlt.org/" target="_blank">tSQLt </a>and <a href="http://www.red-gate.com/products/sql-development/sql-test/" target="_blank">SQL Test</a> to unit test your databases, to give you confidence in your database code.<br />
<br />
I've used SQL 2012 Developer Edition for the course, but tSQLt will work with any version of SQL from 2005 SP2 upwards, and you don't need to purchase SQL Test to follow along.<br />
<br />
<a href="http://pluralsight.com/" target="_blank">Pluralsight </a>offer a 10 day free trial of their service, so you can try before you buy.<br />
<br />
My three hour <a href="http://pluralsight.com/training/Courses/TableOfContents/unit-testing-t-sql-tsqlt" target="_blank">course</a> takes you through why you would want to unit test, and introduces the tools we will use. I then show you how to write your first unit test, before going into more detail on how to isolate dependencies, and the variety of things to test for. We look at how to unit test in your day to day workflow, before looking at some more advanced topics, like using unit tests to enforce development standards.<br />
<br />
The course assumes you are familiar with T-SQL code and stored procedures, but have not used tSQLt before, so it should be of interest to those new to the concept of database unit testing as well as those who have dabbled with tSQLt or SQL Test.<br />
<br />
To see the course, or the detailed list of content, <a href="http://pluralsight.com/training/Courses/TableOfContents/unit-testing-t-sql-tsqlt" target="_blank">click here</a>.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-39454630543966673742013-12-10T14:14:00.000+00:002013-12-11T08:12:03.594+00:00T-SQL Tuesday #49: Wait for It …<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.sqlsoldier.com/wp/sqlserver/tsqltuesday49topiciswaitforit" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;" target="_blank"><span style="color: #666666;"><img alt="T-SQL Tuesday #49" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj62HHf49cMrqkG6sI2wQfrUs6PCf4BGRhvBSolGpc3Qdy5fMYJSHmsI8hiSZcdvlkyM36Zgh3_fHBLwgRBxBvNGbKChq7dYRd3vEFs-08ckXm45eMVOiH_pGm9K9936WT2_qN53Woo8CUR/s1600/TSQL2sDay150x150.jpg" /></span></a></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;">This is a post inspired by the T-SQL Tuesday blog party series; this month (#49) <a href="http://www.sqlsoldier.com/wp/sqlserver/tsqltuesday49topiciswaitforit" target="_blank">the topic is hosted by</a> <a href="https://twitter.com/SQLSoldier" target="_blank">Robert Davis (@SQLSoldier)</a>. Robert challenged us to "g<span style="font-family: 'Droid Sans', sans-serif; line-height: 22px;">ive us your most interesting post involving waits". Which reminds me...</span></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="font-family: 'Droid Sans', sans-serif; line-height: 22px;"><span style="color: #666666;"><br /></span></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="font-family: 'Droid Sans', sans-serif; line-height: 22px;"><span style="color: #666666;">I used to live in a little hamlet in the north of Devon. The closest post office was about 5 miles away, and this was so far in the sticks that the post office and village shop were second only to the pub for being the centre of the conversationalist's universe. As this was both in one, you can imagine how much information flowed through that little building!</span></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="font-family: 'Droid Sans', sans-serif; line-height: 22px;"><span style="color: #666666;"><br /></span></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="font-family: 'Droid Sans', sans-serif; line-height: 22px;"><span style="color: #666666;">However, this wasn't all great news if you were in a hurry. Occasionally, I'd pop across to post some item that needed weighing, or proof of purchase, and discover that in order to buy my stamp and post the letter, I'd have to wait for the conversation in front of me to finish. There was only one serving position, so It wasn't unknown for a queue with two people in it to take upwards of 45 mins. There was a similar queue if you wanted to buy stationary or similar from the shop desk (it seems to be a British tradition that the post office queue is separate from the queue for the rest of the shop - even if the same person then serves you).</span></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;"><br /></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;">So, I hear you ask, where is this going? What relevance does this have? Well, the post office / corner shop metaphor is a reasonable one for some of the waits in SQL Server (and I believe a reasonable attempt at a somewhat humourous twist on the topic at hand). </span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;"><br /></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;">We have the queue - the "to do" list, of people waiting to be served - queries or processes which are waiting to be worked on. We have the thing they are waiting for - time at the counter - this could represent resources such as RAM, with the worker who serves us as the CPU. We have the question of whether there is more than one person serving at a time (Parallelism). Perhaps we have a post office and a corner shop (2 instances) in the same building (server).</span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;"><br /></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;">What about the shop owner (DBA)? He has to plan for whether he has enough capacity (resources) for the people coming to the shop (workload). He needs a big enough doorway (IO) to cope with the people coming and going, with their parcels (data). He doesn't want to pay for (license) more staff than he has need for, but neither does he want people to dread coming to his shop (Server) to do their business (get, put or update the required data) because it takes too long - otherwise he may lose his contract to run the post office (job)! </span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;"><br /></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;">In the day to day process of managing the flow of people through a post office, our shop owner needs to ensure he has information about why people aren't happy, so that he can solve it. Our DBA has these tools however, and one of these is the wait stats view <a href="http://msdn.microsoft.com/en-us/library/ms179984.aspx" target="_blank">sys.dm_os_wait_stats</a>, which gives the aggregated view of waits that the system has encountered - the equivalent of an anonymous survey of all customers using the shop during the day asking what they waited for, which gives us the tools to see where we might be stretched for resources. As with the survey, sometimes the results are a little misleading, and so we need to dig further to cure the problem, but it gives a good place to start looking.</span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;"><br /></span></div>
<div dir="ltr" style="background-color: white; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; line-height: 18px;">
<span style="color: #666666;">A lot of the problems in this simple metaphor are familiar to us all, but I find that relating the technical details to a real world environment can help to clearly explain why things aren't working, and perhaps help us to solve a constraint on our work.</span></div>
<div>
<br /></div>
<div>
<span style="color: #666666;">Lastly, yes I know that this was strictly about waits involving post, rather than a post involving waits... ;-)</span></div>
<div>
<br /></div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com1tag:blogger.com,1999:blog-1789435094629474988.post-92063451168226942502013-11-30T10:09:00.001+00:002013-11-30T10:09:57.726+00:00Using Insert Exec with multiple result setsSometimes, there is a need to get result sets from a stored procedure into a table. One deceptively simple method is with a Insert..Exec statement.<br />
<br />
This works fine for a simple example such as:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">USE </span><span style="color: black;">tempdb<br />GO</span><span style="color: blue;"><br /></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">CREATE PROC </span><span style="color: black;">dbo.tempproc </span><span style="color: blue;">AS<br /> SELECT </span><span style="color: red;">'a' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br /> </span><span style="color: red;">'b' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br /> </span><span style="color: black;">1 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br /><br />GO</span></code><br />
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@table </span><span style="color: blue;">TABLE<br /> </span><span style="color: grey;">(<br /> </span><span style="color: black;">FirstChar </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">1</span><span style="color: grey;">) ,<br /> </span><span style="color: black;">SecondChar </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">2</span><span style="color: grey;">) ,<br /> </span><span style="color: black;">FirstInt </span><span style="color: blue;">INT<br /> </span><span style="color: grey;">)<br /></span><span style="color: blue;">INSERT </span><span style="color: #434343;">@table<br /> </span><span style="color: blue;">EXEC </span><span style="color: black;">dbo.tempproc</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">SELECT </span><span style="color: grey;">* </span><span style="color: blue;">FROM </span><span style="color: #434343;">@table</span></code><br />
<br />
<br />
This returns our table, as we might expect.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2J12bjxQOrhbgZJs-U7i6WJpNf3WAsFDPgGxZZGSOwoqWzcTP4pYwDDClucVw3-IICRl5Y67NraK7pLQTT5SgMr7HTjmbouq8VqOjvMVBCjD0_a6D0zOB241sjbw-xXCwvMl9Sgx4pZB6/s1600/InsertExecOneTableResult.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2J12bjxQOrhbgZJs-U7i6WJpNf3WAsFDPgGxZZGSOwoqWzcTP4pYwDDClucVw3-IICRl5Y67NraK7pLQTT5SgMr7HTjmbouq8VqOjvMVBCjD0_a6D0zOB241sjbw-xXCwvMl9Sgx4pZB6/s1600/InsertExecOneTableResult.PNG" /></a></div>
<br />
<br />
However, the situation gets a little more complex if we return from our stored procedure two datasets, which have the same metadata:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">ALTER PROC </span><span style="color: black;">dbo.tempproc </span><span style="color: blue;">AS<br />
SELECT </span><span style="color: red;">'a' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br />
</span><span style="color: red;">'b' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br />
</span><span style="color: black;">1 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br />
<br />
</span><span style="color: blue;">SELECT </span><span style="color: red;">'c' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br />
</span><span style="color: red;">'d' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br />
</span><span style="color: black;">2 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br />
GO</span></code><br />
<br />
Now, when we run our code to capture the dataset we get:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqxdxWVMgyQ_ERU6is-6TkX1RkdL00H_zQl9HtufDGALmR07OVnxpJyOW0a9zlTeQRj1Ix2aDc-ihwdMWnYIFuH9KTlHw5YAZSHwniIopDa5QZcTrASBLzYI1D9jJ4Mk3s4BwK9ppn8enX/s1600/InsertExecTwoTableResult.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqxdxWVMgyQ_ERU6is-6TkX1RkdL00H_zQl9HtufDGALmR07OVnxpJyOW0a9zlTeQRj1Ix2aDc-ihwdMWnYIFuH9KTlHw5YAZSHwniIopDa5QZcTrASBLzYI1D9jJ4Mk3s4BwK9ppn8enX/s1600/InsertExecTwoTableResult.PNG" /></a></div>
<br />
<br />
This is perhaps a little surprising - and gives us the scenario where the results from our stored proc are indistinguishable from what we would get if we had:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">ALTER PROC </span><span style="color: black;">dbo.tempproc </span><span style="color: blue;">AS<br />
SELECT </span><span style="color: red;">'a' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br />
</span><span style="color: red;">'b' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br />
</span><span style="color: black;">1 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br />
</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL<br />
</span><span style="color: blue;">SELECT </span><span style="color: red;">'c' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br />
</span><span style="color: red;">'d' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br />
</span><span style="color: black;">2 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br />
GO</span></code><br />
<br />
However, if we accessed this same data via a different method (e.g. from CLR or an application looking at distinct result sets) these would be distinguishable. This is in my opinion counter intuitive behaviour which can give us a misleading result.<br />
<br />
Suppose you were capturing a result which would be either in the first result set or in the second (the other to be returned empty). It follows that you couldn't use Insert..Exec to capture this as you would be unable to determine which set had caused it. In other words, the results returned using Insert Exec with the following two queries are indistinguishable:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">ALTER PROC </span><span style="color: black;">dbo.tempproc </span><span style="color: blue;">AS<br />
SELECT TOP </span><span style="color: black;">0 </span><span style="color: red;">'a' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br />
</span><span style="color: red;">'b' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br />
</span><span style="color: black;">1 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br />
<br />
</span><span style="color: blue;">SELECT </span><span style="color: red;">'a' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br />
</span><span style="color: red;">'b' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br />
</span><span style="color: black;">1 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br />
GO</span></code><br />
<code style="font-size: 12px;"><span style="color: black;"><br />
</span><span style="color: blue;">ALTER PROC </span><span style="color: black;">dbo.tempproc </span><span style="color: blue;">AS<br />
SELECT </span><span style="color: red;">'a' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br />
</span><span style="color: red;">'b' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br />
</span><span style="color: black;">1 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br />
<br />
</span><span style="color: blue;">SELECT TOP </span><span style="color: black;">0 </span><span style="color: red;">'a' </span><span style="color: blue;">AS </span><span style="color: black;">FirstChar </span><span style="color: grey;">,<br />
</span><span style="color: red;">'b' </span><span style="color: blue;">AS </span><span style="color: black;">SecondChar </span><span style="color: grey;">,<br />
</span><span style="color: black;">1 </span><span style="color: blue;">AS </span><span style="color: black;">FirstInt<br />
GO</span></code><br />
<br />
Try running the queries individually and you will see what I mean!<br />
<br />
This is something to be aware of, both when coding and testing stored procedures, especially if Insert..Exec is used to capture information.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-28025762407532100172013-10-29T15:43:00.000+00:002013-10-29T17:19:06.268+00:00Testing for Exceptions with tSQLtI've recently been looking at two relatively new pieces of functionality in tSQLt, the unit testing framework that underpins Red Gate software's SQL Test.<br />
<br />
I've <a href="http://d-a-green.blogspot.co.uk/search/label/tSQLt" target="_blank">written </a>and <a href="http://d-a-green.blogspot.co.uk/2013/03/im-sure-my-procedure-does-what-it-should.html" target="_blank">presented </a>about SQL Test and tSQLt before, including <a href="http://d-a-green.blogspot.co.uk/2013/02/upgrading-your-tsqlt-version-for-sql.html" target="_blank">how to upgrade SQL Test to the latest version of tSQLt</a>, but in case you've not been aware of these tools previously, tSQLt is a unit testing framework which allows SQL Server developments to be properly unit tested, within the database, and SQL Test is a user interface which makes it easy to see and run tests.<br />
<br />
One of my gripes in the past has been the limited functionality for testing exceptions. Most applications will raise an error in some place or another, hopefully well captured and handled, but not always. Often the database needs to raise an error to feed back to the application, and this should be tested in the same way as any other requirement.<br />
The problem which I have come across in the past is in how to prevent the inbuilt transaction handling capabilities of SQL server from rolling back the test transaction if you hit an error (tSQLt runs all tests in a transaction). This can cause the test to error, which of course is contrary to the desired behaviour. Previously I've had to use a <a href="http://technet.microsoft.com/en-us/library/ms175976.aspx" target="_blank">catch-try</a> block to set a variable which I then evaluated to check whether an exception had been raised.<br />
<br />
That's where this new functionality comes in - we have two new functions, <a href="http://tsqlt.org/user-guide/expectations/expectexception/" target="_blank">ExpectException </a>and <a href="http://tsqlt.org/user-guide/expectations/expectnoexception/" target="_blank">ExpectNoException </a>which encapsulate the following command, watching for an exception, and pass or fail the test appropriately. ExpectException can even look out for a specific message, and fail if you get a different message.<br />
<br />
So, a demonstration. Consider the following stored procedure:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">CREATE PROC </span><span style="color: black;">dbo.MyProc </span><span style="color: #434343;">@Myvar </span><span style="color: blue;">INT<br />
AS<br />
IF </span><span style="color: #434343;">@Myvar </span><span style="color: grey;">!= </span><span style="color: black;">1</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">BEGIN<br />
RAISERROR </span><span style="color: grey;">(</span><span style="color: red;">'Variable is not one!'</span><span style="color: grey;">,</span><span style="color: black;">16</span><span style="color: grey;">,</span><span style="color: black;">1</span><span style="color: grey;">)</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">END</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">GO</span></code><br />
<br />
This will create a procedure that will error if you do not supply a valid input - a number 1. Clearly this is a fabricated example, but it illustrates the point.<br />
<br />
This can be tested with the following test:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">EXEC </span><span style="color: black;">tSQLt.NewTestClass </span><span style="color: #434343;">@ClassName </span><span style="color: blue;">= </span><span style="color: red;">N'ExceptionTesting' </span><span style="color: green;">-- Test class creation</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">GO<br />
</span><span style="color: blue;"><br />
</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">CREATE PROC </span><span style="color: black;">ExceptionTesting.[test wait for exception old method]</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">AS<br />
DECLARE </span><span style="color: #434343;">@error </span><span style="color: blue;">INT = </span><span style="color: black;">0 </span><span style="color: green;">/* flag to indicate that an error was raised */</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">BEGIN </span><span style="color: black;">TRY</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"> EXEC </span><span style="color: black;">dbo.MyProc </span><span style="color: #434343;">@Myvar </span><span style="color: blue;">= </span><span style="color: black;">2 </span><span style="color: green;">/* the code under test */</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">END </span><span style="color: black;">TRY</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">BEGIN </span><span style="color: black;">CATCH</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"> SET </span><span style="color: #434343;">@error </span><span style="color: blue;">= </span><span style="color: black;">1 </span><span style="color: green;">/* Set variable as an error was raised */</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">END </span><span style="color: black;">CATCH</span></code><br />
<code style="font-size: 12px;"><span style="color: blue; font-size: 12px;"><br />
</span></code> <code style="font-size: 12px;"><span style="color: blue; font-size: 12px;">IF </span><span style="color: #434343; font-size: 12px;">@error </span><span style="color: grey; font-size: 12px;">!= </span><span style="color: black; font-size: 12px;">1 </span><span style="color: green; font-size: 12px;">/* Raise an error if the variable has not been set */</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"> EXEC </span><span style="color: black;">tSQLt.Fail </span><span style="color: red;">'Exception was not raised'</span></code><br />
<code style="font-size: 12px;"><span style="font-size: 12px;">GO</span></code><br />
<br />
Which will fail the test if an exception is not raised. It is even possible to test for a specific message using <a href="http://technet.microsoft.com/en-us/library/ms190358.aspx" target="_blank">ERROR_MESSAGE</a> to determine this in the catch block.<br />
<br />
The new functionality however makes this much more user friendly as a test, which of course makes this more maintainable.<br />
<br />
So how would this test now look?<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">CREATE PROC </span><span style="color: black;">ExceptionTesting.[test wait for exception new method]</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">AS</span><span style="color: blue; font-size: 12px;"><br />
</span></code><br />
<code style="font-size: 12px;"><span style="color: blue; font-size: 12px;">EXEC </span><span style="color: black; font-size: 12px;">tSQLt.ExpectException </span><span style="color: #434343; font-size: 12px;">@ExpectedMessage </span><span style="color: blue; font-size: 12px;">= </span><span style="color: red; font-size: 12px;">'Variable is not one!'</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"> EXEC </span><span style="color: black;">dbo.MyProc 2<br />
<br />
GO</span></code><br />
<br />
As you can see, this is much simpler, and easy to see what is being accomplished. It is also possible to check for specific messages (as has been done here), severities and states, as well as using pattern matching (for example for date or time based messages) as part of this same statement.<br />
<br />
It is worth noting that this is defined in tSQLt as an expectation rather than an assertion, which may catch some out in terms of naming, but is sensible to denote that the expectation is before the code under test is run, whereas assertions happen afterwards.<br />
<a href="https://plus.google.com/116480503975319546252?rel=author"></a>Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com2tag:blogger.com,1999:blog-1789435094629474988.post-81597229495106492972013-09-30T14:54:00.000+01:002013-09-30T14:54:00.817+01:00A word on data typesI came across a situation today where there was clearly confusion as to data types and numeric representation.<br />
<br />
The basic issue at hand was how to represent a large number in SQL Server.<br />
<br />
Now, SQL Server has a number of <a href="http://technet.microsoft.com/en-us/library/ms187752.aspx" target="_blank">data types</a>, which are essentially ways of representing data so that SQL Server can understand something about them. Each bit of data in SQL server needs to have one of these types. Some of these are used for numbers, and that is where our problem starts.<br />
<br />
There are specific ranges of data that can be represented in each, and a corresponding amount of storage used. Clearly this is a concern for DBAs and DB designers who need to calculate storage space for millions of potential records. For example, the Numeric / Decimal types have the following possibilities:<br />
<br />
<table style="border-collapse: collapse; border: 1px solid rgb(187, 187, 187); color: #2a2a2a; padding: 0px; text-align: center;"><tbody>
<tr><th style="background-color: #ededed; border: 1px solid rgb(187, 187, 187); color: #636363; margin: 10px; padding: 10px 8px;"><div style="color: #2a2a2a; line-height: 18px; padding-bottom: 0px;">
Precision</div>
</th><th style="background-color: #ededed; border: 1px solid rgb(187, 187, 187); color: #636363; margin: 10px; padding: 10px 8px;"><div style="color: #2a2a2a; line-height: 18px; padding-bottom: 0px;">
Storage bytes</div>
</th></tr>
<tr><td style="border: 1px solid rgb(187, 187, 187); margin: 10px; padding: 10px 8px; vertical-align: top;"><div style="line-height: 18px; padding-bottom: 0px;">
1 - 9</div>
</td><td style="border: 1px solid rgb(187, 187, 187); margin: 10px; padding: 10px 8px; vertical-align: top;"><div style="line-height: 18px; padding-bottom: 0px;">
5</div>
</td></tr>
<tr><td style="border: 1px solid rgb(187, 187, 187); margin: 10px; padding: 10px 8px; vertical-align: top;"><div style="line-height: 18px; padding-bottom: 0px;">
10-19</div>
</td><td style="border: 1px solid rgb(187, 187, 187); margin: 10px; padding: 10px 8px; vertical-align: top;"><div style="line-height: 18px; padding-bottom: 0px;">
9</div>
</td></tr>
<tr><td style="border: 1px solid rgb(187, 187, 187); margin: 10px; padding: 10px 8px; vertical-align: top;"><div style="line-height: 18px; padding-bottom: 0px;">
20-28</div>
</td><td style="border: 1px solid rgb(187, 187, 187); margin: 10px; padding: 10px 8px; vertical-align: top;"><div style="line-height: 18px; padding-bottom: 0px;">
13</div>
</td></tr>
<tr><td style="border: 1px solid rgb(187, 187, 187); margin: 10px; padding: 10px 8px; vertical-align: top;"><div style="line-height: 18px; padding-bottom: 0px;">
29-38</div>
</td><td style="border: 1px solid rgb(187, 187, 187); margin: 10px; padding: 10px 8px; vertical-align: top;"><div style="line-height: 18px; padding-bottom: 0px;">
17</div>
</td></tr>
</tbody></table>
<br /><div>
What this really means is that a number with a precision of 10 takes up nearly twice the storage space of a number with a precision of 9, but that storage space is only affected when you get beyond certain values - which may mean that you can use more precision for no storage space cost. Precision here is the number of digits that are stored (the total number including those both to the left and right of the decimal point).</div>
<div>
<br /></div>
<div>
The general rule I use is that one should use an appropriate type for the meaning of the data (i.e. if you're storing a number, use a type that is recognised as a number, if you are storing text, use a character-based type, if you are storing a date and time, use a type designed for the purpose, etc).</div>
<div>
<br /></div>
<div>
Of course, there is an obvious problem here. What can we do to store a number that has 39 digits in it?</div>
<div>
<br /></div>
<div>
Well, you cannot store a number in a data type that is outside the range of it. You need to pick an appropriate value. There are two solutions here - The application could report an error as the data is not able to be represented, or (more sensibly) the appropriate data type should be used. For some applications, you may only care about the significant digits (and not the final accuracy) so a <a href="http://technet.microsoft.com/en-us/library/ms173773.aspx" target="_blank">float data type</a> may be appropriate.</div>
<div>
<br /></div>
<div>
These are decisions that ideally would be taken by a database designer before an application is commissioned with full knowledge as to the intended use of the field, and certainly changing data types takes communication between various parties as you would ideally change the data type in the application, and everywhere that data is used in the database, to minimise implicit conversions and any resulting performance issues or <a href="http://technet.microsoft.com/en-us/library/ms187928.aspx" target="_blank">rounding / truncation</a>.It is something to be very careful of in an agile environment where you may find that data changes during development.</div>
<div>
<br /></div>
<div>
A word on the storage of numbers. If you are looking to store the data '9E10' then this could be a string data type (a digit nine, an E, a one and a zero), perhaps an equipment model number, or it could be representing 90000000000 (9*10^10). This is one reason why the data type is important, and typed data is more meaningful than untyped data. It would be stored differently, depending on the data type, and to SQL Server these different interpretations are not necessarily the same information (although if implicitly converted they could be perceived as such). This can lead to unexpected behaviour from your application.</div>
<div>
<br /></div>
<div>
Dates have a similar ambiguity to them, but with the <a href="https://xkcd.com/1179/" target="_blank">added ambiguity</a> that you don't know if 3/2/13 represents the 3rd of Feb (UK format) or 2nd of March (US format). For this reason it is always a good idea to use a specific <a href="http://msdn.microsoft.com/en-us/library/ff848733.aspx" target="_blank">date-based data-type</a> (there are some such as the Date type that only store the day, and others such as datetime, datetime2 or smalldatetime that store various precisions of time as well). <i>If you must use a string type to represent data (for example in printed output) then it is advisable to use an unanbiguous format, such as ISO8601 detailed - CCYY-MM-DD.</i></div>
<div>
<i><br /></i></div>
<div>
I hope this has clarified some of the uncertainty over date types.</div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-85695731278119287372013-08-13T19:12:00.000+01:002013-08-13T19:12:11.033+01:00What happens when the audit trail meets a data migration? <div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj62HHf49cMrqkG6sI2wQfrUs6PCf4BGRhvBSolGpc3Qdy5fMYJSHmsI8hiSZcdvlkyM36Zgh3_fHBLwgRBxBvNGbKChq7dYRd3vEFs-08ckXm45eMVOiH_pGm9K9936WT2_qN53Woo8CUR/s1600/TSQL2sDay150x150.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj62HHf49cMrqkG6sI2wQfrUs6PCf4BGRhvBSolGpc3Qdy5fMYJSHmsI8hiSZcdvlkyM36Zgh3_fHBLwgRBxBvNGbKChq7dYRd3vEFs-08ckXm45eMVOiH_pGm9K9936WT2_qN53Woo8CUR/s1600/TSQL2sDay150x150.jpg" /></a></div>
<div dir="ltr">
This is a post inspired by the T-SQL Tuesday blog party series; this month (#45) the topic is <a href="http://mickeystuewe.com/2013/08/05/t-sql-tuesday-45-invitationfollow-the-yellow-brick-road/" target="_blank">hosted by SQLMickey</a>. The topic this month is <i>Auditing</i>, so I thought I'd write some thoughts on how to deal with auditing when doing a system migration.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
Most systems have an audit trail of sorts, for legal or compliance reasons, or possibly just to make the system administrator's life easier. The idea is that this forms a permanent record of who did what, when. </div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
What happens to this when it's time to change the system or migrate the data out to a new system? How so you retain the audit trail, both of the old system and prove the methods used to transfer the data haven't changed it?</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
There are many types of audit trail, including paper based ones, but during IT projects focus is usually concentrated on the electronic ones built into systems. Usually, you cannot import the trail from an old system into your new system, as that would imply an editable audit trail. This means that you have to use another method in order to maintain traceability and the link between the old system including the data in it and the new.<br /></div>
<div dir="ltr">
If the old system is being maintained then it may be sufficient to simply keep the old system logs where they are, and keep accurate records as proof of how this was transferred to the new system.<br />
During the planning stages of an ETL migration it is likely that documentation will have been developed and this can be retained as proof of how the data was to be migrated.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
This may be in the form of data maps, or instructions, but needs to be at the detailed level necessary to provide the required traceability.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
By migrating data between systems you break the continuity of the records, so it is important that the records you keep are sufficient, and I find that it's best to involve the person from the business who has to defend items to an external auditor at an early stage, as they need to be comfortable that sufficient records are kept. This may involve data that will change as part of the migration, particularly when changing between systems that have a different way of organising data internally - but in my experience this is not important if proper records are kept such that the data can be related - in either direction - in the future.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
If an old system is being decommissioned, it is important to decide whether the audit trail needs to be kept, and if so for how long, as this may be different to how long the data itself needs to be kept. You may also have decisions to make as to the form in which the data is kept - often this is linked to the cost of storage, for example if storing it in the previous system means that a licence would need to be maintained to access the information, but the sanctity of the audit trail must also be considered if the data is to be exported from the system.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
A final point I would make is that however you retain this continuity between the data before and after migration, it is sensible to get someone else to review your work - both in terms of requirements and implementation, and document it so that even your decisions about the audit trail are audited.</div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com1tag:blogger.com,1999:blog-1789435094629474988.post-30401008043675304152013-07-27T07:37:00.004+01:002013-07-27T07:43:11.877+01:00Inserting words in varcharsIt recently occurred to me that not all SQL developers are aware of all of the ways to update string data in SQL server.<br />
<br />
Let's consider a simple table:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">CREATE TABLE </span><span style="color: black;">TempStuff<br />
</span><span style="color: grey;">(<br />
</span><span style="color: black;">i </span><span style="color: blue;">INT </span><span style="color: #434343;">IDENTITY</span><span style="color: grey;">(</span><span style="color: black;">1</span><span style="color: grey;">, </span><span style="color: black;">1</span><span style="color: grey;">)<br />
</span><span style="color: blue;">PRIMARY KEY </span><span style="color: grey;">,<br />
</span><span style="color: black;">mytext </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: magenta;">MAX</span><span style="color: grey;">) ,<br />
</span><span style="color: black;">myshorttext </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">1000</span><span style="color: grey;">)<br />
)</span></code><br />
<br />
Let's put some data in it:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">INSERT </span><span style="color: black;">dbo.TempStuff<br />
</span><span style="color: grey;">( </span><span style="color: black;">mytext</span><span style="color: grey;">, </span><span style="color: black;">myshorttext </span><span style="color: grey;">)</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">VALUES </span><span style="color: grey;">( </span><span style="color: red;">'This is text'</span><span style="color: grey;">, </span><span style="color: green;">-- mytext - varchar(max)<br />
</span><span style="color: red;">'This is text' </span><span style="color: green;">-- myshorttext - varchar(1000)<br />
</span><span style="color: grey;">)<br />
</span><span style="color: blue;">SELECT </span><span style="color: grey;">* </span><span style="color: blue;">FROM </span><span style="color: black;">dbo.TempStuff</span></code><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCqPBKunL2iutbh4DAo0lAOeRoaZbFuV0Sh-KZ67RdrKHsOW2Q-gDyWRUbAH7lW51Tm8HRpGVepuhu7QX1JhRAihph1GX3SDNXt4NHlxWy1yBqtysc1HM1hc1bWKdObglm_vvr6Oi6VIgj/s1600/1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCqPBKunL2iutbh4DAo0lAOeRoaZbFuV0Sh-KZ67RdrKHsOW2Q-gDyWRUbAH7lW51Tm8HRpGVepuhu7QX1JhRAihph1GX3SDNXt4NHlxWy1yBqtysc1HM1hc1bWKdObglm_vvr6Oi6VIgj/s1600/1.PNG" /></a></div>
<br />
So, let's say you want to insert a word into our text. There are several ways of doing it:<br />
<br />
The "left-write-right" method:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">UPDATE </span><span style="color: black;">dbo.TempStuff</span><span style="color: blue;">SET </span><span style="color: black;">mytext </span><span style="color: blue;">= </span><span style="color: magenta;">LEFT</span><span style="color: grey;">(</span><span style="color: black;">mytext</span><span style="color: grey;">, </span><span style="color: black;">7</span><span style="color: grey;">) + </span><span style="color: red;">' test' </span><span style="color: grey;">+ </span><span style="color: magenta;">RIGHT</span><span style="color: grey;">(</span><span style="color: black;">mytext</span><span style="color: grey;">, </span><span style="color: magenta;">LEN</span><span style="color: grey;">(</span><span style="color: black;">mytext</span><span style="color: grey;">) - </span><span style="color: black;">7</span><span style="color: grey;">) ,<br /> </span><span style="color: black;">myshorttext </span><span style="color: blue;">= </span><span style="color: magenta;">LEFT</span><span style="color: grey;">(</span><span style="color: black;">myshorttext</span><span style="color: grey;">, </span><span style="color: black;">7</span><span style="color: grey;">) + </span><span style="color: red;">' test' </span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"> </span></code><span style="color: grey; font-family: monospace; font-size: 12.222222328186035px;"> </span><span style="color: grey; font-size: 12px;">+ </span><span style="color: magenta; font-size: 12px;">RIGHT</span><span style="color: grey; font-size: 12px;">(</span><span style="color: black; font-size: 12px;">myshorttext</span><span style="color: grey; font-size: 12px;">,</span><span style="color: grey; font-size: 12px;"> </span><span style="color: magenta; font-size: 12px;">LEN</span><span style="color: grey; font-size: 12px;">(</span><span style="color: black; font-size: 12px;">myshorttext</span><span style="color: grey; font-size: 12px;">)</span><span style="color: grey; font-size: 12px;"> - </span><span style="color: black; font-size: 12px;">7</span><span style="color: grey; font-size: 12px;">)</span><br />
<code style="font-size: 12px;"><span style="color: blue;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: blue;">SELECT </span><span style="color: grey;">* </span><span style="color: blue;">FROM </span><span style="color: black;">dbo.TempStuff</span></code><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUEqQfTQ0Ip49mgrB71m8gILlyeA0mpP9RjPRai4cyPbo9uueBMpBYQbwYddGXdHtFw0HGWf7h0-KVP2gcpWFsOOBF9KBLcNaCDzx2yQT54E9yi9H_4emjFDMThz_62Epwh0XeEtAAU7Kg/s1600/2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUEqQfTQ0Ip49mgrB71m8gILlyeA0mpP9RjPRai4cyPbo9uueBMpBYQbwYddGXdHtFw0HGWf7h0-KVP2gcpWFsOOBF9KBLcNaCDzx2yQT54E9yi9H_4emjFDMThz_62Epwh0XeEtAAU7Kg/s1600/2.PNG" /></a></div>
<br />
This has simplicity, and is easy to understand for the new coder, but is not the only method. You can use the <a href="http://msdn.microsoft.com/en-us/library/ms188043.aspx" target="_blank">STUFF</a> function:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">UPDATE </span><span style="color: black;">dbo.TempStuff</span><span style="color: blue;">SET </span><span style="color: black;">mytext </span><span style="color: blue;">= </span><span style="color: magenta;">STUFF</span><span style="color: grey;">(</span><span style="color: black;">mytext</span><span style="color: grey;">, </span><span style="color: black;">9</span><span style="color: grey;">, </span><span style="color: black;">0</span><span style="color: grey;">, </span><span style="color: red;">'stuffed '</span><span style="color: grey;">) ,<br />
</span><span style="color: black;">myshorttext </span><span style="color: blue;">= </span><span style="color: magenta;">STUFF</span><span style="color: grey;">(</span><span style="color: black;">myshorttext</span><span style="color: grey;">, </span><span style="color: black;">9</span><span style="color: grey;">, </span><span style="color: black;">0</span><span style="color: grey;">, </span><span style="color: red;">'stuffed '</span><span style="color: grey;">)</span><span style="color: blue;"><br />
</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: blue;">SELECT </span><span style="color: grey;">* </span><span style="color: blue;">FROM </span><span style="color: black;">dbo.TempStuff</span></code><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIQYERJ_78ha0iIAxkhgF6Etf_EY-wAXBfYQHa2QFtiz5I59WsfRYC3km_eRYqkhyRPb348Rsupj-uZiW2jtn6pY5jmfMtnEFrClWfENZq4-kJ-lkg2qsuz5zMM3RaeQLereajSiAg3D1U/s1600/3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIQYERJ_78ha0iIAxkhgF6Etf_EY-wAXBfYQHa2QFtiz5I59WsfRYC3km_eRYqkhyRPb348Rsupj-uZiW2jtn6pY5jmfMtnEFrClWfENZq4-kJ-lkg2qsuz5zMM3RaeQLereajSiAg3D1U/s1600/3.PNG" /></a></div>
<br />
However, I recently found a method I had been previously unaware of, specifically for the varchar(max), nvarchar(max), varbinary(max) types (it doesn't work on, for example, other varchar fields as they are not treated the same way within SQL server). This is the .WRITE method, used to put some text at the end of a string:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">UPDATE</span><span style="color: blue;"> </span><span style="color: black;">dbo.TempStuff</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">SET</span><span style="color: blue;"> </span><span style="color: black;">mytext.WRITE</span><span style="color: grey;">(</span><span style="color: red;">' written at the end'</span><span style="color: grey;">,NULL,</span><span style="color: black;">0</span><span style="color: grey;">)</span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"><br />
</span><span style="color: blue;">SELECT </span><span style="color: grey;">* </span><span style="color: blue;">FROM </span><span style="color: black;">dbo.TempStuff</span></code><br />
<br />
The syntax is:<br />
<br />
<div style="text-align: center;">
<i>StringToUpdate</i>.WRITE(<i>Newtext</i>,<i>StartPosition</i>,<i>CharactersToDelete</i>)</div>
<br />
(if StartPosition is null, the end of the string to be updated is used)<br />
<br />
Now, given the alternatives and limitations, why would you use .WRITE? Well, whereas other methods need to read the text, update it, and write it, this only needs change the updated data. This is important where you have large data, as putting text on the end of the column is minimally logged (if you are in bulk-logged or simple modes) which can be faster. This <a href="http://msdn.microsoft.com/en-us//library/ms177523.aspx" target="_blank">MSDN</a> (see the section "Updating Large Value Data Types") notes that it is most effective if 8060 bytes are inserted at a time.<br />
<br />Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-3560473127845507372013-07-02T10:00:00.000+01:002013-07-02T10:00:06.926+01:00Book Review - SQL Server Transaction Log ManagementI was recently sent a review copy of "<a href="http://www.amazon.co.uk/gp/product/1906434964/ref=as_li_qf_sp_asin_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=1906434964&linkCode=as2&tag=dl053-21">SQL Server Transaction Log Management</a><img alt="" border="0" height="1" src="http://www.assoc-amazon.co.uk/e/ir?t=dl053-21&l=as2&o=2&a=1906434964" style="border: none !important; margin: 0px !important;" width="1" />" by Tony Davis and Gail Shaw, a new book from Red Gate (who publish the SimpleTalk series of websites and books.<br />
<br />
<a href="http://www.amazon.co.uk/gp/product/1906434964/ref=as_li_qf_sp_asin_il?ie=UTF8&camp=1634&creative=6738&creativeASIN=1906434964&linkCode=as2&tag=dl053-21" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="Image of book cover" border="0" src="http://ws.assoc-amazon.co.uk/widgets/q?_encoding=UTF8&ASIN=1906434964&Format=_SL160_&ID=AsinImage&MarketPlace=GB&ServiceVersion=20070822&WS=1&tag=dl053-21" title="" /></a><img alt="" border="0" height="1" src="http://www.assoc-amazon.co.uk/e/ir?t=dl053-21&l=as2&o=2&a=1906434964" style="border: none !important; margin: 0px !important;" width="1" /><br />
This book is part of the Stairway series of books, and as such tackles a narrow subject, but from the very basics to an expert level. I found that the process of building from simple basics to a more in depth discussion of the technical details was accessible both to those who might have inherited a system and need to know how to properly configure it to minimise risk, and those who have a more detailed understanding.<br />
<br />
Whilst this book does tackle data recovery, it is more about prevention and putting yourself in the position of avoiding the disaster in the first place. The process used in this book allows you to take a look at the reasons why you would choose to manage your transaction log in various ways, what implications this might have, and how that might impact upon your service level agreements (time and amount of potential data loss in a disaster) to the business. It deals with why and how to back up the transaction log in order to minimise data loss, and the implications of a corruption or loss of a log backup.<br />
<br />
The book has detailed chapters on Full and Bulk Logged recovery modes, and even deals with Simple mode, and the ways in which the transaction log is used in each of them. It also goes through common scenarios (run away growth of a transaction log, disaster recovery, switching modes) to examine the implications and ways forward for each. It also looks at how to optimise your log so that you get the best performance for your intended use of the system, and how to monitor the log to check it is working optimally.<br />
<br />
The style of the book is very much that of a taught example, and as such it allows the reasons why a course of action is desirable (or not) to be reinforced with a worked example, and specifies where decisions have been taken that aren't ideal for a production environment.<br />
<br />
This is the sort of book I would give to a Junior DBA, to familiarise him or her with why transaction logs are configured as they are, and will keep for my own shelf to remind myself of the more technical details as to what is going on inside SQL Server in various log operations.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-46755411889323156142013-06-21T13:00:00.000+01:002013-06-21T13:00:00.569+01:00Adding sequential values to identical rowsI was recently presented with a requirement whereby I had a list of orders, with parts, and a list of orders with serial numbers with which I needed to update the original table.<br />
<br />
Let's look at the OrderedParts table, which contains a list of the parts used on a customer orders:<br />
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 205px;">
<colgroup><col style="mso-width-alt: 2596; mso-width-source: userset; width: 53pt;" width="71"></col>
<col style="width: 48pt;" width="64"></col>
<col style="mso-width-alt: 2560; mso-width-source: userset; width: 53pt;" width="70"></col>
</colgroup><tbody>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; height: 15pt; text-underline-style: none; width: 53pt;" width="71">OrderNo</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; text-underline-style: none; width: 48pt;" width="64">PartNo</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; text-underline-style: none; width: 53pt;" width="70">SerialNo</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1234</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">ABC1</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1234</td>
<td class="xl65" style="color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">ABC1</td>
<td class="xl65" style="color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1252</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">XYZ3</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1252</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">HHJ3</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;"></td>
</tr>
</tbody></table>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 205px;"><colgroup><col style="mso-width-alt: 2596; mso-width-source: userset; width: 53pt;" width="71"></col><col style="width: 48pt;" width="64"></col><col style="mso-width-alt: 2560; mso-width-source: userset; width: 53pt;" width="70"></col></colgroup><tbody>
</tbody></table>
<br />
This seems straightforward for order number 1252 as the parts are unique, but consider that the orders could be for multiple of the parts, and the only way of telling rows apart is with the serial number, as in order 1234.<br />
<br />
Here is our table of available parts with serial numbers:<br />
<br /><table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 135px;">
<colgroup><col style="width: 48pt;" width="64"></col>
<col style="mso-width-alt: 2596; mso-width-source: userset; width: 53pt;" width="71"></col>
</colgroup><tbody>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; height: 15pt; text-underline-style: none; width: 48pt;" width="64">PartNo</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; text-underline-style: none; width: 53pt;" width="71">SerialNo</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">ABC1</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">X112</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">ABC1</td>
<td class="xl65" style="color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">X113</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">XYZ3</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">I330</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">HHJ3</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">K283</td>
</tr>
</tbody></table>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 134px;"><colgroup><col style="width: 48pt;" width="64"></col><col style="mso-width-alt: 2560; mso-width-source: userset; width: 53pt;" width="70"></col></colgroup><tbody>
</tbody></table>
<br />
We will want the OrderParts table to be updated such that the serial numbers X112 and X113 each appear once in it, showing those serial numbers are used in the order.<br />
<br />
<div>
This presents us with a problem in the traditional update statement; how do we join these tables together such that we can update the records correctly?</div>
<div>
<br /></div>
<div>
Well, let us consider the an update statement:</div>
<div>
<br />
<code style="font-size: 12px;"><span style="color: black;"><br />
</span><span style="color: green;">/* Code snippet coded by Dave Green @d_a_green - June 2012*/<br />
/* Set up tables*/</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">DECLARE </span><span style="color: #434343;">@OrderedParts </span><span style="color: blue;">TABLE<br />
</span><span style="color: grey;">(<br />
</span><span style="color: black;">OrderNo </span><span style="color: blue;">INT </span><span style="color: grey;">NOT NULL ,<br />
</span><span style="color: black;">PartNo </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">) NOT NULL ,<br />
</span><span style="color: black;">SerialNo </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">) NULL<br />
)<br />
</span><span style="color: blue;">INSERT </span><span style="color: #434343;">@OrderedParts</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">VALUES </span><span style="color: grey;">( </span><span style="color: black;">1234</span><span style="color: grey;">, </span><span style="color: red;">'ABC1'</span><span style="color: grey;">, NULL )<br />
, ( </span><span style="color: black;">1234</span><span style="color: grey;">, </span><span style="color: red;">'ABC1'</span><span style="color: grey;">, NULL )<br />
, ( </span><span style="color: black;">1252</span><span style="color: grey;">, </span><span style="color: red;">'XYZ3'</span><span style="color: grey;">, NULL )<br />
, ( </span><span style="color: black;">1252</span><span style="color: grey;">, </span><span style="color: red;">'HHJ3'</span><span style="color: grey;">, NULL )<br />
</span><span style="color: blue;"><br />
</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">DECLARE </span><span style="color: #434343;">@Stock </span><span style="color: blue;">TABLE<br />
</span><span style="color: grey;">(<br />
</span><span style="color: black;">PartNo </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">) NOT NULL ,<br />
</span><span style="color: black;">SerialNo </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">) NOT NULL<br />
)</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">INSERT </span><span style="color: #434343;">@Stock<br />
</span><span style="color: grey;">( </span><span style="color: black;">PartNo</span><span style="color: grey;">, </span><span style="color: black;">SerialNo </span><span style="color: grey;">)</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">VALUES </span><span style="color: grey;">( </span><span style="color: red;">'ABC1'</span><span style="color: grey;">, </span><span style="color: red;">'X112' </span><span style="color: grey;">)<br />
, ( </span><span style="color: red;">'ABC1'</span><span style="color: grey;">, </span><span style="color: red;">'X113' </span><span style="color: grey;">)<br />
, ( </span><span style="color: red;">'XYZ3'</span><span style="color: grey;">, </span><span style="color: red;">'I330' </span><span style="color: grey;">)<br />
, ( </span><span style="color: red;">'HHJ3'</span><span style="color: grey;">, </span><span style="color: red;">'K283' </span><span style="color: grey;">)<br />
<br />
</span><span style="color: green;">--Try joining the tables on the part number<br />
</span><span style="color: blue;">UPDATE </span><span style="color: #434343;">@OrderedParts</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">SET </span><span style="color: black;">SerialNo </span><span style="color: blue;">= </span><span style="color: black;">Stock.SerialNo</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">FROM </span><span style="color: #434343;">@OrderedParts<br />
</span><span style="color: blue;">INNER JOIN </span><span style="color: #434343;">@Stock </span><span style="color: black;">Stock </span></code><br />
<code style="font-size: 12px;"><span style="color: #434343;"> </span><span style="color: blue;">ON </span><span style="color: black;">[@OrderedParts].PartNo </span><span style="color: blue;">= </span><span style="color: black;">Stock.PartNo<br />
</span><span style="color: blue;"><br /></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">SELECT </span><span style="color: grey;">* </span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">FROM </span><span style="color: #434343;">@OrderedParts</span></code><br />
<br /></div>
<div>
That produces the result:<br />
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 205px;">
<colgroup><col style="mso-width-alt: 2596; mso-width-source: userset; width: 53pt;" width="71"></col>
<col style="width: 48pt;" width="64"></col>
<col style="mso-width-alt: 2560; mso-width-source: userset; width: 53pt;" width="70"></col>
</colgroup><tbody>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; height: 15pt; text-underline-style: none; width: 53pt;" width="71">OrderNo</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; text-underline-style: none; width: 48pt;" width="64">PartNo</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; text-underline-style: none; width: 53pt;" width="70">SerialNo</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1234</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">ABC1</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">X112</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1234</td>
<td class="xl65" style="color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">ABC1</td>
<td class="xl65" style="color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">X112</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1252</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">XYZ3</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">I330</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1252</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">HHJ3</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">K283</td>
</tr>
</tbody></table>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 205px;"><colgroup><col style="mso-width-alt: 2596; mso-width-source: userset; width: 53pt;" width="71"></col><col style="width: 48pt;" width="64"></col><col style="mso-width-alt: 2560; mso-width-source: userset; width: 53pt;" width="70"></col></colgroup><tbody>
</tbody></table>
<br />
Which, as you can see has done the instances where only one instance of each part was used in the order (order 1252) , but has not covered instances where multiple of the same part was used in the order (order 1234).<br />
<br />
So how can we get round this?<br />
<br />
Well, I chose to think about them in terms of "we want the first matching part in the first row, and the second matching part in the second row". This got me thinking about how we arrange the rows - numerically, and about using the <a href="http://msdn.microsoft.com/en-us/library/ms186734.aspx" target="_blank">ROW_NUMBER()</a> function.<br />
<br />
So, we can easily add a row number by using something like:<br />
<br />
<code style="font-size: 12px;"><span style="color: black;"><br />
</span><span style="color: blue;">SELECT </span><span style="color: grey;">*,</span><span style="color: black;">ROW_NUMBER</span><span style="color: grey;">() </span><span style="color: blue;">OVER </span><span style="color: grey;">(</span><span style="color: blue;">ORDER BY </span><span style="color: black;">PartNo</span><span style="color: grey;">)</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">FROM </span><span style="color: #434343;">@OrderedParts</span></code><br />
<br />
<br />
So - we need to give both tables a row number, and then use this as part of our join criteria for the update:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">UPDATE </span><span style="color: black;">OrderedParts</span></code><br />
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue;">SET </span><span style="color: black;">SerialNo </span><span style="color: blue;">= </span><span style="color: black;">Stock.SerialNo </span><span style="color: blue;"><br /></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">FROM </span><span style="color: grey;">( </span><span style="color: blue;">SELECT </span><span style="color: grey;">*,<br /> </span><span style="color: black;">ROW_NUMBER</span><span style="color: grey;">() </span><span style="color: blue;">OVER </span><span style="color: grey;">( </span><span style="color: blue;">ORDER BY </span><span style="color: black;">PartNo </span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">RowNumber<br /> </span><span style="color: blue;">FROM </span><span style="color: #434343;">@OrderedParts<br /> </span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">OrderedParts</span><span style="color: blue;"><br /></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">INNER JOIN </span><span style="color: grey;">( </span><span style="color: blue;">SELECT </span><span style="color: grey;">*,<br /> </span><span style="color: black;">ROW_NUMBER</span><span style="color: grey;">() </span><span style="color: blue;">OVER </span><span style="color: grey;">( </span><span style="color: blue;">ORDER BY </span><span style="color: black;">PartNo </span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">RowNumber<br /> </span><span style="color: blue;">FROM </span><span style="color: #434343;">@Stock<br /> </span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">Stock </span><span style="color: blue;">ON </span><span style="color: black;">OrderedParts.PartNo </span><span style="color: blue;">= </span><span style="color: black;">Stock.PartNo<br /> </span><span style="color: grey;">AND </span><span style="color: black;">OrderedParts.RowNumber </span><span style="color: blue;">= </span><span style="color: black;">Stock.RowNumber<br /></span></code><br />
<br />
<br />
Using the row number as a joining criteria in this way gives the answer:<br />
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 205px;">
<colgroup><col style="mso-width-alt: 2596; mso-width-source: userset; width: 53pt;" width="71"></col>
<col style="width: 48pt;" width="64"></col>
<col style="mso-width-alt: 2560; mso-width-source: userset; width: 53pt;" width="70"></col>
</colgroup><tbody>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; height: 15pt; text-underline-style: none; width: 53pt;" width="71">OrderNo</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; text-underline-style: none; width: 48pt;" width="64">PartNo</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: solid none; border-top-color: rgb(79, 129, 189); border-top-width: 0.5pt; color: #366092; font-family: Calibri; font-size: 11pt; font-weight: 700; text-underline-style: none; width: 53pt;" width="70">SerialNo</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1234</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">ABC1</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">X112</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1234</td>
<td class="xl65" style="color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">ABC1</td>
<td class="xl65" style="color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">X113</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1252</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">XYZ3</td>
<td class="xl65" style="background-color: #dce6f1; background-position: initial initial; background-repeat: initial initial; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">I330</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; height: 15pt; text-underline-style: none;">1252</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">HHJ3</td>
<td class="xl65" style="border-bottom-color: rgb(79, 129, 189); border-bottom-width: 0.5pt; border-style: none none solid; color: #366092; font-family: Calibri; font-size: 11pt; text-underline-style: none;">K283</td>
</tr>
</tbody></table>
<br />
This has achieved our desired result, of each available serial number being used uniquely within the order.</div>
Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-43051422047122583022013-05-14T19:24:00.000+01:002013-05-14T19:24:25.149+01:00T-SQL Tuesday #42 - The Long and Winding Road<br />
Have you heard of <a href="https://twitter.com/search?q=%23tsql2sday" target="_blank">T-SQL Tuesday</a>? I hadn't until recently, but it's a series of monthly blogs started by Adam Machanic ( <a href="http://sqlblog.com/blogs/adam_machanic/" target="_blank">blog</a> | <a href="http://www.twitter.com/AdamMachanic" target="_blank">twitter</a> ) some years ago (well, 42 months ago, one presumes) for all to participate in. The idea is that each month someone hosts it, that person sets a topic which we all post to and link our post to twitter using the hash tag <a href="https://twitter.com/search?q=%23tsql2sday" target="_blank">#tsql2sday</a>.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://wendyverse.blogspot.co.uk/2013/05/its-time-for-t-sqltuesday-42-long-and.html" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;" target="_blank"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj62HHf49cMrqkG6sI2wQfrUs6PCf4BGRhvBSolGpc3Qdy5fMYJSHmsI8hiSZcdvlkyM36Zgh3_fHBLwgRBxBvNGbKChq7dYRd3vEFs-08ckXm45eMVOiH_pGm9K9936WT2_qN53Woo8CUR/s1600/TSQL2sDay150x150.jpg" /></a></div>
<br />
This month, the topic is set ("hosted") by SQL MVP Wendy Pastrick ( <a href="http://wendyverse.blogspot.co.uk/" target="_blank">blog</a> | <a href="https://twitter.com/wendy_dance" target="_blank">twitter</a> ). She's entitled it "<a href="http://wendyverse.blogspot.co.uk/2013/05/its-time-for-t-sqltuesday-42-long-and.html" target="_blank">The Long and Winding Road</a>" and challenged us all to post about how you got to where you are, and what you plan to do next, noting what technologies are key to your interests or successes. Here's my story - and first T-SQL Tuesday post...<br />
<br />
I've been using databases (predominantly of the Microsoft variety) for more years than I care to remember now, having started with data entry before rapidly moving on to programming Access (v95? or possibly as early as v.2.0) databases as part of my first full time job (not what I thought I'd be doing, but a good use of what was until then just a hobby). I soon moved onto using SQL Server as well, in about the year 2000. I've dabbled with other technologies, spent time programming, dealt with business administration, but generally it was data management that I enjoyed, and that remains my area of focus. The technology has changed somewhat, but the core concepts remain remarkably unchanged. I've changed how I work, have worked with large companies and small, and every challenge is different.<br />
<br />
Fundamentally I think that the ability to speak to all levels of the business is required as a data professional - you need to be able to connect with the CEO who wants a key metrics view of the business' information, the data entry clerk who has to record the information, the developer who has to build the system, the IT department who have to manage it and the CFO who wants it all done for no money. Of course none of them want to hear that the solution isn't absolutely perfect (ideally with respect to what is needed in the future), or needs maintenance - but that's all part of the fun!<br />
<br />
If I had to say what technology had changed my working life more than any other, it's probably the Internet. Not the protocols, or changes to how I interact with data, but the ability to interact with my peers on demand, and help learn by engaging with other professionals - and amateurs - as they look at their own data challenges. It's introduced me to many more fellow-professionals than I thought existed, the talked about <a href="https://twitter.com/search?q=%23sqlfamily" target="_blank">#sqlfamily</a>.<br />
<br />
I recently posted about <a href="http://d-a-green.blogspot.co.uk/2013/04/who-are-you.html" target="_blank">how the Internet can help you find your next role</a>, and also<a href="http://d-a-green.blogspot.co.uk/2013/01/get-your-learn-on-in-2013.html" target="_blank"> how to connect with the community</a>, and I would also point to Q&A sites like <a href="http://ask.sqlservercentral.com/">ask.sqlservercentral.com</a> and <a href="http://stackoverflow.com/" target="_blank">stack overflow</a>. They're a great source of knowledge and expertise if you need inspiration on a problem, and I have found that by digging into other peoples' questions that interest me I've learned a lot myself. This helps steer my career, by way of introducing me to new technologies I may otherwise not have looked at.<br />
<br />
What's next for me? I'm not honestly sure. I've just (in the last week) had my first child, and so a lot is changing right now on a personal level - so it seems a good time to re-appraise my career priorities. Whatever is next, I'm sure it won't be something I could predict now, except that it will be something exciting...<br />
<br />Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0tag:blogger.com,1999:blog-1789435094629474988.post-61954505261471011362013-04-21T23:15:00.001+01:002013-04-21T23:15:52.355+01:00Who are you?As the economy continues on it's somewhat flat course, there are a number of data professionals who are out of work, or finding it more difficult to find their next role.<br />
<br />
A couple of people I know recently asked me how to give themselves an edge when looking for a new role - and my answer was simple - start a blog. I view it as one type of continuous professional development. The reasons to write about the things that you enjoy are many; here are a few:<br />
<br />
<u>You dedicate time to it</u><br />
<br />
If you have a family, or simply a long commute, it can be difficult to find time outside the normal working day to allow you to explore areas of technology that you don't get to play with as part of your day job. I think this is essential to improving - not all learning can be as a result of your job, and often blogging allows me to explore something that interests me.<br />
<br />
Don't be over ambitious by trying to write too frequently at first - you can always write more. Take some time to get used to writing. I usually only do one post a month, but that's right for me. I also write occasional articles on other sites, and contribute via forums etc.<br />
<br />
<u>You learn something more when you write about it</u><br />
<br />
To fully explain something, you need to think about it. All too often we only get time to scratch the surface of topics at the periphery of our focus, but sitting down and writing something detailed about it can force you to go into more detail about what's going on, and explore how to use it properly.<br />
<br />
<u>You build an on-line presence.</u><br />
<br />
Troy Hunt recently wrote an <a href="http://www.troyhunt.com/2013/02/the-ghost-who-codes-how-anonymity-is.html" target="_blank">article about the ghost coder</a>, and I think his point is a good one - the best way to get a positive interview experience is to have the battle half-won before you go in. My preferred way to do this is to allow those interviewing you to get to know how you think, and how you approach problems through your writing.<br />
<br />
Of course, there are other ways to build an on-line presence - <a href="http://ask.sqlservercentral.com/" target="_blank">forums</a>, <a href="https://twitter.com/d_a_green" target="_blank">Twitter</a>, <a href="http://www.linkedin.com/pub/dave-green/19/442/49" target="_blank">LinkedIn</a>, etc. - and I use those too - but your blog is the presence that you can control the topic of most closely, so can most steer to your chosen direction.<br />
<br />
There are of course some difficulties - choosing a topic, for one. Perhaps set yourself a project, and blog about your progress. Perhaps learn something online, and blog about it - that will tell you if you've learned it properly, and give you an opportunity to cement your new knowledge. You could take part in a <a href="http://sqlblog.com/blogs/adam_machanic/archive/2009/11/30/invitation-to-participate-in-t-sql-tuesday-001-date-time-tricks.aspx" target="_blank">T-SQL Tuesday</a>, which was started a few years ago by Adam Machanic to provide a topic for lots of SQL bloggers to take part in, or perhaps pick up a forum or twitter discussion to blog about in more detail. Perhaps you will simply pick something that occurs to you whilst you're going about your day.<br />
<br />
Whether you choose to take me up on the blog idea, or <a href="http://d-a-green.blogspot.co.uk/2013/01/get-your-learn-on-in-2013.html" target="_blank">contribute to the community</a> another way, it is something I find to be quite rewarding and hope that you do too.Dave Greenhttp://www.blogger.com/profile/17808291619084046049noreply@blogger.com0