You are here:   Blog

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read this before you use the blog! הגדל

Recent Entries

Author: ronen ariely Created: 08/10/2010 21:01 RssIcon
In Ronen Ariely Public Blog you can find technical posts mostly in the field of Data Platform and developing.
By ronen ariely on 20/04/2022 04:36
By default when we add a new external user to a team on Teams using his email, then the user gets a default name based on the email address. This means that if the person uses an email address which is not based on his real full name. then other members on the team might not be able to find the person in the team or recognize his user name when he will participate in activities. IN this short post I will present the issue and the "Best practice" which is configure the name of the user while we adding him to the team. I will also discussion how to fix the user information if he was already added without the right displayed name.
By ronen ariely on 29/03/2022 10:03
When I wrote this post I had a dilemma regarding the title, since I want to bring here 2 different aspects. The first aspect is technical and related to presenting a proven solution in terms of performance over the use of internal queries. The second aspect is more of a psychological discussion related to the choices that users make while completely ignoring performance in small systems. This post discuss using the function LAG and LEAD vs using sub-queries.
By ronen ariely on 18/03/2022 17:32
In the last several days there are reports regarding a strange behavior of SSMS which seems like started with version 18.11 and continue in version 18.11.1: A Beeps sound can is played each time we close the Query Editor Window. My tests suggests that this is a bug related to not disconnecting the server...
By ronen ariely on 26/02/2022 08:21
SQL Server Management Studio version 18.11 was released a few hours ago! What's new in 18.11?
By ronen ariely on 19/02/2022 18:27
Explanation, workaround and optional solution for the error: This operation has been cancelled due to restrictions in effect on this computer.
By ronen ariely on 01/02/2022 11:41

In the last several weeks I see many questions regarding Microsoft released of the new support to manage backups of SQL Server on premises in the Azure storage. This post is not intended to replace the official documentation, but only to simplify operations in a practical way. This post present step by step tutorial on backup to URL, backup to Azure Storage, and restore back to on-premises.

By ronen ariely on 20/01/2022 10:09
If you get the error: "The subscription is not registered to use <provider name>" then this post might help you understand and solve the issue. This can be related to change which was done in your subscription type.
By ronen ariely on 13/01/2022 00:42
step by step creating UBUNTU Azure VM and install SQL Server
By ronen ariely on 01/01/2022 13:40
The function GREATEST was added to Azure SQL but it not yet supported on SQL Server on premises (or synapse dedicated sql pool). In this short post I'll present a few options to implement the same result and discuss performance a bit.
By ronen ariely on 27/12/2021 15:13
This is a simple demo of using FILESTREAM and FILETABLE, following a question in the Microsoft QnA forum
By ronen ariely on 07/12/2021 18:07
Create table permission and Provide permission on the table only to the user which created the table, is not a recommended action, but if this is your requirement then this post would help in the task. It is more important to read this post in order to understand why this action is not recommended than to use it to actually implement such scenario.
By ronen ariely on 01/12/2021 21:07
It is quite common, especially when we are using free subscriptions, to have an issue to create Azure service in specific region, creating service using specific tier, or even creating the same service multiple times. These issues related to limitation in the Azure Subscription resource quotas. This post follows such issue which was raised in the Microsoft QnA forum. In this post I will explain the issue and present optional solution or clarify the limitation.
By ronen ariely on 23/11/2021 11:47
yesterday Microsoft Released the new Cumulative Update #14 for SQL Server 2019. Read more...
By ronen ariely on 19/11/2021 11:39

SQL Server 2022 is almost here. During the Microsoft ignite 2021 conference, on November 3, Microsoft announced the new version and started to present the new features and abilities. If you are working with SQL Server then it is now time to focus on SQL Server 2022. Read more on how to Get start with SQL Server 2022 - list of links

By ronen ariely on 05/11/2021 00:00
New as of today! Microsoft improved the function STRING_SPLIT and added a new optional parameter name: enable_ordinal, which allows us to get the returned SET in an order guaranteed, according to each substring's position in the input string 
By ronen ariely on 31/10/2021 09:44
Several days ago, I publish a riddle in the SQL Server product family on Facebook. The short version of the question is this: When we are executing a CREATE TABLE then the get the error that the table already exists, but when ew are exploring the SSMS Object explorer, then we cannot see any such such table. Can you explain why and how to reproduce this station?
By ronen ariely on 22/10/2021 05:36
Once again, we will dive into the internals of the SQL Server, since someone asked in the forum "How to find who created a database"....
By ronen ariely on 04/10/2021 01:02
This is an answer to a question in the Microsoft's QnA forum. As a result of a bug in the forum, I could not publish the answer
By ronen ariely on 22/09/2021 12:28
Line breaking, also known as word wrapping, is breaking the displayed of a section of text into lines so that the text will fit into the available width of the editor. When writing queries this feature is not so useful as breaking the script line may make the query less readable, but when writing long comments this feature become one of the most useful feature. This post simply shows you how to use word-wrap by default or add a command button to Toggle Word Wrap - it's a built-in feature which is less known and if you did not used it yet, then it is time to use the power of word wrap
By ronen ariely on 09/08/2021 16:32

Happy (Hijri) New Year! Today millions of Muslims celebrating the new year according to the Hijri calendar and this is a great opportunity to lean how SQL Server support (or not) working with Hijri dates. I dedicate this post to all my Muslim friends. This post is in Hebrew and is addressed to members in Israel who work with the Muslim calendar. Unfortunately many Muslims in Israel do not speak English and I do not speak Arabic, but we all here speak Hebrew. I hope this post will helpful and useful for you.

שנה טובה (Hijri)! כיום מיליוני מוסלמים חוגגים את השנה החדשה על פי לוח השנה ההיג'רי וזו הזדמנות מצוינת לבחון כיצד SQL Server תומך (או לא) בעבודה עם תאריכי ההיג'רי. אני מקדיש את הפוסט הזה לכל חבריי המוסלמים. פוסט זה הוא בעברית ופונה לחברים בישראל שעובדים עם לוח השנה המוסלמי. לצערי הרבה מוסלמים בישראל לא מדברים אנגלית ואני לא דובר ערבית, אבל כולנו כאן מדברים עברית. אני מקווה שהפוסט הזה יהיה מועיל ושימושי עבורך.

By ronen ariely on 07/08/2021 03:25
הנושא של רשיונות לשרתי SQL, מורכב מאוד ומעלה הרבה שאלות. בפוסט זה אני אנסה להסביר ולענות על שאלות נפוצות אשר עולות בפורומים. יש לזכור שמודל הרשיונות משתנה בין גרסאות שונות של השרת. בכל מקרה תמיד מומלץ לפנות למסמכים הרשמיים של מיקרוסופט או לקבל תמיכה בנושא ממנהל חשבון טכני של מיקרוסופט שזה תפקידו ומומחיות שלו - Microsoft Technical Account Manager (TAM).
By ronen ariely on 23/05/2021 11:40

A few years ago the GitHub organization set up a new service which operates under the github.io domain owned by GitHub. This service allows us to host websites for free. Site files are stored in a project's repository under a GitHub account. This article combines information from a number of official documentation and posts on the web and provides a first look at the GitHub Pages service, in a simple step-by-step guide. All the information in this article can be found online, and it is recommended that you follow the links in the "More Information" section. This post is essentially a translation of my article that I posted on TechNet Wiki on February 28, 2019.

לפני כמה שנים הקים ארגון GitHub שירות חדש אשר פועל תחת דומיין github.io הנצא בבעלות GitHub. שירות זה מאפשר לנו לארח אתרי אינטרנט בחינם. קבצי האתר מאוחסנים במאגר (repository) של פרוייקט תחת חשבון GitHub. מאמר זה משלב מידע ממספר תיעודים רשמיים ופוסטים ברשת ומספק הצצה ראשונה לשירות GitHub Pages, במדריך פשוט בסגנון צעד-אחרי-צעד. את כל המידע במאמר זה ניתן למצוא באינטרנט, ומומלץ לעקוב אחר הקישורים בפיסקה של "מידע נוסף". פוסט זה הוא בעיקרו תרגום של מאמר שלי אשר פרסמתי ב-TechNet Wiki ב-28 בפברואר 2019.

By ronen ariely on 18/05/2021 21:00
his post follows the discussion or actually my answer in this old MSDN forum thread. It present a few strange behavior of SQL Server when dealing with Hijri dates, explain the issues and end with a short recommendation for these working with Hijri dates.
By ronen ariely on 23/04/2021 11:39
This post come to answer the question how to learn SQL, which raise in the forum from time to time. It brings my personal insights about self-learning and a few sentences on how to start learning SQL. It is not exactly a regular post like I usually write but since the question was asked once more yesterday then I simply copy the answer which I gave in the forum so I will have it documented for the next time it will be asked
By ronen ariely on 11/04/2021 08:02

Azure Files (שירות קבצים משותף) הוא שירות בענן של מיקרוסופט, אשר משתמש בקבצים השמורים Azure File Storage. הקבצים משמשים כתיקיות שיתופיות וירטואליות. כל קובץ מוגבל ל-5TB אך אתה יכול להפעיל מספר שיתופים באמצעות קבצים מרובים. ניתן להשתמש ב-Azure Files כדי להחליף אחסון מחובר ברשת (NAS) או מערכות קבצים מקומיות. השירות מבוסס על פרוטוקול Server Message Block (SMB) ומאפשר להעביר יישומים ונתונים "כפי שהם" באמצעות פרוטוקול זה. ניתן לגשת לקבצים באמצעות API תוך שימוש בתקשורת מוצפנת, או לנהל את הקבצים באמצעות ממשק המשתמש המובנה בפורטל, דרך Azure CLI או PowerShell.

על ידי שימוש בשירות של Azure File Sync, אתה יכול לסנכרן ו/או לשכפל את הקבצים בין מספר שירותים בענן ואחסון מקומי לטובת גיבוי או שיפור ביצועים. השירות מאפשר ליצור מאגר מרכזי עבור קוד או כלי עזר המשמשים במהלך בדיקות ופיתוח.

שירות קבצים משותף מאפשר לרכז מדדים וקובצי יומן להטמעה על ידי כלי ניטור וניתוח. השירות מספק יתירות לניטור ופתרון תקלות נתונים.

By ronen ariely on 25/03/2021 21:04
Structured Query Language (SQL) commands can be categorize into DCL (Data Control Language), TCL (Transaction Control Language), DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language). So, what these terms actually means and how this is related to the way you should raise questions when you ask for help?
By ronen ariely on 17/03/2021 21:29
This issue was reported in the Microsoft QnA forum! Using nested inline User-defined Scalar Functions in SQL Server version 15.0.4102.2 returns an error. I confirmed this issue in several machines.
By ronen ariely on 10/03/2021 09:33
Once more, I got the request to help in cleaning the database from unused tables, and once more I find it important to clarify that we should not be reckless to drop a table or any other entity if you are not familiar with your database structure and you have no understanding what this object is used for or why it was created. It can come back to you to bite you in the ass in a month or maybe a few years. This post is a reply to specific request and present my general point-of-view and approach. It is NOT a full solution.
By ronen ariely on 05/01/2021 10:24
Just for me - shortcuts (will fill it in time)
By ronen ariely on 23/12/2020 11:41
SQL Server Configuration Manager and SQL Server Management Studio display the status of services by using the different icons. In this post I will summarize the different options. Part of the information in  this post was taken from the official documentation while several scenario are not documented and the information is based on my tests and research.
By ronen ariely on 07/11/2020 16:56

There is a great support for Teams in PowerShell, which allow you to get all a list of all the users in a specific team. Here is the procedure in steps

By ronen ariely on 28/10/2020 18:07
Always Encrypted - demo in a nutshell (using PowerShell and C# as clients). This is pure demo code from start to end
By ronen ariely on 25/10/2020 08:53

 Before the release of SQL Server 2016, one of the most required solution was for a function which split string. There are many solutions you can find online. Most of these are based on loops that provide poor performance, using XML, or using SQLCLR which in my test provides the best option.

After many years of requests for such a function, Microsoft has finally provided us with a new function called STRING_SPLIT in SQL Server 2016, which should solve our need to split a string. Unfortunately this solution is a half solution at best!

In most cases we need a solution which guarantee the order of the result set. The STRING_SPLIT does not provide this. Thus from 2016, the requests to provide a function for splitting string, has been replaced by shouts to improve the existing function by adding column which present the order of the text.

With that being said, there is a perfect solution using a different approach using another function which was presented in SQL Server 2016. Let's show how simple it is... 

By ronen ariely on 14/09/2020 10:20
In order to open a support ticket we need to login to Microsoft services. This is true in the Azure as well as in other services like Visual Studio Subscriptions(formally called MSDN subscriptions). So this leads direct to the chicken or the egg dilemma in the case our issue is with login to the account. We cannot login so we cannot open a support ticket. This VERY SHORT post simply provides the way to bypass this issue regarding the Azure (in other services you might find other solutions fit better)
By ronen ariely on 31/08/2020 10:57
Convert Azure Virtual Machine with SQL Server installed into Azure SQL Server Virtual Machine
By ronen ariely on 18/08/2020 21:04
From time to time I come across a question in a forums from someone who claim that SQL Server Execution Plan shows that the cost of a specific operator or the sum of operators is more than 100 percentage. The discussions usually get answers that this is related to the SQL Server Execution Plan, while the truth is that this has nothing to do with the server side but the client tool which calculate the percentages of the cost. In this post I will summarize a bit more information and explain how we can be sure that this issue is indeed in the client side and not a result of information which come from the server (at least the cases I am familiar with).
By ronen ariely on 02/07/2020 17:32
Simple working with Primes numbers
By ronen ariely on 14/06/2020 01:51

When we are using Static Public IP, then there is no need to update the DNS records each time that the IP address changed. This ensures us that there will be no downtime to our services, while the DNS servers are not updated (a process that can take few hours).

The Azure public IP is a separate service, which we can associate to a new or existing Network Interface or to a load Balance. The Network Interface is associated to Virtual Machine. Therefore, if we are using a Static IP, then we can migrate IP Address from one machine to another. This is a powerful option in cases that we need to migrate services between virtual machines.

In this post I go over the theory of networking is a glance in order to cover the main terms which we all must know, before we start to play with the powerful options that Azure Static Public IP provide us. Next I present several practical scenarios of using static public IP (create IP, associate to existing VM, Migrate between VM, and so on).

By ronen ariely on 08/06/2020 05:02
Let's "play" with some data and let me show how to use SQL Server to Analyze the Coronavirus Covid-19 data. The source of the data was taken from the CSSEGISandData GitHub project. In this post I will add some useful queries and explain how to use the data and what can we do with it.
By ronen ariely on 06/06/2020 12:53
Multiple reports arrives in the forums starting 9 hours ago but Microsoft Azure subscription Service Health only alert me about one hour ago! My conclusions: follow the forums and report in the forums for others to get the information fast
By ronen ariely on 29/05/2020 21:07

When we create DSN to the Azure SQL Database using Microsoft tool odbcad32.exe, then the password is not saved. Therefore, each time we use the DSN we need to fill the password. Can we skip this task and store the password in the DSN cata?

Note! This is a pure answer to a question in Microsoft new forum interface Q&A. The forum does not allow me to publish the answer raising the absurd error "Exceeded the Normal request".

By ronen ariely on 28/05/2020 02:32

This is a simple step-by-step tutorial to demonstrate how we can create auditing of logout event using Extended events, on Azure SQL Database. It basically present an answer to a question in the MSDN forum.

By ronen ariely on 18/05/2020 18:07

When executing the powershell command Get-AzSqlDatabaseGeoBackup I am getting an error which claim that the Azure logical server does not exists in the current subscription, while the server is alive and I can execute the command Get-AzSqlDatabase and get all the databases in the server!

Get-AzSqlDatabaseGeoBackup : 40647: Subscription '9be5b21d-3676-40f1-baec-16c10ae909ec' does not have the server 'sponsor01'.

By ronen ariely on 08/05/2020 23:29
Does COLLATE important when we use the function UPPER on the text? Just a small sample related to a forum question, since I cannot add images in the comments
By ronen ariely on 07/05/2020 03:07
Application Change Analysis service is monitoring tool that supports viewing changes for all resources across Azure subscription on Azure portal. Sound awesome! So let's review what works and what not so much...
By ronen ariely on 02/05/2020 20:12
Microsoft Build is an annual conference event held by Microsoft, aimed at software engineers and web developers using Windows, Microsoft Azure and other Microsoft technologies. The attendee price is usually above 2000$ each year, but not this year! Microsoft announced that the conference will be held online this year and the registration will be free. Keep reading and register ASAP since there might be limitation in number of registrations!
By ronen ariely on 22/04/2020 09:42
When we are trying to create a custom name for a new vNet while creating managed instance using the portal, the default name is not changed. The comment in the portal explicitly says "To create new subnet select option with the prefix (new)". Unfortunately, this does not works (tested on Chrome Version 80.0.3987.122). if you use Azure Web Portal and you are going to create a new Azure SQL Managed Instance service, then you should keep reading, since I found a small bug. More interesting is that I will present a simple workaround I found.
By ronen ariely on 11/04/2020 01:41
Execute a job from inside another job is a simple task like executing any query. We simply need to start the external Job using the built-in stored procedure msdb.dbo.sp_start_job. Unfortunately if we have multiple jobs which needed to be executed in specific order Synchronously (meaning that only when one job end then the next one start), is a more complex task using the built-in SQL Server Agent. In this post I will mentioned several options to solve this task.
By ronen ariely on 08/04/2020 02:45
yesterday Microsoft released the new version of SQL Server Management Studio (SSMS) 18.5, build number for this release is noted as 15.0.18330.0. This new version brings a few new features, bug fixes, and more. In this short post I will go over the new changes in the new version.
By ronen ariely on 07/04/2020 01:40

Let's assume that we have two databases A and B and we want to swap the names so the old database A will become B and the old database B will become A. In first glance you might think that you can simply backup both databases and restore database A with the name B and restore database B with the name A. This solution for swat two databases was presented in the MSDN forum. In this post I explain why this is not so simple and demonstrate one of the problematic issue in swapping databases this way.