roseanna.mumby

10 February 2021

Customer tips for improving productivity with Redgate products

We recently invited our customers to share their top tips for improving productivity in the first Redgate Tool Tips Swap. This post outlines their hints for using Redgate products to boost productivity and includes relevant training resources to further your learning.

SQL Prompt

Share formatting settings across your team to consistently format code before checking it into source control. Justin Bird, Principal Consultant, Metricy UK.

“We keep ours alongside the code in source control so everyone can keep up to date. Adhering to code formats ensures ‘developer preference’ doesn’t result in false changes in the code base where a developer alters a block of code just to lay it out differently. The hardest part is getting the team to decide on a format!”

Use the ‘Script as Insert’ feature to re-seed data for an integration test. Scott Sauber, Technical Architect, Lean TECHniques Inc.

“When you get the results back from running a query, you can highlight the entire data set (or just the rows you care about), then right-click and choose ‘Script as Insert’. This will scaffold out the insert statement for you inside of SSMS. This is handy for scenarios such as generating a script to be used for re-seeding data for an integration test without having to write that insert by hand.”

Use the ‘Copy as IN clause’ feature to feed values from one query into another. Scott Sauber, Technical Architect, Lean TECHniques Inc.

“When you get the results back from running a query, you can highlight the entire column (or just the values you care about), then right-click and choose ‘Copy as IN clause’. This will save those values to your clipboard as an IN statement. So, if you highlight values 1,2,3, it will put ‘IN (1,2,3)’ onto your clipboard. This is handy during data exploration and feeding values from one query into another, such as grabbing the IDs from one query and dropping them onto the where clause of another query.”

Learn more:

• The Louis Davidson custom style for SQL Prompt
• How to Test SQL Server Functions and Procedures using SQL Prompt
• SQL Prompt Hidden Gems: The SSMS Results Pane

SQL Data Generator

Use SQL Data Generator to quickly create sets of data – and have fun with it. Dave Davis.

“We import celebrity names and make them into funky alternatives. Swapping the first and last names. It makes clients laugh a little when we show demo reports to them.”

Learn more:

• Getting your SQL Server Development Data in Three Easy Steps
• The Joy of Realistic Generated Fake Database Data

SQL Source Control

Follow the below method for a quick way of setting up and moving code around environments. Ben Brown, Data Technical Lead, Miller Insurance.

1. Hook all your environments up to source control using the Dedicated model (irrespective of whether you are using shared or dedicated environments).

2. Develop in your development environment and commit using SQL Source Control.

3. When you want to move the change to a test environment, just use the Get Latest tab. Same with production deployments.

4. If you ever need to do an emergency hotfix in production, just push to source control from there and make sure you get the latest on your development environment straight away.

“Some people may baulk at this, but I reckon I have done over 3,000 commits and 100s of production deployments using this method without ever running into a problem. It’s worth noting that this technique works well, in my experience if:

a) you have only one or two developers, co-located, and using one-piece workflow

b) you have automated tests so are confident your changes will work.”

The process Ben outlined above is a stepping stone to a more controlled, scalable method. By introducing a few more controls on the way you move changes between environments, you’ll be able to scale this to bigger teams (when required) and establish the basis for automation. To learn more about this, read these product articles:

• The Database Development Stage
• Database Development with GitHub

Thank you to everyone who shared their tips.

Visit the Redgate Forums to share and discuss tips with fellow Redgate users.