Voice of the DBA

Tag: T-SQL

  • 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.

    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 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

    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.

  • The Devil’s in the Details

    This week I was scanning through a number of SQL Server posts (in between working on April Fool’s Jokes) and a couple of them caught my eye. They dealt with simple subjects, but subjects whose details are important. I’ve often found people have had performance issues in SQL Server because of simple misunderstandings of how the system works, or because not enough weight is given to the impact of small details.
    Kimberly Tripp (of SQLskills) wrote a basic post on parameters, variables, and literals, which would think most programmers would understand. However I realize more and more that lots of people that write code in the world taught themselves. That’s amazing, but it does mean that people have many different holes in their knowledge. What I might consider basic or common knowledge might be something that another person never had been exposed to. In any case, Kimberly does a fantastic job of laying out the differences in these three concepts, how they work side by side, and how the choice can dramatically impact performance of your code. Read this one.
    The other post this week was a fairly simple post on data conversions by Rob Sheldon. Again, precedence and conversions are something I dealt with when writing C code, and you do not want to get things wrong there. However many people might not have the same background and wouldn’t understand by varchar and nvarchar wouldn’t be a basic, simple conversion with little cost. Or why the type of conversion might result in far different results than you expect. Since we often test with one set of data and our application work with a (much) wider set of data, this one is a must read as well.
    There are so many things you should consider when writing code it’s really a constant and regular effort to improve your skills in this business. You won’t learn everything, or learn something every day, but pick things up and practice with them. Learn to build better code, one technique at a time, and cement that knowledge with actual implementation in your work.
    You’ve got at least two new things to look at this week, so pick one and try to ensure your code next week considers these details.
    Steve Jones

  • Large Chunks of Data

    This editorial was originally published on Sept 17, 2010. It is being re-run as Steve is on vacation.

    I saw a post recently where someone talked about trying to get better performance from a report. They were selecting 5mm rows from a table and wanted to see if there was a more efficient way to chunk out this data so that the instance would not report memory errors.

    My first question is what kind of report has 5mm rows of data? That’s just too much data.

    How long can a report be?

    From your experience, talking with people, looking at what they analyze, how big is a report? How much data can you really display on a report and make it useful for users?

    I’m thinking here in terms of the raw data you show. A pivot table can summarize millions of records, but realistically I thin kit becomes hard to examine more than a few hundred data points on a page. Whether they are raw data or aggregates of other data, it seems there’s some limit to what a report should provide.

    After all, that’s why we have drill-down 🙂

    Let us know this Friday what you think; what you have observed? Maybe we’ll  help others to build better reports that are more practical and useful to end users, as well as easier to develop.