Building on my earlier post of JSON and XML data, using the same table today we will take a look at accessing data. With that we can easily grab the entire XML document or the entire JSON object, but what if we want to query a specific users first name or last name. Here is how we would do that …
On a week long theme on XML and JSON in SQL Server, this is the second post. After the first post of just showing how to store these values in the database I am going to take a look at how big the data is and how much we can compress each one with row and page level compression. Lets …
SQL 2019 Page and Row Compression with XML and JSON Read more »
This post is the first in a series of blog posts around using XML and JSON in SQL Server. XML has been supported, although slightly clunky since SQL Server 2005, and it has not changed much. Later versions of SQL Server introduce some JSON parsing which is different from how XML is represented in that JSON data is just stored …
How much is too much when it comes to backup history. What we are talking about here is how much backup history do you want to keep, backup sizes, what file was written, etc. It is nice to keep as much as possible so that you can learn from this over time, but how much is too much depends on …
Earlier in the week I answered a question about reclaiming space, and after that post, I decided to put it into a video. Take a look at the video below from YouTube. Take a look at the video and let me know what you think.
Here is a quest that was submitted on the blog today. HelloJust have a question about a table of mineone of the columns is of type text and its of estimated size of 128gb (99%)Now I do not have permissions to remove the column, but I was told that I can remove data from it (to save space).But of course, …
Setting text field to empty or null does not release the space used Read more »
Recently I had a project where I needed to add a number of logins as SQL Server sysadmin users to several SQL Server instances. Rather than spending hours clicking through the SSMS dialogs to add those users I decided to script it out. The requirement was the given a list of usernames and passwords the user be added as a …
Occasionally I have the need to look up the size of all databases on an instance, based on the log file size and the data file size. The following query shows the size in MB for data and logs for each database on an instance. A handy query that I use often.
When you execute xp_cmdshell SQL Server runs a command at the operating system level similar to the old DOS prompt, or CMD shell. There are many security concerns and misconceptions about xp_cmdshell as documented in an earlier post. The purpose of this post is not to debate whether xp_cmdshell is safe or not, but instead to show what user commands …
Posted 3/29/2020 — Edited 3/30/2020 to fix a couple of copy and paste errors, and to clarify a couple of items. Thanks Charlie for your suggestions. I have worked on many projects that involved doing some query work then writing those results to a file. After copying and pasting the typical sp_OA… methods to write to a file too many …