Interested in learning how the latest updates to Microsoft Teams improves collaboration and shared information for sales, service and the entire organization? The August 2020 Tech Insider Update from JourneyTEAM previews the most important releases, updates and changes that promote the effective collaboration your organization needs to close more deals.
The updates to Microsoft Teams include task management capabilities with a new Tasks app, Together Mode in video conferencing, integrated Yammer Communities and Lists apps.
The new Tasks app in Teams merges individual tasks from Microsoft To Do and team tasks from Microsoft Planner for complete task management. The Tasks app is now available in desktop, web and mobile experiences. Guest users will be limited only to mobile and not be able to see it on the desktop version.
Together Mode enables more engaging meetings for remote workers that may be suffering from “meeting fatigue.” This feature places video call participants in a virtual space that make it appear as though they are in the same environment, like an auditorium, sitting side-by-side. In this view, Together Mode also helps presenters better gauge audience reactions.
The Yammer Communities app, an enterprise social networking platform that connects and engages employees through shared communication, has been brought to the Teams experience making it an even greater hub for operations and communications across the organization.
The Microsoft Lists app helps organizations track and organize work simply, and now organizations can create lists directly within Teams with the Lists App integration that is now generally available. The Lists app will be available in individual Channels as a tab. Users that want to track a list can click the “+” button next to tabs, search for the Lists app in the tab gallery, and easily add it.
All the standard features that are available for Lists in SharePoint web are also now accessible in the Lists app for Teams, which includes the three main views – grid, gallery, and calendar, as well as custom. Users can create lists or pick from eight standard templates.
A standalone Microsoft Lists app for iOS is anticipated for Q4 this year.
JourneyTEAM was recently awarded and the Microsoft Eagle Crystal trophy as a top 5 partner for Dynamics 365 Business Central software implementations. Our team has a proven track record with successful Microsoft technology implementations. We have experts ready to answer your questions about the latest Teams updates that can enhance collaboration at your organization and support sales and service goals and CRM activities.
When working with data, it is very crucial to maintain the correctness of the data. The correctness of data leads to reliable and effective decision making that helps in driving sales. Dynamics 365 CRM being a data-driven platform, a user cannot afford to make mistakes. But it is not possible to not make mistakes when handling a large amount of data every day. And, when it comes to your business data — a misplaced data or data loss can bring the entire operation to an abrupt halt.
Suppose you are updating some details of an open opportunity record in your Dynamics 365 CRM. Before you realize it you accidentally updated the amount and the change got saved by CRM. You can’t remember what the actual amount was. And CRM doesn’t allow you to undo the changes made in a CRM record once they are saved. This simple human error could cost a lot to the company. But not if you have installed in your Dynamics 365 CRM, the newest addition to our suite of productivity apps .
Click2Undo is a robust Microsoft Preferred App that enables Undo functionalities in Dynamics 365 CRM and Power Apps. It helps users in restoring records to its last known state with just a single click. Users can undo the last change made to a single record or multiple records in one go. Changes made in the past can also be changed without any hassle. With the help of Click2Undo, users will also be able to restore deleted records in just one click.
Undo and restore last made changes in a record – Click2Undo lets users undo the last changes made to a record and restore it to the former state with just a single click on the Click2Undo button. Changes that are auto-saved can be undone by this feature thereby reducing the risk of losing the data.
Undo and restore changes made in the past – Dynamics 365 CRM with the help of audit logs, captures all the updates made to a record. Using a user-friendly interface, Click2Undo allows users to review those updates so that they can select and restore the updates made to the individual field values of the selected record. From recent changes to the changes done in the previous month, a single click on the Undo button and all can be restored.
Undo Changes in multiple records – With the help of this feature, changes made in multiple Dynamics 365 CRM records can be undone in one go. With the help of this bulk restoring feature, users can save time and increase their productivity.
Imagine misplacing important client’s contact information, only to find out later that your delay resulted in losing your client to a competitor. After working so hard, you don’t want a silly mistake to set you back. Hence, Click2Undo is a must have solution for your Dynamics 365 CRM environment.
While Click2Undo makes sure that the unwanted changes are to be undone with just a click, there is one requirement that needs to be fulfilled: it has to be installed in your Dynamics 365 CRM before you have a problem. After all, Click2Undo is all about preparedness and staying ahead of the potential for things to go wrong.
So, what are you waiting for? Click2Undo is a must have productivity app in your Dynamics 365 CRM / PowerApps that can reduce the risks of data loss and data incorrectness thereby indirectly assisting in maintaining customer relationships and making informed decisions. Download the solution for a free trial of 15 days from our or .
To know more about our other Microsoft Preferred Dynamics 365 CRM / PowerApps solutions, click .
For more information on Click2Undo, refer our online or drop us a mail at crm@inogic.com and our solution experts will be happy to assist you.
And…there is no undo button in real life, so stay safe!
Data allows organizations to make effective business plans, find resourceful insights, and much more. Hence, maintaining good and accurate data in Dynamics 365 CRM and Power Apps is very crucial. But what if there is a data entry error? What measures to take when an important record is deleted from the CRM environment? Dynamics 365 CRM does not allow users to undo changes in a record once it has been saved and nor it allows users to restore deleted records. With our 1 click app , we have brought the right solution for your business needs.
Click2Undo is a productivity app for Dynamics 365 CRM & Power Apps that enables users to undo changes done to a Dynamics 365 CRM record by restoring the record to its previous known state in a single click. From undoing the last changes made to a record or undoing the changes made to a record in the past, Click2Undo can do it without any hassle. It also enables users to restore changes to multiple records with a single click. Users can also restore deleted records if auditing for the said entity is enabled in CRM. Click2Undo works not only on OOB entities but also on Custom entities.
Let’s answer a few of the questions related to Click2Undo and its features that you may have regarding how it can benefit your business:
What unique feature does Click2Undo offer to Dynamics 365 CRM and Power Apps users?
As the name suggests, Click2Undo is a 1 Click app that enables users to undo changes done in a record in Dynamics 365 CRM. With the help of this feature, users can restore the record to its last known state in a single click.
Does Click2Undo allow users to restore changes done in bulk?
Yes, it does. With the help of Click2Undo, users can not only restore a single record to its last known state but can also restore multiple records in one go.
Can changes made in past be restored using Click2Undo?
With the help of the ‘History’ button, which stores the details of all the changes done to a record in the default set period of 30 days, users can restore changes done to a record in the past.
What if a record is deleted from Dynamics 365 CRM? Will Click2Undo be able to help?
When auditing is enabled for any entity, the user also has access to the list of all the records that have been deleted. Users can just click on the ‘Restore Deleted’ button, select the deleted record and restore it as per their requirement.
With all these features, Click2Undo will act as your own ‘Ctrl+z’ button within Dynamics 365 CRM & Power Apps thereby becoming a must-have application.
Wait no more and download the application for a trial period of 15 days from our . Click2Undo is also available on .
Watch our recently concluded to know more about Click2Undo.
To know more on our to Export, Clone or Undo email us at crm@inogic.com.
I’ve been looking to using datasets for the sometimes nice layout and scrolling possibilities.
However I’ve been trying to find a way to “force” the layout when using nested tables.
I can’t find any and it also seems to depend on the type of the values.
Are there any ways described to force a layout?
As a simple example.
planets = ExampleData[{"Dataset", "Planets"}]
ReplacePart[planets, {{"Earth", "Moons", "Moon", "Mass"} ->2, {"Earth", "Moons", "Moon", "Radius"} ->2}]
After 1 , 1 we are quite excited to announce release of our another star-studded addition to our suite of productivity apps – . As the name suggests, it is an app that restores the last state of Dynamics 365 CRM records in just 1 click.
Since Dynamics 365 is a vast customer relationship management system, it deals with bulk of data and at several instances its users make changes that are not desired. In case there is an unwanted change, it becomes nearly impossible for the users to restore the previous state and therefore, it can cause a chaos in the business. Altering data, that is not as per business requirement can lead to an overall havoc and the business may come up with many problems. It is always desirable to find a solution that addresses this issue.
In order to retain the last state of altered data we have developed the robust app Click2Undo. Click2Undo takes care of any changes done in a record and with the help of this app users can restore history changes done in a record and be in control of their business. Let’s see how with the help of Click2Undo businesses can be relieved.
Suppose Sam is the salesperson who is working on his sales target. Here are few scenarios where he can make use of Click2Undo:
Suppose Sam imports bulk records for some business requirement. Here, for few records erroneous data is imported and therefore, Sam needs to restore their previous state. In such a scenario, Sam simply clicks on Click2Undo button and the data is restored to their previous state.
Now, Click2Undo button is clicked and last state is restored.
With the help of Click2Undo Sam can restore the changes done to a record in the past. For instance there was a change in the record’s data one month ago. The change done in the past one month can be done using Click2Undo. By default with Click2Undo changes that were done in past 30 days can be restored.
Let’s undo history changes. For this click the side arrow button next to Click2Undo and select history. The History changes will appear, Sam can select the changes he wants to restore and these changes will be undone in the record.
In a scenario Sam needed to change certain data in few records as per a new deal so he goes ahead and changes them. But for some business requirement the deal does not fructify so he needed to re-instate the altered data to their previous state. In such a case there is no provision in Dynamics 365 to restore the changes done in the record. Here, Click2Undo comes to his rescue. He can select all those records whose last state has to be restored and bring back the records to their original state.
So you have seen how with the help of Click2Undo unwanted changes done in Dynamics 365 CRM records can be scraped and last state be regained. Time saving isn’t it?
So stay there, with this powerful app which acts as a boon for the Dynamics 365 user. For any query or suggestion drop a mail at crm@inogic.com and we can schedule a meeting with you. In the meantime you can visit the of Click2Undo to know more and download the solution for a free trial of 15 days. Follow this for setup information.
Till then, Ciao and Please Stay Safe!
COVID-19 is the biggest human challenge we’ve encountered in decades — and despite the focus on epidemiology it actually centers on data. In Data Surrounds Us, we shift our focus from what happened to what we can do better. Through understanding past crises and recent data in the world around us, we want to help everyday people understand how their choices will lead to a better outcome, dispel uncertainty and use data to chart, draw, and paint a better future.
COVID-19 happened so quickly — and by now it is clear to us all that it will reshape our society in so many lasting ways. It shocked, awed, and changed us in just a few short weeks. While almost everyone was confined to their homes by government-mandated shutdowns, isolation led to deep questions about the world around us: How we’re governed, what matters to us, and with whom we want to spend our precious time. In short, this large, impersonal single-cell driven crisis feels oh-so personal to us all, and is creating in many of us a sense of hopelessness and helplessness.
This isn’t the first time that society collectively experienced a traumatic shock. Both the 9/11 terror attacks and the 2008 economic collapse are still fresh in our recent living memory, but the real comparable events occurred in 1918, 1957, and 1968. This was especially poignant when death totals from the 1918 Spanish Flu exceeded tens of millions of people. Only a handful of survivors of the 1918 pandemic remain, and thus our current crisis is new to nearly all of us.
Even taking into account the two other pandemics of the 20th century, very few people experienced similar levels of fear, uncertainty, and doubt. Past pandemics affected the world in significant ways — politics, lifestyle, families, and of course the economy. There are lessons from those prior events: During the actual lockdowns, but also in the years that followed and the people who endured those tragedies. How we respond will certainly color how we look back on this crisis.
As we entered 2020, things were already changing — political upheaval, globalization breakdown, automation job replacement, innovation moving faster than human comprehension — the world was already in transition. This pandemic hit hard and fast, but now that the shock is subsiding, all nations must begin to chart towards a sense of semblance of normalcy. Many questions still remain unanswered so we should, therefore, note a key learning moment: This crisis provides us all with a chance to pause and reevaluate.
We have an opportunity to look at the data and to decide the world we want to live and work in, and rebuild it together for the better. In my day job, I serve as the Chief Strategy Officer of Sisense, a company that helps deliver data and analytics via an advanced technology platform. More importantly, our mission is to deliver insights to the builders of new constructs, be that data structures, analytics dashboards, or new businesses. Now more than ever, data will be a barometer of truth.
The last few weeks were an adjustment period, where people watched and debated data more than ever before. Hundreds of dashboards count and filter a local view of infections, deaths, and other key metrics around the coronavirus pandemic, and it impacts our collective psyche. This data is disorienting, morbid, and overwhelming but we are all figuring out how to survive this, together. But despite all of the negative signals, I believe, there is also hope.
Data can contribute to the healing process, but also shines a bright light on new and exciting changes in our society. Data cannot replace our values, nor should we simply consign our societal decision making processes to a set of smart algorithms. However, we should use the cast light of data-driven knowledge to gain insights and outline avenues into the future we should strive to shape.
While it’s a jarring shift, many of the things we are doing today will inform a tomorrow that shouldn’t feel distant. These things include our increased focus on the environment, the changing way we are using technology, celebrating front-line workers, how we shop and purchase items, and countless other factors that impact our personal and professional lives.
We are capable of doing more for ourselves than we ever thought. We are resourceful and many people are returning to a simpler way of doing things — whether that’s staying at home with family or getting outdoors (safely) looking for healthier lifestyles. It changed the way we perceive how we could work, the way we may live, what and how much we should give, how our kids would learn, and even how we consume and process information.
In short, it impacts what we prioritize, how we create a sense of purpose in our lives, and where we invest our time. Reality has shifted around us and as we find a new stable perch for our minds in the new normal we can choose to direct our gaze, goals, and values to a new horizon, otherwise blocked by the pre-pandemic grueling daily momentum.
Today, we’re launching a new initiative that explores the impact of COVID-19 on the world, showcasing data that predict a new, better world. We will curate and share trends, surveys, and insightful data. This “global timeout” allows us to realize the many things we can do without and the really important things we truly want back. Family quality time, self-development pursuits, reconnecting with friends, nurturing local initiatives, nature sorely missed, and many more. These are only a few of the newly rediscovered human life truisms which emerged during these days of forced seclusion. No matter how much the past might seem appealing, the world of 2019 is gone, and the future is now, and in our hands for shaping.
There are many questions to be targeted by human endeavor, but our world is embracing change more quickly than ever. At Sisense we want to help you curate and experience those changes through data tips and stories. As the world re-emerges, tough decisions will need to be made around the timing and structure of our transition back to normalcy. Some of these decisions will be generational (like the timing of gen-z vs. boomers returning to work) while others will impact everyone (like the timing of a vaccine rollout).
The decisions we make today will profoundly impact tomorrow for us, our children, and grandchildren. So, in the coming weeks and months, we’ll regularly share insights as a message of hope and with the goal of providing ideas around what a better future could look like. Data will be at the core of each of these stories because that is what is needed, and yes, also because that is what we do best. And the right data is free from political bias or gut feel; it’s something that is indisputable.
We hope you lean into the future with us, because we plan to push the limits around not only the ebbs and flows of the COVID-19 virus, but paint a picture around a pathway to what’s next. The future will be about a new purpose and a complete pivot that will be driven by analytics that empower the builders of tomorrow. We will each get to define our future.
Guy Levy-Yurista, PhD is the Chief Strategy Officer at Sisense, and an executive leader and entrepreneur with over 25 years of experience in Fortune 500, startup, and venture capital environments. Guy specializes in commercializing advanced technologies in dynamic market environments. He holds in-depth knowledge of analytics, social media, mobile, telecommunications, cyber-security, and optical technologies. As a thought leader and sought after speaker he is a contributing author to multiple media outlets and keynote speaker at a variety of industry and investor conferences.
Dynamics 365 CRM is a system that deals with huge amount of data on a daily basis. The end users are constantly updating data during which mistakes tend to happen and data is altered. Since there is a bulk of data to be handled in CRM, the administrators and IT department are under constant burden to manage the data and keep its backup to restore the alteration in data to its last known state in case it is required.
To enable this, we have come up with a powerful productivity app that will make restoring the last known state of data for a Dynamics 365 CRM user possible for both OOB and custom entities in an easy to use interface. This takes the burden off from end users and prevents data loss that may be caused due to alteration in data. Click2Undo not just makes the task of restoring data easier, but fully automates the process with the click of a button. With , the data integrity can be maintained and the biggest business use case of undoing changes done in data can be undone or last known state of data can be restored.
Let’s understand how Click2Undo will be a great time saving tool for Dynamics 365 CRM users. Below is the data that can be restored with the help of Click2Undo:
Undo
With Click2Undo changes done in a record can be undone with the help of undo feature. This gives users the liberty to perform their tasks more freely without the worry of losing last known state of data in case a business requirement arises which requires them to restore last known state.
Undo changes in multiple records
Many times there are changes made in multiple records. With the help of Click2Undo the changes in multiple records can be undone and the records can be quickly restored to their last known state. Thus, at any given moment, the managers or users can easily undo the changes in multiple records without hassle.
Restore changes done in the past
Click2Undo allows the users to restore those changes that were done in the past in a record. For instance, let’s say changes were done in a record few days ago and now you want to undo those changes. In such case, with the help of undo button, the record will be restored back to its previous known state.
Restore deleted record
With the help of Click2Undo any record that had been deleted due to some business requirement or by mistake can be restored.
Restore bulk deleted records
Bulk deletion jobs are used to delete the records in bulk which are not needed anymore. But what if by mistakenly the condition of Bulk Deletion Job is configured such that it ends up deleting important records? In such case you can make use of Restore Bulk Deleted records and reinstate those important deleted records.
Thus, you have seen how with the help of Click2Undo this nearly impossible task of restoring data or deleted records is made possible.
For more information about this amazing app, please visit our product page or drop a mail at crm@inogic.com.
Views are used to encapsulate common queries for reuse by SQL Server database objects or applications. They also allow for customized permissions to be applied while avoiding granting access to the underlying objects.
While this can be hugely beneficial, views are additional database objects that incur a maintainability cost in addition to the typical cost of upkeep for new code.
When a view’s underlying objects change, the view itself will not change. This can result in a view where the data types of columns, as well as nullability, precision, and scale can be reported inaccurately. When this happens, it is possible for queries against these columns to return errors, truncate data, perform poorly, or otherwise behave in unexpected ways.
This article will delve into views, how they are defined, and how T-SQL can be used to programmatically test the validity of views and ensure they never become stale.
To demonstrate how a view can fall out-of-sync with the tables it references, we will introduce a view and step through the process of altering data types underneath it.
Consider the view HumanResources.vEmployeeDepartment, which can be found in the AdventureWorks sample database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE VIEW [HumanResources].[vEmployeeDepartment] AS SELECT e.[BusinessEntityID] ,p.[Title] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,p.[Suffix] ,e.[JobTitle] ,d.[Name] AS [Department] ,d.[GroupName] ,edh.[StartDate] FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[BusinessEntityID] = edh.[BusinessEntityID] INNER JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID] WHERE edh.EndDate IS NULL |
The view contains ten columns that are selected from four tables. The data types for each of these columns are defined based on the underlying source columns. For example, the Suffix column in Person.Person is NVARCHAR(10); therefore, the Suffix column in vEmplloyeeDepartment will also be NVARCHAR(10). The data types of view columns can be quickly checked via SQL Server Management Studio by expanding Views within the database:
Let’s consider an upcoming software release that lengthens the Suffix column from 10 to 15 characters to accommodate new entries from within an application. Lengthening columns is a common change that helps an app adjust to changing business requirements as its data needs evolve over time. The change can be made with the following T-SQL:
|
ALTER TABLE Person.Person ALTER COLUMN Suffix NVARCHAR(15) NULL; |
This change can be confirmed via the table definition:
If we return to the view definition, though, we’ll note that the column definition there has not changed:
The discrepancy is not a UI glitch. No amount of refreshing the table or reopening the database server will change the length of the column. This functionality is intentional, and views are designed not to update when referenced objects change. The security, performance, and data ramifications of cascading data type changes would be far more consequential than the current state of affairs in which no updates occur.
Anyone that looks at the view definition will observe a length of 10, whereas the Person table reports a length of 15. This discrepancy can result in a variety of problems that are not likely to manifest themselves immediately, such as:
Note that the actual observed behavior will vary depending on the details of a view’s definition, the table’s definition, and how they are queried. Arguably, uncertainty is worse in code than a known and well-documented bug, and, therefore, we should not rely on unexpected behavior to diagnose view-related inconsistencies.
Since a software release does not always immediately precede corresponding data changes, it is possible that weeks or months could pass before a user enters data greater than 10 characters into this column. Our ability to keep track of views and update them diligently is therefore important while a release is fresh in our minds.
The solution to stale views is the built-in stored procedure sp_refreshview. When called, this stored procedure will regenerate the metadata for a given view based on all of its underlying objects. The syntax is straightforward and consists of one parameter: the name of the view.
Using this view, we can update the metadata for vEmployeeDepartment:
|
EXEC sp_refreshview @viewname = ‘Humanresources.vEmployeeDepartment’; |
After executing, let’s return to the view definition, refresh it, and check the data type for the Suffix column:
After executing sp_refreshview, the view now reports the Suffix column as NVARCHAR(15), rather than its previous length of 10. Success!
There is another option that allows views to stay in sync with their underlying objects: schema binding. When a view is created with schema binding, its definition is bound to the underlying objects. This prevents all changes to referenced objects while the schema bound view exists.
Schema binding protects a view from unintended changes to any objects it references. To illustrate this, let’s create a new view in AdventureWorks:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE VIEW v_employee_test WITH SCHEMABINDING AS SELECT Employee.BusinessEntityID, Employee.NationalIDNumber, Person.FirstName, Person.MiddleName, Person.LastName, Person.Suffix FROM HumanResources.Employee INNER JOIN Person.Person ON Person.BusinessEntityID = Employee.BusinessEntityID WHERE Person.Suffix IS NOT NULL; |
This view selects a handful of columns from two tables. Note the use of the WITH SCHEMABINDING clause on the CREATE VIEW statement.
Consider a scenario where a developer was to attempt to adjust the length of the Suffix column back to 10:
|
ALTER TABLE Person.Person ALTER COLUMN Suffix NVARCHAR(20) NULL; |
Unlike earlier when the ALTER TABLE command was allowed, this attempt to change the column length will be met with an error message:
Schema binding enforces the underlying database objects and will prevent changes to any of them. This is intentional and forces developers to address the view itself before making changes. The two most common solutions to manage changes to underlying objects are:
The first option is problematic as dropping the view will remove its ability to serve an application. If an app needs the view, then dropping it will result in an outage. The second option has more steps but allows for the view to remain in place while objects are altered. The following script illustrates applying this method to our previous column change:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
ALTER VIEW v_employee_test AS SELECT Employee.BusinessEntityID, Employee.NationalIDNumber, Person.FirstName, Person.MiddleName, Person.LastName, Person.Suffix FROM HumanResources.Employee INNER JOIN Person.Person ON Person.BusinessEntityID = Employee.BusinessEntityID WHERE Person.Suffix IS NOT NULL; GO ALTER TABLE Person.Person ALTER COLUMN Suffix NVARCHAR(20) NULL; GO ALTER VIEW v_employee_test WITH SCHEMABINDING AS SELECT Employee.BusinessEntityID, Employee.NationalIDNumber, Person.FirstName, Person.MiddleName, Person.LastName, Person.Suffix FROM HumanResources.Employee INNER JOIN Person.Person ON Person.BusinessEntityID = Employee.BusinessEntityID WHERE Person.Suffix IS NOT NULL; GO |
This script succeeds in altering the column to a length of 20 by removing schema binding, altering the column length, and then adding schema binding back to the view. The view remains present for the entire operation, ensuring that anything that relies on it will continue to function.
Schema binding is heavy-handed, but an effective solution for managing the tables that views rely on and ensuring that changes cannot slip through the cracks unnoticed. It forces developers to consider dependencies anywhere they exist, prior to changes being made.
Many organizations will not use schema binding for this exact reason, in any case. If a database has many views in it, then schema binding will force developers to alter views frequently throughout the scripting process. This can bloat release scripts and by increasing the volume of release code also increase the risk of things going wrong.
The remainder of this article deals with databases where schema binding is not used for all views.
For most databases, schema binding will not be universally used. As a result, it will be possible to change objects without being forced to also address the views as part of those changes.
Tackling this challenge requires building a solution that can check view metadata before and after a release and determine if underlying objects have changed in ways that could invalidate their dependent views.
Running this test requires a development environment where we can make changes freely without worries about breaking an app. To do this, we would need a database backup or, if data is large, a copy of a database’s schema (with empty tables) to work against. Data is not needed for this validation, so feel free to leave it behind.
The goal is to take a snapshot of all views in the database and compare that against what those views would look like after they are all refreshed. To do this, let’s create a pair of temporary tables that will store these snapshots:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE TABLE #view_metadata_before ( view_metadata_before_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY clustered, view_object_id INT NOT NULL, view_schema_name SYSNAME NOT NULL, view_name SYSNAME NOT NULL, column_name SYSNAME NOT NULL, column_id INT NOT NULL, user_type_id INT NOT NULL, user_type_name SYSNAME NOT NULL, column_max_length SMALLINT NOT NULL, column_precision TINYINT NOT NULL, column_scale TINYINT NOT NULL, is_column_nullable BIT NOT NULL, is_schemabound_view BIT NOT NULL); CREATE TABLE #view_metadata_after ( view_metadata_after_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, view_object_id INT NOT NULL, view_schema_name SYSNAME NOT NULL, view_name SYSNAME NOT NULL, column_name SYSNAME NOT NULL, column_id INT NOT NULL, user_type_id INT NOT NULL, user_type_name SYSNAME NOT NULL, column_max_length SMALLINT NOT NULL, column_precision TINYINT NOT NULL, column_scale TINYINT NOT NULL, is_column_nullable BIT NOT NULL, is_schemabound_view BIT NOT NULL); |
With some holding places defined, we can populate #view_metadata_before with the current state of affairs in the database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
INSERT INTO #view_metadata_before (view_object_id, view_schema_name, view_name, column_name, column_id, user_type_id, user_type_name, column_max_length, column_precision, column_scale, is_column_nullable, is_schemabound_view) SELECT views.object_id AS view_object_id, schemas.name AS view_schema_name, views.name AS view_name, columns.name AS column_name, columns.column_id, types.user_type_id, types.name AS user_type_name, CASE WHEN types.name IN (‘nchar’, ‘nvarchar’) THEN columns.max_length / 2 ELSE columns.max_length END AS column_max_length, columns.precision AS column_precision, columns.scale AS column_scale, columns.is_nullable, OBJECTPROPERTY(views.object_id, ‘IsSchemaBound’) AS is_schemabound_view FROM sys.views INNER JOIN sys.columns ON columns.object_id = views.object_id INNER JOIN sys.types ON types.user_type_id = columns.user_type_id INNER JOIN sys.schemas ON schemas.schema_id = views.schema_id ORDER BY views.object_id, columns.column_id; |
The result of this is a row per column within each view and some metadata describing the nature of each column:
Note the CASE statement that checks the data type and halves the column length when a double-byte data type is present. This is not necessary, but helps the results match up better with what a user might see in SQL Server Management Studio.
The results are not an exhaustive list of column attributes. There are other pieces of metadata stored in sys.columns that could potentially affect how that column operates. These views contain the most commonly used attributes that will actually impact views. If a database environment has unique circumstances, then adjusting these tables to add more metadata is relatively straightforward.
With a baseline defined, we now need to refresh all views. If a view’s underlying objects have changed, then its metadata will also update accordingly. Updating all views requires a distinct list of views, which we can quickly collect from the list we generated above:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE #distinct_view_list (distinct_view_list_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, view_schema_name SYSNAME NOT NULL, view_name SYSNAME NOT NULL); INSERT INTO #distinct_view_list (view_schema_name, view_name) SELECT DISTINCT view_metadata_before.view_schema_name, view_metadata_before.view_name FROM #view_metadata_before view_metadata_before WHERE view_metadata_before.is_schemabound_view = 0; |
This script takes the columns list and distills it into a list of views. Schemabound views are filtered out as they will not impact our testing. Since they cannot have their components altered without explicit view changes, there won’t be any surprises to find from them.
Refreshing all views will require some dynamic SQL to generate sp_refreshview statements for each view identified above:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
CREATE TABLE #view_ddl_exception_list (view_name SYSNAME NOT NULL PRIMARY KEY CLUSTERED); DECLARE @sql_command NVARCHAR(MAX); SELECT @sql_command = ”; SELECT @sql_command = @sql_command + ‘ BEGIN TRY EXEC sp_refreshview N’” + CAST(distinct_view_list.view_schema_name AS VARCHAR(MAX)) + ‘.’ + CAST(distinct_view_list.view_name AS VARCHAR(MAX)) + ”‘; END TRY BEGIN CATCH PRINT ‘‘The following view is broken via DDL errors during sp_refreshview: ‘ + CAST(distinct_view_list.view_schema_name AS VARCHAR(MAX)) + ‘.’ + CAST(distinct_view_list.view_name AS VARCHAR(MAX)) + ”‘; IF @@TRANCOUNT > 0 BEGIN ROLLBACK; END INSERT INTO #view_ddl_exception_list (view_name) SELECT ‘” + CAST(distinct_view_list.view_name AS VARCHAR(MAX)) + ”‘; END CATCH;’ FROM #distinct_view_list distinct_view_list; EXEC sp_executesql @sql_command; |
Note that there is some added complexity here in the form of try…catch blocks. A view can be broken by changes to its underlying tables, and we’d like to catch these errors gracefully, rather than have our code break mid-execution. The resulting command that gets executed will look like this:
This sample shows the T-SQL needed to refresh two views. The full script will contain a TRY and CATCH block for each view. Note that the temp table #view_ddl_exception_list will be populated with a broken view name, which will allow you to research further when this script is complete. We can capture more metrics, such as error codes and messages, but I wanted to stop short of making this script too complex. Feel free to add more debugging if it proves helpful in error-trapping.
Assuming no errors, the dynamic SQL above will execute quietly, and execution will move on to the next section of code with no fanfare. The next step is to capture view metadata a second time, using the same syntax that was used earlier:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
INSERT INTO #view_metadata_after (view_object_id, view_schema_name, view_name, column_name, column_id, user_type_id, user_type_name, column_max_length, column_precision, column_scale, is_column_nullable, is_schemabound_view) SELECT views.object_id AS view_object_id, schemas.name AS view_schema_name, views.name AS view_name, columns.name AS column_name, columns.column_id, types.user_type_id, types.name AS user_type_name, CASE WHEN types.name IN (‘nchar’, ‘nvarchar’) THEN columns.max_length / 2 ELSE columns.max_length END AS column_max_length, columns.precision AS column_precision, columns.scale AS column_scale, columns.is_nullable, OBJECTPROPERTY(views.object_id, ‘IsSchemaBound’) AS is_schemabound_view FROM sys.views INNER JOIN sys.columns ON columns.object_id = views.object_id INNER JOIN sys.types ON types.user_type_id = columns.user_type_id INNER JOIN sys.schemas ON schemas.schema_id = views.schema_id WHERE views.name NOT LIKE ‘x[_]%’ ORDER BY views.object_id, columns.column_id; |
With a before and after snapshot available, we can compare the two and determine if any differences exist. To do this meaningfully, we should perform left joins between the before-snapshot and the after-snapshot. To be thorough, we should execute this query starting with the before metadata and then also starting with the after metadata. This ensures that we capture any changes, including dropped or added columns. The resulting query is a bit lengthy, but will provide exactly what is needed to evaluate changes in views:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 |
WITH CTE_before_except AS ( SELECT view_object_id, view_schema_name, view_name, column_name, column_id, user_type_id, user_type_name, column_max_length, column_precision, column_scale, is_column_nullable, is_schemabound_view FROM #view_metadata_before EXCEPT SELECT view_object_id, view_schema_name, view_name, column_name, column_id, user_type_id, user_type_name, column_max_length, column_precision, column_scale, is_column_nullable, is_schemabound_view FROM #view_metadata_after), CTE_after_except AS ( SELECT view_object_id, view_schema_name, view_name, column_name, column_id, user_type_id, user_type_name, column_max_length, column_precision, column_scale, is_column_nullable, is_schemabound_view FROM #view_metadata_after EXCEPT SELECT view_object_id, view_schema_name, view_name, column_name, column_id, user_type_id, user_type_name, column_max_length, column_precision, column_scale, is_column_nullable, is_schemabound_view FROM #view_metadata_before) SELECT CTE_before_except.view_object_id, CTE_before_except.column_id, CTE_before_except.view_schema_name AS view_schema_name_before, view_metadata_after.view_schema_name AS view_schema_name_after, CTE_before_except.view_name AS view_name_before, view_metadata_after.view_name AS view_name_after, CTE_before_except.column_name AS column_name_before, view_metadata_after.column_name AS column_name_after, CTE_before_except.user_type_id AS user_type_id_before, view_metadata_after.user_type_id AS user_type_id_after, CTE_before_except.user_type_name AS user_type_name_before, view_metadata_after.user_type_name AS user_type_name_after, CTE_before_except.column_max_length AS column_max_length_before, view_metadata_after.column_max_length AS column_max_length_after, CTE_before_except.column_precision AS column_precision_before, view_metadata_after.column_precision AS column_precision_after, CTE_before_except.column_scale AS column_scale_before, view_metadata_after.column_scale AS column_scale_after, CTE_before_except.is_column_nullable AS is_column_nullable_before, view_metadata_after.is_column_nullable AS is_column_nullable_after, CTE_before_except.is_schemabound_view INTO #sp_refreshview_results FROM CTE_before_except LEFT JOIN #view_metadata_after view_metadata_after ON view_metadata_after.view_object_id = CTE_before_except.view_object_id AND view_metadata_after.column_id = CTE_before_except.column_id UNION SELECT view_metadata_before.view_object_id, view_metadata_before.column_id, view_metadata_before.view_schema_name AS view_schema_name_before, CTE_after_except.view_schema_name AS view_schema_name_after, view_metadata_before.view_name AS view_name_before, CTE_after_except.view_name AS view_name_after, view_metadata_before.column_name AS column_name_before, CTE_after_except.column_name AS column_name_after, view_metadata_before.user_type_id AS user_type_id_before, CTE_after_except.user_type_id AS user_type_id_after, view_metadata_before.user_type_name AS user_type_name_before, CTE_after_except.user_type_name AS user_type_name_after, view_metadata_before.column_max_length AS column_max_length_before, CTE_after_except.column_max_length AS column_max_length_after, view_metadata_before.column_precision AS column_precision_before, CTE_after_except.column_precision AS column_precision_after, view_metadata_before.column_scale AS column_scale_before, CTE_after_except.column_scale AS column_scale_after, view_metadata_before.is_column_nullable AS is_column_nullable_before, CTE_after_except.is_column_nullable AS is_column_nullable_after, CTE_after_except.is_schemabound_view FROM CTE_after_except LEFT JOIN #view_metadata_before view_metadata_before ON view_metadata_before.view_object_id = CTE_after_except.view_object_id AND view_metadata_before.column_id = CTE_after_except.column_id; IF EXISTS (SELECT * FROM #sp_refreshview_results) BEGIN SELECT * FROM #sp_refreshview_results; END |
The results of the above T-SQL provide a clean data set showing what views have changed, but have not been refreshed:
Seven views rely on the Suffix column that we altered earlier. The details above show the view name, the column, and how it changed. Before and after metadata are included, allowing for a full understanding of changes without any guesswork. Using this information, we can review our application changes and ensure that views remain accurate as changes occur to tables and columns.
One hugely important note: Always run this process in a development environment! In order to do a before & after schema comparison, it is necessary to refresh views and determine if any changes have occurred. Perform this test in development and then refresh specific views in production to bring them in sync with the underlying schema.
We can also test views for errors that may have arisen due to changes in underlying objects. For example, if a table that a view references is dropped, the view will throw an error when queried. Data type mismatches or truncation could also occur if data types change without related views getting altered or refreshed.
This process is simpler than verifying schema changes and can be conducted by a nice combination of brute force and dynamic SQL. If a view no longer functions correctly, it can be tested by selecting a row and catching errors when they occur. Dynamic SQL provides an easy means to accomplish this task:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
DECLARE @sql_command NVARCHAR(MAX); IF EXISTS (SELECT * FROM tempdb.sys.tables WHERE tables.name LIKE ‘#distinct_view_list%’) BEGIN DROP TABLE #distinct_view_list; END CREATE TABLE #distinct_view_list (distinct_view_list_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, view_schema_name SYSNAME NOT NULL, view_name SYSNAME NOT NULL); INSERT INTO #distinct_view_list (view_schema_name, view_name) SELECT schemas.name AS view_schema_name, views.name AS view_name FROM sys.views INNER JOIN sys.schemas ON schemas.schema_id = views.schema_id; IF EXISTS (SELECT * FROM tempdb.sys.tables WHERE tables.name LIKE ‘#view_ddl_exception_list%’) BEGIN DROP TABLE #view_ddl_exception_list; END CREATE TABLE #view_ddl_exception_list (view_name SYSNAME NOT NULL PRIMARY KEY CLUSTERED); SELECT @sql_command = ”; SELECT @sql_command = @sql_command + ‘ BEGIN TRY SELECT TOP 1 * INTO temp_results_’ + CAST(distinct_view_list.distinct_view_list_id AS VARCHAR(MAX)) + ‘ FROM [‘ + CAST(distinct_view_list.view_schema_name AS VARCHAR(MAX)) + ‘].[‘ + CAST(distinct_view_list.view_name AS VARCHAR(MAX)) + ‘]; DROP TABLE temp_results_’ + CAST(distinct_view_list.distinct_view_list_id AS VARCHAR(MAX)) + ‘; END TRY BEGIN CATCH PRINT ‘‘The following view is broken via DML errors from a SELECT * FROM query: ‘ + CAST(distinct_view_list.view_schema_name AS VARCHAR(MAX)) + ‘.’ + CAST(distinct_view_list.view_name AS VARCHAR(MAX)) + ‘ Error Message: ‘‘ + ERROR_MESSAGE(); IF @@TRANCOUNT > 0 BEGIN ROLLBACK; END END CATCH;’ FROM #distinct_view_list distinct_view_list WHERE distinct_view_list.view_name NOT IN (SELECT view_ddl_exception_list.view_name FROM #view_ddl_exception_list view_ddl_exception_list); EXEC sp_executesql @sql_command; |
The first part of the script collects a list of views and is a simplified version of what we worked with earlier. Since all that is needed are view names, we can forgo collecting column and data type metadata.
The remainder of the script uses dynamic SQL to attempt a SELECT of the top one row from each view. If it succeeds, then nothing happens. If an error is thrown, then the CATCH block will print the error message and view name for our review when the process completes.
Note that permanent tables are used in the dynamic SQL, which guards against errors if there happens to be XML schema collections defined within the database. While uncommon, I chose here to include rather than omit them. Feel free to adjust these to temporary tables if this will not be an issue within your own database environment.
We can test this code by intentionally building a view and then breaking it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
ALTER TABLE Person.Person ADD favorite_ice_cream_flavor VARCHAR(50) NULL; GO UPDATE Person.Person SET favorite_ice_cream_flavor = ‘Mint Chocolate Chip’; GO CREATE VIEW v_employee_ice_cream AS SELECT Person.BusinessEntityID, Person.FirstName, Person.LastName, CAST(Person.favorite_ice_cream_flavor AS VARCHAR(25)) AS favorite_ice_cream_flavor FROM Person.Person; GO SELECT TOP 10 favorite_ice_cream_flavor FROM dbo.v_employee_ice_cream; |
This T-SQL creates a new column in Person.Person to store a favorite ice cream flavor. A view is then created that returns a person’s ID, name, and favorite flavor:
Since the view was not created with schema binding, we can alter the Person table with no immediate feedback:
|
ALTER TABLE Person.Person DROP COLUMN favorite_ice_cream_flavor; ALTER TABLE Person.Person ADD favorite_ice_cream_flavor XML NULL; UPDATE Person.Person SET favorite_ice_cream_flavor = ‘<ice_cream>Mint Chocolate Chip</ice_cream>’; |
By dropping and recreating the column as XML, we are creating a situation where casting the data on the fly is going to fail. Now let’s run the code we created earlier and test all views in our database for DML errors. To make testing this easier, you can find a stored procedure version here. When we do so, the following information is printed for our review:
Our code caught the error shown above and returned it as a friendly message instead. It also provided some details as to which view broke, allowing us to quickly test and verify for ourselves that it is indeed broken:
|
SELECT TOP 10 favorite_ice_cream_flavor FROM dbo.v_employee_ice_cream; |
Here is the message that is returned by SQL Server:
This code will catch any DML error that can be thrown by the view, allowing us to identify obscure or unexpected errors that may not be found otherwise. The benefit of this is that we can reduce bugs up-front, long before app changes are deployed!
Views are useful constructs that can save time and improve application maintainability when used effectively. When not schema bound, though, views can become stale and become the source of bugs. The code discussed in this article has allowed us to identify three distinct types of problems:
Adding a script that performs these tasks on a regular basis as part of a QA process can allow for views to be tested and validated before they ever see a production environment. Over time, this will reduce bugs and improve code quality as mistakes are caught immediately, rather than days or weeks later.
I have a confession to make. Why, in my last article about shortest_path in SQL Server 2019, have I used Gephi in order to illustrate the relationships, instead of using a script in R for the same purpose and demonstrate Machine Learning Services as well?
The initial plan was to use an R script; however, the R script which works perfectly in SQL Server 2017 doesn’t work in SQL Server 2019.
This is the original R script I had planned to use:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
EXEC <strong>Sp_execute_external_script</strong> @language = N‘R’, @script = N‘ require(igraph) g <- graph.data.frame(graphdf) V(g)$ label.cex <- 2 png(filename = “c:\R\plot1.png”, height = 1200, width = 1200, res = 100); plot(g, vertex.label.family = “sans”, vertex.size = 40) dev.off() ‘, @input_data_1 = N‘select LikeMember.MemberName as LikeMember, LikedMember.MemberName as LikedMember from dbo.ForumMembers as LikeMember, dbo.ForumMembers as LikedMember, Likes where Match(LikeMember-(Likes)->LikedMember)’, @input_data_1_name = N‘graphdf’ go |
The purpose of the script was only to create an image file with the relationships stored in graph tables, but in SQL Server 2019, it results in an access denied message.
If you would like to reproduce this demonstration, you can use this script file to create the database in a SQL Server 2017. You must also install the R packages needed by the script. There are many ways to do it. My favourite is this:
When you try and run the above R script. It will work well in SQL Server 2017, and fail in SQL Server 2019 with the error message you can see in the image below:
During PASS Summit, I was able to ask questions to the Microsoft employees in the Data Clinic, and they explained the differences between the two versions. The explanation hasn’t solved all the problems, but I was able to find a solution and create a workaround for this script.
While researching the workaround, I saw many examples of R scripts manipulating files and all these examples may fail in SQL Server 2019. Highlighting this problem, solution, and workaround in this article will be important to everyone using SQL Server Machine Learning and planning to migrate to SQL Server 2019.
First, I’ll review how Machine Learning in SQL Server works: it involves the execution of an external script, in R or Python, but has a flexible structure, you could create your own external language. The execution itself is started by a service called SQL Server Launchpad, isolating the execution from SQL Server itself. The image below (from What is the Machine Learning Server) illustrates how the execution architecture works, although this article will not go so deep into the architecture.
In SQL Server 2017 the Launchpad starts processes for each execution. The processes need an identity, so SQL Server dynamically creates users inside the group SQLRUserGroup. In the following image, you can see two groups, SQLRUserGroup, for SQL Server 2019 and SQLRUserGroupSQL2017 for SQL Server 2017, since I have both in the same machine.
The difference in the name is due to the instance name; SQL Server 2019 is the default instance in my machine. Due to that, its user group name has only the core name, while SQL Server 2017 is in an instance called SQL2017, so the instance name is attached to the core group name.
The processes have access to the machine resources, limited by the permissions given to the SQLRUserGroup. Since SQL Server 2017 creates many users, you shouldn’t grant permissions directly to the users, only to the group.
In order to be able to manipulate files in the file system, you only need to give the correct permissions to the group SQLRUserGroup. The image below shows the group and users for SQL Server 2017
In SQL Server 2019, the Launchpad was improved. Instead of using processes for each execution, it uses app containers. The app containers need only a single identity, so they use the same identity as the Launchpad service.
While the 2017 version, the SQLRUserGroup, had many users; in the 2019 version, it has only one, the Launchpad service account. The image below shows the group for SQL Server 2019.
Nowadays, when talking about containers immediately, we think about Docker, but not in this case. The app containers I’m mentioning here are a sandbox set of APIs introduced in Windows 8. These allow any application to create app containers and start an execution inside an app container.
The app containers work as isolated virtual machines (but much more lightweight, of course). This means that you can’t access the file system of the host (your SQL Server), neither for reading or to save files. You save any files inside the app container, and they are destroyed after the app container is released. In fact, the container is redirected to save files in a temporary folder inside the host hard drive. This increases the security, no doubt about that, but any script dealing with the file system may need changes to work in SQL Server 2019.
These app containers are created by Launchpad using the windows Sandbox API. The Launchpad doesn’t offer any way to customize the app containers creation, which could solve the problem.
You can learn more about app containers with these two links:
https://techcommunity.microsoft.com/t5/Windows-Kernel-Internals/Windows-Sandbox/ba-p/301849
https://www.malwaretech.com/2015/09/advanced-desktop-application-sandboxing.html
In order to determine this concept, you can use a script in R to access the disk and list the files. You can execute the script below in both SQL Server versions, 2017 and 2019, and compare the results. In SQL Server 2017, the script will have access to the host disk. In SQL Server 2019, the script will have access only to a fake disk, which is, in fact, a temporary folder in the host disk and will be deleted after its use by the app container.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
EXEC <strong>Sp_execute_external_script</strong> @language = N‘R’, @script = N‘data <- list.files() data2 <- data.frame(data)’, @output_data_1_name = N‘data2′ EXEC <strong>Sp_execute_external_script</strong> @language = N‘R’, @script = N‘data <- list.dirs(“..\”) data2 <- data.frame(data)’, @output_data_1_name = N‘data2′ EXEC <strong>Sp_execute_external_script</strong> @language = N‘R’, @script = N‘data <- list.dirs(“..\..”) data2 <- data.frame(data)’, @output_data_1_name = N‘data2′ |
The image below the script shows the result in SQL Server 2019.
The app containers are created during the SQL Server Machine Learning services setup. They are objects inside the Windows local directory, and such as all the objects in the local directory, they have a unique SID to identify the app container.
You can define file system permissions directly for the SIDs. The problem is: How to identify the SID’s of the app containers since they are not listed as user or groups.
Analyse two possible solutions:
You can test both solutions using a straightforward script. First, create a folder called C:\testFolder and copy some files, any files, to the folder. Try to list the files in this folder using the script below.
|
EXEC Sp_execute_external_script @language = N‘R’, @script = N‘data <- list.files(“c:\testFolder”) data2 <- data.frame(data)’, @output_data_1_name = N‘data2′ |
The All Application Container object has a fixed SID in the local directory, which is S-1-15-2-1 . You can use the application icacls to grant permission
|
icacls c:\testFolder /grant *S-1-15-2-1:(OI)(CI)F /t |
The challenge you will face when giving permissions to the App Containers SID is to discover which are the App Containers SID. There is not a direct solution for that, although, after knowing where to look, it becomes easy. SQL Server installs firewall rules in Windows Firewall in order to forbid app containers from making external contact to the network. These firewall rules are created precisely to block the app containers SID from using the network, so you can identify these SIDs by analysing these firewall rules.
If you look at the firewall rules, you can see that the app containers are blocked.
The firewall UI doesn’t provide the SIDs, however. You need to dig deeper using PowerShell to retrieve the SIDs from the firewall rules.
The PowerShell command is a combination of the cmdlet Get-NetFirewallRule, to retrieve the firewall rules and the cmdlet Get-NetFirewallApplicationFilter in order to retrieve, from each rule, the app container filter information.
The complete PowerShell command line is this:
|
(Get-NetFirewallRule | Where-Object { ($ _.Direction -eq “Outbound”) -and ($ _.DisplayName -like “*appcontainer*”)}) | %{ ($ _ | Get-NetFirewallApplicationFilter) | %{Write-Output $ _.Package } } |
Analyse this command line in more detail:
Get-NetFirewallRule will retrieve all the firewall rules in Windows FirewallWhere-Object filters the firewall rules, retrieving only the outbound rules which contains ‘appcontainer’ in the name%{ } is a shortcut to foreach-object so that the instructions will be executed for each firewall ruleGet-NetFirewallApplicationFilter is executed for each firewall rule, having the firewall rule ($ _ ) as a parameter% { } is used again to run one line for each application filter foundWrite-Output shows the value of the Package property on each application filter
Having discovered the SIDs, you need to set the permission to the folder. The instruction will look like the one below for each SID.
|
ICACLS “C:\testFolder” /grant “*S-1-15-2-1853514363-3573294594-1452771951-225645021-3819702349-824866239-3302992986″:(OI)(CI)F /t |
You could copy and paste all the sids returned to create a batch file to set the permissions, but PowerShell can do all of this you as well. The following script prompts you for a file path, the one where you want Machine Learning to have access, and sets the permissions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Param ( [Parameter( Mandatory)] [string] $ FolderName ) Get-NetFirewallRule | Where-Object { $ _.Direction -eq “Outbound” -and $ _.DisplayName -like “*appcontainer*” } | ForEach-Object { $ _ | Get-NetFirewallApplicationFilter | ForEach-Object { $ Acl = Get-Acl $ FolderName $ Sec= New-Object System.Security.Principal.SecurityIdentifier($ _.Package) $ AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule( $ Sec, “FullControl”, (1 -bor 2), 0, “Allow” ) $ Acl.SetAccessRule($ AccessRule) $ Acl | Set-Acl $ FolderName } # end 2nd FE-O } # end 1st FE-O |
The script is using COM objects System.Security.Principal.SecurityIdentifier and System.Security.AccessControl.FileSystemAccessRule in order to set the access rule on the folder.
After running the script, take a look at the security properties of the folder, and the R script should now work from SQL Server 2019.
Although I have identified two solutions, it’s interesting to notice Microsoft is moving SQL Server Machine Learning towards a more secure environment, avoiding network and file system access. Considering this, you may like to use an alternative solution.
There are some options, all based on returning the image as an output parameter to SQL Server. Here is one of the options.
A filetable can map a disk folder as a table in SQL Server. This a very good solution when you need to deal with files. The script in R will see the table as a regular table. While reading or inserting into the table, the script will be reading and inserting from/to the disk.
It’s a very good solution for a permanent environment, however, it has some problems:
The Filetable technology was created in SQL Server 2016, based on the filestream technology which was built on the previous version.
A filetable solution needs some administrative care; for this reason, this solution can only be used after enabling it on a server configuration level. Follow these steps to enable this solution:
|
EXEC sp_configure ‘filestream access level’, 2 RECONFIGURE |
Value 1 enables filestream only for T-SQL access, which is not enough for filetable. Value 2 enables filestream for T-SQL and I/O access.
In order to support filetable, you need to make some changes to the database. You need to create a filegroup to contain the filestream data, configure a special file, which will point to a folder and change some database settings.
Here is the T-SQL to execute for the database. Be sure to delete the c:\R folder before running this code:
|
ALTER DATABASE graphdemo ADD filegroup filestreamgroup CONTAINS filestream ALTER DATABASE graphdemo ADD FILE (NAME=fs, filename=‘c:\R’) TO filegroup filestreamgroup |
|
CREATE TABLE images AS filetable WITH ( filetable_directory = ‘Images’ ); go |
The filetable you created, Images, is linked to a folder. Which folder? If you check the path c:\R, which was used to configure the database file, you will find some files and folders, but it’s not easy to understand them. So, what’s missing?
Follow these steps to see:
|
select * from Images |
The filetable is empty.
|
select * from Images |
This time the table has one row, exactly the row for the new file you copied. The filetable is linked with the folder. So, if you insert a record inside the filetable, the record will be a file available in the folder, in this way you can save to disk the image generated by the R script
After preparing the filetable environment, the new script will be this one:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DECLARE @img <em>VARBINARY</em>(max) EXEC <strong>Sp_execute_external_script</strong> @language = N‘R’, @script = N‘ require(igraph) require(hexView) g <- graph.data.frame(graphdf) V(g)$ label.cex <- 2 png(filename = “plot1.png”, height = 1200, width = 1200, res = 100); plot(g, vertex.label.family = “sans”, vertex.size = 40) dev.off() img <- readRaw(“plot1.png”) imageContent <- img$ fileRaw’, @input_data_1 = N‘select LikeMember.MemberName as LikeMember, LikedMember.MemberName as LikedMember from dbo.ForumMembers as LikeMember, dbo.ForumMembers as LikedMember, Likes where Match(LikeMember-(Likes)->LikedMember)’, @input_data_1_name = N‘graphdf’, @params = N‘@imageContent varbinary(max) OUTPUT’ , @imageContent = @img output; INSERT INTO images (NAME, file_stream) VALUES (‘test.png’, @img) go |
The script is very similar to the initial one; however, this time, it results in an output variable with the image.
Here are the differences between this script and the original:
varbinary(max) variable which will be used as an outputhexView, must be installed in order to do additional manipulations in the file (yes, you will still use a file)imageContent) is defined as a parameter using the @params argument of the sp_execute_external_scriptThe result of the execution of the script above will be a new file in the folder, you can check by accessing \localhost\MSSQLSERVER\RFILES\Images
During my research on the web, I saw many examples of R scripts dealing with the file system. These scripts which are running nowadays in SQL Server 2017 will break in SQL Server 2019 unless one of the changes explained in this article is applied.
You can choose to fix the file system permission or start to avoid direct file system access from the R script. I believe Microsoft will provide a fix for this problem soon, what probably will be an easier way to give permissions to the app container SIDs
Over the years, you probably have experienced or heard that using user-defined functions (UDF’s) do not scale well as the number of rows processed gets larger and larger. Which is too bad, because we have all heard that encapsulating your code into modules promotes code reuse and is a good programming practice. Now the Microsoft SQL Server team have added a new feature to the database engine in Azure SQL Database and SQL Server 2019 that allows UDF’s performance to scale when processing large recordsets. This new feature is known as T-SQL Scalar UDF Inlining.
T-SQL Scalar UDF Inlining is one of many new features to improve performance that was introduced in the Azure SQL Database and SQL Server 2019. This new feature contains many options available in the Intelligent Query Processing (IQP) feature set. Figure 1 from Intelligent Query Processing in SQL Databases shows all the IQP features introduced in Azure SQL Database and SQL Server 2019, as well as features that originally were part of the Adaptive Query Processing feature set that was included in the older generation of Azure SQL Database and SQL Server 2017.
Figure 1: Intelligent Query Processing
The T-SQL Scalar UDF Inlining feature will automatically scale UDF code without having to make any coding changes. All that is needed is for your UDF to be running against a database in Azure SQL Database or SQL Server 2019, where the database has the compatibility level set to 150. Let me dig into the details of the new inlining feature a little more.
The new T-SQL Scalar UDF Inlining feature will automatically change the way the database engine interprets, costs, and executes T-SQL queries when a scalar UDF is involved. Microsoft incorporated the FROID framework into the database engine to improve the way scalar UDFs are processed. This new framework refactors the imperative scalar UDF code into relational algebraic expressions and incorporates these expressions into the calling query automatically.
By refactoring the scalar UDF code, the database engine can improve the cost-based optimization of the query as well as perform set based optimization that allows the UDF code to go parallel if needed. Refactoring of scalar UDFs is done automatically when a database is running under compatibility level 150. Before I dig into the new scalar UDF inlining feature, let me review why scalar UDF’s are inherently slow, and discuss the differences between imperative and relational equivalent code.
When running a scaler UDF on a database with a compatibility level set to less than 150, they just don’t scale well. By scale, I mean they work fine for a few rows but run slower and slower as the number of rows processed gets larger and larger. Here are some of the reasons why scalar UDF’s don’t work well with large recordsets.
Imperative and Relational Equivalent Code
Scalar UDFs are a great way to modularize your code to promote reuse, but all too often they contain procedural code. Procedural code might contain imperative code such as variable declarations, IF/ELSE structures, as well as WHILE looping. Imperative code is easy to write and read, hence why imperative code is so widely used when developing code for applications.
The problem with imperative code is that it is hard to optimize, and therefore query performance suffers when imperative code is executed. The performance of imperative code is fine when a small number of rows are involved, but as the row count grows, the performance starts to suffer. Because of this, you should not use them for larger record sets if they are executed on a database running with a compatibility less than 150. With the introduction of version 15.x of SQL Server, the scaling problem associated with UDFs has been solved by the refactoring of imperative code using a new optimization technique known as the FROID framework.
The FROID framework refactors imperative code into a single relational equivalent query. It does this by analyzing the scalar UDF imperative code and then converts blocks of imperative code into relational equivalent algebraic expressions. These relational expressions are then combined into a single T-SQL statement using APPLY operators. Additionally, the FROID framework looks for redundant or unused code and removes it from the final execution plan of the query. By converting the imperative code in a scalar UDF into re-factored relational expressions, the query optimizer can perform set-based operations and use parallelism to improve the scalar UDF performance. To further understand the difference between imperative code and relational equivalent code, let me show you an example.
Listing 1 contains some imperative code. By reviewing this listing, you can see it includes a couple of DECLARE statements and some IF/ELSE logic.
Listing 1: Imperative Code Example
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @Sex varchar(10) = ‘Female’; DECLARE @SexCode int; IF @Sex = ‘Female’ SET @SexCode = 0 ELSE IF @Sex = ‘Male’ SET @SexCode = 1; ELSE SET @SexCode = 2; SELECT @SexCode AS SexCode; |
I have then re-factored the code in Listing 1 into a relational equivalent single SELECT statement in Listing 2, much like the FROID framework might doing it when compiling a scalar UDF.
Listing 2: Relational Code Example
|
SELECT B.SexCode FROM (SELECT ‘Female’ AS Sex) A OUTER APPLY (SELECT CASE WHEN A.Sex = ‘Female’ THEN 0 WHEN A.Sex = ‘Male’ THEN 1 ELSE 2 END AS SexCode) AS B; |
By looking at these two examples, you can see how easy it is to read the imperative code in Listing 1 to see what is going on. Whereas in Listing 2, which contains the relational equivalent code, requires a little more analysis/review to determine exactly what is happening.
DECLARE or SET statementSELECT statementIF/ELSE logicRETURN statementsEXISTS and ISNULLThe two listings found in this section only logically demonstrate how the FROID framework might convert imperative UDF code into relational equivalent code using the FROID framework. For more detailed information on the FROID framework, I suggest you read this technical paper.
In order to see FROID optimization in action, let me show you an example that compares the performance of a scalar UDF running with and without FROID optimization.
To test how a scalar UDF would perform with and without FROID optimization, I will run a test using the sample WorldWideImportersDW database (download here). In that database, I’ll create a scalar UDF called GetRating. The code for this UDF can be found in Listing 3.
Listing 3: Scalar UDF that contains imperative code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR ALTER FUNCTION dbo.GetRating(@CityKey int) RETURNS VARCHAR(13) AS BEGIN DECLARE @AvgQty DECIMAL(5,2); DECLARE @Rating VARCHAR(13); SELECT @AvgQty = AVG(CAST(Quantity AS DECIMAL(5,2))) FROM Fact.[Order] WHERE [City Key] = @CityKey; IF @AvgQty / 40 >= 1 SET @Rating = ‘Above Average’; ELSE SET @Rating = ‘Below Average’; RETURN @Rating END |
By reviewing the code in Listing 3 you can see that I am creating my scalar UDF that I will be using for testing. This function calculates a rating for a [City Key] value. The rating returned is either “Above Average” or “Below Average” based on 40 being the average rating. Note that this UDF contains imperative code.
In order to test how scalar inlining can improve performance I will be running the code in Listing 4.
Listing 4: Code to test performance of scalar UDF
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
– Turn on Time Statistics SET STATISTICS TIME ON; GO USE WideWorldImportersDW; GO – Set Compatibility level to 140 ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 140; GO – Test 1 SELECT DISTINCT ([City Key]), dbo.GetRating([City Key]) AS CityRating FROM Dimension.[City] – Set Compatibility level to 150 ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 150; GO – Test 2 SELECT DISTINCT ([City Key]), dbo.GetRating([City Key]) AS CityRating FROM Dimension.[City] GO |
The code in Listing 4 runs two tests. The first test (Test 1) calls the scaler UDF dbo.GetRating using compatibility level 140 (SQL Server 2017). For the second test (Test 2), I only changed the compatibility level to 150 (SQL Server 2019) and ran the same UDF as Test 1 without making any coding changes to the UDF.
When I run Test 1 in Listing 4, I get the execution statistics shown in Figure 2 and the execution plan shown in Figure 3.
Figure 2: Execution Statistics for Test 1 
Figure 3: Execution plan when using compatibility level 140 using Test 1
Prior to reviewing the time statistics and execution plan for Test 1 let me run Test 2. The time statistics and execution plan for Test 2 can be found in Figure 4 and Figure 5, respectfully.
Figure 4: Execution Statistics for Test 2
Figure 5: Execution plan when using compatibility level 150 using Test 2
The only change I made between Test 1 and Test 2 was to change the compatibility level from 140 to 150. Let me review how the FROID optimization changed the execution plan and improved the performance when I executed my test using compatibility level 150.
Before running the two different tests, I turned on statistics time. Figure 6 compares the time statistics between the two different tests.
Figure 6: CPU and Elapsed Time Comparison Between Test 1 and Test 2
As you can see, when I executed the Test 1 SELECT statement in Listing 4 using compatibility level 140, the CPU and elapsed time took a little over 30 seconds. Whereas, when I changed the compatibility level to 150 and ran the Test 2 SELECT statement in Listing 4, my CPU and Elapsed time used just over 1second of time each. As you can see, Test 2, which used compatibility level 150 and the FROID framework, ran magnitudes faster than List 1 which ran under compatibility 140 without the FROID framework optimization. The improvement I gained using the FRIOD framework and compatibility level 150 achieved this performance improvement without changing a single line of code in my test scalar UDF. To better understand why the time comparisons were so drastically different between these two executions of the same SELECT statement, let me review the execution plans produced by each of these test SELECT queries.
If you look at Figure 3, you will see a simple execution plan when the SELECT statement was run under compatibility 140. This execution plan didn’t go parallel and only includes two operators. All the work related to calculating the city rating in the UDF using the data in the Fact.[Order] table is not included in this execution plan. To get the rating for each city, my scalar function had to run multiple times, once for every [City Key] value found in the Dimension.[City] table. You can’t see this in the execution plan, but if you monitor the query using an extended event, you can verify this. Each time the database engine needs to invoke my UDF in Test 1, a context switch has to occur. The cost of the row by row operation nature of calling my UDF over and over again causes the query in Test 1 to run slow.
If we look at the execution plan in Figure 5, which is for Test 2, you see a very different plan as compared to Test 1. When the SELECT statement in Test 2 was run, it ran under compatibility level 150, which allowed the scalar function to be inlined. By inlining the scalar function, FROID optimization converted my scalar UDF into a relational operation which allowed my UDF logic to be included in the execution plan of the calling SELECT statement. By doing this, the database engine was able to calculate the rating value for each [City Key] using a set-based operation, and then joins the rating value to all the cities in the Dimension.[City] table using an inner join nested loop operation. By doing this set based operation in Test 2, my query runs considerably faster and uses fewer resources than the row by row nature of my Test 1 query.
Not all scalar function can be inlined. If a scalar function contains coding practices that cannot be converted to relational algebraic expressions by the FRIOD framework, then your UDF will not be inlined. For instance, if a scalar UDF contains a WHILE loop, then the scalar function will not be inlined. To demonstrate this, I’m going to modify my original UDF code so it contains a dummy WHILE loop. My new UDF is called dbo.GetRating_Loop and can be found in Listing 5.
Listing 5: Scalar UDF containing a WHILE loop
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE OR ALTER FUNCTION dbo.GetRating_Loop(@CityKey int) RETURNS VARCHAR(13) AS BEGIN DECLARE @AvgQty DECIMAL(5,2); DECLARE @Rating VARCHAR(13); – Dummy code to support WHILE loop DECLARE @I INT = 0; WHILE @I < 1 BEGIN SET @I = @I + 1; END SELECT @AvgQty = AVG(CAST(Quantity AS DECIMAL(5,2))) FROM Fact.[Order] WHERE [City Key] = @CityKey; IF @AvgQty / 40 >= 1 SET @Rating = ‘Above Average’; ELSE SET @Rating = ‘Below Average’; RETURN @Rating END |
By reviewing the code in Listing 5, you can see I added a dummy WHILE loop at the top of my original UDF. When I run this code using the code in Listing 6, I get the execution plan in Figure 7.
Listing 6: Code to run dbo.GetRating_Loop
|
1 2 3 4 5 6 7 8 9 10 |
USE WideWorldImportersDW; GO – Set Compatibility level to 150 ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 150; GO – Test UDF With WHILE Loop SELECT DISTINCT ([City Key]), dbo.GetRating_Loop([City Key]) AS CityRating FROM Dimension.[City] GO |
Figure 7: Execution plan created while execution Listing 6.
By looking at the execution plan in Figure 7, you can see that my new UDF didn’t get inlined. The execution plan for this test looks very similar to the execution plan I got when I ran my original UDF in Listing 3 under database compatibility level 140. This example shows not all scalar UDF functions will be inlined. Just those scalar UDF that use only the functionality support by the FRIOD framework will be inline.
With this new version of SQL Server, the design team wanted to make sure you could disable any new features at the database level or statement level. Therefore, you can use the code in Listing 6 or 7 to disable scalar UDF inlining. Listing 6 shows how to disable scalar UDF inlining at the database level.
Listing 6: Disabling inlining at the database level
|
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF; |
Listing 7 shows how to disable scalar inlining when the scalar UDF is created.
Listing7: Disabling when defining UDF
|
CREATE FUNCTION dbo.MyScalarUDF (@Parm int) RETURNS INT WITH INLINE=OFF ... |
If you want to make your Scalar UDF run faster without making any coding changes, then SQL Server 2019 is for you. With this new version of SQL Server, the FROID framework was added. This framework will refactor a scalar UDF function into relational equivalent code that can be placed directly into the calling statement’s execution plan. By doing this, a scalar UDF is turned into a set-based operation instead of being called for every candidate row. All it takes to have a scalar UDF refactored is to set your database to compatibility level 150.