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
Last updated
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
Last updated
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
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)
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
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
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)
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:
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.
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.
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:
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
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.
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:
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.
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
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:
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
It is tempting and easy to right click a table and script out the selection.
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
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.
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:
Then moving over to your database, create the users from the logins:
Then roles are added by the sp_addrolemember procedure, add both moon and mars to the db_owner role.
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.
