Voice of the DBA

Tag: T-SQL

  • How Many Bytes Are In My Column? – T-SQL Functions

    One of the things you want to be aware of when writing T-SQL is using the proper function for a particular problem. Someone posted a question asking about why they were getting a 0 for this code:

    SELECT Mychar
    , '''' + mychar + ''''
   FROM dbo.MyTable

    That gave me these results

    mytable1

    I used the quotes in order to show that one of my columns has spaces trailing in one of the columns. I noticed that the poster was wondering why they had these results?

    SELECT Mychar
    , LEN(mychar)
   FROM dbo.MyTable

    mytable2

    In the table, clearly there are 4 characters for the row with “4D” and 5 characters for the next row. However the length is being returned as 0. If you were planning on testing for blank strings, or using some substring function, this could be an issue.

    The reason is simple. LEN, as noted in Books Online, ignores trailing spaces. The description of the function is: Returns the number of characters of the specified string expression, excluding trailing blanks.

    So if you have a space at the end of your string, or just a string of spaces, you don’t get the correct length. What should you use?

    Datalength – This function is designed to show the number of bytes used by the string, not the characters. Code shown below:

    SELECT 
   MyID
 , '''' + mychar + ''''
 , LEN(mychar)
 , DATALENGTH(mychar)
   FROM dbo.MyTable

    mytable3

     

    A good thing to be aware of if you are writing string test routines. LEN is the function I know most people use, but it is somewhat flawed, IMHO, in 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: