Voice of the DBA

Author: way0utwest

  • Clean Code is Easier to Read – SQL Prompt

    I saw a post recently that had query that looked like this:

    select a.*,name, b.*
 from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT'
and
b.grantee_principal_id = a.principal_id

     

    Ugly to read, at least to me, and in a poorly written format. The table, table format isn’t ANSI compliant and isn’t recommended. So I did this:

    formatsql

    A little better, and easier to read, but not great.

    SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a ,
        sys.database_permissions b
WHERE   permission_name = 'INSERT'
        AND b.grantee_principal_id = a.principal_id

    However now I can make a few quick edits. Remove the comma between tables and add “INNER JOIN” and then move the AND clause up to an ON clause to give me this:

    SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a
  INNER JOIN sys.database_permissions b
    ON b.grantee_principal_id = a.principal_id
WHERE   permission_name = 'INSERT'

    Much better, and easier to read.

  • Learn to Use Filegroups

    In SQL Server, filegroups are a management technique that I don’t see many people using. It’s amazing how many people ask questions about filegroups on the discussion forums, often unsure of how they fit into a well architected SQL Server. I have tended to use filegroups mostly as a space management technique, when I need to add more disks to my server, but they can be used in many more places.

    We continue to grow our data sizes all the time. While many databases are still measured in the single digits of gigabytes (or smaller), it is fairly common to find many database servers with over a terabyte of disk space. Our disks grow larger and larger, but it seems that data grows faster than disks, requiring larger storage subsystems all the time.

    While our storage grows larger, the tolerance for delays shrinks and demands for better performance increase. That means that data professionals need to be more cognizant of not only how their code is written, but also how they design storage. Tiering storage is one idea that I think has merit, but one that requires some planning.

    In SQL Server, we can’t split a table across filegroups. Or can we? We can partition a table (Enterprise Edition and higher), which can allow us to manage performance and storage appropriately. There is also the recommended practice of only having system objects in the primary partition and using separate filegroups for user data. That allows you to bring a partial database online, again, in Enterprise Edition only, while you restore different filegroups.

    This isn’t the first thing I would recommend you learn about SQL Server, but as you advance your knowledge, you should better understand when and how filegroups can help you. You will use them at some point and being comfortable with a filegroup restore is one of the skills that separates the accidental DBA from the data professional.

    Steve Jones

    The Voice of the DBA Podcasts

  • Patch Week

    My email account started getting notices of Windows patches yesterday, indicating it’s patch week again. If you manage Windows devices, be sure you are aware of the patches that came out. The May Bulletin on Technet shows 2 issues, one critical, one important.

    I tend to apply patches late, probably a month after release, just to see if anyone reports an issue. It’s up to you, but be aware of the patches, and if you could be affected, make sure you test machines and then schedule the downtime.

  • Networking and Connections

    This quote says it all:

    connections

    It’s from an article on the 9/11 memorial and how an algorithm was used to inscribe the names in particular places.

    That could easily be applied to your career as well. The connections you have, with people that know you, respect you, like your work, like you, etc. are the most important things you have.

    Your skills matter, but the skills help build those connections.

    Learn to network.

    Cross posted to The Modern Resume