Tag: T-SQL

  • Implicit and Explicit Conversions

    Don't trust implicit conversions

    In a talk recently with some people I had someone note that the always chose to use explicit conversions on data types to prevent any unforeseen issues. That’s what I’d recommend as well. I have seen code in production function for years using implicit conversions, only to start failing when someone finally entered an invalid character in a row.

    How does that happen? Usually when someone is using character data types to store data that can be represented as character data,  even though the data must be dealt with in it’s native format. An example of this is storing a date as a varchar(10) or sticking numerical quantities in a character field to preserve formatting notations like dollar signs, or commas.

    That kind of code can work , pass a QA process, and live for years in a production system. However sooner or later someone will enter data that will break a query and return an error. Depending on your error handling system, this can be problematic to track down because it’s very data dependent. The code might work for some data sets but not for others.

    The best advice I can give is to store data in the proper data types whenever possible, and use explicit conversions when comparing data that might be of disparate types. Don’t always expect ’09/01/2001′ to compare to getdate(), and don’t expect ‘1’ to equal 1 in your code. At some point bad data will get into the system and those comparisons will error out.

    Steve Jones


    The Voice of the DBA Podcasts

  • SQL Server Truncate Table Permissions

    I saw a note recently where someone asked what permissions were needed for a user to execute TRUNCATE TABLE. In previous versions we needed ownership of the table or DBO level permissions. I had thought this was changed in SQL 2005 to require just the CONTROL permission.

    However when I checked the TRUNCATE Books Online page, I found this: The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

    Alter permissions is the minimum?!?!!?

    That sounded fishy, so I did this. First I created a new user, with no permissions other than public. My user was, appropriately, MyTestUser.

    Next I created a table and granted permissions:

    CREATE TABLE TRLC 
    (
      est_no varchar(10) default ' '
    , right_no int default 0
    )
    GO
    
    INSERT TRLC SELECT 'Test', 1
    
    GRANT CONTROL ON TRLC TO MyTestUser

    I then opened up another Query Window and changed the connection to use MyTestUser. This user only had CONTROL permissions and nothing else. A quick test showed that this user could indeed clear out the table. This:

    TRUNCATE TABLE dbo.TRLC

    executed without error.

    I think Books Online needs an update, and I’ll submit a note to that team to clarify this.

  • Common SQL Server Mistakes

    This presentation is designed to cover some of the basic mistakes that I find people making quite often when working with SQL Server. It is a mix of development and administrative items, designed to help beginners get a grounding in those skills that often cause the most problems in SQL Server.

    The talk is 75 minutes.

    Slide Decks:

  • Insert_Identity Permissions

    Despite my joking with Aaron Bertrand on Twitter that his suggestion on Connect got voted down because he’s Canadian, I think he has a great point with a hole in Books Online. He wrote a nice blog on the item, and it’s one you ought to consider voting up if you agree.

    I voted it up, and I think this is the type of half-*ssed documentation that is strewn throughout Books Online. While it’s a great document, and has a lot of information, it’s not always clear what is meant, and there are often holes. Most items have permissions listed, but not all, and even those that do are not clearly written or even correct.

    In my mind, the ability to change the identity values on a table ought to be either explicitly a permission that is given to users (GRANT IDENTITY_INSERT on xx to yy) or it ought to be included with the INSERT permission. There isn’t really anything here that’s altering a schema. It’s an insert permission for a user, that explicitly needs to put in a value, perhaps to close a sequence, or fix a failed insert. There’s no reason to require any elevated permission for this action.

    In any case, having the documentation clearer, even if it doesn’t behave as I’d expect it, is something I think is worth voting for.