Tis the Season

It is the season for TSQL Tuesday.  More importantly it is the season for giving and reflection.  And whether you celebrate Christmas or Chanukkah or Kawanzaa or TSQLTuesday because you believe or simply because of tradition, it is a good time to reflect and help somebody else.

Amidst the hustle and bustle, Allen White (Blog|Twitter) is hosting at least one party during this season.  And with all of our hustle and bustle, many of us will be attending at least one party this year (as evidenced by this post).

Allen’s party theme is an invitation to “Share Your Tricks.”  Well, in the spirit of the Holidays, I want to share some tricks and tips.  They can be my gifts to you during the holidays.  And maybe they can help you give to somebody else.

Tricks

ObjectProperty()

I find myself using this frequently in queries.  A popular use for myself is to use it to find the value of ‘IsMSShipped’.  But that is only one possible use for this function, there are many many more.  Check out MSDN to see more power!!

Here is a relatively meager example.

Code block

SELECT OBJECT_NAME(OBJECT_ID) AS TabName,create_date,type,type_desc
FROM sys.objects
WHERE OBJECTPROPERTY(OBJECT_ID,'isMSShipped') = 0
AND OBJECTPROPERTY(OBJECT_ID,'isTable') = 1

ServerProperty()

I like to use this function regularly as well.  Sometimes it is handy to break out information about the server into a columnar set.  I can find useful information from Service Pack level to whether the Instance is Clustered or not.  Here is a quick sample.

Code block

SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion
,SERVERPROPERTY('ProductLevel') AS SPLevel
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('ResourceVersion') AS ResourceDBVersion
,SERVERPROPERTY('InstanceName') AS InstanceName
,SERVERPROPERTY('IsClustered') AS Clustering
,SERVERPROPERTY('LicenseType') AS LicenseType
,SERVERPROPERTY('NumLicenses') AS NumLicenses
,SERVERPROPERTY('Collation') AS CollationLevel

Primes

Now for a little bit of fun stuff.  It was suggested on Twitter last night that I show a TSQL solution for generating prime numbers.  Thanks to Adam Mikolaj (Twitter) for this suggestion.  I am not going to explain it other than to say that this will help generate the prime numbers between 1 and 1000.

Code block

WITH Units ( nbr ) AS (
    SELECT Number AS nbr
FROM (VALUES (0),(1),(2), (3), (4), (5), (6), (7),(8), (9) )AS X(number))
, nums (Number) AS (
SELECT u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1
      FROM Units u1, Units u2, Units u3
     WHERE u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1 <= 1000)
 
SELECT 1 AS Primes
UNION All
SELECT 2
UNION All
SELECT n.Number AS Number
FROM nums c,nums n
WHERE c.Number < n.Number
        AND c.Number <> n.Number
        And c.Number between 2 and 35
    GROUP BY n.Number
    HAVING MIN(n.Number % c.Number) > 0
    ORDER BY 1

Tips

No Changes

Don’t plan any rollouts during the holiday season.  Try to have a production freeze implemented.  For many companies this is a busy season.  To further the point, many employees like to take a vacation during this time period.  With a lighter staff, key personal may be out of touch should an emergency occur due to a rollout.  So minimize your stress and minimize the chances of an emergency and don’t do any changes (excepting emergency fixes) during this time of year.  It is a good time to catch up on other items on your to-do list.

Take a Time-Out

Don’t be too busy for the important things in life.  Some of these things include self and family.  Don’t get yourself going too fast for too long that you miss out on the good stuff.  Don’t be soooo busy that you have no time for yourself to relax.  Recharge your batteries by taking a time-out.  Yes!  Give yourself a timeout.  Spend time with your spouse and kids.  If you don’t have a spouse or kids, then spend some time with a friend or #sqlfriend or #sqlfamily.

Help somebody in need

This is closely intertwined with the previous topic.  Give something of yourself to help somebody else.  A little joke, a little smile, a little service will go a long way (just as much for you as the other person).  If it means donating a toy, some food, some cash – do it.