THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

  • Temporal Tables - Part 5 - Start and End Type Precision

    In this fifth (and maybe final until at least getting to the concurrency chapter prep) blog about temporal I wanted to briefly cover the precision of the ROW START and END times. You can use any of the datetime2 types for these values. The precision will let you choose how many changes would be seen by the user. Even with 6 digits of precision from datetime2(7), there is no guarantee that every change will be visible to the user via the temporal settings on a FROM clause, but it is generally much more likely than if you are using datetime2(0) as we will see.

    In this blog, I will use datetime2(0) to give the least possible precision possible to show what can occur. Note that (not unsurprisingly) you have to use the same precision for both ROW START and END times or you get the following error:

    Msg 13513, Level 16, State 1, Line 6
    SYSTEM_TIME period columns cannot have different datatype precision.

    I will use the same basic structures I have used in previous examples, so if you have created the tables, you will need to drop the table and the history table:

    ALTER TABLE Sales.SalesOrder
        SET (SYSTEM_VERSIONING = OFF);
    go
    DROP TABLE Sales.SalesOrder;
    DROP TABLE Sales.SalesOrderHistory;
    GO

    To generate some data, I will use SEQUENCE object that I will format to put out a hexedecimal value, which I will put into a default constraint so the repeating code will be easier to read.

    CREATE SEQUENCE Sales.SalesOrderData_SEQUENCE MINVALUE 1 START WITH 1;
    GO
    CREATE TABLE Sales.SalesOrder

        SalesOrderId int NOT NULL CONSTRAINT PKSalesOrder PRIMARY KEY,
                                  --default to a text hex value, so we can see changes...
        Data varchar(30) NOT NULL DEFAULT (SUBSTRING(CONVERT(varchar(20), cast(NEXT VALUE FOR Sales.SalesOrderData_SEQUENCE as varbinary(5)), 1),3,12)),
        ValidStartTime datetime2 (0) GENERATED ALWAYS AS ROW START,
        ValidEndTime datetime2 (0) GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME (ValidStartTime, ValidEndTime)

    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Sales.SalesOrderHistory)); --Another future thought, put in a different schema? I think not, but, it intrigues me nonetheless.

    Next let's create a sequence of history rows, starting with a simple insert, wait a second, then three sets of 5 inserts.

    --create a first row
    INSERT INTO Sales.SalesOrder(SalesOrderId, Data)
    VALUES (1, DEFAULT);

    WAITFOR DELAY '00:00:01';
    GO
    --update the table 5 times
    UPDATE  Sales.SalesOrder
    SET     Data = DEFAULT
    WHERE   SalesOrderId = 1;
    GO 5
    WAITFOR DELAY '00:00:01';
    GO
    --update the table 5 more times
    UPDATE  Sales.SalesOrder
    SET     Data = DEFAULT
    WHERE   SalesOrderId = 1;
    GO 5
    WAITFOR DELAY '00:00:01';
    GO
    --update the table 5 last times
    UPDATE  Sales.SalesOrder
    SET     Data = DEFAULT
    WHERE   SalesOrderId = 1;
    GO 5

    Now, checking out the data:

    SELECT  *
    FROM    Sales.SalesOrder
    WHERE   SalesOrderId = 1;
    SELECT  *
    FROM    Sales.SalesOrderHistory
    WHERE   SalesOrderId = 1;

    The final state of the row has the Data column = '0000000010', and you can see the complete progression from '0000000001' through '000000000F'.

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    1            0000000010                     2016-05-02 02:53:12         9999-12-31 23:59:59

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    1            0000000001                     2016-05-02 02:53:09         2016-05-02 02:53:10
    1            0000000002                     2016-05-02 02:53:10         2016-05-02 02:53:10
    1            0000000003                     2016-05-02 02:53:10         2016-05-02 02:53:10
    1            0000000004                     2016-05-02 02:53:10         2016-05-02 02:53:10
    1            0000000005                     2016-05-02 02:53:10         2016-05-02 02:53:10
    1            0000000006                     2016-05-02 02:53:10         2016-05-02 02:53:11
    1            0000000007                     2016-05-02 02:53:11         2016-05-02 02:53:11
    1            0000000008                     2016-05-02 02:53:11         2016-05-02 02:53:11
    1            0000000009                     2016-05-02 02:53:11         2016-05-02 02:53:11
    1            000000000A                     2016-05-02 02:53:11         2016-05-02 02:53:11
    1            000000000B                     2016-05-02 02:53:11         2016-05-02 02:53:12
    1            000000000C                     2016-05-02 02:53:12         2016-05-02 02:53:12
    1            000000000D                     2016-05-02 02:53:12         2016-05-02 02:53:12
    1            000000000E                     2016-05-02 02:53:12         2016-05-02 02:53:12
    1            000000000F                     2016-05-02 02:53:12         2016-05-02 02:53:12

    Starting at the first second that was recorded, the first thing you can see is the row where Data = '0000000001':

    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:09';

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    1            0000000001                     2016-05-02 02:53:09         2016-05-02 02:53:10


    But the next row you will see will not be '0000000002', it will actually be '0000000006'. Using fractional times will be truncated. Such as if we try '2016-05-02 02:53:09.9'.

    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:09.9';

    This returns the same thing that 2016-05-02 02:53:09 does:

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    1            0000000001                     2016-05-02 02:53:09         2016-05-02 02:53:10

    If you use the next second, you will get '0000000006':

    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:10'

    This returns:

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    1            0000000006                     2016-05-02 02:53:10         2016-05-02 02:53:11

    The only way you will see rows '0000000001' - '0000000005' is to query the history table. We can only see rows where ValidStartTime <> ValidEndTime. In the following query I will get all of the rows that you can see in the table using each second:

    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:09'
    UNION ALL
    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:10'
    UNION ALL
    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:11'
    UNION ALL
    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:12';

    Which returns:

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    1            0000000001                     2016-05-02 02:53:09         2016-05-02 02:53:10
    1            0000000006                     2016-05-02 02:53:10         2016-05-02 02:53:11
    1            000000000B                     2016-05-02 02:53:11         2016-05-02 02:53:12
    1            0000000010                     2016-05-02 02:53:12         9999-12-31 23:59:59

    Note that this is the same output you will see if you execute the following query that returns all data:

    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME CONTAINED IN ('1900-01-01','9999-12-31 23:59:59.9999999');

    Or

    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME ALL;

    So you will definitely want to set your precision to the level that you will have the most likelihood of seeing all changes in your data. Once multiple connections are making simultaneous changes, you wouldn't wnt to lose data that would be interesting to the user.

  • Just over a week until SQL Saturday Atlanta

    I am looking forward to being both a speaker and an attendee at this year's SQL Saturday in Atlanta (it is number 521… wow). Don't know what SQL Saturday is? (I doubt that if you are reading this blog, but if so, click here quickly my friend).

    My topic this year is concurrency:

    Let Me Finish... Isolating Write Operations

    OLTP databases can be constantly written to and reporting databases are written to at least periodically. In order to ensure consistent results, connections must be isolated from one another while executing, ideally with the lowest possible cost to concurrency.  How this isolation is handled is based on the isolation level, whether the classic lock based or the newer optimistic scheme of the in-memory OLTP engine is used, or even if both engines are enlisted in the same transaction. In this session we will look at examples of how SQL Server isolates reading and writing operations from other writing operations to explore how this may affect your application through error messages and performance hits.

    But beyond getting to teach about SQL for an hour, I look forward to a few things even more:

    1. Seeing so many friends - Many I don't even know their names, just their avatars on twitter. Many I have known for years from SQL events. And even my cousin's husband will be in attendance so I get to see them as well. So much time to hang out with friends from dinners, lunches, between sessions, etc.

    2. Sessions! - I personally go to these events to fill in gaps in my knowledge (and sometimes reinforcing my knowledge helps too!) Sessions like: Efficient Table Design by Alex Grinberg, Building Blocks of Cortana Intelligence Suite in Azure by Melissa Coates, Indexes from the Ground Level by Lindsay Clark, and definitely Vote or Die 2016: Which is Faster by Robert Verell (I saw it the first time, so I know the answers!).  And there is a Women In Tech lunch with Rie Irish that will definitely be interesting as well.  Of course, even if I say I am going to these sessions, if I make it to one or two it will be amazing. Too often I am distracted by some other session and end up learning something completely different.

    Will I see you there? (If not, maybe I will see you in Pensacola a few weeks later?)

  • Temporal Tables - Part 4 - Synchronizing changes across tables

    So way back in June of last year, when I started this series on Temporal Tables: Part 1 - Simple Single Table Example, Part 2 – Changing history; and even in Part 3 - Synchronizing Multiple Modifications; I only referenced one table. In this entry, I want to get down to what will actually be a common concern. I want my objects to be consistent, not just at the current point in time, but throughout all points in time.  I won't even try to mix this concern with changing history, but I imagine that it could be a major undertaking depending on the data you already have from any change log tables you have created, if you were not concerned with viewing data at previous points in time.

    In part 3, I looked at what happens when the same row, or two rows in the same table would behave. 2 tables will behave quite the same, and there is a compelling reason to be cognizant of the temporal timestamps when you are dealing with multiple tables and want to see all tables as they existed at a given point in time.

    If you were writing you own versioning, you might use a trigger and write the change. Each change you would write would have a timestamp as of the time the change was written. I won't cover the code (in this blog, but I will in the book) that you need for keeping history of changes here, but it is a widely used pattern. When a row changes, make a copy of the deleted rows in the trigger in a table that looks like the primary table, and set the time when the change was made.

    A problem with this, if you want to see how the database looks at any point in time, you would see the progression of changes over time, which could lead to inconsistent views of the data at any given point in time. So say you have a SalesOrder and SalesOrderLineItem table, and you have a ControlTotal on the SalesOrder that needs to match the sum of the line item values for the SalesOrder. There really is no way to enforce a relationship between columns in different tables as this because SQL Server does not have delayed constraints or triggers. I will use this (what could be deemed a denormalization, depending on how it is viewed in the requirements) scenario, but not all issues will be quite so obvious.

    While we must trust the client to get the math correct or reject it, if we are going to keep temporal versions of data, it is highly important that we make sure that the views across time are consistent (even down to the microsecond if we are using a very high granularity in our start and end time columns,) in order to make coding easier. By making sure that our modifications are packaged in transactions, we can do this.

    First, let's create our tables (and I will drop the SalesORder table we had previously and add the ControlTotal column. Note that you can't just drop a table with versioning on, you need to turn off versioning and drop both tables.):

    ALTER TABLE Sales.SalesOrder
        SET (SYSTEM_VERSIONING = OFF);
    go
    DROP TABLE Sales.SalesOrder;
    DROP TABLE Sales.SalesOrderHistory;
    GO

    Then I will recreate the table with a change from Data to ControlTotal, and then a LineItem table that has the line total, as well as foreign key constraint to the SalesOrder table:

    SET NOCOUNT ON;
    CREATE TABLE Sales.SalesOrder

        SalesOrderId int NOT NULL CONSTRAINT PKSalesOrder PRIMARY KEY,
        ControlTotal DECIMAL(10,2) NOT NULL,
        ValidStartTime datetime2 (7) GENERATED ALWAYS AS ROW START,
        ValidEndTime datetime2 (7) GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME (ValidStartTime, ValidEndTime)

    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Sales.SalesOrderHistory)); --Another future thought, put in a different schema? I think not, but, it intrigues me nonetheless.
    GO

    CREATE TABLE Sales.SalesOrderLineItem

        SalesOrderLineItemId INT NOT NULL CONSTRAINT PKSalesOrderLineItem PRIMARY KEY,
        SalesOrderId int NOT NULL CONSTRAINT FKSalesOrderLineItem$ContainsDetailsFor$SalesSalesOrder REFERENCES Sales.SalesOrder(SalesOrderId),
        LineItemNumber INT NOT NULL,
        LineItemTotal DECIMAL(10,2) NOT NULL,
        CONSTRAINT AKSalesORderLineItem UNIQUE (SalesOrderId, LineItemNumber),
        ValidStartTime datetime2 (7) GENERATED ALWAYS AS ROW START,
        ValidEndTime datetime2 (7) GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME (ValidStartTime, ValidEndTime)

    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Sales.SalesOrderLineItemHistory));  GO

    Now let's do a progression of data, leaving a two second gap between operations, slowing down time a little bit like can happen in reality.

    INSERT  INTO Sales.SalesOrder (SalesOrderId, ControlTotal)
    VALUES  (1, 100);

    WAITFOR DELAY '00:00:02';

    INSERT  INTO Sales.SalesOrderLineItem (SalesOrderLineItemId, SalesOrderId, LineItemNumber, LineItemTotal)
    VALUES  (1, 1, 1, 50);

    WAITFOR DELAY '00:00:02';

    INSERT  INTO Sales.SalesOrderLineItem (SalesOrderLineItemId, SalesOrderId, LineItemNumber, LineItemTotal)
    VALUES  (2, 1, 2, 50);

    Now let's take a look at the data, just after the insert:

    SELECT  *
    FROM    Sales.SalesOrder
    WHERE   SalesOrderId = 1;
    SELECT  *
    FROM    Sales.SalesOrderHistory
    WHERE   SalesOrderId = 1;

    SELECT  *
    FROM    Sales.SalesOrderLineItem
    WHERE   SalesOrderId = 1;
    SELECT  *
    FROM    Sales.SalesOrderLineItemHistory
    WHERE   SalesOrderId = 1;

    We have 3 rows in the base tables, starting at three different times, and no history yet:


    SalesOrderId ControlTotal                            ValidStartTime              ValidEndTime
    ------------ --------------------------------------- --------------------------- ---------------------------
    1            100.00                                  2016-05-01 20:36:51.1670200 9999-12-31 23:59:59.9999999

    SalesOrderId ControlTotal                            ValidStartTime              ValidEndTime
    ------------ --------------------------------------- --------------------------- ---------------------------

    SalesOrderLineItemId SalesOrderId LineItemNumber LineItemTotal                           ValidStartTime              ValidEndTime
    -------------------- ------------ -------------- --------------------------------------- --------------------------- ---------------------------
    1                    1            1              50.00                                   2016-05-01 20:36:53.2000286 9999-12-31 23:59:59.9999999
    2                    1            2              50.00                                   2016-05-01 20:36:55.2452606 9999-12-31 23:59:59.9999999

    SalesOrderLineItemId SalesOrderId LineItemNumber LineItemTotal                           ValidStartTime              ValidEndTime
    -------------------- ------------ -------------- --------------------------------------- --------------------------- ---------------------------

    Now, even before we have any version history, the timestamps start to matter. Looking at current data, no problem

    SELECT ControlTotal, LineItemTotal
    FROM   Sales.SalesOrder
                JOIN Sales.SalesOrderLineItem
                    ON SalesOrderLineItem.SalesOrderId = SalesOrder.SalesOrderId

     
    The results look just like you expect. But what if we are doing temporal queries on the table?

    ControlTotal                            LineItemTotal
    --------------------------------------- ---------------------------------------
    100.00                                  50.00
    100.00                                  50.00

    At the time the first operation:

    DECLARE @asOfTime datetime2(7) = '2016-05-01 20:36:52';

    SELECT ControlTotal, LineItemTotal
    FROM   Sales.SalesOrder FOR SYSTEM_TIME AS OF @asOfTime
                LEFT OUTER JOIN Sales.SalesOrderLineItem FOR SYSTEM_TIME  AS OF @asOfTime
                    ON SalesOrderLineItem.SalesOrderId = SalesOrder.SalesOrderId;

    The data looks wrong:

    ControlTotal                            LineItemTotal
    --------------------------------------- ---------------------------------------
    100.00                                  NULL

    And at 20:36:54:

    DECLARE @asOfTime datetime2(7) = '2016-05-01 20:36:54';

    SELECT ControlTotal, LineItemTotal
    FROM   Sales.SalesOrder FOR SYSTEM_TIME AS OF @asOfTime
                LEFT OUTER JOIN Sales.SalesOrderLineItem FOR SYSTEM_TIME  AS OF @asOfTime
                    ON SalesOrderLineItem.SalesOrderId = SalesOrder.SalesOrderId;

    Things are better, but not quite right:

    ControlTotal                            LineItemTotal
    --------------------------------------- ---------------------------------------
    100.00                                  50.00

    Naturally, the likelihood of coming up against such a condition in the case of one row over two seconds is pretty slim, But the more concurrent operations using temporal, the more likely that you get back some weird totals that you don't want. And even more of a concern is that users often need to fix some data that is broken when you have these inter-table dependencies. If you are fixing data, you may need to fix history as well now (fixing history is an interesting topic that I may discuss some day. The central question will be based on requirements. It really depends if you want to see the data through time, or the information through time. Information should be correct. Data is what it was.

    This time, for the second SalesOrder,  I will make sure that all of the data is inserted and updated in the same transaction to ensure that the view of the data remains consistent:

    BEGIN TRANSACTION;

    INSERT  INTO Sales.SalesOrder (SalesOrderId, ControlTotal)
    VALUES  (2, 100);

    WAITFOR DELAY '00:00:02';

    INSERT  INTO Sales.SalesOrderLineItem (SalesOrderLineItemId, SalesOrderId, LineItemNumber, LineItemTotal)
    VALUES  (3, 2, 1, 50);

    WAITFOR DELAY '00:00:02';

    INSERT  INTO Sales.SalesOrderLineItem (SalesOrderLineItemId, SalesOrderId, LineItemNumber, LineItemTotal)
    VALUES  (4, 2, 2, 50);

    COMMIT TRANSACTION

    Then checking the data:

    SELECT  *
    FROM    Sales.SalesOrder
    WHERE   SalesOrderId = 2;
    SELECT  *
    FROM    Sales.SalesOrderHistory
    WHERE   SalesOrderId = 2;

    SELECT  *
    FROM    Sales.SalesOrderLineItem
    WHERE   SalesOrderId = 2;
    SELECT  *
    FROM    Sales.SalesOrderLineItemHistory
    WHERE   SalesOrderId = 2;

     

    It is clear to see that no matter what the AS OF time used, you will not have the issue with illogical results, since all of the start and end times are the same to 7 decimal places:

    SalesOrderId ControlTotal                            ValidStartTime              ValidEndTime
    ------------ --------------------------------------- --------------------------- ---------------------------
    2            100.00                                  2016-05-01 20:48:30.0154948 9999-12-31 23:59:59.9999999

    SalesOrderId ControlTotal                            ValidStartTime              ValidEndTime
    ------------ --------------------------------------- --------------------------- ---------------------------

    SalesOrderLineItemId SalesOrderId LineItemNumber LineItemTotal                           ValidStartTime              ValidEndTime
    -------------------- ------------ -------------- --------------------------------------- --------------------------- ---------------------------
    3                    2            1              50.00                                   2016-05-01 20:48:30.0154948 9999-12-31 23:59:59.9999999
    4                    2            2              50.00                                   2016-05-01 20:48:30.0154948 9999-12-31 23:59:59.9999999

    SalesOrderLineItemId SalesOrderId LineItemNumber LineItemTotal                           ValidStartTime              ValidEndTime
    -------------------- ------------ -------------- --------------------------------------- --------------------------- ---------------------------

    Finally, let's update the line item 1 row to 25, and the total to be 75

    BEGIN TRANSACTION;

    UPDATE Sales.SalesOrder
    SET  ControlTotal = 75
    WHERE SalesOrderId = 2;

    UPDATE Sales.SalesOrderLineItem
    SET LineItemTotal = 25
    WHERE SalesOrderId = 2
    AND LineItemNumber = 1;

    COMMIT TRANSACTION;

    Looking at the data and history:

    SELECT  *
    FROM    Sales.SalesOrder
    WHERE   SalesOrderId = 2;
    SELECT  *
    FROM    Sales.SalesOrderHistory
    WHERE   SalesOrderId = 2;

    SELECT  *
    FROM    Sales.SalesOrderLineItem
    WHERE   SalesOrderId = 2;
    SELECT  *
    FROM    Sales.SalesOrderLineItemHistory
    WHERE   SalesOrderId = 2;


    We see that the SalesOrder and line time 1 start times match, but not the 4th one, as you would expect since we did not apply any modification statement to that row:

    SalesOrderId ControlTotal                            ValidStartTime              ValidEndTime
    ------------ --------------------------------------- --------------------------- ---------------------------
    2            75.00                                   2016-05-01 20:52:41.6210321 9999-12-31 23:59:59.9999999

    SalesOrderId ControlTotal                            ValidStartTime              ValidEndTime
    ------------ --------------------------------------- --------------------------- ---------------------------
    2            100.00                                  2016-05-01 20:48:30.0154948 2016-05-01 20:52:41.6210321

    SalesOrderLineItemId SalesOrderId LineItemNumber LineItemTotal                           ValidStartTime              ValidEndTime
    -------------------- ------------ -------------- --------------------------------------- --------------------------- ---------------------------
    3                    2            1              25.00                                  2016-05-01 20:52:41.6210321 9999-12-31 23:59:59.9999999
    4                    2            2              50.00                                   2016-05-01 20:48:30.0154948 9999-12-31 23:59:59.9999999

    SalesOrderLineItemId SalesOrderId LineItemNumber LineItemTotal                           ValidStartTime              ValidEndTime
    -------------------- ------------ -------------- --------------------------------------- --------------------------- ---------------------------
    3                    2            1              50.00                                   2016-05-01 20:48:30.0154948 2016-05-01 20:52:41.6210321

    Now we can check the data as of a few times, and see that things are consistent:

    At the original time of insert:

    DECLARE @asOfTime datetime2(7) = '2016-05-01 20:48:30.0154948';

    SELECT ControlTotal, LineItemTotal
    FROM   Sales.SalesOrder FOR SYSTEM_TIME AS OF @asOfTime
                LEFT OUTER JOIN Sales.SalesOrderLineItem FOR SYSTEM_TIME  AS OF @asOfTime
                    ON SalesOrderLineItem.SalesOrderId = SalesOrder.SalesOrderId
    WHERE SalesOrder.SalesOrderId = 2;

    Two rows returned, total matches line item totals:

    ControlTotal                            LineItemTotal
    --------------------------------------- ---------------------------------------
    100.00                                  50.00
    100.00                                  50.00

    At the time of the update:

    DECLARE @asOfTime datetime2(7) = '2016-05-01 20:52:41.6210321';

    SELECT ControlTotal, LineItemTotal
    FROM   Sales.SalesOrder FOR SYSTEM_TIME AS OF @asOfTime
                LEFT OUTER JOIN Sales.SalesOrderLineItem FOR SYSTEM_TIME  AS OF @asOfTime
                    ON SalesOrderLineItem.SalesOrderId = SalesOrder.SalesOrderId
    WHERE SalesOrder.SalesOrderId = 2;

    This returns:

    ControlTotal                            LineItemTotal
    --------------------------------------- ---------------------------------------
    75.00                                   25.00
    75.00                                   50.00

    I will leave it to you to try other times for yourself.

    One quick note, if I had updated SalesOrderLineItemId = 4, even to the same value, I would get version rows. Be really careful not to just update rows repeatedly if there is
    no change. You will want to do what you can to avoid it, or you could get this to occur:

    UPDATE Sales.SalesOrder
    SET    SalesOrderId = SalesOrderId
    WHERE  SalesOrderId = 2
    GO 10

    Beginning execution loop
    Batch execution completed 10 times.

    So now the row has been updated 10 times with no change to the data. The version history is now considerably larger:

    SELECT  *
    FROM    Sales.SalesOrderHistory
    WHERE   SalesOrderId = 2;

    SalesOrderId ControlTotal                            ValidStartTime              ValidEndTime
    ------------ --------------------------------------- --------------------------- ---------------------------
    2            100.00                                  2016-05-01 20:48:30.0154948 2016-05-01 20:52:41.6210321
    2            75.00                                   2016-05-01 20:52:41.6210321 2016-05-01 20:59:53.9903127
    2            75.00                                   2016-05-01 20:59:53.9903127 2016-05-01 20:59:54.0059626
    2            75.00                                   2016-05-01 20:59:54.0059626 2016-05-01 20:59:54.0215896
    2            75.00                                   2016-05-01 20:59:54.0215896 2016-05-01 20:59:54.0372406
    2            75.00                                   2016-05-01 20:59:54.0372406 2016-05-01 20:59:54.0372406
    2            75.00                                   2016-05-01 20:59:54.0372406 2016-05-01 20:59:54.0528342
    2            75.00                                   2016-05-01 20:59:54.0528342 2016-05-01 20:59:54.0528342
    2            75.00                                   2016-05-01 20:59:54.0528342 2016-05-01 20:59:54.0528342
    2            75.00                                   2016-05-01 20:59:54.0528342 2016-05-01 20:59:54.0528342
    2            75.00                                   2016-05-01 20:59:54.0528342 2016-05-01 20:59:54.0684602

    Very little change in the start times, but some never the less (you can see the start and end times do change a little bit over time.) This could be a horrible feature to turn on if you have such an interface
    (as most of us probably do) where if nothing has changed and the user can press save over and over, causing update after update. So definitely watch your history tables after you turn this feature on to a new table to make sure of what is occurring. 

  • Temporal Tables - Part 3 - Syncing Multiple Modifications

    Back last June, I started this series on temporal tables, and in my head, I had enough information to make an educated set of examples on how to use them. In the back of my mind though, I knew that I hadn't quite thought enough about the whole process, particularly when you have several rows (or as I will note in the next blog entry, tables) that you are going to work with as a unit.

    So in this blog, I want to look a the mechanics of how multiple operations in the same transaction behave when we are in a single table. Then in the next entry to the series, I will take it to the logical conclusion of how we manage things when we have an "object" (like a salesOrder and salesOrderLineItem) that need to be bundled together.

    This is executing in RC3:

    select @@version

    --------------------------------------------------------------------------------------------------------------------------
    Microsoft SQL Server 2016 (RC3) - 13.0.1400.361 (X64)
        Apr  9 2016 01:59:22
        Copyright (c) Microsoft Corporation
        Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

    First, create a table. Note that you don't have to use the name SysStartTime or SysEndTime for the time period columns, which is the typical name because they use is BOL, but they used a different name in the introductory example. So I used a name more like my personal naming standards. I also noticed that the datetime2 column was not a datetime2(7, but rather was a (2). Later in this series, I will try out the different granularities to show what meaning they have for our queries as well.

    CREATE SCHEMA Sales
    GO

    CREATE TABLE Sales.SalesOrder

        SalesOrderId int NOT NULL CONSTRAINT PKSalesOrder PRIMARY KEY,
        Data varchar(30) NOT NULL,    --just the piece of data I will be changing
        ValidStartTime datetime2 (7) GENERATED ALWAYS AS ROW START,
        ValidEndTime datetime2 (7) GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME (ValidStartTime, ValidEndTime)

    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Sales.SalesOrderHistory)); --Another future thought, put in a different schema? I think not, but, it intrigues me nonetheless.
    GO

    Scenario 1: Single row, in a transaction create a row, update it a few times

    Note: I won't demo rolling back, as it will be have exactly as expected.

    First, start a transaction, and insert some data.

    BEGIN TRANSACTION
    INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
    VALUES (1,'Original');

    Looking at the data, we see:
     
    SELECT *
    FROM    Sales.SalesOrder;
    SELECT *
    FROM   Sales.SalesOrderHistory;

    This returns:

    SalesOrderId Data                      ValidStartTime              ValidEndTime
    ------------ ------------------------- --------------------------- ---------------------------
    1            Original                  2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999

    SalesOrderId Data                      ValidStartTime              ValidEndTime
    ------------ ------------------------- --------------------------- ---------------------------

    No version history yet, naturally, just the data as we created it. Now, still in the transaction, update the row.

    --SELECT @@TRANCOUNT; --Check if you need to make sure!
    --WAITFOR DELAY '00:00:01'; -- Use a WAITFOR if you want to test this stuff in a single batch, or the times might always be the same.


    UPDATE Sales.SalesOrder
    SET    Data = 'First Change'
    WHERE  SalesOrderID = 1

    Then we check the data:

    SELECT *
    FROM   Sales.SalesOrder;
    SELECT *
    FROM   Sales.SalesOrderHistory;

    You can see a version has been created, but notice the start and end times are exactly the same:
     

    SalesOrderId Data                      ValidStartTime              ValidEndTime
    ------------ ------------------------- --------------------------- ---------------------------
    1            First Change              2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999

    SalesOrderId Data                      ValidStartTime              ValidEndTime
    ------------ ------------------------- --------------------------- ---------------------------
    1            Original                  2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225

    Also note that the start and end time on the base SalesOrder row has not changed either. Lets update the row again:
     
    UPDATE Sales.SalesOrder
    SET    Data = 'Second Change'
    WHERE  SalesOrderID = 1;

    SELECT *
    FROM   Sales.SalesOrder;
    SELECT *
    FROM   Sales.SalesOrderHistory;

    Still no changes to the timestamp. But we keep accumulating changes:

    SalesOrderId Data                      ValidStartTime              ValidEndTime
    ------------ ------------------------- --------------------------- ---------------------------
    1            Second Change             2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999

    SalesOrderId Data                      ValidStartTime              ValidEndTime
    ------------ ------------------------- --------------------------- ---------------------------
    1            Original                  2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225
    1            First Change              2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225

    Now we have completed what we are wanting, so we commit:

    COMMIT

    Checking the results, effectively, since the versions never really existed, you cannot see them using the syntax in the FROM clause as you can see using the AS OF time of the start timestamp, which would be the only time that even somewhat looks like it might return the history:

    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-04-30 22:08:48.1200225';

    This returns:

    SalesOrderId Data                      ValidStartTime              ValidEndTime
    ------------ ------------------------- --------------------------- ---------------------------
    1            Second Change             2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999
             
    You just see the data. This is as we expected, knowing something of how this stuff works from Part 1. (Yeah, I had to look it up too). AS OF uses:

    SysStartTime >= PassedValue > SysEndTime (where SysStartTime and SysEndTime corresponds to the names you chose)

    So the changed rows are never seen unless you query the history table.

    Scenario 2: Create two rows slightly apart. Want to make sure the start time is different for the rows. Start a transaction, update them both at different times.

    The thing here is that we want to see how you use the fact that the StartTime values are synchronized with the transaction. If you have more than one row that need to be treated as a group, ideally you modify them in a transaction already. If for no other reason than rolling back the previous operations in the last modification fails. Here though, we are starting to think temporally. If you don’t synchronize your timestamps, you are apt to get illogical results at times.  I will show this more in Part 4 when I have two tables with control values that need to match (denormalizations are another typical concern. If you expect column1 to match column2, and they are updated in two statements, the time gap in the temporal progression could give you weird results if you hit it just right.)

    First, let’s do this without a transaction

    INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
    VALUES (2,'Original');

    WAITFOR DELAY '00:00:01' --slowing down time makes showing concurrency problems easier so you can hit the gaps easier

    INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
    VALUES (3,'Original');

    and then, verify you are in a consistent state that you expect:

    SELECT *
    FROM   Sales.SalesOrder
    WHERE  SalesOrderId IN (2, 3);
    SELECT *
    FROM   Sales.SalesOrderHistory
    WHERE  SalesOrderId IN (2, 3);

    This returns:

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    2            Original                       2016-04-30 22:13:19.1927715 9999-12-31 23:59:59.9999999
    3            Original                       2016-04-30 22:13:20.2358806 9999-12-31 23:59:59.9999999

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------

    Note that the start time is bit over a second different. Now start a transaction, update both rows in a way that shows the time they were changed. Wait a moment between updates, either manually, or using waitfor, as I have, but this time use a transaction:

    BEGIN TRANSACTION;

    UPDATE Sales.SalesOrder
    SET    Data = SYSDATETIME()
    WHERE  SalesOrderId = 2;

    WAITFOR DELAY '00:00:02'

    UPDATE Sales.SalesOrder
    SET    Data = SYSDATETIME()
    WHERE  SalesOrderId = 3;

    Now, look at the state of the table as you can see it:

    SELECT *
    FROM   Sales.SalesOrder
    WHERE  SalesOrderId IN (2, 3);
    SELECT *
    FROM   Sales.SalesOrderHistory
    WHERE  SalesOrderId IN (2, 3);

    The start times are synchronized now for the two rows, so the view of 2 and 3 will be consistent for this change, if not the insert:

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    2            2016-04-30 17:17:16.0944986    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
    3            2016-04-30 17:17:18.0999866    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    2            Original                       2016-04-30 22:13:19.1927715 2016-04-30 22:17:16.0944986
    3            Original                       2016-04-30 22:13:20.2358806 2016-04-30 22:17:16.0944986

    The start time of row 2 is exactly the time you put into the Data column for row 2 (the first row) for both rows. That would effectively be the time the transaction started.

    Add one more row, with the SYSDATETIME() value for the Data column, which will let you see when the row was created:

    INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
    VALUES (4, SYSDATETIME());

    SELECT *
    FROM   Sales.SalesOrder
    WHERE  SalesOrderId IN (2, 3, 4);
    SELECT *
    FROM   Sales.SalesOrderHistory
    WHERE  SalesOrderId IN (2, 3, 4);

    The new row has the same ValidStartTime value as the other modified rows.

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    2            2016-04-30 17:17:16.0944986    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
    3            2016-04-30 17:17:18.0999866    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
    4            2016-04-30 17:18:17.5493927    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    2            Original                       2016-04-30 22:13:19.1927715 2016-04-30 22:17:16.0944986
    3            Original                       2016-04-30 22:13:20.2358806 2016-04-30 22:17:16.0944986

    Commit the changes:

    COMMIT

    This is actually really great, because you can effectively update, modify, and delete all rows at the same time as far as the temporal history will reflect notice. I will use this in the next blog entry to deal with logical object level changes (SalesOrder and SalesOrderLineItems).

    Scenario 3: Single row, created, updated a few times, deleted.

    To show the final thing that will occur. I will, in a transaction, make a new row, change it and toss it aside. You would think the row never really existed, and you would wrong really.  It would have log entries, it might even have been used to create other data. As such, it will still have history, which could be useful in some scenario I haven't yet figured out! (If you roll back the transaction, it really would have never existed).

    BEGIN TRANSACTION

    INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
    VALUES (5, 'Original');

    UPDATE Sales.SalesOrder
    SET    Data = 'First Change'
    WHERE  SalesOrderID = 5

    UPDATE Sales.SalesOrder
    SET    Data = 'Second Change'
    WHERE  SalesOrderID = 5;

    DELETE Sales.SalesOrder
    WHERE SalesOrderId = 5;

    COMMIT

    Look at the data

    SELECT *
    FROM   Sales.SalesOrder
    WHERE  SalesOrderId = 5;

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------

    But in the history:

    SELECT *
    FROM   Sales.SalesOrderHistory
    WHERE  SalesOrderId = 5;

    SalesOrderId Data                           ValidStartTime              ValidEndTime
    ------------ ------------------------------ --------------------------- ---------------------------
    5            Original                       2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619
    5            First Change                   2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619
    5            Second Change                  2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619

    Everything we did is in the results. But no data for SalesOrderId will show up in any query on the table, temporal or otherwise:

    SELECT *
    FROM   Sales.SalesOrder FOR SYSTEM_TIME CONTAINED IN ('1900-01-01','9999-12-31 23:59:59.9999999')
    WHERE  SalesOrderId = 5;

    Returns nothing.

    This is all quite interesting, and I hope that it helps you as a reader someday when you are wanting to try out some of these scenarios. In this blog I wanted to look as some esoteric situations for how temporal would work, leading up to how you might need to think of things when you are modifying logical objects of data, rather than just one independent row at a time.

  • Row Level Security-Part 3-A few more advanced scenarios

    In this final entry (for now!) in my series on Row Level Security (Part 1, Part 2) I am going to show a couple of slightly more complex scenarios. I will only scratch the surface of what you could do, but keep in mind that performance is really important to how you make use of Row Level Security. In a future blog, I will build a couple of really large tables and try out Dynamic Data Masking and Row Level Security, as I am sure many other bloggers will as well.

    I will do two main scenarios. First dealing with the scenario that you have one database principal context you are working with, and the second allowing you to set up a table that you specify that a user has rights to an individual row.

    Code for this third entry executed using:

    SELECT @@version

    ---------------------------------------
    Microsoft SQL Server 2016 (RC2) - 13.0.1300.275 (X64)   Mar 26 2016 03:43:12   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

    Single Login

    In this first scenario, you have an app that only does one login, but users have different roles that you want to seperate out. SQL Server 2016 has added a connection level function that allows you to set a context on the session that a system function can read. This is a practice I have used for many years with context_info, but it was a single value that other users might tramle on. Session_context gives you a set of name-value pairs that you can read in, allowing you to set variables for a connection.

    Basically, you can execute something like this on your connection:

    EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'BigHat';

    And then when you need the value, execute:

    SELECT SESSION_CONTEXT(N'securityGroup');

    You get the following back (truncated from the max 256 characters):

    ---------------
    BigHat

    Note: if you want to read more about the details of session_context, check out Aaron Bertrand's great blog here: https://www.mssqltips.com/sqlservertip/4094/phase-out-contextinfo-in-sql-server-2016-with-sessioncontext/

    Ok, so now we will drop the security policy, and recreate the function using the SESSION_CONTEXT instead of USER_NAME() as
    we did it in the previous blogs:

    DROP SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy;
    GO

    ALTER FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@ManagedByUser AS sysname)
        RETURNS TABLE
    WITH SCHEMABINDING
    AS
        RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
                WHERE @ManagedByUser = SESSION_CONTEXT(N'securityGroup') --If the ManagedByUser = the securityGroup
                   OR (SESSION_CONTEXT(N'securityGroup') = N'MedHat' and @ManagedByUser <> 'BigHat')  --if the securityGroup is MedHat, and the row isn't managed by BigHat
                   OR (SESSION_CONTEXT(N'securityGroup') = 'BigHat') --Or the user is the BigHat person, they can see everything ;
                   OR (USER_NAME() = 'dbo')); --dbo gets it all


    Compare to our earlier predicate function from part 1 (link)

        RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
                WHERE @ManagedByUser = USER_NAME() --If the ManagedByUser = the database username
                   OR (USER_NAME() = 'MedHat' and @ManagedByUser <> 'BigHat') --if the user is MedHat, and the row isn't managed by BigHat
                    OR (USER_NAME() IN ('BigHat','dbo'))); --give 'dbo' full rights;

    The only difference is that we are only using USER_NAME for the DBO user, and if you aren't a member of one of the security groups you will see nada.

    Now we recreate our security policy that lets people see data if they manage the row, and insert only to the security group they are set to.

    CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD FILTER PREDICATE [rowLevelSecurity].[ManagedByUser$SecurityPredicate]([ManagedByUser]) ON Demo.saleItem,
    ADD BLOCK PREDICATE [rowLevelSecurity].[ManagedByUser$SecurityPredicate]([ManagedByUser]) ON Demo.SaleItem AFTER INSERT
    WITH (STATE = ON, SCHEMABINDING = ON);

    Bear in mind that you should usually not let a user do the following in ad-hoc SQL, because there is no control over the value parameter that would make sense (other than perhaps limiting the group names. But any user could put anything in if they can execute the procedure (as I am about to do.)

    First using an undefined security group;

    EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'Undefined';
    SELECT * FROM Demo.SaleItem;


    Wait, what?

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat
    7           BigHat
    8           BigHat

    Ah yes, I am the dbo. Security testing is tricky! I will set context to SmallHat, one of the accounts we have been using.

    EXECUTE AS USER = 'SmallHat';

    Now try again!

    EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'Undefined';
    SELECT * FROM Demo.SaleItem;

    No rows are returned

    saleItemId  ManagedByUser
    ----------- ---------------

    Now let us try one of the roles we defined.

    EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'SmallHat';
    SELECT * FROM Demo.SaleItem;

    saleItemId  ManagedByUser
    ----------- ---------------
    5           SmallHat
    6           SmallHat

    The two rows expected. Next the last two.

    EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'MedHat';
    SELECT * FROM Demo.SaleItem;
    EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'BigHat';
    SELECT * FROM Demo.SaleItem;

    SaleItemId  ManagedByUser
    ----------- ---------------
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat
    7           BigHat
    8           BigHat

    What is important to note here is if you are doing this wih a single connetion, you don't necessarily have to disconnect, but it is imperative that the user executes the session context setting procedure before every execution.

    Don't forget to revert back to dbo so you can do the next configuration if desired:

    REVERT;

    (Slightly More) Complex (But Not As Complex as It Could Be) Security Configurations

    Realitically, you can do almost any mapping you want. In this example, I just want to demo using a table to hold an access control list, where in addition to the security we already have configured, I specifically say what my 2 users can see beyond what they are set up as managers, while leaving BigHat Open to seeing all data.

    To start with, I will create a table of primary keys for the SaleItem, along with the security context names (I don’t need to grant rights to the users to read from the table, just like I don’t have to grant rights to execute the function.)

    CREATE TABLE rowLevelSecurity.SaleItemOverride
    (
        SaleItemId int NOT NULL,
        ManagedByUser nvarchar(15) NOT NULL,
        CONSTRAINT PKSaleItem PRIMARY KEY (SaleItemId, ManagedByUser)
    );


    I am going back to using USER_NAME() instead of the session context, but the concept is very much the same.
    Now we create some security data. What I am going to do here is, in addition to idea that the user gets to see
    all items that they are the managing user for, now we are giving them access to additional rows.

    INSERT INTO rowLevelSecurity.SaleItemOverride
    VALUES ( 1, 'SmallHat'), (2,'SmallHat'),(8,'MedHat');

    Now we add in the SaleItemId into the parameters, so we can check the data against the data in the table we have
    just created.

    DROP SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy;
    GO

    ALTER  FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@saleItemId int, @ManagedByUser AS sysname)
        RETURNS TABLE
    WITH SCHEMABINDING
    AS
        RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
                WHERE @ManagedByUser = USER_NAME()
                   OR (USER_NAME() = N'MedHat' and @ManagedByUser <> 'BigHat')
                   OR USER_NAME() = 'BigHat'   
                  --adding on this predicate that says to check the user name to the managedbyUser column in the override table        
                   OR EXISTS ( SELECT 1
                                FROM   rowLevelSecurity.SaleItemOverride
                                WHERE (ManagedByUser = USER_NAME() --the user who is being given access to a row
                                       or USER_NAME() = N'MedHat') --or MedHat who has accesss to all that the small user has
                                  AND SaleItemId = @saleItemId)
                               );
    GO


    And now recreate the security policy, this time with two parameters for each function:

    CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
        ADD FILTER PREDICATE [rowLevelSecurity].[ManagedByUser$SecurityPredicate](SaleItemId, ManagedByUser) ON Demo.saleItem,
        ADD BLOCK PREDICATE [rowLevelSecurity].[ManagedByUser$SecurityPredicate](SaleItemId, ManagedByUser) ON Demo.SaleItem AFTER INSERT
        WITH (STATE = ON, SCHEMABINDING = ON);


    With this recreated, now let's try it out. First we will show the override rows, and then change to the SmallHat security principal and see what we get. First reviewing what is in the override table.

    SELECT *
    FROM   rowLevelSecurity.SaleItemOverride;

     
    This returns:

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           SmallHat
    2           SmallHat
    8           MedHat

    So for SmallHat, we should be able to see SalesItemId 1 and 2, no matter who the ManagedyUser is, along with any rows with ManagedByUser = SmallHat in the SaleItem table:

    EXECUTE AS USER = 'SmallHat';
    GO
    SELECT * FROM Demo.SaleItem;
    GO
    REVERT;


    Which we can.

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    5           SmallHat
    6           SmallHat

    Now let's see what happens when we do MedHat, who can see the rows of SmallHat too, even if they were given to them through our security table:

    EXECUTE AS USER = 'MedHat';
    GO
    SELECT * FROM Demo.SaleItem;
    GO
    REVERT;

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat
    8           BigHat

    And if you execute the BigHat example, you will see that they do still have complete access.

    Naturally, this is not the end of the possible scenarios you could implement with row level security. You will be able to create complex examples, commonly not by using users, but using the groups that users are members of, and/or possibly your complex org chart, which might be a hierarchy. Using a hierarchy is one example that Books Online suggests as a bad idea, but check on the concept of the Kimball Helper table as a way to have your hierarchy, but a flattened version of it as well that will make queries a lot faster (in my test examples from my hierarchies presentatation with over 1/2 million nodes, I have seen amazing performance even on a laptop level machine) if you can use a
    key lookup rather than some form of scan.

    I will be adding to the blog after I finish the book some deeper examples of hiearchies. For an example now of how Jamey Johnston (@statcowboy) has built a hierarchy to work with some data in the real world, check his blog where he details the examples from his 2016 presentation: http://blog.jameyjohnston.com/oil-gas-sql-server-security-demo/.

  • Presenting on Concurrency Three Times This Month…Though Not Concurrently

    Tomorrow night, March 15, I will be at the Hampton Roads SQL Server User Group’s March meeting, then at SQL Saturday Richmond on Saturday the 19th, then finally back at home on the 25th for my home user group in Nashville (which I haven’t seen much of this year) to present a presentation that I am pretty fond of after having done it once before in Nashville, and 20 times for myself.

    Here is the abstract:

    Let Me Finish... Isolating Write Operations

    OLTP databases can be constantly written to and reporting databases are written to at least periodically. In order to ensure consistent results, connections must be isolated from one another while executing, ideally with the lowest possible cost to concurrency.  How this isolation is handled is based on the isolation level, whether the classic lock based or the newer optimistic scheme of the in-memory OLTP engine is used, or even if both engines are enlisted in the same transaction. In this session we will look at examples of how SQL Server isolates reading and writing operations from other writing operations to explore how this may affect your application through error messages and performance hits.

    What I like about it is that it takes the “fun” parts (okay, technical and nerdy parts) of my In-Memory DB Design session I had done several times last year (until SQL Server 2016 changed it beyond recognition) and makes it more applicable to more people. I cover the locking and optimistic concurrency controls that are in SQL Server 2016, and since RC0 has just arrived, I get to do it on what might be the release version of SQL Server.

    Hope to see you at one of these (and if not, I am scheduled to do this presentation again for Pensacola’s SQL Saturday in June).

  • Row Level Security–Part 2-Write Operations

    In the first entry in this series (to view, follow this link), I took a look at the how row level security worked with read operations. Now in this entry, I want to expand that to how you use it to limit what a user might write to, or delete from a table.

    To recap our current situation, we have three users:

    CREATE USER BigHat WITHOUT LOGIN;--DROP IF EXISTS works with users as well as objects:

    CREATE USER MedHat WITHOUT LOGIN; --MediumHat, which we want to get all of SmallHat's rights, but not BigHats

    CREATE USER SmallHat WITHOUT LOGIN; -- gets a minimal amount of security

    A VERY simple table:

    CREATE TABLE Demo.SaleItem
    (
        SaleItemId    int CONSTRAINT PKSaleIitem PRIMARY KEY,
        ManagedByUser sysname
    )

    With data that looks like this (the dbo can see all of the data).

    SELECT *
    FROM   Demo.SaleItem

    Which returns:

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat

    The following security policy has been implemented:

    CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
        ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem
        WITH (STATE = ON, SCHEMABINDING = ON);

    Based on the following TVF:

    CREATE FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@ManagedByUser AS sysname)
        RETURNS TABLE
    WITH SCHEMABINDING
    AS
        RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
                WHERE @ManagedByUser = USER_NAME()
                   OR (USER_NAME() = 'MedHat' and @ManagedByUser <> 'BigHat')
                   OR (USER_NAME() IN ('BigHat','dbo'))); --give 'dbo' full rights

    Now the goal is going to be to demonstrate how we can execute INSERTs, UPDATEs, and DELETEs on the table.

    First, I will grant the user rights to INSERT, UPDATE and DELETE from the table to all of the users we have set up:

    GRANT INSERT,UPDATE,DELETE ON Demo.SaleItem TO SmallHat, MedHat,BigHat;

    Before we start to look at the BLOCK predicates, let't take a look at what the user can do at this point, starting with an INSERT from the SmallHat user, with a ManagedByUser that we configured back in part 1 that they cannot see:

    EXECUTE AS USER = 'SmallHat';
    GO
    INSERT INTO Demo.SaleItem (saleItemId, ManagedByUser)
    VALUES (7,'BigHat');
    GO
    SELECT * FROM Demo.SaleItem;
    SELECT COUNT(*) FROM Demo.SaleItem WHERE saleItemId = 7
    GO
    REVERT

    This returns:

    saleItemId  ManagedByUser
    ----------- ---------------
    5           SmallHat
    6           SmallHat

    -----------
    0

    Which would, no doubt in my mind, drive the end user nuts thinking "Where did my row go?" So in just a bit, we will fix so it can't occur, if you don't want it to. In my contrived business rules, I will establish a case where we want to do just this, so that the user could update a row and it is no longer in view. (A real version might be to hide soft deleted rows from most users. ( For example a deletedFlag bit NOT NULL column with a value of 1, perhaps.) After RTM I will give that case a test when I test performance.)

    As the dbo:

    SELECT *
    FROM   Demo.SaleItem
    WHERE  saleItemId = 7;

    I can see it

    saleItemId  ManagedByUser
    ----------- ---------------
    7           BigHat

    Next up, can we UPDATE or DELETE the row as the SmallHat user? It seems fairly obvious we cannot since we can’t see it in a WHERE clause of a SELECT, but it never hurts to check:

    EXECUTE AS USER = 'SmallHat';
    GO
    UPDATE Demo.SaleItem
    SET    ManagedByUser = 'SmallHat'
    WHERE  SaleItemId = 7; --Give it back to me!

    DELETE Demo.SaleItem
    WHERE  SaleItemId = 7; --or just delete it
    GO
    REVERT;
    GO
    SELECT *
    FROM   Demo.SaleItem
    WHERE  SaleItemId = 7;

    If you haven't turn the NOCOUNT setting on for your connection, you will see:


    (0 row(s) affected)

    (0 row(s) affected)

    SaleItemId  ManagedByUser
    ----------- ---------------
    7           BigHat

    So the FILTER predicate we previously established works on UPDATES and DELETEs as well. Great. Now let's work on making sure that
    the user can't do something silly to the data they have in their view UNLESS it is an acceptable purpose.

    I will drop the exiting security policy for the time being to demonstrate how the block predicate works. We will put back the filter in the very last part of the blog to meet the requirement of letting the user modify data to a state they can’t see:

    DROP SECURITY POLICY IF EXISTS rowLevelSecurity.Demo_SaleItem_SecurityPolicy;

    Next we are going BLOCK predicate, that will block users from doing certain options. There are two block types: AFTER and BEFORE.

    • AFTER - If the row would not match your ability to see the data after the operation, it will fail. Here we have INSERT and UPDATE. So in my example scenario, for INSERT SmallHat would not be able to insert a row that didn't have 'SmallHat' for the ManagedByUser. For UPDATE (with no before setting), SmallHat could update any row they can see to 'SmallHat', but not something else.
    • BEFORE - This seem like it is the same thing as the filter predicate, saying that if you can't see the row, you can't UPDATE or DELETE it, but there is a subtle difference. This says, no matter if you can see the row, before you can modify the row, it must match the predicate. So in our case, if we added BEFORE update, and dropped the FILTER predicate, the SmallHat could see all rows, but only change the rows they manage.

    I am going to set one of the obvious (to me) set of row level security predicates that one might set in a realistic scenario for a managed by user type column.

    1. BLOCK AFTER INSERT, to say that if you can't see the row, that you can't create a new row.
    2. BLOCK UPDATE and DELETE you don't own.
    3. Allow you to update a row to a manager that you cannot see, to enable you to pass the row to a collegue. Naturally some level of "are you sure" protection needs to be placed on the row, because once you update it, it will be gone from your view

    So, using the security predicate function we already created, we apply the following:

    CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.saleItem AFTER INSERT,
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.saleItem BEFORE UPDATE,
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.saleItem BEFORE DELETE
        WITH (STATE = ON, SCHEMABINDING = ON);


    Now, let's try again add a row that SmallHat couldn't see:

    EXECUTE AS USER = 'SmallHat';
    GO
    INSERT INTO Demo.SaleItem (SaleItemId, ManagedByUser)
    VALUES (8,'BigHat');

    Nice try, it says, but:

    Msg 33504, Level 16, State 1, Line 171
    The attempted operation failed because the target object 'SimpleDemos.Demo.SaleItem' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
    The statement has been terminated.

    (Notice that the error message includes that database and schema of the object too. Nice!)

    Now try again, with SmallHat as the ManagedByUser column value:

    INSERT INTO Demo.SaleItem (SaleItemId, ManagedByUser)
    VALUES (8,'SmallHat');

    This works, and now to show that it worked (still in the security context of the SmallHat user:

    SELECT * FROM Demo.SaleItem

    This returns:

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat
    7           BigHat
    8           SmallHat

    And we see all rows in the table because we dropped the FILTER predicate.

    Next, continuing in the security context of the SmallHat user, let's try the UPDATE and DELETE we tried earlier to SaleItemId 7:

    UPDATE Demo.SaleItem
    SET    ManagedByUser = 'SmallHat'
    WHERE  SaleItemId = 7;
    GO
    DELETE FROM Demo.SaleItem WHERE SaleItemId = 7;

    Two errors that looks just like this:

    Msg 33504, Level 16, State 1, Line 211
    The attempted operation failed because the target object 'SimpleDemos.Demo.SaleItem' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
    The statement has been terminated.

    Msg 33504, Level 16, State 1, Line 211
    The attempted operation failed because the target object 'SimpleDemos.Demo.SaleItem' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
    The statement has been terminated.

    Showing we have stopped the user from modifying the rows, even though they can see them.

    Now, lets use the security hole we left. That of letting the user update the row by not checking that the value matched AFTER the UPDATE operation , in the following case changing the ManagedByUser column to another user.

    UPDATE Demo.SaleItem
    SET    ManagedByUser = 'BigHat'
    WHERE  SaleItemId = 8;

    SELECT * FROM Demo.SaleItem WHERE SaleItemId = 8;

    You can see that this was allowed

    SaleItemId  ManagedByUser
    ----------- ---------------
    8           BigHat

    Now, let's go back to the context of the dbo, and let's add the FILTER predicate back to the security policy, to show how you add
    a policy to one

    REVERT;

    We can add a predicate using the same syntax, without knowing the other items that are in the policy.

    ALTER SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
        ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem;
    GO


    Now we have the following policy defined:

    CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
        ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem,
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem AFTER INSERT,
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem BEFORE UPDATE,
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem BEFORE DELETE
        WITH (STATE = ON, SCHEMABINDING = ON);


    But what if we want to remove a predicate, in this example, say the redundant BEFORE predicates to the FILTER one we just added back.
    Note that there is no name to each predicate, so for example, to drop the different BEFORE BLOCK predicates on Demo.SaleItem:

    ALTER SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
        DROP BLOCK PREDICATE ON Demo.SaleItem BEFORE UPDATE,
        DROP BLOCK PREDICATE ON Demo.SaleItem BEFORE DELETE;


    I rather expect that in many cases it will be easier to drop and recreate the policy that is desired, but it is available to remove individual predicates. You can get away with only specifying the predicate type because as we mentioned in the previous entry in this series, you can only have one predicate of a given type on each table.

    In this blog, I wanted to cover the range of things one might do with one table and a fairly simple predicate function. In the next entry, I will get a bit more complex with the functions you can build to apply, including accessing other tables. I won't cover any more about the DDL of the CREATE SECURITY POLICY statement, just note that it does not have to center on one table. The following is certainly possible:

    CREATE SECURITY POLICY rowLevelSecurity.MultipleTables
        ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.Sale,
        ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem,
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.Sale AFTER INSERT,
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem BEFORE UPDATE,
        ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.UserName BEFORE DELETE
        WITH (STATE = ON, SCHEMABINDING = ON);


    I don't believe there to be any real advantage to doing it this way versus another other than convenience of turning policies off being easier (and I could see error troubleshooting to be more difficult.) I will update the blog if this turns out to not be correct.

  • How Sure Are You of Your PASS Summit Abstract?

    You know who you are. You have a presentation idea that has been percolating since last October. You have asked friends and coworkers if it is a good idea. You may have presented it at 10 SQL Saturdays to thunderous appreciation. You may have even tried to submit last year but was turned down.  You have this abstract written and may have even entered it on the Call for Speaker’s page and you think the idea is great, but you just aren't sure that the abstract is good enough. Who can you turn to?

    PASS has a service that can help you take the concern about the quality of your abstract out of the equation. It is called Abstract Coaching and it is completely free for any PASS member thinking about submitting for the Summit.

    You fill out the Microsoft Word form you can find here on the PASS website that has almost everything  that your Summit Submission will have:

    • Title - A name to tell the potential attendee what the session is about
    • Length – The length of your session. Whether you have a 75 minute General Session or are trying for a Full Day Pre-Conference session, we will review your abstract.
    • Level - How technical will the audience need to be to attend this season
    • Track - The major area of concentration for the session, such as DBA, developer, BI, etc.
    • Prerequisites * - Here you will list what you expect attendees to your session to already understand before attending. Every session needs these unless a person who wanders off the street could attend and understand what is going on.
    • Goals * - 3 big concepts you are trying to get across to the attendee to help the selection committee really understand what your plan is for the presentation
    • Abstract - A short paragraph or so that explains the session in enough detail that the selection committee and prospective attendees can understand what they will be getting. This along with the title will be the primary "advertisement" for the session.

    Note: Items with an * are for the selection committee only, and will not be shared with the attendees.

    The document will also list some basic instructions and restrictions. It might seem like a lot to fill out, but going through the exercise will help you flesh out your idea and give the commenters more understanding about what you are wanting to say. After filling out the form, simply email it in to the address included in the form and a few days later you will have an analysis of your submission.

    One thing you may notice is that your name and bio are not included in the coaching session document. The service is more or less anonymous, with the coaching team leader being the only person who will see your name connected to your submission. Your abstract will be sent anonymously to a commenter and returned the same way.  The entire team is under NDA about the details of the names and details of your abstract as well.

    There are a few restrictions to be aware of. The service offers no guarantees or promises of being selected, just one honest opinion of your abstract in terms of:

    • Grammar - Misspellings and other poor writing skills have tanked more great submission concepts than speaking skills ever will.
    • Possible Technical Concerns - Your abstract will be reviewed by someone who is a good writer, but also is a speaker and leader in technology as well.
    • Cohesion of the Submission Parts - Here we are looking at how well all of the parts of your abstract fit together. Quite often, a submission will have a title like "How to Do X" but will not even mention what X is in the goals or abstract leading to great confusion about what the presentation would be about.
    • Pretty much anything else the reviewer wants to share - Commenters will do their best to give you any comments about anything that is not out of bounds (mostly things like how likely you are to get chosen). 

    In the end, the Coaching team’s goal is to help you as much as possible, so you can worry about what most database professionals worry about most: the concept, the demos, and not oversleeping on the day of your presentation.

    Helpful links:

  • Row Level Security – Part 1–Simple Read operations

    This is part 1 of my Row Level Security blogs for my upcoming book project.

    Row Level Security is a very interesting feature. Unlike Dynamic Data Masking (link), which looks interesting on it's face, once you dig in the utilization of the feature looks to be limited by how granular you can make the security work. Row Level Security on the other hand is very configurable, and looks mainly to be limited by the possible performance limitations (and there may be a method or two to attack it, but I haven’t been able to make them work in CTP 3.2 yet)

    For the simple case, we will create a simple table valued user defined function (TVF) that returns either a single row with 1, or no rows. 1 means to show the row, no row the opposite. The parameters will be mapped to 1 or more columns as input. This means that for every row that is to be output to the user from any queries, this function will be executed. If this sounds like something that is generally considered a bad idea, it is. But to be fair, 1: this is a simple TVF, so it is more or less like a subquery and 2: other methods of implementing row level security suffer from common issues.

    In the next (at least) three blogs, I will cover row level security in some detail, with examples of several ways it can be used. Later in my experimentation with features for the book, I will attempt to use the feature with a very large dataset for performance testing, but I want to wait until after RTM for that exercise. (Or let's be real, Aaron Bertrand or another of my MVP friends will probably have beaten me to the subject, which will be easier for me anyhow!)

    The following is the version of SQL Server I used for this series of blogs until I state otherwise:

    select @@version

    Microsoft SQL Server 2016 (CTP3.2) - 13.0.900.73 (X64)
        Dec 10 2015 18:49:31
        Copyright (c) Microsoft Corporation
        Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

    To get started you will need to go to a SQL Server 2016 database (I am using the same one I created for the Dynamic Data Masking example) and we are going to drop and create 3 login-less users (in real scenarios, use roles), so we know they are starting fresh security wise:

    DROP USER IF EXISTS BigHat; --DROP IF EXISTS works with users as well as objects:
    CREATE USER BigHat WITHOUT LOGIN;

    DROP USER IF EXISTS MedHat --MediumHat, which we want to get all of SmallHat's rights, but not BigHats
    CREATE USER MedHat WITHOUT LOGIN;

    DROP USER IF EXISTS SmallHat -- gets a minimal amount of security
    CREATE USER SmallHat WITHOUT LOGIN;


    Next, the rowLevelSecurity function are best if placed in their own schema (for security purposes, so if you give a user access to a user schema, they won’t have access to this schema… This paradigm fails if you use db_datareader and db_datawriter, particularly as later we will be adding some tables to the schema I will be creating when we get to the more “creative” solutions.) I will make a very obviously named schema:

    CREATE SCHEMA rowLevelSecurity;

    Now we create a function that will be used to say, for a given row, should the user be able to see a row. This function will later be applied to one or more configuration of security policies.

    For the tables we will create for the demos, I will include a "ManagedByUser" column, which will hold the database user name of the user. This will be the parameter passed in to my function.

    The predicate for the function needs to determine:

    1. Bighat user sees all
    2. SmallHat user sees only rows where ManagedByUser = 'SmallHat' (we wll expand this in later blogs, but we are starting here
    3. MedHat sees rows where ManagedByUser = 'MedHat' or the username <> 'BigHat', allowing for other low rights user to be createdin the future

    So I will create the following function:

    CREATE FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@ManagedByUser AS sysname)
        RETURNS TABLE
    WITH SCHEMABINDING
    AS
        RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
                WHERE @ManagedByUser = USER_NAME() --If the ManagedByUser = the database username
                   OR (USER_NAME() = 'MedHat' and @ManagedByUser <> 'BigHat') --if the user is MedHat, and the row isn't managed by BigHat
                   OR (USER_NAME() = 'BigHat')); --Or the user is the BigHat person, they can see everything


    You don't need to give the user rights to the function, but in order to test the function, I will give the user access temporarily

    GRANT SELECT ON rowLevelSecurity.ManagedByUser$SecurityPredicate TO PUBLIC; --testing only

    Now, we can test the function in the security context of each user

    EXECUTE AS USER = 'smallHat';
    GO
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('BigHat');
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('MedHat');
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('SmallHat');
    GO
    REVERT;

    Returns Nothing, Nothing, 1; indicating that the user would be able to SmallHat rows only

    EXECUTE AS USER = 'medHat';
    GO
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('BigHat');
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('MedHat');
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('SmallHat');
    GO
    REVERT;

    Returns Nothing, 1, 1; indicating that the user would be able to SmallHat and MedHat rows, but not BigHat's

    EXECUTE AS USER = 'bigHat';
    GO
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('BigHat');
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('MedHat');
    SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate('SmallHat');
    GO
    REVERT;

    Returns 1, 1, 1; indicating that the user would be able to see all rows. So let's remove the rights on the function, and create our sample table

    REVOKE SELECT ON rowLevelSecurity.ManagedByUser$SecurityPredicate TO PUBLIC; --was for testing only

    Just a very simple table, and grant select rights to our three users:

    /* create the schema if you don't have it yet:
    CREATE SCHEMA Demo;
    */
    CREATE TABLE Demo.SaleItem
    (
        SaleItemId    int CONSTRAINT PKSaleIitem PRIMARY KEY,
        ManagedByUser nvarchar(15) --more typically would be sysname, but nvarchar(15) is easier to format for testing
    )
    INSERT INTO Demo.SaleItem
    VALUES (1,'BigHat'),(2,'BigHat'),(3,'MedHat'),(4,'MedHat'),(5,'SmallHat'),(6,'SmallHat');
    GO
    GRANT SELECT ON Demo.SaleItem TO SmallHat, MedHat, BigHat;

    At this point, each of these users can see every row in the table. We are going to change that quick.

    We are going to create a SECURITY POLICY object (note that it is schema owned), with one FILTER PREDICATE. This is used to filter read access to rows. In he next blog in the series, we will see the way to protect against writes and deletes to certain rows as well.

    --simple, data viewing filter
    CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
        ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem
        WITH (STATE = ON); --go ahead and make it apply

    Note that you can have other predicates on the same table that we will discuss in the next blog, as well as predicates on different table in the same security policy. Whether you want to do that will likely depend on what kind of control you want to be able to turn off a policy for some purpose, and how you manage your DDL.  If I discover there to be some particular value in either direction, I will update this blog.

    You can only have one enabled filter predicate per table. If you try to put another in the same policy you get either message, depending on if you try to create two in the same policy, or different policies:

    Msg 33262, Level 16, State 1, Line 146
    A FILTER predicate for the same operation has already been defined on table 'Demo.SaleItem' in the security policy 'rowLevelSecurity.Demo_SaleItem_SecurityPolicy'.

    Msg 33264, Level 16, State 1, Line 146
    The security policy 'rowLevelSecurity.Demo_SaleItem_SecurityPolicy2' cannot be enabled with a predicate on table 'Demo.SaleItem'. Table 'Demo.SaleItem' is already referenced by the enabled security policy 'rowLevelSecurity.Demo_SaleItem_SecurityPolicy'.

    Next, let's test our predicates by executing select statements on the table we have created in the context of our three users:

    EXECUTE AS USER = 'SmallHat';
    GO
    SELECT * FROM Demo.SaleItem;
    GO
    REVERT;

    Which returns:

    SaleItemId  ManagedByUser
    ----------- ---------------
    5           SmallHat
    6           SmallHat

    Which makes sense to our desired outcome based on the business rules. Next, for MedHat:

    EXECUTE AS USER = 'MedHat';
    GO
    SELECT * FROM Demo.SaleItem;
    GO
    REVERT;

    All of MedHat rows, and SmallHat:

    SaleItemId  ManagedByUser
    ----------- ---------------
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat

    And finally, the BigHat:

    EXECUTE AS USER = 'BigHat';
    GO
    SELECT * FROM Demo.SaleItem;
    GO
    REVERT;


    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat

    Awesome, now, let's compare this to the contents of the table as the dbo sees it:

    SELECT * FROM Demo.SaleItem;

    Returns:

    SaleItemId  ManagedByUser
    ----------- ---------------

    Well, that was less successful than I kind of expected. As the sa/dbo, I can't see any of the data. This runs counter-intuitive to the common practice/thinking that these users/admin roles are not subject to security. For MOST cases, the DBA will want to include the dbo user in the TVF that you base your functions on.  I will change this now for our demos:

    DROP SECURITY POLICY IF EXISTS rowLevelSecurity.Demo_SaleItem_SecurityPolicy; --if exists helps when debugging!
    go
    ALTER FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@ManagedByUser AS sysname)
        RETURNS TABLE
    WITH SCHEMABINDING
    AS
        RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
                WHERE @ManagedByUser = USER_NAME()
                   OR (USER_NAME() = 'MedHat' and @ManagedByUser <> 'BigHat')
                   OR (USER_NAME() IN ('BigHat','dbo'))); --give 'dbo' full rights
    GO
    CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem
    WITH (STATE = ON);


    NOW we compare our output with the output of the BigHat query:

    SELECT * FROM Demo.SaleItem;

    And we get the data happiness we expected:

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat

    For the final read operation demonstration, lets see how it works from a stored procedure.

    CREATE PROCEDURE Demo.SaleItem$select
    AS
        SET NOCOUNT ON; 
        SELECT USER_NAME(); --Show the userName so we can see the context
        SELECT * FROM  Demo.SaleItem;
    GO
    GRANT EXECUTE ON   Demo.SaleItem$select to SmallHat, MedHat, BigHat;


    Now execute the procedure as the different users (I am only going to include SmallHat to avoid being over repetitive, try it out for yourself)

    EXECUTE AS USER = 'SmallHat';
    GO
    EXEC Demo.SaleItem$select;
    GO
    REVERT;

    Which returns:

    -----------------------------
    SmallHat

    SaleItemId  ManagedByUser
    ----------- ---------------
    5           SmallHat
    6           SmallHat

    This shows us that the row level security works as expected without ownership chaining coming into place for the selection of rows, but it does come into play for running the TVF that determines which rows can be seen. In a later entry in this series, I will show how you can use a table in the function if you can't simply code the query to just use system functions.

    So how could you override it? Just like in the Dynamic Data Masking examples, in the procedure code, use WITH EXECUTE AS to elevate to a different users rights (and we will see some other possible solutions later as well in the third entry where I will show some methods of using values other than the simple system functions.)

    ALTER PROCEDURE Demo.SaleItem$select
    WITH EXECUTE AS 'BigHat' --use a similar user/role, and avoid dbo/owner if possible to avoid security holes.
    AS
        SET NOCOUNT ON;
        SELECT USER_NAME();
        SELECT * FROM Demo.SaleItem;

    Now execute this and note the output:

    EXECUTE AS USER = 'smallHat'
    go
    EXEC Demo.SaleItem$select
    GO
    REVERT

    Not only did you get the elevated rights of the user for objects they own, you now look like that user to the USER_NAME() function which is good for this example.

    ----------------------------------
    BigHat

    SaleItemId  ManagedByUser
    ----------- ---------------
    1           BigHat
    2           BigHat
    3           MedHat
    4           MedHat
    5           SmallHat
    6           SmallHat

    The downside here is that if you are using USER_NAME for any sort of logging purposes, this might be a bad thing. I would suggest that if you are using the user's context for logging stuff like RowLastModifiedByUser, to consider using ORIGINAL_LOGIN(), which will always return the server principal that the user attached to the server with.

    In the next entry, I will be covering the predicates whcih apply to the INSERT, UPDATE, and DELETE stements called BLOCK PREDICATES; which block write actions from occurring on the objects in the security
    policy.

    If you can’t wait and want to see some of where my material came from (along with blind experimenation!) Here are several links I found useful in writiing about this subject if you can’t wait until part 2 (and why should you?):

    Links:
    http://www.infoq.com/news/2015/06/SQL-Server-Row-Level-Security
    https://msdn.microsoft.com/en-us/library/dn765135.aspx
    https://www.mssqltips.com/sqlservertip/4094/phase-out-contextinfo-in-sql-server-2016-with-sessioncontext/
    https://www.mssqltips.com/sqlservertip/4004/sql-server-2016-row-level-security-introduction/

  • Target Audience - SQL Saturday 489 Tampa - “How to Design a Relational Database” Precon

    A few days back, I blogged an  intro to my pre-con on How to Design a Relational Database. You can see that blog here and you can (must?) go see the abstract and register: here.

    But who exactly is the target audience? I think there are really four reasonable answers, one answer that I feel says it all:

    Beginner Data Architects

    If you want to be a data architect, and have at least some SQL skills, you will probably not get so lost as to feel left out the entire day. I start at the beginning, introducing all of the concepts of a database, and we are going to do our work in groups so you will get some experience, if brief, in the database design process. I take this cue from my first Kimball University class on data warehousing, which I used as an inspiration to my first pre-con I did (which was pretty much the same as this one, plus or minus 20% from experience.) I was lost parts of the class since I was new to data warehousing at the time, but there was a nice mix of people in the class from beginner to near-expert, and the experience has paid many dividends throughout the years since, as I have done more and more data warehousing design and implementations along with my more natural relational database designs.

    Intermediate Data Architects

    While beginners may not have seen some of the stuff we are doing, intermediate data architects have probably done some of this stuff already. When I was at this point in my career, my goal was to make sure I was doing things the right way (sometimes I was, sometimes I wasn’t!). You are often mentored by people who have all sorts of ideas about what is right, coupled with websites, databases that are shipped with products (I am looking at you AdventureWorks!), not to mention you may have taken a class or two in college on the subject. In this class, my goal is to provide practical discussion about database design that can help clarify some of these things you have seen. That along with the ability to ask me questions, as well as discuss possible solutions to the teamwork assignment will hopefully clarify the way to design a database using some patterrns you may not have heard of, or to be honest, that you have heard are a bad idea but may not be.

    Since this is only a 7 hour class, we won’t have a ton of time to discuss your specific needs, but if you can fashion your questions in a specific, generic manner, we can discuss some ideas as a class.  If you have a design you want me to look at after the class, just send me some requirements and a design and I will be happy to discuss a bit more after the class is over. I want to make sure you get your money’s worth, and I usually learn something new from one or more of the attendees insane problems they have to deal with.

    Skilled Data Architects

    This is the most difficult group to mention, but here is what I endeavor to do for members of this group: I would enjoy attending this session myself even if I were not the one doing the talking. Worst case: you know everything, get to do some fun database design, and don’t have to work on a Friday.

    DBA/Developers Who Don’t Focus on SQL

    One group that I think needs a class like this as much as anyone are the people who use relational databases but don’t really focus on them for most of their work. This group tends to either write or support SQL, and need to know why a design should look a given way. “Why are there so many tables? Wouldn’t it be easier to just have one table that held all of the data?” Even if you won’t exactly apply these skills as your primary focus, it is good to understand the fundamentals, and participate in a design session or two. It is the reason I often go to reporting tool and programming sessions at conferences, to make sure I understand the other person’s job that I am affecting.

    Everyone (aka the one answer to rule them all)

    Finally, let’s be clear about one thing. If you can (or can get your boss to) fork out just shy of $160, I will promise you at least a tiny bit of entertainment, and the chance to meet a new friend or two. Worst case you didn’t have to work on a Friday. Best case, you learn how easy it is to design a great database, start a new career that lets you enjoy every day at the office. Worst case, you help offset the cost of my vacation to Disney World when I am finished with SQL Saturday. Either way, it is pretty much a no lose proposition for us both.

    Hopefully I will see several of my blog readers down in Tampa for this event on the 26th of February. I know I am pretty excited about it!

  • Dynamic Data Masking - Part 2–Security and Incorporating into Code

    This is part 2 of my Dynamic Data Masking blogs for my upcoming book project. Part 1 is here: Dynamic Data Masking - Part 1 - The Mechanism.

    Previously, in my first blog on Dynamic Data Masking - Part 1 - The Mechanisms, we had created the following table (I include this so you can see the DDL of creating the table with the MASKED WITH clause on the datatype. It interestingly comes before the NULL specification):

    CREATE TABLE Demo.Person(
        PersonId int NOT NULL,
        FirstName nvarchar(10) NULL,
        LastName nvarchar(10) NULL,
        PersonNumber varchar(10) MASKED WITH (FUNCTION = 'partial(1, "-------", 2)') NOT NULL,
        StatusCode varchar(10) MASKED WITH (FUNCTION = 'partial(0, "Unknown", 0)') NULL
            CONSTRAINT CHKPersonStatus CHECK  ((StatusCode='New' OR StatusCode='Inactive' OR StatusCode='Active'))
            CONSTRAINT DFLTPersonStatus  DEFAULT ('New') ,
        EmailAddress nvarchar(40) MASKED WITH (FUNCTION = 'email()') NULL,
        InceptionTime date MASKED WITH (FUNCTION = 'default()') NOT NULL,
        YachtCount tinyint MASKED WITH (FUNCTION = 'random(1, 100)') NOT NULL
             CONSTRAINT DFLTPersonYachtCount  DEFAULT ((0))
             CONSTRAINT CHKPersonYachtCount CHECK  ((YachtCount>=(0))),
    CONSTRAINT PKPerson PRIMARY KEY CLUSTERED
        (
            PersonId
        )
    );

    And we have two users (roles in a typical real setup!) that we want to give differing access to data:

        1. Those who can see all data (Test User: "BigHat"
        2. Those who can only see the first and last name of the person. Test User: ("SmallHat")

    CREATE USER BigHat without login;
    CREATE USER SmallHat without login;
    GRANT SELECT on Demo.Person to BigHat;
    GRANT SELECT on Demo.Person to SmallHat;

    As it stands, the data raw looks like this:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 0000000014   Active     fred@flintstone@slatequarry.net          1959-01-01    0
    2           Barney     Rubble     0000000032   Active     barneyrubble@aol.com                     1960-08-01    1
    3           Wilma      Flintstone 0000000102   Active     NULL                                     1959-01-01    1

    And for the 2 users that are not sa/dbo but have SELECT rights:

    EXECUTE AS User='SmallHat'
    go
    SELECT *
    FROM   Demo.Person;
    go
    REVERT;

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 0-------14   Unknown    fXXX@XXXX.com                            1900-01-01    23
    2           Barney     Rubble     0-------32   Unknown    bXXX@XXXX.com                            1900-01-01    15
    3           Wilma      Flintstone 0-------02   Unknown    NULL                                     1900-01-01    15

    So let's look at the different users. SmallHat user is fine as is. They were the target configuration, and the original masking goal has been met. Next, let's go for BigHat. The goal is to let BigHat see all of the data in this table. To do this, that is a privilege you can grant called UNMASK.

    First, let's check the data for BigHat:

    GRANT UNMASK ON Demo.Person TO BigHat;

    Executing this, you get:

    Msg 102, Level 15, State 1, Line 94
    Incorrect syntax near 'UNMASK'.

    Which is a weird error messagge because the syntax issue is with ON, but it turns out that UNMASK is a database privilege, not an object one. So, you can grant users rights to see ALL masked data, or ALL unmasked data by database.

    GRANT UNMASK TO BigHat;
    GO
    EXECUTE AS User='BigHat';
    go
    SELECT *
    FROM   Demo.Person;
    go
    REVERT;

    This returns:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 0000000014   Active     fred@flintstone@slatequarry.net          1959-01-01    0
    2           Barney     Rubble     0000000032   Active     barneyrubble@aol.com                     1960-08-01    1
    3           Wilma      Flintstone 0000000102   Active     NULL                                     1959-01-01    1

    But this opens up the user to ALL masked data in the database, not just one object or schema. So this does somewhat limit the value of the feature, since 1, it doesn't stop dbo users from seeing the data, even casually, and 2, it is all db or none.

    Hence, we really cannot do this for a multi-tier purpose at all, so if you have a user with rights between BigHat and SmallHat (call them MedHat, which we will use in the Row Level Security blogs coming next) that needs to mask some data, you may still end up using a view to do some or all of the masking. Column level security would allow us to limit access to some columns but column level security will make the output of different user's queries take on different shapes to work. Data Masking allows the shape of the output to stay the same so the UI and querying layers can be simple.

    While we are on security though, what about other uses in queries. First, what about a WHERE clause. If the data is masked, can you query for it?

    EXECUTE AS User='SmallHat';
    GO
    SELECT *
    FROM   Demo.Person
    WHERE  YachtCount = 0; 
    GO
    REVERT;

    Despite what the outputted masked yachtCount says.:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 0-------14   Unknown    fXXX@XXXX.com                            1900-01-01    80

    That is the row with 0, as we showed in the unmasked query.  Query for YachtCount = 1 and the output will be two rows. Searching for rows where PersonNumber = '0000000032', you will get the rows that match:

    EXECUTE AS User='SmallHat';
    go
    SELECT *
    FROM   Demo.Person
    WHERE PersonNumber = '0000000014';
    GO
    REVERT;

    Which returns:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 0-------14   Unknown    fXXX@XXXX.com                            1900-01-01    7

    The moral here is that need to be careful about how you use this feature. It is not as strict as column level security (or as Row Level Security will turn out to be, which is the next series of blogs to follow), so if a user has ad-hoc access to your db, they could figure out the data with some simple queries.

    And what if you aggregate? The next query would be a head scratcher to the average user, especially if they new that PersonNumber was supposed to be a unique value (which probably isn't you, reading this blog, unless you went to bed at 2AM and at 3AM a support call comes in):

    EXECUTE AS User='SmallHat';
    GO
    SELECT SUM(YachtCount) as YachtCount, MAX(PersonNumber) as MaxPersonNumber, 
           MIN(PersonNumber) as PersonNumber, COUNT(*) as MatchingRows
    FROM   Demo.Person
    WHERE  YachtCount > 0
      AND  PersonNumber <> 'xxxx';
    GO
    REVERT;

    YachtCount  MaxPersonNumber PersonNumber MatchingRows
    ----------- --------------- ------------ ------------
    0           xxxx            xxxx         2

    Note that for the aggregate output, it used the default default value for the masked values, but allowed YachtCount in the WHERE, so 2 rows were returned, though it seems like it shouldn't be.

    Next, what about if you cast the data:

    EXECUTE AS User='SmallHat';
    GO
    SELECT PersonId, FirstName, LastName, PersonNumber, cast(PersonNumber as varchar(10)) as CastedPersonNumber,
                                          YachtCount, cast(YachtCount as tinyint) as CastedYachtCount
    FROM     Demo.Person
    WHERE YachtCount = 1;
    GO
    REVERT;


    PersonId    FirstName  LastName   PersonNumber CastedPersonNumber YachtCount CastedYachtCount
    ----------- ---------- ---------- ------------ ------------------ ---------- ----------------
    2           Barney     Rubble     0-------32   xxxx               47         0
    3           Wilma      Flintstone 0-------02   xxxx               59         0

    I did NOT see that one coming... While you don't see the data, it is masked with the defaults, not the expected answer. In the case of YachtCount, it gives you a couple of doozies of answers that do not make sense with the WHERE clause.

    This is CTP3.2, so it may change... if not, these limitations are confusing at best. In earlier CTPs the data was unmasked by the cast, so this may just be another improvment to come. Be sure to test yourself, and I will try to update the blog after RTM as I am using this as material for the book.

    Next let's attempt an UPDATE.  We will need to give the user rights to modify the table:

    GRANT Insert, Update, Delete On Demo.Person to SmallHat;

    Next, let do an insert, update, and delete to the table as the SmallHat user:

    EXECUTE AS User='SmallHat';
    go

    --delete Wilma
    DELETE FROM demo.person WHERE PersonNumber = '0000000102';

    --add Betty
    INSERT INTO Demo.Person (PersonId,FirstName,LastName,PersonNumber, StatusCode, EmailAddress, InceptionTime,YachtCount)
    VALUES(4,'Betty','Rubble','0000000153','Active','betty.rubble@aol.com','8/1/1960',0);

    --update Fred's person number using only a masked column
    UPDATE Demo.Person
    SET    PersonNumber = '1111111114'
    WHERE  PersonNumber = '0000000014';

    SELECT *
    FROM   Demo.Person;
    GO

    REVERT;

    Looking at the output, you can see that everything worked (Fred's entry now starts with a 1, where it started with a 0 previously):

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 1-------14   Unknown    fXXX@XXXX.com                            1900-01-01    98
    2           Barney     Rubble     0-------32   Unknown    bXXX@XXXX.com                            1900-01-01    32
    4           Betty      Rubble     0-------53   Unknown    bXXX@XXXX.com                            1900-01-01    45

    So it doesn't stop any DDL or SELECT statment clauses other than the SELECT clause from working, even ORDER BY:

    EXECUTE AS User='SmallHat';
    GO
    SELECT *
    FROM   Demo.Person
    ORDER  BY YachtCount;
    GO
    REVERT;

    Which you can see is ordered by the YachtCount (if you look at the base data that is, the masked values are a bit confusing.)

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 1-------14   Unknown    fXXX@XXXX.com                            1900-01-01    43
    4           Betty      Rubble     0-------53   Unknown    bXXX@XXXX.com                            1900-01-01    65
    2           Barney     Rubble     0-------32   Unknown    bXXX@XXXX.com                            1900-01-01    18

    This was another thing I didn’t expect. The ORDER BY clause is (was in previous version?) the last to be processed, which allowed you to use aliases from the SELECT in the ORDER BY. But now, the output of the query seems to be being masked later in the process. Take for example, this substring or PersonNumber, first as dbo:

    select PersonId, substring(PersonNumber, 2,6) as PersonNumberSubstring
    from   Demo.Person

    PersonId    PersonNumberSubstring
    ----------- ---------------------
    1           111111
    2           000000
    4           000000

    Now, as SmallHat, after granting rights to look at the plan:

    GRANT SHOWPLAN TO SmallHat;
    EXECUTE AS User='SmallHat';
    GO
    SELECT PersonId, substring(PersonNumber, 2,6) as PersonNumberSubstring
    FROM    Demo.Person
    ORDER BY  PersonNumberSubstring;
    GO
    REVERT;

    The data is sorted correctly:

    PersonId    PersonNumberSubstring
    ----------- ---------------------
    2           xxxx
    4           xxxx
    1           xxxx

    But the output is masked, and the plan doesn’t show anything about masking:

    image

    Moving along, remember CTP, not RTM… Next, let's try a stored procedure and a view. So let's create a simple stored procedure using the same query as we have been using

    CREATE PROCEDURE Demo.Person$select
    AS
        SELECT PersonId, FirstName, LastName, PersonNumber, cast(PersonNumber as varchar(10)) as PersonNumber,
                                              YachtCount, cast(YachtCount as tinyint) as YachtCount
        FROM   Demo.Person
        WHERE  YachtCount = 1;
    go
    GRANT EXECUTE ON Demo.Person$select TO SmallHat;
    go
    --Plus, we revoke select on the person table, to show they can't query the data using their security:
    REVOKE SELECT ON Demo.Person To SmallHat;

    Now we run it as that user:

    EXECUTE AS User='SmallHat';
    GO
    EXEC Demo.Person$select;
    GO
    SELECT *
    FROM   Demo.Person;
    GO
    REVERT;

    I admit, I really didn't see this coming that the data would be masked.

    PersonId    FirstName  LastName   PersonNumber PersonNumber YachtCount YachtCount
    ----------- ---------- ---------- ------------ ------------ ---------- ----------
    2           Barney     Rubble     0-------32   xxxx         39         0

    And the output to show the user couldn't access the data directly:

    Msg 229, Level 14, State 5, Line 270
    The SELECT permission was denied on the object 'Person', database 'SimpleDemos', schema 'Demo'.

    I expected that due to security chaining that we would not see the data masked, since the user's rights to use the table doesn't come into play (especially since I revovked them). If you want to elevate the user's rights in the procedure you can use the WITH EXECUTE AS clause of the CREATE/ALTER PROCEDURE statement, such as:

    ALTER PROCEDURE Demo.Person$select
    WITH EXECUTE AS 'BigHat' --More typically this is AS OWNER, but using a more limited user is generally a better practice
    AS
        SELECT PersonId, FirstName, LastName, PersonNumber, cast(PersonNumber as varchar(10)) as PersonNumber,
                                              YachtCount, cast(YachtCount as tinyint) as YachtCount
        FROM   Person
        WHERE YachtCount = 1;


    and now you see the data as it exists in the table:

    EXECUTE AS User='SmallHat'
    GO
    EXEC Demo.Person$select;
    GO
    REVERT;

    PersonId    FirstName  LastName   PersonNumber PersonNumber YachtCount YachtCount
    ----------- ---------- ---------- ------------ ------------ ---------- ----------
    2           Barney     Rubble     0000000032   0000000032   1          1


    Next, let's use this table in a view. Since a view is a more lightweight container than a stored procedure that usually optimized as the code in the view at runtime, (and what happened with the stored procedure), I did expect this:

    CREATE VIEW Demo.Person_View
    AS
        SELECT PersonId, FirstName, LastName, PersonNumber,
               StatusCode, EmailAddress, InceptionTime, YachtCount
        FROM   Demo.Person;
    GO
    GRANT SELECT ON Demo.Person_View TO SmallHat;

    Then execute it:

    EXECUTE AS User='SmallHat';
    GO
    SELECT *
    FROM   Demo.Person_View;
    GO
    REVERT;

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 1-------14   Unknown    fXXX@XXXX.com                            1900-01-01    17
    2           Barney     Rubble     0-------32   Unknown    bXXX@XXXX.com                            1900-01-01    59
    4           Betty      Rubble     0-------53   Unknown    bXXX@XXXX.com                            1900-01-01    13

    Finally, for a level of completeness short of seeing how a masked object would work in a user defined function that was used in a DEFAULT constraint (something that I would really suggest against) I will change Person$select to use the view (and remove WITH EXECUTE AS...)

    ALTER PROCEDURE Demo.Person$select
    AS
        SELECT PersonId, FirstName, LastName, PersonNumber, cast(PersonNumber as varchar(10)) as PersonNumber,
                                              YachtCount, cast(YachtCount as tinyint) as YachtCount
        FROM   Demo.Person_View
        WHERE  YachtCount = 1;
    go
    REVOKE SELECT ON Demo.Person_View TO SmallHat;
    GO

    EXECUTE AS User='SmallHat';
    GO
    EXECUTE Person$select;
    GO
    REVERT;

    PersonId    FirstName  LastName   PersonNumber PersonNumber YachtCount YachtCount
    ----------- ---------- ---------- ------------ ------------ ---------- ----------
    2           Barney     Rubble     xxxx         xxxx         0          0
    3           Wilma      Flintstone xxxx         xxxx         0          0

    Still works as you would expect. In conclusion, on first attempt, Dynamic Data Masking is a limited, but possibly useful tool to have in some circumstances. I see its use cases generally limited to reporting type databases where there is some data that is generally not needed for most users, but is useful to have. The primary limitation is that you can access the data using a WHERE clause so it would certainly not be appropriate for data like salary amount where a user could build a rudimentary sorting algorithm to determine all of the salaries in your database.

  • SQL Saturday 489 Tampa-“How to Design a Relational Database” Precon

    On February 26, I will be doing my "How to Design a Relational Database" pre-conference session in Tampa, FL.  You can see the abstract here if you are interested, and you can sign up there too, naturally. Currently priced at 158.19 it is a bargain and I totally promise it will be around 8 hours of talking about and designing databases (including lunch and break time), which will certainly be better than what you do on a normal work day, even a Friday.

    You can read the abstract, but what should you really expect?  First off, expect to not sit in one spot while I drone on and on for 7 hours of lecture. Honestly preparing to talk for 7 hours straight would actually be easier to prepare for, but the best part of the class time is doing some database design. This means I need requirements that will touch on some of the more interesting design patterns and be achievable enough in around 45 minutes that we can all learn something from the practical experience of doing a design.

    The day is broken up into 3 or 4 modules (depending on where the class wants to go), with several participation exercises along the way. The sections are:

    Section 1: Introduction with a bit of history

    We start with just enough introduction to the materials, the stuff you need to do *before* you design, and introduction to the history of the craft to make sure we are all on the same page. A big part of this section is just getting it straight why we design like we do and establishing some of the terminology, and understanding the process of designing and testing a database.

    Section 2: Modeling and structures

    This section will cover the fundamental building blocks of relational databases, like tables, columns, keys, etc; and how to create a data model of the constructs. This is by far the largest part of the lecture, and by the end we should all be on the same page as to what goes into the database, if not exactly "how" the final product should look.

    We will stop at this point, and I will get out my modeling camera (which sounds a LOT more glamorous than it will turn out to be) and we will do some modeling on paper, eliciting attendees to provide the parts of a simple database, and we will all decide what should go into the model.

    The document that I base this model on is VERY simple, but the complexities of translating the document to a even a primative database design are always quite interesting to experience as a class, and I get an idea of who is going to be the outgoing class members at this point too.

    Section 3: Model Standardization/Normalization

    In this section, we will look at the kinds of things you need to do to the model to prepare the model to be implementable by truly analyzing the structures to see if they make "sense" within the confines of the relational model. It is always interesting to me that all models are normalized to some degree, but people think that normalizing makes things slower. In fact, normalization is generally to make business databases faster. The misconceptions about the higher normal forms are always confusing to me as well because the pre-concieved notions that people have are often really wrong. (I think too often the problem is that a properly built database does not lend itself to a simple UI…so you either get messy UIs or messy databases. It is really hard to get a team together that can do both and build the software to make messy business needs into beautiful, easy to use interfaces.

    Once we are done with the slide material in this section, we will start a couple of exercises. The first exercise is planned as a full class exercise, where I will man the data model (first on paper, then in a modeling tool), and elicit input from the class, in a manner that make sure everyone gets a say.

    Then we will break up into small teams and build a final model on paper, which I will bring up to the projector and we will discuss the different solutions.

    Section 4: Physical Modeling Overview

    Assuming we still have time/energy (and we don’t have discussion that lasts the rest of the day), we will take the last part of the class and cover turning the model into a "real" database. Data types, domain implementations, constraints, testing, etc. will be covered.

    Due to the limitations of the limited time format (in a perfect scenario we might have 3 or 4 days), and a *strong* preference of previous classes towards actually doing some design, there are topics we won't cover. But honestly, if you can get the basic design correct and make the model close to what final model ought to, the rest is kind of gravy (and well documented in a lot more websites and books than mine!) When I am building a database for a customer, I spend a lot more time figuring out the shape of the database objects than I do on the implementation aspects. Almost always you will find the problems with database designs come down to an inability to match the user’s requirements to yhr tables created. Missing an index here and there can be caught in testing and even after implementation (and that is why God created DBAs & Data Programmers).

    What I really love about doing all of the designs is that we really get the flavor of a real design meeting. A few differing opinions, a few ideas that I hadn't planned for, and a few argumentative types who really want their own way. But none of the arguments so far have gotten out of hand so far, and they have all been very much like the typical data modeling meeting.

    I hope to see you in class, and stay tuned to this blog entry for any more details as they come around. And don’t forget to register for the class: https://www.eventbrite.com/e/sql-saturday-precon-2016-how-to-design-a-relational-database-tickets-21099706791, and definitely don’t miss Saturday’s event http://www.sqlsaturday.com/489/eventhome.aspx. This is my first year to go to Tampa, so I am stoked to see how they do an event!

  • Dynamic Data Masking - Part 1 - The Mechanism

    This is part 1 of my Dynamic Data Masking blogs for my upcoming book project. Part 2 is here.

    An interesting feature that is being added to SQL Server 2016 is Dynamic Data Masking. What it does is, allow you to show a user a column, but instead of showing them the actual data, it masks it from their view. Like if you have a table that has email addresses, you might want to mask the data so most users can’t see the actual data when they are querying the data. It falls under the head of security features in Books Online (https://msdn.microsoft.com/en-us/library/mt130841.aspx), but as we will see, it doesn’t behave like classic security features, as you will be adding some code to the DDL of the table, and (as of this writing in CTP3.2, the ability to fine tune who can and cannot see unmasked data isn’t really there.)

    In a 2016 database, I will be executing my code. I am using a database named SimpleDemos, but any database will do.  The version I am using is 3.2, running on a Hyper-V VM on my laptop.:

    SELECT @@version;

    If you haven’t seen it yet, note that they have added a lot of stuff to @@version these days. Great if you are only using it for purposes like this.

    Microsoft SQL Server 2016 (CTP3.2) - 13.0.900.73 (X64)   Dec 10 2015 18:49:31   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

    Now, say we have the following simple table structure (note that I would probably use a domain table for StatusCode values in a real system, but I wanted this to be a single table.

    CREATE SCHEMA Demo;
    GO
    CREATE TABLE Demo.Person --warning, I am using very small column datatypes in this example to make formatting of the output easier
    (
        PersonId    int NOT NULL CONSTRAINT PKPerson PRIMARY KEY,
        FirstName    nvarchar(10) NULL,
        LastName    nvarchar(10) NULL,
        PersonNumber varchar(10) NOT NULL,
        StatusCode    varchar(10) CONSTRAINT DFLTPersonStatus DEFAULT ('New')
                                CONSTRAINT CHKPersonStatus CHECK (StatusCode in ('Active','Inactive','New')),
        EmailAddress nvarchar(40) NULL,
        InceptionTime date NOT NULL, --Time we first saw this person. Usually the row create time, but not always
        --a number that I didn't feel could insult anyone of any origin, ability, etc that I could put in this table
        YachtCount   tinyint NOT NULL CONSTRAINT DFLTPersonYachtCount DEFAULT (0)
                                CONSTRAINT CHKPersonYachtCount CHECK (YachtCount >= 0),
    );

    Into which I will be loading in some simple (silly) data:

    INSERT INTO Demo.Person (PersonId,FirstName,LastName,PersonNumber, StatusCode, EmailAddress, InceptionTime,YachtCount)
    VALUES(1,'Fred','Flintstone','0000000014','Active','fred@flintstone@slatequarry.net','1/1/1959',0),
          (2,'Barney','Rubble','0000000032','Active','barneyrubble@aol.com','8/1/1960',1),
          (3,'Wilma','Flintstone','0000000102','Active',NULL, '1/1/1959', 1);

    Next, we want to let anyone in the company see this data, and we have decided that row level security is too cumbersome, and
    (for argument's sake,) that a view wasn't the right method to use. (Note: I am not currently sure whether this method is better
    than a view, but I can see a few advantages, certainly for a reporting database).

    So we have the following rules:

    We have three users (which in real life, would be roles, but keeping it simple for the non-relevant details:
        1. Those who can see all data (Test User: "BigHat")
        2. Those who can only see the first and last name of the person. Test User: ("SmallHat")
       
    The goal is that we set up this system such that this scenario is handled. There are (currently) four different types of masks we can apply to data:

    • Default – Takes the default mask of the datatype (NOT the default of the column)
    • Email – Masks the email so you only see a few meaningful characters
    • Random – Puts a random number in place of an actual number (which is kind of weird, as we will see)
    • Partial – where you control what characters to keep and what to replace them with

    So let’s take a look at each:

    Default

    Datatypes have different default masks they will apply, for example: string types are X characters, integers are 0 (which as we will see is confusing.)  I will start by giving every column (other than the names) default masks:

    ALTER TABLE Demo.Person ALTER COLUMN PersonNumber
        ADD MASKED WITH (Function = 'default()');
    ALTER TABLE Demo.Person ALTER COLUMN StatusCode
        ADD MASKED WITH (Function = 'default()');
    ALTER TABLE Demo.Person ALTER COLUMN EmailAddress
        ADD MASKED WITH (Function = 'default()');
    ALTER TABLE Demo.Person ALTER COLUMN InceptionTime
        ADD MASKED WITH (Function = 'default()');
    ALTER TABLE Demo.Person ALTER COLUMN YachtCount
        ADD MASKED WITH (Function = 'default()');

    Note that you can do this in the CREATE statement if you wish as well. Now, to check to see what this looks like, execute:

    SELECT *
    FROM    Demo.Person;

    Which returns:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 0000000014   Active     fred@flintstone@slatequarry.net          1959-01-01    0
    2           Barney     Rubble     0000000032   Active     barneyrubble@aol.com                     1960-08-01    1
    3           Wilma      Flintstone 0000000102   Active     NULL                                     1959-01-01    1

    You are probably thinking, what a ripoff (and eventually you will be thinking, why isn’t this changing and it will drive you a bit crazy…) but as the dbo, you are not going to see the masking. So we need to create the users we initially set, and grant them rights:

    CREATE USER BigHat WITHOUT LOGIN;
    CREATE USER SmallHat WITHOUT LOGIN;

    We will get a bit deeper into security in the next entry in this series, but to start with we need to give our users the ability to select from the table.

    GRANT SELECT ON Demo.Person TO BigHat;
    GRANT SELECT ON Demo.Person TO SmallHat;

    Now we can run the following statements and see the effect:

    EXECUTE AS User='SmallHat';
    GO
    SELECT *
    FROM   Demo.Person;
    GO
    REVERT;

    Which now returns:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone xxxx         xxxx       xxxx                                     1900-01-01    0
    2           Barney     Rubble     xxxx         xxxx       xxxx                                     1900-01-01    0
    3           Wilma      Flintstone xxxx         xxxx       NULL                                     1900-01-01    0

    Note that the NULL value still shows up as NULL (meaning ‘UNKNOWN’) and the other values show up as ‘xxxx’, meaning ‘UNKNOWN, other than there IS a value’, which may be all that many users need to actually see. Everyone seems to have started in the database on 1900-01-01 (which may be an issue for some applications, but is at least an unreasonable value for most databases), and YachtCount is all 0s, which was actually the value for Fred, but not the others. Note that I included a default in the table DDL of ‘Unknown’ for StatusCode, but it was ignored for the ‘xxxx’ value, which is mildly disappointing. We will be able to set it to whatever value we want later, but it is nice when you can use the table’s structure to make the data easier.

    Email

    Next we have a simple mask for Email, so we remove the masking from the EmailAddress column, and add it back (You can currently change the masking without dropping the current masking, but I have seen it act weird… this is just CTP3.2).

    ALTER TABLE Demo.Person ALTER COLUMN EmailAddress DROP MASKED;

    ALTER TABLE Demo.Person ALTER COLUMN EmailAddress
        ADD MASKED WITH (Function = 'email()');

    Now query the data:

    EXECUTE AS User='SmallHat';
    go 
    SELECT * 
    FROM   Demo.Person;
    go 
    REVERT;

    And you can see the email is masked by showing the first character of the email address, then XXX@XXXX.com:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone xxxx         xxxx       fXXX@XXXX.com                            1900-01-01    0
    2           Barney     Rubble     xxxx         xxxx       bXXX@XXXX.com                            1900-01-01    0
    3           Wilma      Flintstone xxxx         xxxx       NULL                                     1900-01-01    0

    Looking at the base data, notice that Fred’s email address is ‘fred@flintstone@slatequarry.net’, so the only meaningful character you are giving the user is the first character.

    Random

    Random is a very interesting function, mostly because it would take a very specific use (that I can’t think of right now) to make it make sense. I added YachtCount to the model so I could demonstrate (and not offend anyone with a count of body parts, cars, etc. I figure the intersection of Yacht owners and people reading this blog to be low enough to risk it.)

    So, let's try the random(start, end) function, that will replace a numeric value with a value between two values:

    ALTER TABLE Demo.Person ALTER COLUMN YachtCount
        ADD MASKED WITH (Function = 'random(1,100)'); --make the value between 1 and 100. You could make it always the same value pretty easily by using the same value for start and end

    Now, check out the data:

    EXECUTE AS User='SmallHat';
    GO
    SELECT *
    FROM   Demo.Person;
    GO
    REVERT;

    Which will return something along these lines:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone xxxx         xxxx       fXXX@XXXX.com                            1900-01-01    65
    2           Barney     Rubble     xxxx         xxxx       bXXX@XXXX.com                            1900-01-01    92
    3           Wilma      Flintstone xxxx         xxxx       NULL                                     1900-01-01    64

    Not the most useful function, but it is there if you need it. If you are like me (pity), then you are wondering about numeric types (I know I was). So let’s create a quick table, named for the Blackadder fans in the crowd:

    CREATE TABLE demo.bob
    (
        value decimal(15,5) MASKED WITH (Function = 'random(-999999999,999999999)')
    );
    GRANT SELECT ON demo.bob to SmallHat;
    INSERT INTO demo.bob
    VALUES (1),(2),(3);
    GO

    EXECUTE AS User='SmallHat';
    GO
    SELECT *
    FROM   Demo.Bob;
    GO
    REVERT;

    Returns:

    value
    ---------------------------------------
    222199941.87482
    -137196271.70996
    -498355155.12580

    Partial

    The most configurable of the masking functions is partial. It lets you mask the characters you want. For example, our person number. If you want to keep 1 leading character, and 2 trailing, you make a mask such as:

    ALTER TABLE Demo.Person ALTER COLUMN PersonNumber
        ADD MASKED WITH (Function = 'partial(1,"-------",2)');
     ––note the double quotes on the text

    Looking at the data as user Smallhat,

    EXECUTE AS User='SmallHat';
    GO
    SELECT *
    FROM   Demo.Person;
    GO
    REVERT;

    you will see:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 0-------14   xxxx       fXXX@XXXX.com                            1900-01-01    99
    2           Barney     Rubble     0-------32   xxxx       bXXX@XXXX.com                            1900-01-01    6
    3           Wilma      Flintstone 0-------02   xxxx       NULL                                     1900-01-01    89

    The size of the dashes is controlled by you. If you said (1,”-“2), the first row would be 0-14. If you said (1,”-------------------“,14), the first row would be 0---------, as the returned value always fits in the original datatype.

    Finally, we want StatusCode to default to ‘Unknown’ when the value is masked. You can't pass parameters to default(), so you need to use partial to replace the entire value (slight dig on the name, pattern might have
    been better, but it is what it is.)

    ALTER TABLE Demo.Person ALTER COLUMN StatusCode
        ADD MASKED WITH (Function = 'partial(0,"Unknown",0)');

    Checking out the data:

    EXECUTE AS User='SmallHat';
    GO
    SELECT *
    FROM   Demo.Person;
    GO
    REVERT;
    GO

    You see that the data now says: ‘Unknown’:

    PersonId    FirstName  LastName   PersonNumber StatusCode EmailAddress                             InceptionTime YachtCount
    ----------- ---------- ---------- ------------ ---------- ---------------------------------------- ------------- ----------
    1           Fred       Flintstone 0-------14   Unknown    fXXX@XXXX.com                            1900-01-01    39
    2           Barney     Rubble     0-------32   Unknown    bXXX@XXXX.com                            1900-01-01    18
    3           Wilma      Flintstone 0-------02   Unknown    NULL                                     1900-01-01    12

    Summary, Blog 1

    Of course, whether that, or any of the methods we have seen here make sense is really a matter of design. If a value doesn’t look masked, that may signal a user that it isn’t masked. This particular blog is all about how the data masking feature works in doing the masking. In the next blog we will leave the data masked as it is, and we will look at what we can do via security. How do we let non-dbo users access the data, and what happens with WHERE clauses and UPDATE statements? What happens when the masked table is accessed through a view or stored procedure? See you real soon with answers to these and other questions.

    Continue reading to Part 2

  • Is It Ever Better to Not Name Constraints?

    This is part 1 of my Tips and Tricks blogs for my upcoming book project.

    For all of my professional writing career, I have been forced to be on the top of my formatting game when writing code. Uppercasing this, spacing this, aligning that, formatting code just right. In my personal life, I also try to format my code in a readable manner, though I do format code differently (I pretty much LOATHE uppercase characters), but I work really hard to make code readable so my coworkers don’t need maze traversal skills to read my code (or Red-Gate SQL Prompt to reformat).

    It has long been a habit that I name my constraints, and even if it wasn’t useful for database comparisons, it just helps me to see the database structure all that much eaiser. The fact that I as I get more experience writing SQL and about SQL, I have grown to habitually format my code a certain way makes it all the more interesting to me that I had never come across this scenario to not name constraints.

    Consider the following table structure:

    CREATE TABLE #testName
    (
        testNameId int CONSTRAINT PKTestName PRIMARY KEY
    )

    Create it on one connection, in the context of any database. Seems harmless enough, right? Executes, works just fine. Now, without dropping that table, go to another connection. Run the code again.

    Msg 2714, Level 16, State 5, Line 1
    There is already an object named 'PKTestName' in the database.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint or index. See previous errors.

    Well, doesn’t that just beat all. Looking in the system tables (I won’t lie, I had to go back and capitalize SELECT, FROM and WHERE to prove my earlier point abut formatting):

    SELECT name, object_id, type
    FROM   tempdb.sys.objects
    WHERE  name = 'PKTestName'

    You will see it right there in the tables:

    name           object_id   type
    -------------- ----------- ----
    PKTestName     -1450612125 PK

    If you want to check to see if the table exists only once, run the following query:

    SELECT name
    FROM   tempdb.sys.objects
    WHERE  name like '#testName%'

    You will get back something like:

    name                                                                                                                           
    --------------------------------------------------------------------------------------------------------------------------------
    #testName___________________________________________________________________________________________________________000000000009

    Now, changing the create table query to:

    CREATE TABLE #testName
    (
        testNameId int PRIMARY KEY
    )

    Re-execute the query on sys.objects for the temp table and you will see two rows with a root name of #testName.

    name
    --------------------------------------------------------------------------------------------------------------------------------
    #testName___________________________________________________________________________________________________________000000000009
    #testName___________________________________________________________________________________________________________00000000000B

    So the answer to the question of is it ever better to not name your constraints is “yes”, but only when your table will not outlive your connection. Clearly you don’t want to be stuck with names like: PK__#testNam__3EE579F50DAE3402 which is the delightful name that was assigned the primary key object the second time. The third time: PK__#testNam__3EE579F5088FC982, which is just plain ugly.

    Bonus points if you thought, can’t you just make the constraint name a temporary name by putting a # in front like so?

    CREATE TABLE #testName
    (
        testNameId int CONSTRAINT #PKTestName PRIMARY KEY
    )

    It doesn’t work, but it was a good idea.

    Msg 8166, Level 16, State 0, Line 1
    Constraint name '#PKTestName' not permitted. Constraint names cannot begin with a number sign (#).

  • Less than Two Weeks to SQL Saturday Nashville!

    No sooner does the official holiday season end than SQL Saturday season begins. Next week on January 9 is the Atlanta BI edition, but the week of the 16th is the event here in Nashville (well, technically we will be in Murfreesboro, but it is close!). Tamera Clark (@tameraclark) is again heading up the team that is making it happen, and I will be there again this year (sans walker, thankfully), and for the first year, I will be speaking (so I can’t use Nashville to fulfill #3 on my Pass Year’s Resolutions!)

    If you are interested in attending on Saturday or even one of the awesome looking Pre-Cons, head over to: http://www.sqlsaturday.com/480/eventhome.aspx and get registered immediately. It is sure to be a great time of nerdy fellowship and learning. Check out the schedule and if you are not new to the SQL Community you will see a lot of names you recognize and trust along with some you will hopefully come to know in the future

    My presentation is a new one (that I am finishing up writing in the next few days) on concurrency, and the many different ways you tune and handle concurrent operations that access the same data.  The abstract is:

    Let Me Finish... Isolating Write Operations

    OLTP databases can be constantly written to and reporting databases are written to at least periodically. In order to ensure consistent results, connections must be isolated from one another while executing, ideally with the lowest possible cost to concurrency. How this isolation is handled is based on the isolation level, whether the classic lock based or the newer optimistic scheme of the in-memory OLTP engine is used, or even if both engines are enlisted in the same transaction. In this session we will look at examples of how SQL Server isolates reading and writing operations from other writing operations to explore how this may affect your application through error messages and performance hits.

    I love giving giving presentations at SQL Saturdays, but it is never as much fun (or as scary) when presenting a new topic (if you can call concurrency “new”). Every night until the conference I will be editing, and presenting to myself working to get it just right with just enough material to fill an hour. I hope to see you there, and if I don’t see you, there are 11 other concurrent sessions going on at the same time…thankfully not in the same room

More Posts Next page »

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement