Paging Example

This is a great example of doing paging in SQL versus on the front end.

-- find activity logs
insert into @tblActivityLogs
select al.ActivityLogId
	, al.TargetPartyId
	, pWhoDidIt.PartyId
from dbo.ActivityLog al
left join dbo.Party pWhoDidIt on al.PartyIdentifier = pWhoDidIt.Identifier
where al.TargetPartyID = @PartyId
	and al.Created >= @DateTimeRangeFrom
	and al.Created <= @DateTimeRangeTo
order by al.Created
	offset ((@Page - 1) * @PageSize) rows fetch next @PageSize rows only

SQL Saturday #682

Full Session Schedule

https://www.youtube.com/user/SQLPASSTV

Session #1: Reading Execution Plans Successfully

By: Arthur Daniels

Twitter: https://twitter.com/arthurdanSQL?lang=en

http://www.dba-art.com/

If you’ve seen an execution plan but didn’t know how to read it, this session is for you.

The goal of this session to learn how SQL Server is interpreting your query into an execution plan. We’ll discuss execution plan internals, how SQL Server estimates the cost of your query, and what a graphical execution plan is displaying through its operators.

Learning to read an execution plan is a great way to begin troubleshooting performance. At the end, we will take a look at how SQL Server 2016 provides more tools for exploring execution plans.

Notes:

Session #2: Getting Started with Extended Events 

By: Andy Galbraith

Twitter: https://twitter.com/DBA_ANDY?lang=en

http://nebraskasql.blogspot.com/

Few subjects in Microsoft SQL Server inspire the same amount of Fear, Uncertainty, and Doubt (FUD) as Extended Events. Many DBA’s continue to use Profiler and SQL Trace even though they have been deprecated for years. Why is this?

Extended Events started out in SQL Server 2008 with no user interface and only a few voices in the community documenting the features as they found them. Since then it has blossomed into a full feature of SQL Server and an amazingly low-impact replacement for Profiler and Trace.

Come learn how to get started – the basics of sessions, events, actions, targets, packages, and more. We will look at some base scenarios where Extended Events can be very useful as well as considering a few gotchas along the way. You may never go back to Profiler again!

Notes:

Session #2: Query Optimization Statistics : Driving Force Behind Performance

By: Vern Rabe

Twitter: https://twitter.com/VernRabe?lang=en

http://rabedata.com/

When the SQL Server optimizer evaluates a query to determine how best to execute it, the statistics are quite possibly the most important tool at its disposal. But SQL Server statistics objects aren’t perfect because they only contain estimated summary information. In this session, we’ll start with an overview of what the statistics objects are, how the optimizer uses them, and some general guidelines for their maintenance. Then we’ll look at some of the issues, how to find them, and how to solve them, that can arise due to their imperfection: ascending keys (the most prevalent statistics based performance killer?), correlated predicates, skewed distribution, or downright bad summary information. There’ll be many examples, and even a stored procedure to help you find ascending keys. By applying the techniques we’ll discuss, you WILL see improved query performance.

    

Notes:

Session #2: Getting the most out of SQL Server Data Tools 

By: Eric Strom

Twitter:

SSDT has been around for a while, but a lot of people don’t use the tool to its full capabilities.  In this session, we will cover writing unit tests, good deployment script writing practices, T4 and command variables.  This session requires a good understanding of T-SQL and Visual Studios.

Session #3: Lightning Talks (Round 1)

Vagrant Auto Build System

By: Mitchell Hamann

This session talked about the Vagrant Software that can be used to build SQL development VMs and deploy to developers machines.

The bonus to doing something  like this is that the SQL tools may not need to be installed on each developers machine.

SQL Server Monitoring on No Budget

By: Daniel Crowson

A review of different free software that can be used to monitor your SQL server including sp_whoisactive, DMVs, Perfmon Counters.

In the Q&A session the following list of lower budget options were also listed:

  • SQL Monitor
  • FogLight
  • Brent Ozar (Highly recommended)
  • Solar Winds
  • Sentry One (Highly recommended)
  • SP Blitz
  • Stack Overflow Observor

Testing Backups in One step

By: Constantine (CK) Kokkinos

Twitter: https://twitter.com/mobileck

https://constantinekokkinos.com/

Session about using the https://dbatools.io/ software to test the current state of databases quickly and cleanly.

Session #3:  Index 360 – Looking at Indexes from Multiple Perspectives

By: John Eisbrener

Twitter: https://twitter.com/johnedba?lang=en

http://www.dbatlas.com

If you have used a database, chances are almost certain you’ve utilized indexes as well.  In this presentation I will discuss both Rowstore and Columnstore Indexes and why they are important to anyone that interacts with a database.  This session will cover what they are, how they are utilized, how best to take advantage of them, and even when they can be problematic. It is my intention to help anyone become more comfortable with indexes and understand what they can do for you and your role, be it a DBA, Developer, or BI Professional.

Session #4: Difficult Queries

By: Rick Bielawski

https://rickbielawski.wordpress.com/

Notes:

  • Cross Joins, Cursors
  • Snooze

Session #5: Intro to Machine Learning

By: Jared Zagelbaum

Notes:

 

  • Flew wayyyyy over my head.

Session #5: Free SQL Server Tools

By: Cecil Spivey

Everybody loves a free lunch. Come to this session to learn about all the SQL Server freebees.

Twin Cities Code Camp #21

Over the weekend our team got together and all attended the Twin Cities Code Camp together.  It was a ton of fun and I have lots of new tech stuff to research.  Here is a summary of my notes.

Unboxing ASP.NET Core

by Kevin Leung
  @KSLHacks 

http://kevinleung.com/

“ASP.NET Core is a new open-source Web framework optimized for building cross-platform web apps, IoT apps and mobile backends. ASP.NET Core comes with great new features ready to use out of the box with minimal setup required! In this talk we’ll look at .NET Core, architecture, package management and how to begin weighing the options between choosing .NET Framework and .NET Core. I will also share my experiences and insights while working alongside the .NET Core team at Microsoft to port over an existing project from .NET Framework to .NET Core; as well as the challenges we faced. Core brings Microsoft into the exciting world of open-source, cross-platform and package modularity/portability – Let’s see what we can build!”

Notes:

 

Building Reusable UI Components in ASP.NET Core MVC

by Scott Addie
  @Scott_Addie 

https://scottaddie.com/

“ASP.NET proper MVC developers have long relied upon partial views and HTML helpers to construct reusable UI components. ASP.NET Core MVC expands the arsenal of options for creating such UI components by introducing view components and tag helpers. Do these new offerings render partial views and HTML helpers obsolete? Absolutely not! Using the right tool for the job is important, which means understanding the differences between these options is paramount. In this session, you’ll gain an understanding of when it’s most appropriate to use each of them in the real world. You’ll also see how to create basic view components and tag helpers.”

Notes

 

Building Shiny Web Apps with TypeScript and Angular 2

by Dustin Ewers
  @dustinjewers 

https://dustinewers.com/

“From humble beginnings, JavaScript has gone from a hastily thrown together language for web pages to a hastily thrown together language that runs everywhere. If you can do it with code, you can probably do it in JavaScript. It’s the Swiss Army Knife of programming languages.  Unfortunately, JavaScript wasn’t designed for the large scale apps we use it in today. We’ve had to rely on design patterns, willpower, and luck to mitigate JavaScript’s failings. However, there is a better way. TypeScript adds features to JavaScript that make it usable on large projects. Additionally, the bar for web applications gets higher everyday. jQuery was cool ten years ago, but it doesn’t cut it for modern web apps. We need something with a little more horsepower. Angular 2 makes it easy to build clean, modular web apps. In this talk, we’ll explore TypeScript and Angular 2 and how they can be used together to build large-scale web applications. We will learn how to get started and get up to speed quickly.”

Notes

Unit Testing for the Scared, Skeptical and Ashamed

by J Wynia
  @jwynia 

http://www.wynia.org/

“Does the mention of the phrase “unit testing” make your “fight or flight” response kick in? Do you feel like the right time to get started unit testing was a while back and you missed the boat? Do you just have a hard time believing that writing unit tests won’t cut your development speed in half? Come learn about getting started unit testing in a judgement-free environment. Examples in C#, but applicable to other languages as well.”

Notes

 

Cool SQL Server Features Everyone Should Know About

by David Berry
  @DavidCBerry13 

http://buildingbettersoftware.blogspot.com/

“We all use SQL Server every day in our jobs, so it pays to know what SQL Server can do for us that will make our jobs easier. This talk will introduce you to some key features of SQL Server that you might not know about but will definitely want to use once you learn about them. First, we’ll discuss temporal tables, which provide a convenient way to track all of the changes made to data in a table. Second, we’ll talk about the JSON support built into SQL Server 2016 and what capabilities it provides us. Third, we’ll cover some advanced SQL constructs like the MERGE statement and Common Table Expressions that can make the SQL you write simpler. And finally, we’ll wrap up by talking about the windowing functions in SQL Server, which provide powerful analytic capabilities to our SQL Statements. After this talk, you will better appreciate some of the rich functionality built into SQL Server and understand how to use these capabilities to make your job easier.”

Notes

#TinyMCE: IE11 Insert Content

In one of our main projects we are using a custom TinyMCE plugin to allow the users to attach files to an email that is sent from our system.   The plugin pops up a modal and asks the user for information then inserts the entered data into the editor at the cursor.   This works perfectly well in most cases, except for IE11 where it inserts the text at the beginning of the editor.

We found a Stack Overflow post (TinyMCE – Inserting content at cursor position in IE doesn’t work) that gave the basics on how to fix it.   Here is what I ended up doing.

Create the plugin with the bookmark code:


tinymce.PluginManager.add('customattachment', function (editor, url) {
  var self = this;

  // ie11 does not retain the current cursor position when the attachment popup loads.  We need to retain the current position
  // and then restore it in the "InlineAttachment" function using the self.restoreBookmark() defined in this file.
  var bookmark;

  editor.addButton('insertcustomattachment', {
    title: "Insert Attachment",
    type: 'button',
    icon: 'attachment-ico',
    onclick: function (e) {
      //save bookmark
      bookmark = tinyMCE.activeEditor.selection.getBookmark();

      $.fancybox({
        content: "<iframe class=\"naked customattachment\" src=\"/attachment/upload/InlineAttachment/\" scrolling=\"no\" style=\"border:none; width:500px; min-height:250px; margin:15px;\"></iframe>",
        scrolling: 'no',
        padding: 0,
        margin: 0,
        onClosed: function () {
        }
      });

      // prevent bootstrap from hijacking the click (on Website | Home full-width pages)
      $('.fancybox-inner').find('iframe').on('focusin', function (evt) {
        var $ipt = $($(evt.target)[0].contentWindow.document).find('.custom-attachment-title').closest('form');
        if ($ipt.length) {
          evt.stopImmediatePropagation();
        }
      });

    },
  });
  self.restoreBookmark = function () {
    //restore bookmark
    return tinyMCE.activeEditor.selection.moveToBookmark(bookmark);
  }
});

Call the code to insert the attachment after the modal is closed:


  // Used in _komodal_tinymce for customattachment
  function InsertInlineAttachment(item) {
    tinyMCE.activeEditor.plugins["customattachment"].restoreBookmark();
    tinyMCE.activeEditor.execCommand('mceInsertContent', false, item.replace(/"/g, "'"));
    $.fancybox.close();
  }

#VisualStudio Extension: Custom Title Bar

Sometimes there is a need to work in two different versions of the same project, it can get complicated to keep track of which window you are in.

I just found a new extension to add my Visual Studio that changes the title bar to show the root path of the solution you are working in.

Once installed it looks like this

The key settings to set it up for our SVN file structure is this

jQuery tablesorter with Font Awesome arrows

I needed to add font awesome arrows to a jQuery.tablesorter table.  The css was a bit tricky and I don’t want to lose it.

    table.tablesorter thead tr th.headerSortUp:after,
    table.tablesorter thead tr th.headerSortDown:after,
    table.tablesorter thead tr th.header:after {
      font-family: FontAwesome;
    }
    table.tablesorter thead tr th.header:after {
      content: "\f0dc";
    }
    table.tablesorter thead tr th.headerSortUp:after {
      content: "\f0de";
    }
    table.tablesorter thead tr th.headerSortDown:after {
      content: "\f0dd";
    }
<table id="table" class="table tablesorter">
    <thead>
        <tr>
            <th>Header 1</th>
            <th>Header 2</th>
            <th>Header 3</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Data 1</td>
        </tr>
        <tr>
            <td>Data 2</td>
        </tr>
        <tr>
            <td>Data 3</td>
        </tr>
    </tbody>
</table>

Visual Studio 2012

I love working on teams that use MVC.  They always seem to be the ones willing to grab the latest technology and start using it.  I have worked in the past for places that say “We use the latest and greatest…” but when I have been there a while I find out that they really aren’t willing to upgrade anything.  Today I got to install Visual Studio 2012 and start using it.  Here are a few links to sites that had useful information:

Scott Hanselman is awesome.  Here is his article on Visual Studio.

Microsoft has a product guide for 2012.