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.

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.

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.

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.

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.

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.

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.

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.

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...

SQL Server Management Studio version 18.11 was released a few hours ago! What's new in 18.11?

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.

step by step creating UBUNTU Azure VM and install SQL Server

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.

This is a simple demo of using FILESTREAM and FILETABLE, following a question in the Microsoft QnA forum

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.

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.

yesterday Microsoft Released the new Cumulative Update #14 for SQL Server 2019. Read more...

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

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 

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?

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"....

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

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

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 תומך (או לא) בעבודה עם תאריכי ההיג'רי. אני מקדיש את הפוסט הזה לכל חבריי המוסלמים. פוסט זה הוא בעברית ופונה לחברים בישראל שעובדים עם לוח השנה המוסלמי. לצערי הרבה מוסלמים בישראל לא מדברים אנגלית ואני לא דובר ערבית, אבל כולנו כאן מדברים עברית. אני מקווה שהפוסט הזה יהיה מועיל ושימושי עבורך.

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.

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

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?

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.

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.

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.

Always Encrypted - demo in a nutshell (using PowerShell and C# as clients). This is pure demo code from start to end

 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... 

Convert Azure Virtual Machine with SQL Server installed into Azure SQL Server Virtual Machine

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).

Simple working with Primes numbers

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.

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

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".

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'.

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

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.

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.

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.

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.

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?!?

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.

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.

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.

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!

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...

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.

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

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.

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...

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.

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 😃

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

Read more...

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.

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.

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.

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.

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.

THIS POST DOES NOT REPLACE THE SESSION BUT COMES TO SUMMERTIME IT
It is highly recommended to watch the recording first

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

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)

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

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.

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

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.

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.

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

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

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.

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.

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...

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

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...

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.

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

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.

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

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.

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.

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!

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!

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.

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.

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...

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.

SSMS vNext BUG during creating script to delete database from azure SQL Server

Please to post a nice certificate I got from the India Mentor communities leader...

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.

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.

Step by step video demo: Monitoring growth of SQL Server database files using Profiler and Using the 

* 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.

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

 

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.

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...

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...

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...

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?

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.

background

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.

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.

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.

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.

Simple CLR code for SQL Server

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.

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.

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).

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.

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.

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)

* 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).

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.

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

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

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

Split Date's Range into Shifts per days

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.

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.

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 :-) 

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.

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.

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.

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

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

הקדמה

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

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

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

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

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

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.

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

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.

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

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 והורדת מסד הנתונים מהאוויר.

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).

SQL Random String using CLR

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).

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

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

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

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

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

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

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

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

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

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

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).

short and simple way to dynamically use bulk insert to import data from all files in specific folder.

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?!?

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

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!

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?

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 :-)

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

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

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

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

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

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

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

A brief summary of the similarities and differences between 'PRIMARY KEY' and a 'Unique index'

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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