Press "Enter" to skip to content

Curated SQL Posts

T-SQL Tuesday Roundup

Published 2016-01-21 by Kevin Feasel

Robert Davis hosted T-SQL Tuesday, and he has a nice roundup of posts:

Thanks to everybody that participated in this month’s T-SQL Tuesday. A big thanks to everyone who wrote a participating blog post, and a really huge thanks to everyone who read the posts shared by this month’s participants. If you follow one of the links on this round-up page, I will kindly ask that you leave a comment on a blog post that you read if it teaches you something, gives you a new perspective, or makes you think. A blog post is just a bunch of words until somebody reads it, and blog writers love it when they hear that their post resonated with someone on some level.

Thanks to Robert for hosting T-SQL Tuesday #74.

Comments closed

Trust Relationship Failed

Published 2016-01-21 by Kevin Feasel

Chrissy LeMaire ran into a trust issue one one node of a cluster:

You know what’s scary as hell? When one node of an important cluster loses its trust relationship with the domain and you see the error “the trust relationship between this workstation and the primary domain failed”. That happened to me late last year with one of my SQL Server 2008 R2 nodes. The scary part was that I just didn’t know what to expect. The fix could be simple, or it could require a node rebuild.

Trust no one.

Comments closed

Setting Up A Scrub Server

Published 2016-01-21 by Kevin Feasel

Tom Norman talks about setting up a data scrubbing server:

With that said, when I setup a Scrub server, it is in a very secure area where the data access is very, very limited. For example, in my current company, the server is in a separate domain from Production and QA/Dev. Only DBAs are allowed to access this server. If you have multiple DBAs at your location, you may want to even limit which DBAs have access to this server. Our goal is to automate the entire scrubbing process so no one has to access the data including copying backup files from Production and to a shared scrub location for QA/Dev to retrieve.

Scrub servers are a way of stripping personally identifiable or sensitive information from production data so developers can safely use the data in lower, less secure environments.

Comments closed

Power BI Gateways

Published 2016-01-21 by Kevin Feasel

Ginger Grant talks about Personal and Enterprise Power BI Gateways:

The Personal Gateway takes the data and imports it into Power BI. If you want to extract data from a variety of different places such as an Oracle Database, and Excel Spreadsheets, the Personal Gateway will support this, and the Enterprise Gateway won’t.   Remember the Enterprise Gateway only connects to three different data sources, and Excel and Oracle are not on that list. If you want to manage connection and refresh of the data as the administrator or provide access to the data to everyone who needs it, use the Personal Gateway.

It sounds like these are currently different enough that “both” might be the correct option within an organization, at least until Enterprise Gateway adds the missing features.

Comments closed

Columnstore Pagination

Published 2016-01-20 by Kevin Feasel

Aaron Bertrand investigates whether columnstore indexes help with pulling out specific “pages” of data:

Since then, I’ve wondered if ColumnStore indexes (both clustered and non-clustered) might help any of these scenarios. TL;DR: Based on this experiment in isolation, the answer to the title of this post is a resounding NO. If you don’t want to see the test setup, code, execution plans, or graphs, feel free to skip to my summary, keeping in mind that my analysis is based on a very specific use case.

I actually would have been surprised to find the answer here to be “yes.”  Columnstore is designed with aggregation in mind, rather than pulling out a fairly small subset of the data.

Comments closed

Finding A Page

Published 2016-01-20 by Kevin Feasel

Kenneth Fisher is looking for the database page tied to a particular row:

It’s not one of those things you have to do frequently but every now and again you need to know what page a particular row is on. It’s not terribly difficult. There is a virtual column called %%physloc%%, but unfortunately it’s not terribly useful on it’s own.

Finding a record’s page probably isn’t something you want to do every day; this is more one of those “once in a great while” activities which can help with troubleshooting.

Comments closed

Naming Standards

Published 2016-01-20 by Kevin Feasel

Aaron Bertrand talks about naming standards:

I’ve long been a proponent of not caring about which naming standards you use, but I do find it very important that your standards follow these three basic rules:

  1. The conventions make sense. You should be able to argue why the chosen convention is better than an alternative, and it can’t just be because you like it better. This doesn’t mean you have to win that argument, just that you should be arguing for something tangible.

  2. The entire team is on board. You should all agree on a standard before implementation, and any changes you make over time should be by committee.

  3. You don’t make exceptions. Even if you’re a one-person team, if you’re going to bother having a standard, it needs to be used consistently. It’s amazing how quickly exceptions can become the new rules.

If you want to talk subjectivity, I disagree with the idea that tables should be plural, as I tend to think terms of an entity (e.g., Person) which contains attributes, rather than the collection of entities which contain a specific combination of attributes.  Regardless, “set a standard and stick to it” is important advice.

Comments closed

OPENROWSET And BULK Rowsets

Published 2016-01-20 by Kevin Feasel

Dave Mason looks at the OPENROWSET function:

The built-in SQL Server function OPENROWSET() provides a way to access remote data from an OLE DB data source. It can be used with the BULK rowset provider to read data from a file without loading the data into a target table. This post will show the basics to get started with OPENROWSET(), the BULK rowset provider, and text files of fixed-width data fields.

For permanent connections, look into linked servers.  But for one-off things, OPENROWSET works fine.

1 Comment

What’s New In Power BI 2.0?

Published 2016-01-20 by Kevin Feasel

Meagan Longoria tells us what’s in Power BI version 2.0:

The Microsoft Power BI team was fast and furious in 2015, and there are no indications they are slowing down in 2016. If you haven’t checked out Power BI V2 since it was first released last summer, you might want to take another look. Many features have been added and updated since then. Based upon the release schedules since July, it seems there are 3 separate release cycles for Power BI:

  • The Power BI Service (PowerBI.com) gets weekly updates.

  • The Power BI Desktop tool gets monthly updates.

  • The Power BI mobile apps get monthly updates.

I expect no fewer than 6 updates per week from the Power BI team.

Comments closed