Voice of the DBA

Tag: SQLNewBlogger

  • Get a comma separated list

    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 was working on a test of sorts and wanted to return multiple values as the output, but as a single variable. In other words, I couldn’t return a result set, I needed to return a string.

    I knew this was easy, and decided this would make a nice simple blog. Here we go.

    Let’s start with a simple table. Here’s one that has a few rows in it.

    CREATE TABLE MyTest
    ( id int);
    GO
    INSERT mytest values (1), (2), (3);
    go

    I want to return the values “1, 2, 3” as a string, in any order. Here’s how it works:

    DECLARE @i VARCHAR(MAX);
    SELECT @i = COALESCE(@i + ‘, ‘,”) + CAST( Id AS VARCHAR)
    FROM MyTest;
    SELECT @i;

    The COALESCE is important as the first time this runs, we have a NULL for the variable. In this case, we return an empty string. This is almost like the inverse of the operation that ends recursion. We add in the first row, and we end up with ‘1’ as the string.

    Note: it could be 2 or 3 in the string as I don’t have an ORDER BY. DO NOT depend on the order of insertion in the table. If you care about ordering, always include an ORDER BY.

    The next execution has a blank string (NOT NULL), so that is returned. In this case, we have ‘1’ + ‘, ‘ for the first part. The second part adds in the next row.

    This continues, and I get a nice set of output.

    2015-06-10 17_20_39-SQLQuery4.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQL Server

    SQLNewBlogger

    This one was short. It took me almost as much time to write the code and find a reference as it did to write the post. Five minutes.

    References

    This is basic T-SQL, but here’s another look at this.

  • Defining Foreign Keys at Table Create Time

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

    How many of you can define a foreign key when you create the table? Probably a few of you, but I bet most of you are like me and don’t necessarily know the syntax. I have often defined these later, which is fine. As long as they get defined.

    However I knew I needed a specific key when I was creating a table and couldn’t remember the syntax, so I had to search and learn how. I used Google and saw a few links from MSDN, but those tend to be overly documentation heavy. One of the links was to SQL Authority, run by Pinal Dave. He does a great job of simplifying things (and he’s a friend), so I followed that link. I could see the syntax and tested it in minutes.

    It’s easy to create a Primary Key in CREATE TABLE, and I wrote about that for one of my first SQLNewBlogger posts. The Foreign Key is similar, but not quite as simple.

    Imagine that I have a parent table:

    CREATE TABLE orders
  (
    orderid INT IDENTITY(1, 1) CONSTRAINT Orders_PK PRIMARY KEY ( orderid )
  , orderdate DATETIME
  , complete BIT
  );
GO

    I now want to create a child table and link the orderid in the child to the parent. I can do it like this:

    CREATE TABLE OrderLines
  (
    orderlineid INT
      IDENTITY(1, 1) CONSTRAINT OrderLines_PK PRIMARY KEY ( orderlineid )
  , orderid INT
      CONSTRAINT orderlines_order_fk FOREIGN KEY REFERENCES orders ( orderid )
  , qty INT
  );
GO

    Note that I define a constraint inline, just as I did for the parent. However I note this one is an FK and it "references" another table. In this case, I list the Orders table and put the columns in parenthesis.

    Quick, easy, build your FKs inline when you know about them in advance.

    SQLNewBlogger

    While trying to remember how to create an FK, I ran a search and chose the reference below to start. A matter of seconds had me seeing the syntax and writing the code.

    Putting this together was less than ten minutes.

    References

    Creating Primary Key and Foreign Key Constraints – http://blog.sqlauthority.com/2008/09/08/sql-server-%E2%80%93-2008-creating-primary-key-foreign-key-and-default-constraint/

  • 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