multiple columns in a cursor and CTEs when not abusing MIN

I struggled with some SQL tonight and learned:

1. My notes here poorly describe how to cast more than one column into a cursor. Do so like this:
   DECLARE curs CURSOR FOR SELECT TOP 10 Ying, Yang From dbo.[Baz]
   OPEN curs
   FETCH NEXT FROM curs into @unalterednumber, @somethingelse
   ...that is by separating the variables for each column with commas everywhere I otherwise have merely @unalterednumber in my blog posting.
2. something painful to get used to in GROUP BY/HAVING statements is that in scenarios where you know a column just repeats its value universally you cannot... oh, wait... I realize what I am about to say is not true as I type it. If a value is just going to repeat universally, you can show it as a column which means you will have to include it in the GROUP BY. This act will not screw up the report as what the reporting was before adding the extra condition (which adds nothing as all of the values it concerns are identical for the record sets it would have come up with anyways) varies not at all in terms of numbers of rows or aggregation
3. what I was going to say, inappropriately, was that if you need to show the consistently repeating value out of such a column, you will need to nest the GROUP BY/HAVING thing inside the WITH cause of a CTE and then join on the appropriate table to end up selecting this extra column
4. that is if the extra column is of, say, a varchar type... if it is of a numeric type you may get the repeating column as a column in the GROUP BY/HAVING statement without specifying it in the GROUP BY by using the MIN aggregate function ...but, again, you don't need to do this

two-way binding

In a talk by Dan Hollenbeck last night at The JavaScript Austin Meetup Group on "Three Backbone.Marionette/Node.js Apps: Useful Patterns and Lessons Learned," Mr. Hollenbeck defined two-way binding as the choice between Data Events and UI Events. In a Backbone app a pattern using UI Events would look like so:

       DOM                         View                        Model
-----------------            ----------------            -----------------
|               |            |              |            |               |
|               |  UI Event  |              |  Set/Save  |               |
|     click--------------------->function------------------->attribute   |
|               |            |     |        |            |               |
|               |            |     |        |            |               |
|               |            |     V        |            |               |
|          <---------------------function   |            |               |
|               |   Update   |              |            |               |
|               |            |              |            |               |
-----------------            ----------------            -----------------

 
 

This comes with some assumptions. There is a fire and forget approach to the data layer and a lack of an ability to support asynchronous feedback. If one wants to verify a transaction through a roundtrip across a web socket and back (while using Node.js or IIS8) then one will need to be able to support asynchronous replies. I can see how this would come into play in the name of ensuring that one was not adding a duplicate record and forking reactive behavior based upon if one was or was not. Beyond the data layer challenge one could just want to trigger an event to listen to for feedback to the DOM to address ambiguity as to whether or not the ultimate DOM element to manipulate already exists or is out of scope. In a scenario in which an event to subscribe to is needed after an act has occurred there is a Data Events pattern to use:

       DOM                         View                        Model
-----------------            ----------------            -----------------
|               |            |              |            |               |
|               |  UI Event  |              |  Set/Save  |               |
|     click--------------------->function------------------->attribute   |
|               |            |              |            |       |       |
|               |            |              |            |       |       |
|               |            |              |            |       V       |
|          <---------------------function<---------------------event     |
|               |   Update   |              | Data Event |               |
|               |            |              |            |               |
-----------------            ----------------            -----------------

C# 4.0 uses the regular expressions of Perl 5.

The last chapter of C# 4.0 in a Nutshell is on regular expressions. The logic used is the same as that found in Perl 5.

optimistic locking mechanics?

Piggybacking onto this, this suggests that in an optimistic scenario that one will alert the user who changed a record (between the read and update of another user at a CRUD web page form) of the alteration instead of being silent about it meaning that there will still be need to store who changed the record last at a database column (in my interpretation). To digress: My understanding of how one does pessimistic concurrency mechanics entails keeping a database column for who (user id) last "touched" the row in the database in question (even if only to read), a DateTime type column for when the "touching" started, and then possibly a boolean/bit type field for if a lock is on. If the last of these fields is skipped then I wonder if the DateTime field just becomes nullable to manage this sort of on/off state. Clearly, if the date is too old then any locking needs to be assumed to be old news, so maybe such checking negates the need for the DateTime to be nullable (or to have the boolean/bit column). You have to check anyways, right? I suppose the nullable state or the boolean/bit column could make checking faster.

Just run a new build in Jenkins to deal with dirtiness that occurs when a "rename" straddles two check-ins.

This WILL fix the build. My situation:

1. I realized a file could have a better name.
2. I made a new file and destroyed the old one.
3. I checked in the delete locally to Mercurial, but not the addition.
4. I then remembered the addition, and checked it in locally too.
5. I then committed to my branch the two separate changesets.
6. The build broke in Jenkins! Jenkins could not handle the “missing” file!
7. I made some further unrelated changes and committed, first locally and then to my branch again.
8. The build succeeded in Jenkins!

I saw the .NET Ninja's Toolbelt talk by Latish Sehgal fifteen days ago at Austin Code Camp.

The talk more or less went over what is listed at http://dotnetsurfers.com/tools/ and hence you may be wondering why you would even want to read a bullet list breakdown of the talk by someone else when you can just visit the bullet list breakdown of Mr. Sehgal directly at http://dotnetsurfers.com/tools/ and indeed I do not know that I have a compelling reason for why you should continue reading. I wrote up blog postings on the other six talks I saw fifteen days ago however, so I'm going to be consistent and give a write up of the Latish Sehgal talk too. Here is what is in a Ninja's toolbelt:

1. JetBrains ReSharper ...Enough said. You're already an addict.
2. Firebug ...Ditto. You already use this Firefox plugin and know why it rocks. The Google Chrome Developer tools are damn impressive too and, for Firefox, YSlow is a plugin for a plugin (the Firebug plugin) which gives suggestions on things which could be better.
3. VSVim ...This a Visual Studio Extension that will allow you to do Vimesque keyboard commands within Visual Studio. Get it, and other extensions, by going to "Extensions and Updates..." within the "Tools" menu within Visual Studio 2012. An "Extensions and Updates" dialog box will pop up. Select "Online" at the left and then search for "VSVim."
4. NCrunch ...The little animation at http://www.ncrunch.net/ should put you in awe. Black circles appear at the left of lines of code not under test. Green circles appear at lines of code that are under test where the test passes. The green circles turn red as soon as code changes in a manner that breaks a test.
5. TestDriven.NET ...Right-click anywhere in Visual Studio to have an option to run all tests. Get terse success and failure messages in the status bar along the base of Visual Studio instead of some popup panel which takes up gobs of screen real estate.
6. JSLint ...Living at http://www.jslint.com/ this tool allows you to copy and paste in JavaScript code to which it will respond with warnings and suggestions.
7. Sando ...Get Googlesque search capabilities against your own code base.
8. KillCassini ...This tool was authored by Latish himself. It kills instances of Cassini and IIS Express.
9. VSColorOutput ...Changes color rules for easier reading.
10. BugAid ...Have you ever set a breakpoint somewhere in a loop for debugging and then had to press F5 over and over again in debugging to reach the iteration in the loop that you really cared about? This tool addresses that pain point. It allows you to select an item out of a collection that has your eye and it then allows you to hit a breakpoint when (and only when) that particular player is up to bat. I can see how this will really save one time if the looping is a bottleneck in front of drilling into debugging something deeper.
11. glimpse ...This is a NuGet package which will allow you to hit glimpse.axd at http://www.example.com/glimpse.axd and in turn put an eye icon at the lower right of your web site which, when clicked, opens up an informative panel full of performance metrics. You will obviously not want to roll out the glimpse stuff to production hosting.
12. WPF Inspector ...This, um, helps inspect elements, in WPF.
13. Hawkeye ...WPF Inspector for WinForms.
14. Code Perspective ...This tool found at https://codeperspective.com/ does yet more investigative stuff.
15. SSMS Tool Pack ...Microsoft SQL Server Management Studio add-ins don't get better than this one per Latish. It is free for the 2005 and 2008 stuff but has a price tag in 2012land. It does a number of things, such as let you have regions in big sprawling SQL messes, but most interesting/helpful is that it keeps a running history of your queries!
16. SQL Smash ...Another add-in. It allows you to highlight some SQL in the midst of a blob of SQL and (try to) run just the highlighted part.
17. WinDbg ...This is a piece of the Windows SDK that you already have. It allows you to dump programs to dump files. To use it, pull up the task manager, right click on an item, and pick: "Create dump file" (Windows 8)
18. GrayWolf ...Will let you import an .exe and will then try to decompile a WinForms application to IL (Intermediate Language).
19. scotthanselman.com ...This is a great example of a smart person's blog. Keep reading. Do use the 80/20 rule when reading. If you plan to dedicate a day to do research the real benefit will likely come from the first twenty percent of your time.

I saw Mark Rosenberg speak on optimizing stored procedures this month at Austin Code Camp.

Things suggested by Mark Rosenberg:

• Try to use table variables instead of temp tables. Table variables are just like other declared variables save that their type is TABLE. This has this example:
  DECLARE @ProductTotals TABLE
  {
     ProductID int,
     Revenue money
  }
  When you do encounter temp tables: Try to pull temp tables out of code into real tables, and/or make SQL make temp tables and then fill them instead of using a SELECT INTO approach for doing both acts at once.
• When MSSQL Server runs a query it must first plan how to do so. The plan is called "query plan." It is stored within a stored procedure, but does not exist premade in dynamic SQL (SQL found inline in code and SQL which perhaps varies in shape from query to query based upon conditions in code). Since SQL Server 2000, if dynamic SQL of a particular shape is run enough times then its query plan will become stored, however as the query plan must be drummed up otherwise it is obvious that a stored procedure will perform better than a dynamic query holding the same query logic as it does not have to drum up a query plan on the fly.
• Use SET NOCOUNT ON in a sproc to keep MSSQL from returning a count of records from a select statement. This will make a sproc faster. One does not have to call SET NOCOUNT OFF explicitly to achieve the opposite effect. The opposite effect, in which a count of records is returned, happens by default.
• Using a qualified name for a table called in a sproc will make the sproc speedier as MSSQL Server will not have to figure out where to look.
• If returning only a single record, just return the contents as output parameters while using the return value for an error code.
• Don't start the name of a sproc with "sp_" underscore as Microsoft uses this convention for its own stored procedures and will crawl through its own stored procedures first in attempting to find your stored procedure should you use this convention.
• Use sp_executesql instead of EXECUTE to run a stored procedure. This speaks to the difference.
• sp stands for special object!
• There is a distinction between data manipulation logic and data definition logic. The latter is of making and modifying tables and columns and the former is for operations against the tables and columns crafted in data definition logic. Do not put Data Definition Language (DDL) in a stored procedure.
• Use WITH RECOMPILE in a stored procedure to escape using a query plan. There is a way to use SQL Server Profiler to view a list of stored procedures and see which ones are using query plans.
• Use a WHERE clause before a HAVING clause to filter records before aggregation.
• UNION has DISTINCT baked into it. Using UNION ALL instead will give you better performance as it does not come with this extra thing.
• When matching on LIKE '%foo%' a query will have to look at every possible string value to parse every character inwards from the beginning in attempt to find "foo" somewhere downstream from the beginning. However, when matching on LIKE 'foo%' a query will only have to look at strings starting with the letter f. If possible, avoid the leading percent symbol in LIKE matching.
• Mark suggested trying to get out of the pain of swapping between CREATE and ALTER when running stored procedure code by doing some if-exists sanity checking. This has some potential examples of that sort of thing.