by Contributed | Jul 16, 2023 | Technology
This article is contributed. See the original author and article here.
Recently, I encountered an intriguing scenario where our customer needed to dynamically create views based on multiple tables, allowing them to efficiently filter and aggregate data. The challenge arose when dealing with varying amounts of data, which required careful consideration for performance optimization. Through this article, I would like to share my insights and present a stored procedure that offers additional options to address this dynamic view creation requirement effectively.
Script Overview:
The “SearchAndView” stored procedure accepts several input parameters that define the behavior and structure of the dynamic view to be created. Let’s take a closer look at each parameter and its significance:
1. @SearchField: This parameter specifies the field on which the search operation will be performed within the tables. If provided, the dynamic view will only include rows that match the given criteria.
2. @SearchText: This parameter holds the search text that will be matched against the @SearchField. It complements the search functionality and allows for targeted data retrieval.
3. @TableList: This parameter contains a comma-separated list of tables from which data will be retrieved and combined in the dynamic view. By specifying the desired tables, users can tailor the view to their specific needs.
4. @TableSchema: This parameter defines the schema under which the tables reside. It ensures the correct retrieval of table data within the specified schema context.
5. @ViewSchema: This parameter represents the schema under which the dynamic view will be created. If the schema doesn’t exist, the script dynamically creates it to accommodate the view.
6. @ViewName: This parameter defines the name of the dynamic view. It should be unique within the specified @ViewSchema and serves as an identifier for accessing the combined data.
7. @FieldList: An optional parameter, @FieldList, allows users to specify a list of fields they want to include in the dynamic view. If not provided, all fields from the tables will be included by default.
8. @TopCount: Another optional parameter, @TopCount, determines the maximum number of rows to include in the dynamic view. By setting this value, users can control the amount of data returned and improve query performance.
9. @AddUniqueField: This binary parameter, when set to 1, adds an additional column called “UniqueField” to each row of the dynamic view. The value of this field is generated using the NEWID() function and ensures uniqueness within the view’s result set.
Script Execution:
The “SearchAndView” stored procedure follows a structured workflow to create the dynamic view. Here’s an overview of the execution steps:
1. Parameter Validation: The script first validates the input parameters, ensuring that essential values like @TableSchema, @ViewSchema, @ViewName, and @TableList are provided. If any required parameter is missing, an error is raised, and the procedure is terminated.
2. Schema Creation: The script checks if the specified @ViewSchema exists. If not, it dynamically creates the schema using the CREATE SCHEMA statement. This step ensures that the view is created within the desired schema context.
3. View Existence Check: Before creating the dynamic view, the script verifies if a view with the same @ViewName already exists within the specified @ViewSchema. If found, the existing view is dropped to avoid conflicts during view creation.
4. Dynamic View Creation: The script constructs the CREATE VIEW statement using the specified @ViewSchema, @ViewName, and other parameters. It then iterates over the tables provided in @TableList, generating SELECT statements for each table. The statements include the necessary JOIN or UNION ALL operations to combine data from multiple tables.
5. Optional Functionality: If @FieldList is provided, it is included in the SELECT statement to specify the desired fields in the dynamic view. If @TopCount is specified, a TOP clause is added to limit the number of rows returned. If @AddUniqueField is set to 1, the NEWID() function is used to generate a unique identifier column, which is included in each row of the dynamic view.
6. Dynamic SQL Execution: The constructed SQL statement is executed using sp_executesql to create the dynamic view within the specified @ViewSchema and @ViewName.
7. Error Handling: The script includes error handling logic within a TRY…CATCH block. If any error occurs during view schema creation, dropping the existing view, or creating the dynamic view, an error message is raised, providing details about the encountered error.
CREATE PROCEDURE SearchAndView (
@SearchField VARCHAR(100),
@SearchText VARCHAR(100),
@TableList VARCHAR(MAX),
@TableSchema VARCHAR(100),
@ViewSchema VARCHAR(100),
@ViewName VARCHAR(100),
@FieldList VARCHAR(MAX) = NULL,
@TopCount INT = NULL,
@AddUniqueField BIT = 0
)
AS
BEGIN
-- Check if TableSchema is empty
IF @TableSchema IS NULL OR LTRIM(RTRIM(@TableSchema)) = ''
BEGIN
RAISERROR('Error: TableSchema is required.', 16, 1)
RETURN;
END
-- Check if ViewSchema is empty
IF @ViewSchema IS NULL OR LTRIM(RTRIM(@ViewSchema)) = ''
BEGIN
RAISERROR('Error: ViewSchema is required.', 16, 1)
RETURN;
END
-- Check if ViewName is empty
IF @ViewName IS NULL OR LTRIM(RTRIM(@ViewName)) = ''
BEGIN
RAISERROR('Error: ViewName is required.', 16, 1)
RETURN;
END
-- Check if TableList is empty
IF @TableList IS NULL OR LTRIM(RTRIM(@TableList)) = ''
BEGIN
RAISERROR('Error: TableList is required.', 16, 1)
RETURN;
END
DECLARE @SQLStatement NVARCHAR(MAX)
DECLARE @ErrorMessage NVARCHAR(4000)
-- Check if the view schema exists, if not, create it
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @ViewSchema)
BEGIN
SET @SQLStatement = 'CREATE SCHEMA ' + QUOTENAME(@ViewSchema)
BEGIN TRY
EXEC sp_executesql @SQLStatement
END TRY
BEGIN CATCH
-- Handle any errors that occurred during execution
SET @ErrorMessage = ERROR_MESSAGE()
RAISERROR('Error occurred while creating the view schema. Error message: %s', 16, 1, @ErrorMessage)
RETURN
END CATCH
END
-- Check if the view already exists and drop it if necessary
IF EXISTS (SELECT * FROM sys.views WHERE name = @ViewName AND SCHEMA_NAME(schema_id) = @ViewSchema)
BEGIN
SET @SQLStatement = 'DROP VIEW ' + QUOTENAME(@ViewSchema) + '.' + QUOTENAME(@ViewName)
BEGIN TRY
EXEC sp_executesql @SQLStatement
END TRY
BEGIN CATCH
-- Handle any errors that occurred during execution
SET @ErrorMessage = ERROR_MESSAGE()
RAISERROR('Error occurred while dropping the existing view. Error message: %s', 16, 1, @ErrorMessage)
RETURN
END CATCH
END
-- Create the view
SET @SQLStatement = 'CREATE VIEW ' + QUOTENAME(@ViewSchema) + '.' + QUOTENAME(@ViewName) + ' AS '
-- Split the table list into separate table names
DECLARE @Tables TABLE (RowNumber INT IDENTITY(1,1), TableName VARCHAR(100))
INSERT INTO @Tables (TableName)
SELECT value FROM STRING_SPLIT(@TableList, ',')
-- Check if FieldList is empty, if so, assign default value '*'
IF @FieldList IS NULL OR LTRIM(RTRIM(@FieldList)) = ''
SET @FieldList = '*'
-- Generate the SELECT statement for each table
DECLARE @Counter INT = 1
DECLARE @NumTables INT = (SELECT COUNT(*) FROM @Tables)
WHILE @Counter <= @NumTables
BEGIN
SET @SQLStatement += 'SELECT '
IF @TopCount IS NOT NULL
BEGIN
SET @SQLStatement += 'TOP(' + CAST(@TopCount AS NVARCHAR(10)) + ') '
END
IF @AddUniqueField = 1
BEGIN
SET @SQLStatement += 'NEWID() AS UniqueField, ' + @FieldList
END
ELSE
BEGIN
SET @SQLStatement += @FieldList
END
SET @SQLStatement += ' FROM ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME((SELECT TableName FROM @Tables WHERE RowNumber = @Counter))
IF @SearchField '' AND @SearchText ''
BEGIN
SET @SQLStatement += ' WHERE ' + QUOTENAME(@SearchField) + ' = ''' + @SearchText + ''''
END
IF @Counter < @NumTables
SET @SQLStatement += ' UNION ALL '
SET @Counter += 1
END
-- Execute the dynamic SQL statement
BEGIN TRY
EXEC sp_executesql @SQLStatement
END TRY
BEGIN CATCH
-- Handle any errors that occurred during execution
SET @ErrorMessage = ERROR_MESSAGE()
RAISERROR('Error occurred while creating the view. Error message: %s', 16, 1, @ErrorMessage)
END CATCH
END
How to call the stored procedure
BEGIN TRY
EXEC SearchAndView 'name', 'John', 'Table1,Table2,Table3','dbo','MyView', 'MyView','Age,Name',Null,1
END TRY
BEGIN CATCH
-- Handle any errors that occurred during execution
PRINT 'Error occurred while executing the stored procedure.'
PRINT 'Error message: ' + ERROR_MESSAGE()
END CATCH
WITH cte(AgeMax, Number)
as
(
select MAX(AGE) as AgeMax,COUNT(*) as Number from [MyView].[MyView]
)
select AgeMax, Number from cte
Tables and data example
-- Create EnglishNames table
CREATE TABLE EnglishNames (
Name varchar(100)
);
-- Insert English names into EnglishNames table
INSERT INTO EnglishNames (Name)
VALUES
('John'), ('William'), ('James'), ('Charles'), ('George'),
('Thomas'), ('Joseph'), ('Robert'), ('Michael'), ('David'),
('Richard'), ('Daniel'), ('Paul'), ('Mark'), ('Donald'),
('Edward'), ('Steven'), ('Brian'), ('Kevin'), ('Jason'),
('Jeffrey'), ('Scott'), ('Kenneth'), ('Anthony'), ('Eric'),
('Stephen'), ('Timothy'), ('Patrick'), ('Gregory'), ('Matthew'),
('Andrew'), ('Christopher'), ('Jonathan'), ('Ronald'), ('Dennis'),
('Jerry'), ('Gary'), ('Frank'), ('Raymond'), ('Peter'),
('Samuel'), ('Charles'), ('Henry'), ('Larry'), ('Jose'),
('Albert'), ('Arthur'), ('Ryan'), ('Carl'), ('Roger'),
('Benjamin'), ('Wayne'), ('Billy'), ('Walter'), ('Harry'),
('Howard'), ('Philip'), ('Stephen'), ('Vincent'), ('Jeremy'),
('Ralph'), ('Sean'), ('Johnny'), ('Bobby'), ('Louis'),
('Eugene'), ('Randy'), ('Russell'), ('Gerald'), ('Russell'),
('Keith'), ('Philip'), ('Franklin'), ('Alan'), ('Willie'),
('Roy'), ('Jesse'), ('Frederick'), ('Dustin'), ('Victor'),
('Glenn'), ('Alfred'), ('Leonard'), ('Melvin'), ('Lee'),
('Eddie'), ('Reginald'), ('Bill'), ('Wayne'), ('Martin'),
('Craig'), ('Dale'), ('Randall'), ('Bradley'), ('Jesus'),
('Leroy'), ('Curtis'), ('Warren'), ('Clarence'), ('Jerome'),
('Lewis'), ('Don'), ('Nathan'), ('Calvin'), ('Keith');
-- Create Table1
CREATE TABLE Table1 (
Name varchar(100),
Age int
);
-- Insert random data into Table1 using English names
DECLARE @Counter INT = 1;
WHILE @Counter <= 10000
BEGIN
INSERT INTO Table1 (Name, Age)
SELECT
Name,
ABS(CHECKSUM(NEWID())) % 100 + 1 -- Random age between 1 and 100
FROM
EnglishNames
ORDER BY NEWID();
SET @Counter += 1;
END;
-- Create Table2
CREATE TABLE Table2 (
Name varchar(100),
Age int
);
-- Insert random data into Table2 using English names
SET @Counter = 1;
WHILE @Counter <= 10000
BEGIN
INSERT INTO Table2 (Name, Age)
SELECT
Name,
ABS(CHECKSUM(NEWID())) % 100 + 1 -- Random age between 1 and 100
FROM
EnglishNames
ORDER BY NEWID();
SET @Counter += 1;
END;
-- Create Table3
CREATE TABLE Table3 (
Name varchar(100),
Age int
);
-- Insert random data into Table3 using English names
SET @Counter = 1;
WHILE @Counter <= 10000
BEGIN
INSERT INTO Table3 (Name, Age)
SELECT
Name,
ABS(CHECKSUM(NEWID())) % 100 + 1 -- Random age between 1 and 100
FROM
EnglishNames
ORDER BY NEWID();
SET @Counter += 1;
END;
by Contributed | Jul 14, 2023 | Technology
This article is contributed. See the original author and article here.
Last month, I presented at Women in Tech Global Conference on the topic. During the conference and afterwards, I received a lot of queries on:
- Do I as a Technologist have a role to play in ‘Sustainability’?
- What is Technology’s role in Sustainability?
- How is Technology getting influenced by ‘Sustainability’?
In this blog, I am answering these frequently asked questions.
We often relate to Sustainability as an initiative or agenda through which carbon footprint of any product is shared and some organizations share their commitment to reduce the same.
Here is the high-level summary of the journey any enterprise or business function needs to take to become sustainable. It starts with measuring the current environmental impact of the business, which enables monitoring and leads to corrective action and thereby creating a difference by minimizing environmental impact.
What is ‘Environmental Impact’? Carbon emissions, Waste generation, water consumption and land use are key impacts to ‘Environment’ that any industrialization process brings along, digital technology included.
To make this relatable, and understand how sustainability can be factored into every business process, let’s wear a hat of Garments industry Customer.
Just imagine, you come across a great deal (in terms of price):
|
Your deciding factors primarily will be:
- Value of Product
- Price, you pay for it (in terms of money)
|
Now what if some additional information is provided:
With this additional information, the decision of purchase will add an entirely new dimension, to consider the ‘Environmental Impact’ of this purchase decision. It will add additional factors:
- Is the ‘Environmental Impact’ of this product worth the value?
- What are the alternative options?
- How can I maximize the usage of this product to minimize the ‘Environmental Impact’?
This transparency will lead the Garment manufacturers to explore the best available options across their whole business process from sourcing through manufacturing, distribution, and operations to ensure minimal environmental impact, there by staying competitive. This leads to prioritization for Sustainability at each stage of business enabled by technology.
To summarize, if we start measuring and sharing the environmental impact of every business, this will lead to consciousness and drive towards minimizing the impact.
These insights will lead to efficiencies across the product lifecycle leveraging the overall ecosystem. Each participant in this ecosystem has a role here in terms of sustainability, be it producer, operator, or consumer.
On the same lines, as a technologist we need to know:
- What is the environmental impact of the business application we are supporting / leveraging?
- How is this impact compared to other products available in the market?
- How can you optimize the usage of these products, thereby minimizing the ‘Environmental Impact’?
So, whether you are a technology consumer or producer, we all have a role to play to ensure environmental impact is taken into account at every decision point.
We all have a role to play and responsibility here.
Who can leverage these solutions and resources?
Any technology customer needs to be conscious about the environmental impact their business is generating, thereby making a conscious decision during every stage of their business, be it procurement, operations, customer support or even disposition.
As of now this is heavily prioritized by the regional and industry-based compliance and competitive landscape, but it is not far that every business decision will be made with awareness and consideration of impact and responsibility towards the environment.
All these products and offerings enabling Sustainability awareness and transparency are at initial stages right now but are developing on rapid pace owing to the growing awareness and demand of having this transparency in addition to the Dollar price.
by Contributed | Jul 14, 2023 | Technology
This article is contributed. See the original author and article here.
We are excited to announce the public preview of new alerts for IT admins managing Cloud PCs in Intune to better inform them about Cloud PCs in a grace period. This capability is available for Windows 365 Enterprise customers today!
We heard you. You want to receive proactive alerts on Cloud PCs so you can easily take appropriate actions based on this information. This can help reduce risk in situations where an IT admin may not be fully aware of Windows 365 provisioning controls and, therefore, make changes to user licensing or group membership that inadvertently trigger the grace period. Then you need this capability to extend to automated scripts, ensuring comprehensive coverage and proactive notification.
With this Windows 365 IT admin alerts, an email is delivered whenever a Cloud PC enters the grace period state. This provides admins with greater awareness of their environment so they can take appropriate actions and acts as a preventive measure against unintended Cloud PC deprovisioning.
Windows 365 IT admin alerts offer the following features:
- Admins can establish and customize system-based alerts for Cloud PCs in the grace period based on their preferences.
- IT admins can define alert rules by selecting from the available options, setting thresholds, specifying frequency, and choosing notification channels for receiving alerts.
- IT admins are able to assess their environment and take informed decisions to either reprovision or end the grace period for specific Cloud PCs as needed.
Enabling the functionality to generate alerts when a Cloud PC enters the grace period is a critical, precautionary measure. It acts as a safety net in scenarios such as Cloud PC license expiration or inadvertent changes made by IT admins to groups, resulting in a Cloud PC being set to deprovision within seven days.
Why is this important? Windows 365 Enterprise users are granted a seven-day grace period to continue using a Cloud PC once it enters that state. After the grace period elapses, the user is logged off, loses access to the Cloud PC, and the Cloud PC undergoes deprovisioning. Deprovisioning is a significant and irreversible action. By proactively notifying IT admins, unnecessary deprovisioning of Cloud PCs can be mitigated.
The Alerts (preview) in Microsoft Intune
In the Microsoft Intune admin center, under Tenant admin, you can review the alert history and monitor the status of a Cloud PC alert event, including details such as severity, state, and date.
Screenshot of Tenant admin in Alerts (preview) menu
Easy-to-understand alert insights
The event summary page provides a more detailed overview of the specific alert event that needs attention. This enables you to promptly investigate issues related to Cloud PCs in the grace period and gain a comprehensive understanding of the impact.
Screenshot of Alerts (preview) menu with a red highlight on the reports “Show all Cloud PCs in grace period”
When you select Show all Cloud PCs in grace, you are redirected to the corresponding alert event page, as displayed below. This page offers additional information about the alert event, enabling you to take appropriate actions to resolve the issue.
Screenshot of All Cloud PCs tab under the Windows 365 menu with a red highlight over the status of devices in grace period
If you select In grace period for a particular Cloud PC, a fly-out will appear, providing details about the impact of Cloud PCs in the grace period. You can then choose to either Reprovision Cloud PC or End the grace period.
Screenshot of CPC-SB pop up menu showing the option to “reprovision Cloud PC” or “end grace period”
Managing alert rules and email notifications
With this new capability, you have the flexibility to customize and enable or disable the alert rules, including conditions, settings, and notifications, depending on their specific requirement.
Additionally, you have the flexibility to configure your preferred notification methods for events by choosing options such as portal pop-up and email. We also provide support for email localization, allowing you to customize the language in which you prefer to receive alert notifications.
Screenshot of Alerts (preview) under the Alert Rules tab showing the optional notification methods
Screenshot of the Cloud PCs in grace period menu under the Alert rules tab showing more details on notification options
Prerequisites and what’s next
Windows 365 system-based alerts are currently available for Windows 365 Enterprise customers and only with Microsoft Intune. The account needs to have the Intune Global Admin, Intune Admin, or Windows 365 Admin roles assigned.
Enhanced IT admin alerts for Cloud PCs that are unable to connect are coming soon. You’ll also soon have the ability to proactively notify IT admins when Cloud PCs encounter issues such as unhealthy hosts, persistent connection errors, suspected infrastructure problems, or other systemic issues. These new capabilities will also provide valuable insights to assist in resolving the problem promptly.
For a demo on this new alerts capability for Cloud PCs in a grace period, now in public preview, please check out this video:
Learn more
Looking to see what the latest capabilities in Windows 365 alerts look like up close? See our documentation on Alerts in Windows 365.
Finally, have feedback or suggestions? Visit this forum to share your ideas and help shape the future of Windows 365!
Continue the conversation. Find best practices. Bookmark the Windows Tech Community and follow us @MSWindowsITPro on Twitter. Looking for support? Visit Windows on Microsoft Q&A.
by Contributed | Jul 13, 2023 | Technology
This article is contributed. See the original author and article here.
Like you, professionals around the world know that the cloud is transforming the business landscape for the better, while offering unparalleled opportunities for innovation and growth. As you look to the cloud to do more with less and to help support your organization’s goals and success, we’re confident that Azure can help you do exactly that—from saving money by migrating your apps and data to the cloud, to optimizing those cloud costs, and then reinvesting those savings to drive progress.
Knowing how critical these areas are, our mission is for Azure to be the world’s computer, so you can do what you do best—innovate, create, and differentiate your business. We also know that you need to focus on strategy and decision-making rather than on navigating the technical complexities of the cloud, even while you explore Azure costs and benefits, along with its ability to help solve your organization’s challenges.
So, where can you find an Azure overview?
We’re happy to introduce the new Get to know Microsoft Azure cloud platform: An overview for business professionals learning path on Microsoft Learn, crafted with you and your learning goals, needs, and preferences in mind. This free practical resource is designed to bridge the gap between cloud platform knowledge and business strategy—with no technical background required. Plus, you can complete it in about 90 minutes, which makes this an extremely time-efficient investment.
Created to help you quickly learn the advantages of Azure and how it can support your organization’s success, this learning path focuses on real-world examples across multiple industries. It demonstrates how cloud adoption can benefit your operations, drive cost savings, fuel growth, and more.
What can you expect to learn?
We know that business leaders want the innovation and agility that the cloud enables, but you need to learn at your own pace and on your own schedule. So we’ve distilled the essential aspects of Azure into concise modules, helping you to get the maximum value from your time. With practical insights and real-life case studies, we highlight the transformative power of cloud adoption, offering the inspiration and confidence to explore the possibilities of Azure in three modules:
- Describe the basics of Azure cloud for business details what the cloud is, what Azure is, and how your business can grow and transform with Azure and cloud economics.
- Describe the business benefits of Azure covers cost efficiency, innovation, agility, and security.
- Transition your business to Azure examines the various decisions to anticipate as you move to the cloud. Review strategic considerations and best practices for migrating to Azure and look ahead to the Microsoft Cloud Adoption Framework.
Modules and lessons included in the “Get to know Microsoft Azure cloud platform: An overview for business professionals” learning path
What other new learning paths are available for business leaders?
Like the cloud, AI has the power to change how organizations around the world operate, compete, and build value. And AI is ushering in a transformative era of innovation, efficiency, and unprecedented possibilities. We recently announced another new free educational series on Microsoft Learn, Transform Your Business with Microsoft AI, created to help you build your AI knowledge, insights, and skills.
This curriculum, which complements the new Azure overview for business leaders, explores:
- The competitive advantage and potential of AI.
- How to make informed decisions about its adoption.
- Examples from the Microsoft AI journey.
- Guidance from Microsoft experts and other industry leaders.
With an emphasis on responsible AI, including ethics, fairness, transparency, and accountability, this learning path aims to empower organizations of all sizes to harness the potential of AI. For more details, read AI for business leaders: Discover AI advantages in this Microsoft AI learning series.
Make the most of the new Azure training
When you take this 90-minute cloud journey, you gain insight into the transformative power of Azure, enhancing your cloud fluency and examining strategic decisions that can help drive your business forward. With Microsoft Azure as your trusted cloud provider, you get not only security and privacy but also a reliable business partner invested in your success. Embrace the benefits of cloud computing, explore how it can solve your challenges, and discover the potential savings it offers, with this new learning path for business leaders.
Recent Comments