Tag Archives: TSQL

What you need to know about SQL Database Dynamic Data Masking before the GA

Posted on by

Please read this post after reading this, as some critical changes have gone to the DDM feature of SQL Databases in GA
SQL Database Dynamic Data Masking will be Generally Available (GA) very soon by the end of this month.

For more information about Dynamic Database Masking please read this article.

As per the article you can configure the dynamic data masking on columns which have sensitive data, and also we have the options to specify the SQL Logins which should be excluded from masking. Meaning any user connecting to the database using the specified SQL login will see the data without being masked.

  • SQL Logins will be removed from the exclusion list and you have to specify the SQL users or Azure AD users directly in the exclusion list.
  • SQL users with administrative privileges are always excluded from dynamic data masking

Example:

In a SQL Database server with a SQL Database named Db1 there are 3 SQL logins.

SQL Login

SQL user

Privilege
thuru Thuru Admin (sa) for the server
login1 User1 db_owner for Db1
login2 User2 db_owner for Db1

First thing to note is, after the GA of the feature you cannot user SQL login names in the exclusion list in the portal meaning that you cannot specify login1, login2 in the list. Instead of specifying the SQL login you should specify the SQL users as user1, user2.

And SQL users who have administrative privileges always see the data.

Look at the image below as I have specified only user1 in the exclusion list.

Login to the database as thuru and executing a SELECT statement results this.

As you see, though I haven’t specified the admin user in the exclusion list, still admins are automatically excluded from dynamic data masking. If you’re using admin users in your application and enabled dynamic data masking, you have to create other SQL logins to access the database.

Login to the database as user1 and user2 outputs the following results respectively.

How to create other SQL Logins in SQL Database

Login to the server using the admin user.

  1. Select the master database and create the logins using this command.

  2. Connect to the database you want to add the user to and add the user using the created SQL login in step 1

  3. Add roles to the user

Advertisement

Hierarchy in TSQL – step by step guide

Posted on by

Hierarchy data type is introduced in SQL Server 2008. It is a CLR data type. This post gives a simple step by step guide to implement HierarchyID data type

Hierarchical navigation or simply the navigation between nodes could happen in 2 ways.

  1. Depth First
  2. Breadth First

Having said that, we’ll start the tutorial. Create a table as follows. Node is the HierarchyID type, NodeLevel column is a calculated column which contains the level of the Node in the hierarchical tree. ID and Name are custom data type for the information we have. Let’s try to model a organizational reporting hierarchy in using the table h.

   1: create table h (

   2: Node HierarchyID primary key clustered,

   3: NodeLevel AS Node.GetLevel(),

   4: ID INT UNIQUE NOT NULL,

   5: Name VARCHAR(50) NOT NULL

   6: )

Since we’ve defined the Primary Key in the Node column, we get the Depth First index by default. To create a Breadth First index you can run the following query. In real scenarios you do not need to create both indexes, it depends on your requirement to decide which index to be created. Sometime you may need both as well.

   1: -- this creates the bfs index to the table.

   2: create unique index bfs_index

   3:  on h (NodeLevel,Node)

Now we’ll insert data.

   1: insert into h (Node, ID, Name)

   2:    values (HierarchyId::GetRoot(), 1, 'Thuru')

Here I’ve passed the HierarchyId::GetRoot() as the value for the Node. Few things to note here, we ask the SQL Server to give us the HierarchyID of the root node. We use the SQL CLR function GetRoot for this. The :: is marked because GetRoot is a static method inside the HierarchyID SQL CLR data type.

Mark this statement as our common SELECT statement. NodeText is the string representation of Node.

   1: select Node.ToString() AS NodeText, * FROM h

image

Now I insert a person in the next level. 

   1: declare @parent hierarchyid = hierarchyid::GetRoot()

   2: insert into h (Node,id,Name) values

   3:     (@parent.GetDescendant(null,null),2,'Johnny')

   4: go

Here I get the parent Node value using the static function GetRoot(). GetDescendant() method returns the descendant Node value between the right and left sibling. In this case our top level node doesn’t have any children, apparently no siblings so we do pass null. After executing the above query now we have this structure.

image

Let’s add another person below ‘Thuru’. Note this time I get the parent node using the SELECT statement which asks for the Node of ‘Thuru’. And now I want to insert the new Node in the level of Johnny and after him. So for the new node the right sibling is Johnny and the left sibling is null. The query goes like this.

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @jhony hierarchyid = (select Node from h where name = 'Johnny')

   3: insert into h (Node, ID, Name) values (@parent.GetDescendant(@jhony,null), 3, 'Robert')

image

 

image

Now it’s a simple guess for you what should be done to insert a new node between Johnny and Robert. 

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @jhony hierarchyid = (select Node from h where name = 'Johnny')

   3: declare @robert hierarchyid = (select Node from h where name = 'Robert')

   4: insert into h (Node, ID, Name) values (@parent.GetDescendant(@jhony,@robert), 4, 'Alex')

image image

 

Executing couple of inserts ….

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @alex hierarchyid = (select Node from h where name = 'Alex')

   3: declare @robert hierarchyid = (select Node from h where name = 'Robert')

   4: insert into h (Node, ID, Name) values (@parent.GetDescendant(@alex,@robert), 5, 'Niki')

   5: go

   6:  

   7: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   8: declare @alex hierarchyid = (select Node from h where name = 'Alex')

   9: declare @niki hierarchyid = (select Node from h where name = 'Niki')

  10: insert into h (Node, ID, Name) values (@parent.GetDescendant(@alex,@niki), 6, 'Steve')

  11: go

  12:  

  13: select Node.ToString() AS NodeText, * FROM h

  14: go

 

image image

 

Now let’s add 2 children nodes for Steve. Execute the following query.

   1: declare @steveParent hierarchyid = (select Node from h where name = 'Steve')

   2: insert into h (Node, ID, Name) values (@steveParent.GetDescendant(null,null), 7, 'S1')

   3: go

   4:  

   5: declare @steveParent hierarchyid = (select Node from h where name = 'Steve')

   6: declare @s1 hierarchyid = (select Node from h where name = 'S1')

   7: insert into h (Node, ID, Name) values (@steveParent.GetDescendant(@s1,null), 8, 'S2')

   8: go

image image

 

IsDescendantOf method and GetAncestor methods are useful in querying the structure.

The following query returns the immediate set of nodes of the specified node.

IsDescendantOf 

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @parentNodeLevel int = (select NodeLevel from h where name = 'Thuru')

   3: select Node.ToString() AS NodeText, *  from h where 

   4: Node.IsDescendantOf(@parent) = 'TRUE' and node != @parent and 

   5: NodeLevel = @parentNodeLevel + 1

image

In the above query I restricted the query to return only the immediate children using the NodeLevel column. And also notice I have opted out the parent node because IsDescendantOf method includes the parent node as well in the result.

 

GetAncestor

Gets the ancestors of the specified node in the passed level, if we pass 0 it returns the siblings. 

   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(2)

2 steps above.

image

Immediate parent

   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(1)

image

Siblings

   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(0)

image

 

Deletion

Deleting a node does not delete its children nodes. This results in orphaned children. 

   1: delete from h where Name = 'Steve'

After deleting Steve if we execute our SELECT statement we get the following result

image

Note that S1 and S2 have the NodeText /1.1.1/1 and /1.1.1/2 where there’s no /1.1.1 resulting orphaned S1 and S2

 

Microsoft recommends to use proper stored procedures to match the business scenario to deal with the HierarchyID thus eliminating unwanted results like orphaned children.

Moving Nodes

The GetReparentedValue method is used to move the nodes to different locations.

   1: declare @newParent hierarchyid = (select Node from h where name = 'Johnny')

   2: update h set Node = Node.GetReparentedValue(Node.GetAncestor(1),@newParent)

   3: where name = 'S1'

Here we want to move the S1 as a child node of Johnny. We get the node value of Johnny and get the ancestors in the level of where Johnny is and update the Node of S1. This moves the S1 under Johnny leaving the hierarchy like this.

image

Sequence Column and IDENTITY

Posted on by

When you design tables you need a column to be updated automatically with a sequence number, ultimately which you may use as a Primary key of the table. (Some we use other columns as primary key)

But in case if you haven’t included a sequence column how you can include one.

Take a look,

Create a table in a database

image

Insert 10 rows

image

 

Now the table is populated like this

image

 

We have to Edit the SeqNo column but no way, since we don’t have any way identify the row uniquely.

First delete the SeqNo column

image

Now Add an IDENTITY column

image

Now the work is done

image

 

To make the new SeqNo column as Primary Key

image

Work Done Winking smile

MSDN Trackback : http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/158d58b0-0f3a-4d34-bff5-b51d2fad5416

Selecting the Top N in each Category

Posted on by

When we write queries in TSQL selecting the top ‘n’ number of rows is easy. It can be achieved by a query similar to the following.

SELECT TOP(10) Names FROM [Users]
WHERE IsActive = 'True'
ORDER BY ApprovedOn DESC

But when selecting the items which are from different tables and with subcategories this is quite tricky.

I had a scenario where Users can comment on each service/product. Each service/product is listed under a sub category. The need is the query should list down the recent 5 comments under each service sub category with the details of who made the comment.

Apparently I have to JOIN 4 tables. Selecting the TOP(n) from each category is challenging. I searched the Internet and some solutions say that execute the queries to get the top 5 in each category and combine them using the UNION. That doesn’t sound good and in my scenario the number of categories is dynamic. (So If I have to go with the above solution I have to write COUNT for sub categories and then follow that with bulky TSQL stored procedure).

This is a method I tried by combining some techniques mentioned in the Internet.

Table Structure (Tables contain more fields, but in the picture only the related fields are shown)

Capture

There two ways you can write the query. First using a temporary table which is not advisable in terms of efficiency. Second is using sub queries.

Using Temporary tables.

DECLARE @Temp TABLE
(
    Row INT,
    RealServiceID BIGINT,
    CategoryName VARCHAR(100),
    ServiceName VARCHAR(100),
    Comment VARCHAR(MAX),
    Username VARCHAR(50)
)

INSERT INTO @Temp
SELECT ROW_NUMBER() OVER
(PARTITION BY SC.CategoryID ORDER BY SC.CategoryName) AS Row, 

RS.RealServiceID, SC.CategoryName, RS.ServiceName, CM.Comment, US.Username
    FROM RealServices AS RS
              JOIN Comments AS CM ON
        RS.RealServiceID = CM.CommentOn JOIN
            ServiceCategory AS SC ON RS.CategoryID = SC.CategoryID
        JOIN Users AS US ON CM.UserID = US.UserID
    ORDER BY SC.CategoryName, CM.CommentOn

SELECT * FROM @Temp WHERE Row <= 5

Using Sub queries

SELECT RowNumber, RealServiceID, CategoryID, CategoryName, ServiceName,
    Comment, Username, SubmissionDate
FROM (
    SELECT ROW_NUMBER() OVER
(PARTITION BY SC.CategoryID ORDER BY SC.CategoryName, CM.CommentOn) AS RowNumber,
        RS.RealServiceID,
        SC.CategoryID,
        SC.CategoryName,
        RS.ServiceName,
        CM.Comment,
        US.Username,
        CM.SubmissionDate

    FROM RealServices AS RS
        JOIN Comments AS CM ON
        RS.RealServiceID = CM.CommentOn JOIN
            ServiceCategory AS SC ON RS.CategoryID = SC.CategoryID
        JOIN Users AS US ON CM.UserID = US.UserID
    ) AS InnerTable
WHERE RowNumber <= 5
 ORDER BY ServiceName, SubmissionDate DESC

Feel free to make comments on this Smile