Voice of the DBA

Tag: T-SQL

  • Updating Extended Properties on a Table

    I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

    I wrote recently about adding an extended property to a table. As part of what I was testing, I also needed to update properties, changing values back and forth. It’s fairly easy to do so, and I wanted to document this for my own reference.

    The sp_updateexteendedproperty is analogous to the sp_addextendedproperty procedure. Here’s the code I used to change my property value on the table from the last post.

    EXEC sp_updateextendedproperty 
@name = N'PKException', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;

    As you can see, I pass in the same parameters. The procedure then changes the parameter in the table. A quick check in SSMS will show you the values changed. In my case, I was changing the value from 0 to 1 to test a query.

    The property does need to exist. If I execute this:

    EXEC sp_updateextendedproperty 
@name = N'PKcheck', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;

    I get an error thrown from the database engine.

    Msg 15217, Level 16, State 2, Procedure sp_updateextendedproperty, Line 112

    Property cannot be updated or deleted. Property ‘PKcheck’ does not exist for ‘dbo.SalesTax3’.

     

    This is a good way to handle this, as a TRY..CATCH can trap the error and do an insert instead of something else.

    SQLNewBlogger

    This was another side post from my testing of a solution. As I used this code to solve a problem, I kept a copy and made a few screenshots. This one was about 10 minutes in total.

    References

    sp_updateextendedproperty – https://msdn.microsoft.com/en-us/library/ms186885.aspx

    sys.extendedproperties – https://msdn.microsoft.com/en-us/library/ms177541(v=sql.90).aspx

  • Adding Extended Properties to a Table

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I had the need recently to get put an extended property on a table in a database. I could easily have done this in SSMS, and have used the GUI before, but since I wanted to make a number of changes for testing, I wanted this done programmatically.

    I knew there had to be an easy way to do this, and was hoping for an ALTER TABLE statement, but that’s not the way it works right now. There’s an sp_addextendedproperty procedure that you can use.

    This procedure is somewhat of a generic procedure that takes a number of parameters, which are used to specify where the extended property applies. There is a name and value of the property, essentially a key-value pair, and then there are 3 levels of properties you can specify.

    Each of the levels has a name and type as well, so this is almost like a hierarchical EAV table. It’s a bit of a mess, IMHO, but that’s OK. It’s nice to have the ability to use Extended Properties for objects, though I wish this were better implemented at different levels and embedded as a core part of your database. The levels are

    • Level0 – Should be used for database scope items. For our purposes, we will use SCHEMA as the type here.
    • Level1 – The next level and should be the type of object getting the property (table, view, procedure, etc.)
    • Level2 – The level that gives the part of the Level1 object, i.e. COLUMN, TRIGGER, etc.

    These will change, and there are some notes on BOL, so be careful and read this before you do much.

    This post looks only at adding a property to a table, so let’s do that.

    I want to add a property to note that a particular table doesn’t need a Primary Key (PK). To do that, I’m going to call my type [PKException] and use a value of 1 to indicate that no PK is expected on this table.

    My call for the procedure will be:

    EXEC sp_updateextendedproperty 
@name = N'PKException', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;

    In this case, I have a table called “SalesTax3” and it’s in the dbo schema. Those are my values for the Level0 and Level1 parameters. I can ignore the Level2 parameter since I am specifying this as a table level property.

    When I do this, I can then see the property in a few ways, but the easiest for most people is in the table properties, the Extended Properties tab,

    2015-05-26 09_55_09-Table Properties - SalesTax3

    That’s about it. If I want more properties, I can add them by changing the name and value of the property in the code above. I can also change the schema and table if I want this property added to other tables.

    SQLNewBlogger

    This was another side post from a separate post I was writing. I was working on solving a problem and needed an extended property. As I looked up the data to solve my issue and wrote code, I copied the Extended Property code and took a screenshot, leading to this side post.

    Once I had that, this was about 15 minutes to write. I’ll publish this one first, and refer to it in the post that solved my original problem.

    References

    sp_addextendedproperty – https://msdn.microsoft.com/en-us/library/ms180047.aspx

    sys.extendedproperties – https://msdn.microsoft.com/en-us/library/ms177541(v=sql.90).aspx

  • FORMATing Dates

    I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

    FORMAT is a function that was introduced in SQL Server 2012. It is designed to format dates and numbers as date/times. It was added to try and reduce the complexity and cumbersome nature of CONVERT and CAST.

    What I didn’t know, which I did like, is that the FORMAT command can be driven by language settings. For example, if I take a specific date, like today, I can reformat the date based on a language code.

    DECLARE @d DATETIME = GETDATE();
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'

    The results of this code are different, based on the cultural settings. Of course, the Chinese settings is really the only good way to show dates without confusion.

    dateresults

    This is interesting in that if you can get the regional settings for the client, you can easily return data in a format that makes sense to the user. Of course, we typically don’t want to do too much formatting on the server.

    The default language is that of the current session. For dumb uni-lingual people like me, this isn’t an issue, but it might matter for people that speak multiple languages and might move from system to system.

    It is interesting in that you can easily format the data from a date with FORMAT. For example, I change orders to match the Chinese format:

    DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'YYYY/MM/DD');

    That gets me “2015/07/20”.

    I can easily change to other formats, adding in times, or even partial times that might not make sense. For example, the result below named “Hours” has only the date. The “Date and hours” has the date and the hour only.

    DECLARE @d DATETIME = GETDATE();

SELECT  'Default' ,
        FORMAT(@d, 'd')
UNION
SELECT  'Full Date' ,
        FORMAT(@d, 'YYYY/MM/DD hh:mm:ss.ffff t zzz')
UNION
SELECT  'Date and hours' ,
        FORMAT(@d, 'YYYY/MM/DD hh')
UNION
SELECT  'Date and minutes' ,
        FORMAT(@d, 'YYYY/MM/DD mm')
UNION
SELECT  'Hours' ,
        FORMAT(@d, 'hh');



--      ,FORMAT ( @d, 'yymmdd', 'en-gb' ) AS 'CleanUS'

    The results are:

    Date and hours    2015/11/20 04

    Date and minutes  2015/11/20 11

    Default           5/20/2015

    Full Date         2015/11/20 04:11:28.9600 P -06:00

    Hours             04

    I think that there are lots of reporting queries where the formatting of dates would be handy and easier with FORMAT than CONVERT. It certainly is more intuitive to read than seeing something like “,110” in code.

    I still have the habit of using CAST and CONVERT when I’m changing types, and I’ll continue to do that. Especially as FORMAT is really limited to date types.  However when trying to get dates to render in proper formats, it’s a good choice.

    SQLNewBlogger

    This post came about while I was checking on another issue. I happened to run into the FORMAT command and hadn’t used it much, so I spent a few minutes messing around. This post came out of around 5 minutes of experimentation and 15-20 minutes of writing.

    References

    FORMAT – https://msdn.microsoft.com/en-us/library/hh213505.aspx

    Formatting Types in the .NET Framework – https://msdn.microsoft.com/library/26etazsy.aspx

  • Visualizing the Tally Table

    I was reading Dwain Camps’ article on Time Slots and thought it was a very interesting solution to a problem I’ve had a few times. Getting time slots inside of a period that I want to query. If you have a similar need, or want to learn more, I’d urge you to read the article.

    It’s always easier to join to a set of data that matches what you need than to try and filter out other rows. SQL excels at joins, so whenever possible you want to join to data. As such, when I was looking at Dwain’s code, I thought the way he listed the tally table was very interesting. I’ve seen plenty of these generated, but I hadn’t run across someone spelling it out in comments. In case you are wondering, Dwain had code like this:

    WITH Tally (n) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based
    -- Returns exactly 86400 rows (number of seconds in a day)
    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows 
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows
),                                                                          -- = 86,400 rows

    That’s a great visualization, and one I plan on using in the future. It makes it easy to see what’s being generated and at what scale.

    For example, I can easily do this:

    SELECT
        ROW_NUMBER() OVER ( ORDER BY ( SELECT
                                            NULL
                                     ) )
    FROM
        ( VALUES ( 0), ( 0) ) a ( n )                   -- 2 rows

    which returns a single column table with the values 1 and 2 in it. Two rows.

    Let’s say I want to now build a list of 12 rows. I could do this a few ways. One is to multiple 2 x 6 and get 12.

    SELECT
   ROW_NUMBER() OVER (ORDER BY ( SELECT NULL))
 FROM
   ( VALUES (0), (0) ) a(n)         -- 2 rows
   CROSS JOIN 
   ( VALUES (0), (0), (0), (0), (0), (0) ) b(n);  -- x 6
;

    Or I could give myself more flexibility to add and remove data with comments by doing factorials. How about 2 x 3 x 2 = 12?

    SELECT
   ROW_NUMBER() OVER (ORDER BY ( SELECT NULL))
 FROM
   ( VALUES (0), (0) ) a(n)         -- 2 rows
   CROSS JOIN 
   ( VALUES (0), (0), (0) ) b(n)  -- x 3
   CROSS JOIN 
   ( VALUES (0), (0) ) c(n)  -- x 2
;

    That gives me the same result: 12 rows. Of course, I can easily expand this quickly to thousands of rows.

    The technique isn’t anything new, but the visualization is interesting, and to me, this is much easier technique to see and understand when you run into it in code. Right away I know I’m generating xx rows and I can easily see how to grow or shrink the number of I have the need.