Voice of the DBA

Tag: T-SQL

  • Community Direction

    When Microsoft implemented Connect, I thought it was a great idea. It was a way for real users to submit bugs, and others to see those bugs, voting on them if they thought they were important. It would help Microsoft determine what features and bugs are important and perhaps allocate resources accordingly. However there was a fundamental problem with the system. People would see individual items, and could vote for them, but wouldn’t have an idea of what other items might be listed.

    The work on SQL 11 is underway, and recently I got a note from Itzik Ben-Gan asking people to vote for windowing enhancements to the T-SQL language. I’m not sure exactly of all the places that these are useful, but Itzik is one of the smartest people I know and I tend to believe that if he finds these enhancements useful, they are likely going to make T-SQL easier to work with.

    But are these items a priority? I am sure they are valuable, but are they more valuable than CREATE or REPLACE? IS it more of a priority than allowing SSMS add-ins? There are any number of enhancements that are listed, but most of us don’t have the time to dig through them all, or even try to determine how important they might be when weighed against other items.

    Microsoft can do what they want, and they need to keep one eye on the sales generated from new features. However I wish that they’d reserve a slice of their development efforts for older features and get some community help in choosing which items to work on. I’d love to see a list of the items they are considering, maybe the top 20 features, and let us add votes to pick the 10 they can work on.

    We may not sign the purchase orders, but us DBAs really like SQL Server and would appreciate improvements that make our jobs easier.

    Steve Jones

    PS: Here are Itzik’s items:

  • Table Variables and Transactions

    I actually had a question of the day submitted on SQLServerCentral about table variables and transactions, but the person didn’t have a reference for it. So I had to go digging around to find one. It doesn’t seem to be documented in BOL, but numerous MVPs and MS employees have posted about this behavior (by design) in places.

    Here’s the code I saw:

    DECLARE @MyTable TABLE 

      ( MyIdentityColumn INT IDENTITY(1,1),
            MyCity NVARCHAR(50))
    INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
    BEGIN TRANSACTION IdentityTest
    INSERT INTO @MyTable (MyCity) VALUES (N'London')
    ROLLBACK TRANSACTION IdentityTest
    INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
    SELECT * FROM @MyTable mt

    What do you expect from that? What about this code?

    CREATE TABLE TranTest 
     ( MyIdentityColumn INT IDENTITY(1,1),
           MyCity NVARCHAR(50))
    GO
    INSERT INTO     TranTest (MyCity) VALUES (N'Boston');
    BEGIN TRANSACTION IdentityTest
    INSERT INTO Trantest (MyCity) VALUES (N'London')
    ROLLBACK TRANSACTION IdentityTest
    INSERT INTO TranTest (MyCity) VALUES (N'New Delhi');
    SELECT * FROM TranTest mt

    In the second case, you’d expect two rows, right? Something like this:

    trantest1

    However in the first case you get:

    trantest2

    The insert with “London” isn’t rolled back. This is because the table variable doesn’t participate in transactions. You can see this with this update as well.

    DECLARE @MyTable TABLE (MyIdentityColumn INT IDENTITY(1,1),
         MyCity NVARCHAR(50))
    INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
    BEGIN TRANSACTION IdentityTest
    UPDATE @MyTable SET MyCity = 'Denver'
    ROLLBACK TRANSACTION IdentityTest
    INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
    SELECT * FROM @MyTable mt

     

    trantest3

    You might think this is a bug, after all, a transaction is supposed to capture changes and enforce the ACID principles. That is true, but a table variable isn’t a permanent change on the database. It’s a temporary object that exists only in memory, and only for the duration of the batch.

    This means that if you want to persist anything from a table variable, you need to write it to a real table, which will enforce ACID principles.

    Where do you need this? It’s extremely handy for capturing information about potential issues in a transaction, like logging, and returning them outside of the transaction (where/when you can store them in a real table).

  • Rights for one table

    I ran across a thread that was asking how to grant rights to a person for one table only, and not other tables.

    My response is simple:

    CREATE ROLE MySingleTableRole
    GO
    GRANT SELECT ON     dbo.MyCustomers TO MySingleTableRole
    GO
    EXEC     sp_addrolemember 'MySingleTableRole', 'Steve'

    That’s it. By default users do not have rights to any tables even if they have rights to the database. If you have a user that needs rights to one table, just grant them rights to that table.

    If the user is a member of a group that has rights to other tables, you should probably remove them from the other group/role. Then build another group for them, or for the other users. If this is the case, then your group is incorrectly being used as you have people in the group needing different permissions.

  • T-SQL Tuesday #10 – Indexes

    TSQL2sDay150x150[1] It’s time for T-SQL Tuesday again, the brainchild of Adam Machanic (Blog|Twitter), and this month’s topic is Indexes. A thanks to Michael Swart for hosting this month’s event, and sending me a personal invite through email. That was pretty cool, especially since I don’t necessarily remember when the event is kicking off.

    The invitation is here, and time is running out to participate, but if you want to blog, you can do it today before this script (from Michael) tells you that you can’t

    IF GETUTCDATE() BETWEEN '20100914' AND '20100915'
    SELECT 'You Can Post'
    ELSE
    SELECT     'Not Time To Post'

    Remembering to Index

    Indexing seems to be one of those things that so many people forget to do. As a result, adding a few indexes is often the easiest win, the lowest hanging fruit, the most effective way of improving performance in many situations.

    I know in the past that I almost always create a single index on a new table, the PK, when I build it. If there are FKs, those are setup as well, and this usually helps, but when building a database, I sometimes forget to add other indexes since I don’t know what other queries I might be running when I start. And as I start to build those queries (to support new functions for the user), it’s easy to forget to add an index.

    sqldatagenerator[1] Since it’s hard to guess what you will query often, and the think/build/test/refactor model leads you down dead ends at times, I don’t typically build an index for a new query. I don’t notice performance issues since I’m usually working with test data that has dozens of rows, not hundreds. In the past, I’ve wished for something like Data Generator. Now that I have it, I typically don’t need it anymore.

    I try to go back and add in indexes as we get close to deployment, but I’ll admit that I typically have found in the production system that I’m missing indexes. This usually occurs over time, as data grows and performance decreases. I’ve wished that I had a better method for handling indexes, and apparently Microsoft was listening to wishes since we now have sys.dm_db_missing_index_details and other missing index DMVs.

    However I found someone that did have a solution. The company I worked for bought a third party piece of software to handle some specialized function for a department. I wasn’t involved, but one day they came to me with a performance issue. As I dug into the application, which had been working for months, I noticed something interesting.

    Every column was indexed.

    Not only was every column indexed, they had added other indexes that reversed columns in the index, so if I had a table like this one:

    CREATE TABLE Customers
    (
    CustomerID INT
    , FirstName VARCHAR(50)
    ,     LastName VARCHAR(50)
    ,     ADDRESS1 VARCHAR(50)
    ,     City VARCHAR(50)
    ,     StateID INT
    , Country VARCHAR(50)
    , Notes varchar(max)
    )

    I might have these indexes:

    • CI : CustomerID
    • NCI: FirstName
    • NCI: LastName
    • NCI: FirstName, Lastname
    • NCI: LastName, Firstname
    • NCI: Address1
    • NCI: City
    • NCI: StateID
    • NCI: Country
    • NCI: Notes
    • NCI: Address1, city, stateid, country

    It seemed like a little overkill, and after discussing this with the support engineer for awhile, he admitted that a number of these indexes weren’t needed, but the developer didn’t know what to index, so they indexed everything.

    We decided to run a trace on the application for a week, sort through queries, and come up with some more rational choices for indexes. We also removed a few of the duplicate indexes, and we improved performance for a number of functions.

    Indexing is important, but you can overdo it. Make sure that you index, but also think about what you index, and don’t index everything.