You are here:   Blog
Register   |  Login

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 first, before you you use the blog! Maximize

Recent Entries

By ronen ariely on 20/03/2019 22:40

This post summarize my lecture from last week at PASS DBA Virtual Group.

Understanding what we have under the hood is not only done for the sake of learning a theory, but it directly impacts your daily work, and it can help us to improve performance and reduce resources dramatically.

During this session we discussed the structure of tables behind the scenes. We saw the changes behind the scenes related to DDL actions, which we execute on tables. Using this information, I demonstrated several real case scenarios, and the dramatic impact of understanding the internals on choosing our solution.

Is the order of columns important? Is dropping a column is the right solution and what is the impact? Questions like those are very common in the forums and usually does not get the right answers even by experts, but the answers might be a game changer in production environment. 

This session is a live demo!
There is no presentation to share, but in this post I will share the code and some points.

It is highly recommended to watch the recording first

By ronen ariely on 07/03/2019 21:05

The table msdb.dbo.backupmediafamily includes information regarding the media which used for database's backups.  The values in the physical_device_name column shows the physical name of the backup device like backup destination path or restore source path.

I noticed a question at MSDN forum, which was asked many times before in different forums. In this short post I will give some info and explain where these GUID came from and why we see record on backups but we cannot find any backup file related to these

By ronen ariely on 13/02/2019 00:55
This post does not aim to provide a solution for production but to present some playing with the function STRING_SPLIT which was added to SQL Server 2016, and discuss some theoretical results we can get using the function (Not For Production)
By ronen ariely on 11/02/2019 08:02

In this post I want to present several tips which can help in daily queries. This post does not present an advanced topic and it does not bring some high insights, but these tips can be very useful and I already wrote them, so why not present them😃

* You can understand more about what I mean by "I already wrote them" in the off-topic section, or simply skip it and move to the technical part

By ronen ariely on 12/01/2019 05:37

Several days ago, i saw a question in the MSDN forums, which led me to write this blog. I will rephrase the original question a bit and discuss the following request: How can we get the name of the current Stored Procedure (SP) from inside the SP

This is actually very simple question with multiple solutions which we can use on SQL Server On-Premises, but these solutions are not supported in other frameworks like Azure Data Warehouse and Parallel Data Warehouse.

My goal is to provide solutions for Azure Data Warehouse and Parallel Data Warehouse, but on the way I will cover the common solution for SQL Server and Azure Database as well.

By ronen ariely on 01/12/2018 04:42
There is a very common mistake which I saw many times in the forums. People are using (or advising others to use) Azure Resource locks in order to prevent deletion of Azure SQL database or other services, but is it really the solution and does in work for this task?!? The short answer is NO and for the explanation and full demo, you can keep reading this post
By ronen ariely on 28/11/2018 19:44

A common question that raise in forums: Is The Order of columns important? 

We focus on the database's side and not the client's side point-of-view. For example, is order of columns related to performance, is it related to errors that might rise only when use specific order, or will SELECT query return different DATA (not important the order of the result SET but the DATA) if the order of columns is different...

In this post I want to present some simple demo which answer the question

Update 2019-03-21: adding example from my last lecture.

By ronen ariely on 01/10/2018 12:02

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

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

By ronen ariely on 25/09/2018 08:24

PowerShell gives us a powerfull tool to invoke queries using the cmdlet command "Invoke-Sqlcmd".

Invoke-Sqlcmd execute statements which are supported by the SQL Server SQLCMD utility, which is where the name Invoke-Sqlcmd came from probably. It allows us to execute Transact-SQL or XQuery statements, or sqlcmd commands. Using the parameter "-Query" we can execute inline queries, and using the parameter "-inputfile" we can specifies a path to a file, which is used as the query input to this cmdlet.

There are a lot of tutorials online on how to use Invoke-Sqlcmd command, which usually focus on the built-in paramatres and how to execute the command. One of these parameters is "Out-File", which allows us to send the output of the queries to a file, but how can we control the output of PRINT for example or the output of ERROR massage?!? 

Today I want to discuss some undocumented options of using Invoke-Sqlcmd. In this post I will show how we can control the target of the output not only of the queries but also errors and user-defined messages (PRINT statements), and how we can send these to a new file, to the shell screen or to any other target.

By ronen ariely on 24/09/2018 23:12

היום באירוע השנתי של Ignite 2018, מיקרוסופט הודיעה על שחרור גרסת הבטא (Public Preview) של SQL Server 2019.

כמי שמלווה את הפיתוח של השרת כבר זמן רב תחת Private Preview וללא יכולת לדבר על המוצר המדהים הבא בשל הסכמי הסודיות עליהם אני חתום, השחרור היום מהווה מבחינתי האישית את האישור הרשמי להתחיל ולדברעל הגרסה הבאה של שרתי SQL. ותאמינו לי שיש הרבה על מה לדבר! אבל תחילה בואו נפתח עם ההכרזה הרשמית, ובימים ובחודשים הקרובים אני אתחיל להעביר הרצאות יותר מעמיקות על התכונות החדשות אשר נוספו בגרסת 2019 והשיפורים של התכונות הקיימות אשר ניתן למצוא.