Voice of the DBA

Tag: SQLNewBlogger

The May Blogger Challenge–Continuing On
Last month I participated in Ed Leighton-Dick’s April Challenge. My four posts were tagged the April Challenge, and this month I plan to continue, and hopefully get a few more people to up their blogging.

Building on April

To make the next step in starting to blog regularly, I’d like to see people in May continue with their weekly blogging, but do one more thing: write one additional post.

This additional post I’d like to be one of the T-SQL Tuesday topics. Pick one from the list of past choices and write a post. Feel free to title it with the T-SQL Tuesday number and post it. It really doesn’t matter when you wrote it. The whole idea is to just write.

Getting Started in May

If you didn’t participate, or you struggled to get 4 posts, get started in May. Write one post per week and publish it. You can tag it the #SQLNewBlogger or you can just send a note to myself or Ed. We’d like to know what you’re doing.

If you struggle to write, do one of these two things:
- Make a note, send yourself an email, whatever, when you close a ticket or complete a section of code.
- Use one of my topics
Either one of these things work. I’d prefer you do the first one and just make a note when you do something. I’m sure you do something at work. Maybe it’s check logs, maybe it’s solve a problem with an instance, maybe it’s write a report. Anytime you do something, make a note.

Then write about that note in a weekly session.

If you are still struggling for topics, here are a few that I’d like to see you tackle in May:
- Where do you use a unique index?
- Generate a script after adding a column in the SSMS table designer
- Query for the list of recent full backups in msdb for a single database
- Show how to join two tables.
Pick one and write. Set aside a 15 minute session a week to write, and work on a short, < 1 page, post.

Good luck, and hope to see you blogging in May.
April 27, 2015
April Blogger Challenge 3 – Filtered Indexes
The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

Why Filtered Indexes

I haven’t used filtered indexes much in my career as they didn’t exist while I was a full time SQL developer and DBA. They appeared in SQL Server 2008, and are a very useful feature in certain circumstances.

Imagine I have a simple table:

CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (firstname, lastname) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO

I want to add some indexes that look for specific fields since those are times I am limiting searching in the table. For example, suppose that gender is an optional entry by users. I find that I often have NULL in this column, so I create a filtered index.

I’ll do that as follows:
```
CREATE INDEX Users_Gender_Filtered 
   ON Users (gender) 
   WHERE gender IS NOT NULL;
```
This is the same type of index statement we normally use, but we’ve added a WHERE clause to the statement. This means that only those rows with a non-NULL value in the gender column will be included in this index.

Why Use Filtered Indexes?

There are some reasons given in the BOL article below, but the main reason to do this is performance. I could easily add an index using gender that isn’t filtered, but if I had 1.5mm rows in my table and 1.4mm of them have NULL here, then do I want to scan those 1.4mm rows if a query wants a specific gender? I don’t.

Indexes take space, which means more space on disk, more backup space, and more time for backups. However they also impact performance when we read the index, or we update a column used in an index. Each of those operations requires either a read or write of the index. If I can reduce the reads and writes to indexes, then I can improve performance dramatically.

I would urge you to look at filtered indexes whenever you have data where a large number of rows are not going to match a query. In these cases, you can use a filtered index that is highly selective.

References

A few places I used to research this post.
- https://msdn.microsoft.com/en-us/library/cc280372.aspx
- https://www.simple-talk.com/sql/performance/introduction-to-sql-server-filtered-indexes/
Quick and Easy Blogging

This post occurred to me while I was writing some code and was the first post I thought of for the challenge. However I realized I could start with the other posts, and so I wrote those posts first. This one took about 10 minutes to read through limits on filtered indexes, try 4-5 experiments to get the code to work, and write the post.

Look for the other posts in the April challenge.
April 16, 2015
April Blogging Challenge 2 – Primary Key in CREATE TABLE
The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

Designating a Primary Key at Design Time

In the first post on this subject, I noted that I often add a primary key after the fact, using ALTER TABLE. However I’ve been wanting to build the habit of adding the PK at design time, inline with the CREATE TABLE statement, so I decided to look up the syntax and start practicing.

The inline code adds a CONSTRAINT clause into the table definition. I can do this in two ways, the most common is after I’ve built all the columns, I add CONSTRAINT, a name (PKUsers) then the PRIMARY KEY keyword with the optional clustered/nonclustered designation, and finally the column(s) in parentheses

Here’s my new code:

CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (MyID) ); GO

The better way, IMHO, is to do this inline if you have one column. I know this isn’t consistent, but I can easily see the constraint this way.

CREATE TABLE Users ( MyID int IDENTITY(1, 1) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (MyID) , firstname varchar(250) , lastname varchar(250) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO

This is more difficult to see if you have multiple columns, but you can do this:

USE sandbox; GO CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (firstname, lastname) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO DROP TABLE dbo.Users; GO

Simple and easy to find the PK now, it’s named correctly, and it’s easy to read.

References

A few places I used
- https://msdn.microsoft.com/en-us/library/ms189039.aspx
Quick and Easy Blogging

This post occurred to me while I was writing my first post. Actually, this occurred to me first, but I realized that I often do the ALTER method in post 1, so I wrote that first.

Changing to this post required using the same MSDN article, dropping the table, rewriting the DDL code, and testing it. About 5 minutes for this one because it was based on a previous post.

Look for the other posts in the April challenge.
April 9, 2015
New Blogger Challenge 1 – Adding a Primary Key
The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

Primary Keys

I firmly believe that every table should have a primary key. At least until you have a reason not to have one. If you have a reason, fine, but if you can’t explain it or convince me, then just add a primary key.

I have tended to build tables like this:
```
CREATE TABLE Users
(
  MyID int IDENTITY(1, 1)
, firstname varchar(250)
, lastname varchar(250)
, gender char(1)
, postalcode varchar(12)
, contactphone varchar(12)
);
GO
ALTER TABLE Users ADD PRIMARY KEY (MyID);
```
Lately I’ve not liked that as my primary key now has a name like [PK__Users__7131A74146D2BBC1]. I’d rather have a more organized database with a touch more effort.

The better way to add the key later is like this:
```
ALTER TABLE dbo.Users 
  ADD CONSTRAINT pkUsers PRIMARY KEY (MyID);
```
This way I can name the key, and I specifically note this is a constraint, and with the PRIMARY KEY option, it’s a unique constraint.

References

A few places I searched around to double check myself.
- https://msdn.microsoft.com/en-us/library/ms189039.aspx
- http://stackoverflow.com/questions/11794659/add-primary-key-to-existing-table
Quick and Easy Blogging

This post occurred to me while I was writing some code. I mocked up a table in about 2 minutes, and then ran a quick search on the Internet. Reading a few links was about 10 minutes and then testing the code (including dropping the table and recreating it a few times) was less than 5 minutes. All told, I solidified some knowledge and completed this in about 20 minutes. I also have drafts and ideas from this post for 2 other posts that cover this same topic in a similar way.

Look for the other posts in the April challenge.
April 2, 2015