Flow on MS SQL - Basics

This is the first part of the traning "Flow on MS SQL Server". It is accompanied with a tranining video, see below for more information

Video: https://www.youtube.com/watch?v=EPKQXTCJIRY Length of video: 1 hour Estimated length of training: 4 hours Target audience: Basic understanding of how Flow and Flow portal works, and/or have completed similar training, and want to know more about how to use Flow together with MS SQL

Prerequisites:

You need access to a SQL server (SQL Server Express can be downloaded and installed for free), with at least database owner (dbo) permissions on a database (or permissions to create a new DB that we will work in).

​SQL Server Management Studio (SSMS) installed on your PC (it is backward compatible so just download the latest version)

​

Overview

The first part (basics) is designed to give a basic understanding of SQL (tables, views, diagrams, primary and foreign keys, joins and security). It also covers best practices and some tips and tricks when working with MS SQL and Flow. It is a good idea to browse through Basics even if you feel familiar with both Flow and MS SQL.

In the later parts, we are going to look at more complex functions that work well with Flow and Flow Portal

Naming standard and casing

Singularity - Try to name views and tables in their singular form, i.e. Project instead of Projects etc.

Casing - It can be a bit of a controversial topic, but we are going with what is referred to as PascalCase or Upper Camel Case on the internet, it is like camel case but you start every word with an uppercase, mostly since it looks better if you have only one word. We will however deviate a little from this standard when naming views, no rule without exception

snake_case: all_lower_case_with_underscores SCREAMING_SNAKE: ALL_UPPER_WITH_UNDERSCORES camelCase: firstlowerThenUppercase PascalCase: AllWordsStartsWithUppercase

Types of Casing

Table

Best Practice when working with your tables:

• Try to always create these columns for new tables:

  • RowCreatedDt Datetime2(0) with default Getdate()

  • RowUpdatedDt Datetime2(0) (Updated with Flow)

  • RowCreatedBy Nvarchar(200) (Updated with Flow)

  • RowUpdatedBy Nvarchar(200) (Updated with Flow)

• If it makes sense, just use one PrimaryKey (PK) where you tell sql server to automatically assign a key (Identity Specification). There is this Scope_Identity() function that we will look at more in detail later when we start creating Flows where we will see the benefit.

• Try to have the PK be named TableNameId. E.g. If the table name is Project, name the first column ProjectId (and you can make it and Identity column as described below)

SQL Datatypes

Every column in a SQL table needs a datatype. They vary a little compared to Oracle if you are used to that, but the principles are similar.

Unicode (Nvarhcar and Nchar vs Varchar and char)

A simplified explanation of Unicode is that it gives support for special characters (Japanese, Cyrillic, emojis etc.), so it is common to use Unicode when a human can interact with the field. It takes up more space on disk than non-unicode so in some cases varchar can be more optimal, for example, if a system always writes ‘prd’ or ‘test’ or something like that that we know is going to be set of 7-bit characters. But as a rule of thumb, use Unicode datatype rather than non-Unicode when working with free text.

The “n” in front of the datatype indicates if it is Unicode, for example, Nvarchar is Unicode and Varchar is not

Char vs Varchar: Char always has a set amount of characters. So if you set a field to char(5) and enter ‘Hi’ the field will actually contain Hi… (where the dots represent spaces). If instead setting it to varchar(5) a hi will only contain two letters, the “var” part likely means variable. So the optimal varchar(x)-number is the longest word you will ever have in your column.

It is almost always recommended to use Nvarchar/Varchar instead of Nchar/Char.

So our takeaway for text fields, use Nvarchar(x) where x is the highest number of letters you think will be needed.

To illustrate the difference, we can run this code:

Set Nocount On
Declare @Chartest char(10)
Declare @VarChartest Varchar(10)
Declare @NVarChartest NVarchar(10)

select @Chartest = 'Hi'
Select @Chartest as Chartest
select @VarChartest = 'Hi'
Select @VarChartest as VarChartest

select @VarChartest = 'Hi😂'
Select @VarChartest as VarChartestEmoji
select @NVarChartest = N'Hi😂'
Select @NVarChartest as NVarChartestEmoji

And see the result

Varchar, Nvarchar, Char, Nchar

We can learn several things from this, first we need to return the results as text, or the studio will truncate the spaces for us, so set it to “Results as text (Ctrl+t)” (red arrow).

To make my result clearer, I start with “SET NOCOUNT On”

Then highligtning my result I see the difference between Charttest and VarChartest (green arrows), note how the Chartest has spaces after it, whereas others do not.

Then I have placed an emoji into varchar and in Nvarchar to illustrate the difference (blue arrows). Note the N in select @NVarChartest = N'Hi😂'

The smiley here can also be a special language character, Unicode is not only created for emojis.

Change the results back to Grid (Ctrl+D)

Date and datetime: Date is just the date part of datetime, so if you only need a date, use date. If you also need the time part, use datetime2(0).

There is a very small difference between using DateTime and DateTime2, and that is that DateTime includes milliseconds, while in DateTime2 you can set what granularity you want on the timestamps. So datetime2(0) is actually leaner than datetime, and it is very rare that you need milliseconds in the timestamp. Be sure to set a 0 in the parentheses, it sometimes changes back to a 7 in the SQL user interface.

Time zones are related to date and time, but that will be discussed more in detail later.

HierarachyId might be a very underappreciated datatype when working with lots of data in a hierarchy structure. We are going to dive deep into this in the advanced section. Often times it is better to use a limitless hierarchy structure instead of fixed- for example, instead of painting yourself into a corner with task and subtask and maybe even subsubtask if you need three levels, use a non-fixed structure with hierarchy.

Create table from user interface

To create a table graphically using SQL server user interface, under the database you want to place it – right click on Tables and choose to create a New Table

Following our best practice, we will later call the table “Project” so we will therefore call our Primary Key column ProjectId and set it to Identity(1:1)

Typically- except for the Id- the rows in our table need a friendly description. Different systems use different standards (or lack thereof) when it comes to the naming standard where it sometimes is called Description, Name, text or similar. Use what you find most intuitive and stick to that, it could be so that xxxName is a shorter description (typically 100 chars or so) and xxxDescription could be a more descriptive expression of each row.

To keep this simple we are going with ProjectName Nvarchar(100).

Under the RowCreatedDt, set “Default Value or Binding” to GetDate(). This way SQL Server will put a constraint on the column to automatically set the date and time when the row is created.

Create the other Row-update columns:

And set ProjectId as the primary Key for your table:

The save the table by pressing the little floppy disk symbol and name your Table “Project”

Key Takeaways:

• Naming standard:

  • For tables we use the singular form of the word, like “Project” instead of Projects

  • We try to have one Primary Key that is called TableNameId. Using PascalCamelCase, the d in Id is then lower case.

• Logging columns:

  • Always try to have at least the RowCreatedDt column with a default GetDate(). Knowing when the row was created can be a lifesaver.

  • Using the other three RowCreated and RowUpdated columns must be supported in the flows (to update the data). To track all the updates on a table it is better to use an audit trigger, we might look at that later, but the RowUpdate-columns is a lightweight quick solution that might help.

Script creation of tables

Now that we have our first table, we can script the creation of that table to a new editor window

We love copy and paste so just replacing “Project” with “Task” will create a perfect Task table for us

When doing this, make sure the name of the constraints and table also gets replaced or you will get an error message.

Before we execute the script, we will add a column as well since we are going to create a foreign key here to our Project table. Insert the following rows just below TaskName:

ParentTaskId int, ProjectId int, Hid HierarchyId,

It should look like this:

Press F5 to create our Task table (and if you like, right click on “tables” on the left hand side and choose “refresh” to see that the table is created).

We also need a TaskAssignment table, run this code:

CCREATE TABLE [dbo].[TaskAssignment](
 [TaskAssignmentId] [int] IDENTITY(1,1) NOT NULL,
 ResourceId int,
 TaskId int,
 [RowCreatedDt] [datetime2](0) NULL,
 [RowCreatedBy] [nvarchar](150) NULL,
 [RowUpdatedDt] [datetime2](0) NULL,
 [RowUpdatedBy] [nvarchar](150) NULL,
 CONSTRAINT [PK_TaskAssignment] PRIMARY KEY CLUSTERED
(
 [TaskAssignmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TaskAssignment] ADD CONSTRAINT [DF_TaskAssignment_RowCreatedDt] DEFAULT (getdate()) FOR [RowCreatedDt]
GO
--And we finally need a Resource Table, run this code:
CREATE TABLE [dbo].[Resource](
 [ResourceId] [int] IDENTITY(1,1) NOT NULL,
 ResourceName Nvarchar(15),
 [RowCreatedDt] [datetime2](0) NULL,
 [RowCreatedBy] [nvarchar](150) NULL,
 [RowUpdatedDt] [datetime2](0) NULL,
 [RowUpdatedBy] [nvarchar](150) NULL,
 CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED
(
 [ResourceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Resource] ADD CONSTRAINT [DF_Resource_RowCreatedDt] DEFAULT (getdate()) FOR [RowCreatedDt]
GO

​

Prevent saving changes that require table re-creation

Make sure you have unchecked the “Prevent saving changes that require table re-creation” from Tools – Options in SQL Server Management Studio. This must be done every time you install MSSMS

Graphical user interface, text, application Description automatically generated

SQL Diagrams

We will now create the foreign keys and- at the same time- take a look at the database diagram.

Right click on Database Diagrams and choose to create a New Database Diagram (and if you get a message about installing that component, please do so).

You might get an error like this:

​​

The fix for that is to set an owner under Files under properties on the database:

Add the four tables you just created to the diagram:

(Tables can be modified and created from within the diagram window as well. However, Microsoft is considering reducing the diagram functionality, something that is not very well received in the MS SQL Community, as it is actually a pretty good tool)

So now we are going to connect our tables by dragging our intended Foreign Keys columns from the Foreign Key table to the Primary key on the Primary table

​​​

Just check so that it looks good and click ok

To save the changes to the database we need to save our Diagram by clicking the floppy disk icon again. Then we can name our diagram “Task” (or what you feel is a fitting name).

Click ok if this warning comes up.

We have now created a Foreign Key between the Task and Project table meaning that the tables are connected, so I cannot create a task for a ProjectId that does not exist (unless I let ProjectId be NULL in my task table).

A very handy feature is to set the Delete rules on the relation. If set to “No Action” SQL server will prevent a Project from being deleted if there are tasks connected to it.

Setting to Cascade will also delete all tasks if the project is deleted. And we can also tell SQL server to set Null or the Default on the table.

Choose Cascade and save the diagram to commit the changes.

Create foreign Keys between TaskAssignment and Task, and TaskAssignment and Resource with cascading deletion rules.

Save the changes.

We will also need two more columns in the task table, so while in the database diagram – also add StartDt and EndDt to the Tasks table (you might need to change to Standard Table View so that you can add the data type).

Save the changes.

Note adding columns in the “middle” of the table as we do above can lead to complications if you have views or functions that are based on the table, so normally I would recommend just add tables to the “end” of the table (below your last added column). However, in this case, we know that nothing is based on our table, so it feels ok to put it on top.

Views

A common way to simplify the fetching of data is to create views on top of the tables. Some ERPs use views to set permissions, and if nothing else it is a good way to control what is being visualized. When working with Flow Portal and SQL it can make life easier when having many portlets reading from the same source. As an example, if you create two dummy columns in a view called “Filter” and “Prio” with null values, and then connect Flow Inboxes to those columns in your view, then you just need to make changes in one place to affect all portlets / inboxes at a later stage.

Another reason to create views is if Flow does not support a certain data type. In our example, HierarchyId is not supported yet so we will have to handle that.

Always start your view with a comment section, and always comment changes you do to a view. You will someday thank yourself for doing that, and surely someone else will too.

Another best practice can be to name your views with an initial “v” so that it is always easy to see if you are grabbing data from a view.

Let’s create a view on top of the tables that we have just built that we will call vTask. Then, add to the view a comment section- leaving room for added rows with the date, who did the change, and what was changed. Then I like to have separate selections of the underlying tables, and a select against the view itself to save many fractions of seconds for your future self. Like so:

Create view vTask as
/*
2021-04-19 olcase: Created

To Test:
Select * from vTask
Select * from Task
*/

Select
*
From Task t

Now that the view is created, we need to change the syntax to Alter view instead of Create View in the initial statement. And instead of doing select *, it can be clearer to add the actual columns that we want to select, and a shortcut to do that is to grab the columns from the Object explorer and drag them out to where you want them in the query editor

In this view, we will not need the RowCreated or the RowUpdated columns, so we can remove them. We must also add the prefix to the columns with the alias we have given our table (the “t”). Always do this, both for visualization but also to avoid issues with the view if you happen to add a column with the same name to one of the tables included in the view.

SQL Tip: Refresh SQL intellisense (Ctrl + Shift + R)

As you might see in your sql as well, our latest created Columns (StartDt and EndDt) each have a red curly line under them indicating that something is not right. As we have just added the columns and dragged them out from the object explorer, we know that there is nothing wrong with them, but the IntelliSense parser hasn’t had the time yet to update itself. You can force a refresh by clicking CTRL + SHIFT + R to refresh the IntelliSense in Microsoft SQL Server Management Studio. Waiting just a few seconds should remove the error indicator.

We also need to change the Hid-column as we do not yet support HierarchyId in Flow. We will discuss this more later but for now, just remove hid and instead type t.Hid.ToString() Hierarachy

​

Joins

Explaining all the different joins in detail is a bit out of the scope for this training. But we will always do real ansi-joins, not just selects and joining by where statements (and sometimes +-signs).

The difference between a join (or inner join) and a left join (or an outer left join) is that in the first (join) the records must also exist in the outer table, while with a left join they do not. A full outer join includes all records that exist in either of the two tables, and a cross join checks all possible matches.

So between Task and project we will use a left join (the task does not need to be connected to a project) Left join Project p on p.ProjectId = t.TaskId

There does however need to be a Task Assignment to each task (or our portlets won’t work anyway), so we will Inner Join TaskAssignment ta on ta.TaskId = t.TaskId

And, the same thing with our resources, join Resource r on r.ResourceId = ta.ResourceId

Also select: ,p.ProjectName, ta.TaskAssignmentId, ta.ResourceId, r.ResourceName

And for training's sake, make a small comment on the changes. So that our editor window now looks like this:

​

CREATE view [dbo].[vVisualPlanning]
as

/*
2021-05-23 olcase: Created

Select * from vVisualPlanning
Select * from Task
Select * from Project
Select * from TaskAssignment
Select * from Resource

*/

Select 
t.[TaskId], t.[TaskName], t.[ParentTaskId], t.[StartDt], t.[EndDt], t.[Projectid]
from task t
left join Project p on p.ProjectId = t.Projectid
join TaskAssignment ta on ta.TaskId = t.TaskId
Join Resource r on ta.ResourceId = r.ResourceId

SQL Tip: Alt + click

A nice feature in the more sophisticated text editors that is not so well known is the option to ctrl and left click to select and update a block of text. Say that we would like to left join our TA and R tables instead, press ctrl on the keyboard while selecting just before our joins:

And type “left”, notice how it updated both rows? Doesn’t matter perhaps if we are dealing with two lines, but very handy when having more data. This also works in Notepad++ and many other text/code editors.

Don’t save the changes, we will need an inner join (or just a join) in this case between the t, ta and r tables, this was just to show the functionality

SQL Tip: Selecting from views and tables

It is tempting and easy to right click a table and script out the selection.

Graphical user interface, text, application Description automatically generated

But always remove the full table name from the query if using it flows and views since it then will work to subscribe the flow to prod or other environments. Also, be aware of the select top 1000 which can create confusion.

Text Description automatically generated

Inserting and updating tables

Graphical

There is a graphical way to update your data in SQL server that is accessed by right clicking on the table you want to edit and select “Edit Top 200 Rows”

That will open up an excel-like sheet of the top 200 rows of your table. You can create a new row here by just filling out the necessary fields in the last row that has a little Asterix in front of it

(Ctrl+0 sends a NULL if you accidentally typed something in a cell that should be NULL, typing NULL works as well)

If the data you want to modify is not in the top 200 rows you can modify the statement by right clicking and selecting SQL:

Then modify the query and execute it by right click – Execute SQL

Insert and Update statement

An easier way to insert into a table is to use the insert into command.

There will be plenty of examples later when we are connecting flows to manipulate our data.

SQL Logins, Users, Roles and Permissions

In SQL Server you first create a login on the server level where the password is set, and then access to databases is given by adding that login as a user to that specific database. So, note the difference between a user and a login.

If your server is locally installed you can do this via the user interface, while if you are on an Azure hosted SQL server you need to do this by code so we will only do it by code here to cover both scenarios.

Always create the logins using proper passwords. I know it is very tempting to use just blank or something like Summer2021. It is recommended to use KeePass or some other tool to generate your password (and while you are generating it there, save it there as well so you have it stored).

So, start by making sure you are in the master database, and add a login for Mars and one for Moon:

​

--Use Master
CREATE LOGIN Mars WITH password='2ABYlabiRdWckJsSnOxd';
CREATE LOGIN Moon WITH password='dNADM2vDJ1MdnzLlLBCp';

Then moving over to your database, create the users from the logins:

--use Your Database
CREATE USER Mars FROM LOGIN Mars;
CREATE USER Moon FROM LOGIN Moon;

Then roles are added by the sp_addrolemember procedure, add both moon and mars to the db_owner role.

--use Your Database
EXEC sp_addrolemember N'db_owner', N'Moon'
EXEC sp_addrolemember N'db_owner', N'Mars'

​

This means they will have full access to all tables and views we have created. A more limiting option would be to not add them to the db_owner role but instead create your own roles that you then grant access to. To not dig too deep into the weeds around security we will not cover this now, a quick google of grant and revoke sql permissions should give all the answers.

To try this out you can log on in MSSMS using your newly created user, either to mount a new server in the object explorer (the buttons in the red ring in the image) or connect your existing query window (the buttons in the blue ring).

The SQL Logins you create here are probably what you would enter either under the Connector (to have the same sql user for all flow users) or per flow user (so that different Flow users can have different SQL Users, meaning different access in SQL).

Wrap up: We now know how to create tables and views, some good practices around relations between tables, and we have a basic understanding of datatypes and joins. We also know how to manage roles, logins and users. This should be enough to start building flows and portlets on top of the tables to create a full solution.

​