Voice of the DBA

Tag: T-SQL

  • Common SQL Server Mistakes – Functions in the WHERE Clause

    This continues my series on Common SQL Server mistakes, looking at more T-SQL mistakes.

    What’s Wrong?

    If you saw a query like this, would you see a problem?

    select
      o.OrderID
      , o.CustomerID
      , o.Qty
    from Orders o
    where datepart( yyyy, o.OrderDate) = '2010'

    If there are 1,000 orders in this table, there probably isn’t an issue. But if there are 1,000,000, then this is an issue.

    Why? Let’s examine the execution plan:

    This table has 1000 rows in it, but it doesn’t use indexing to find those orders that were placed in 2010. Instead it scans all rows. The reason is that the function being used in the WHERE clause means that the index cannot be used.

    Instead, what you would want to do is write the query like this:

    select
      o.OrderID
      , o.*
      , o.Qty
    from [OrderItems] o
    where o.OrderDate >= '20100101'

    In this way, we eliminate the function from the WHERE clause and allow the query optimizer to take advantage of the indexes on the column OrderDate.

    You see similar issues with queries like:

    select
    lastname
    from Person.Contact
    where left(Lastname, 1) = 'S'

    This can be fixed as:

    select
    lastname
    from Person.Contact
    where Lastname like 'S%'

    Basically you want to move the function away from the column and put it on the other side of the comparison so that indexes can be used.

    Too often we have developers writing queries like this, assuming that the functions are efficient. They are, but when they are executed against every row in a table, an index can’t be used for seek operations, which are always quicker than scans for any significant data set.

    When you are writing queries, do your best to avoid functions against columns in your tables. Instead try to rework the query to move the function. An alternative that I’ll blog about another time is computed columns.

  • Common SQL Server – Not Indexing FKs

    This series looks at Common SQL Server mistakes that I see many people making in SQL Server.

    Foreign Keys

    It’s way too often that I see people building databases without including declared referential integrity (DRI) in their databases. Even when I see people setting a primary key on tables, it seems that often they ignore foreign keys and creating linkages between tables that link them together.

    However, even when people have declared a FK, they often don’t create an index on that column. Perhaps they assume that SQL Server will create the index like it does for PKs, but it does not.

    If I create these two tables and join them with a FK:

    CREATE TABLE [dbo].[Products](
        [ProductID] [int] NOT NULL,
        [ProductName] [varchar](50) NULL,
    CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
    (
        [ProductID] ASC
    )
    ) ON [PRIMARY]

    GO
    CREATE TABLE [dbo].[ProductDetails](
        [ProductDetailID] [int] NOT NULL,
        [ProductID] [int] NULL,
        [SKU] [varchar](50) NULL,
        [Price] [numeric](18, 2) NULL,
    CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED
    (
        [ProductDetailID] ASC
    )
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[ProductDetails]  WITH CHECK ADD  CONSTRAINT [FK_ProductDetails_Products] FOREIGN KEY([ProductID])
    REFERENCES [dbo].[Products] ([ProductID])
    GO

    ALTER TABLE [dbo].[ProductDetails] CHECK CONSTRAINT [FK_ProductDetails_Products]
    GO

    If I go and check indexes on ProductDetails, I find that there is only one index, the index for the PK.

    FKIndex_a

    Why is this a problem? It’s because of performance. We should realize that indexes speed up performance by reducing the amount of work that SQL Server has to do.

    With FK columns, what I’ve often found with child tables is that I know the value of the FK column I am searching for and don’t need to join with the parent table. However without an index on the FK column, this query requires a table scan.

    select
    sku
    , price
    from ProductDetails pd
    where pd.ProductID = 3

    If you are creating FKs in your database, don’t forget to index them where appropriate.

    Auto Creation

    I’ve seen some people ask why SQL Server doesn’t automatically create indexes on those FK columns. I am torn on this, but I like the 80/20 rle. If 80% of the tables would benefit from it, I think it should be done. I am leaning towards some intelligent mechanism to do this.

    The main issue is that you might not want just an index on the FK column. You might want some sort of covering index that includes columns in addition to the FK column to prevent key/bookmark lookups to the clustered index. If you can avoid those, you can drastically increase performance.

    There is also the chance that with your query load, you never use these indexes. That can be horrible for performance as well since there is overhead to maintain these indexes on all insert/update/delete operations.

    The Advice

    Look at the queries that are coming into your database. Check the missing index DMVs and if you find that the FK columns are being used, index them.

    If you’re not sure, or don’t know how to look for missing indexes, here’s a reference.

  • Common SQL Server Mistakes – Equals NULL

    One thing that I don’t see a lot, but it still happens with people new to SQL Server is the comparisons they’ll make with NULL values. Often those people new to T-SQL will write this:

    select CustomerID, CustomerName
    from Customers
    where SalesRepID = NULL

    The thought here is they are looking for those customers that don’t have a salesrep assigned. Or they might enclose the NULL in quotes, but this won’t work.

    The correct way to do this is:

    select CustomerID, CustomerName
    from Customers
    where SalesRepID Is NULL

    Note the “Is NULL” that will correctly return those customers who have a NULL value stored in that column.

    Why?

    NULL is an unknown value. We just don’t know what value it is, so it’s not a variable in algebra like “x”. In algebra, x=x, but NULL != NULL. Since we don’t know what the value is, and since each row could potentially have a different value (remember every NULL’s value is unknown) we can’t expect any NULL to equal any other NULL.

    NULL isn’t a placeholder like a blank or space, or even zero. It’s an unknown value, so equals (and not equals) does not apply. Instead you need to use “Is NULL” or “Is Not NULL” for your comparisons.

  • T-SQL Tuesday #12 – Misconceptions

    TSQL2sDay150x150 It’s time for T-SQL Tuesday again, and I’m happy to participate again. This is a monthly blog party started by Adam Machanic (Blog|Twitter )that asks people to write on a particular topic all on the same day.

    This month’s theme is misconceptions in SQL Server, hosted by Sankar Reddy. You can read the rules for the party and get information from his blog.

    The Most Common Misconception

    I read a lot of forum posts, literally hundreds a week on all topics in SQL Server. One thing that I consistently see asked is the dreaded:

    “My transaction log has grown so large it filled the disk”

    That’s a common occurrence, too common in my opinion, and while there are people that forget to setup any backups, I also find in the majority of cases people just don’t understand one thing:

    A full backup does not clear the transaction log.

    Too many people assume that the log will get managed by a full backup. It doesn’t. While some log record get included in a full backup, they do not get marked as “backed up” and the space re-used.

    If there is one thing that I wish everyone managing a SQL Server knew, it would be that they need to make full and log backups to properly manage the disk space usage by their logs.