THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

  • Seldom Used Keyboard Tricks

     

    I was giving an internal talk on SSMS productivity trips and there were a few that I believe are seldom used but can be a real time or keystroke saver that I would like to mention. To the best of my knowledge these will work in any version of SSMS from at least 14.0 onward and likely earlier but I can’t verify older versions at this time. Since these are basically Visual Studio shortcuts they also work in Visual Studio and SSDT to the best of my knowledge. The first is the ability to select text in a vertical fashion as shown in the examples below. Before we get to how to do that I know your first question is why would you want to do that. Well I will leave all the possibilities up to you since everyone has slightly different techniques and circumstances. However I am confident that after seeing a few examples it will spark interest in many of you and you will immediately think of times when this will help you code more efficiently.

    OK now on to the HOW to do this. That part is really pretty simple and there are two ways that I know of off hand. BTW, I am sure that after playing with this for just a few minutes you will have figured it all out anyway but I will post some instructions and examples just the same. The first way is to position your mouse cursor on the line at the point in which you want to start. Then you can use your keyboard and press SHIFT – ALT and then use the arrow keys as you see fit. Or you can press only the ALT key and move your mouse in the direction you wish to highlight or operate on. Both techniques mentioned will give you the same results. I find the mouse method a little easier overall but YMMV. I will use the block of code shown directly below as the basis for most of the subsequent examples.

    image

    Keep in mind that there are 2 types of processes or operations that you might use this for. The first is typically used when you want to insert something into existing code at a certain spot but across multiple lines at once. The example directly below shows what that would look like when selecting the rows at a specific position or insertion point. If I placed my cursor just in front of the number 1 in the first row and then pressed SHIFT – ALT – Down Arrow 7 times I would get a thin (in this case blue) line like the one with the arrow pointing to it. More on this technique shortly.

    image

     

    However if I needed to use the second type of operation I would take that one step further and move my cursor / mouse left or right to highlight one or more characters in each row as shown below.

    image

    Once I have highlighted a set of rows / columns I can then replace that highlighted text with new text of my choosing in a number of ways. If I had something in my clipboard I can do a paste and all of the highlighted sections in each row will be replaced with the clip board contents.  OK I hear someone saying well you could have just done a search and replace. Well sure but if you look closely you will see each character that I had highlighted in the vertical column was a different value in each row. I would have had to do 8 different search and replaces instead of just one using this method. Or I can simply start typing and you will see the original text is instantly replaced with what ever I type as shown in the partially completed replacement below.

    image

    Another great benefit of this technique is that you can be selective in which blocks of code you want the text replaced or acted upon. For instance I want to operate on only 4 of the 8 lines of code. If I did a search and replace I would have to do this one line at a time and choose which line I wanted to act upon and which I wanted left alone.

    image

    Here I selected the middle 4 lines and upper cased the word select. How did I do that? Well this technique also works with standard keyboard short cut’s such as Ctrl- Shift-U which will upper case what ever is highlighted as I did below.  I am sure you can begin to see the possibilities of this technique.

    image

     

    In this example shown below I use the other technique mentioned earlier to position the cursor at a certain place vertically across what ever rows I need.

    image

    I often have to terminate a number of rogue processes at the spur of the moment and this technique comes in very handy for that. I can identify the session_id’s using something like sp_whoisactive but when there are multiple sessions it can get tedious and error prone to manually type the “KILL nnn” commands for each one. Now I can simply copy the list of session_id’s and paste them into a query window and set the insertion point directly in front of all the session_id’s at once. Now I simply type “KILL “ and it instantly fills this in at the correct spot before all the lines at once. Again this is impossible to do with search and replace as each row is different text. Now all I had to do is execute the batch and smile as I rest assured the SQL Server instance is once more safe from these rogue processesSmile.  Obviously you will find more uses for this based on your own environment but I think you get the idea.

    image

    And finally I wanted to point out that if you are using the pure keyboard method don’t forget to use the SHIFT along with the ALT. If you simply press just the ALT key plus an up or down arrow you will shift the line the cursor is on up or down one line for each arrow press. The example below shows what happens if the cursor was anywhere on line 3 and the ALT and up arrow key was pressed. While that tip can certainly come in handy as well it may not do what you expect. If you use the ALT and Mouse method this will not happen.

    image

    image

    I realize this was a long winded set of examples but I had fun outlining them. This is a very under utilized set of features that is built into some of your most important daily tools so remember to take advantage of them. I have also include a few links to some more extensive lists of short cuts for SSMS that you should find useful.

    BOL SSMS Keyboard Shortcuts

    Andy Mallon’s SSMS Keyboard Shortcuts

    Red-Gate SSMS Keyboard Shortcuts

     

    Enjoy,

    Andy

  • SQL Updates Newsletter–May 2017

     

    I missed posting the last few months of these but here is the latest update about the monthly newsletter that a good friend of mine (Fany Carolina Vargas) from Microsoft puts together each month. This shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here and this months update can be found here. I highly encourage each of you to look this over now and each month as I am confident there is something useful in there for each and every one of you. Here is the full URL for this months newsletter as well.

    https://blogs.msdn.microsoft.com/sqlupdates/2017/05/31/sql-updates-newsletter-may-2017/

    Andy

  • New Cumulative Updates for SQL Server 2014

     

    Microsoft announced two new CU’s for SQL Server 2014 yesterday. Below are the links to the KB’s and some quick facts for each CU.

    Cumulative Update 12 for SQL Server 2014 SP1

    SQL Server 2014 Service Pack 1 Cumulative Update #12 Quick Facts

    12 Issues Resolved

    BI\Analysis Services

    1

    BI\Reporting Services

    1

    Integration Services\Engine

    1

    SQL Engine\High Availability and Disaster Recovery

    2

    SQL Engine\Replication

    4

    SQL Engine\Service Broker

    2

    SQL Engine\SQLOS

    1

    Grand Total

    12

    Build: 12.0.4511.0

    Cumulative Update 5 for SQL Server 2014 SP2

    SQL Server 2014 Service Pack 2 Cumulative Update #5 Quick Facts

    24 Issues Resolved

    BI\Analysis Services

    4

    BI\Reporting Services

    3

    SQL Engine\Backup_Restore

    1

    SQL Engine\Column Stores

    1

    SQL Engine\DB Management

    1

    SQL Engine\Extreme OLTP

    1

    SQL Engine\High Availability and Disaster Recovery

    2

    SQL Engine\Metadata_Infrastructure

    1

    SQL Engine\Query Execution

    1

    SQL Engine\Query Optimizer

    1

    SQL Engine\Replication

    4

    SQL Engine\Search

    1

    SQL Engine\Service Broker

    2

    SQL Engine\SQLOS

    1

    Grand Total

    24

    Build: 12.0.5546.0

     

    Andy

  • Latest SQL Server 2014 CU Updates

     

    Microsoft released a few more Cumulative Updates for SQL Server 2014 as listed below. There are a number of interesting fixes so I highly recommend if you have SQL Server 2014 to take a look at the list of hotfixes included in each update.

    SQL 2014 SP1 CU11

    SQL 2014 SP2 CU4

    Happy patching,

    Andy

  • Stopping Execution of Future Batches

     

    There is a command that as far as I can tell has been in the product since SQL2008 but I was woefully unaware of. Actually I find many others are unaware as well and hence the blog post. The command is SET NOEXEC which has options for ON or OFF.  Normally this is OFF by default and the issue I was trying to solve was this:

    I have a TSQL Script with N individual batches separated by a GO. Or simply N many individual statements or sets of statements that I don’t ever want to be executed all at once. In real life I often have a single script with lots of pieces (batches) vs. many individual scripts for convenience, ease of use etc. However I don’t want to accidently run the entire script or any other statements that I don’t manually highlight and run. A simple example is shown below:

    PRINT 'Got Here 1' ;
    GO
    PRINT 'Got Here 2' ;
    GO
    PRINT 'Got Here 3' ;
    GO
    PRINT 'Got Here 4' ;
    GO

    If we were to hit F5 (or however you execute your TSQL statements in SSMS) without highlighting any statement(s) they would all be executed, one batch after the other. Even if one batch were to fail or we had a THROW in that batch it would fail at that point but execution would continue immediately after the next GO until the end. This is where SET NOEXEC ON comes into play. If I add that at the beginning of the script all succeeding code would not be executed. The statements would only be compiled and not actually run. It would look like this:

    SET NOEXEC ON;

    PRINT 'Got Here 1' ;
    GO
    PRINT 'Got Here 2' ;
    GO
    PRINT 'Got Here 3' ;
    GO
    PRINT 'Got Here 4' ;
    GO

    And if needed you could always set it back OFF as the example below show:

    SET NOEXEC ON;

    PRINT 'Got Here 1' ;
    GO
    PRINT 'Got Here 2' ;
    GO
    PRINT 'Got Here 3' ;
    GO
    SET NOEXEC OFF;

    PRINT 'Got Here 4' ;
    GO

    If you run each of these in SSMS you will quickly see how this setting affects the actual execution of the statements in the batches. Adding this command at the top of your scripts will help to prevent unwanted execution of the code in that script whether it be the entire set of statements or any individual ones. Once this is in the script you would either have to comment out the SET NOEXEC ON statement or manually highlight the statement or set of statements you want to run before hitting F5. I am pretty confident that many of you will find this tip useful. I know I did and wish I knew about it much earlier.

    Good luck,

    Andy

  • SQL Updates Newsletter – January 2017

     

    Here is the latest update about the monthly newsletter that a good friend of mine (Fany Carolina Vargas) from Microsoft puts together each month which shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here and this months update can be found here. I highly encourage each of you to look this over now and each month as I am confident there is something useful in there for each and every one of you. Here is the full URL for this months newsletter as well.

    https://blogs.msdn.microsoft.com/sqlupdates/2017/01/31/sql-updates-newsletter-january-2017/

    Andy

  • Latest SSMS Download (16.5.3)

     

    There was a slight issue with the SSMS version that was released a few days back (16.5.2) which has been fixed and replaced with release 16.5.3. If you want the latest SQL Server SSMS 2016 release or if you already loaded the 5.2 version you should get this one.

    SSMS 2016 Version 16.5.3

    Andy

  • Latest SQL Server Updates

     

    There are some new updates for SQL Server 2012 and SQL Server 2016 that were released today. I am not sure what happened to SQL 2014 but I suspect those will be out shortly as well. Here are the links and the associated KB’s.

    SQL Server 2012 Service Pack 2 CU16KB3205054

    SQL Server 2012 Service Pack 3 CU7   – KB3205051

    SQL Server 2016 RTM  CU4KB3205052

    SQL Server 2016 Service Pack 1 CU1KB3208177

     

    Andy

  • SQL Updates Newsletter – December 2016

     

    Another friendly reminder about the monthly newsletter that a good friend of mine (Fany Carolina Vargas) from Microsoft puts together each month which shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here and this months update can be found here. I highly encourage each of you to look this over now and each month as I am confident there is something useful in there for each and every one of you. Here is the full URL for this months newsletter as well.

    https://blogs.msdn.microsoft.com/sqlupdates/2017/01/03/sql-updates-newsletter-december-2016/

    Andy

  • SQL Server 2014 CU3 for SP2 and CU10 for SP1 are Available

     

    Just an FYI for those of you on SQL Server 2014 SP2 that there is a new CU available for download can can be found here. Also if you are still on SP1 for SQL Server 2014 you can get CU10 as well here.  https://www.microsoft.com/en-us/download/details.aspx?id=51186  There are a number of interesting fixes but one in particular that has caused issues for me is described in this KB here. It is when statistics are blown away when you rebuild a partition on a partition aligned index. I for one am happy to see this finally fixed but there are many more. Check them out and happy updating.

    Andy

  • SQL Updates Newsletter – November 2016

     

    This is a friendly reminder about a monthly newsletter that a good friend of mine (Fany Carolina Vargas) from Microsoft puts together each month which shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here and this months update can be found here. I highly encourage each of you to look this over now and each month as I am confident there is something useful in there for each and every one of you. Here is the full URL for this months newsletter as well.

    https://blogs.msdn.microsoft.com/sqlupdates/2016/12/01/sql-updates-newsletter-november-2016/

    Andy

  • Source Control for SSMS

     

    It would be awesome if we could use source control such as TFS directly in SSMS like we can in Visual Studio but at the moment we can’t for the reasons explained by Ken Van Hyning in this blog post.  However he also explains how we can manually add some components that will give us the ability to use TFS with SSMS in the mean time.  This is something I personally have been wanting to do for a while and didn’t think possible so I wanted to share this with all of you as well.

            Source Control in SSMS

    Enjoy,

    Andrew J. Kelly

  • SQL Nexus 5.5.0.1

     

    The SQL Nexus set of utilities are some of the most under utilized tools available to the SQL Server community. I used and touted the RML utility for many years to help people analyze trace information very fast and easily. Eventually XE traces were added to the list along with much more.  This blog post link will give you a pretty good overview of what you can expect along with the the codeplex download page which also goes into some details. The included documentation has always been top notch in my opinion. If you have never took a look at these utilities I highly recommend doing so. As always, enjoy.

    Andy

  • SQL Server 2016 SP1 is now Available

     

    Some big news today in the world of SQL Server and Visual Studio announced at Connect.  One of the announcements that will spark the interest of most of the people here on this blog is that SP1 for SQL Server 2016 is available for download now.  Why is this important? Well there are a ton of changes related to what features are available in the different versions as shown in the chart below. I won’t repeat all the details since these two links below go into pretty good detail already.

    SQL Server 2016 SP1 Released

    SQL Server Blog Announcement

    Enjoy,

    Andy

  • SQL Updates and More

     

    A good friend of mine (Fany Carolina Vargas) from Microsoft puts together a fantastic blog post each month which shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here. I highly encourage each of you to look this over as I am confident there is something useful in there for each and every one of you.

    Andy

More Posts Next page »

This Blog

Syndication

Privacy Statement