A Fine Slice Of SQL Server
Bryan Shalloway continues a series:
Part 1 of my series of posts on building prediction intervals used data held-out from model training to evaluate the characteristics of prediction intervals. In this post I will use hold-out data to estimate the width of the prediction intervals directly. Doing such can provide more reasonable and flexible intervals compared to analytic approaches.
Click through for the article, and be sure to check out part 1 if you haven’t already.
Comments closedEsat Erkec shows us how to use the Bulk Copy Program (BCP) to bulk load data into SQL Server:
If the installed version is older than the last version, we can download and install the latest version from the Microsoft website. The main capability of the SQL Server BCP is not much complex because it can only run with several arguments. The syntax of the BCP is like below:
bcp {table|view|”query”} {out|queryout|in|format} {data_file|nul} {[optional_argument]…}For example, if we want to export any data of a table to a text file, we have to specify the table name, the out option, and the data file. The following command will export the Production table into the specified text file.
bcp AdventureWorks2017.Production.Product out C:\ExportedData\Product.txt -S localhost -T –w
I don’t know if I’m the only person for which this is true, but the data file format has always been a royal pain for me to get right, to the point where I’d happily build an SSIS package to perform bulk loading over having to use BCP myself.
2 CommentsJack Vamvas answers a question:
I use Powershell extensively to manage SQL Server and the Windows OS. A common problem is to identify location of the Windows Recycle Bin and clear the contents down – particuarly if there is a space issue.
How can I locate the Windows Recycle Bin and clear it down?
Read on for the Powershell v5 solution as well as the solution which works for earlier versions.
Comments closedNick Edwards has bad news and a workaround:
Have you ever come across an issue where your Power BI matrix column widths just aren’t the same width and visually just don’t look right?
Unfortunately (as of April 21’) there is no easy way to make all column widths equal in the format pane of a matrix visual.
However there is a hack to set the width of your all columns in a matrix so that they are all equal and pixel perfect with DAX!
Click through to see how. It’s a hack in all the finest ways: clever, unexpected, and a bit unwieldy.
Comments closedErik Darling has an interesting scenario for us:
This post isn’t meant to dissuade you from using EXISTS or NOT EXISTS when writing queries. In fact, most of the time I think they make a lot of sense.
But weird things can happen along the way, especially if you don’t have supporting indexes, or if supporting indexes aren’t chosen by the optimizer for various reasons.
In this post, I’m going to show you a query plan pattern that can occur in semi-join plans, and what you can do about it.
Click through for the problem and the solution. Me? I don’t like semi-joins on principle. Either join or don’t join; give me none of these cowardly half-measures. I’m not sure what to think about anti-semi-joins because I’m apparently anti semi-join for the purposes of this belabored joke, but I’m a bit suspicious of them as well.
Comments closedDeepthi Goguri digs into data page internals:
Each byte you see in the picture has a purpose. The first two blocks containing a byte, Status Bit A and Status Bit B contains the bitmap information about the row, like if the row is logically been deleted/ghosted, row type information, versioning tag, if the row contains any NULL values, Variable length columns. The next 2 bytes is used for storing the length of the fixed length data. The next n bytes are for storing the fixed length data itself. There is a null bitmap after that which will have both the 2-byte column count in the row and null bitmap array. Regardless of if the column in null or not, each and every column will have one bit per every column.
Read on to see how those 8kb pages fill up so quickly.
Comments closedChad Callihan has a few tips for making that sa login safer:
Every SQL Server install includes the sa login as a sysadmin. This can be good for consistency; however, that also makes it a prime target for attackers trying to get into your SQL Server. That is one of many reasons why you should make the following changes to protect your sa login from being used in an attack.
Click through for the list. Troll me says the best answer is to rename sa (like Chad mentions), create a new account called sa, and write a script to add a rule to your firewall banning the IP of anybody who logs in with this account.
Comments closedJeffrey Hicks doesn’t have time for parameters:
A quick post today to remind you of a way to make PowerShell even easier to use. PowerShell cmdlets and functions obviously help us get a lot done, and most commands offer a number of parameters to customize what needs to be done. Unless you love typing, you probably would like an even easier way to use PowerShell. Let me show you.
Read on to see how. My one concern here would be if you forget about the parameters, you may end up with unexpected results compared to people without your default parameters. This seems to me a case for using these defaults somewhat sparingly.
Comments closedEdwin Sanchez takes us through a few issues you might run into with CASE expressions:
If you are trying to scan the subheadings to see what the issues are, I can’t blame you. Readers, including me, are impatient.
I trust that you already know the basics of SQL CASE, so, I won’t bore you with long introductions. Let’s dig into a deeper understanding of what’s happening under the hood.
Click through for the list.
Comments closedJosh Darnell walks us through an error when setting up an Availability Group:
For setting up the environment, I was following this really in-depth guide from former Data Platform MVP and current Microsoft employee Ryan J. Adams: Build a SQL Cluster Lab Part 1
The guide is generally fantastic, and provides a lot of good insight into the non-SQL Server related aspects of setting up an Availability Group. I’d highly recommend checking it out if you’re interested in that sort of thing.
Relevant to this post, he has provided a diagram of how the different networks are configured:
If you’re very experienced with networking, you may already have some idea of what the problem is going to be. Don’t spoil it for everyone else okay?
I’ll admit I did not have an idea of what the problem was.
Comments closed