Voice of the DBA

Tag: T-SQL

  • Common Mistakes

    At times I am rather dismayed by the quality of code I see written today. I’m not sure it’s worse than the poor code compiled early in my career, but there are so many more people writing code in our industry that it seems there is more and more poorly written code.

    We suffer from the chef problem. As more companies look to become software companies, they need to hire more software people. To meet the staffing demand, more and more marginally skilled people will be chosen, and software quality goes down.

    Part of what we do here is to try and educate the SQL Server professionals on how to become better at their jobs. That’s really the core mission that started SQLServerCentral and continues today thanks to the belief in that mission by Red Gate Software. As we look to do that, we want to bring to light the things that aren’t good ideas and can cause problems.

    What common mistakes do you see T-SQL developers making?

    The question this week is based on a post by the talented Doug Lane, who wrote about the top three mistakes T-SQL developers make. Doug has a good list, and I’d urge you to read it, along with some sage advice from Brad McGeHee. However I’m sure many of you see different common issues in your own work.

    What things need to be fixed later? What code regularly causes performance issues? The more specific problems that you can share, along with their solutions, the more you might help another developer build better code in the future.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • ALTER SCHEMA TO ADD PERMISSIONS

    I’m sure some of you have wanted to do this:

    ALTER SCHEMA Steve AUTHORIZATION Steve

    You realize this doesn’t work, and you can’t grant the user Steve, rights to his schema after it’s created. You can do this:

    CREATE SCHEMA Steve Authorization Steve

    UPDATE: Someone pointed out this works after the fact:

    ALTER AUTHORIZATION ON SCHEMA::Steve TO Steve

    But not alter it. Strange and annoying. In my last post, I showed dropping and recreating the schema. That works well if you are beginning development, but not when you’re in the middle.

    Let’s make this less confusing and see how we actually allow a developer to access a schema to create procedures (or other objects) when the schema exists.

    First, let’s assume we want a developer, Steve, to be able to create procedures in the ETL schema. We have these conditions:

    • The ETL schema exists
    • The ETL schema is owned by another developer.
    • The login and user, Steve, exists in this database with no permissions.

    I want to now allow Steve to build the procedure ETL.MyProc.

    Grant Permissions

    The first thing I do is grant create procedure permissions to Steve.

    CREATE LOGIN steve WITH PASSWORD = ‘Test’;
    GO
    USE Sandbox
    GO
    CREATE USER Steve FOR LOGIN Steve
    GO
    GRANT CREATE PROCEDURE to Steve;

    GO

    With this done, now let’s set up our schema.

    CREATE SCHEMA ETL
    GO
    There are no default permissions, so the user Steve cannot create ETL.MyProc right now. How do we fix this?

    The trick here is that I need to allow Steve to ALTER the schema. I can do this by using this statement.

    GRANT ALTER ON SCHEMA::ETL TO Steve;
    GO

    I could do other things. I could grant CONTROL. to Steve instead, but I might not want to do that. That gives Steve the ability to actually drop the schema, which probably isn’t want. It’s certainly not the “least permissions” to let the developer create objects in a schema.

  • The Worst Comments

    I was watching a presentation recently on refactoring C# code and was amazed by some of the comments that the speakers showed in the code. The example was a real application that had been obfuscated and simplified a bit for the talk. The comments, however, had only been changed when they might disclose a specific person or company. The speakers pointed out a few of those changes, but also noted that most of the comments were verbatim from the original code.

    Comments like “Dave changed this from the old way”  or “Bug 445: as per the operations group” were good examples of bad comments. These items don’t really help a developer understand the code. The comments in application code should be there to add to the code itself, helping someone understand a reason for the code, not an obscure reference or an obvious statement (“this code adds two balances together).

    With that in mind, I’m sure many of you have come across some comments in code that have evoked a wide range of emotions. I’m sure you’ve been frustrated, annoyed, or something else. Perhaps even from your own comments. With that in mind…

    What are the worst comments you have found in code?

    I hope you don’t have examples in your current application, but perhaps you do. Perhaps you have even committed your own code recently without really taking the time to accurately describe the change. Maybe you want to go look in your VCS and see what you’ve entered lately.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Altering a Column with NOT NULL

    A short piece, as I ran into the need recently to alter a column to NOT NULL status. I’ve rarely done this in the past, usually specifying NOT NULL when I create the table. Often in future changes, I’ve been wary of not allowing NULLs since I’ll always find an application, or worse, a business situation where there is no good value available. However that’s a separate discussion.

    Altering the Column

    Let’s say I have a column that is specified as NULL in a table, and I want to change that. I initially tried this:

    ALTER TABLE Tags ALTER COLUMN Status NOT NULL;

    However, I got a syntax error. For the life of me, I couldn’t understand why, so I looked up the syntax. If you look at the ALTER TABLE syntax, it shows that the ALTER COLUMN item needs the type included. While I am not changing the data type, to alter the column, I need to do:

    ALTER TABLE Tags ALTER COLUMN Status tinyint NOT NULL;

    Another inconsistency in SQL. We don’t provide the whole definition again, and here we need to provide the column definition, even when only changing one of the settings.