SQL Prompt has lots of great features that can help you write SQL quicker. However, you’ve got to train yourself to use a few of these and not just start to type with your old habits. This quick tip looks at one of those areas where a little customization can make things work smoothly.

Perhaps you are like me and you’re often creating databases for testing things. It’s quick and easy, and certainly a good template goes a long way to ensuring that you don’t have any issues when you build a database.

There’s a “cdb” snippet in SQL Prompt that I have come to really like. Of course, the default code leaves something to be desired, so I usually change it. I wrote a bit about this on the Redgate blog, but there are a few other things I’d add to my changes for a developer.

Here’s the default code:

CREATE DATABASE database_name
ON
PRIMARY ( -- or use FILEGROUP filegroup_name
  NAME = database_name_data,
  FILENAME = 'database_name.mdf'
) --, and repeat as required
LOG ON
(
  NAME = database_name_log,
  FILENAME = 'database_name.ldf'
) --, and repeat as required
--COLLATE collation_name
--WITH
--  DB_CHAINING ON/OFF
--  TRUSTWORTHY ON/OFF
--FOR LOAD
--FOR ATTACH
--WITH
--  ENABLE_BROKER
--  NEW_BROKER
--  ERROR_BROKER_CONVERSATIONS
--FOR ATTACH_REBUILD_LOG
GO

Here’s how I changed this on the Redgate blog:

CREATE DATABASE database_name
ON
PRIMARY ( 
  NAME = database_name_data,
  FILENAME = 'E:\SQLServer\MSSQL12.SQL2014\MSSQL\DATA\database_name.mdf'
) 
LOG ON
(
  NAME = database_name_tlog,
  FILENAME = 'E:\SQLServer\MSSQL12.SQL2014\MSSQL\Log\database_name.ldf'
) 
WITH
  TRUSTWORTHY ON
GO

However, I really want other things in place when I’m working in a dev environment. For example, I’ve started to want to ensure that I create random test databases with the Simple recovery model. While I usually have the model database set to Simple, that isn’t the default and I sometimes forget. As a result, I’ll add this code to my snippet:

ALTER DATABASE database_name SET RECOVERY SIMPLE;

I also usually want to change the growth settings. I don’t care too much about the total limit, since I use placeholders, but I do want a slightly larger initial size to prevent growths when I load test data. In my case, I usually want to specify an initial size larger than model. Having this in the snippet also means I can easily modify it.

However, I don’t want to make this complex. I’ll make this easy by using the GUI to generate template code. I can open the Create Database dialog and see my options, which I can change, as I’ve done for size in the image (50 from 1 for data).

There’s a dialog for growth, and as you can see, I’ve adjusted the values.

and for things like recovery model. I’ve open the dialog below. Below here are all the other database options I might want to set.

When I’m done, I can click the “Script button shown in this image. It’s at the top of the dialog.

Once I do this, I cancel out of the GUI, and I can see my code.

Now I’ll cut and paste the items I want into my SQL Prompt snippet. This gives me something like this:

I could have more or less options, depending on what matters to me. I might even have a cdbp snippet for production settings, where I have more items specified to be sure they’re set. It’s one thing to expect defaults, it’s another thing to think they won’t change and shouldn’t be specified.

Hopefully, you’ll see the value in SQL Prompt and start using snippets to improve your ability to code quickly and take the hassles and guesswork out of building clean SQL Code. If you’d like to see another take on this snippet, check out my Redgate blog.

Try a SQL Prompt evaluation today and then ask your boss to get you this productivity enhancing tool, or if you’re using the tool, practice using ii the next time you need to insert some data.

You can see a complete list of SQL Prompt tips at Redgate.

Filed under: Blog Tagged: Redgate, SQL Prompt, syndicated