tag:blogger.com,1999:blog-90385668467061157082024-03-17T00:24:15.273-07:00Building Better SoftwareAnonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.comBlogger51125tag:blogger.com,1999:blog-9038566846706115708.post-73927984722786016222018-04-03T18:53:00.001-07:002018-04-03T18:53:46.875-07:00Building Enterprise Grade Web APIs - Use Feature FoldersOne of the most important attributes of any code we write is maintainability. Over the lifetime of a piece of code, it will be debugged, modified and upgraded numerous times, often by developers who were not on the original team that wrote the code. <br />
<br />
One of the most important aspects of maintainability is that the developer maintaining the code needs to be able to easily find what they are looking for. This is where feature folders come in. When using feature folders in our API, we don't use the traditional folders named Models and Controllers. Instead, we name our folders after the endpoints of our API (the features) and place all the code related to to that endpoint (feature) within that folder. Here is an example:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipXGlrwwxYMuQREKnKKkTi8Ts4B8vXpH8DJoNfWfI2JRv4Y2_sTKJV8EwS9muAbaSyBEBvKvEROKp1ZXmNozhTGHL2cT6RxYOrdgzSeVOneiN17F4rj6S6MqkrEiJIZqXEtcdcH-NuClpB/s1600/FeatureFolders.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="600" data-original-width="401" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipXGlrwwxYMuQREKnKKkTi8Ts4B8vXpH8DJoNfWfI2JRv4Y2_sTKJV8EwS9muAbaSyBEBvKvEROKp1ZXmNozhTGHL2cT6RxYOrdgzSeVOneiN17F4rj6S6MqkrEiJIZqXEtcdcH-NuClpB/s640/FeatureFolders.PNG" width="425" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
As you have probably guessed, this is an API related to Food Trucks. If you looked at the endpoints for the API you would see URLs like <span style="font-family: Courier New, Courier, monospace;"><b>/api/FoodTrucks</b></span> and <span style="font-family: Courier New, Courier, monospace;"><b>/api/Locations</b></span>. It is pretty easy to guess where the code is for those endpoints, in the FoodTrucks and Locations folders respectively. For child endpoints, that is, endpoints like <span style="font-family: Courier New, Courier, monospace;"><b>/api/FoodTrucks/{foodTruckId}/Reviews</b></span>, I use a dot notation on the folder name like FoodTrucks.Reviews. As you can see though, it is very easy to map from what an endpoint is to where the code is that supports that endpoint.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
As you see in the screenshot above, I have expanded the Locations folder so we can have a look inside. We see the following:</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<ul>
<li>The controller - LocationsController</li>
<li>The view models used by this feature - LocationModel, CreateLocationModel and UpdateLocationModel</li>
<li>Two validator classes that are used to implement validation rules related to this feature - CreateLocationModelValidator and UpdateLocationModelValidator</li>
<li>The AutoMapper profile used to map objects related to this feature - LocationsAutoMapperProfile.</li>
</ul>
<div>
Without even looking inside the files at the code, you already have a pretty good idea about what is going on. And you know where everything is related to this feature. Contrast this with the traditional folder layout in MVC projects:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuBdRoR2vIxqe-LtfUhKSkySVSQhCTWN_TvM2wUwO-Dbi_acWrnsaXvOWFGJMaT4RTn70UdHY2BUFNOKEliIvIooKmn1M6Cs-jP3eAtlk5yznDi3DeAocj2iACKyzuxZ5NI1AmhVRoKUjb/s1600/TraditionalFolders.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="550" data-original-width="352" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuBdRoR2vIxqe-LtfUhKSkySVSQhCTWN_TvM2wUwO-Dbi_acWrnsaXvOWFGJMaT4RTn70UdHY2BUFNOKEliIvIooKmn1M6Cs-jP3eAtlk5yznDi3DeAocj2iACKyzuxZ5NI1AmhVRoKUjb/s640/TraditionalFolders.png" width="408" /></a></div>
<div>
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Here if we need to debug and then make a change to the Locations endpoint, we have to navigate multiple folders to do so. In fact, I'm not even showing the validator classes or the AutoMapper profile classes in this screenshot because they don't fit on the screen. Sure, we could put our validators in with our models in the ApiModels folder, but for even for this relatively API, we are starting to get some folders that are unwieldy.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
There is another benefit of using feature folders. Since all of the code used for a feature is in one folder, the folder (and thereby the namespace) forms a boundary in our code that delineates this feature. If we find ourselves needing to reach across folders, we are crossing a boundary in our code base and we have an indication that something is wrong. This helps to discourage us from sharing model objects across two or more controllers where the object would have more than one responsibility. Having boundaries defined like this in our code helps us be more modular and keep everything where it belongs.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<h3 style="clear: both; text-align: left;">
Summary</h3>
<div class="separator" style="clear: both; text-align: left;">
I think feature folders are the way to go. This is a pattern that you actually see quite a bit in the Angular and React communities, and I think there is no reason not to adopt the same in ASP.NET projects. I think we end up with a much cleaner organization of the code and it is easier to find what you are looking for. So for your next project, give feature folders a try. I think once you complete a project using feature folders you will agree it is the way to go.</div>
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com47tag:blogger.com,1999:blog-9038566846706115708.post-69941383212241744752018-04-01T11:27:00.001-07:002018-04-03T18:21:48.646-07:00Implementing HATEOAS Functionality in an ASP.NET Core Web API<a href="https://en.wikipedia.org/wiki/HATEOAS" target="_blank">HATEOAS</a> (Hypertext As The Engine Of Application State) is a feature of REST based APIs in which the server provides not just data in its responses, but hyperlinks to related data and/or actions that a client can be accessed on the API. This allows the client to self discover what operations are available on an API and dynamically navigate through them.<br />
<br />
An API response that implements HATEOAS would look like the following:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19</pre>
</td><td><pre style="line-height: 125%; margin: 0;">{
<span style="color: #007700;">"foodTruckId"</span>: <span style="color: #0000dd; font-weight: bold;">4</span>,
<span style="color: #007700;">"name"</span>: <span style="background-color: #fff0f0;">"Rice Bowl"</span>,
<span style="color: #007700;">"description"</span>: <span style="background-color: #fff0f0;">"Asian favorites served in a bowl of rice"</span>,
<span style="color: #007700;">"website"</span>: <span style="background-color: #fff0f0;">"http://foodtrucknation.com/RiceBowl"</span>,
<span style="color: #007700;">"lastModifiedDate"</span>: <span style="background-color: #fff0f0;">"2017-10-23T01:57:37.185"</span>,
<span style="color: #007700;">"tags"</span>: [
<span style="background-color: #fff0f0;">"Asian"</span>,
<span style="background-color: #fff0f0;">"Chinese Food"</span>
],
<span style="color: #007700;">"reviewCount"</span>: <span style="color: #0000dd; font-weight: bold;">4</span>,
<span style="color: #007700;">"reviewAverage"</span>: <span style="color: #0000dd; font-weight: bold;">4</span>,
<span style="color: #007700;">"socialMediaAccounts"</span>: [],
<span style="color: #007700;">"meta"</span>: {
<span style="color: #007700;">"self"</span>: <span style="background-color: #fff0f0;">"http://localhost:8000/api/FoodTrucks/4"</span>,
<span style="color: #007700;">"reviews"</span>: <span style="background-color: #fff0f0;">"http://localhost:8000/api/FoodTrucks/4/Reviews"</span>,
<span style="color: #007700;">"schedules"</span>: <span style="background-color: #fff0f0;">"http://localhost:8000/api/FoodTrucks/4/Schedules"</span>
}
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
In this case, I've grouped all of the hyperlinks under a property named meta, and we can see in this case I'm providing links to additional data about the food truck, namely where to find the reviews and schedule for this food truck as well as the URL for this food truck itself.<br />
<br />
Why would you want to implement HATEOAS, when after all many APIs work just fine without it? One reason is because it is part of the REST spec, but I think there are better reasons than simply "it is part of the spec". I think what is nice is the self discovery aspect, especially for a developer who is new to your API. They can easily see how the different endpoints and objects relate to each other. They can follow these links in their browser and walk through your API discovering the various relationships as they go. Yes, there are tools like Swagger, but it can be really powerful to walk through an API and see real data as you click on links to different end points. The hope is that one day there are automated clients can do this, but today, HATEOAS still helps the most important client of your API, the developer who is consuming it.<br />
<br />
That said, one of the barriers to implementing HATEOAS is the ability to generate the correct URLs in your response. In this post, I am going to show how this can be easily done in ASP.NET Core.<br />
<br />
<h3>
Sample Project</h3>
All of this code is implemented in my Food Truck Nation API that is available on Github at the following URL.<br />
<br />
<div style="text-align: center;">
<a href="https://github.com/DavidCBerry13/FoodTruckNationApi/"><span style="font-size: large;">https://github.com/DavidCBerry13/FoodTruckNationApi/</span></a></div>
<br />
<h3>
Assumptions</h3>
Lets set the stage with some assumptions so we are all talking the same language.<br />
<br />
<ul>
<li>This blog post and the sample code is currently using ASP.NET Core 2.0. The solution should work for ASP.NET Core 1.1 as well, but you may need a minor tweak here and there.</li>
<li>I am assuming you have separate view model objects that you use to return data back to the client and you aren't just returning your domain objects. By domain objects, I mean whatever you are querying out of your database and working with in your application.</li>
<li>This post and the sample code uses <a href="https://automapper.org/" target="_blank">AutoMapper</a> to map between the domain objects and the view models. If you roll your own mapping code or use a different mapping framework, many of the lessons should still apply, but of course the implementation will be different.</li>
</ul>
<br />
<h3>
Model Objects</h3>
The first thing we need to do is define properties in the model objects that we will return to the client to hold the hyperlinks we want to include. For my Food Truck Model, here is what my response model object looks like:<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26</pre>
</td><td><pre style="line-height: 125%; margin: 0;"> <span style="color: #888888;">/// <summary></span>
<span style="color: #888888;">/// Model class to represent the food truck data sent back to the client</span>
<span style="color: #888888;">/// </summary></span>
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #008800; font-weight: bold;">class</span> <span style="color: #bb0066; font-weight: bold;">FoodTruckModel</span>
{
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #333399; font-weight: bold;">int</span> FoodTruckId { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> String Name { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> String Description { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> String Website { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> List<String> Tags { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #333399; font-weight: bold;">int</span> ReviewCount { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #333399; font-weight: bold;">double</span> ReviewAverage { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> DateTime LastModifiedDate { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> FoodTruckLinks Meta { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #557799;">#region Nested Types</span>
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #008800; font-weight: bold;">class</span> <span style="color: #bb0066; font-weight: bold;">FoodTruckLinks</span>
{
<span style="color: #008800; font-weight: bold;">public</span> String Self { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> String Reviews { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> String Schedules { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
}
<span style="color: #557799;">#endregion</span>
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
The <span style="font-family: "courier new" , "courier" , monospace;">Meta</span> property is the property we want to pay attention to, as it contains a <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruckLinks</span> </b>object that groups together all of the hyperlinks we want to send back with this model object. The <b><span style="font-family: "courier new" , "courier" , monospace;">Reviews</span> </b>and <span style="font-family: "courier new" , "courier" , monospace;"><b>Schedules</b></span> properties on the <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruckLinks</span> </b>object will all contain hyperlinks to their respective resources while the <b><span style="font-family: "courier new" , "courier" , monospace;">Self</span> </b>property will contain a link (the resource identifier) for this object.<br />
<br />
The idea of using the property name <span style="font-family: "courier new" , "courier" , monospace;">Meta</span> came from another talk I saw, though I don't remember which one. Whatever name you use though, I think it is a good idea to group all of the links you want to provide together in an object like this and to give them a consistent name on your model objects. This way the client knows where to look for this information and it is all grouped together in one place.<br />
<br />
Finally, you can see that I model my <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruckLinks</span> </b>object as a nested (inner) class. This is because the <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruckLinks</span> </b>object (and all my other Link objects) really don't have any purpose outside of the context of their parent class. Therefore, I modeled them as nested classes to reinforce the notion that this object really belongs in and exists just in the context of its parent (owning) object. You do not have to model you link objects this way, but I find that this a useful technique to use.<br />
<br />
<h3>
Creating URLs in ASP.NET Core</h3>
One of the major challenges is how to generate correct URLs in your application. Fortunately, ASP.NET Core includes a built in Url Helper class that will help create URLs for us. This class knows about the protocol, server name and the directory where our API is deployed so it can create a proper URL for us without us having to put all of these details together ourselves and then perform some string concatenation to create a URL. Being able to use a built in class to create our URLs not only saves us a bunch of work, but is also more reliable because we can be confident that Microsoft has properly handled all of the corner cases that we might encounter.<br />
<br />
The Url Helper class is exposed as an interface, <a href="https://docs.microsoft.com/dotnet/api/microsoft.aspnetcore.mvc.iurlhelper?view=aspnetcore-2.0" target="_blank"><span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b></span></a>, and we can access the interface from the <b><span style="font-family: "courier new" , "courier" , monospace;"><a href="https://docs.microsoft.com/dotnet/api/microsoft.aspnetcore.mvc.controllerbase.url?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev15.query%3FappId%3DDev15IDEF1%26l%3DEN-US%26k%3Dk(Microsoft.AspNetCore.Mvc.ControllerBase.Url);k(DevLang-csharp)%26rd%3Dtrue%26f%3D255%26MSPPError%3D-2147217396&view=aspnetcore-2.0" target="_blank">Url</a></span> </b>property of our Controller classes. To generate an absolute URL, we want to use the <span style="font-family: "courier new" , "courier" , monospace;"><b>RouteUrl </b></span>method like this.<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5
6</pre>
</td><td><pre style="line-height: 125%; margin: 0;"> String selfUrl = <span style="color: #008800; font-weight: bold;">this</span>.Url.Link(GET_FOOD_TRUCK_BY_ID,
<span style="color: #008800; font-weight: bold;">new</span> { foodTruckId = foodTruckId });
String reviewsUrl = <span style="color: #008800; font-weight: bold;">this</span>.Url.Link(FoodTruckReviewsController.GET_ALL_FOOD_TRUCK_REVIEWS,
<span style="color: #008800; font-weight: bold;">new</span> { foodTruckId = foodTruckId });
String schedulesUrl = <span style="color: #008800; font-weight: bold;">this</span>.Url.Link(FoodTruckSchedulesController.GET_FOOD_TRUCK_SCHEDULE,
<span style="color: #008800; font-weight: bold;">new</span> { foodTruckId = foodTruckId });
</pre>
</td></tr>
</tbody></table>
</div>
<br />
You might be tempted to use the <b><span style="font-family: "courier new" , "courier" , monospace;">Action</span> </b>method on <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b></span>, but the <b><span style="font-family: "courier new" , "courier" , monospace;">Action</span> </b>method will only generate an absolute path of the URL (like <span style="font-family: "courier new" , "courier" , monospace;"><b>/api/FoodTrucks/4</b></span>), not the full URL including the server name and directory path unless you include these as arguments, and that is what we want to avoid. So <b><span style="font-family: "courier new" , "courier" , monospace;">Link</span> </b>is the method we want to use.<br />
<br />
The first argument is the name of the route. If you want to implement HATEOAS in your API, you are going to need to name your routes. To do this, you include the <span style="font-family: "courier new" , "courier" , monospace;">Name</span> property on your <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpGet</b></span>, <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpPost</b></span>, <b><span style="font-family: "courier new" , "courier" , monospace;">HttpPut</span> </b>and <b><span style="font-family: "courier new" , "courier" , monospace;">HttpDelete</span> </b>attributes that you use to decorate your like this:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11</pre>
</td><td><pre style="line-height: 125%; margin: 0;"> <span style="color: #888888;">/// <summary></span>
<span style="color: #888888;">/// Route name constant for route that gets an individual food truck</span>
<span style="color: #888888;">/// </summary></span>
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #008800; font-weight: bold;">const</span> String GET_FOOD_TRUCK_BY_ID = <span style="background-color: #fff0f0;">"GetFoodTruckById"</span>;
<span style="color: #0000cc;"> [HttpGet("{foodTruckId:int}", Name = GET_FOOD_TRUCK_BY_ID)]</span>
<span style="color: #008800; font-weight: bold;">public</span> IActionResult <span style="color: #0066bb; font-weight: bold;">Get</span>(<span style="color: #333399; font-weight: bold;">int</span> foodTruckId)
{
<span style="color: #888888;">// Action Code Here</span>
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
In this case, I'm using a constant to hold the value of the route name. More importantly, we see that we are including setting the <b><span style="font-family: "courier new" , "courier" , monospace;">Name</span> </b>property for the route in the <b><span style="font-family: "courier new" , "courier" , monospace;">HttpGet</span> </b>attribute so we can refer to this route in other parts of our code, namely the places we need to use the URL Resolver to create URLs for us.<br />
<br />
The second argument is an anonymous object of the route parameters. In our case, each of the three routes take just one parameter, <span style="font-family: "courier new" , "courier" , monospace;"><b>foodTruckId</b></span>, so that is the only parameter we include in our anonymous object. If however your route required multiple parameters, your anonymous object would have one property for each parameter in the route. Also note that the name of the property in the anonymous object must match the name of the parameter exactly. Our <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruckReviewsController</span> </b>expects a parameter of <span style="font-family: "courier new" , "courier" , monospace;"><b>foodTruckId</b></span>, so that is what we name our property. We can't name it just <b><span style="font-family: "courier new" , "courier" , monospace;">id</span> </b>and have things work. We need to match the names exactly.<br />
<br />
If we place the code snippet above in one of controllers and debug through it, we can see we get an actual URL:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiH_MratY676i_YoR3YE6skUmREVevpsmwdRyHHnblOlG-0x_B6UQYQ_0s7JD6Azv-MYfq93Lbk0ZgkBOQWIFuvb70CD0k-wUpkAM0yFPBMr7QF1OWP1OAM_quq5cKBwm020dn8fh1F8iV/s1600/UrlResolverHyperlink.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="201" data-original-width="1227" height="104" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiH_MratY676i_YoR3YE6skUmREVevpsmwdRyHHnblOlG-0x_B6UQYQ_0s7JD6Azv-MYfq93Lbk0ZgkBOQWIFuvb70CD0k-wUpkAM0yFPBMr7QF1OWP1OAM_quq5cKBwm020dn8fh1F8iV/s640/UrlResolverHyperlink.png" width="640" /></a></div>
Since I am debugging on my local machine, I get an address that includes localhost and the port number I am running on. But rest assured that when we are running on a server, <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlResolver </b></span>will create a URL with that servers name and the path to where the application is deployed correctly.<br />
<br />
Using code like this, we could map from our entity object to a model object in our controller like this.<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #0000cc;"> [HttpGet("{foodTruckId:int}", Name = GET_FOOD_TRUCK_BY_ID)]</span>
<span style="color: #008800; font-weight: bold;">public</span> IActionResult <span style="color: #0066bb; font-weight: bold;">Get</span>(<span style="color: #333399; font-weight: bold;">int</span> foodTruckId)
{
FoodTruck foodTruck = <span style="color: #008800; font-weight: bold;">this</span>.foodTruckService.GetFoodTruck(foodTruckId);
<span style="color: #008800; font-weight: bold;">if</span> (foodTruck == <span style="color: #008800; font-weight: bold;">null</span>)
{
<span style="color: #008800; font-weight: bold;">return</span> <span style="color: #008800; font-weight: bold;">this</span>.NotFound(<span style="color: #008800; font-weight: bold;">new</span> ApiMessageModel() { Message = <span style="background-color: #ffaaaa; color: red;">$</span><span style="background-color: #fff0f0;">"No food truck found with id {foodTruckId}"</span> });
}
<span style="color: #008800; font-weight: bold;">else</span>
{
String selfUrl = <span style="color: #008800; font-weight: bold;">this</span>.Url.Link(GET_FOOD_TRUCK_BY_ID,
<span style="color: #008800; font-weight: bold;">new</span> { foodTruckId = foodTruckId });
String reviewsUrl = <span style="color: #008800; font-weight: bold;">this</span>.Url.Link(FoodTruckReviewsController.GET_ALL_FOOD_TRUCK_REVIEWS,
<span style="color: #008800; font-weight: bold;">new</span> { foodTruckId = foodTruckId });
String schedulesUrl = <span style="color: #008800; font-weight: bold;">this</span>.Url.Link(FoodTruckSchedulesController.GET_FOOD_TRUCK_SCHEDULE,
<span style="color: #008800; font-weight: bold;">new</span> { foodTruckId = foodTruckId });
<span style="color: #333399; font-weight: bold;">var</span> model = <span style="color: #008800; font-weight: bold;">new</span> FoodTruckModel()
{
FoodTruckId = foodTruck.FoodTruckId,
Name = foodTruck.Name,
Description = foodTruck.Description,
Website = foodTruck.Website,
Meta = <span style="color: #008800; font-weight: bold;">new</span> FoodTruckModel.FoodTruckLinks()
{
Self = selfUrl,
Reviews = reviewsUrl,
Schedules = schedulesUrl
}
};
<span style="color: #008800; font-weight: bold;">return</span> <span style="color: #008800; font-weight: bold;">this</span>.Ok(model);
}
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
This code will return a model object like we saw at the beginning of this article which includes the hyperlinks to this resource (the Food Truck), the reviews for the food truck and the schedule for the food truck.<br />
<br />
What is not ideal is that this code requires us to do the mapping in directly in our controller action, and we would need to do this in each and every one of our actions that returned a model. Typically though, we use a library like <a href="https://automapper.org/" target="_blank">AutoMapper </a>to translate our data from domain objects into model objects. This is where things get a little tricky, so lets take a look at how we can make that work.<br />
<br />
<h3>
Creating Hyperlinks Using AutoMapper</h3>
When we created the URLs for our hyperlinks above, we were in an Action method of our Controller, so we had access to the <b><span style="font-family: "courier new" , "courier" , monospace;">IUrlHelper</span> </b>object. However, when our objects are being mapped inside of AutoMapper, but default, AutoMapper (or any other mapping library) will not know anything about <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b></span> or how to create URLs. Fortunately though, there is a way we can inject both the <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b></span> object and some custom mapping code into the mapping process such that we can create proper URLs when we are mapping our objects.<br />
<br />
One of the features of AutoMapper is the ability to define <a href="https://github.com/AutoMapper/AutoMapper/blob/master/docs/Custom-value-resolvers.md" target="_blank">Custom Value Resolvers</a>. These allow us to take control of the mapping process by writing a custom class that will handle the mapping process. If you look at the <span style="font-family: "courier new" , "courier" , monospace;"><a href="https://github.com/DavidCBerry13/FoodTruckNationApi/blob/master/src/FoodTruckNationApi/FoodTrucks/FoodTruckAutomapperProfile.cs" target="_blank">FoodTruckAutoMapperProfile</a></span> class, you will see this is exactly what I am doing. Here is the relevant code snippet:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31</pre>
</td><td><pre style="line-height: 125%; margin: 0;"> <span style="color: #008800; font-weight: bold;">this</span>.CreateMap<FoodTruck, FoodTruckModel.FoodTruckLinks>()
.ForMember(
dest => dest.Self,
opt => opt.ResolveUsing<UrlResolver, RouteUrlInfo>(src =>
<span style="color: #008800; font-weight: bold;">new</span> <span style="color: #0066bb; font-weight: bold;">RouteUrlInfo</span>()
{
RouteName = FoodTrucksController.GET_FOOD_TRUCK_BY_ID,
RouteParams = <span style="color: #008800; font-weight: bold;">new</span> { id = src.FoodTruckId }
}
)
)
.ForMember(
dest => dest.Reviews,
opt => opt.ResolveUsing<UrlResolver, RouteUrlInfo>(src =>
<span style="color: #008800; font-weight: bold;">new</span> <span style="color: #0066bb; font-weight: bold;">RouteUrlInfo</span>()
{
RouteName = Reviews.FoodTruckReviewsController.GET_ALL_FOOD_TRUCK_REVIEWS,
RouteParams = <span style="color: #008800; font-weight: bold;">new</span> { foodTruckId = src.FoodTruckId }
}
)
)
.ForMember(
dest => dest.Schedules,
opt => opt.ResolveUsing<UrlResolver, RouteUrlInfo>(src =>
<span style="color: #008800; font-weight: bold;">new</span> <span style="color: #0066bb; font-weight: bold;">RouteUrlInfo</span>()
{
RouteName = Schedules.FoodTruckSchedulesController.GET_FOOD_TRUCK_SCHEDULE,
RouteParams = <span style="color: #008800; font-weight: bold;">new</span> { foodTruckId = src.FoodTruckId }
}
)
);
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Rather than thinking of this code as mapping a <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruck</span> </b>object to a <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruckLinks</span> </b>object (line 1), think of this code as the code that will create the <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruckLinks</span> </b>object, and we need to use the <b><span style="font-family: "courier new" , "courier" , monospace;">FoodTruck</span> </b>object as input into this process for some of the data that we need. The three <b><span style="font-family: "courier new" , "courier" , monospace;">ForMember</span> </b>calls (lines 2, 12 and 22) all do the same process, just for different URLs, so lets walk through the first mapping, the <span style="font-family: "courier new" , "courier" , monospace;">Self</span> property that gets the hyperlink for the current object.<br />
<br />
Line 3 designates that we are populating the self property. Line 4 is where things get interesting. The <span style="font-family: "courier new" , "courier" , monospace;"><b>opt.ResolveUsing</b></span> call tells AutoMapper we want to map this value using a Custom Value Resolver class. The name of that Custom Value Resolver class is <span style="font-family: "courier new" , "courier" , monospace;"><b><a href="https://github.com/DavidCBerry13/FoodTruckNationApi/blob/master/src/Framework.ApiUtil/UrlResolver.cs" target="_blank">UrlHelper</a></b></span>, which we see as the first generic parameter and we'll take a look at in a moment. The second generic parameter allows us to specify a custom source object of data we need to pass into the value resolver. This is critical, because now we can pass additional information into the resolver like the name of the route and the any route parameters that we need in our mapping process. For our purposes, I've defined an object called <b><a href="https://github.com/DavidCBerry13/FoodTruckNationApi/blob/master/src/Framework.ApiUtil/RouteUrlInfo.cs" target="_blank"><span style="font-family: "courier new" , "courier" , monospace;">RouteUrlInfo</span> </a></b>that just acts as a container for the data we need in our custom mapping process.<br />
<br />
Finally, on lines 5 through 9 we see a lambda function that tells AutoMapper how to populate this custom source information before it calls the custom value resolver. In this case, we are creating and populating the <b><span style="font-family: "courier new" , "courier" , monospace;">RouteUrlInfo</span> </b>object with the route name and the parameters needed for the route.<br />
<br />
That may seem complicated on the surface, but all that is really happening is that we are writing a class with some custom mapping code that we need (<span style="font-family: "courier new" , "courier" , monospace;"><b>UrlHelper</b></span>) and then AutoMapper will invoke a method named <span style="font-family: "courier new" , "courier" , monospace;"><b>Resolve()</b></span> on that object when it needs to map that property. In addition, we get the chance to pass some custom data into this mapping process, so we do that by using a <b><span style="font-family: "courier new" , "courier" , monospace;">RouteUrlInfo</span> </b>object that is created via a lambda function right before the mapping is to occur.<br />
<br />
So lets take a look at the code for <b><span style="font-family: "courier new" , "courier" , monospace;">UrlResolver</span> </b>and see how what the custom mapping process looks like for creating a link.<br />
<br />
<h3>
UrlResolver</h3>
Below is the code for the <span style="font-family: "courier new" , "courier" , monospace;"><b>UrlResolver </b></span>class:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19</pre>
</td><td><pre style="line-height: 125%; margin: 0;"> <span style="color: #008800; font-weight: bold;">public</span> <span style="color: #008800; font-weight: bold;">class</span> <span style="color: #bb0066; font-weight: bold;">UrlResolver</span> : IMemberValueResolver<<span style="color: #333399; font-weight: bold;">object</span>, <span style="color: #333399; font-weight: bold;">object</span>, RouteUrlInfo, String>
{
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #0066bb; font-weight: bold;">UrlResolver</span>(IHttpContextAccessor httpContextAccessor)
{
<span style="color: #333399; font-weight: bold;">var</span> httpContext = httpContextAccessor.HttpContext;
<span style="color: #008800; font-weight: bold;">this</span>.urlHelper = (IUrlHelper)httpContext.Items[<span style="background-color: #fff0f0;">"URL_HELPER"</span>];
}
<span style="color: #008800; font-weight: bold;">private</span> <span style="color: #008800; font-weight: bold;">readonly</span> IUrlHelper urlHelper;
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #008800; font-weight: bold;">virtual</span> <span style="color: #333399; font-weight: bold;">string</span> <span style="color: #0066bb; font-weight: bold;">Resolve</span>(<span style="color: #333399; font-weight: bold;">object</span> source, <span style="color: #333399; font-weight: bold;">object</span> destination,
RouteUrlInfo sourceMember, <span style="color: #333399; font-weight: bold;">string</span> destMember, ResolutionContext context)
{
<span style="color: #008800; font-weight: bold;">return</span> <span style="color: #008800; font-weight: bold;">this</span>.urlHelper.Link(sourceMember.RouteName, sourceMember.RouteParams);
}
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
As you see, this class implements AutoMapper's <span style="font-family: "courier new" , "courier" , monospace;"><a href="http://docs.automapper.org/en/stable/Custom-value-resolvers.html" target="_blank"><b>IMemberValueResolver</b> </a></span>interface. Classes implementing <span style="font-family: "courier new" , "courier" , monospace;"><b>IMemberValueResolver </b></span>are defined with four generic parameters which are as follows.<br />
<ul>
<li>The type of the source object being mapped from. Since we want our resolver class to work for any domain object, we use the type <span style="font-family: "courier new" , "courier" , monospace;"><b>object</b></span>.</li>
<li>The type of the destination object being mapped to. Since we want to allow any model or links object, again we use the type <span style="font-family: "courier new" , "courier" , monospace;"><b>object</b></span>.</li>
<li>The type of the custom source object AutoMapper will pass in. This is our custom data carrier object <span style="font-family: "courier new" , "courier" , monospace;"><b>RouteUrlInfo </b></span>that was discussed earlier. This parameter allows us to design a custom object that has any other information we need for our mapping and pass it into the custom resolver. </li>
<li>The type of the object to be returned from this custom mapping process. In our case, we want a URL which is just a <span style="font-family: "courier new" , "courier" , monospace;"><b>string</b></span>.</li>
</ul>
<br />
The <b><span style="font-family: "courier new" , "courier" , monospace;">Resolve</span>()</b> method is what gets called when AutoMapper needs to resolve the custom value during the mapping of these objects. We see that this class has <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b></span> object that it grabs out of the <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpContext</b></span> and then it is able call the <span style="font-family: "courier new" , "courier" , monospace;"><b>Link()</b></span> method on <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper </b></span>just like before to create the hyperlink. To call the <span style="font-family: "courier new" , "courier" , monospace;"><b>Link()</b></span> method, we need the name of the route and any parameters for the route, and these come our of the <b>RouteUrlInfo</b> class, which is a class we define to help pass additional information into the mapping process.<br />
<br />
Here is the source code for the <span style="font-family: "courier new" , "courier" , monospace;"><b>RouteUrlInfo </b></span>class.<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5
6
7
8
9</pre>
</td><td><pre style="line-height: 125%; margin: 0;"> <span style="color: #008800; font-weight: bold;">public</span> <span style="color: #008800; font-weight: bold;">class</span> <span style="color: #bb0066; font-weight: bold;">RouteUrlInfo</span>
{
<span style="color: #008800; font-weight: bold;">public</span> String RouteName { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
<span style="color: #008800; font-weight: bold;">public</span> <span style="color: #333399; font-weight: bold;">object</span> RouteParams { <span style="color: #008800; font-weight: bold;">get</span>; <span style="color: #008800; font-weight: bold;">set</span>; }
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
The <span style="font-family: "courier new" , "courier" , monospace;"><b>RouteName </b></span>parameter is obvious enough, just the name of the route we want to create a Hyperlink for. The <span style="font-family: "courier new" , "courier" , monospace;"><b>RouteParams </b></span>property should be populated with a C# anonymous object that contains a property for each parameter needed in the route. If a route needs just one parameter, then the anonymous object will have just one property. If the route needs two parameters, then the object will have two properties and so on. Further, the property names in the anonymous object need to exactly match the names of the parameters on the route. In this way, this simple object can deliver all of the information needed to let our custom value resolver object know what it needs to know to create our route.<br />
<br />
There is one item that we have still not discussed, and that is of how the <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper </b></span>got into the <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpContext </b></span>in the first place.<br />
<h3>
Making <span style="font-family: "courier new" , "courier" , monospace;">IUrlHelper </span>Available to Your Custom Value Resolver Object</h3>
<div>
ASP.NET exposes an <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper </b></span>object to us as a property on our Controller classes as we saw in the first part of this post, so our challenge is to get this instance from our controller over to our custom value resolver (<span style="font-family: "courier new" , "courier" , monospace;"><b>UrlResolver</b></span>). The vehicle for sharing data throughout a request life cycle in ASP.NET is the <span style="font-family: "courier new" , "courier" , monospace;"><b>Items </b></span>property off of <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpContext</b></span>. What we need to do is every time before an action method runs, put a copy of <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper </b></span>in <span style="font-family: "courier new" , "courier" , monospace;">Items </span>collection of the current <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpContext</b></span>. The best way to do this is to override the <span style="font-family: "courier new" , "courier" , monospace;"><b>OnActionExecuting</b> </span>method on your Controller class:</div>
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5
6</pre>
</td><td><pre style="line-height: 125%; margin: 0;"> <span style="color: #008800; font-weight: bold;">public</span> <span style="color: #008800; font-weight: bold;">override</span> <span style="color: #008800; font-weight: bold;">void</span> <span style="color: #0066bb; font-weight: bold;">OnActionExecuting</span>(ActionExecutingContext context)
{
<span style="color: #008800; font-weight: bold;">base</span>.OnActionExecuting(context);
context.HttpContext.Items.Add(<span style="background-color: #fff0f0;">"URL_HELPER"</span>, <span style="color: #008800; font-weight: bold;">this</span>.Url);
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
I actually do this in a <span style="font-family: "courier new" , "courier" , monospace;"><b><a href="https://github.com/DavidCBerry13/FoodTruckNationApi/blob/master/src/Framework.ApiUtil/Controllers/BaseController.cs" target="_blank">BaseController</a> </b></span>class that I define, and then have all of my controller classes derive from the <span style="font-family: "courier new" , "courier" , monospace;"><b>BaseController</b> </span>class, so I know this automatically done for every action method I might define. We see all this is doing is grabbing the <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b> </span>object in the <span style="font-family: "courier new" , "courier" , monospace;"><b>Url</b> </span>property of the controller and putting it in <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpContext</b></span>'s <span style="font-family: "courier new" , "courier" , monospace;">Items </span>collection.<br />
<br />
Then, <span style="font-family: "courier new" , "courier" , monospace;"><b>UrlResolver</b> </span>can pull the pull the reference out in its constructor so it is available when the <span style="font-family: "courier new" , "courier" , monospace;"><b>Resolve()</b></span> method gets called. Here is the constructor for <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlResolver</b></span>:<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5</pre>
</td><td><pre style="line-height: 125%; margin: 0;"> <span style="color: #008800; font-weight: bold;">public</span> <span style="color: #0066bb; font-weight: bold;">UrlResolver</span>(IHttpContextAccessor httpContextAccessor)
{
<span style="color: #333399; font-weight: bold;">var</span> httpContext = httpContextAccessor.HttpContext;
<span style="color: #008800; font-weight: bold;">this</span>.urlHelper = (IUrlHelper)httpContext.Items[<span style="background-color: #fff0f0;">"URL_HELPER"</span>];
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
We actually can't inject <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpContext</b> </span>directly, but rather have to inject an <span style="font-family: "courier new" , "courier" , monospace;"><b>IHttpContextAccessor</b> </span>object. The good news is though that ASP.NET Core's built in DI framework knows how to take care of everything, so we just need to define our constructor like this and everything else is taken care of. And now, our custom value resolver (<span style="font-family: "courier new" , "courier" , monospace;"><b>UrlResolver</b></span>) will have access to the <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b> </span>object it needs to create hyperlinks.<br />
<h3>
Summary</h3>
This has been a long journey, but as when you look back, it actually isn't that hard to implement HATEOAS in your ASP.NET Core APIs. And you can actually do this rather seamlessly, where the incremental cost for each response model is just defining the appropriate mappings in you AutoMapper profile. So to summarize, here are the major steps.<br />
<br />
<ul>
<li>Leverage the built in <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b></span> to create your hyperlinks. This class knows how to form proper URLs and will take care of creating a link with the correct protocol, server, port, app directory, path and parameters for you.</li>
<li>Override the <span style="font-family: "courier new" , "courier" , monospace;"><b>OnActionExecuting()</b></span> method on your Controller class to put a reference to the <span style="font-family: "courier new" , "courier" , monospace;"><b>IUrlHelper</b> i</span>nto the Items collection of <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpContext</b></span>. This way it will be available to our AutoMapper custom value resolver object later. I suggest you define a base controller class and perform this logic there so it is consistently done for all your controllers and actions.</li>
<li>Create an AutoMapper custom value resolver object that contains the logic for how to create hyperlinks given a route name and its parameters. This is the <span style="font-family: "courier new" , "courier" , monospace;"><b>UrlResolver</b> </span>class, and this is where the custom mapping code lives that gets run when we need to create a hyperlink during the mapping process.</li>
<li>Define a simple data carrier object (<span style="font-family: "courier new" , "courier" , monospace;"><b>RouteUrlInfo</b></span>) that we can use to passes additional information like the route name and any parameters down to our mapping process when we need to map a domain object to a model object</li>
<li>Name your routes. This needs to be done so <b><span style="font-family: "courier new" , "courier" , monospace;">IUrlHelper</span></b> can find your routes by name. You do this by simply including the Name property in the <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpGet</b></span>, <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpPost</b></span>, <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpPut</b></span> or <span style="font-family: "courier new" , "courier" , monospace;"><b>HttpDelete</b></span> attributes on your action methods.</li>
<li>Define our custom mappings in the our AutoMapper profile objects. Basically, this is just some syntax to tell AutoMapper for each property in our Links object what route goes with the link and what the parameters are.</li>
</ul>
<div>
As you have seen, a lot of the code is code that you write once and just include in your project (or you can just copy it out of my project). Now the only thing you have to do each time is to name your routes and implement to correct mapping code in your AutoMapper profile. And that becomes very boiler plate. So for not a lot of work, your APIs can support HATEOAS.</div>
<div>
<br /></div>
<h3>
Is It Worth It?</h3>
<div>
This code actually took me a very long Saturday to develop as there were lots of pitfalls along the way. But now I have it, so all the hard work has been done. I like having the links in my responses just because I think it makes self-discovery a little easier for a new user of the API. Being able to walk the tree and understand how everything relates is really invaluable when you are trying to figure out how everything fits together. There are a few more bytes that end up going over the wire, but I will take that trade-off in order to make the API a little easier to use and understand. This is especially true since the heavy lifting is done and I have all of the supporting framework developed and ready to go.</div>
<div>
<br /></div>
<div>
So what are your thoughts? Leave them in the comments below or reach out on <a href="https://twitter.com/DavidCBerry13" target="_blank">Twitter</a>. I'd love to know if you find this as a useful approach.</div>
<div>
<br /></div>
<br />
<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com38tag:blogger.com,1999:blog-9038566846706115708.post-82679374670018900042017-09-10T14:13:00.000-07:002017-09-10T14:32:36.647-07:00MKE Dot Net 2017 Review<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglc3WxRhyphenhyphen0yyfS4eW05E9CH2Ky8AuyaePPJ8kLAc6UWoe7f_nbumfSeS7iasOD1CAL-jCQTVjEZg6NAjdXDd_1-aTJD7Zb-Z-yaiCxKvn4VideDJiTqHXMuOjdDy00IwvjLoY7bCCrHG5z/s1600/MKEDOTNET_logo.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="400" data-original-width="400" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglc3WxRhyphenhyphen0yyfS4eW05E9CH2Ky8AuyaePPJ8kLAc6UWoe7f_nbumfSeS7iasOD1CAL-jCQTVjEZg6NAjdXDd_1-aTJD7Zb-Z-yaiCxKvn4VideDJiTqHXMuOjdDy00IwvjLoY7bCCrHG5z/s200/MKEDOTNET_logo.png" width="200" /></a></div>
<br />
<br />
On Saturday, September 9, I attended the 3rd annual <a href="http://www.mkedotnet.com/" target="_blank">MKE DOT NET</a> conference here in Milwaukee. This is a one day event organized by <a href="http://www.centare.com/" target="_blank">Centare</a>, a local technology consultancy and focuses on Microsoft .NET and related technologies. I was also fortunate enough to be selected as a speaker for the second year in a row.<br />
<br />
I said this last year after attending, and I will say it again this year. This is one of the best organized and executed events that I have ever attended. The 2016 version of the event was excellent, and yet the MKE DOT NET team found a way to deliver an even better event this year. I say that as both a speaker and as an attendee. This is a real first class event, an event that every developer in Wisconsin and the Chicago area should be looking to attend in future years. You can travel farther and spend more money, but you will not find a better executed event than this one.<br />
<br />
For the rest of this post, I'll review various aspects of my experiences in the hopes they are useful to others when looking to attend or speak at this event in future years.<br />
<br />
<h3>
Speaker Experience</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD6gFy47Qzf5gjKLTaBiP-Ky_IOe_ElWHdYtDeK6TnuAbTB7RRfxpXvtxyQsZS_x8Ts-uzw_3eTog7CH5nXEbFBERwKHYtulMeS6prppP15oRhBvdFkX-6Kfo00N8g9aroB13IMnYxGxwe/s1600/IMG_20170910_152235073.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1600" data-original-width="1135" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD6gFy47Qzf5gjKLTaBiP-Ky_IOe_ElWHdYtDeK6TnuAbTB7RRfxpXvtxyQsZS_x8Ts-uzw_3eTog7CH5nXEbFBERwKHYtulMeS6prppP15oRhBvdFkX-6Kfo00N8g9aroB13IMnYxGxwe/s320/IMG_20170910_152235073.jpg" width="226" /></a></div>
<br />
The speaker experience at MKE DOT NET is in a word superior. <br />
<br />
The communication between the organizers and speakers is excellent. Speaker selections went out on June 22nd, well ahead of the event. In addition, two weeks before the event they sent out a speaker guide, answering questions about the A/V capabilities of the room, the schedule for the day and other general information about the event it is good to know beforehand. They also offered to pick up any out of town speakers at the airport if need be. This level of communication really makes the speaker's job easier, because a lot of the questions you have about an event, they have already proactively answered. <br />
<br />
Like last year, they hosted a speaker dinner on Friday night before the event. This year it was at <a href="https://thirdspacebrewing.com/" target="_blank">Third Space Brewing</a> in Milwaukee. This year, more speakers were able to attend, and the space was more conducive to being able to socialize with your fellow speakers than last year. Not to say last year wasn't good, it was very good. But last year was more of just a dinner where as this year was a dinner and an opportunity to get to know everyone, so this was excellent.<br />
<br />
As for the actual speaking part, this was also excellent. The event was held at the <a href="https://www.paysbig.com/" target="_blank">Potawatomi Casino</a> in Milwaukee, which also has a hotel and conference space. The A/V in the rooms was excellent. Projectors where built into the room, had a 16:9 aspect ration, were bright and projected onto a large screen built into the room. In short, the rooms were built for events like this, with high resolution projectors so everyone could easily see. This is a big win to have high quality A/V like this. The rooms sat around 75-80 people, so there was plenty of space for popular sessions. Finally, while I was setting up, two volunteers from the conference came in to check with me that I had everything I needed and everything was working. Again, a first class experience on the rooms.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3q5Cr5PoBS00DS0dU7ZovrQUQrrrqQCt1IlCyf3TlrczwQyYud1p8TEjNiwXXSyXWEchKRaHbO7iQh4TEbXM6SuSeX6kKKiTSVH8CrgDPtsnJ1_uAMobdPrgAKyGFA8WKrKXjuq28BZw8/s1600/IMG_20170909_135918764.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1600" data-original-width="900" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3q5Cr5PoBS00DS0dU7ZovrQUQrrrqQCt1IlCyf3TlrczwQyYud1p8TEjNiwXXSyXWEchKRaHbO7iQh4TEbXM6SuSeX6kKKiTSVH8CrgDPtsnJ1_uAMobdPrgAKyGFA8WKrKXjuq28BZw8/s320/IMG_20170909_135918764.jpg" width="180" /></a></div>
<br />
<br />
As for an opportunity to improve, this year talks where 45 minutes rather than the normal hour. I would prefer in future years they go back to the hour long format for talks. As a speaker, you often prepare your talks for an hour because that is what most events allocate. So I needed to do some trimming on my talk to have it fit in the allotted time, and even then, I still felt a little rushed on the last section. <br />
<br />
<h3>
Attendee Experience</h3>
Aside from giving my session, I was able to attend a number of other sessions at the event as well. Again, I think the attendee experience was superior.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWVomReyB1kGp9I_3Fkkh35ksnm71i25KGJzjWCMw7_9MdtzIsq9xFr2IfC0-xW-fVcKi-61R3pIVbJW8GfBPOoqNpsNi1zGcqXH3x-Da7saqFe638Gv9jD5d5o6hditbSMTrTnpfMgBrB/s1600/IMG_20170910_134227834.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1600" data-original-width="1431" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWVomReyB1kGp9I_3Fkkh35ksnm71i25KGJzjWCMw7_9MdtzIsq9xFr2IfC0-xW-fVcKi-61R3pIVbJW8GfBPOoqNpsNi1zGcqXH3x-Da7saqFe638Gv9jD5d5o6hditbSMTrTnpfMgBrB/s320/IMG_20170910_134227834.jpg" width="286" /></a></div>
<br />
The most important factor for attendee experience comes down to who is speaking and are there interesting sessions to attend at each time slot. What I like about this event is that there is a mix of local speakers and speakers from out of the area who have more recognizable names. I think that is an important balance to keep in the future. It is nice to see some recognizable names to draw people into the event, but I would hate for all of the area speakers to be crowded out of the event is well. Being able to learn from and network with both is important, and I hope this continues.<br />
<br />
I found <a href="https://twitter.com/joel_karr" target="_blank">Joel Karr's</a> talk on not thinking when you right code very thought provoking. He is right, people have different skill levels with different technologies, and we should consider that when assigning out work on a team. What this also says is that we want to gain enough practice with with key technologies so we don't have to think when we work on a task -- and by that I mean we understand the technology and problem so well that we can rely on muscle memory. Further, he talked about that we need to admit when we are in learning mode. This is an excellent point. There is nothing wrong with learning mode. We all have to do it, and we all do it a lot. But let's admit this to ourselves and know that progress will be slower and it will be hard to really define the work while learning. And as such, we need to make time for this learning. These are all very good thoughts to keep in mind.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4L7K3wmU7RDxoZDuZSnQOiXFU1gFsxryTNMVNCKw-heb_0r0VuFaIXXsfgvfN9BIwfcI_I-_G4yqm4Ubfvw-T795BQfOPDplDgcWK7z8g5EDx6Tr1TegfeBPzztJKhTvHwHhk-YfQpS7q/s1600/IMG_20170909_114202937.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="900" data-original-width="1600" height="225" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4L7K3wmU7RDxoZDuZSnQOiXFU1gFsxryTNMVNCKw-heb_0r0VuFaIXXsfgvfN9BIwfcI_I-_G4yqm4Ubfvw-T795BQfOPDplDgcWK7z8g5EDx6Tr1TegfeBPzztJKhTvHwHhk-YfQpS7q/s400/IMG_20170909_114202937.jpg" width="400" /></a></div>
<br />
<br />
I then attended the talk "Components of Good UI: An Intro to ReactJS" by Vince Maiuri and Ryan Feil. During this talk, they live coded a simple ReactJS application, which was good to see because it really helped me understand how the different pieces of a ReactJS app fit together. I've looked at some tutorials for ReactJS before, but this talk was the clearest explanation I've seen yet about how to get going with ReactJS. My team is working on an Angular 4 app right now, so I don't have any plans to start working with ReactJS right away, but it is good to understand the different approaches the two frameworks take.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Tjxkj909BrSLUUyhw8AmwQATRaOres0AVqdewM088afykJE90wPw4CfbR7_MZIaKOzXWM25MQcJb72CZa7p5f-HiAG-ZiHT8CZfYuNXPdimB6c_NNBv0a6jVz76LYq3U1UnRFipzIakt/s1600/IMG_20170909_130351237.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="900" data-original-width="1600" height="225" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Tjxkj909BrSLUUyhw8AmwQATRaOres0AVqdewM088afykJE90wPw4CfbR7_MZIaKOzXWM25MQcJb72CZa7p5f-HiAG-ZiHT8CZfYuNXPdimB6c_NNBv0a6jVz76LYq3U1UnRFipzIakt/s400/IMG_20170909_130351237.jpg" width="400" /></a></div>
<br />
<br />
The next session I attended was on Mocking and Unit Testing by <a href="https://twitter.com/Wright2Tweet" target="_blank">John Wright</a>. John works at Stack Overflow and is clearly a really sharp guy. I liked the history he gave of mocking libraries and how he introduced the different things you could do with a mocking library. The biggest takeway for me was the capabilities of some of the unconstrained mocking frameworks. While these are all pay libraries, they will "rewrite code on the fly" during the JIT process and allow you to test things that otherwise you would not be able to test easily like legacy code. So something worth keeping in mind.<br />
<br />
<blockquote class="twitter-tweet" data-lang="en">
<div dir="ltr" lang="en">
Slide, tons of info/resources and feedback links for my Mocking talk:<a href="https://t.co/xIlfujiQj3">https://t.co/xIlfujiQj3</a> <br />
<br />
Feedback is greatly appreciated!<a href="https://twitter.com/hashtag/mkedotnet?src=hash">#mkedotnet</a></div>
— John M. Wright (@Wright2Tweet) <a href="https://twitter.com/Wright2Tweet/status/906609656113647616">September 9, 2017</a></blockquote>
<script async="" charset="utf-8" src="//platform.twitter.com/widgets.js"></script>
<br />
The final session I attended was <a href="https://twitter.com/DustinJEwers" target="_blank">Dustin Ewer's</a> session on d3.js. I've seen Dustin speak a few times before, and he always does a really good job and this time was no exception. I played around with d3 a couple years ago, so this was a good re-introduction to the capabilities for me. He also talked about some of the libraries that have now been built on top of d3.js that make consuming the library a little easier. This is something I wish I had time to play around with more, but is a topic that has to go onto the learning backlog for now.<br />
<br />
<blockquote class="twitter-tweet" data-lang="en">
<div dir="ltr" lang="en">
The magnificent <a href="https://twitter.com/DustinJEwers">@DustinJEwers</a> putting on a good show talking about d3.js at <a href="https://twitter.com/mkedotnet">@mkedotnet</a> <a href="https://twitter.com/hashtag/mkedotnet?src=hash">#mkedotnet</a> <a href="https://t.co/7AXuQGIKdR">pic.twitter.com/7AXuQGIKdR</a></div>
— David Berry (@DavidCBerry13) <a href="https://twitter.com/DavidCBerry13/status/906624508685111296">September 9, 2017</a></blockquote>
<script async="" charset="utf-8" src="//platform.twitter.com/widgets.js"></script>
<br />
<br />
There were a number of other sessions I wish I could of attended. I think <a href="https://twitter.com/jeffreystrauss" target="_blank">Jeff Strauss's</a> talk on Open Source software would have been excellent. So also would the talks on Functional JavaScript by <a href="https://twitter.com/jonathanfmills" target="_blank">Jonathan Mills</a> and <a href="https://twitter.com/jprusakova" target="_blank">Jane Prusakova's</a> talk on the power of a Nudge. And I could go on and on, it just speaks to the depth of quality sessions which is the most important attribute of any conference.<br />
<br />
Finally, one of the other nice perks of the event is they host a happy hour at the conclusion where everyone gets a drink ticket, a custom commemorative glass and the opportunity to talk with their fellow attendees about the day's happenings. This is a really nice touch, and a great chance to catch up with others rather than everyone just taking off after the last session ends. This year, the commemorative glass was a really heavy mug that I am sure will be great for a big mug of Sprecher root beer or even a root beer float. Just need to get it washed so I can try it out.<br />
<br />
<blockquote class="twitter-tweet" data-lang="en">
<div dir="ltr" lang="en">
Did we mention there's a happy hour? <a href="https://twitter.com/hashtag/mkedotnet?src=hash">#mkedotnet</a> <a href="https://t.co/yVPbLhRTd8">pic.twitter.com/yVPbLhRTd8</a></div>
— Centare (@Centare) <a href="https://twitter.com/Centare/status/906593040881651714">September 9, 2017</a></blockquote>
<script async="" charset="utf-8" src="//platform.twitter.com/widgets.js"></script>
<br />
<h3>
Other Thoughts</h3>
I really liked the Potawatomi as the location for the event. As I covered above, the rooms were first class and really designed for events like this. I thought the food at both breakfast and lunch was very good and there was plenty of space to host an event of this size. I also like the location because if someone is looking for something to do after the event, there are lots of options. Obviously there is the casino itself for those who like that sort of thing, but they also have restaurants and live music at the Potawatomi. And if that isn't your cup of tea, you are a quick 10-12 minute drive from downtown Milwaukee and the Historic Third Ward with all of their restaurants and entertainment options. So just a really good location.<br />
<br />
This year's event had about 320 attendees (excluding speakers), which is up from last year and is very good. but for as good as this event is, that number should be more, like 500 or so. I think most of the attendees were Milwaukee based, but there is no reason that developers from Chicago, Madison and Appleton/Green bay shouldn't be able to make the quick drive over as well, especially given the quality of the event. You are just not going to find a better speaker lineup and a better put on event anywhere in the area. Tickets ranged from $79 to $119, but that is really cheap considering that included in that were two meals, and after party and a t-shirt. I am happy with the 320 number, but next year I would like to see what I can do to help get this number up even higher.<br />
<br />
One other thought is that the event started at 8:00 AM this year. Unfortunately, this was also complicated by the Brewer's half marathon blocking off most of the roads to the event, so a lot of people including myself arrived late. For myself, this meant that I missed the keynote presentation which was disappointing. However, I wonder if it might be smart to push back the keynote to start at say 8:45 or even 9:00 AM. If an attendee is going to drive in from Chicago, Madison or the Fox Valley, a little later start time makes things easier, so maybe a simple change like this could boost attendance from those other areas. <br />
<br />
<h3>
Wrap Up</h3>
All in all, a great event. Enough credit cannot go to <a href="https://twitter.com/davidpine7" target="_blank">David Pine</a>, <a href="https://twitter.com/pepopowitz" target="_blank">Steven Hicks</a>, <a href="https://twitter.com/rachelkrau" target="_blank">Rachel Krause</a>, <a href="https://twitter.com/adaering" target="_blank">Amanda Daering</a> and the rest of the Centare team for putting this on. So take this as a big thank you, for putting on the event, for giving me the opportunity to speak and for giving all of us the opportunity to learn. <br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com18tag:blogger.com,1999:blog-9038566846706115708.post-13462619642731463452017-09-02T15:23:00.000-07:002017-09-02T15:23:14.380-07:00Converting an Existing SQL Server Table to a Temporal TableOne of the very useful features added in SQL Server 2016 were temporal tables. With a temporal table, SQL Server will automatically record a history of all changed data rows to a history table associated with the temporal table. Further, SQL Server gives us some new syntax to be able to easily query what the data in the table looked like at any point in time or to show the entire history of a row in a table. If you want more details, you can check out this <a href="http://buildingbettersoftware.blogspot.com/2017/04/sql-server-temporal-tables.html" target="_blank">earlier blog post</a> I wrote on temporal tables.<br />
<br />
However, what if we have an existing table in our database that we want to convert to a temporal table? Lets take a look at how we do that.<br />
<br />
For this example, lets assume that we have the following table that already exists in our database.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">CREATE</span> <span style="color: #008800; font-weight: bold;">TABLE</span> Employees
(
EmployeeId <span style="color: #007020;">INT</span> <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
FirstName <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">20</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
LastName <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">20</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
Email <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">50</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
Phone <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">20</span>) <span style="color: #008800; font-weight: bold;">NULL</span>,
<span style="color: #008800; font-weight: bold;">CONSTRAINT</span> PK_Employees
<span style="color: #008800; font-weight: bold;">PRIMARY</span> <span style="color: #008800; font-weight: bold;">KEY</span> (EmployeeId)
);
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<br />
To convert this table to a temporal table, it is a two step process. The first step is that we need to add our ValidFrom/ValidTo columns to the table to represent when the row was active in the table. So we can run the following statement to do this.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5
6</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> Employees <span style="color: #008800; font-weight: bold;">ADD</span>
ValidFrom DATETIME2(<span style="color: #0000dd; font-weight: bold;">3</span>) <span style="color: #008800; font-weight: bold;">GENERATED</span> ALWAYS <span style="color: #008800; font-weight: bold;">AS</span> <span style="color: #008800; font-weight: bold;">ROW</span> <span style="color: #008800; font-weight: bold;">START</span>
<span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span> <span style="color: #008800; font-weight: bold;">DEFAULT</span> <span style="background-color: #fff0f0;">'1900-01-01 00:00:00.000'</span>,
ValidTo DATETIME2(<span style="color: #0000dd; font-weight: bold;">3</span>) <span style="color: #008800; font-weight: bold;">GENERATED</span> ALWAYS <span style="color: #008800; font-weight: bold;">AS</span> <span style="color: #008800; font-weight: bold;">ROW</span> <span style="color: #008800; font-weight: bold;">END</span>
<span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span> <span style="color: #008800; font-weight: bold;">DEFAULT</span> <span style="background-color: #fff0f0;">'9999-12-31 23:59:59.999'</span>,
PERIOD <span style="color: #008800; font-weight: bold;">FOR</span> SYSTEM_TIME (ValidFrom, ValidTo);
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Here we are adding the two columns needed for when the row is valid and the PERIOD that is required by a temporal table. Some things to note:<br />
<br />
<ul>
<li>We could name the columns <i>ValidFrom</i> and <i>ValidTo</i> anything that we want to, these are just the names that I chose.</li>
<li>These columns must be of a DATETIME2 data type. In this case, I am using DATETIME2(3) to go down to millisecond precision.</li>
<li>We need to provide default values for these columns in order to populate the existing rows on the table. For my <i>ValidFrom</i> I chose 1/1/1900 as a default starting date. The ending date for the rows in <i>ValidTo</i> column must be the maximum date/time value for our data type, so in this case, 12/31/999 at 23:59:99.999.</li>
<li>Otherwise, the syntax for the columns look much like the syntax for the columns in the CREATE TABLE statement.</li>
</ul>
Then, we need to run step 2 of the process:
<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> Employees
<span style="color: #008800; font-weight: bold;">SET</span> (SYSTEM_VERSIONING <span style="color: #333333;">=</span> <span style="color: #008800; font-weight: bold;">ON</span> (HISTORY_TABLE <span style="color: #333333;">=</span> dbo.EmployeesHistory));
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Here, we turn on SYSTEM_VERSIONING for the table so the <i>ValidFrom</i> and <i>ValidTo</i> dates will be auto-generated and define the name of the history table to use.
<br />
<div>
<br />
And that is all there is to it. Now, your table has been converted to a temporal table and any changes to your table will be tracked in the history table. </div>
<div>
<br /></div>
<br />
<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com6tag:blogger.com,1999:blog-9038566846706115708.post-9104573246408850552017-04-29T12:59:00.001-07:002017-04-29T12:59:31.517-07:00Chicago Code Camp Slides and ResourcesThanks to everyone who attended my presentation at <a href="https://www.chicagocodecamp.com/">Chicago Code Camp</a>. The slides and other resources from my talk are below.<br />
<br />
<span style="font-size: large;"><b>Slides</b></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://drive.google.com/open?id=0B-kpH7AX6Uc5ZF90RWYtdUh6dms"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrdBU8I4ZmppZFUJ_RK5eNYlzxMuDLMCmTMnlXd8q6246Gxuc-gEgg_GVOyOnCHhRZUOIAAhYDTKUv9L6KUKiQxTEdeQyRXkZQpSN_sjrt_WgV7fPZ0oEfspQk6I2NOf8exEdWvCS9OaaK/s640/SQL-Server-Performance-For-Developers-Chicago-Code-Camp.png" width="640" /></a></div>
<br />
<br />
<b><span style="font-size: large;">Pluralsight Course</span></b><br />
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; unicode-bidi: embed; word-break: normal;">
<span style="font-family: Calibri;"><a href="http://bit.ly/SqlPerformanceCourse"><span style="font-size: large;">http://bit.ly/SqlPerformanceCourse</span></a></span></div>
<br />
<br />
<b><span style="font-size: large;">Sample Database</span></b><br />
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; unicode-bidi: embed; word-break: normal;">
<span style="font-family: Calibri; font-size: large;"><a href="http://bit.ly/SampleSqlPerformanceDatabase">http://bit.ly/SampleSqlPerformanceDatabase</a></span></div>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; unicode-bidi: embed; word-break: normal;">
<br /></div>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; unicode-bidi: embed; word-break: normal;">
<br /></div>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; unicode-bidi: embed; word-break: normal;">
<b><span style="font-size: large;">DMV Queries</span></b></div>
<div>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; unicode-bidi: embed; word-break: normal;">
<span style="font-family: Calibri;"><span style="font-size: large;"><a href="http://bit.ly/PluralsightCourseDmvQueries">http://bit.ly/PluralsightCourseDmvQueries</a></span></span></div>
</div>
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com12tag:blogger.com,1999:blog-9038566846706115708.post-87818889600626180572017-04-24T19:30:00.001-07:002017-04-24T19:30:53.483-07:00A Scary Incident at Twin Cities Code CampWe had a scary incident at Twin Cities Code Camp this last weekend. <br />
<br />
About 45 minutes into <a href="https://twitter.com/Scott_Addie">Scott Addie's</a> talk on Reusable UI Components in ASP.NET Core, an attendee collapsed, fell out of his chair and hit the floor completely unconscious. I was looking down at the time, and by the time I looked up, several other attendees were rushing to aid the gentleman who had passed out.<br />
<br />
Fortunately, he came to in 10 seconds, though he still complained about being light headed. Though he was able to talk, he clearly was not right, so another attendee called 911. Within a few minutes, Campus Safety had arrived followed by paramedics who took the gentleman to a local hospital to run further tests and keep him under observation. During this 10-15 minutes from the time to collapsed until the time he was wheeled out on a stretcher, the man was conscious, coherent and able to talk. But like I said above, he clearly was not right. I do not have any more details on his present condition, but all of us at the event and in the technology community in general hope he makes a speedy recovery and is able to get whatever underlying issues that may be going on addressed.<br />
<br />
<b><i>This was scary, terribly scary.</i></b> When I looked up and first realized what was happening, my first thought was that he may have had a heart attack. I was scared for this gentleman and I felt very helpless at the time. It was just a few minutes until Campus Safety arrived, but those few minutes ticked by very slowly. In a moment all of us went from trying to learn something new to wondering what kind of medical help this individual needed and if that help would arrive in time.<br />
<br />
It was scary too in that you think of your own health, and in my case, I realize it isn't nearly as good as it should be. I'm in my mid 40's now, and health isn't something that can be taken for granted. The last few years, I have been much less active than I used to be, and as a result, I've gained a lot of weight. When your clothes don't quite fit right or they don't fit at all any more, that is telling you something. And not just that I have become too sedentary. It also speaks to my eating habits and how in general, I am just not taking good care of my most important possession, my health.<br />
<br />
I don't think I am alone in the technology community. Like many of us, I sit in a chair all day. I consume way too much soda while doing so. And then I go home, eat, and sit in a different chair to code some more. I know where too little activity and an unhealthy diet leads. We all do. Sometimes we need a wake up call though in order to take some action.<br />
<br />
<h3>
What I am Going to Do About It</h3>
I had a lot of time to think about this driving back from the Twin Cities, and I decided that I have to change some things. Now. These are things that I am changing right away:<br />
<br />
<ol>
<li><b>I will make sure to get at least 30 minutes of activity a day.</b> Ideally with summer coming a lot of this time will be on my road bike, and then can be a lot longer in duration. But no matter what, every day, I need to get out and get some activity. Tonight, that was a 30 minute walk around the neighborhood after dinner. That is all it takes. I'm not looking to be an Olympian, but we all need to be active every day, no exceptions.</li>
<li><b>I will cut my soda intake in half.</b> Hate to say it, but soda is an addiction for me like cigarettes are for others. Yes, I've tried to quit before, and that soda habit comes right back. I wish I could quit cold turkey today, but I have to be realistic, and I want these goals to be achievable. Cutting my intake in half will be major progress for me, and my weight and health will improve because of it.</li>
<li><b>I will sign up for a Red Cross First Aid/CPR class and complete it by the end of summer. </b>The last time I had first aid and CPR training was way back in college. I hope this is training I never have to use. But I didn't like that feeling of being helpless. If a similar situation ever happens in the future, I want to be confident I know what to do and be able to do it. </li>
</ol>
<div>
On June 1st, a little over a month from now, I'll post again on this topic with my progress. and I'll post again at the beginning of September. Mostly that is to help me hold myself accountable.</div>
<div>
<br /></div>
<h3>
What I Am Encouraging Everyone Else To Do</h3>
<br />
I would encourage you to set up your own heath goals like I have above. If you do, leave a comment here. Or blog about them or tweet about them. And then do so again in a month. In the mean time, hold yourself accountable for them. We can all do a 30 minute walk each and every day. Yes, it means a little less time glued to our devices or a little less time hacking on our personal projects. Its OK. Our teams, our friends and most importantly our families need us, all of us to be healthy. Besides, some time away from all of the screens we stare at all day might actually help us improve our creative processes.<br />
<br />
On Saturday, everything appears to have turned out OK. But it was a scary moment. What we can do is learn from it and take action about it. We spend a lot of time keeping our codebases healthy. We need to pay just as much attention to keeping ourselves healthy. I got that message loud and clear.<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com10tag:blogger.com,1999:blog-9038566846706115708.post-76313226943432657952017-04-23T16:39:00.000-07:002017-04-23T16:39:45.653-07:00JSON Functionality in SQL Server - Part 2This is the second part of a series on JSON functionality in SQL Server. This post will focus on accessing data in arrays that may be part of your JSON data.<br />
<br />
You can find the other parts of the series here (I'll update as I go)<br />
<br />
<ol>
<li><a href="http://buildingbettersoftware.blogspot.com/2017/04/json-functionality-in-sql-server.html" target="_blank">Accessing scalar properties on JSON objects stored in SQL Server</a></li>
<li>Accessing array data in JSON objects stored in SQL Server (this post)</li>
</ol>
<br />
<br />
Also, if you want to recreate the database I have here and try the samples out for yourself, you can find everything you need on Github ar:<br />
<br />
<div style="text-align: center;">
<b><a href="https://github.com/DavidCBerry13/CoolSqlServerFeatures/"><span style="font-size: large;">https://github.com/DavidCBerry13/CoolSqlServerFeatures/</span></a></b></div>
<br />
<br />
In the first part of this series, we had a simple table that contained weather data, and each row of the table contained a single JSON object with the weather observations. <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUMVrFiREBCyczLPVKGyNc88JYNYe56WW_s3x18uW3V4f8OmiZoAw-FmiC_smHSDKnZzzkbiEnXLWUjxDGcgMkdguunls2pwQOWuIbQCU_laxPRp9DL_RIFt4X7nuQdfcr1on29AhbQ-Tc/s1600/SimpleQueryResults.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUMVrFiREBCyczLPVKGyNc88JYNYe56WW_s3x18uW3V4f8OmiZoAw-FmiC_smHSDKnZzzkbiEnXLWUjxDGcgMkdguunls2pwQOWuIbQCU_laxPRp9DL_RIFt4X7nuQdfcr1on29AhbQ-Tc/s640/SimpleQueryResults.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
The JSON object in the <i>ObservationData</i> column contains all scalar properties. That is, if we want a value, we can just write a path expression and use the JSON_VALUE function to get the data element we want back. But life is not always so simple. What if instead of a single object, we had an object that contained an array of values or an array of objects. How would we interrogate the JSON data in our column to get access to those values? <br />
<br />
Lets consider the following table.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">CREATE</span> <span style="color: #008800; font-weight: bold;">TABLE</span> DailyWeatherDataJson
(
ObservationId <span style="color: #007020;">int</span> <span style="color: #008800; font-weight: bold;">IDENTITY</span>(<span style="color: #0000dd; font-weight: bold;">1</span>,<span style="color: #0000dd; font-weight: bold;">1</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
StationCode <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">10</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
City <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">30</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
<span style="color: #008800; font-weight: bold;">State</span> <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">2</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
ObservationDate <span style="color: #007020;">DATE</span> <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
ObservationData <span style="color: #007020;">VARCHAR</span>(<span style="color: #008800; font-weight: bold;">max</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
<span style="color: #008800; font-weight: bold;">CONSTRAINT</span> PK_DailyWeatherDataJson
<span style="color: #008800; font-weight: bold;">PRIMARY</span> <span style="color: #008800; font-weight: bold;">KEY</span> (ObservationId)
);
</pre>
</td></tr>
</tbody></table>
</div>
<br />
This is very similar to the table we had before with the following differences:<br />
<br />
<ul>
<li>There will be one row in the table for each weather station per day</li>
<li>The ObservationData column will now hold a JSON object that contains an array of all of the observations for that day</li>
<li>Since our JSON will be larger, we have changed to a VARCHAR(MAX) data type to store our JSON</li>
</ul>
<div>
The JSON in the ObservationData column will look like this:</div>
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61</pre>
</td><td><pre style="line-height: 125%; margin: 0;">{
<span style="color: #007700;">"stationCode"</span>: <span style="background-color: #fff0f0;">"14819"</span>,
<span style="color: #007700;">"observationDate"</span>: <span style="background-color: #fff0f0;">"2016-07-15T00:00:00"</span>,
<span style="color: #007700;">"weatherObservations"</span>: [
{
<span style="color: #007700;">"stationCode"</span>: <span style="background-color: #fff0f0;">"14819"</span>,
<span style="color: #007700;">"location"</span>: {
<span style="color: #007700;">"city"</span>: <span style="background-color: #fff0f0;">"Chicago"</span>,
<span style="color: #007700;">"state"</span>: <span style="background-color: #fff0f0;">"IL"</span>,
<span style="color: #007700;">"stationName"</span>: <span style="background-color: #fff0f0;">"CHICAGO MIDWAY INTL ARPT"</span>
},
<span style="color: #007700;">"observationDate"</span>: <span style="background-color: #fff0f0;">"20160715"</span>,
<span style="color: #007700;">"observationTime"</span>: <span style="background-color: #fff0f0;">"0053"</span>,
<span style="color: #007700;">"observationDateTime"</span>: <span style="background-color: #fff0f0;">"2016-07-15T00:53:00"</span>,
<span style="color: #007700;">"skyCondition"</span>: <span style="background-color: #fff0f0;">"FEW070"</span>,
<span style="color: #007700;">"visibility"</span>: <span style="color: #0000dd; font-weight: bold;">10</span>,
<span style="color: #007700;">"dryBulbFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">72</span>,
<span style="color: #007700;">"dryBulbCelsius"</span>: <span style="color: #6600ee; font-weight: bold;">22.2</span>,
<span style="color: #007700;">"wetBulbFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">64</span>,
<span style="color: #007700;">"wetBulbCelsius"</span>: <span style="color: #6600ee; font-weight: bold;">17.5</span>,
<span style="color: #007700;">"dewpointFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">58</span>,
<span style="color: #007700;">"dewpointCelsius"</span>: <span style="color: #6600ee; font-weight: bold;">14.4</span>,
<span style="color: #007700;">"relativeHumidity"</span>: <span style="color: #0000dd; font-weight: bold;">62</span>,
<span style="color: #007700;">"windSpeed"</span>: <span style="color: #0000dd; font-weight: bold;">7</span>,
<span style="color: #007700;">"windDirection"</span>: <span style="background-color: #fff0f0;">"280"</span>,
<span style="color: #007700;">"stationPressure"</span>: <span style="color: #6600ee; font-weight: bold;">29.26</span>,
<span style="color: #007700;">"seaLevelPressure"</span>: <span style="color: #008800; font-weight: bold;">null</span>,
<span style="color: #007700;">"recordType"</span>: <span style="background-color: #fff0f0;">" "</span>,
<span style="color: #007700;">"hourlyPrecip"</span>: <span style="color: #008800; font-weight: bold;">null</span>,
<span style="color: #007700;">"altimeter"</span>: <span style="color: #6600ee; font-weight: bold;">29.92</span>
},
{
<span style="color: #007700;">"stationCode"</span>: <span style="background-color: #fff0f0;">"14819"</span>,
<span style="color: #007700;">"location"</span>: {
<span style="color: #007700;">"city"</span>: <span style="background-color: #fff0f0;">"Chicago"</span>,
<span style="color: #007700;">"state"</span>: <span style="background-color: #fff0f0;">"IL"</span>,
<span style="color: #007700;">"stationName"</span>: <span style="background-color: #fff0f0;">"CHICAGO MIDWAY INTL ARPT"</span>
},
<span style="color: #007700;">"observationDate"</span>: <span style="background-color: #fff0f0;">"20160715"</span>,
<span style="color: #007700;">"observationTime"</span>: <span style="background-color: #fff0f0;">"0153"</span>,
<span style="color: #007700;">"observationDateTime"</span>: <span style="background-color: #fff0f0;">"2016-07-15T01:53:00"</span>,
<span style="color: #007700;">"skyCondition"</span>: <span style="background-color: #fff0f0;">"FEW070"</span>,
<span style="color: #007700;">"visibility"</span>: <span style="color: #0000dd; font-weight: bold;">10</span>,
<span style="color: #007700;">"dryBulbFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">71</span>,
<span style="color: #007700;">"dryBulbCelsius"</span>: <span style="color: #6600ee; font-weight: bold;">21.7</span>,
<span style="color: #007700;">"wetBulbFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">63</span>,
<span style="color: #007700;">"wetBulbCelsius"</span>: <span style="color: #0000dd; font-weight: bold;">17</span>,
<span style="color: #007700;">"dewpointFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">57</span>,
<span style="color: #007700;">"dewpointCelsius"</span>: <span style="color: #6600ee; font-weight: bold;">13.9</span>,
<span style="color: #007700;">"relativeHumidity"</span>: <span style="color: #0000dd; font-weight: bold;">61</span>,
<span style="color: #007700;">"windSpeed"</span>: <span style="color: #0000dd; font-weight: bold;">8</span>,
<span style="color: #007700;">"windDirection"</span>: <span style="background-color: #fff0f0;">"280"</span>,
<span style="color: #007700;">"stationPressure"</span>: <span style="color: #6600ee; font-weight: bold;">29.26</span>,
<span style="color: #007700;">"seaLevelPressure"</span>: <span style="color: #008800; font-weight: bold;">null</span>,
<span style="color: #007700;">"recordType"</span>: <span style="background-color: #fff0f0;">" "</span>,
<span style="color: #007700;">"hourlyPrecip"</span>: <span style="color: #008800; font-weight: bold;">null</span>,
<span style="color: #007700;">"altimeter"</span>: <span style="color: #6600ee; font-weight: bold;">29.92</span>
},
<span style="background-color: #ffaaaa; color: red;">...</span>
]
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
The '...' is meant to represent that in almost all cases, we'll have more than two elements in the <i>weatherObservations </i>array, since most National Weather Service stations (where I downloaded this data from) take observations at least every hour, if not more often.<br />
<br />
<b>Accessing Individual Elements with the JSON_VALUE Function</b><br />
If we know we want to access a value on an individual element of the array, we can do that using the JSON_VALUE function we saw in the last post. The following SQL statement will get the temperature reading for the first element of the array that represents readings for Chicago on July 15, 2016.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">SELECT</span>
ObservationId,
StationCode,
City,
<span style="color: #008800; font-weight: bold;">State</span>,
ObservationDate,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.weatherObservations[0].dryBulbFarenheit'</span>) <span style="color: #008800; font-weight: bold;">As</span> Temperature,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.weatherObservations[0].observationDateTime'</span>) <span style="color: #008800; font-weight: bold;">As</span> ObservationTime,
ObservationData
<span style="color: #008800; font-weight: bold;">FROM</span> DailyWeatherDataJson
<span style="color: #008800; font-weight: bold;">WHERE</span> City <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'Chicago'</span>
<span style="color: #008800; font-weight: bold;">AND</span> <span style="color: #008800; font-weight: bold;">State</span> <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'IL'</span>
<span style="color: #008800; font-weight: bold;">AND</span> ObservationDate <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'2016-07-15'</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
And here are the results<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyxVYmFIaOiQPDlbbsdt4T0BSj-6FnbW1IbM5J51xMhg7m-RrcItXLuaOhcj5doWYbKXt67pfgi3-nkhwe9_rKqrjJsbpMt4xU-HlA4yrAC1Itwf23giXvLxwzFW-Sa9G7rN3KyrnbT2Ti/s1600/JsonValueArrayElementResults.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="68" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyxVYmFIaOiQPDlbbsdt4T0BSj-6FnbW1IbM5J51xMhg7m-RrcItXLuaOhcj5doWYbKXt67pfgi3-nkhwe9_rKqrjJsbpMt4xU-HlA4yrAC1Itwf23giXvLxwzFW-Sa9G7rN3KyrnbT2Ti/s640/JsonValueArrayElementResults.png" width="640" /></a></div>
<br />
This is useful, but we are still pretty limited. We don't really want to go element by element through the array like this, and besides, we aren't even sure how many elements the array will have. We need to be able to work with the array as an array, so lets look at a way to do that.<br />
<br />
<b>Accessing The Array With the JSON_QUERY Function</b><br />
What I'm going to do here is jump ahead to the solution and then explain what is going on. Our goal is to get back each element of the JSON array and basically turn it into a row in our result set such that we can operate on the data like it was in a normal relational format. So lets see the answer:<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">SELECT</span>
ObservationId,
StationCode,
City,
<span style="color: #008800; font-weight: bold;">State</span>,
ObservationDate,
<span style="color: #008800; font-weight: bold;">CONVERT</span>(datetime, JSON_VALUE(Observations.[Value], <span style="background-color: #fff0f0;">'$.observationDateTime'</span>), <span style="color: #0000dd; font-weight: bold;">126</span>) <span style="color: #008800; font-weight: bold;">As</span> ObservationTime,
JSON_VALUE(Observations.[Value], <span style="background-color: #fff0f0;">'$.dryBulbFarenheit'</span>) <span style="color: #008800; font-weight: bold;">As</span> Temperature,
JSON_VALUE(Observations.[Value], <span style="background-color: #fff0f0;">'$.relativeHumidity'</span>) <span style="color: #008800; font-weight: bold;">As</span> Humidity,
JSON_VALUE(Observations.[Value], <span style="background-color: #fff0f0;">'$.windDirection'</span>) <span style="color: #008800; font-weight: bold;">As</span> WindDIrection,
JSON_VALUE(Observations.[Value], <span style="background-color: #fff0f0;">'$.windSpeed'</span>) <span style="color: #008800; font-weight: bold;">As</span> WindSpeed
<span style="color: #008800; font-weight: bold;">FROM</span> DailyWeatherDataJson d
<span style="color: #008800; font-weight: bold;"> </span><span style="color: #008800; font-weight: bold;">CROSS</span> APPLY OPENJSON(JSON_QUERY(ObservationData, <span style="background-color: #fff0f0;">'$.weatherObservations'</span>)) Observations
<span style="color: #008800; font-weight: bold;">WHERE</span> City <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'Chicago'</span>
<span style="color: #008800; font-weight: bold;">AND</span> <span style="color: #008800; font-weight: bold;">State</span> <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'IL'</span>
<span style="color: #008800; font-weight: bold;">AND</span> ObservationDate <span style="color: #333333;">=</span> (<span style="background-color: #fff0f0;">'2016-07-15'</span>);
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Lets start on line 13, because this is where the magic starts happening.<br />
<br />
The JSON_QUERY function lets us pull back a JSON array or object inside of another JSON object as a String, so we use this with the path expression '$.weatherObservations' to pull just the array of observations out of our larger JSON object.<br />
<br />
Then, we use the OPENJSON table valued function which will parse the JSON result we have into a rowset. And then the CROSS APPLY operator is used so we can join these results back to our main data set. <br />
<br />
So now we basically have a row for each individual observation in the JSON array that was in our original JSON object. And in each row, we have an individual JSON object that represents that individual observation. That object is in the Value property of the Observations alias, and now we can access properties on that object using JSON_VALUE like we did before, and we see this on lines 7 through 11. So basically what we have done is get all of the array elements out of the array and turn them into a JSON object that we have a row for, and now, we can extract data out of that individual JSON object like we saw in the last post.<br />
<br />
Yes, there is a lot going on here. But this is just a pattern. When you have a JSON object that has an array in it, you just look up this pattern, plug in the appropriate pieces and apply it. And what is really cool is that now data that is down in a JSON array, we can interact with just like it was stored as normal rows and columns in our database. So we can apply filters in the form of a WHERE clause, join to values, use aggregate functions, whatever we need to do and are familiar with using SQL.<br />
<br />
<b>Putting a View Over the Top</b><br />
I think most people would probably agree, they don't want to have to memorize that syntax or write it every time. So the answer is just to put a view over the top. The data is still stored as JSON, but you can write traditional SQL that we are all familiar with in order to interact with the data. <br />
<br />
I want to come back to one of the themes that I started the last post off with. A lot of times today, the choice sees to be presented to us that you are either in a relational world or a NoSQL world. The two camps strongly disagree with each other and if you do it the other way, someone thinks you are dead wrong.<br />
<br />
What I take from this functionality in SQL Server is that really within one database, I can have a foot in both worlds. If I want to act in a NoSQL way and just store JSON, sure, I can do that. But if I need to interact with that data in traditional SQL ways, I can do that too. So the question isn't either/or. In fact, I don't even think there is a question. The answer is I can do both, in the same place at the same time. So whatever works best for my application, I can do, and I find that very exciting.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com20tag:blogger.com,1999:blog-9038566846706115708.post-23818776046573437092017-04-15T20:57:00.000-07:002017-04-23T16:41:23.294-07:00JSON Functionality in SQL ServerSQL Server 2016 gives us the ability to work with JSON data directly in SQL Server, which is a very useful. Traditionally, we've thought of relational databases and NoSQL databases as distinct entities, but with databases like SQL Server implementing JSON functionality directly in the database engine, we can start to think about using both relational and no-sql concepts side by side, which opens up a lot of exciting possibilities.<br />
<br />
There is enough to cover that I will split this topic into multiple blog posts. This is post number one, where I will cover how to store JSON in your tables and how to interact with scalar values. The other posts are (will be updated as I go):<br />
<br />
You can find the other parts of the series here (I'll update as I go)<br />
<br />
<ol>
<li><a href="http://buildingbettersoftware.blogspot.com/2017/04/json-functionality-in-sql-server.html" target="_blank">Accessing scalar properties on JSON objects stored in SQL Server (this post)</a></li>
<li><a href="http://buildingbettersoftware.blogspot.com/2017/04/json-functionality-in-sql-server-part-2.html" target="_blank">Accessing array data in JSON objects stored in SQL Server</a></li>
</ol>
<br />
<br />
Also, if you want to recreate the database I have here and try the samples out for yourself, you can find everything you need on Github ar:<br />
<br />
<div style="text-align: center;">
<b><a href="https://github.com/DavidCBerry13/CoolSqlServerFeatures/"><span style="font-size: large;">https://github.com/DavidCBerry13/CoolSqlServerFeatures/</span></a></b></div>
<br />
<br />
So lets take a look at what we can do.<br />
<br />
<b>Storing JSON Data in a Table</b><br />
SQL Server does not contain a dedicated data type for JSON data, you simply just store your data in a VARCHAR (or NVARCHAR) data type of the appropriate size. <br />
<br />
Below is a table that I have defined that is going to hold some weather data.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">CREATE</span> <span style="color: #008800; font-weight: bold;">TABLE</span> WeatherDataJson
(
ObservationId <span style="color: #007020;">INT</span> <span style="color: #008800; font-weight: bold;">IDENTITY</span>(<span style="color: #0000dd; font-weight: bold;">1</span>,<span style="color: #0000dd; font-weight: bold;">1</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
StationCode <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">10</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
City <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">30</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
<span style="color: #008800; font-weight: bold;">State</span> <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">2</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
ObservationDate DATETIME <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
ObservationData <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">4000</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
<span style="color: #008800; font-weight: bold;">CONSTRAINT</span> PK_WeatherDataJson
<span style="color: #008800; font-weight: bold;">PRIMARY</span> <span style="color: #008800; font-weight: bold;">KEY</span> (ObservationId)
)
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Our JSON will be stored in the column named <i>ObservationData. The data type for this field is simply a </i>VARCHAR(4000) field, which is sufficient to hold the JSON objects we''l be storing in it. If you have larger objects, you can use a VARCHAR(MAX) field, and indeed we'll se an example of this later.<br />
<br />
This is a sample of the JSON we'll be storing in this column.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27</pre>
</td><td><pre style="line-height: 125%; margin: 0;">{
<span style="color: #007700;">"stationCode"</span>: <span style="background-color: #fff0f0;">"04825"</span>,
<span style="color: #007700;">"location"</span>: {
<span style="color: #007700;">"city"</span>: <span style="background-color: #fff0f0;">"Appleton"</span>,
<span style="color: #007700;">"state"</span>: <span style="background-color: #fff0f0;">"WI"</span>,
<span style="color: #007700;">"stationName"</span>: <span style="background-color: #fff0f0;">"OUTAGAMIE CO RGNL AIRPORT"</span>
},
<span style="color: #007700;">"observationDate"</span>: <span style="background-color: #fff0f0;">"20160701"</span>,
<span style="color: #007700;">"observationTime"</span>: <span style="background-color: #fff0f0;">"1245"</span>,
<span style="color: #007700;">"observationDateTime"</span>: <span style="background-color: #fff0f0;">"2016-07-01T12:45:00"</span>,
<span style="color: #007700;">"skyCondition"</span>: <span style="background-color: #fff0f0;">"SCT045"</span>,
<span style="color: #007700;">"visibility"</span>: <span style="color: #0000dd; font-weight: bold;">10</span>,
<span style="color: #007700;">"dryBulbFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">66</span>,
<span style="color: #007700;">"dryBulbCelsius"</span>: <span style="color: #0000dd; font-weight: bold;">19</span>,
<span style="color: #007700;">"wetBulbFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">55</span>,
<span style="color: #007700;">"wetBulbCelsius"</span>: <span style="color: #6600ee; font-weight: bold;">12.6</span>,
<span style="color: #007700;">"dewpointFarenheit"</span>: <span style="color: #0000dd; font-weight: bold;">45</span>,
<span style="color: #007700;">"dewpointCelsius"</span>: <span style="color: #0000dd; font-weight: bold;">7</span>,
<span style="color: #007700;">"relativeHumidity"</span>: <span style="color: #0000dd; font-weight: bold;">47</span>,
<span style="color: #007700;">"windSpeed"</span>: <span style="color: #0000dd; font-weight: bold;">7</span>,
<span style="color: #007700;">"windDirection"</span>: <span style="background-color: #fff0f0;">"360"</span>,
<span style="color: #007700;">"stationPressure"</span>: <span style="color: #6600ee; font-weight: bold;">29.13</span>,
<span style="color: #007700;">"seaLevelPressure"</span>: <span style="color: #008800; font-weight: bold;">null</span>,
<span style="color: #007700;">"recordType"</span>: <span style="background-color: #fff0f0;">" "</span>,
<span style="color: #007700;">"hourlyPrecip"</span>: <span style="color: #008800; font-weight: bold;">null</span>,
<span style="color: #007700;">"altimeter"</span>: <span style="color: #6600ee; font-weight: bold;">30.11</span>
}
</pre>
</td></tr>
</tbody></table>
</div>
<br />
So we see we have a number of weather related properties off of the main object and a nested object for the location. Our data in this column may be for different cities, but it all has this same format, which sets us up for the next step, querying the data.<br />
<b><br /></b>
<b>Querying Scalar Values in JSON Data</b><br />
First lets look at if we run a plain old SQL Query what we get back. Here is our initial query:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5
6</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">SELECT</span> <span style="color: #333333;">*</span>
<span style="color: #008800; font-weight: bold;">FROM</span> WeatherDataJson
<span style="color: #008800; font-weight: bold;">WHERE</span> City <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'Appleton'</span>
<span style="color: #008800; font-weight: bold;">AND</span> <span style="color: #008800; font-weight: bold;">State</span> <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'WI'</span>
<span style="color: #008800; font-weight: bold;">AND</span> ObservationDate <span style="color: #333333;">></span> <span style="background-color: #fff0f0;">'2016-07-01'</span>
<span style="color: #008800; font-weight: bold;">AND</span> ObservationDate <span style="color: #333333;"><</span> <span style="background-color: #fff0f0;">'2016-07-02'</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
And our results:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf43l41TjuAd0H_wKNEwmv33388Ft35_K05qNSy_1OrR75D2DVmQz6evAJ7AKekLEiMU724IrnAMUTe-iEFu6tB_9k-oHcxh1FGx5wXgRqTQEB2ODtceqcVXxW6v57nfUisfYlwSfGLRMT/s1600/SimpleQueryResults.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf43l41TjuAd0H_wKNEwmv33388Ft35_K05qNSy_1OrR75D2DVmQz6evAJ7AKekLEiMU724IrnAMUTe-iEFu6tB_9k-oHcxh1FGx5wXgRqTQEB2ODtceqcVXxW6v57nfUisfYlwSfGLRMT/s640/SimpleQueryResults.png" width="640" /></a></div>
<br />
We can clearly see there is JSON stored in the ObservationData column. But now we want to do something with it. Lets pull out the temperature and humidity readings from each observation and make those columns in our result set.<br />
<br />
To do this, we use a new function in SQL Server called <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql" target="_blank">JSON_VALUE</a>. JSON_VALUE takes two arguments:<br />
<br />
<ul>
<li>A JSON expression, which is typically a the name of a column that contains JSON text, but could also be a T-SQL variable containing JSON.</li>
<li>A path expression, which describes how to navigate to the scalar value you want to extract out of the JSON.</li>
</ul>
Path expressions start with the $ sign and then use dot notation to navigate to the property that you want the value of. So if I wanted to get the station name of where the measurement was made in our JSON above, I would use the expression<span style="font-family: "courier new" , "courier" , monospace;"> <span style="color: #660000;">'$.location.stationName'</span></span>. To get the temperature and humidity, we'll use the expressions <span style="color: #660000; font-family: "courier new" , "courier" , monospace;">'$.dryBulbFarenheit'</span> and <span style="color: #660000; font-family: "courier new" , "courier" , monospace;">'$.relativeHumidity'</span> respectively. <br />
<br />
So now, we are going to rewrite our query to extract these fields out as columns like this.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">SELECT</span>
ObservationId,
StationCode,
City,
<span style="color: #008800; font-weight: bold;">State</span>,
ObservationDate,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.dryBulbFarenheit'</span>) <span style="color: #008800; font-weight: bold;">As</span> Temperature,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.relativeHumidity'</span>) <span style="color: #008800; font-weight: bold;">As</span> Humidity,
ObservationData
<span style="color: #008800; font-weight: bold;">FROM</span> WeatherDataJson
<span style="color: #008800; font-weight: bold;">WHERE</span> City <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'Appleton'</span>
<span style="color: #008800; font-weight: bold;">AND</span> <span style="color: #008800; font-weight: bold;">State</span> <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'WI'</span>
<span style="color: #008800; font-weight: bold;">AND</span> ObservationDate <span style="color: #333333;">></span> <span style="background-color: #fff0f0;">'2016-07-01'</span>
<span style="color: #008800; font-weight: bold;">AND</span> ObservationDate <span style="color: #333333;"><</span> <span style="background-color: #fff0f0;">'2016-07-02'</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
And here are our results.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5A9GJY3CpBmKWuT-W7joQI12RzR2oK5YKrfLxpK8-WPynJ_0mOU4a5iRDsp_9DQqFxZflE3fWtJo-7Txig8XK1zy0dtKk4H526XIeN6cDAij4GM1ZSfuL-Yo5zUvvuXJHiNOjJXEThbnp/s1600/JsonValueResults.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="174" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5A9GJY3CpBmKWuT-W7joQI12RzR2oK5YKrfLxpK8-WPynJ_0mOU4a5iRDsp_9DQqFxZflE3fWtJo-7Txig8XK1zy0dtKk4H526XIeN6cDAij4GM1ZSfuL-Yo5zUvvuXJHiNOjJXEThbnp/s640/JsonValueResults.png" width="640" /></a></div>
<br />
We see that SQL Server used the JSON_VALUE function to dynamically pull those values out of our JSON so we can see those as columns in our result set. And now we can interact with them like we would any other column in a table. So we can keep this data stored as JSON, but interact with it in a relational manner when that makes sense, which is pretty cool. Traditionally, we would have needed an ETL process that would have parsed this data out and placed each element in a column, but now we can do that on the fly.<br />
<br />
<b>The Power of Virtual (Computed) Columns</b><br />
With our weather data, it would be pretty cool if I didn't have to write that syntax each time I queried the table but could just get a couple of these key fields like temperature and humidity. By combining the JSON_VALUE function with SQL Server's computed columns feature, we can create a set of virtual columns on our table which will do just that, pulling the data out of the JSON each time so it appears as just another column on the table. Here is what the syntax looks like.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> WeatherDataJson
<span style="color: #008800; font-weight: bold;">ADD</span> Temperature <span style="color: #008800; font-weight: bold;">AS</span> JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.dryBulbFarenheit'</span>);
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> WeatherDataJson
<span style="color: #008800; font-weight: bold;">ADD</span> Humidity <span style="color: #008800; font-weight: bold;">AS</span> JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.relativeHumidity'</span>);
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Now, if we enter our original SELECT * query against the table, we will see that these two columns just show up in our result set like any other columns. And of course, we could also refer to them by name, either in a SELECT clause, WHERE clause or JOIN condition. The function just like any other column, even though what is happening is real time, SQL Server is parsing the JSON and retrieving this value for us.<br />
<br />
That is an important point if we want to use these columns in a WHERE clause or a JOIN condition. We know that for a table of any size, if we query the table by a column that is not indexed, our performance is going to be very slow because SQL Server has to read all of the rows of the table from disk and find the columns we are looking for. Here, our results would probably be even worse if we tried to filter by one of these JSON virtual columns, because not only is SQL Server going to have to scan the whole table, it will also have to parse the JSON for each row. Not good!<br />
<br />
<b>Indexing Computed Columns</b><br />
However, SQL Server allows you to create an index over a computed (virtual) column. When you do this, the values of the computed columns are computed at index creation time and then persisted in the index so they can be searched quickly like values of any other column. In this way, but using the JSON_VALUE function in conjunction with a computed column and an index over that computed column, we can quickly search values in our JSON data like we would any other column in our table. Lets look at an example.<br />
<br />
Imagine we redefined our table from above to be about as simple as could be, a surrogate key <i>ObservationId </i>for the primary key and a column to hold our JSON data so that our CREATE TABLE statement now looks like this.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5
6
7</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">CREATE</span> <span style="color: #008800; font-weight: bold;">TABLE</span> BasicWeatherDataJson
(
ObservationId <span style="color: #007020;">INT</span> <span style="color: #008800; font-weight: bold;">IDENTITY</span>(<span style="color: #0000dd; font-weight: bold;">1</span>,<span style="color: #0000dd; font-weight: bold;">1</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
ObservationData <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">4000</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
<span style="color: #008800; font-weight: bold;">CONSTRAINT</span> PK_BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">PRIMARY</span> <span style="color: #008800; font-weight: bold;">KEY</span> (ObservationId)
)
</pre>
</td></tr>
</tbody></table>
</div>
<br />
This is about as simple as storing data can get. All of our data is inside the <i>ObservationData </i> column. This includes fields that we will probably want later in order to look the data up by, things like the observation time, the city, the state and the station code. We could write a query like this...<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">SELECT</span>
ObservationId,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.stationCode'</span>) <span style="color: #008800; font-weight: bold;">As</span> StationCode,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.location.city'</span>) <span style="color: #008800; font-weight: bold;">As</span> City,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.location.state'</span>) <span style="color: #008800; font-weight: bold;">As</span> <span style="color: #008800; font-weight: bold;">State</span>,
<span style="color: #008800; font-weight: bold;">CONVERT</span>(datetime2(<span style="color: #0000dd; font-weight: bold;">3</span>), JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.observationDateTime'</span>), <span style="color: #0000dd; font-weight: bold;">126</span>) <span style="color: #008800; font-weight: bold;">As</span> ObservationDate,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.dryBulbFarenheit'</span>) <span style="color: #008800; font-weight: bold;">As</span> Temperature,
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.relativeHumidity'</span>) <span style="color: #008800; font-weight: bold;">As</span> Humidity
<span style="color: #008800; font-weight: bold;">FROM</span> BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">WHERE</span>
JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.location.city'</span>) <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'Appleton'</span>
<span style="color: #008800; font-weight: bold;">AND</span> JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.location.state'</span>) <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'WI'</span>
<span style="color: #008800; font-weight: bold;">AND</span> <span style="color: #008800; font-weight: bold;">CONVERT</span>(datetime2(<span style="color: #0000dd; font-weight: bold;">3</span>), JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.observationDateTime'</span>), <span style="color: #0000dd; font-weight: bold;">126</span>) <span style="color: #333333;">></span> <span style="background-color: #fff0f0;">'2016-07-01'</span>
<span style="color: #008800; font-weight: bold;">AND</span> <span style="color: #008800; font-weight: bold;">CONVERT</span>(datetime2(<span style="color: #0000dd; font-weight: bold;">3</span>), JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.observationDateTime'</span>), <span style="color: #0000dd; font-weight: bold;">126</span>) <span style="color: #333333;"><</span> <span style="background-color: #fff0f0;">'2016-07-02'</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Not exactly the paragon of simplicity. Further, the execution plan confirms, this query is doing a full table scan to go and find the data, so it is both slow and resource intensive. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8FYbL9JCg7qQHdhLinOwEXsdRZbPJGREsxfW7HfIOwF3Ygm7PHzyIh_3z8rIR2IqgDmRUNjiEwgThYbKspSgqNi_q47yJhpsO1zmO3rPhs91riF1bfAeFD-lI9-hJsToR-XTqpjdG9lYf/s1600/JsonQueryTableScan.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8FYbL9JCg7qQHdhLinOwEXsdRZbPJGREsxfW7HfIOwF3Ygm7PHzyIh_3z8rIR2IqgDmRUNjiEwgThYbKspSgqNi_q47yJhpsO1zmO3rPhs91riF1bfAeFD-lI9-hJsToR-XTqpjdG9lYf/s1600/JsonQueryTableScan.png" /></a></div>
<br />
So first we are going to create some virtual columns to one, make the table easier to work with and two, so that we can create an index in the next step.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">ADD</span> StationCode <span style="color: #008800; font-weight: bold;">AS</span> JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.stationCode'</span>);
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">ADD</span> City <span style="color: #008800; font-weight: bold;">AS</span> JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.location.city'</span>);
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">ADD</span> <span style="color: #008800; font-weight: bold;">State</span> <span style="color: #008800; font-weight: bold;">AS</span> JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.location.state'</span>);
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">ADD</span> ObservationDate <span style="color: #008800; font-weight: bold;">AS</span> <span style="color: #008800; font-weight: bold;">CONVERT</span>(datetime2(<span style="color: #0000dd; font-weight: bold;">3</span>), JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.observationDateTime'</span>), <span style="color: #0000dd; font-weight: bold;">126</span>);
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">ADD</span> Temperature <span style="color: #008800; font-weight: bold;">AS</span> JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.dryBulbFarenheit'</span>);
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">ADD</span> Humidity <span style="color: #008800; font-weight: bold;">AS</span> JSON_VALUE(ObservationData, <span style="background-color: #fff0f0;">'$.relativeHumidity'</span>);
</pre>
</td></tr>
</tbody></table>
</div>
<br />
And now for the indexes.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">CREATE</span> <span style="color: #008800; font-weight: bold;">INDEX</span> IX_BasicWeatherJson_ObservationDate_StationCode
<span style="color: #008800; font-weight: bold;">ON</span> BasicWeatherDataJson (ObservationDate, StationCode);
<span style="color: #008800; font-weight: bold;">CREATE</span> <span style="color: #008800; font-weight: bold;">INDEX</span> IX_BasicWeatherJson_ObservationDate_State_City
<span style="color: #008800; font-weight: bold;">ON</span> BasicWeatherDataJson (ObservationDate, <span style="color: #008800; font-weight: bold;">State</span>, City);
</pre>
</td></tr>
</tbody></table>
</div>
<br />
When you create these indexes, you will get a warning that you may be exceeding the maximum non-clustered index key size, because theoretically you could be pulling a very large string out of the JSON. If you have larger strings, you want to make sure that these will fit under the 1700 byte limit for the key size of an index, and in our case, we are well below that, so we are fine to continue.<br />
<br />
So now, we can rewrite our query so it looks like this:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">SELECT</span>
ObservationId,
StationCode,
City,
<span style="color: #008800; font-weight: bold;">State</span>,
ObservationDate,
Temperature,
Humidity
<span style="color: #008800; font-weight: bold;">FROM</span> BasicWeatherDataJson
<span style="color: #008800; font-weight: bold;">WHERE</span>
City <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'Appleton'</span>
<span style="color: #008800; font-weight: bold;">AND</span> <span style="color: #008800; font-weight: bold;">State</span> <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'WI'</span>
<span style="color: #008800; font-weight: bold;">AND</span> ObservationDate <span style="color: #333333;">></span> <span style="background-color: #fff0f0;">'2016-07-01'</span>
<span style="color: #008800; font-weight: bold;">AND</span> ObservationDate <span style="color: #333333;"><</span> <span style="background-color: #fff0f0;">'2016-07-02'</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
This is much simpler and performs much better, as now the query is using one of our indexes. So again, pretty cool that we can not just reach into the JSON to grab the values out, but we can also index any key properties we have so we can quickly search through the data that we have.<br />
<b><br /></b>
<b>Summary</b><br />
So there are the basics of storing JSON in SQL Server and interacting with scalar values. in part 2, we'll take a look at interacting with arrays that may be stored in your JSON data.Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com0tag:blogger.com,1999:blog-9038566846706115708.post-83060508844631776972017-04-14T18:20:00.004-07:002017-04-14T18:20:56.241-07:00SQL Server Temporal TablesOne of the newest and most useful features of SQL Server is Temporal Tables, which are available in SQL Server 2016 and SQL Azure. In a nutshell, temporal tables give you a simple way to capture all of the changes that are made to rows in a table. Of course you could do this in prior versions of SQL Server by defining your own history table and trigger, but now this functionality is built directly into SQL Server.<br />
<br />
Why is this useful? Have you ever had to go back and audit when a piece of data changed in a table? Maybe you have needed to see the history of all of the changes to a certain record in the table? Or needed to recreate the table as it was at a certain point of time. If any of these apply, then temporal tables can be a big help.<br />
<b><br /></b>
<b>Defining a Temporal Table</b><br />
The syntax for defining a temporal table is straightforward and shown below.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;"> CREATE</span> <span style="color: #008800; font-weight: bold;">TABLE</span> Contacts
(
ContactID <span style="color: #007020;">INT</span> <span style="color: #008800; font-weight: bold;">IDENTITY</span>(<span style="color: #0000dd; font-weight: bold;">1</span>,<span style="color: #0000dd; font-weight: bold;">1</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
FirstName <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">30</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
LastName <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">30</span>) <span style="color: #008800; font-weight: bold;">NOT</span> <span style="color: #008800; font-weight: bold;">NULL</span>,
CompanyName <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">30</span>) <span style="color: #008800; font-weight: bold;">NULL</span>,
PhoneNumber <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">20</span>) <span style="color: #008800; font-weight: bold;">NULL</span>,
Email <span style="color: #007020;">VARCHAR</span>(<span style="color: #0000dd; font-weight: bold;">50</span>) <span style="color: #008800; font-weight: bold;">NULL</span>,
ValidFrom DATETIME2(<span style="color: #0000dd; font-weight: bold;">3</span>) <span style="color: #008800; font-weight: bold;">GENERATED</span> ALWAYS <span style="color: #008800; font-weight: bold;">AS</span> <span style="color: #008800; font-weight: bold;">ROW</span> <span style="color: #008800; font-weight: bold;">START</span>,
ValidTo DATETIME2(<span style="color: #0000dd; font-weight: bold;">3</span>) <span style="color: #008800; font-weight: bold;">GENERATED</span> ALWAYS <span style="color: #008800; font-weight: bold;">AS</span> <span style="color: #008800; font-weight: bold;">ROW</span> <span style="color: #008800; font-weight: bold;">END</span>,
PERIOD <span style="color: #008800; font-weight: bold;">FOR</span> SYSTEM_TIME (ValidFrom, ValidTo),
<span style="color: #008800; font-weight: bold;">CONSTRAINT</span> PK_Contacts <span style="color: #008800; font-weight: bold;">PRIMARY</span> <span style="color: #008800; font-weight: bold;">KEY</span> (ContactId)
)
<span style="color: #008800; font-weight: bold;"> WITH</span> (SYSTEM_VERSIONING <span style="color: #333333;">=</span> <span style="color: #008800; font-weight: bold;">ON</span> (HISTORY_TABLE <span style="color: #333333;">=</span> dbo.ContactsHistory));
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Lines 2 through 8 contain our normal column definitions for the table, in this case, a simple table to manage contact information.<br />
<br />
We then need to define two columns that represent when the column is start and end times that the row is considered active, and this is done on lines 9 and 10 with the <i>ValidFrom </i>and <i>ValidTo</i> columns. You can use any names for these columns you want, its just a good idea to make sure the names convey what these columns represent, the time the row is active. These columns will have a DATETIME data type, and in this case I am using a DATETIME2(3) data type so we will store these values down to millisecond precision. Finally, you need to include the text GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END for your respective start and stop columns. This signals to SQL Server to automatically populate these columns with the UTC value of the system time when a row is inserted, updated or deleted.<br />
<br />
On line 11, we have some syntax that indicates that our ValidFrom and ValidTo columns represent a period in time. Finally, at the end of our table definition on line 14, we tell SQL Server that we need to have System Versioning of this table on and give SQL Server the name of the history table that we want to use.<br />
<br />
If you don't want users to see the <i>ValidFrom</i> and <i>ValidTo</i> columns, you can hide these columns like so:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> Contacts
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">COLUMN</span> ValidFrom <span style="color: #008800; font-weight: bold;">ADD</span> HIDDEN;
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> Contacts
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">COLUMN</span> ValidTo <span style="color: #008800; font-weight: bold;">ADD</span> HIDDEN;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
If you change your mind and want to have them show up again, then simply do this.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> Contacts
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">COLUMN</span> ValidFrom <span style="color: #008800; font-weight: bold;">DROP</span> HIDDEN;
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">TABLE</span> Contacts
<span style="color: #008800; font-weight: bold;">ALTER</span> <span style="color: #008800; font-weight: bold;">COLUMN</span> ValidTo <span style="color: #008800; font-weight: bold;">DROP</span> HIDDEN;
</pre>
</td></tr>
</tbody></table>
</div>
That just drops the hidden attribute, not the column itself.<br />
<br />
When you execute this statement, SQL Server will create both this table and the specified history table. Where things get interesting is when you start inserting and updating data in your table.<br />
<br />
<b>Working With Temporal Tables</b><br />
You insert, update and delete data in your temporal table just like you would any other table. The only thing you need to know is that you do not specify values for the ValidFrom or ValidTo columns. SQL Server will take care of populating these columns for you.<br />
<br />
So what happens when we perform DML operations against out temporal table?<br />
<br />
<br />
<table style="border-collapse: collapse; border: 1px solid black;">
<tbody>
<tr>
<td style="border-collapse: collapse; border: 1px solid black;"><b>Operation</b></td>
<td style="border-collapse: collapse; border: 1px solid black;"><b>Description</b></td>
</tr>
<tr>
<td style="border-collapse: collapse; border: 1px solid black;"><b>INSERT</b></td>
<td style="border-collapse: collapse; border: 1px solid black;">The row will be inserted into the primary table (in this case contacts). <br />
<br />
The <i>ValidFrom</i> column is populated with the current system time in UTC<br />
<br />
The <i>ValidTo</i> field is populated with '9999-12-31 23:59:59.999' (the system max time)<br />
<br />
No entry is made in the history table. <br />
<br /></td>
</tr>
<tr>
<td style="border-collapse: collapse; border: 1px solid black;"><b>UPDATE</b></td>
<td style="border-collapse: collapse; border: 1px solid black;">The existing row will be moved from the primary table to the history table. When this is done, its ValidTo time is populated with the current system time in UTC.<br />
<br />
The existing row is then replaced by the new row in the primary table. The new row will have a V<i>alidFrom </i>of the current system time in UTC and the <i>ValidTo</i> field will be the system max time.<br />
<br /></td>
</tr>
<tr>
<td style="border-collapse: collapse; border: 1px solid black;"><b>DELETE</b></td>
<td style="border-collapse: collapse; border: 1px solid black;">The existing row will be moved from the primary table to the history table with its <i>ValidTo</i> time populated as the current system time in UTC<br />
<br />
No row will be present in the primary table, since the row has been deleted.</td></tr>
</tbody></table>
<br />
<br />
<b>Querying Data</b><br />
SQL Server also gives us some new constructs to query data out of our temporal tables and see what has changed in time or what the state of the data was at any point in time. What is nice about these constructs is that we don't have to query the primary table and the history table individually and then union them together in order to get a complete view of our data over time. SQL Server does this for us behind the scenes, leaving us with a much simpler query syntax. So lets take a look.<br />
<br />
First of all, we can query the table normally which will show us all of the rows that are currently active.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">-- Plain old SQL</span>
<span style="color: #008800; font-weight: bold;">SELECT</span> <span style="color: #333333;">*</span>
<span style="color: #008800; font-weight: bold;">FROM</span> Contacts;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
No surprises here in terms of the data that comes back, just what rows are currently in the primary table<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGPcoOTf4YWc-FsUv2Eg3uGVLpjU9Z8jp8hkiraYOlbmvkJach3dXh8cOPonDH7wzLmXIC6t2vYvAQON4URYPg2SFA-wNp29antRztgGTHB4fG6Inxxzoi1DU4WqYWz-E717nD3xC5AzcQ/s1600/NormalSqlSelectResults.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="88" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGPcoOTf4YWc-FsUv2Eg3uGVLpjU9Z8jp8hkiraYOlbmvkJach3dXh8cOPonDH7wzLmXIC6t2vYvAQON4URYPg2SFA-wNp29antRztgGTHB4fG6Inxxzoi1DU4WqYWz-E717nD3xC5AzcQ/s640/NormalSqlSelectResults.png" width="640" /></a></div>
<br />
We can see these rows have been inserted at different times over a few days, and we see that all of the <i>ValidTo</i> dates are set to the system max time. But more has been going on with this table, so lets check that out.<br />
<b><br /></b>
<b>Show Complete History of a Table</b><br />
If you want to look at every instance of every row that has been in a table, you can use the FOR SYSTEM TIME ALL clause immediately after your table name in your FROM clause like this:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">-- Gets all records</span>
<span style="color: #008800; font-weight: bold;">SELECT</span> <span style="color: #333333;">*</span>
<span style="color: #008800; font-weight: bold;">FROM</span> Contacts
<span style="color: #008800; font-weight: bold;">FOR</span> SYSTEM_TIME
<span style="color: #008800; font-weight: bold;">ALL</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Now lets take a look at these results.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvv4R0pW7cc5L5OqLdh060OM_in3aDWeDxGyxN60bow0-wLNnWeEm2UeFLUL1UF3VS1T666PtwZ9Ax26Q0pYszMZLekyczyi6a01qjIxdpDXnR-1qX_BzCFtSElqHzCpcJAsZAs4A7SuJ3/s1600/ForSystemTimeAllResults.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="124" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvv4R0pW7cc5L5OqLdh060OM_in3aDWeDxGyxN60bow0-wLNnWeEm2UeFLUL1UF3VS1T666PtwZ9Ax26Q0pYszMZLekyczyi6a01qjIxdpDXnR-1qX_BzCFtSElqHzCpcJAsZAs4A7SuJ3/s640/ForSystemTimeAllResults.png" width="640" /></a></div>
<br />
So now we see that there have indeed been some changes to the data in our table over time. We see the 5 active rows that we saw before, but we also see some additional rows. This view shows that we had two contacts that were deleted, contact number 4 for Garfield Arbuckle and contact number 6, Odie Arbuckle. In addition, we see that the record for contact number 1 Charlie Brown has changed, and specifically it looks like his phone number changed in the row. And by looking at the ValidTo date, we can tell when each of these events occurred.<br />
<br />
<br />
<b>What Did the Data Look Like at a Point In Time?</b><br />
We can use the FOR SYSTEM TIME AS OF clause to view the data in a table as it appeared in a point in time as follows.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">SELECT</span> <span style="color: #333333;">*</span>
<span style="color: #008800; font-weight: bold;">FROM</span> Contacts
<span style="color: #008800; font-weight: bold;">FOR</span> SYSTEM_TIME
<span style="color: #008800; font-weight: bold;">AS</span> <span style="color: #008800; font-weight: bold;">OF</span> <span style="background-color: #fff0f0;">'2017-04-12 14:00:00.0000000'</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
This query will view the table as it was as of April 12, 2017 at 14:00 UTC. Remember when specifying this time that you need to use UTC time as that is what is contained in the ValidFrom and ValidTo tables and not your system local time.<br />
<br />
It should also be pointed out that we could very well attach a WHERE clause to this query to get just a subset of rows or even a single row. In our case though, here are the results that we get.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyzQ6GaMlaRnxx7vifQsFJcRWtrZnRtTEUAhZVZR4citj22Ng-SCFe6GeXII9f40gBUq-4WHQNUIm0YXA_HADkhMZ2rjUCE1Qj53yjAl6DX8yH-OGLSNsizxRIbBvgso286xqKGnFDcvyT/s1600/ForSystemTimeAsOfResults.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="88" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyzQ6GaMlaRnxx7vifQsFJcRWtrZnRtTEUAhZVZR4citj22Ng-SCFe6GeXII9f40gBUq-4WHQNUIm0YXA_HADkhMZ2rjUCE1Qj53yjAl6DX8yH-OGLSNsizxRIbBvgso286xqKGnFDcvyT/s640/ForSystemTimeAsOfResults.png" width="640" /></a></div>
This shows us that at this time, records for Sally Brown, Franklin Armstrong and Odie Arbuckle had not even been added yet and that the record for Garfield Arbuckle was still active. This can be extremely useful when you are trying to recreate a report that was run at a certain time, because you can see exactly what the data was in the table at that instant in time.<br />
<br />
<b>Querying All Records Active for a Period of Time</b><br />
Finally, we have some new syntax which will show us any record that was active during a given time period. <br />
<br />
<ul>
<li><span style="font-family: "courier new" , "courier" , monospace;"><b>FROM <start date> TO <end date></b></span> - Shows all rows that were active during this time period, but excludes rows that became active on the boundary</li>
<li><b><span style="font-family: "courier new" , "courier" , monospace;">FOR SYSTEM TIME BETWEEN <start date> AND <<end date></span></b> - Shows all rows active in this time period, including rows that became active on the upper boundary of the time period</li>
<li><span style="font-family: "courier new" , "courier" , monospace;"><b>FOR SYSTEM TIME CONTAINED IN (<start date>, <end data>)</b></span> - Gets all the rows that opened and closed during the specified time period</li>
</ul>
<br />
<b>Summary</b><br />
I've found temporal tables to be one of the best additions to SQL Server. They are super easy to use and being able to go back and look at all of the changes are to your table is extremely helpful. They are so easy to use that I find almost no reason not to use them. So next time you need to create a table, think about making the table a temporal table. The first time you need to look up what has changed about a record, you will be glad that you did.<br />
<br />
<b><br /></b>
<b>Further Reading</b><br />
Official Microsoft Documentation<br />
<a href="https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables">https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables</a><br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com1tag:blogger.com,1999:blog-9038566846706115708.post-13655921894768716432016-11-09T18:25:00.000-08:002016-11-09T18:25:04.650-08:00Why I Avoid Using Hints in my Database QueriesFor those who have watched my Pluralsight courses on either Oracle or SQL Server performance tuning, you will notice that I don't talk about database hints in either course. This is intentional on my part. I tend not to use hints in any of my production SQL statements and overall I only use hints in very limited cases.<br />
<br />
I made the decision not to cover hints in my courses because too often times, I have seen hints used incorrectly, often by someone who didn't understand what the hint was doing. I've seen cases where someone read about hints "on the Internet" and thought by dropping a hint into their SQL statement, it would act as some sort of magical performance booster for their statement. Unfortunately, there is no magic going on, and like any technology or technique, the result can actually be a worse outcome if you don't understand what is going on.<br />
<br />
So lets explain what a little bit about hints.<br />
<br />
<b>The Query Optimization Process</b><br />
When you submit a SQL statement to any database, a piece of software called the Query Optimizer parses the SQL statement and determines the fastest way to process that statement. It is the query optimizer that determines if an index can be used or a table scan should be performed. It also determines how to perform joins between tables and in what order the tables should be joined if you have multiple join conditions.<br />
<br />
To determine the most efficient way to process your statement, the optimizer looks at the statistics of the tables involved in the statement, including the total number of rows and the distribution of those rows in the table. It also looks at the indexes on the table and the number of unique keys in the index and matches all of this data up with the where clauses and join criteria you have specified in your statement. Using these statistics, the optimizer can estimate the cost of all of the different ways to perform the SQL statement, and it will pick the lowest cost combination of these operations.<br />
<br />
All of this usually happens in 100 milliseconds or less. And optimizers today are really, really good at picking the right execution plan that will result in the fastest way to execute your statement.<br />
<br />
<b>What Does a Hint Do</b><br />
When you supply a database hint, you are taking control of how the statement will execute and taking this control away from the query optimizer. The problem is that the best way to execute a statement may change over time. Maybe a table gets more data or the distribution of the data in a table changes. Under normal circumstances, the query optimizer can adjust to these changes and come up with a new plan which is most efficient for the current data set. <br />
<br />
When you provide a hint, you are taking this flexibility away from the optimizer. So now as things change in your database, the optimizer cannot adjust. So now, you have a sub-optimal plan, and many times that plan is much less efficient than the plan the query optimizer would have come up with on its own. The problem is that while you can figure out the right hint for the way the data looks today, you have no way of knowing what the data will look like tomorrow. Yet by providing a hint, you are really committing to a specific execution plan, even if that plan is wildly inefficient for tomorrows problem.<br />
<br />
<b>Is There Ever a Time to Use Hints</b><br />
Sometimes I am surprised when I get a certain execution plan. For example, I may have expected the optimizer to use a different index than what it did. So in my SQL editor, I'll use a hint to look at that different version of the plan. In almost every case, the plan produced by the optimizer is less expensive than the plan with the hint. Its not that I don't trust the optimizer, but sometimes being able to contrast the two execution plans helps me understand why the optimizer is making the decisions it is.<br />
<br />
The other time to use a hint would be if you were instructed to do so by Oracle or Microsoft technical support. I've never had to do this, and I'm guessing these instances are very few and very far between.<br />
<br />
<b>Summary</b><br />
I really feel like it is best to avoid using hints in any production SQL statements your application might run. At first, they may seem magical. But the optimizers included in today's database products are really, really good. When you start including hints in your statement, you are taking control away from the optimizer and its ability to find the best plan for the current state of the table. This almost always results in a less efficient plan and therefore slower running SQL statement. So I encourage you to avoid using hints in any production SQL you might run.Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com4tag:blogger.com,1999:blog-9038566846706115708.post-14405121955317186312016-11-07T20:50:00.000-08:002016-11-07T20:50:13.227-08:00MKE DOT NET 2016On Saturday, October 29th, I attended <a href="http://www.mkedotnet.com/">MKE DOT NET</a> in Pewaukee, WI. This is a one day conference focused around .NET organized by <a href="http://www.centare.com/">Centare</a>. I was fortunate enough to be accepted to speak at the event, which gave me a chance to share my knowledge as well as attend and learn from others.
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIOpWQeNLXenrAXI7BKatAODSlwTuAmCurJYA0eF_8gYGrzoPCfSTwJiVjeC-T4wMUX8psDDAcK3ocPSalr5qazIX6DAgQ77l9U3EtvDfhjZttqUnqnr6I93ItUSXgs7JJJTW5KBVnePLa/s1600/MKE_DOT_NET_400px.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIOpWQeNLXenrAXI7BKatAODSlwTuAmCurJYA0eF_8gYGrzoPCfSTwJiVjeC-T4wMUX8psDDAcK3ocPSalr5qazIX6DAgQ77l9U3EtvDfhjZttqUnqnr6I93ItUSXgs7JJJTW5KBVnePLa/s320/MKE_DOT_NET_400px.png" width="320" /></a></div>
One of the big draws of this event is the speaker lineup. In the two years the event has existed, Centare has used their connections in the .NET community to get some high profile speakers for the event. In 2015 (which I did not attend) they had uncle Bob Martin give the keynote. This year, the keynote was delivered by <a href="http://www.hanselman.com/">Scott Hanselman</a> of Microsoft. In addition, <a href="http://blog.stephencleary.com/">Stephen Cleary</a> and <a href="https://www.benday.com/">Ben Day</a> were also speaking at this years event. However, there was also a strong contingent of speakers from the Milwaukee and Madison area, so there was a nice balance of well known names with local practitioners. This was one of the first things that I really liked about the event was this balance of different perspectives, and I hope this continues in future years.
Below, I'll give a brief review of the talks I attended, my impressions and my takeaways.<br />
<br />
<b> Keynote - Scott Hanselman
</b><br />
The morning started with a an hour long talk by Scott Hanselman discussing how and why Microsoft is evolving .NET to the .NET Core platform. We read a lot about .NET Core these days, but there is very little written about the reasons why the change is being made. Mainly, Microsoft wants a much smaller package that someone can get started with coding .NET without having to download a 2 GB package containing Visual Studio and all the other tooling that is necessary to code in .NET today. He also talked about how while the .NET Core runtime has been released, a lot of the tooling is still in beta. This is useful to know where the state of everything is as you decide what version to use for new projects.<br />
<br />
The one piece of advice I really liked is when he said "know what level of abstraction you are working at and make sure to know how things work one level deeper". This is great advice, and something I have always believed in. I think the is a great way to put this and sums things up nicely. We always want to know what are tools and frameworks are doing behind the scenes, and this statement encapsulates that advice nicely.<br />
<br />
<b>Session #2 - Ben Day - Claims Based Security</b><br />
I was speaking in the first slot, so the first session I attended was Ben Day's session on Claim based security. I think he did a really good job of summarizing why role based authentication is really too simplistic of a model in today's world. He described claims based security, and how most systems will fist check if a user is in a role and then check individual claims. I've started to see more system s move to more of a claims based model, and this talk provided a good overview of how this is implemented in .NET using the identity provider.<br />
<h4>
<br />Session #3 - Josh Schultz - Working With Humans<br />
<span style="font-weight: normal;">This was a talk about how to work with people, and I found a lot of the advice very practical and useful. I loved the suggestion of having lunch with a coworker you may not get along with very well and talking about anything outside of work. </span> <span style="font-weight: normal;">A lot of his advice was very practical, like considering other perspectives and not making assumptions about what others are thinking. All things we know, but that we should constantly be reminded of. I also liked that he stressed that there is a really important aspect to development which is understanding the problem you are trying to solve, and asking lots of questions to make sure you do. This was a really good talk. While Josh claimed he didn't have all the answers, he had a lot of good advice in his presentation.</span><br />
<span style="font-weight: normal;"><br /></span></h4>
<b>Session #4 - Andrew Bloechl - Building SaaS Products in the Cloud</b><br />
<span style="font-weight: normal;">This talk was really more like a retrospective of an effort to move one of their applications to the cloud. He went through the decisions that they made, why they made those decisions and some of the decisions that they later reversed. I really liked this talk because it wasn't the typical overview of a cloud service. It was real world experience of the ups and downs. </span><br />
<span style="font-weight: normal;"><br /></span>
<span style="font-weight: normal;">For example, they encountered problems where the system was very slow for the first user because Azure services would all shut down after 20 minutes of non-use, meaning the whole stack had to be fired up again when the next user hit the site. There is now a feature where you can keep everything in an 'always up' state, but that isn't something you typically think about from the start. They also had issues where people created virtual machines, forgot about them and left them running and got a big bill at the end of the month. Overall, they did really like the cloud solution and the flexibility it brought, but it was good to hear about some of the challenges they had to overcome. So often these solutions are presented in such a blue sky manner, its great to see talks like this that remind us there is a learning curve and can help the rest of us get up to speed on that curve.</span><br />
<br />
<b>Session #5 - Samrat Saha - Azure Functions</b><br />
This was an interesting talk on Azure Functions, which allow you to write a function to be implemented in Azure and not worry at all about how the function is hosted or set up from an infrastructure perspective. The example the speaker used was a series of functions that could respond to text messages. He actually talked about how you could chain these functions together by using message queues so that you could keep each function small and concise, which is the point. He also talked about some of the challenges they had in sharing code between different functions and how they solved that by coming up with a set of shared libraries. Azure functions is something I want to look at more, so this provided a good introduction to the service.<br />
<br />
<br />
<b>Other Details</b><br />
The event was extremely well organized. When checking in, everyone received a nicely printed program listing all of the talks and what room they were in. In addition, all of this information was posted on the outside of each room. Each room had plenty of seating and there was a water station set up in the back<br />
<br />
When I went to my room to setup my presentation, they had multiple video adapters up at the podium so you could connect your laptop to the projector. I carry my own adapters, but this is really thoughtful just in case someone forgot to bring their adapter. There was a staff member in each room to help with any setup issues as well. Finally, each room was equipped with a wireless mic. From a speakers perspective, the room setup was one of the best that I have seen.<br />
<br />
Both breakfast and lunch was provided at the event. In addition, they had a social event afterwards where everyone received two drink tickets plus a nice commemorative glass from the event. Way better than a t-shirt, which all of us already have zillions of. Now I have a nice glass that I can think back to the good memories any time I use it.<br />
<br />
The cost to attend was $99. While this might be a detriment to some, I think the event was well worth it. Some first rate speakers, a nice facility, meals and first rate organization. I don't have any connection to Centare other than speaking at this conference, but I think they are really to be commended for how well the event was put together. If you are in the upper Midwest next fall, be sure to watch for when the event is announced, because I really think this event is worth attending.<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com2tag:blogger.com,1999:blog-9038566846706115708.post-62285777660303421772016-10-29T10:20:00.001-07:002016-10-29T10:20:33.736-07:00Slides from my MKE Dot Net talk on SQL Server PerformanceThanks to everyone who attended my talk at MKE Dot Net today. The slides are available by clicking on the image or the link below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://drive.google.com/open?id=0B-kpH7AX6Uc5NW5GTng4VmwyX0k" target="_blank"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5h5aYmbfeSuPJcDHtFDM4FtVT6Y7mIFxW6_k817iMVzIDrS5JWbukMw_f8KcJR-gE2ujUUocETDUdtcXw56w7mvUFP8MPx4K_BIt9yMLu1jhh1U3DZ-38bny89CaPT562L3s4JDVQ_tNJ/s400/SQL-Server-Performance-For-Developers.png" width="400" /></a></div>
<br />
<br />
<a href="https://drive.google.com/open?id=0B-kpH7AX6Uc5NW5GTng4VmwyX0k" target="_blank">MKE Dot Net - What Every Developer Should Know About SQL Server Performance - Slides</a><br />
<br />
Thanks again for attending.Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com1tag:blogger.com,1999:blog-9038566846706115708.post-71861991401347511362016-07-24T18:02:00.000-07:002016-07-24T18:02:33.828-07:00What About Indexing SQL Server Tables That do Not Have a Primary Key or Cluster KeyI had a question in the comments section of my recent Pluralsight course, and the basic premise of the question was, "If your table does not have a primary key or cluster key, is it still worth it to create an index on the table?". The answer to this question is absolutely, yes. I want to take this opportunity to discuss this topic further though.<br />
<br />
<b>The Most Common Scenario - A Review</b><br />
In my course, I describe a the most typical setup in SQL Server, such that when you create a table with a primary key, the primary key column(s) are used as the cluster key of the table, and the cluster key is what the data in the table is physically sorted by. What this means is that the actual table data in SQL Server is stored in a B-tree structure something like what you see below.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxi3WbzhD3iG1ihfmyNEQbMO8Su5J4CvnO1NtgjySJBBWje2BOQocalzQ74Y9FXQbd4Rqqgy2W0SWfesDvhcJXW12Asfpg1xzxvJAMMOw_nNImSbT_t8Pp8wpVx-FSRkgflu52GWB62tlF/s1600/TableStructure.png" imageanchor="1"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxi3WbzhD3iG1ihfmyNEQbMO8Su5J4CvnO1NtgjySJBBWje2BOQocalzQ74Y9FXQbd4Rqqgy2W0SWfesDvhcJXW12Asfpg1xzxvJAMMOw_nNImSbT_t8Pp8wpVx-FSRkgflu52GWB62tlF/s640/TableStructure.png" width="640" /></a><br />
<br />
In computer science, a B-tree structure can be searched very rapidly, as only a handful of comparisons are required to get you to the lowest level of the table (the leaf level) where the data is stored. This is why looking up data in SQL Server by the primary key value is so fast, because by default, the primary key is also the cluster key, and then SQL Server can take advantage of this tree structure to rapidly find the associated row of data.<br />
<br />
Typically though, you need to search for data in a table on some other field or fields than the primary key. For example, you might not know a student's id number, but you know their first and last name. Without an index, SQL Server would have to scan through all of the rows of the table, and this would not just take a lot of time, but also be very resource intensive in terms of system resources (CPU and disk IO). So what we do is create an index on these columns since we commonly use them to search for data in the table. This index uses the same B-tree structure, but now this tree structure is organized (sorted) by the columns in the index key -- last name and first name in our case. And in the leaf nodes of the index (the bottom level), the index doesn't contain the data for the row, but instead the value of the primary key of the table.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJEU9Dp2Sn4WHdcR6IPFfBEx43Syd8xpFSZMr4DCssW2a2I8D5W54kpI-at7VDWTNKDSv14Dr9t5oqAtb-FRRRU2xEnBRoJX7ii8vc53xotqMrsOha9ObCSEIcz7LFfzshDIs_fzMHlSvM/s1600/IndexLookup.png" imageanchor="1"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJEU9Dp2Sn4WHdcR6IPFfBEx43Syd8xpFSZMr4DCssW2a2I8D5W54kpI-at7VDWTNKDSv14Dr9t5oqAtb-FRRRU2xEnBRoJX7ii8vc53xotqMrsOha9ObCSEIcz7LFfzshDIs_fzMHlSvM/s640/IndexLookup.png" width="640" /></a><br />
<br />
So what SQL Server will do is first traverse the index, which again is very fast because we are traversing a tree structure, and find all of the index keys that match the input criteria (WHERE clause) specified. Then, it will get the primary key values out of the index and go over to the table and look those values up by their primary key. And as we said before, these lookups in the table are very fast because the table is stored in a tree structure organized by the primary key.<br />
<br />
<b>How We Got Here</b><br />
What I did not mention in the course are some of the other scenarios that can occur. There is always a dilemma when putting together a course about what to put in and what to leave out, and I chose not to cover the less common scenarios because I felt like it was more important to make sure the viewer had a good understanding of the most common scenario. What I will do it go over those other scenarios here.<br />
<br />
<b>A Table With No Cluster Key</b><br />
It is possible in SQL Server to create a table with no cluster key. In this case, the rows of the table are stored in what is called a heap, which is just a way of saying that some space is allocated on disk and the rows of the table are stored in no particular order within that allocated area. So now we don't have our rows organized in a nice tree structure, they are just stored in effectively random order in whatever pages on disk the table is using.<br />
<br />
In this case, you can still (and should) create indexes on your table over the columns you search the table by. In this case though, the record in the index doesn't contain the primary key value, because that wouldn't really help us since our data isn't organized by primary key any more. Instead, it will contain a row pointer value that points tells SQL Server where it can find the corresponding row in the table. This row pointer value will include the the page the where the data is stored as well as a row identifier so it can find the row in the page. With this information, SQL Server can very quickly locate where the actual data for the row is, read it off of disk and return it to you. <br />
<br />
<b>A Table Key with a Cluster Key Different Than The Primary Key</b><br />
It is also possible to create a table that has a cluster key that is not the primary key. That is, in a fictional students table, my primary key is a column named StudentId, but I am going to make my cluster key on the columns LastName and FirstName. This would mean that the data I store in my table would be physically organized (sorted) by the combination of LastName and FirstName. <br />
<br />
You might think that would be a good idea, because after all, if I commonly search for students by their first and last name, then having the data organized like this would be making searching super fast, and I'd avoid the lookup step where you have to go look the actual row up by the student id after you have found the entry in the index. But before you do that, you should read this superbly written article by <a href="https://twitter.com/sqlfool">Michelle Ufford</a>.<br />
<br />
<a href="https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/"><i>Effective Clustered Indexes - https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/</i></a><br />
<br />
The problem is that as you insert data into the table, you are going to need to be inserting data in the middle of the table. That will lead to page splits, which will over time degrade your performance. Since our data is stored in a tree structure, it is much better if we are inserting new data at the end of the tree, not somewhere in the middle. <br />
<br />
Further, we want our cluster key be unique, since SQL Server has to have some way to uniquely identify rows. If you choose a cluster key that is not unique, SQL Server will have to add a unique identifier value to the cluster key such that it can uniquely identify the rows. <br />
<br />
Back to our question though, and lets say you have a table that has a cluster key different than the primary key and further, the cluster key is not unique. Should you still create indexes on the table? Absolutely, because otherwise you will have to scan each and every row of the table to locate your data. You will pay a small price because the cluster key is not unique, but have appropriate indexes will still be very beneficial to the overall performance of your application.<br />
<br />
<b>In Summary</b><br />
The rule still holds that for a table of any size, you want to make sure that your SQL Statement is using an index whenever accessing the table. The execution plan and resulting data paths might look a little different, but a well thought out index will still provide a major performance improvement for your SQL statements.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com7tag:blogger.com,1999:blog-9038566846706115708.post-32770812166672233082016-04-24T11:19:00.000-07:002016-04-24T11:19:04.072-07:00DMV Queries From my Pluralsight Course "What Every Developer Should Know About SQL Server Performance"I've actually broken these queries up into separate blog posts, so here are the links to the individual posts where you will find the queries.<br />
<br />
<a href="http://buildingbettersoftware.blogspot.com/2016/04/exposing-sql-server-dmv-data-to.html">Exposing SQL Server DMV Data to Developers and other non-DBA Users</a><br />
<br />
<a href="http://buildingbettersoftware.blogspot.com/2016/04/using-dmvs-to-find-sql-server.html">Using DMV's to Find SQL Server Connection and Session Info</a><br />
<br />
<a href="http://buildingbettersoftware.blogspot.com/2016/04/what-statements-are-running-in-my-sql.html">Which Statements Are Currently Running in my SQL Server Database</a><br />
<br />
<a href="http://buildingbettersoftware.blogspot.com/2016/04/finding-most-expensive-longest-running.html">Finding the Most Expensive, Longest Running Queries in SQL Server</a><br />
<br />
<a href="http://buildingbettersoftware.blogspot.com/2016/04/analyzing-index-usage-in-sql-server.html">Analyzing Index Usage in SQL Server</a><br />
<i>(Contains queries for both missing indexes and index usage stats)</i>Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com5tag:blogger.com,1999:blog-9038566846706115708.post-6107222360216936062016-04-24T11:04:00.000-07:002016-04-24T11:04:14.968-07:00Using DMV's to Find SQL Server Connection and Session InfoIt is often times useful to understand how your application is connecting to SQL Server and any other connections that may be present to your application's database. This can be understood by lookng at the <a href="https://msdn.microsoft.com/en-us/library/ms176013.aspx">sys.dm_exec_sessions</a> view. Here is a sample query to use:<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">SELECT</span>
database_id, <span style="color: #408080; font-style: italic;">-- SQL Server 2012 and after only</span>
session_id,
status,
login_time,
cpu_time,
memory_usage,
<span style="color: green; font-weight: bold;">reads</span>,
writes,
logical_reads,
host_name,
program_name,
host_process_id,
client_interface_name,
login_name <span style="color: green; font-weight: bold;">as</span> database_login_name,
last_request_start_time
<span style="color: green; font-weight: bold;">FROM</span> sys.dm_exec_sessions
<span style="color: green; font-weight: bold;">WHERE</span> is_user_process <span style="color: #666666;">=</span> <span style="color: #666666;">1</span>
<span style="color: green; font-weight: bold;">ORDER</span> <span style="color: green; font-weight: bold;">BY</span> cpu_time <span style="color: green; font-weight: bold;">DESC</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<br />
This simply lists all of the current connections to SQL Server, but we also get some useful information with each connection. We see we have fields like cpu_time (in milliseconds), memory_usage (in 8 KB pages), reads, writes and logical_reads. So if we have a session that is consuming a lot of resources in SQL Server, we can immediately see that by looking at these fields.<br />
<br />
We also have columns like host_name, program_name and host_process_id which can help us identify where a connection is coming from. You would be surprised how many times you look at a production database and see users connecting directly to the database from applications like Management Studio or even Excel. <br />
<br />
We can write a little bit different query to get a different look at this data:<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">SELECT</span>
login_name,
host_name,
host_process_id,
<span style="color: green; font-weight: bold;">COUNT</span>(<span style="color: #666666;">1</span>) <span style="color: green; font-weight: bold;">As</span> LoginCount
<span style="color: green; font-weight: bold;">FROM</span> sys.dm_exec_sessions
<span style="color: green; font-weight: bold;">WHERE</span> is_user_process <span style="color: #666666;">=</span> <span style="color: #666666;">1</span>
<span style="color: green; font-weight: bold;">GROUP</span> <span style="color: green; font-weight: bold;">BY</span>
login_name,
host_name,
host_process_id;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
This counts up all of the logins by login name and client process. So if you have an ASP.NET web application, you would see each worker process with the number of connections the worker process has to the database, plus any other processes that might be connected up. Sometimes this is a useful view, because you can validate that indeed all of the machines in your web cluster are indeed talking to the database and you can get a feel for how many connections they have in their connection pools.<br />
<br />
It might seem simple to know who is logged in, but what this can help you do is understand any connectivity issues you may be having and see if there is a session using a lot of resources, so for those reasons, these simple queries against the dm_exec_sessions view can tell you quite a bit.<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com2tag:blogger.com,1999:blog-9038566846706115708.post-9414534696743458042016-04-23T15:53:00.002-07:002016-04-23T15:53:51.695-07:00Exposing SQL Server DMV Data to developer and other non-DBA UsersAs a developer, it is very useful to have access to a number of SQL Server's Dynamic Management Views so you can view performance related statistics about how your application is interacting with the database. This includes finding which of your SQL Statements are taking the longest, if SQL Server thinks there are any missing indexes and usage data for your existing indexes.<br />
<br />
Unfortunately, access to DMV's in SQL Server requires the VIEW SERVER STATE permission. Many DBA's are reluctant to grant this permission to members of the development team because of the amount of data that one can see when they have this level of access. Unfortunately, this removes an important set of performance tuning tools from the developer's toolbox or forces them to ask a DBA for help whenever they need this information. There has to be a better way.<br />
<br />
What we need is a way that we can grant to a developer or any other non-DBA type user access to a select subset of DMV's information. For example, it is useful for a developer to see query execution statistics, missing index information and index usage data. Further, we would like to limit the information to just the database a developer is working in. So how do we do this?<br />
<br />
You would think this would be easy to do--create a view as a dba user that exposes only the information you want a developer to access and then grant the developer SELECT permission on that view. This fails though with the following message.<br />
<br />
<div style="background: grey; border: 1px;">
<i><span style="color: red; font-family: "courier new" , "courier" , monospace; font-size: x-small;">Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
</span></i></div>
<br />
<br />
The next thought you might have is to create a <a href="https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx" target="_blank">table valued function</a> with <a href="https://msdn.microsoft.com/en-us/library/ms188354.aspx" target="_blank">EXECUTE AS</a> set to a user that has permissions to view the DMV's. However, this also fails with an error message complaining about the module not being digitally signed. <br />
<br />
There is a solution, but it turned out to be much more complex than I would have initially thought. My approach is based on <a href="https://www.mssqltips.com/sqlservertip/3532/give-sql-server-users-access-to-extended-events/">this blog post</a> by Aaron Betrand, though I had to do things a little bit differently than he did. <br />
<br />
<h3>
The Solution</h3>
What really helps is to visualize the solution before we start going through the individual steps, so here is what it looks like.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinwoQ8e787vQw8WNEPXQya4rg8EJQu2dnv_54gO9S7cwC_imzq-Lf1D-M3b0gqiq6JGvwj95HQqBA7VHNMnw5939T8ATEYRsPeSPK-oCjnd41HOQZnlTdZ75GduWi1_6YdnyxOzI26u1-i/s1600/ExposingDmvsToDevelopers.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="267" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinwoQ8e787vQw8WNEPXQya4rg8EJQu2dnv_54gO9S7cwC_imzq-Lf1D-M3b0gqiq6JGvwj95HQqBA7VHNMnw5939T8ATEYRsPeSPK-oCjnd41HOQZnlTdZ75GduWi1_6YdnyxOzI26u1-i/s640/ExposingDmvsToDevelopers.png" width="640" /></a></div>
<br />
What we have to do is create a database (called PerfDB in this example) that is going to be a holding area for a set of table valued functions and views that we create to expose our DMV information. This database will have the option TRUSTWORTHY ON option set, which will allow our table valued function to execute as a user with VIEW SERVER STATE permissions and access the DMV's (this gets around the error about function not being digitally signed that was mentioned above). Then, we can create the rest of our objects to expose the DMv information we want to. This approach gives us a way to expose just the information we want, which is what we are after.<br />
<br />
Let's walk through the individual steps we need to take for all of this to work.<br />
<br />
<br />
<b>Step 1 - Create your PerfDB container database</b><br />
So to get started, we'll create the PerfDB database. I did this in SQL Server Management Studio using the UI, mainly because that is the easiest way for me to do it.<br />
<br />
Once the database is created, I open a query window and run the following command to set the TRUSTWORTHY option to ON.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">ALTER</span> <span style="color: green; font-weight: bold;">DATABASE</span> PerfDB <span style="color: green; font-weight: bold;">SET</span> TRUSTWORTHY <span style="color: green; font-weight: bold;">ON</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Some DBA's might have some concerns about setting TRUSTWORTHY to ON, but that is the reason for using a separate database that is just a container for the objects we create. By separating these objects out into a separate database, we've limited any security risks we might have.<br />
<br />
<div>
<br /></div>
<b>Step 2 - Create the Custom DMV Performance Functions in the PerfView Database</b><br />
Now we are ready to create a table valued functions which will query DMV's of interest for us. In this example, I am creating a function that will return back the query execution statistics in SQL Server. You would create one of these functions for each piece of DMV info you wanted to expose.<br />
<br />
The function basically just executes a query against a DMV or DMVs and returns that data in a table object. In this function, you can see I am taking in a parameter of the database id so that in the next step, I'll be able to able to limit access for a user to only seeing data for one database.<br />
<br />
You will need to create this function as a user that has the VIEW SERVER STATE privilege, so this could be an existing user or you could create a separate, dedicated user that owns this function and others like it. What is important is that this function has the WITH EXECUTE AS SELF option set, so that it will run as the user who created the function. This will allow this function to query the DMV's even if the calling user does not have permission to see the DMV's.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">CREATE</span> <span style="color: green; font-weight: bold;">FUNCTION</span> dbo.GetSqlExecutionStatistics(<span style="color: #666666;">@</span>database_id <span style="color: green;">INT</span>)
<span style="color: green; font-weight: bold;">RETURNS</span> <span style="color: #666666;">@</span>x <span style="color: green; font-weight: bold;">TABLE</span>
(
DatabaseId <span style="color: green;">INT</span>,
DatabaseName <span style="color: green;">VARCHAR</span>(<span style="color: #666666;">100</span>),
ExecutionCount <span style="color: green;">BIGINT</span>,
CpuPerExecution <span style="color: green;">BIGINT</span>,
TotalCpu <span style="color: green;">BIGINT</span>,
IOPerExecution <span style="color: green;">BIGINT</span>,
TotalIO <span style="color: green;">BIGINT</span>,
AverageElapsedTime <span style="color: green;">BIGINT</span>,
AverageTimeBlocked <span style="color: green;">BIGINT</span>,
AverageRowsReturned <span style="color: green;">BIGINT</span>,
TotalRowsReturned <span style="color: green;">BIGINT</span>,
QueryText NVARCHAR(<span style="color: green; font-weight: bold;">max</span>),
ParentQuery NVARCHAR(<span style="color: green; font-weight: bold;">max</span>),
ExecutionPlan XML,
CreationTime DATETIME,
LastExecutionTime DATETIME
)
<span style="color: green; font-weight: bold;">WITH</span> <span style="color: green; font-weight: bold;">EXECUTE</span> <span style="color: green; font-weight: bold;">AS</span> <span style="color: green; font-weight: bold;">OWNER</span>
<span style="color: green; font-weight: bold;">AS</span>
<span style="color: green; font-weight: bold;">BEGIN</span>
<span style="color: green; font-weight: bold;">INSERT</span> <span style="color: #666666;">@</span>x (DatabaseId, DatabaseName, ExecutionCount, CpuPerExecution, TotalCpu, IOPerExecution, TotalIO,
AverageElapsedTime, AverageTimeBlocked, AverageRowsReturned, TotalRowsReturned,
QueryText, ParentQuery, ExecutionPlan, CreationTime, LastExecutionTime)
<span style="color: green; font-weight: bold;">SELECT</span>
[DatabaseId] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">CONVERT</span>(<span style="color: green;">int</span>, epa.value),
[DatabaseName] <span style="color: #666666;">=</span> DB_NAME(<span style="color: green; font-weight: bold;">CONVERT</span>(<span style="color: green;">int</span>, epa.value)),
[ExecutionCount] <span style="color: #666666;">=</span> qs.execution_count,
[CpuPerExecution] <span style="color: #666666;">=</span> total_worker_time <span style="color: #666666;">/</span> qs.execution_count ,
[TotalCpu] <span style="color: #666666;">=</span> total_worker_time,
[IOPerExecution] <span style="color: #666666;">=</span> (total_logical_reads <span style="color: #666666;">+</span> total_logical_writes) <span style="color: #666666;">/</span> qs.execution_count ,
[TotalIO] <span style="color: #666666;">=</span> (total_logical_reads <span style="color: #666666;">+</span> total_logical_writes) ,
[AverageElapsedTime] <span style="color: #666666;">=</span> total_elapsed_time <span style="color: #666666;">/</span> qs.execution_count,
[AverageTimeBlocked] <span style="color: #666666;">=</span> (total_elapsed_time <span style="color: #666666;">-</span> total_worker_time) <span style="color: #666666;">/</span> qs.execution_count,
[AverageRowsReturned] <span style="color: #666666;">=</span> total_rows <span style="color: #666666;">/</span> qs.execution_count,
[TotalRowsReturned] <span style="color: #666666;">=</span> total_rows,
[QueryText] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">SUBSTRING</span>(qt.<span style="color: green;">text</span>,qs.statement_start_offset<span style="color: #666666;">/2</span> <span style="color: #666666;">+1</span>,
(<span style="color: green; font-weight: bold;">CASE</span> <span style="color: green; font-weight: bold;">WHEN</span> qs.statement_end_offset <span style="color: #666666;">=</span> <span style="color: #666666;">-1</span>
<span style="color: green; font-weight: bold;">THEN</span> LEN(<span style="color: green; font-weight: bold;">CONVERT</span>(nvarchar(<span style="color: green; font-weight: bold;">max</span>), qt.<span style="color: green;">text</span>)) <span style="color: #666666;">*</span> <span style="color: #666666;">2</span>
<span style="color: green; font-weight: bold;">ELSE</span> qs.statement_end_offset <span style="color: green; font-weight: bold;">end</span> <span style="color: #666666;">-</span> qs.statement_start_offset)
<span style="color: #666666;">/2</span>),
[ParentQuery] <span style="color: #666666;">=</span> qt.<span style="color: green;">text</span>,
[ExecutionPlan] <span style="color: #666666;">=</span> p.query_plan,
[CreationTime] <span style="color: #666666;">=</span> qs.creation_time,
[LastExecutionTime] <span style="color: #666666;">=</span> qs.last_execution_time
<span style="color: green; font-weight: bold;">FROM</span> sys.dm_exec_query_stats qs
<span style="color: green; font-weight: bold;">CROSS</span> APPLY sys.dm_exec_sql_text(qs.sql_handle) <span style="color: green; font-weight: bold;">as</span> qt
<span style="color: green; font-weight: bold;">OUTER</span> APPLY sys.dm_exec_query_plan(qs.plan_handle) p
<span style="color: green; font-weight: bold;">OUTER</span> APPLY sys.dm_exec_plan_attributes(plan_handle) <span style="color: green; font-weight: bold;">AS</span> epa
<span style="color: green; font-weight: bold;">WHERE</span> epa.attribute <span style="color: #666666;">=</span> <span style="color: #ba2121;">'dbid'</span>
<span style="color: green; font-weight: bold;">AND</span> <span style="color: green; font-weight: bold;">CONVERT</span>(<span style="color: green;">int</span>, epa.value) <span style="color: #666666;">=</span> <span style="color: #666666;">@</span>database_id;
<span style="color: green; font-weight: bold;">RETURN</span>;
<span style="color: green; font-weight: bold;">END</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<br />
<b>Step 3 - Create a Database Specific View to Wrap Your Function</b><br />
I'll create a view specific to the database I want to expose data for and pass in the appropriate database id to the function created in step 2. The reason I am doing this is because ultimately, I'll give my users SELECT access on this view and not directly on the table valued function. This way I have the logic (my query) in just one place (the function) that I can use over and over again, but what my users will see is their specific view for their specific database.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">CREATE</span> <span style="color: green; font-weight: bold;">VIEW</span> AppDatabaseName_SqlExecutionStatistics <span style="color: green; font-weight: bold;">AS</span>
<span style="color: green; font-weight: bold;">SELECT</span> <span style="color: #666666;">*</span> <span style="color: green; font-weight: bold;">FROM</span> GetSqlExecutionStatistics( <span style="color: #666666;"><<</span>application <span style="color: green; font-weight: bold;">database</span> id<span style="color: #666666;">>></span> );
</pre>
</td></tr>
</tbody></table>
</div>
<br />
You would replace <i>AppDatabaseName</i> in the name of this view with the name of your application database. You would also create a view for each different app database you wanted developers to have access to. <br />
<br />
If you need to know the database id of your database, this information can be obtained from the sys.databases view in the master database.<br />
<br />
<br />
<b>Step 4 - Grant Permission for Users to Query From the View</b><br />
For your user's to be able to query this information, we need to grant them the SELECT privilege to the view we just created in step 4. But before we do that, we need to map their login to a user in the PerfDB database. Basically, they have to have access to the database before they can even see the view, so we can do this in the Management Studio GUI or by running the following command.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3</pre>
</td><td><pre style="line-height: 125%; margin: 0;">USE PerfDB;
<span style="color: green; font-weight: bold;">CREATE</span> <<username>> <span style="color: green; font-weight: bold;">FROM</span> LOGIN <<user login name>>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
For every user you want to have access to this information, you need to do this. So that means if you have five developers who need access to this information, you'll need to give all five of those developer logins access to the PerfDB database in this way. <br />
<br />
Then, we can grant permission so those user's can query the view.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">GRANT</span> <span style="color: green; font-weight: bold;">SELECT</span> <span style="color: green; font-weight: bold;">ON</span> AppDatabaseName_SqlExecutionStatistics
<span style="color: green; font-weight: bold;">TO</span> <<username>>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
You will probably want to create a role that has select permissions for all of the DMV views you create for a database, and then put user's inside of that view. The important point though is that you have to get the user's permission to be able to select from the view.<br />
<br />
<br />
<b>Step 5 - Querying the Data</b><br />
Querying the data is as simple as Querying the view we just created, and this can be done by our non-DBA user who does <b><i>not</i> </b>have VIEW SERVER STATE permission.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">SELECT</span> <span style="color: #666666;">*</span>
<span style="color: green; font-weight: bold;">FROM</span> PerfDb.dbo.AppDatabaseName_SqlExecutionStatistics;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
If you want to make things a little easier, you can create a synonym in the application database so user's don't have to fully qualify the object name in their queries.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">CREATE</span> SYNONYM [dbo].[QueryExecutionStatistics]
<span style="color: green; font-weight: bold;">FOR</span> [PerfDb].[dbo].[AppDatabaseName_SqlExecutionStatistics]
<span style="color: green; font-weight: bold;">GO</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
And now users can simply run this query<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">SELECT</span> <span style="color: #666666;">*</span> <span style="color: green; font-weight: bold;">FROM</span> QueryExecutionStatistics;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Now, normal user's who have access to this view can access the information contained within. This also allows you to selectively choose what information is exposed from your DMV's since you control the query inside of the table valued function and you can limit the results to data from just a single database.<br />
<br />
Its unfortunate there isn't a more straightforward way to do this, but at least it can be done. And of course all of this work can be incorporated into scripts, making it much easier to run.<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com5tag:blogger.com,1999:blog-9038566846706115708.post-5394327743722660622016-04-02T18:07:00.000-07:002016-04-20T19:48:08.604-07:00Analyzing Index Usage in SQL ServerFor our applications, we need to have effective indexes to insure acceptable database performance. This really boils down into two questions.<br />
<br />
<b>Am I missing any indexes that I should have in my database?</b><br />
If I am missing an index, it is likely that SQL Server will have to resort to a scan operation over the table in order to find the data needed for a SQL Statements. This means SQL Server will have to read and process each row of the table, which is not only slow but also resource intensive. <br />
<br />
<b>Do I have any indexes that are not being used that I should drop?</b><br />
Indexes are not free. They are a separate physical structure that SQL Server has to maintain whenever a DML (insert, update or delete) statement is executed against the table. As long as an index is being used, it is a reasonable trade off to pay the price of maintaining the index because usually, a queries that use the index are run far more frequently than DML statements that have to update the index. But how do we know this is the case?<br />
<br />
These questions can be easily answered using SQL Server dynamic management views. DBA's have known about these DMV's for years, but many developers do not. While in some organizations a DBA is fully involved with the development of an application, in my experience this is not the case in most organizations. So it is the development team that is creating tables and writing SQL for their data access layer. To do this effectively and have a well performing application, they need visibility to what is happening inside of SQL Server, especially views like the ones I am about to show you that helps someone determine if they have the right indexes created.<br />
<br />
So let's see how DMV's can help answer these questions<br />
<h3>
<br />Finding Missing Indexes</h3>
Here is the query we are going to use<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">SELECT</span>
TableName <span style="color: #666666;">=</span> d.<span style="color: green; font-weight: bold;">statement</span>,
d.equality_columns,
d.inequality_columns,
d.included_columns,
s.user_scans,
s.user_seeks,
s.avg_total_user_cost,
s.avg_user_impact,
AverageCostSavings <span style="color: #666666;">=</span> ROUND(s.avg_total_user_cost <span style="color: #666666;">*</span> (s.avg_user_impact<span style="color: #666666;">/100</span>.<span style="color: #666666;">0</span>), <span style="color: #666666;">3</span>),
TotalCostSavings <span style="color: #666666;">=</span> ROUND(s.avg_total_user_cost <span style="color: #666666;">*</span> (s.avg_user_impact<span style="color: #666666;">/100</span>.<span style="color: #666666;">0</span>) <span style="color: #666666;">*</span> (s.user_seeks <span style="color: #666666;">+</span> s.user_scans),<span style="color: #666666;">3</span>)
<span style="color: green; font-weight: bold;">FROM</span> sys.dm_db_missing_index_groups <span style="color: green; font-weight: bold;">g</span>
<span style="color: green; font-weight: bold;">INNER</span> <span style="color: green; font-weight: bold;">JOIN</span> sys.dm_db_missing_index_group_stats s
<span style="color: green; font-weight: bold;">ON</span> s.group_handle <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">g</span>.index_group_handle
<span style="color: green; font-weight: bold;">INNER</span> <span style="color: green; font-weight: bold;">JOIN</span> sys.dm_db_missing_index_details d
<span style="color: green; font-weight: bold;">ON</span> d.index_handle <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">g</span>.index_handle
<span style="color: green; font-weight: bold;">WHERE</span> d.database_id <span style="color: #666666;">=</span> db_id()
<span style="color: green; font-weight: bold;">ORDER</span> <span style="color: green; font-weight: bold;">BY</span> TableName, TotalCostSavings <span style="color: green; font-weight: bold;">DESC</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
This query works by joining together the <a href="https://msdn.microsoft.com/en-us/library/ms345407.aspx" target="_blank">sys.dm_db_missing_index_groups</a>, <span style="background-color: #f8f8f8; line-height: 16.25px;"><a href="https://msdn.microsoft.com/en-us/library/ms345421.aspx" target="_blank">sys.dm_db_missing_index_group_stats</a>, and </span><span style="background-color: #f8f8f8; line-height: 16.25px;"><a href="https://msdn.microsoft.com/en-us/library/ms345434.aspx" target="_blank">sys.dm_db_missing_index_details</a> views. It also limits is results to just the current database by virtue of line 18. If you wanted to get results database wide, you would simply remove this WHERE criteria.</span><br />
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;">These views give us information on the index SQL Server believes should be created. This includes the table name (like 2 - no idea why this is called statement in the DMV), the columns that should be part of the index key (lines 3 and 4) and any possible include columns for the index (line 5). Between these columns, we could construct our create index statement.</span><br />
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;">We also get some statistics around why SQL Server believes the index should be created. The user_scans and user_seeks values (lines 6 and 7) represent how many times this index could have been used in one of the operations had the index existed. So if you see a high number, especially in the user_seeks columns, this means there are large numbers of statements executing that could benefit from this index.</span><br />
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;">The avg_total_user_cost column (line 8) gives us the average cost of statements that have been run against this table that would benefit from the index. The avg_user_impact column (line 9) tells us the percent that SQL Server believes this cost would be reduced by creating this index. From these two values, you can calculate some the average cost savings per statement (line 10) and the total cost savings for all statement executions (line 11) to give you an idea of how significant the cost savings might be.</span><br />
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;">I would, however, encourage you to<b><i> not </i></b>simply go and create an index for every row in this table. What you want to do is look through the recommendations in this table and look for patterns. You will find a number of recommendations for each table and many of these will be similar. So what you want to do is analyze these together and figure out which indexes make sense. This may also mean modifying an existing index rather than creating a new index. With this view, you can see all the recommendations that exist for a table and come up with the right set of indexes for the table.</span><br />
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<br />
<h3>
<span style="background-color: #f8f8f8; line-height: 16.25px;">Index Usage Statistics</span></h3>
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;">Let's now tackle the problems of making sure our indexes are being used and identifying indexes that may need to be dropped.</span><br />
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">SELECT</span>
[DatabaseName] <span style="color: #666666;">=</span> DB_Name(db_id()),
[TableName] <span style="color: #666666;">=</span> OBJECT_NAME(i.object_id),
[IndexName] <span style="color: #666666;">=</span> i.name,
[IndexType] <span style="color: #666666;">=</span> i.type_desc,
[TotalUsage] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">IsNull</span>(user_seeks, <span style="color: #666666;">0</span>) <span style="color: #666666;">+</span> <span style="color: green; font-weight: bold;">IsNull</span>(user_scans, <span style="color: #666666;">0</span>) <span style="color: #666666;">+</span> <span style="color: green; font-weight: bold;">IsNull</span>(user_lookups, <span style="color: #666666;">0</span>),
[UserSeeks] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">IsNull</span>(user_seeks, <span style="color: #666666;">0</span>),
[UserScans] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">IsNull</span>(user_scans, <span style="color: #666666;">0</span>),
[UserLookups] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">IsNull</span>(user_lookups, <span style="color: #666666;">0</span>),
[UserUpdates] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">IsNull</span>(user_updates, <span style="color: #666666;">0</span>)
<span style="color: green; font-weight: bold;">FROM</span> sys.indexes i
<span style="color: green; font-weight: bold;">INNER</span> <span style="color: green; font-weight: bold;">JOIN</span> sys.objects o
<span style="color: green; font-weight: bold;">ON</span> i.object_id <span style="color: #666666;">=</span> o.object_id
<span style="color: green; font-weight: bold;">LEFT</span> <span style="color: green; font-weight: bold;">OUTER</span> <span style="color: green; font-weight: bold;">JOIN</span> sys.dm_db_index_usage_stats s
<span style="color: green; font-weight: bold;">ON</span> s.object_id <span style="color: #666666;">=</span> i.object_id
<span style="color: green; font-weight: bold;">AND</span> s.index_id <span style="color: #666666;">=</span> i.index_id
<span style="color: green; font-weight: bold;">WHERE</span>
(OBJECTPROPERTY(i.object_id, <span style="color: #ba2121;">'IsMsShipped'</span>) <span style="color: #666666;">=</span> <span style="color: #666666;">0</span>)
<span style="color: green; font-weight: bold;">ORDER</span> <span style="color: green; font-weight: bold;">BY</span> [TableName], [IndexName];
</pre>
</td></tr>
</tbody></table>
</div>
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span><span style="background-color: #f8f8f8; line-height: 16.25px;">This query uses the sys.dm_db_index_usage_stats table and joins it with the sys.indexes system view. Again, we are only getting data for the current database, this time because the sys.objects and sys.indexes views only contain data for the current database</span><br />
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;">We have some information about the index, including the table it is on, the index name and the index type. In the index type column, two of the most common values you will see are CLUSTERED and NONCLUSTERED. CLUSTERED refers to a clustered index, which is how SQL Server typically lays out the data for a table. We really want to focus on NONCLUSTERED indexes, because these are indexes we have created on the table for some specific purpose, and we have control over the columns in those indexes and if they should exist or not.</span><br />
<span style="background-color: #f8f8f8; line-height: 16.25px;"><br /></span>
<span style="background-color: #f8f8f8; line-height: 16.25px;">There are three types of ways an index can be used:</span><br />
<br />
<ul>
<li><span style="line-height: 16.25px;">user_seeks - SQL Server is traversing the b-tree structure of the index to perform a lookup on a key. This is the operation we want to see. SQL Server is using the index as intended.</span></li>
<li><span style="line-height: 16.25px;">user_scans - SQL Server is reading the entire index to find the value(s) it wants. If this is a clustered index, that means SQL Server is reading the entire table. If this is a Nonclustered index, all of the index keys have to be read. This is much more expensive than a seek operation</span></li>
<li><span style="line-height: 16.25px;">user_lookups - These are lookup operations against the table, typically when SQL Server is looking up a row in the clustered index structure of a table.</span></li>
</ul>
<div>
<span style="line-height: 16.25px;">The user_updates column (line 10) gives the maintenance cost of the index -- that is how many time a DML statement has caused this index to need to be updated.</span></div>
<div>
<span style="line-height: 16.25px;"><br /></span></div>
<div>
<span style="line-height: 16.25px;">What you want to look for are indexes with very few or zero user_seeks. These are indexes that you are paying to maintain, but for whatever reason SQL Server is not able to use. And then you want to investigate why that index is not being used. Maybe the index is a unique index and is there to only to enforce a constraint. Maybe something in the application has changed so an index is no longer used. Maybe the columns are in the wrong order or the index is not selective enough. You want to figure this out and then either A) modify the index so it can be used or B) drop the index. These stats give you the visibility into what indexes you need to look at.</span></div>
<div>
<span style="line-height: 16.25px;"><br /></span></div>
<div>
<span style="line-height: 16.25px;"><br /></span></div>
<div>
<span style="line-height: 16.25px;"><br /></span></div>
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com4tag:blogger.com,1999:blog-9038566846706115708.post-5334269199124519072016-04-02T13:13:00.000-07:002016-04-02T13:13:08.573-07:00What Statements are Running in my SQL Server DatabaseSometimes you need to know what statements are running in your SQL Server database right now. I've often had this need when someone shows up at me desk and says something like "Something is going on! Your app is performing really slowly right now!". <br />
<br />
In situations like this, there is a need to triage what is going on with the app. Is it application code? What about the web server, is the CPU spiked there? What about the database? Do we have some long running queries that are making things appear to be stuck? Once we figure out what tier is responsible for the problem, we can drill down further to find and fix specifically what is going on.<br />
<br />
To that end, one of the SQL Server queries I keep around is one that will query the SQL Server DMV's and report back all of the statements that are running right now in the database. For any database that is in use, this query will always return some rows, because generally there is always some process somewhere that is running an application. But you can also use this query to look for any long running or blocked statements, so lets take a look at the query.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">SELECT</span>
[DatabaseName] <span style="color: #666666;">=</span> db_name(rq.database_id),
s.session_id,
rq.status,
[SqlStatement] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">SUBSTRING</span> (qt.<span style="color: green;">text</span>,rq.statement_start_offset<span style="color: #666666;">/2</span>,
(<span style="color: green; font-weight: bold;">CASE</span> <span style="color: green; font-weight: bold;">WHEN</span> rq.statement_end_offset <span style="color: #666666;">=</span> <span style="color: #666666;">-1</span> <span style="color: green; font-weight: bold;">THEN</span> LEN(<span style="color: green; font-weight: bold;">CONVERT</span>(NVARCHAR(<span style="color: green; font-weight: bold;">MAX</span>),
qt.<span style="color: green;">text</span>)) <span style="color: #666666;">*</span> <span style="color: #666666;">2</span> <span style="color: green; font-weight: bold;">ELSE</span> rq.statement_end_offset <span style="color: green; font-weight: bold;">END</span> <span style="color: #666666;">-</span> rq.statement_start_offset)<span style="color: #666666;">/2</span>),
[ClientHost] <span style="color: #666666;">=</span> s.host_name,
[ClientProgram] <span style="color: #666666;">=</span> s.program_name,
[ClientProcessId] <span style="color: #666666;">=</span> s.host_process_id,
[SqlLoginUser] <span style="color: #666666;">=</span> s.login_name,
[DurationInSeconds] <span style="color: #666666;">=</span> datediff(s,rq.start_time,getdate()),
rq.start_time,
rq.cpu_time,
rq.logical_reads,
rq.writes,
[ParentStatement] <span style="color: #666666;">=</span> qt.<span style="color: green;">text</span>,
p.query_plan,
rq.wait_type,
[BlockingSessionId] <span style="color: #666666;">=</span> bs.session_id,
[BlockingHostname] <span style="color: #666666;">=</span> bs.host_name,
[BlockingProgram] <span style="color: #666666;">=</span> bs.program_name,
[BlockingClientProcessId] <span style="color: #666666;">=</span> bs.host_process_id,
[BlockingSql] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">SUBSTRING</span> (bt.<span style="color: green;">text</span>, brq.statement_start_offset<span style="color: #666666;">/2</span>,
(<span style="color: green; font-weight: bold;">CASE</span> <span style="color: green; font-weight: bold;">WHEN</span> brq.statement_end_offset <span style="color: #666666;">=</span> <span style="color: #666666;">-1</span> <span style="color: green; font-weight: bold;">THEN</span> LEN(<span style="color: green; font-weight: bold;">CONVERT</span>(NVARCHAR(<span style="color: green; font-weight: bold;">MAX</span>),
bt.<span style="color: green;">text</span>)) <span style="color: #666666;">*</span> <span style="color: #666666;">2</span> <span style="color: green; font-weight: bold;">ELSE</span> brq.statement_end_offset <span style="color: green; font-weight: bold;">END</span> <span style="color: #666666;">-</span> brq.statement_start_offset)<span style="color: #666666;">/2</span>)
<span style="color: green; font-weight: bold;">FROM</span> sys.dm_exec_sessions s
<span style="color: green; font-weight: bold;">INNER</span> <span style="color: green; font-weight: bold;">JOIN</span> sys.dm_exec_requests rq
<span style="color: green; font-weight: bold;">ON</span> s.session_id <span style="color: #666666;">=</span> rq.session_id
<span style="color: green; font-weight: bold;">CROSS</span> APPLY sys.dm_exec_sql_text(rq.sql_handle) <span style="color: green; font-weight: bold;">as</span> qt
<span style="color: green; font-weight: bold;">OUTER</span> APPLY sys.dm_exec_query_plan(rq.plan_handle) p
<span style="color: green; font-weight: bold;"> </span><span style="color: green; font-weight: bold;">LEFT</span> <span style="color: green; font-weight: bold;">OUTER</span> <span style="color: green; font-weight: bold;">JOIN</span> sys.dm_exec_sessions bs
<span style="color: green; font-weight: bold;">ON</span> rq.blocking_session_id <span style="color: #666666;">=</span> bs.session_id
<span style="color: green; font-weight: bold;"> </span><span style="color: green; font-weight: bold;">LEFT</span> <span style="color: green; font-weight: bold;">OUTER</span> <span style="color: green; font-weight: bold;">JOIN</span> sys.dm_exec_requests brq
<span style="color: green; font-weight: bold;">ON</span> rq.blocking_session_id <span style="color: #666666;">=</span> brq.session_id
<span style="color: green; font-weight: bold;">OUTER</span> APPLY sys.dm_exec_sql_text(brq.sql_handle) <span style="color: green; font-weight: bold;">as</span> bt
<span style="color: green; font-weight: bold;">WHERE</span> s.is_user_process <span style="color: #666666;">=1</span>
<span style="color: green; font-weight: bold;">AND</span> s.session_id <span style="color: #666666;"><></span> <span style="color: #666666;">@@</span>spid
<span style="color: green; font-weight: bold;">AND</span> rq.database_id <span style="color: #666666;">=</span> DB_ID() <span style="color: #408080; font-style: italic;">-- Comment out to look at all databases</span>
<span style="color: green; font-weight: bold;">ORDER</span> <span style="color: green; font-weight: bold;">BY</span> rq.start_time <span style="color: green; font-weight: bold;">ASC</span>;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<br />
This query is using the <a href="https://msdn.microsoft.com/en-us/library/ms176013.aspx" target="_blank">sys.dm_exec_sessions</a> view in conjunction with the <a href="https://msdn.microsoft.com/en-us/library/ms177648.aspx" target="_blank">sys.dm_exec_requests</a> view to get all of the current running statements in SQL Server. I am restricting this to the current database (line 39), but you can easily comment out this line to look database wide. <br />
<br />
With just these two views, we do have some useful information like what time this statement started executing (line 13), how long it has been running so far (line 12), how much CPU it has consumed so far (line 14) and how many logical reads it has performed so far (line 15). If we see a statement that has been running for a long time and/or has already consumed a lot of CPU and IO resources, then this is casuse for further investigation.<br />
<br />
I also like to get some information about who is running a SQL statement that might be taking a long time, and that is what lines 8 through 11 do. You would be amazed at how often someone logs into a <i>production </i>database from their desk and runs an ad-hoc query in the middle of the day from management studio or even Excel and has no idea that they are causing a performance impact on the database. Columns like this help to pinpoint what process is running a statement that might be consuming a lot of resources.<br />
<br />
Then, I'm pulling in some other useful information that we can look at in our result set. Bringing in the <a href="https://msdn.microsoft.com/en-us/library/ms181929.aspx" target="_blank">sys.dm_exec_sql_text</a> (line 30) allows us to include the statement that is running in the result set, which of course if a statement is taking a long time you want to know what the statement is. We also use <a href="https://msdn.microsoft.com/en-us/library/bb326654.aspx" target="_blank">sys.dm_exec_query_plan</a> (line 31) to pull in the execution plan for the statement. If something is taking a long time, you probably want to just be able to click through and see the execution plan, so this lets us do that.<br />
<br />
Finally, sys.dm_exec_requests contains a column called blocking_session_id. If this statement is currently blocked by another, this value will be non-zero. By joining this value back to the sys.dm_exec_seesions and sys.dm_exec_requests views a second time (lines 32-36), you can get information about the blocker (lines 21-26), like the blocking SQL and information about the program running that SQL. Sometimes, you may catch a statement that is blocked when you run this query, but if you run this query twice in a row and you see the same blocker, you know you have a problem.<br />
<br />
All in all, this query gives you the ability to get a good pulse on what is happening in your database right now. When you are in the middle of troubleshooting a performance problem, this is very useful, because at a glance, you can determine if the problem you are facing is in the database tier and if so, probably get a pretty good idea of what might be the cause. So keep this query handy, and next time you need to know what is happening right now inside of SQL Server, you will be prepared.<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com3tag:blogger.com,1999:blog-9038566846706115708.post-27657963839305287702016-04-02T11:26:00.000-07:002016-04-02T11:26:33.585-07:00Finding the Most Expensive, Longest Running Queries in SQL ServerOne of the things we often want to know is what are our worst performing statements in our application so we can fix those statements and improve our overall performance. We can easily do this by querying the dynamic management views in SQL Server.<br />
<br />
One of the great advantages of using the DMV's in this way is that we immediately get a global view of what our application is doing inside of SQL Server and where we might need to concentrate our performance tuning efforts. The statement below gives us a good idea of what statements are being run from our application, how often, how long they take and how many resources they use on average. That is a lot of information for the low cost of running a single query.<br />
<br />
To run this query, you will need to VIEW SERVER STATE permission in SQL Server. If you don't have this permission, ask your DBA to run this query for you and give you the results.<br />
<br />
OK, lets look at the query<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #f8f8f8; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: green; font-weight: bold;">SELECT</span> TOP <span style="color: #666666;">20</span>
DatabaseName <span style="color: #666666;">=</span> DB_NAME(<span style="color: green; font-weight: bold;">CONVERT</span>(<span style="color: green;">int</span>, epa.value)),
[Execution <span style="color: green; font-weight: bold;">count</span>] <span style="color: #666666;">=</span> qs.execution_count,
[CpuPerExecution] <span style="color: #666666;">=</span> total_worker_time <span style="color: #666666;">/</span> qs.execution_count ,
[TotalCPU] <span style="color: #666666;">=</span> total_worker_time,
[IOPerExecution] <span style="color: #666666;">=</span> (total_logical_reads <span style="color: #666666;">+</span> total_logical_writes) <span style="color: #666666;">/</span> qs.execution_count ,
[TotalIO] <span style="color: #666666;">=</span> (total_logical_reads <span style="color: #666666;">+</span> total_logical_writes) ,
[AverageElapsedTime] <span style="color: #666666;">=</span> total_elapsed_time <span style="color: #666666;">/</span> qs.execution_count,
[AverageTimeBlocked] <span style="color: #666666;">=</span> (total_elapsed_time <span style="color: #666666;">-</span> total_worker_time) <span style="color: #666666;">/</span> qs.execution_count,
[AverageRowsReturned] <span style="color: #666666;">=</span> total_rows <span style="color: #666666;">/</span> qs.execution_count,
[Query <span style="color: green;">Text</span>] <span style="color: #666666;">=</span> <span style="color: green; font-weight: bold;">SUBSTRING</span>(qt.<span style="color: green;">text</span>,qs.statement_start_offset<span style="color: #666666;">/2</span> <span style="color: #666666;">+1</span>,
(<span style="color: green; font-weight: bold;">CASE</span> <span style="color: green; font-weight: bold;">WHEN</span> qs.statement_end_offset <span style="color: #666666;">=</span> <span style="color: #666666;">-1</span>
<span style="color: green; font-weight: bold;">THEN</span> LEN(<span style="color: green; font-weight: bold;">CONVERT</span>(nvarchar(<span style="color: green; font-weight: bold;">max</span>), qt.<span style="color: green;">text</span>)) <span style="color: #666666;">*</span> <span style="color: #666666;">2</span>
<span style="color: green; font-weight: bold;">ELSE</span> qs.statement_end_offset <span style="color: green; font-weight: bold;">end</span> <span style="color: #666666;">-</span> qs.statement_start_offset)
<span style="color: #666666;">/2</span>),
[Parent Query] <span style="color: #666666;">=</span> qt.<span style="color: green;">text</span>,
[Execution Plan] <span style="color: #666666;">=</span> p.query_plan,
[Creation Time] <span style="color: #666666;">=</span> qs.creation_time,
[<span style="color: green; font-weight: bold;">Last</span> Execution Time] <span style="color: #666666;">=</span> qs.last_execution_time
<span style="color: green; font-weight: bold;">FROM</span> sys.dm_exec_query_stats qs
<span style="color: green; font-weight: bold;">CROSS</span> APPLY sys.dm_exec_sql_text(qs.sql_handle) <span style="color: green; font-weight: bold;">as</span> qt
<span style="color: green; font-weight: bold;">OUTER</span> APPLY sys.dm_exec_query_plan(qs.plan_handle) p
<span style="color: green; font-weight: bold;">OUTER</span> APPLY sys.dm_exec_plan_attributes(plan_handle) <span style="color: green; font-weight: bold;">AS</span> epa
<span style="color: green; font-weight: bold;">WHERE</span> epa.attribute <span style="color: #666666;">=</span> <span style="color: #ba2121;">'dbid'</span>
<span style="color: green; font-weight: bold;">AND</span> epa.value <span style="color: #666666;">=</span> db_id()
<span style="color: green; font-weight: bold;">ORDER</span> <span style="color: green; font-weight: bold;">BY</span> [AverageElapsedTime] <span style="color: green; font-weight: bold;">DESC</span>; <span style="color: #408080; font-style: italic;">--Other column aliases can be used</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<b>This query selects the 20 statements in the current database that have the longest average elapsed time, that is, the 20 statements that on average took the longest to run. </b><br />
<br />
<ul>
<li>If you want more statements, then you can modify the "TOP 20" statement in line 1. You could also just remove the "TOP 20" criteria to get all of the statements running in this database.</li>
<li>You can sort the list by different criteria. CpuPerExecution and IOPerExecution are also good choices because these tell you what statements take the most resources to run.</li>
<li>If you have a batch process, you may get a few statements that were only run once or twice at the top of this list, because statements run in a batch process tend to be expensive by nature. Just add a criteria in the WHERE clause to only show statements with an execution count > 10, 20 or whatever if you want to exclude these.</li>
</ul>
<div>
<b>What units is the data in?</b></div>
<div>
<ul>
<li>The elapsed time and CPU time numbers are in microseconds, but the MSDN page claims these values are only accurate to the millisecond</li>
<li>The number of IO operations is in number of pages</li>
</ul>
<div>
<b>What should I look for?</b></div>
<div>
<ul>
<li>Statements that take a long time to run on average. For line of business applications, I want to understand anything taking over 500 ms. But if a statement takes 400 ms and every other statement takes only 50 ms, then I want to look at the 400 ms statement and see what is going on.</li>
<li>Any statement that has a high amount of average CPU or IO usage relative to its peers.</li>
<li>Statements with high numbers of executions. Is this legit? Is the app firing off this statement more than necessary? Is there a caching opportunity here?</li>
</ul>
</div>
<div>
The query above will return the execution plan for each statement in this list, so you can quickly click into the plan to see what is happening and if you want to take further action</div>
<div>
<b><br /></b></div>
<div>
<b>What if I want to see a different database on my SQL Server?</b></div>
</div>
<div>
<ul>
<li>This query is set up to look at the current database the user is in (line 25 - <i>epa_value = db_id()</i>). So just switch to the other database or comment out this line to see data for all statements across SQL Server</li>
</ul>
</div>
<div>
<b>How current is the data returned by this query?</b></div>
<div>
<ul>
<li>The data for this query comes from SQL Server's plan cache, which generally holds onto execution plans for statements that have been executed in the last few hours. Its a good idea to run this query a few different times throughout the day to capture any different loads that might be put on the database. Most of the data will probably agree, but a few different runs will give you a more complete overall picture.</li>
</ul>
</div>
<br />
<br />
In my opinion, this is probably the single most useful query of the DMV's because it immediately tells me if there are statements that aren't performing well and which ones they are. If you have a performance issue in your database, this query will take you right to the heart of what is going on. <br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com3tag:blogger.com,1999:blog-9038566846706115708.post-76443855834095439452016-02-28T12:26:00.001-08:002016-06-25T17:02:50.971-07:00Sample Database for "What Every Developer Should Know About SQL Server Performance"To download the sample database for 'What Every Developer Should Know About SQL Server Performance", use the link below that corresponds to your version of SQL Server.<br />
<br />
<br />
<ul>
<li><a href="https://drive.google.com/file/d/0B-kpH7AX6Uc5b1dRMENZSmVXWjQ/view?usp=sharing" target="_blank">SQL Server 2014</a></li>
<li><a href="https://drive.google.com/file/d/0B-kpH7AX6Uc5RWFiYjh6MndHem8/view?usp=sharing" target="_blank">SQL Server 2012</a></li>
<li><a href="https://drive.google.com/file/d/0B-kpH7AX6Uc5UmZKcmNDRlQtSVE/view?usp=sharing" target="_blank">SQL Server 2008</a></li>
</ul>
<div>
<br /></div>
<div>
Once you have the file downloaded, unzip the file. Then, in SQL Server Management Studio, right click on the databases folder and select "Restore Database". In the dialog box, choose "Device" and locate the "Students.bak" file you just unzipped. Follow the prompts and the database will be restored to your system.<br />
<br />
<h4>
<b>Using bcp to Import Data Instead</b></h4>
If you have trouble with the backup file, or just prefer to import your data directly, I've put together a zip file of the raw data exported out using the SQL Server bcp utility. In this file, I have a readme.txt file that tells the exact steps to bring the data in, but basically, you will do the following:<br />
<br />
<ol>
<li>Go into Management Studio and create a new database called Students.</li>
<li>Run the included Students-Schema.sql file in Management Studio to create all of the schema objects (tables)</li>
<li>At a command prompt, run a series of bcp commands to bring the data into SQL Server. In the file, there is a list of all the commands you need to run in the correct order. </li>
</ol>
<div>
The link to the bcp file is here: <a href="https://drive.google.com/open?id=0B-kpH7AX6Uc5TFlJaEtsbGQwWEE">Students Data as bcp Export</a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Either way, the result is to same, the data will get into your database and give you an opportunity to play around.</div>
</div>
Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com20tag:blogger.com,1999:blog-9038566846706115708.post-79212807589147384342015-08-09T09:35:00.003-07:002015-08-09T09:35:52.582-07:00Slides from That Conference for my Talk on Personal Development and Becoming a Lifelong LearnerOn Monday, August 10th, I will be speaking at <a href="https://www.thatconference.com/" target="_blank">That Conference</a> on the topic of personal development and how to become a lifelong learner. It is a great honor for me to be chosen to speak at That Conference. I also would like to extend a thank you to everyone who decides to attend my session. There are so many great sessions that are available, I am truly humbled that you have chosen to attend my talk. I hope in return I can share with you some practical tips that help you become a more effective personal learner.<br />
<br />
Of course, many times attendees are interested in the slides from the talk, so here those are.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://drive.google.com/file/d/0B-kpH7AX6Uc5dTdULVdpRDJqSjg/view?usp=sharing" target="_blank"><img border="0" height="225" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRj5Vn4YMZr78LhWW9u7JPRY_-oF_KQKOlBsaRg2EaMsPJ_vkWXPkHFsk5uW7FCfm2P6U9XkNXKHYrDeE6hwSbysfoeSN65KwUgfQfdf8CPUAFgmaWuS1SS9r5fev2s6t1N4S2ZBqF6fHO/s400/Personal-Development-Title-Slide.png" width="400" /></a><span id="goog_715912774"></span><span id="goog_715912775"></span><a href="https://www.blogger.com/"></a></div>
<br />
<br />
<br />
Also, I reference posts on the blog at a few points in the talk. You can search around for those, but I'll make those easy for you and just list them here.<br />
<br />
<br />
<ul>
<li><a href="http://buildingbettersoftware.blogspot.com/2015/08/why-standard-corporate-development-plan.html" target="_blank">Why the Standard Corporate Development Plan Doesn't Work (and What Does)</a></li>
<li><a href="http://buildingbettersoftware.blogspot.com/2015/08/using-trello-to-create-personal-skills.html" target="_blank">Using Trello to Create a Personal Skills Matrix</a></li>
<li><a href="http://buildingbettersoftware.blogspot.com/2015/08/some-books-to-help-develop-you.html" target="_blank">Some Books to Help Develop Your Professional Skills</a></li>
<li><a href="http://buildingbettersoftware.blogspot.com/2015/08/using-moocs-for-your-personal.html" target="_blank">Using MOOCs for Your Personal Development</a></li>
</ul>
<br />
Thanks again for attending. I am looking at turning the talk into a series of YouTube videos at some point, so those who are not able to attend conferences or code camps can still get the benefit of the talk. Check back here or follow me on <a href="https://twitter.com/davidcberry13" target="_blank">Twitter</a> for news on how that is going.<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com3tag:blogger.com,1999:blog-9038566846706115708.post-30731827508580435142015-08-08T22:22:00.003-07:002015-08-08T22:22:55.488-07:00Using Trello to Create a Personal Skills MatrixWhen creating a person development plan, you need to know what skills you are good at and what items you need improvement on. Furthermore, you want to be tracking this information over the long term. As you work on a skill, it will improve. Also though, new needs will emerge and you need to be able to add these to your matrix and decide where they fit in.<br />
<br />
What we are talking about here is conducting a skills assessment and getting our skills into a skills matrix. There are a number of tools that we could use for this, but I am going to show you how it can be done in a tool called <a href="https://trello.com/" target="_blank">Trello</a>.<br />
<br />
Trello is a free online <a href="https://en.wikipedia.org/wiki/Kanban_board" target="_blank">Kanban board</a>, but it is also easily adapted to a variety of other purposes. For our purpose, we are really just using the fact that we can create cards in the board, group them together in columns and do some basic commenting and sorting. For our skills matrix, we don't need to move cards between columns. Still, everything we need to do is well within Trello's capabilities.<br />
<br />
<h3>
Creating Your Skills Matrix</h3>
This is what we want our end product to look like:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8st4THQ9AoZZPcXz9W_m0pNELQOmV0z146QcPOO0f0ZHvovLLl-L6NhDjh3Elof8DpDQeT8o0QGQ7pP2H5xkEnSobbS3U79TPDr43l0RTmxFUKvsWJaMFMctwTywxEKdYv2uIGPn70oJy/s1600/SampleSkillsMatrix.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8st4THQ9AoZZPcXz9W_m0pNELQOmV0z146QcPOO0f0ZHvovLLl-L6NhDjh3Elof8DpDQeT8o0QGQ7pP2H5xkEnSobbS3U79TPDr43l0RTmxFUKvsWJaMFMctwTywxEKdYv2uIGPn70oJy/s640/SampleSkillsMatrix.png" width="640" /></a></div>
<br />
<br />
As you can see, each column forms a group of related skills. I have columns for .NET Development, Web Development, Database and Professional Skills. In reality, you would probably have a few more columns, I just want to keep this example to the point. Don't worry though, Trello will let you create as many columns as you need to. Then, within each column, you create an item (a card in Trello terminology) for each skill and give yourself a rating for that skill.<br />
<br />
OK, so how do we get to this point.<br />
<br />
<h3>
Creating Your Skills Matrix</h3>
Lets walk through step by step how to do this.<br />
<br />
<b><i>Create a Trello Account</i></b><br />
Go to <a href="http://trello.com/">Trello.com</a> and click on the big green button in the middle of the page. You can create an independent Trello account or login with your Google account. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgitUsBTFb53eRD0GOlIAfXIoK9VGcPc-zDqIRXu4_HYQKCQiUIyOmWHTnhnL9e4nxYvLMr5cuCuITkQzg6befHQNRsv7EzWpNuj8JwV70GYy6fWPCmUebKPTgjJsKzgNmdHq9_dOBbZ095/s1600/TrelloSignUp.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgitUsBTFb53eRD0GOlIAfXIoK9VGcPc-zDqIRXu4_HYQKCQiUIyOmWHTnhnL9e4nxYvLMr5cuCuITkQzg6befHQNRsv7EzWpNuj8JwV70GYy6fWPCmUebKPTgjJsKzgNmdHq9_dOBbZ095/s640/TrelloSignUp.png" width="640" /></a></div>
<br />
<br />
<br />
<b><i>Create Your First Board</i></b><br />
Click on the grey box that says "Create new board...". When prompted, call this board "Skills Matrix or something similar.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMBcTWTm7rN-ZnZtKGJa7rU9gaSb4CKLLF4yo2TlpcPSu4WU75O-ZJWbguyVXrGJkOh68nFQk6onjX9xRBKx-LJKFSQu7xZoQzBimLGIfMJNz_n_i2Q-0DIJ9dURgem7-XBv5x9BelCZJn/s1600/CreateNewTrelloBoard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMBcTWTm7rN-ZnZtKGJa7rU9gaSb4CKLLF4yo2TlpcPSu4WU75O-ZJWbguyVXrGJkOh68nFQk6onjX9xRBKx-LJKFSQu7xZoQzBimLGIfMJNz_n_i2Q-0DIJ9dURgem7-XBv5x9BelCZJn/s640/CreateNewTrelloBoard.png" width="640" /></a></div>
<br />
<br />
<br />
<br />
<b><i>Add Columns To Your Board</i></b><br />
On the left hand side of the board you will see a box that says "Add a list...". Click on the textbox here and add your first column. Once you have added this column, you will see this same box, just moved to the right, so keep clicking it to add all of the columns you need.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjr27XxR1oyV2Umh6DYbiSxK8Uazl9U2HYdwuDL_tlVsNskbYhCTW-xhUiHXe1B8-W5XqRusD_LaY0iWSz8AW5I6B4vF8r49gtc2JaVyPQLF0m6zmg-ntJixGhwHp-0cMGnki0izk9gtMY7/s1600/AddingLists.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjr27XxR1oyV2Umh6DYbiSxK8Uazl9U2HYdwuDL_tlVsNskbYhCTW-xhUiHXe1B8-W5XqRusD_LaY0iWSz8AW5I6B4vF8r49gtc2JaVyPQLF0m6zmg-ntJixGhwHp-0cMGnki0izk9gtMY7/s640/AddingLists.png" width="640" /></a></div>
<b><i><br /></i></b>
Remember, columns are just groupings of skills, and you can group these skills any way you want. You can also add columns at any time if a new skill group emerges or you want to break an existing column into two groups to make things easier to manage and visualize.<br />
<br />
Here are some sample groups you might consider:<br />
<br />
<ul>
<li>C#/.NET Development</li>
<li>Web Development (focusing on front end aspects like HTML, CSS, JavaScript and JS frameworks)</li>
<li>Database Development (SQL, Data Modeling, Query Tuning, etc)</li>
<li>Servers (IIS, Windows Server)</li>
<li>Cloud Technologies</li>
<li>Data Analysis and Reporting</li>
<li>Professional Skills (Soft Skills)</li>
</ul>
<br />
Don't feel like you have to have all of these categories or use the same ones I do. Come up with what works best for you. If you need to change it up later, no worries. Probably the only one that everyone should have is professional skills. The others depend on what role you have and what technologies you work with.<br />
<b><i><br /></i></b>
<b><i><br /></i></b>
<b><i>Add Labels To Your Board</i></b><br />
We need to be able to rate ourselves on each of these skills so we know where we are strong and where we need to improve. We are going to use a four point rating system as follows:<br />
<br />
<ul>
<li><b>Mastery (Green)</b> - I am highly proficient at this skill, so much that I can teach it to someone else. This is really something I know inside and out.</li>
<li><b>Proficient (Blue) </b>- This is something I am very good at. I can usually perform tasks involving this skill without asking questions or the help of others. I'm able to quickly perform these tasks and rarely have defects in my work</li>
<li><b>Developing (Orange) </b>- I have some experience with this skill, but these are items that tend to take longer than others as I am still learning this skill. I may have quite a few questions or need the assistance of others when doing these items. I tend to look a lot of things up. I may also have received feedback that this is an area I need to improve.</li>
<li><b>Novice (Red)</b> - This is a new skill to me. I have heard of it, but I have worked with it very little or not at all. But this is something I want to track because I feel it will be important for me to develop in this area at some point.</li>
</ul>
<br />
You actually can choose any color scheme you want, it is just that we want to break things down so we have quick visual cues of where we are at on our skills.<br />
<b><i><br /></i></b>
To do this in Trello, look on the right hand sidebar and click the link that says "More"<br />
<b><i><br /></i></b>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivYMxTu1dJp7JNjCbMxYr8nhyphenhyphen6GglCWRlzfEDFsnIgtWsNAQjem5gZBA_jFFsMLhI5y1xOIO2FqKLOG0MrooU6F2VUnz5yXiY6D1khogC3UWDU2vbmzgjMp524qCXBElQgzhr7_1ddCriS/s1600/AddingLabels-ClickingMore.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivYMxTu1dJp7JNjCbMxYr8nhyphenhyphen6GglCWRlzfEDFsnIgtWsNAQjem5gZBA_jFFsMLhI5y1xOIO2FqKLOG0MrooU6F2VUnz5yXiY6D1khogC3UWDU2vbmzgjMp524qCXBElQgzhr7_1ddCriS/s640/AddingLabels-ClickingMore.png" width="640" /></a></div>
<b><i><br /></i></b>
Then, again in the right hand sidebar, click the link that says "Labels"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgji7BmwUgydBvKt0m9e_GWMQeEuWLTIQ1VKoRX5WxdRuwJZw4vVhxipwjjv7eHS2PgEh4BoEvuc8LM7DTaLi7P0ocUHUO4sVgb_KhGM81xRTu_4LnNEDmxd5bW08bCTRCiaxSSatwLx6NR/s1600/AddingLabels-ClickingLabels.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgji7BmwUgydBvKt0m9e_GWMQeEuWLTIQ1VKoRX5WxdRuwJZw4vVhxipwjjv7eHS2PgEh4BoEvuc8LM7DTaLi7P0ocUHUO4sVgb_KhGM81xRTu_4LnNEDmxd5bW08bCTRCiaxSSatwLx6NR/s640/AddingLabels-ClickingLabels.png" width="640" /></a></div>
<br />
<br />
Finally, you will see the Label colors, and to give these a name, you just click on each one and type the names you want (the names that we gave above -- Mastery, Proficient, Developing, Novice)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk9rPXtpaCwj8roiqeACftyP7OZAiSYjtIPgqg8mYnzGhwLPVi3hiS190Jc-w-tIWFIUHOIY4qHsK3quy7MSqDqOKT2KJ1EEg5tz_IX4Q_Xbi4pKqeWI9P0ELwjwIt_YKjxKMtRL9N7BkB/s1600/AddingLabels-LabelNames.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk9rPXtpaCwj8roiqeACftyP7OZAiSYjtIPgqg8mYnzGhwLPVi3hiS190Jc-w-tIWFIUHOIY4qHsK3quy7MSqDqOKT2KJ1EEg5tz_IX4Q_Xbi4pKqeWI9P0ELwjwIt_YKjxKMtRL9N7BkB/s640/AddingLabels-LabelNames.png" width="640" /></a></div>
<br />
Now we will be able to add these to our cards when we start creating them below.<br />
<b><i><br /></i></b>
<b><i>Start Adding Cards for Each Skill You Have, Need or Want to Acquire</i></b><br />
Now, in each column, click on the link that says "Add a card..." and starting adding cards for each skill.<br />
<br />
<br />
<br />
<h3>
How Do I Know What Skills I Should Add</h3>
Glad you asked. The first thing is to ask yourself what are the skills needed for your current role. These are usually things like languages, frameworks and features of languages. For example, if you are a C# developer, you don't just want to put C# in the .NET Develpment column. Break this down by features that you need to use in your role. For example, you could have cards for each of the following:<br />
<br />
<ul>
<li>General C# Constructs</li>
<li>Object Orientated Programming</li>
<li>Generics</li>
<li>Parallel Execution/Threading/Async Programming</li>
<li>Entity Framework</li>
<li>WCF</li>
<li>Design Patterns</li>
<li>SOA</li>
</ul>
<br />
And so forth. You want to break things down such that each one is a self contained group of knowledge. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6SAwy7Qo9eEwQLSo3BL74Q4rfY1ffkcPETLhuMY9WAvZHeBHxBI-Y5XWTJ9npKthqlg_5cuendvTMDV4zGD_hdHZ4j-8CuKmH_Q3P391LHzjg3_-JEsE3lSj1M6ysGmojIDGzOJUdHcmR/s1600/AddingNewSkillsAsCard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6SAwy7Qo9eEwQLSo3BL74Q4rfY1ffkcPETLhuMY9WAvZHeBHxBI-Y5XWTJ9npKthqlg_5cuendvTMDV4zGD_hdHZ4j-8CuKmH_Q3P391LHzjg3_-JEsE3lSj1M6ysGmojIDGzOJUdHcmR/s640/AddingNewSkillsAsCard.png" width="640" /></a></div>
<br />
<br />
One good way to get an idea of the skills needed for your role is to look at job descriptions for titles similar to yours and see what skills employers are asking for. If you are a web developer and keep seeing Angular.js over and over again in various job postings, that is a pretty good indication that is an important skill and should be on your matrix somewhere.<br />
<br />
You also want to consider how your job is changing and where you want to go with your career. Ask yourself, if I am in my same role 12-24 months from now, what new technologies are emerging that I will need to know to remain effective in this job. If you are looking to move into a different role, look at others already in that role and ask yourself what skills do they have that make them successful in that role.<br />
<br />
You are likely to end up with quite a list, and that is the point. We will narrow down what we will work on later, but what we want to do is capture what knowledge we have or could need so we can prioritize what we need to work on.<br />
<br />
<h3>
This List is Meant to Be Dynamic</h3>
You just went to a user group meeting and everyone was talking about this great new JavaScript framework! Or cloud technology! Or super cool shiny fancy gizmo! This is going to happen, and this is one of the reasons we use Trello. It handles these dynamic situations well.<br />
<br />
Create a new card for this technology under the appropriate grouping. In the Card, add some notes about why you are excited about the technology and how it relates to the work you are doing. And now, you are tracking this skill along with all of your other skills. As you prioritize what you want to learn, you can evaluate your need for this skill with all of your other development opportunities. But the point is that you have written it down, so now it won't get lost or forgotten about. Maybe this is so important that you really will start working on learning it next month. Maybe it will be six months. No matter. The point is as you learn about any new skill that you need to learn, get it on the board so it can be tracked.<br />
<br />
<h3>
Rating Yourself in Terms of Proficiency</h3>
Now for each skill, you want to rate yourself using the scale described above. If you are unsure about a skill, a good way to determine where you are really at is to go look at the table of contents for a couple Pluralsight courses on the topic. Go through each major area and ask yourself if you really know that area well enough that you can perform that function without help. If there are a lot of areas you are unfamiliar with or feel you need help in, this is probably pointing to rating yourself lower.<br />
<br />
The important thing here is to be honest with yourself. This is not a job interview. This is for you, to know the areas you really need more practice in. No one else is going to see this other than you, so don't be afraid to be candid with where you need to improve.<br />
<br />
For items that you rate yourself lower in, you can also go into the card and add some comments about areas that you feel you need to improve. As we will see below, you aren't going to work on all of these at once, so it is useful to have some notes about what specific parts of the skill you think you need to improve at. Again, this is an advantage of Trello in that it supports this ability to store some notes along with each card.<br />
<br />
<h3>
Where Do I Go From Here?</h3>
At this point, you have lots of different skills in different categories, and more than a few of these will have ratings of novice or developing on them.<br />
<br />
So pick <i>at most two</i> of those skills to work on. And then create a plan to work on those one to two skills over the next six to eight weeks (I'll go into how to create this plan in another post). What we want is a focused effort for a relatively short duration of time so that we can move the one or two skills up to the next level. Then, we'll come back to the board and look at what the next set of skills are that we should focus on and repeat the process all over again. We might pick a specific skill a couple of times in a row if it is really important and an area we need to grow in, or we might pick different skills. But the point is we are really focusing in one one or two items at a time and improving ourselves in those areas.<br />
<br />
<h3>
Wrapping Up</h3>
You don't have to use Trello if there is another Kanban type board that you like and want to use. The important point is that we are cataloging our skills and getting a visual representation of where we are and what we need to work on. At a glance, we can tell where we need to be spending our time. And this helps us spend our development time more effectively, because we are spending our time improving on one of these topics, not wondering what we should work on next.<br />
<br />
This approach is also flexible. We can store notes to ourselves on each card, like specific areas we feel we need improvement in or the names of resources someone has recommended to us. When we hear about something new we think we might need to learn, we just have to add a card to our board, and not it is included in the big picture of how we need to develop. And through Trello, we have related skills grouped together, can filter on skills or search all of our cards. So this electronic version really makes a lot of sense.<br />
<br />
I hope this has been useful to you, and if you come up with any additional pointers, leave them in the comments so others can benefit from your learning's.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com4tag:blogger.com,1999:blog-9038566846706115708.post-15844242725551276622015-08-08T18:35:00.000-07:002015-08-08T18:35:02.997-07:00Using MOOC's for Your Personal DevelopmentWe have more resources than ever available for personal development today, and one of the resources that people often overlook are MOOCs. MOOC stands for Massive Open Online Course. They are courses that are offered by universities through a provider like Coursera or Edx that anyone can sign up for and take over the internet. When I say anyone, I mean anyone. Classes often start out with tens of thousands of students from all over the world. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://www.coursera.org/courses" target="_blank"><img border="0" height="83" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYnK946bnaKBzViz7gyvt-F_skyi-C1f9rnzsCi4V5ngWnZS5EZ-Bhc9qJE-z-xLqIyzDY3fSt-qxGk7KLE5wDBKSL8bB1-sxNoxv8PLinlqqIZ0Lj2OsY7DjK0NretMVawX9GxbISIyK9/s320/Coursera.png" width="320" /></a><span id="goog_824863595"></span><span id="goog_824863596"></span><a href="https://www.blogger.com/"></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://www.edx.org/course" target="_blank"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKpLzz1Z6AeLYzO7x7gmnG-wtj4pOOD2egWxSs9C_0s7TFrd4Y-eTLogSpnz-qQnb0pQZj1DlhJHnZxH7W9aKBhp79x_LkXwTDtfIUFjZNuZXRGQ8dSZRADXaZaMY1U0Pys19aRvncmXSB/s1600/edx.png" /></a></div>
<br />
<br />
Generally, you will watch 1-2 hours of videos each week, and often within the video there will be some practice quizzes. Videos can be watched at any time, so this is helpful for busy professionals who don't have time to take out of their day to go to a class at a specific time. You also typically have an assignment to complete each week. In many cases, these are peer graded, meaning other students will grade your work and provide feedback, while you do the same for their work. This actually turns out to be pretty effective and you get multiple perspectives on how you are learning.<br />
<br />
Usually just taking a class is free or you can pay a small fee ($50 - $100) to earn a verified certificate. At this time, these classes still don't carry the same weight as a standard university program, but attitudes on this may be starting to change. I also would say that the primary reason you should be doing this is for personal enrichment. The fact that you are learning is the most important thing, and in the end, if you are learning and improving yourself, that always shines through regardless of whether or not you earned a certificate.<br />
<br />
<h3>
What Types of Courses Are Available</h3>
Just about everything. This includes computer science courses, math courses, engineering courses and business courses.<br />
<br />
What many of these courses do is provide you a good introduction to the subject. Think of it like attending a 3 day seminar in the topic. That isn't going to be a full semester's worth of depth, but it will get you familiar with the fundamentals of the topic. Often times, I have seen similar such seminars advertised and these cost $1500-$2000. So it is really a good deal to be able to take courses like this for low or no cost. Once you take a course and know you are interested in the topic, you can always pursue additional studies as well.<br />
<br />
That doesn't mean that there aren't courses that get to depth. A friend of mine took the Algorithms course taught by Princeton on Coursera. He showed me the homework and I can say it was a very in-depth, difficult course. There are also Calculus courses that look like the equivalent to taking the same course on campus. So these are great if for some reason you need to go back to school and brush up on some of these topics.<br />
<br />
<br />
<h3>
Some Specific Recommendations</h3>
I want to recommend two courses that I have taken that I found to be very good. Both of these courses are in the business/management area, and I found them useful from a sense that they have helped me on gaining new perspectives about how decisions are made from a business perspective.<br />
<br />
<br />
<b>Developing Innovative Ideas for New Companies</b><br />
<div>
<a href="https://www.coursera.org/course/innovativeideas">https://www.coursera.org/course/innovativeideas</a></div>
<div>
<br /></div>
<div>
This course is aimed at people who are interested in starting their own business. By this I mean starting any business, not just a technology business. It is an introductory level course, but it does give you some exposure to some things you need to consider when thinking about starting a new business and putting together a business plan.</div>
<div>
<br /></div>
<div>
I think what I found most useful was that this course introduced me to something called the <a href="http://www.businessmodelgeneration.com/canvas/bmc" target="_blank">Business Model Canvas</a>. The business model canvas is a visual way to answer nine key questions about a new business you intend to start. These include areas like what is your value proposition, who are your customers, who are your partners and what is your cost structure. These are all factors that would go into a business plan, but by organizing this information visually, you can get a better idea of how it fits together and what areas really might need more thought.</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJTc9pBEl69R2bqFSzrHAcseMlt6NcqflmqdxTryl0ILhcUBWDaECCyHA3H23BZzO8SG6Dpt1srkz0HijWGsjaGvO50cKGhYI_5V4jXkE5da3or9_DO3FMenDIUZ1eOtkvuC_o4fvmyFXp/s1600/business-model-canvas.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="425" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJTc9pBEl69R2bqFSzrHAcseMlt6NcqflmqdxTryl0ILhcUBWDaECCyHA3H23BZzO8SG6Dpt1srkz0HijWGsjaGvO50cKGhYI_5V4jXkE5da3or9_DO3FMenDIUZ1eOtkvuC_o4fvmyFXp/s640/business-model-canvas.png" width="640" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
One thing to understand though is that a tool like the business model canvas is not just for someone who is starting a business. These are really questions that any business or non-profit should be able to answer at any time and clearly communicate to everyone involved. So using this tool, you can better understand how your company or your department fits into the larger business ecosystem and what are the real drivers behind the decisions that are made. To be honest, I wish every company would fill out and regularly update a business model canvas of their own, and then hang an extra large version up on a wall somewhere where everyone could see it and contribute to it. I think this would really help to bring a lot of clarity to what the mission was and how everything fits together.</div>
<div>
<br /></div>
<div style="direction: ltr; language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; mso-line-break-override: none; punctuation-wrap: hanging; text-align: center; unicode-bidi: embed; word-break: normal;">
<br /></div>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-align: left; unicode-bidi: embed; word-break: normal;">
<b>Foundations of Business Strategy</b></div>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-align: left; unicode-bidi: embed; word-break: normal;">
<a href="https://www.coursera.org/learn/business-strategy">https://www.coursera.org/learn/business-strategy</a></div>
<br />
My degree is in engineering, so I took relatively few business courses while in college. As we progress as professionals, we understand that technology is rarely the sole driver of any decision. There are marketplace dynamics and an overall strategy of our organization to be considers. This course provides an introduction to different strategies that a firm can choose and why a firm might choose to adopt them.<br />
<br />
One of my takeaways from the course was that even though two firms are in the same market, they may not be competitors because they are targeting different segments of that market. What this course does is give you some of the tools to answer those questions about competitive dynamics and how markets are structured. What is useful here is to understand what segment of the market your firm competes in and the competitive forces that surround that segment, and this course gives you the tools to answer those questions.<br />
<br />
<br />
<h3>
Summary</h3>
<div>
Don't overlook these online courses for your personal learning. These courses are especially useful for helping you to learn about a topic that you have minimal familiarity with, and you can do so on your own time at very low cost.</div>
<div>
<br /></div>
<div>
If you have a particular course that you have taken and found useful, feel free to leave a comment. I'd be interested to hear what other's experiences has been with an MOOCs they have taken.</div>
<div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com1tag:blogger.com,1999:blog-9038566846706115708.post-19355099462146370822015-08-08T15:04:00.000-07:002015-08-08T15:04:00.830-07:00Some Books to Help Develop You Professional SkillsWe often here the term "soft skills" when talking about personal development, but I prefer to call these professional skills, because I think that better describes the skills we are trying to develop. When we talk about skills like time management, communication, the ability to work in a team or work independently, the ability to make good decisions, I think what we are really talking about is someone's ability to work in a professional environment and carry themselves as a professional.<br />
<br />
So we will often hear someone say "This person needs to work on their soft skills". We might even hear someone mention a specific soft skill, like a person needs to work on their communication skills. But what is often missing from the discussion are the specifics of how to work on these skills. Without the how, these phrases are just empty advice.<br />
<br />
I read not only technical material, but also quite a bit of material on what I would consider professional skills. As you would expect, some is good, some is OK and some didn't meet my expectations. What I have done below is summarize the best of the material I have read and spelled out why I think each particular resource is worth the investment of your time. <br />
<br />
As a technical professional, we have to devote a lot of our learning time to technical skills, whether that be development languages and techniques, IT Pro skills or different techniques around analysis and project management. But everyone still needs to set aside some time to work on these professional skills. So with that in mind, lets take a look at the list:<br />
<br />
<br />
<h3>
Personal Kanban</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8DzFvCQVSrv0ZkyWLR_nekn6mww_N-sZ2rLqPSxv7rabLa_RvurabUBMCA7d6gmALGtD2M70rCplkvb4tyl9YmatlM_jvsuFaIu2MNVcT5MFH5bYLdA21T2dxVSlo78e_EEeWmIEtQ5S4/s1600/Personal-Kanban.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8DzFvCQVSrv0ZkyWLR_nekn6mww_N-sZ2rLqPSxv7rabLa_RvurabUBMCA7d6gmALGtD2M70rCplkvb4tyl9YmatlM_jvsuFaIu2MNVcT5MFH5bYLdA21T2dxVSlo78e_EEeWmIEtQ5S4/s320/Personal-Kanban.png" width="240" /></a></div>
<br />
We've all heard "You need to work smarter, not harder". Ever notice that no one ever tells you how to do that? <br />
<br />
Kanban has its roots in the Toyota Production System and has two essential rules. Limit your work in progress and visualize your workflow. By limiting your work in progress, you actually get things done faster because you are focused and not constantly expending cycles to switch tasks. By visualizing your workflow, you you can better understand the work you have in front of you an prioritize the right things to be worked on first. <i>Personal Kanban</i> shows you in detail to apply these principles you your personal workflow The result is that you feel more in control of what you are working on and can make the right choices about what needs to get done when.<br />
<br />
<br />
<br />
<h3>
The 7 Habits of Highly Effective People</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRf_7OBCllot_vVEW79u6A-XPkonwohEPVQfrgYvexrfVkKGpaV9tCVGTbgFFNm4EyAm9Y5YnWsAOcvIhrQyiZwmjmuRsqznUPI2JR28X0DmBpTPs-UCi1HL9BXhfvyoG2YFCyIUm1StRT/s1600/Seven-Habits-Of-Highly-Effective-People.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRf_7OBCllot_vVEW79u6A-XPkonwohEPVQfrgYvexrfVkKGpaV9tCVGTbgFFNm4EyAm9Y5YnWsAOcvIhrQyiZwmjmuRsqznUPI2JR28X0DmBpTPs-UCi1HL9BXhfvyoG2YFCyIUm1StRT/s320/Seven-Habits-Of-Highly-Effective-People.png" width="208" /></a></div>
This is a book that every professional in every field should read, and then probably re-read every few years. The seven habits laid out by Dr. Covey (Be Proactive, Begin with the end in mind, Put first things first, Think win/win, Seek first to understand, then to be understood, Synergize and Sharpen the saw) are habits that none of us will ever truly master, but we must always be improving in order to improve our personal effectiveness. Dr. Covey also describes how the goal is to have a team of interdependent people working together, who combine their skills to be more than the sum of their individual talents. But to do so, everyone must first grow from a stage of dependence to interdependence, and then achieve a level of trust with others that make us interdependent. <br />
<br />
This is not a book filled with cliche's and pie in the sky pictures. There is real advice in this book about how to improve yourself in these areas and as a result improve your professional life. The seven habits are timeless and serve as the underpinning of all of our achievements, which is why it is so important that we are aware of these habits and constantly striving to improve in these areas.<br />
<br />
<br />
<h3>
Decisive</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZD5NUGkiBsUk6V3YpnLIDNbdPlzC9p9CQavs9h66Zwq3SvaJWoI8i2YnXNltKSsH1virhMdFFkaOftFjP-Ni26CoCBtuvJSa1hXobu02106B1iSslIUd6OKpvEqbrT07711k8DOCVqkO6/s1600/Decisive.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZD5NUGkiBsUk6V3YpnLIDNbdPlzC9p9CQavs9h66Zwq3SvaJWoI8i2YnXNltKSsH1virhMdFFkaOftFjP-Ni26CoCBtuvJSa1hXobu02106B1iSslIUd6OKpvEqbrT07711k8DOCVqkO6/s320/Decisive.png" width="214" /></a></div>
We make decisions all of the time. However, most of us don't have a good process for making decisions. As the first chapter of this book discusses, neither do most companies. As a result, the success rate of the decisions we make is much lower than it should be.<br />
<br />
<i>Decisive</i> is all about how to refine your decision making process so that you can make more informed and ultimately better decisions. The author's first explore the reasons why we tend to make bad decisions, like limiting our options or favoring information that supports a position we are pre-disposed to. Then, they discuss techniques that help you overcome these shortcomings in our decision making process. <br />
<br />
After reading <i>Decisive</i>, you will have the tools to be a much more analytical decision maker, who considers a wider variety or options and knows how to really test if your assumptions are valid or not. And you will learn how to set tripwires after you have made a decision to make sure that you have indeed made the right choice or you need to reconsider. <br />
<br />
<h3>
Delivering Happiness</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzveM9dHkyvZk33JNnKK6KSEMCRnQXszEy0pDIEi9NzvwNO8FDF4lpabbWCNDxvKlx8iB4VjKBvIrNETu3sTyIo2dpsgDuDu55S1RlLch_D-uaD8ykGNZb0T69OPno6xtw8NoHft0ktbq0/s1600/Delivering-Happiness.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzveM9dHkyvZk33JNnKK6KSEMCRnQXszEy0pDIEi9NzvwNO8FDF4lpabbWCNDxvKlx8iB4VjKBvIrNETu3sTyIo2dpsgDuDu55S1RlLch_D-uaD8ykGNZb0T69OPno6xtw8NoHft0ktbq0/s320/Delivering-Happiness.jpg" width="213" /></a></div>
<br />
As I continue in my career, I have realized how important customer focus is. Everyone has a customer, and if that customer isn't happy, they will find someone else that can better meet their needs. This is even true for internal customers. <br />
<br />
Today though, customer satisfaction is about much more than just "checking all the boxes" or "we delivered what the customer asked for". Tony Hsieh (CEO of Zappos) introduces us to the concept that we have to delight our customers. And this is so true. When we delight our customers, they become our biggest supporters.<br />
<br />
There is much more to this book, and a lot of it is about new thinking in terms of leadership. Zappos doesn't track the time its customer service associates spends on any one call because it trusts them to do the right thing. And for associates who join and decide Zappos isn't the place for them, they will actually give them a lump sum to walk away, no hard feelings. These are refreshing new ideas about how to lead a company, and will challenge you to think about leadership differently.<br />
<br />
<br />
<h3>
Drive</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgam0Y1V37K3ICG18wVcz8JwWXevse5MjtOaAUPyMzlX1cAfA6744H_8tqyDQ8ITOy_ZlmjZKOPXyC-z43t1kJzHmFBeDRaNvJQa6QC2qAdq7XsC6hQ5gZNQ7HiEWw2Mk-3ceWTZKg0EAKM/s1600/Drive.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgam0Y1V37K3ICG18wVcz8JwWXevse5MjtOaAUPyMzlX1cAfA6744H_8tqyDQ8ITOy_ZlmjZKOPXyC-z43t1kJzHmFBeDRaNvJQa6QC2qAdq7XsC6hQ5gZNQ7HiEWw2Mk-3ceWTZKg0EAKM/s320/Drive.png" width="212" /></a></div>
I strongly recommend that anyone in a leadership position, whether formal or informal read <i>Drive</i>.<br />
<br />
Mr. Pink describes how we have managed people for the last hundred years or so in a top down, hierarchical fashion and why this mode of management is outdated today, especially for knowledge workers. He describes the differences between extrinsic motivation (e.g. financial incentives, carrot and stick approaches) and intrinsic motivation (people who are self motivated to do a good job based on the purpose), and he explains why intrinsic motivation is always superior to extrinsic motivation. <br />
<br />
Pink describes three factors that lead to true motivation: autonomy, purpose and mastery. It is these three factors you need to make sure everyone has in order to insure they are truly motivated to do their best work. Making sure these three factors are present is the key to making sure that people are intrinsically motivated, and ultimately to performing their best. This isn't about motivating people, but about creating the right environment where people are intrinsically motivated to do their best work.<br />
<br />
<br />
<br />
<h3>
Turn the Ship Around</h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyDM-Fer4bTteJMd0dlNN0JHldyQNKTWqQY4h6ZOzOH_A4AkKrnXhGlXHrJgRja374ceFniFUNrsiC6oYTIUr9iF_ZZmSG3er3bFuoxHK5oSTCz-e5GK9lyGv74xqQ0NB-iq1d7paz0Fvp/s1600/TurnTheShipAround.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyDM-Fer4bTteJMd0dlNN0JHldyQNKTWqQY4h6ZOzOH_A4AkKrnXhGlXHrJgRja374ceFniFUNrsiC6oYTIUr9iF_ZZmSG3er3bFuoxHK5oSTCz-e5GK9lyGv74xqQ0NB-iq1d7paz0Fvp/s320/TurnTheShipAround.jpg" width="212" /></a></div>
There are a lot of books that have been written about leadership and many of them are filled with empty cliches and advice. This book is the real deal though.<br />
<br />
Written by a former US Navy submarine Captain David Marquet, this book talks about creating leadership at all levels and about how you can effectively move away from the command and control structures common in most organizations. In the beginning of the book, he talks about so often someone starts a new job with excitement and the feeling that they can make a difference, only to be completely burned out and disillusioned 6 months later when it is clear that they have no power and decisions flow from the top.<br />
<br />
This book describes the practices and challenges envountered by Captain Marquet as he took the USS Sante Fe from the worst performing ship in the fleet to the best. What I liked most about the book is that Captain Marquet doesn't just tell us his success stories, he also tells us about the failures along the way, how he was tempted to go back to the old command model and how he ultimately overcame them. <br />
<br />
Whether you are in a formal or informal leadership position, this book is well worth your time. We so much need everyone to feel as though they can take the initiative, to contribute their ideas and that their voice matters, and this book shows you how giving away control to those around you can result in a better organization that truly does value everyone's contributions.<br />
<br />
<br />
<h3>
The Essential Drucker</h3>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfEoF8ZsA7bU3Nwte-m4Rv0BOIet6VLn8MSVVWioMfTlUTtSrufHTYJmvUQpHSL1X4VHo5Gm2wt92eHHCDtm-XnJYhIxH53qnG0CnT48WAGNpUwxzWwerLvUeTwov4wo6JQmt7Z6u8BJsc/s1600/TheEssentialDrucker.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfEoF8ZsA7bU3Nwte-m4Rv0BOIet6VLn8MSVVWioMfTlUTtSrufHTYJmvUQpHSL1X4VHo5Gm2wt92eHHCDtm-XnJYhIxH53qnG0CnT48WAGNpUwxzWwerLvUeTwov4wo6JQmt7Z6u8BJsc/s320/TheEssentialDrucker.jpg" width="202" /></a></div>
Peter Drucker is known as the father of modern management, and his advice today is as relevant as it was 50 years ago. You might think that because much of these writings come from decades ago, they would be out of date in today's world. Not so. Mr. Drucker was a man who was truly ahead of his time.<br />
<br />
This work covers every aspect of leadership in an organization, fro having a clear objective to strategy to hiring to problem solving. There are not many areas it does not touch on.<br />
<br />
This was one of the first business books that I read and I still consider it one of the best. Probably what I like most is how plain spoken the advice is. Again, as with all of these books, there aren't any unicorns. There is simply real hands on advice to real, every day problems that are encountered by people at every level of the organization.<br />
<br />
<br />
<br />
<br />
<br />
As time goes on, I'll add more books to this list, so you may want to check back occasionally. Most of these books you can get an eBook copy from Amazon for around $10-$15. I would recommend this approach because many of these books you may want to refer back to or re-read at different times to refresh yourself on the principles they share. If cost is an issue though, don't hesitate to check your local public library for a copy.<br />
<br />
I hope you enjoy the list, and if you have any reading recommendations for me, put them in comments below and I'll try to get to them.<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com1tag:blogger.com,1999:blog-9038566846706115708.post-17807296847174671492015-08-05T20:47:00.001-07:002015-08-05T20:47:46.815-07:00Why the Standard Corporate Development Plan Doesn't WorkAbout a week from now, I will be speaking at <a href="https://www.thatconference.com/" target="_blank">That Conference</a> in Wisconsin Dells on the topic of personal learning and becoming a lifelong learner. I have an interest in this topic for a couple of reasons. First, as a <a href="http://www.pluralsight.com/author/david-berry" target="_blank">Pluralsight author</a>, I have a natural interest in helping others learn and helping them learn more effectively. <br />
<br />
Second, I have done just about every job in an IT department, and this includes time where I have been an IT manager, that is, I had staff that reported to me. One of the things that I learned is that while we encourage people to actively learn new topics and keep their skills up to date, many individuals are at a loss how to put together a personal development plan and then execute on that plan to acquire the skills they need. For this reason, I decided to put together a talk that I will deliver at That Conference and hopefully other code camps in the area to help others learn how they can put together a development plan for themselves. I'll also be posting much of the same information as blog posts so that attendees or any other interested party can view additional information on the topic.<br />
<br />
<h3>
The Standard Corporate Development Plan</h3>
If you work for any mid-size to large company, you probably go through an annual goal setting and review process. Often as part of this process or as a compliment process, you will sit down with your manager and define a personal development plan for the year, and that plan will be a document that looks something like this.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTIr0fIXgFfMs-rJnVbcuQiqaf-X9EzC8GAtLDrfKy4LAvNIo9v0LwW79j6ocfOa1fnXgpsOhkx40L8a1dkcAJtxqLNkjrmgL2NcUykgkPUCUWpEOhwHy2lMZKR3xkIJglMYPqZq2pbywB/s1600/StandardPersonalDevelopmentPlan.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="328" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTIr0fIXgFfMs-rJnVbcuQiqaf-X9EzC8GAtLDrfKy4LAvNIo9v0LwW79j6ocfOa1fnXgpsOhkx40L8a1dkcAJtxqLNkjrmgL2NcUykgkPUCUWpEOhwHy2lMZKR3xkIJglMYPqZq2pbywB/s640/StandardPersonalDevelopmentPlan.png" width="640" /></a></div>
<br />
<br />
I've went through this process both as an employee and as a manager working with my direct reports to complete this form. You go through at the beginning of the year listing out all of the things that you want to improve upon in the coming year. Acronyms like setting S.M.A.R.T. objectives get thrown around (Specific, Measurable, Attainable, Relevant and Time bound) and all of this is done with the very best of intentions.<br />
<br />
The problem is that from what I have seen, this rarely works. Too often we put a lot of effort in creating this document in January only to see it forgotten by March. And that is a real shame, because all of us, no matter who we are, have skills we need to improve on. Setting up learning goals is a good idea, and so is writing them down. So where did we go wrong here, and more importantly, what needs to change about this process to make sure that we are getting the personal development we need.<br />
<br />
<h3>
Where the Above Process Goes Wrong</h3>
From my experience, there are two primary places this process breaks down. They are as follows:<br />
<br />
<b>The Time Frame Is Too Long</b><br />
Typically, the time frame for such a document is one year. This is too long. The problem is that when we have a very long time to do something, most of us tend to procrastinate to at least some degree. After all, we have a year. I can get going on that next month. Since we have a year to complete all of these items, they don't seem urgent, certainly not compared to the other responsibilities we have at work. So we end up working on our more "urgent" day to day priorities rather than our "important" personal development objectives.<br />
<br />
At some point, we realize that we don't have all year any more but only a month or two left to meet these objectives, to which there is usually one of three outcomes. <br />
<br />
The first outcome is that we try to cram all of our learning into a short time period. Yes, I know I should have been watching Pluralsight videos all year, but instead I'll binge watch all Friday and Saturday and I'll "complete" the courses I was supposed to. The problem is this isn't a very effective way to learn. Yes, you may have completed the videos, but watching 20 hours of videos over two days doesn't give you time to really digest and retain the content you just watched. Just like most of us learned in college, we needed to keep up with our assignments in a course and study throughout the semester, not just cram at finals time. So this is really not a good outcome.<br />
<br />
The second possible outcome is that rather than spending a significant amount of time on each objective and really getting to depth with the topic, we instead have to choose a shallower approach to learning the topic. For example, you may have intended to read a book on time management skills for busy professionals, but since you don't have time for that, now you decide to just read a couple of articles online. So instead reading a book over 5 or 6 hours where you have time to digest the information and see different perspectives and techniques that you could use, you are spending 30 minutes to read a couple of quick articles so you can basically check something off of your list. The problem here is that we aren't really getting to depth with our topic and our personal development need at this point, so likely, this topic will remain something we have to work on.<br />
<br />
The third possible outcome is that we simple just don't complete some or even all of our goals. And of course if this is the outcome, then we have to ask why these were goals in the first place.<br />
<br />
<br />
<b>Too Many Objectives</b><br />
The second issue with the standard template and process most companies use is that it compels us to list too many different objectives. The template I created above has six lines. Upon being handed that template, most of use would feel compelled to put down 5 or 6 different development objectives for the year. After all, if we only put down two, that would really look awkward.<br />
<br />
In Jim Collins book "Good to Great" he remarks "If you have more than three priorities, then you don't have any". This certainly applies to creating a personal development plan. Having five, six or even more objectives almost assures that our efforts will be unfocused. We'll end just scratching the surface of many of the objectives we have outlined for ourselves rather than really getting the the depth we need. And without getting to that depth, it is difficult to see how we can really improve.<br />
<br />
<h3>
A Better Way</h3>
Fortunately, there is a solution here, and it is very easy. We need to do the following:<br />
<ul>
<li>Define our learning goals on a shorter time frame, preferably 2 months or less</li>
<li>Limit ourselves to a maximum of two objectives at any one time</li>
</ul>
<div>
What we are trying to do here is focus. Rather than take on a large number of goals over a long time frame, we want to focus on just a couple of goals over a short period of time. We want to make sure that we are focus on these goals so we become proficient in what we are trying to learn. We don't want to dabble in 10 things. We want to master a couple of topic areas, and then at the end of this 4-8 week period, evaluate what our next set of goals are and repeat this process again.<br />
<br />
If you have been involved in any sort of Agile project management, this thinking is really no different. In an Agile project environment, we define a couple of critical items that we need to get done in the next iteration, and we focus all of our energy on completing those items. What we don't do is get distracted by other items not in the iteration, not because those items aren't important, but because we know the best way to make progress is to focus on just a couple of items at a time and get those knocked out. It is the same with the development plan. We are defining a shorter time period and keeping our goals very focused for that time period.<br />
<br />
What I will do is take the document above and modify it slightly so that there are only two rows for goals. And that is on purpose. If there are only two rows, then you can only put two goals. My document looks like this:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwfS7cpzO7FmzXi8nG2mrQzSH9YmFyX9o_rMdyr8ZW4MeWvSIRXxq1Oul-lgLoFjakU4MAqSEte1WuwTbJnBN3677q9iUsTwRvgQD8ZbgP5FZLIfxHB5hKqgGb8CCg0-OUSE66j7Z4KRit/s1600/FocusedPersonalDevelopmentPlan.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="278" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwfS7cpzO7FmzXi8nG2mrQzSH9YmFyX9o_rMdyr8ZW4MeWvSIRXxq1Oul-lgLoFjakU4MAqSEte1WuwTbJnBN3677q9iUsTwRvgQD8ZbgP5FZLIfxHB5hKqgGb8CCg0-OUSE66j7Z4KRit/s640/FocusedPersonalDevelopmentPlan.png" width="640" /></a></div>
<br />
<br />
We have a lot of the same columns as before, but I have added one additional column: Total Estimated Time. This is an estimate, it doesn't have to be exact. If you are taking Pluralsight courses, add up the time for the courses and then add in another 50-100% for time to do some exercises with the technology and get some hands on experience. If you are reading a book, figure out how many pages you can read an hour and estimate the time to read the book.<br />
<br />
The reason for this column is that if we come up with a skill that lets say we estimate will take 60 total hours to learn, then we want to break this into smaller pieces and do several iterations over these pieces. Again, this is just like agile project management where we want to break these really big tasks down into smaller more consumable ones. So in this case I would advise someone to identify the first 15-20 hours of training and some goals around those, and do that in the first iteration of the plan (the first 6-8 weeks, depending how many hours they are spending on development per week). Then, in the second one of these documents, you handle the second part of the plan an so on.<br />
<br />
<br />
<h3>
Breaking Things Down Even Further</h3>
For myself, the above breakdown is usually sufficient. Sometimes though, it is helpful to break your plan down even further like so.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRj1W6mUzvKYxZ4ERNTkfGhGvsUevRcINNgHRWaZ-DZqp0ecbqm5HUY8KXhldoxk1tQBOp-_mcyCGoriwTpzFSay_rbkGcynscgJ96CWR-q0RVRC3jehqywU6-4FTZhSfoQAdv-GyUXrxc/s1600/WeeklyBreakdownOfDevelopmentItems.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="288" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRj1W6mUzvKYxZ4ERNTkfGhGvsUevRcINNgHRWaZ-DZqp0ecbqm5HUY8KXhldoxk1tQBOp-_mcyCGoriwTpzFSay_rbkGcynscgJ96CWR-q0RVRC3jehqywU6-4FTZhSfoQAdv-GyUXrxc/s640/WeeklyBreakdownOfDevelopmentItems.png" width="640" /></a></div>
All I have done here is create a simple Word document and then take the individual tasks I am going to do to learn the topic and break them down by what I am going to do on a week to week basis. In this case, There is a Pluralsight course I am going to watch roughly a module a week from and then I am also allocating some time where I'll apply what I learned in a simple demonstration website I am going to create. <br />
<br />
The main goal of breaking the tasks down by week is to make sure that I am not underloading or overloading a week. And it continues to give detail and structure to what I am trying to accomplish. Now I know exactly what I do to stay on track. This just helps break things down into some smaller hurdles I have each week, so it is more like "learn this concept". OK, now learn this concept. So if this further breakdown helps you, it might be something you want to do.<br />
<br />
<br />
<h3>
But Wait, My Company Makes Me Do an Annual Development Plan</h3>
At many companies, you are going to be required to do an annual development plan and you will be required to use the standard template for your company.<br />
<br />
In this case, go ahead and complete the standard company template. But then, break these goals down into smaller pieces such that you can focus on 1-2 goals at a time in 4-8 week periods like was talked about above. Once you start working on a couple of goals, keep your focus there and don't worry about all the other goals that are on the standard development plan document. All you are really doing here is breaking those big goals down into smaller chunks.<br />
<br />
<h3>
Summary</h3>
The main point here is focus. Focus one on or two items at a time, and focus the amount of time you spend on these items so there is a short, well defined period where you work on these topics and master them. The real goal of having a development plan is so that you acquire new skills and improve in areas that you need to. We are trying to achieve depth and mastery. What I have found is focused effort is a much better way to achieve this mastery than a large number of goals that end up not really being time bound because of the period they are planned over.<br />
<br />
<br />
<br />
<br /></div>
<br />
<br />Anonymoushttp://www.blogger.com/profile/00869580597099256063noreply@blogger.com2