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

Recent Entries

By ronen ariely on 10/03/2023 03:00
אם אתה עובד עם sqlcmd אז אולי פספסת את הופעת הבכורה של ה-sqlcmd החדש, המכונה go-sqlcmd. כלי זה מבוסס על מנהל ההתקן של Go mssqldb אשר פותח לעבודה עם שפת Go. הכלי החדש עדיין בגרסת הרצה בטא אבל מיקרוסופט הודיעו שהם מצפים שהוא יחליף את ה-sqlcmd הקיים תוך כדי הוספת יכולות חדשות. הכלי תומך מערכות מבוססות מעcדי arm64 בנוסף למערכות x64, הוא תומך עבודה עם Azure SQL Database והתקנות מקומיות של SQL Server. כדאי להמשיך ולקרוא כאן ->
By ronen ariely on 25/02/2023 14:18
Following the inquiry in the forums, it seems that there is a bug when we are using sp_refreshsqlmodule on a stored procedure which call HIDDEN system-versioned temporal tables with hidden columns. In this post I will reproduce the issue and present a simple explanation. For solution we will need Microsoft team to fix the bug.
By ronen ariely on 18/02/2023 05:38

When creating a VIEW using SQL Server Management studio (SSMS) wizard, then you might encounter the error: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. This error is is NOT from the SQL Server but from the SSMS application. In this post I will elaborate a bit more, demonstrate the issue and explain the meaning of the message. There is no need to solve anything in most cases and it's just a warning for the user.

By ronen ariely on 21/12/2022 03:31
The issue: When we use windows operating system or most modern system and an error raise in applications, then in most cases the error will be alerted in an alert windows. In this post I will present how to get the text of the error message out of the error message window - this action is highly important for example in order to search for more information using a search engine or in order to ask for more information in forums.
By ronen ariely on 15/07/2022 10:22
I am writing this post following a question and the discussion over Facebook about the question what is the different between JOIN and UNION. There are many common mistake which even experts are doing in defining these two task and some of these appeared in the mentioned discussion. I prefer to write the answer here since Facebook is not the best interface to write a long message - drive me nuts when in the middle of the writing Facebook raise and error and I lose all I wrote.
By ronen ariely on 14/07/2022 00:38
Following a question in the forum, I want to present the issue of using a variable in the a VIEW and how we can solve it as well as the right way to solve such request (hint: think if you are stuck in an "XY problem").

This discussion is only theoretical and only focus on the question.

By ronen ariely on 14/06/2022 00:36
Following my lecture at the .Net conference, about how to develop extensions to SQL Server Management studio, you can download the code and compiled package here. This is small add-in for SSMS in order to work with JSON and actually be able to read the JSON code. It has a "pretty JSON" functionality amount other tools.
By ronen ariely on 15/04/2022 04:34
Delete old backup files can be done by running transact SQL query from a job that we schedule. Deleting files using transact SQL query can be done in multiple approach using external languages and OLE object, built-in undocumented Extended Stored Procedures and more. In this post I will go over some of the options we can use. This post is not a deep dive into the solutions but a review. For more information I am adding relevant links to each option I mention.
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 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 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 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 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 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 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 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 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 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 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.

By ronen ariely on 16/01/2020 14:19

Microsoft official Documentation state that "Read/write data filegroups and log files cannot be placed on an NTFS compressed file system. Only read-only databases and read-only secondary filegroups can be put on an NTFS compressed file system", but is is this accurate?!?

By ronen ariely on 07/01/2020 18:13

This is a simple compare I do for the sake of a question in the forums. Since I don't have time now, I will add other solutions and dive into better and I will check further comparisons in the future. At this time I will add the result of two suggested solutions.

By ronen ariely on 22/10/2019 14:29

Azure SQL Database and SQL Server 2019 introduce full support for UTF-8. This is a game changer for developers, but it came with a price! Old functions like CHAR() and ASCII() which were designed to be used on Extended ASCII, return strange result and might lead to unexpected result when we are using UTF-8 COLLATE. Moreover! the documentation at this time is wrong and was not updated to cases that the user use UTF-8 COLLATE. 

In this post I will go over the function ASCII, explain how it was designed to work (according to my understanding), show the problematic in SQL Server 2019, and I present full explanation including some internals information which will help us better understand the topic.

By ronen ariely on 13/10/2019 10:00

This post includes two simple demo code to demonstrate (1) how order of columns can impact the size of the table and the database, and (2) That order of columns can be related to errors in the execution.

By ronen ariely on 13/10/2019 09:56

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 will go over the structure of tables behind the scenes. We will see the changes behind the scenes related to DDL actions which we execute on tables.

Using this information, I will demonstrate 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 the right solution? Questions like those are very common in the forums and usually does not get the right answers. The answers might be a game changer in production environment

This session is a live demo!

By ronen ariely on 13/10/2019 00:30
The issue: SQL Server grand Server level permissions and database level permissions to all new users, which is a security issue. In this post I will present some of the permissions which any new login gets and how we can revoke or deny these permissions. This post based on the question asked in the MSDN forum and presents some theoretical options. Whether you should do it in production is another discussion...
By ronen ariely on 29/09/2019 07:36

OPENJSON is a build-in SQL Server table-valued function (return tabular structure), which parse text that formatted as JSON. By default, the function only parse the first level of the JSON document, but using explicit path to the nodes in the document we can parse the entire document including unlimited level of hierarchy. This sound great except the fact that JSON document is commonly used for unstructured data, meaning we do not know what nodes the document will have and what is the the path to each node... OPENJSON does not provide a dynamic parsing for hierarchy structure.

In this blog, I will present a simple recursive table-valued function, which based on OPENJSON and provide full dynamic hierarchy parsing of JSON document.

By ronen ariely on 22/08/2019 10:39

SQL Server 2019 release candidate is now available! So what does it brings?

By ronen ariely on 07/08/2019 04:38
This post includes sample code for step by step: (1) create database. (2) CREATE TABLE. (3) create folder for the Audit logs. (4) CREATE SERVER AUDIT. (5) CREATE SERVER AUDIT SPECIFICATION. (6) CREATE DATABASE AUDIT SPECIFICATION. (7) Read the log file.
By ronen ariely on 17/07/2019 02:45
According to the documentation if you want to Access the error log files on Linux then you can convert the files to UTF-8. There is only one issue... The files are already written in UTF-8 encoding and not like in windows in UTF-16, which mean that this paragraph is really confusing...
By ronen ariely on 03/07/2019 02:28

Well, I don't know if you noticed but in the last year or so, blogs on the MSDN and TechNet system were closed or become "read-only". Unfortunately, this includes two of the blogs where I serve as co-administrator (wikininjas and forumninjas) which are in the same situation, but today I do not want to focus on these. In this post I want to focus only on the blogs where the Microsoft Data Platform teams write. The problem is that by closing the blogs on MSDN/TechNet blogs it seems like the blogs of the SQL Server teams has dispersed, and today each person or each group publishes his blog elsewhere in a different interface. It is almost impossible to follow all the blogs this way... I hope this list can be useful to you.

By ronen ariely on 26/06/2019 20:13

The official announcement of the release of SQL Server 2019 community technology preview 3.1 was published several minutes ago! Be the second to download the new version 😃

By ronen ariely on 21/06/2019 09:49

Error: The UTC time represented when the offset is applied must be between year 0 and 10,000.

A question in the MSDN forum about an error message which the user get, turned out to be an unknown (until now) bug in the SQL Server Management Studio SSMS (relevant to SSMS 18.1 and before). 

If you are in Israel then you are probably fine😃, in fact anyone who use UTC/GMT Time Zone or positive offset in time (Meaning, location east to Greenwich England) is fine... But if you are in US for example, then you must know about this


By ronen ariely on 28/05/2019 01:42

Did you encounter issues with opening SQL Server Management studio version 18?

The online and offline communities discuss this issue in the last several months. You can find reports like: SSMS not run, SSMS closes immediately after startup, SSMS won't open, and so on...

Well... this blog only summarize the issue and provide the workaround
Everything I write in this blog is not original but summarize. You can check the following link if you want to follow the entire discussion about this issue.

Update: The fix for this issue was included in SSMS 18.1.

By ronen ariely on 12/04/2019 18:36

This post present code to configure default configuration parameters for each new LOGIN created in the server instance. The code configures parameters like: default database, default language, password will not expire, and password policy will not be checked. The solution presented here is based on using DDL TRIGGER ON CREATE_LOGIN event and is part of an answer I gave on the MSDN forum.

By ronen ariely on 30/03/2019 23:59

We have a SET of separated slots/ranges and we want to represent the merged ranges which cover all the separated ranges, using a SET of minimum number of ranges. In other words, we want to to Group continuous and overlaps Ranges together. If we have SET of slots/ranges, and two or more of these ranges has shared point/s (a point which exists in both ranges), then these ranges can be merge together into a single range. 

For example if we are dealing with ranges of integers and we have the ranges 3-6 and 5-8, then we can marge these into the single range 3-8, or the ranges 2-4,4-6 can be merged into 2-6.

In my previous post I covered the solution using the "Gaps and Islands" approach. In this post I will cover a solution which is based on SQL Server built-in GEOMETRY data type.

By ronen ariely on 25/03/2019 13:19

Note! This post is draft, which I post fast as answer to question which asked at stackoverflow. Since I don't have time this week to prepare a well formatted post with images, full explanation, and well formatted text, but at the same time I want to provide a solution for the sake of these who asked for it, therefore I publish this as a first draft and provide the code for one of two solutions I will discuss.

Note! The solution which presented here is not the one which I recommend to use in this case. This solution address the question using a well known approach named "Gaps and Islands" and might fit for most cases. In my next post I will present a totally different approach using my personal trick, which can improve performance dramatically in some scenario.

So what do we have here? The post include code and some comments to solve several scenario of Grouping continuous Ranges together.

I will start with the simplest case which is ranges of integers, for example 2-4, 6-8, 8-10, 13-14 which should be grouped into 2-4, 6-10, 13-14.

Next I will move to explain issue related to the resolution of space between the ranges, and I will go to present a solution for Ranges of Decimal numbers.

Finally, using the solution which I presented in detail for INTEGERS I will present a solution for "Grouping continuous time-slots together", which was the original question in the forum.

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 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 והשיפורים של התכונות הקיימות אשר ניתן למצוא.

By ronen ariely on 09/09/2018 18:12

A web server software processes incoming network requests, and serve contents to the World Wide Web. A client software like a browser initiates communication by making a request for a specific resource and the server responds with the content of that resource.

Common web severs like Apache and Internet Information Services (IIS) allow us to keep track the incoming network requests. The server stores the information in simple text files. The raw log file, which lists the information as recorded by the server, are not particularly easy to read directly. The raw log files can become incredibly large very quickly. Moreover, usually we are interested in aggregate information like “how many people visit my website” or “how many times people view my blog”.

In this short post I will show how you can use the power of SQL Server and Transact SQL language in order to (1) import IIS log files to SQL Server table, (2) parse IIS log files data directly or from the data that was imported to the SQL Server Database, and (3) I will show several examples of queries to process the IIS log and get useful information out of it.

By ronen ariely on 22/08/2018 12:10

We have a table with Integer Column, which is no longer needed. What should we do in order to reclaim the size and/or improve performance?

(1) DROP column
(2) UPDATE values to zero
(3) Change the column to null
(4) ALTER column type

This post presents some important points related to discussion about a real-life case, which was asked in the MSDN's forums. For the sake of the post, I will present a summarize of the solutions, which were mentioned in the original thread, and a short discussion on each.

This post is advance topic, level 500 - Internals!

Update 2019-03-019: After presenting a lecture in PASS user group, i add some more points and code.

By ronen ariely on 11/08/2018 01:52

Several days ago I got a support call from a senior DBA, saying that "someone" in the company dropped a column in a table by mistake, before they copied several of the values,which they needed. Moreover, he explained that they do have any backup of the database and therefore they lost important data.

Before he contacted to me, he tried to get help in the forums, to contact several experts, and he found Microsoft's documentation which claimed that "This action cannot be undone". The question was simply a request to find away to get the lost data... 

Can it be done?!?

The short answer is, that a common user which work only with the LOGICAL layer of SQL Server using the documented tools cannot undone this action, but for SQL Server internals level of users, this is a different story...

By ronen ariely on 23/07/2018 08:38

In this sample code I will present a simple solution to the question "how to drop CLUSTERED INDEX from a PRIMARY KEY column. The case study is that the user created a table with PRIMARY KEY but he did not meant the PRIMARY KEY to become a CLUSTERED INDEX, which is what happen if you do not configure a different CLUSTERED INDEX or you explicitly configure the PRIMARY KEY as a NONCLUSTERED INDEX

By ronen ariely on 10/04/2018 22:00

Seems like Microsoft papered their services to the new GDPR regulation, and as part of the preparation several weeks ago a new feature named “PRIVACY” was added to the MSDN and TechNet profile. This feature enables us to download the data that Microsoft stores about our activities! Unfortunately, the data that is given to us is not formatted in a readable way, but in one long line of text [*updated] (JSON FORMAT) which can be in the size of hundreds of megabytes. In this post, I show how to get our data, view the data in raw format (JSON) and in readable format, and I gave queries to parse all the data into tabular structure using the built-in JSON features in SQL Server.

* Update 2018-04-17: I added Microsoft team's feedback and future plan, accordingly my post...

By ronen ariely on 20/03/2018 14:57
Seems like there is a confusion regarding the meaning of the style parameter in CONVERT function, and the way SQL Server use it. This short post based on a question that raise in the MSDN forum, and I will try to bring some insights here.
By ronen ariely on 14/03/2018 08:02
We are living in a very complex world. The combination of fast evolution in technologies (and other fields) together with the online communities and the laziness of people who want to use short words brings to the world a new acronym every several minutes probably. Each acronym word can have 10 and sometimes 10 thousand different meanings... let's go over the meaning of MMDBMS regarding SQL Server OLTP in-memory tables
By ronen ariely on 10/03/2018 04:43

When we are working with Many-To-Many relationship between two entity tables, we often need to find all the entities (rows in one table) which have relations with specific list of entities from the second type (rows in the second entity table). Our goal is to get all the Users that related to all specific roles. For example, all the users that have both RoleId 2 and RoleId 4.

By ronen ariely on 17/02/2018 05:24

let me summarize the new features which were added to SQL Server Management Studio (SSMS) version 17.5

By ronen ariely on 14/02/2018 00:13
Microsoft SQL Server Command Line Utilities version 14.0 was released several days ago, and it is available for download here. This post summarizes some information about Microsoft SQL Server Command Line Utilities, and provides links to the official documentation of sqlcmd in general and of each tool.
By ronen ariely on 09/02/2018 12:56
חדש!!! שירות מופע מנוהל של שרתי SQL באזור (Azure SQL Managed Instance ), הוא שירות חדש ששוחרר היום ל Public Preview וזמין לשימוש.
By ronen ariely on 30/01/2018 01:48
There is a temporarily issue in using Azure Automation with sqlserver PowerShell module, which lead to the error message: Invoke-ProcessASDatabase : The term 'Invoke-ProcessASDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
By ronen ariely on 19/01/2018 06:36

According to the documentation “ReadOnly lock on a SQL Database prevents you from deleting or modifying the database”, but in fact this is not accurate!

* Everything that I write about was tested at the time I write the post, and might be changed in the future!Let’s test it!
By ronen ariely on 05/01/2018 00:00
If you are one of the tech freaks who can afford to buy a new computer every week, or if it's just time for you to buy a new computer, or even if you simply use modern Intel processors dating back at least a decade, then you should read this post! A vulnerability affecting computers running Intel chips was announced two days ago!
By ronen ariely on 23/12/2017 15:54

בהמשך לדיון בהועלה בפיסבוק אני רוצה להעלות כמה נקודות ולהציג את 2 הסנטים שלי בנושא המשמעות של הפרדת דיסקים בעידן הוירטואלי.

כדי לחסוך לעצלנים את קריאת הבלוג (שהוא גם כך קצר מאוד), הרשו לי להתחיל מהסוף:

באופן חד משמעי, לחלוקה נכונה של קבצי מסדי הנתונים יכולה להיות משמעות מכרעת על הביצועים גם במכונות וירטואליות!

בוא נעבור על כמה נקודות שיכולות  לכוון אתכם להבנת הנושא

By ronen ariely on 23/11/2017 10:22
This is a simple Step-By-Step tutorial, showing how to use Azure BLOB Storage services to store backups of SQL Server databases. During the tutorial I will start with creating a new Azure Storage Account, next I will create new Credential in our local SQL Server and using the Credential I will backup and restore the SQL Server database.
By ronen ariely on 04/07/2017 16:03

Using the SQL Server Management Studio Stretch Database wizard with the option to filter the rows, does not fit for cases that the table does not include data which fit the stretch condition! 

We can use the built-in SQL Server Management Studio wizard in order to enable Stretch on the database and configure the table which will be stretched to the Azure on the same time, but it does not fit for all cases. In this short blog, I want to show example (again) why you should not use wizards.

* The solution is simply to use T-SQL queries, and please forget about using Stretch Database wizard, as I usually recommend regarding most wizards.

By ronen ariely on 10/06/2017 04:23

This is short post I publish as an answer to a question in the MSDN forum. In this post I will go very fast on the theoretical topic and will move to a short demo. Relational database system implements relations by using foreign key, which point to the related unique key identifying the row in the other table (named Primary Key). Since foreign key can only point to a single row in the other table, this model cannot implement a direct Many-To-Many relationship between rows in two entity tables. Read more to see how we implement it...

By ronen ariely on 10/05/2017 21:51

I want to bring a short story regarding a bug report related to SSMS 2017 and a User defined Extensions. I spend several hours to find the issue and I hope this blog will help you save the time. Therefore, if you are developing extensions (addins) to SSMS using VSIX template, or if you interesting in the subject, or if you simply tried to install extension and the installer did not recognize the SSMS, which installed in your machine, then this blog is for you.

By ronen ariely on 31/03/2017 19:03
SSMS vNext BUG during creating script to delete database from azure SQL Server
By ronen ariely on 12/03/2017 00:00
Please to post a nice certificate I got from the India Mentor communities leader...
By ronen ariely on 10/12/2016 07:42

SET IDENTITY_INSERT doesn't work with 4 parts name, and as such it doesn't work with linked servers unless you execute dynamic SQL. Moreover, "SET IDENTITY_INSERT" is a session level setting, therefore when you connect to another server the setting of current session are not relevant. In this short blog I will show how we can use SET IDENTITY_INSERT on linked server using dynamic SQL.

By ronen ariely on 01/12/2016 07:03

This post demonstrates several options to convert VARBINARY data into Base64 string, and vice versa convert Base64 string into VARBINARY.

* In future posts I will show practical examples of using these actions. For example, we can use Base64 string in order to send emails, which include embedded images, stored in the database as VARBINARY.

By ronen ariely on 19/11/2016 20:53
Step by step video demo: Monitoring growth of SQL Server database files using Profiler and Using the 
By ronen ariely on 02/10/2016 06:30

* This post base on a question asked at the MSDN forums in this link

In order to restore the database in the same instance with the original database, we need to (1) move the restored database files to new location, since two files cannot have the same path in windows operating system. (2) we must change the DIRECTORY_NAME for the Filetable data, since it is instance level element and it must be unique in this SQL Server instance.

We can configure the new path for the restored files using simple command "restore... with move <original path> to <new path>" for each file, but there is no built-in option to configure the DIRECTORY_NAME at the same time during restore. Well... In this post I will show step by step a simple procedure to restore the database as a new database on the same instance as the original database.

By ronen ariely on 29/08/2016 02:31

Hello all,

Today I am trying something new...
Instead of writing a long post, which describe a bug, I will show you the bug in action using a simple video i created.

in fact, this is probably not really a simple video. In the past I used free version of apps to create simple videos, but it is time to move one step forward. I wanted to learn a bit about using advance video editor app, in order to start recording technical tutorials (webinars). Therefore, I used lot of the options that the video editor app includes. So, what app did I used? I use a full version licence of "Camtasia Studio", which is given free of charge to Microsoft MVPs.

Well, let's see how was I in my first test creating this short video :-)

You are welcome to post feedback and comments at Facebook.

- Ronen


By ronen ariely on 09/07/2016 19:54
One of the new security features that SQL Server version 2016 brings, is the Dynamic Data Masking (DDM). Implementing DDM internally in SQL Server, aims to bring us better security, in the user level rules. User not been granted permission "UNMASK", cannot be exposed the original value, if column configured to be masked. Is this actually works?!? In this blog I will show how easy it is to expose the masked data, by any user.
By ronen ariely on 26/06/2016 07:11
Did you tried to open the Help Viewer and instead of seen the content you see HTML code? Well, i wasted several minutes to find the issue, which is actually related to the browser configuration...
By ronen ariely on 23/06/2016 23:25

When you try to open the Help Viewer from SSMS 2016, there are two behaviors that people reports about: (1) Instead of getting the new Help Viewer version 2.2, which came with the SSMS 2016, the old version 1.1 is opened(2) Instead of getting any help viewer, the browser opened and redirects us to the Book-Online, or An error raises with the message "no app are installed to open this type of link (ms-xhelp)".

In one of my previous blogs I wrote about how to install Microsoft SQL Server 2016 Book-online for offline use. I mentioned this bug regarding starting the help viewer:

! Quote In the current SSMS version (April 2016 preview), when we click on F1 or if we select "Help" -> "View Help" in order to get help, instead of opening the Help Viewer, SSMS opens the download page in the browser, even if the Help Viewer already installed! It seems like a bug.

The current version is SSMS June 2016 generally available release, which mean that this version theoretically ready for production. Yet, the bug is still there, and it is time to focus a bit more on what seems to behave wrong, and how we can workaround these issues...

By ronen ariely on 19/06/2016 04:47

Text strings are created from characters. A collection of characters called a "character set". A character set, also known as a code-page or charset. It is an ordered set of characters in which each character is mapped to a numeric index, called a "code-point".

Character Encoding is the mechanism to map the code points into bytes. The code points are stored in the computer as one or more bytes (collation of 0 and 1). The visual representation of characters is configured by the font. A font is a collection of glyph definitions used to display characters...

By ronen ariely on 14/06/2016 08:05

Row-Level Security enables customers to control access to rows in a database table. Access to row-level data in a table is restricted by security predicate, defined as an inline table-valued function. The function is then invoked and enforced by a security policy.

According to the BOL, RLS filter predicates are functionally equivalent to appending a WHERE clause. The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42. Is this accurate?

By ronen ariely on 22/05/2016 18:29

Using "Microsoft Help Viewer", which is a small free tool, we can download the entire official SQL Server Book-Online. In this short blog I will explain how to do it step-by-step from downloading Microsoft Help Viewer to installing the SQL Server documentation.

By ronen ariely on 15/04/2016 12:45


At the beginning of the month I lectured at the SQLSaturday # 481 event about the new support of JSON in SQL Server 2016. During the demonstration I showed queries that returns JSON text. SQL Server returns the JSON text formatted as compact text / dense format (one line, no extra space), which is good for most cases that we want to store the text or pass it between machines for example. But, by using a compact format the structure of the JSON document is not readable. 

In NoSQL servers, which based on JSON, we usually have a built-in method to format the JSON document into a format that is easier for humans to read (for example in mongoDB it named "pretty"). This feature must be added to the SSMS or to the SQL Server, in my opinion. In the mean time, for the sake of the lecture, I created simple app that do the job. it is basically a "one line app" using the nuget package Json.NET (Newtonsoft.Json).

After I uploaded the lecture materials (code and presentation), I got requests to upload the application as well. Since I do not like the idea that people execute code without understanding it, and since this is very simple and short code, and most DBA does not develop dot.net application, therefore I decided to post the code, as a step-by-step tutorial for beginners.

By ronen ariely on 15/04/2016 10:39

In this session we will demonstrate several built-in JSON support functions in SQL Server 2016, and we will finish with some tricks and tips to improve performance.

By ronen ariely on 29/02/2016 21:57

Most people heard about the 2000 year bug, which occur once in 1000 years, but less people familiar with much more common bugs related to February 29, which occur approximately every 4 years. Let's named these as the "February 29 bug" or the "Leap year bug".

This blog based on a message posted at MSDN forum, by Yaniv Cohen, who notice that the use of the function ISNULL returns unexpected result, regarding February 29. Is this really unexpected result and we found a BUG?!? Starting with the original issue, as a starting point, I explain how the relevant functions behaves behind the scenes, and the difference between them. I reproduces the "unexpected behavior" using simpler case, and point to the source of the "unexpected behavior". I will present demos that you can execute in order to figure out how the relevant functions works, and in particular gives you the tools to understand the extent of the problem.

By ronen ariely on 12/02/2016 14:49

A very common question that raise in the forums is: why a FULL BACKUP, which was fast in past becomes slow. It can be a one-time issue (specific backup), or permanent. These question are usually not related to big database (100TB +), which we are not using FULL BACKUP usually, and are more common on small-medium databases. In this blog I will provide information, explanation, tips, and some links to read more. This blog is in permanent process of improving. If you have any comment please post it in my Facebook home page.

By ronen ariely on 31/12/2015 20:29

Simple CLR code for SQL Server

By ronen ariely on 23/12/2015 19:34

Several days ago I stumbled a new blog that was published with what seem to me as partial solution. The original blog based on real customer request, and it discusses on using hash function in order to create a first filter, and then working on the filtered data in order to get the final result as requested (two steps job). The blog included only the first step (as a solution for the client?!?), probably focusing on the main idea of using Hash function and splitting the job into two steps.

In this blog firstly I will discuss a very small improvement to the query in the original post, for the first step. Next, I will show a simple full solution (one-step job) for the client's request. 

* Using the original DDL+DML the full solution seem to use the same resource as the first step solution in the original post. As this is not a real case, and I do not have the full description of the original client request or any information regarding the client hardware, software, SQL Sever versions & edition, This blog will not assume that this is a better solution, but just come in order to give another solution, using different approach.

By ronen ariely on 14/12/2015 10:38

I see in the forum this question raised all the time, and yet the solution that people get usually based on looping and complex math, while there is a simple and much better solution as shown in this code. In this short blog I will wrote a simple and VERY fast solution to convert Decimal number to Binary number, Decimal number to Hexadecimal number, and vise versa without using any loop. My solution for binary base is based on using BITWISE.

By ronen ariely on 06/09/2015 14:03

Bitwise Operators performs "wise" "bit by bit" manipulations between two expressions of types integers or binaries (binary, bit, int, smallint, tinyint, varbinary, bigint). There are three Bitwise Operators in T-SQL which includes: & (Bitwise AND), | (Bitwise OR), ^ (Bitwise Exclusive OR). In this short post we will explain the behavior of each of these operators by following a simple practical examples.

** This post include several accessories, which you can use in other cases. For example A function to convert integers base ten number into base two number (without using any direct loop).

By ronen ariely on 16/08/2015 21:45

The question "what is the difference between Unique Constraints and Unique Indexes" always rises in forums. Instead of writing this every time in the each forum and each question, I have decided to post this blog. There are several articles that you can find online regarding this issue. This blog does not intend to replace a full tutorial, but it is more like a preview or a summary of the issue, by showing several differences in practice.

By ronen ariely on 25/05/2015 13:10
In some cases the SQL Server query optimizer engine fail to build the best execution plan. This is the place where our abilities as DBAs can be handles. One of these cases is when we are using windows functions like ROW_NUMBER, which use "ORDER BY" the clustered index. Since our data is already sorted by the clustered index, there is no reason to sort the data again. In simple query the SQL Server query optimizer "understand" it, but in more complex queries it might fail. In this short post, I will show a small trick to improve our query and make sure that the windows function do not waist resource on sorting the data.
By ronen ariely on 07/05/2015 14:17

In this blog I will show step by step how I examined the DateTime2 data, in order to find the actual storing format. The basic process is based on reverse engineer by examining the binary data that we get in CONVERT to VARBINARY(16)

-- check the data as binary show us the format behind the scenes:
DECLARE @d DATETIME2 = '2016-02-27 00:00:00.000' -- My next birthday :-)
* Result: 0x070000000000143B0B

The goals in this blog are: (1) understand how DateTime2 stored in the database, (2) to provide a small glimpse on the process that we can use in order to examine what is going behind the scenes in the Page Data File (for specific data type).

By ronen ariely on 04/05/2015 17:36

There is a common confusion regarding "displaying format" and "storing format" of dates types. We can find lot of questions in the forums regarding "storing format" of dates, which are actually regarding the "displaying format", in external application.

For example questions like: I am getting the error “DateTime data type resulted in an out-of-range value”, or questions like: “how can I change the DateTime column format in the database”.

In this post I will I will focus on SQL DateTime type. I will organizes the issue using the different terms, and I will give some basic solutions for common issues.

By ronen ariely on 17/03/2015 04:09

A trigger is a special kind of stored procedure that automatically executes as responds to event that occurs in the database server. For example on select, update, or others DML or DDL query's execution events.

In this section, we will go step by step, and we will create different triggers, discuss different cases, and mention some useful tips and tricks.

Webinar link: https://www307.livemeeting.com/lrs/0000000379_116/Registration.aspx?pageName=gdx8jdv0r7rmftrk

By ronen ariely on 05/03/2015 20:48

אני רוצה להזמין את כולם לכנס העולמי של קהילת TechNet WIKI הנערך בעוד כמה ימים, בתאריכים 17-18-19 החודש (מרץ). הכנס מועבר באינטרנט  Online , וכמובן אינו כרוך בתשלום. בכנס יועברו הרצאות ודיונים שונים על ידי כ-30 מרצים מכל רחבי העולם שנבחרו על ידי מיקרוסופט.

אני שמח להמנות על המרצים בכנס , ואני מאוד אשמח לראות את חברי הקבוצה, מגיעים להרצאה שלי :-)
ההרצאה שלי תערך ביום רביעי 18 לחודש בשעה 19:45 לפי שעון ישראל (שינויים ופרטים נוספים אני אפרסם בעמד הפייסבוק שלי).
ניתן להירשם כאן!

By ronen ariely on 28/11/2014 13:46
Split Date's Range into Shifts per days
By ronen ariely on 29/10/2014 16:18

In some case we have to use single value, in order to represent a list of elements (values). This is a very common needs in hardware and software developing. All the logics that we discus in this article, can be implements regardless of database use. In this article we will focus on using SQL Server to implement our logics. This might be an external demand (applications for example) or even a result of bad database design. Yet, the needs is clear, we want to store multiple elements in one column. Those elements can be properties list, options, security permissions, dates, or any other data.

In this article we will go over several solutions, using different logics. Our challenge is to find a good logic, which give us a one-to-one correspondence, between each available combination of our elements, to a single value which represent this combination.

By ronen ariely on 17/10/2014 10:27

Sometimes A new version or feature release and a new bug which related to this feature revealed, but sometimes we stuck with a BUG for years.. many years... 15 years even... will it be fix in the next version?

Using User Defined Function In Constraint can lead to a BUG! In this short blog I will just reproduce the BUG and show a simple workaround. This is not a new topic, and you probably can find lot of articles on net,on the same issue, but since it came up again... i decided to post this blog again.

By ronen ariely on 10/10/2014 00:36
Short and simple Stored Procedure to find the pearson who was the last one who deleted a record from a table. Very useful when your boss delete row and start to shout at you why the data is not accurate, or why tour application have a bug :-) 
By ronen ariely on 01/10/2014 10:53
This is small set of script in order to get the connection properties. You can use it in order to compare two execution of the same query on the same database which use different Execution Plan as a result of different Connection Properties.
By ronen ariely on 11/09/2014 12:01

This short blog give a simple example how to use "FOR XML" hint in order to consolidate data in different rows into a single row in the result SET (an operation known as Un-Split data). In this example we are going to group together only rows with the same value in specific column/s. In short we can call this action "Un-split data with grouping". This post was updated on July 25 2018 in order to add a solution using STRING_AGG function which was added in SQL Server 2017.

By ronen ariely on 04/09/2014 09:41

SQL Server can listen to request which come to specific IP address on specific port, or to all request regardless the IP, It can listen to request which come to a Named pipe, as well. By default, SQL server uses port 1433 in TCP/IP, and default instance of Microsoft SQL Server listens on "\\.\pipe\sql\query" named pipe.

In this short article I will show how we can configure those options.

By ronen ariely on 17/08/2014 19:11

לפני כמה ימים נשאלה שאלה מעניינת בפורום של MSDN העולמי (אנגלית). לאחר דיון קצר בפרוום ובפייסבוק נראה שהדיון גווע עם האמירה "אי אפשר". מי שמכיר אותי יודע שאמירה כזו במחשבים לא מקובלת עלי ולמעשה הגעתי לשרשור של ההודעה בדיוק בנקודה זו. לכן החלטתי למצוא דרך פשוטה לפתרון הבעיה. בלוג זה מציג את הרעיון העומד מאחורי הפתרון בצורה מפורטת.

אז מה הבעיה מולה אנחנו עומדים?

שימוש ב DBCC כחלק משאילתת VIEW לא מאפשר בצורה ישירה. אז כיצד אפשר לבצע את זה בכל זאת?

בואו נראה עד כמה הדברים קלים לביצוע :-)
קריאה מהנה...

By ronen ariely on 26/07/2014 23:21

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

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

By ronen ariely on 15/07/2014 20:56


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

על מנת לעמוד במטרות של טור זה, השרת חייב להבטיח שהערכים בחותמת הזמן יהיו תמיד ייחודיים, בין שני זמנים שונים.

שרתי SQL  נועדו לעבודה מסיבית עם נתונים הן מבחינת כמות הנתונים והן מבחינת מהירות העבודה עם הנתונים. השימוש בגדלים מוחלטים כגון סוגי זמנים שונים, לא נותן לנו את הפתרון המתאים בשל תלות ברזולוציה של הנתונים, כפי שנראה בהמשך. כחלק מהצורך לניהול נתונים במערכות כבדות נמצא פתרון בעזרת סוג שונה, שקיבל את השם חותמת זמן - timestamp.

במאמר זה אני אנסה לתת הסבר מקיף ומלא על נושא חותמות זמן בשרתי SQL Server החל מהגרסאות הקדומות שלו ועד השרתים החדשים.

By ronen ariely on 30/05/2014 08:36

Buffer Pool Extension או בשם המקוצר BPE היא תוספת חדשה בגרסאות Standard ו Enterprise של SQL Server 64 Bit. באופן תיאורטי הרחבה זו יכולה לשפר באופן משמעותי את היכולת של מערכות הצורכות זיכרון כגון OLTP. ההרחבה מבוססת על הוספת Buffer Pool היושב על מחיצה מהירה (SSD למשל), ובכך מאפשרת להרחיב את השימוש בזיכרון RAM. ה BPE יראה עבור המשתמש כזיכרון מטמון נוסף. רכיב בשם Buffer Pool Manager אחראי על ניהול המידע בזיכרון המטמון המורחב (תיאום בין ה RAM לבין ההרחבה). ההרחבה נעשית על ידי יצירת מטמון זיכרון הירארכי, כאשר הרמה העליונה מתקיימת ב RAM והרמה השנייה בהרחבה של הזיכרון המטמון.

By ronen ariely on 28/04/2014 13:10

In this short blog I will post a simple dynamic query to search in one operation all string columns ('text','ntext','varchar','nvarchar','char','nchar') of the specific table or in all tables of the database. Next step, we can use CROSS APPLY and SPLIT Function (or user type) in order to expand this query to get result from several searching strings.

By ronen ariely on 09/04/2014 20:46

לפני כמה חודשים העליתי מאמר בנושא הרצת אותה שאילתה דרך אפליקציות שונות. במאמר הקודם, ראינו שהשרת בונה תכנית הרצה שונה לחלוטין לפעמים לאותה שאילתה כאשר היא מורצת דרך תוכנת ה SSMS וכאשר היא מורצת דרך אפליקציית Dot.Net למשל. אם נרצה לסכם את המאמר לשורה אחת אז נוכל להגיד שמקור ההבדל נובע מהגדרות שונות של ה SESSION וההתחברות אל מסד הנתונים והשרת. במאמר זה נדון בנושא הנשמע דומה דומה אבל עם הסבר שונה לחלוטין: הרצת אותה שאילתה בגרסאות שונות של שrת ה SQL. אפשר לנחש מהעובדה שאני כותב בלוג זה שככל הנראה, שגרסאות שונות יתנהגו באופן שונה.

By ronen ariely on 31/03/2014 02:21

In several cases in life we encounter a date range defined by its start and end dates, and we need to split the range into months included in that range. For example, we may need to find the months include with in a given date range, or in a bit more complex demand, we may need to find the exact date range in each of the months include with in a given date range. In this case if our date range is defined by starting date 2014-03-14, and ending date of 2014-05-16 then our result set should be:

From Date       To Date
2014-03-14     2014-03-31
2014-04-01     2014-04-30
2014-05-01     2014-05-16

This short article we will show how we can split a single date's range (start date, end date) into the data set of months which included in that range. In the next step we will use this solution to break a set of date ranges (table) into one set of months.

By ronen ariely on 11/01/2014 19:04

Sub Queries is not permitted in Check Constraint. we will show how to bypass this restriction.

By ronen ariely on 07/01/2014 13:21

Volume Shadow Copy Service (עותק צל) הוא שם של קבוצת ממשקים (interface) מסוג COM interfaces. רכיבי COM אלו מיישמים סביבת הרצה שמאפשרת לבצע גיבויים בזמן שיישומים במערכת ממשיכים לכתוב לאמצעי האחסון ונועלים חלק מהקבצים. במערכות הפעלה של מייקרוסופט VSS נתמך מגרסת XP והלאה. במערכות מבוססות NT אמצעי האחסון מנוהלים על ידי ה Disk Management MMC snap-in. מאחורי הקלעים תוכנה זו עושה שימוש ברכיבי ה VSS.

הליך של ביצוע עותק צל לא יעבוד טוב אם בזמן ביצוע ההעתקה, יבוצעו קריאות לשינויים בקבצים אשר לא ינוהלו בצורה מתאימה לאפליקציות העובדות עם הקבצים. בגרסת SQL 2005 מייקרוסופט סיפקה לנו תמיכה לביצוע עותק צל על ידי כך שסיפקה  כתבן (VSS writer) מתאים בשם SqlServerWriter.

במאמר זה נסביר בצורה כללית מהו עותק צל (VSS) וכיצד הוא עובד; נראה בקצרה כיצד עובדים עם תוכנת הניהול VssAdmin ליצירה, מחיקה וקריאה של עותקי צל במערכת ההפעלה; נראה את הקשר והחשיבות לשרתי SQL; נשים דגש על החלק המעשי כאשר נציג כיצד הדברים עובדים עם קבצי SQL. נראה כיצד אפשר לפתח אפליקציה קטנה להעתקת קבצי SQL תוך כמה דקות של עבודה. אפליקציה זו תאפשר לנו להעתיק קבצים ללא הרצת שאילתת גיבוי וללא צורך בביצוע פעולת detach והורדת מסד הנתונים מהאוויר.

By ronen ariely on 11/12/2013 03:03

Hierarchical data is organized into a tree-like structure. The structure allows representing information using parent/child relationships: each parent can have many children, but each child has no more than one parent (one-to-many relationship). Our records have a unique column (named "ID" in our case). Parents and children are tied together by "pointer" column. In our case we will call our pointer column "ParentID".

Our requirement is to sort the data which is based on the Child-Parent hierarchy according to the tree structure. Each child must be under its parent tree, and all children with the same father, sorted by their ID order (on Appendix A we will show how to sort by any other column).

By ronen ariely on 25/11/2013 18:56
SQL Random String using CLR
By ronen ariely on 28/10/2013 16:44

I see many questions about the difference between Cursor and While Loop in SQL queries. I think this is a fundamental mistake to compare them at all. It's like comparing a car and a boat. We use the car moving on land, and we use a boat to travel at sea. I would not recommend anyone to try the opposite. So what's so confusing?

Let's try to have a brief discussion about "Cursors and Loops" in general and the replacing between them (yes we can car to travel at sea and a boat to move on land).

By ronen ariely on 24/10/2013 15:21

In the last few weeks we are all talking about the upcoming next version of SQL Server 2014. In the process to the final release, Microsoft give us the CTP beta versions, which release with poor documentation. The second best feature I loved on the SQL 2014 (after the In-Memory OLTP) is by sure the new "extremely useful" DMV: sys.dm_exec_query_profiles. This DMV did not work on CTP1, and I understood that it should be ready on CTP2.

Is it working, and is it extremely useful as expected?!?

Let's play with it a bit

By ronen ariely on 20/10/2013 06:40

שרת ה SQL מנהל את קובץ הלוג הפיסי ביחידות וירטואליות הנקראות Virtual Log File או בקיצור VLF.

* בלוג קצר זה מהווה רק הפתיחה להרצאה שלי בנושא זה ויכול לתת את נקודת ההתחלה בלימוד.

By ronen ariely on 16/10/2013 18:41

קדימה להורדות אנשים ובלי תירוצים!

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

By ronen ariely on 13/10/2013 19:11

שאילתות DML עושות שימוש מאחורי הקלעים ב 2 טבלאות: inserted וכן deleted. שרתי SQL  מייצרים טבלאות אלו on-the-fly ומנהלים אותן. אלו טבלאות היושבות בזיכרון (memory-resident tables). משך החיים של טבלאות אלו הוא הטרנזקציה.

נראה בקצרה את השמוש בטבלאות אלו.

By ronen ariely on 25/09/2013 09:44

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

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

נחזור לנושא הבחירות. אחד המועמדים הפעם הוא עמי לוין (Ami Levin) שהיה האחראי על קבוצת המשתמשים בישראל כ 8 שנים, עד שעזב את הארץ לטובת עבודה בארה"ב, שם הוא מבלה את השנה האחרונה. זאת הזדמנות לתמוך בנציג במועצת המנהלים של PASS, אשר יוכל לייצג אותנו ולפעול למעננו.

By ronen ariely on 22/09/2013 03:55

In this short blog I will show how easy it is to add sequence's column to an existing table which already have data. Sequence is a new element added to SQL server 2012 (and can be find in MySQL and Oracle for a very  long long… long time) for implementing automatically assigned integer values. We can even assign a sequence to an existing integer column which already have values in it, in a direct simple query (not like an Identity column).

By ronen ariely on 20/09/2013 07:51
short and simple way to dynamically use bulk insert to import data from all files in specific folder.
By ronen ariely on 15/09/2013 16:32

Some report generated by third party application export data which do have a consistent format. Each line come in different format. Those application use an export's format that design on-the-fly, for each record and each value in the record separately. The format is set by the value itself.

For example if there is data in the field then it will export the data inside a quotation mark, but if there is no data then the data will be blank and without a quotation mark. Moreover some application do not use 'data type' when generating a report. If there is data and the data fit as numeric then again some applications might not use any quotation mark and in the same field on different record a data that do not fit as numeric will be export in quotation mark. We can think about a single CSV file which a specific column is exporting in 6 different formats. In order to use bulk insert directly we have to make sure that all the data is consistent with one format. So how do we do it?!?

By ronen ariely on 08/09/2013 10:16
RAND / NewID and some more build in function have a side-effecting, and cannot be used directly in a UDF. One of the workaround is to create a View and your own UDF as random function
By ronen ariely on 08/09/2013 08:42

Microsoft SQL Server 2005 introduces the max specifier. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. Before SQL Server 2005 we used ntext , text, and image data types.

At this time SQL Server 2012 still support those types, But the SSMS GUI does not fully support it!

By ronen ariely on 29/07/2013 23:31
Every machine on a network has a unique number assigned to it, called an IP address. What is the best way to store a list of IP's in the Database?
By ronen ariely on 10/07/2013 22:56

Case Study:

We need to restore a database which original file size was 100GB. Our disks on the target computer hold 80GB. We know that the database can be shrink to less than 80GB. The problem is that restore operation always restores the files to the same sizes they were before the backed up. There is currently no build-in way to "restore with shrink". But… There is always a BUT :-)

By ronen ariely on 04/07/2013 09:29

במדריך קצר אני אראה כיצד אנחנו יכולים להכין אלמנט CLR בשרת SQL ולהחזיק פרמטר של ה CLR בזיכרון לשימוש משותף של כל הפניות ל CLR. לשם הדוגמה נכין פונקציה קטנה של COUNT. הפונקציה תשמש לנו לספור כמה פעמים פנו אל ה CLR שלנו מהרגע שהשרת התחיל לעבוד. המורכבות היא כמובן לא בחלק של הפיתוח קוד בדוט-נט, כי אם קשור בהגדרות שונות בשרת ה SQL על מנת לאפשר להריץ את הקוד. על מנת לשתף פרמטר בין מופעים שונים של מחלקה ואיפוס הפרמטר שלנו עם ההרצה הראשונה של האלמנט, אנחנו נעשה שימוש בבנאי סטאטי (Static Constructors).

By ronen ariely on 26/05/2013 08:18

הדרך הפשוטה ביותר להעתיק משתמש משרת אחד לשרת אחר היא פשוט לייצר משתמש עם אותה ססמה ואותו שם, אבל האם זה יעבוד בצורה הפשוטה? האם אלו אכן העתקים של אותו משתמש? האם נוכל עתה להעתיק מסד נתונים למשל ולהיכנס אליו עם המתמש הזה שהיו לו הרשאות למסד הנתונים בשרת הקודם? כמובן שלא אחרת לא היה לי על מה לכתוב :-)

By ronen ariely on 06/04/2013 11:03

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

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

By ronen ariely on 29/03/2013 15:08

בבלוג קצר זה אציג דרך יעילה למחיקת רשומות כפולות ללא מציאת הרשומות הכפולות תחילה (חלק זה כבר הצגתי בבלוג אחר) וכאשר אין לנו מפתח ראשי או אינדקס ייחודי שיכול לעזור לנו לקבוע מי הרשומה שאנו רוצים לשמור. אחת האפשרויות שאציג תעשה שמוש בפונקציה ROW_NUMBER על מנת למצוא כפילויות, ואפשרויות שנייה תתבסס על המיקום הפיזי של הרשומה ב page. מיקום זה הוא כמובן ייחודי מפני שכל רשומה נמצאת במיקום אחר.

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

By ronen ariely on 28/03/2013 18:39

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

By ronen ariely on 19/03/2013 18:22
A brief summary of the similarities and differences between 'PRIMARY KEY' and a 'Unique index'
By ronen ariely on 20/02/2013 20:05

COLUMNS_UPDATED היא פונקציה קטנה מובנית בשרתי SQL שנועדה לעבודה עם טריגרים (trigger). פונקציה זו מאפשרת לנו לזהות איזה טורים עודכנו או הוכנסו לטבלה (או לטבלה הוירטואלית VIEW).

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

By ronen ariely on 20/02/2013 19:50

MSDTC או DTC בקיצור הוא שירות קטן שרץ ברקע ומנהל את הטרנזקציות במערכת ההפעלה.

MSDTC משמש לשרתי ה SQL כמו גם לכל אפליקציה אחרת על מנת לנהל טרנזקציות מבוזרות בין כמה מכונות. הרעיון הבסיסי הוא לנהל מצב לדוגמה בו אנחנו מתחילים טרנזקציה הפועלת על מכונה A ומבצעת כמה פעולות ואז נגשים למכונה B לבצע עוד כמה פעולות. אם הפעולות במכונה B נכשלו ולכן בוטלו (rollback) אז הפעולות במכונה A שכבר בוצעו יבוטלו גם כן

By ronen ariely on 08/01/2013 21:35
Activity Monitor is a new features and improvements in SQL Server 2008 Management Studio (SSMS). We can use Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server.
By ronen ariely on 08/01/2013 19:08

SSMA הוא כלי חינמי נתמך מבית מיקרוסופט המפשט את תהליך העברת נתונים ממסד נתונים אחרים שונים לשרתי SQL Server.

By ronen ariely on 30/12/2012 11:48

בהמשך לשאלה שנשאלה באחד הפורומים אני מעלה בלוג קצר זה בנושא מעניין מאוד לדעתי.

השאלה העומדת על הפרק היא כזו:

יש לנו שאילתה של SELECT פשוטה עם סינון לפי טור מסויים. נניח ID = 10000. כמו כן אנחנו רוצים לקחת את נתון זה כחלק מהנתונים החוזרים.

מה יהיה נכון יותר, לרשום את הערך בצורה מפורשת בחלק של ה SELECT או להישתמש בשם של הטור?

-- Using parameter VS using a fixed data
select id from QQ01 where id  = 10000
select 10000 from QQ01 where id  = 10000

* האם אין דרך שלישית טובה יותר?!? נראה דרך נוספת

By ronen ariely on 12/12/2012 19:10

בבלוג קצר זה אני אציג 2 שאילתות לתחזוקה.

בשאילתה הראשונה נמצא את כל תוכניות ההרצה השמורות כרגע בזכרון ה CACH של השרת ואשר הן לא רצו עד עתה יותר מפעם אחת. ההנחה הסיסית שניתן לבצע היא שאם הן לא רצו הרבה פעמים אולי אין בהן צורך בזכרון...

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

By ronen ariely on 13/07/2012 11:58

לא אחת עלינו לעבוד עם נתון של תאריך בפורמט של שרשרת או הפוך. כאשר השרשרת שרוצים לקבל או ליצור היא בפורמט המוכר על ידי שרת ה SQL כפורמט של תאריך אנחנו יכולים לעבוד ישירות עם אחת מהפונקציות המובנות שיש לנו להמרה CONVERT או CAST. אבל אם מדובר בפורמט מעט שונה?

הערה: בשרת SQL בגרסה 2012 נוספה לנו פונקציה חדשה מבוססת על CLR בשם FORMAT. פונקציה זו יעילה מאוד (מבוססת כאמור על CLR) ובאפשרותה ניתן לבצע את כל שיש במדריך זה בעקרון. מדריך זה נועד לעבודה בגרסאות קודמות. עם זה כדי לא להשאיר את הדברים באוויר נראה בסופו של המדריך כיצד ניתן לעבוד עם גרסת 2012 ופונקצית FORMAT.

By ronen ariely on 29/06/2012 20:41

מעונינים לעבור לשרת חדש?

מה נעשה עם כל ההגדרות שיש לנו בשרת הישן? כיצד נזכור מה להעביר כדי לקבל עותק קרוב ככל הניתן לשרת הישן?

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

By ronen ariely on 26/06/2012 18:32

הפרמטר UNION משמש לנו לצורך איחוד שאילתות של שליפת נתונים. ז"א אם יש לנו 2 או יותר שאילתות (בלתי תלויות) מסוג SELECT אשר מחזירות רשומות, ואנו רוצים לאחד את התוצאות של שני השאילתות לתוצאה אחת המחזירה את כל הרשומות של כל אחת מהשאילתות שלנו, אז נוכל לעשות שימוש בפרמטר זה.

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

בבלוג זה אני אדון בבעיות הקשורות ב UNION ובפתרונות

By ronen ariely on 26/06/2012 17:11

יצרתם טבלה זמנית בצורה דינאמית ועתה אתם רוצים לקבל אינפורמציה על הטורים השונים של הטבלה?

בבלוג קצר זה נציג שאילתה למציאת האינפורמציה של טורים בטבלה זמנית

By ronen ariely on 31/05/2012 08:35

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

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

החלק "המיוחד" בו נדון הוא דרישת הלקוח לקבוע לכל שאלה תשובה אחת כברירת המחדל.

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

By ronen ariely on 05/05/2012 22:31

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