Cleaning up the [NOT] NULL for Columns with SQL Prompt 10


SQL Prompt 10 is out and there are a few interesting things that have changed in the product. One of these is the Quick Fixes, inspired by some other IDEs that help developers learn to fix their code.

This post looks at one of the most common things developers ignore, and I’m guilt of this in many of my demos and code.

Defining Columns

When I create a table, I often will write some code like this:

CREATE TABLE dbo.EventSchedule
( EventScheduleKey INT IDENTITY(1,1)
, ScheduleName VARCHAR(100)
, StartTime DATETIME2
, Active BIT)

This looks fine, and it works. However, it’s a SQL code smell, and it’s something that you should avoid. The problem with this code, is that I haven’t specified the nullability setting for the columns. I am assuming a default for my database, which has worked for me, but as I work with a more diverse set of customers, I know this isn’t a good practice.

SQL Prompt 10 actually catches this. If I screenshot the code, I can see the green squiggly that notes there’s an issue.

2019-11-13 15_00_11-SQLQuery3.sql - Plato_SQL2017.sandbox (PLATO_Steve (60))_ - Microsoft SQL Server

That’s a code analysis item that our software has flagged. If I hover over the area, I see this:

2019-11-13 15_02_49-SQLQuery3.sql - Plato_SQL2017.sandbox (PLATO_Steve (60))_ - Microsoft SQL Server

That’s good, but if I’m a junior developer, what do I do? What does this mean? One of the very nice things with SQL Prompt 10 is that we provide some guidance, similar to what Visual Studio does.

Look on the left of the image below. I’ve put the cursor on the line with the green squiggly. There’s a little yellow light bulb in the margin. This is a note that we provide a fix here. You can click the keyboard or the yellow icon.

2019-11-13 15_07_30-SQLQuery3.sql - Plato_SQL2017.sandbox (PLATO_Steve (60))_ - Microsoft SQL Server

When you do this, you will see that there a few choices. I can fix this (the wrench or spanner icon), get the details for this code issue (the eye icon) or see all issues in my code.

2019-11-13 15_07_42-SQLQuery3.sql - Plato_SQL2017.sandbox (PLATO_Steve (60))_ - Microsoft SQL Server

If I click the “fix” icon, I’ll see this:

2019-11-13 15_07_49-SQLQuery3.sql - Plato_SQL2017.sandbox (PLATO_Steve (60))_ - Microsoft SQL Server

NOT NULL has been added, but the NOT is highlighted. I can hit <enter> and this will stay there, or hit Backspace and remove the NOT. Either way, I get the code cleaned up.

At least some of it. You see the next green squiggly on the line below.

SQL Prompt continues to be one of the best productivity tools for developers working with SQL Server. If you have it, look for the quick fixes, and code smell squiggly lines. If you haven’t, download a trial today and see how it will help you become a better T-SQL developer.

Original post (opens in new tab)
View comments in original post (opens in new tab)