Increasing your Coding Speed in SQL Server Management Studio 2017

Every developer wants to be productive and get more things accomplished during their day to day work. Given a choice between working harder and working smarter, you will most likely choose the latter. But the big question is – How do you boost your productivity? How do you reduce your development time? How do you increase your coding speed? Gaining a superlative understanding of the IDE goes a long way in increasing one’s productivity.

In this session, you will learn a number of tips and tricks which will enhance your productivity and efficiency in SSMS IDE. We will explore ways to stop doing manual repetitive tasks, use shortcuts, type less and generate more lines of code in minimal time. This will be a content rich session with plenty of live demos.

By the end of this session, you will learn some useful techniques to take back with you and apply in your day to day work.

Transcript

1. INCREASING YOUR CODING SPEED IN SQL SERVER MANAGEMENT STUDIO 17 SAMIR BEHARA @samirbehara dotnetvibes.com
2. About Me I am Samir Behara. Currently working as a Senior Developer in EBSCO Industries, Birmingham. More than 12 years of experience in IT industry, working on large-scale enterprise applications involving complex business functions, web integration, and data management in various domains like Insurance, Manufacturing and Publishing. Twitter – @samirbehara LinkedIn -- https://www.linkedin.com/in/samir-behara-78953870 Website – www.dotnetvibes.com Steel City SQL User group - https://www.meetup.com/SteelCitySQL/
3. What we will try to cover in this session? Boost Productivity SSMS Tips and Tricks Increase Coding Speed SSMS 2017 Enhancements
4. SSMS 2017 Productivity Enhancements
5. SQL Server Management Studio 2017 • SSMS 2017 is FREE. • SSMS is an independent web installer. • SSMS is decoupled from the underlying SQL Server Engine. • SSMS now uses Visual Studio 2015 isolated shell. • More frequent releases for new features, enhancement and bug fixes.
6. New Installation Experience
9. Number of rows affected SSMS 2016 and Older Versions SSMS 2017
10. Import Flat File Wizard Predictive File Splitting Predictive Date Type Assignment Simplify the import experience of flat files to SQL Server
11. Built in XEvent Profiler Directly integrated into SSMS Built on top of Extended Events technology
12. Built In Performance Dashboard Reports • CPU bottlenecks • IO bottlenecks • Index recommendations (missing indexes) • Blocking
13. Searching in Execution Plans Use CTRL + F Right Click and select ‘Find Node’ option
14. Have you come across this scenario? You execute a TSQL Query and it just runs and runs, without displaying any results and you finally end up cancelling the executing query. How do you troubleshoot this problem?
15. Collect actual metrics of the query while it is running…
16. Benefits of Live Query Statistics Troubleshoot Long running queries Real Time Insights into SQL Query Execution Process Visually track overall query progress
17. How to enable Live Query Statistics? Using SQL Server Management Studio Using Activity Monitor
18. Navigation Buttons Move quickly between various tabs ‘Navigate Backward’ button has a drop-down list box containing the list of all your navigation history.
19. Automatic Check for Updates Built in notification system to alert users about updates for new releases. SSMS is up to date
20. Option to manually check for updates
21. Vertical Scroll Bar Enhancements The enhanced scroll bar displays different colors on the vertical bar – • Red – Syntax Errors • Blue – Current Cursor Position • Green – Saved Changes • Yellow – Unsaved Changes
22. Quick Launch – The search tool • Search features & options inside SSMS • No need to remember any shortcuts
23. Improvements to Execution Plan window • Allows you to zoom the contents of the execution plans. • CTRL + Mouse Scroll to increase/decrease size of execution plan.
24. Compare Execution Plans ‘Compare Showplan’ feature allows side by side comparison of two execution plans.
25. Dual Properties Window Choose specific operator and compare – • Actual Number of rows • Estimated Operator Cost • Number of rows read
26. Compare Global Properties Choose the SELECT operator and view – • Compile Time • Estimated Subtree Cost • Missing Indexes
27. Color Theme • Option to switch between 2 color themes – Blue and Light • Dark theme and high DPI support to be enabled in future releases
29. SSMS Tips and Tricks
30. Type less and generate more code Code Snippets are ready-made snippets of code, which you can insert into your T-SQL code. Expansion Snippet Type - CTRL K + X Surround With Snippet Type - CTRL K + S
31. Reuse code by creating Custom Code Snippet Create your own Code Snippet using XML Register the Code Snippet in SSMS Invoke the Code Snippet
32. Get Row Count of tables without executing a T-SQL script Click F7 or go to View -> Object Explorer Details window
33. Save connection information of frequently accessed servers Registered Servers 1 2 3
34. Execute a SQL Query against multiple servers at the same time 1 2 No need to connect to different database instances one by one and execute the same query multiple times.
35. Save time by leveraging pre-defined SQL scripts Template Browser 1 2 3
36. Use Keyboard Shortcuts to increase efficiency Shortcuts Action CTRL+R Toggle between displaying and hiding Results Pane CTRL+N Open new query tab with current database connection CTRL+L Display estimated execution plan of the query SHIFT+ALT+ENTER Toggle between Code Editor being displayed in Full Screen CTRL + ] Navigate to the matching parenthesis CTRL+K+X Insert SQL Code Snippets CTRL+SHIFT+U Change text to upper case CTRL+SHIFT+L Change text to lower case CTRL+K+C Comment selected text CTRL+K+U Uncomment selected text CTRL+TAB to switch between tabs Using Keyboard shortcuts for repetitive day to day actions
37. Leverage Regular Expressions for faster find and replace CTRL+SHIFT+H to open Find and Replace dialog box
38. Maximize your workspace with multiple code windows Horizontal/Vertical Tab Groups 1 2
39. Use custom colors to differentiate between environments
40. 1 2 Set different colors for connection to separate environments
41. Reuse your copied items by storing in memory Cycle Clipboard Ring - CTRL+SHIFT+V Allows you to keep track of last 20 items you have cut/copy and reuse them
42. Edit multiple lines of code at the same time Vertical Block Select Mode Keyboard Shortcut – ALT + SHIFT + Arrow Keys Mouse – ALT + Left-Click + Drag
43. Leverage built in Performance Reports in SSMS Standard Reports for Performance Monitoring & Troubleshooting
44. Generate SQL Insert scripts using SSMS ‘Generate and Publish Scripts’ Wizard 1 2
45. Troubleshoot issues with Intellisense in SSMS Edit -> IntelliSense -> Refresh Local Cache
46. Recover your unsaved SQL queries in SSMS Configure the ‘Auto-Recovery’ option to be helpful during SSMS Crashes & Sudden machine reboots
47. SSMS tries to recover unsaved queries, incase of unexpected SSMS shutdown. If you don’t get File Recovery prompt, navigate to the backup file location.
48. Customize your environment for better productivity Personalize the toolbar by placing frequently used buttons
49. Add buttons to the toolbar
51. More in-depth details on my blog – https://dotnetvibes.com/ https://dotnetvibes.com/my-sqlshack-articles/ SQL Scripts used for the purpose of this presentation are upload in – https://gist.github.com/samirbehara More information…
52. THANK YOU