Press "Enter" to skip to content

Month: January 2016

Dynamic Data Masking

Published 2016-01-29 by Kevin Feasel

Ronit Reger introduces us to Dynamic Data Masking:

DDM can be used to hide or obfuscate sensitive data, by controlling how the data appears in the output of database queries. It is implemented within the database itself, so the logic is centralized and always applies when the sensitive data is queried. Best of all, it is incredibly simple to configure DDM rules on sensitive fields, which can be done on an existing database without affecting database operations or requiring changes in application code.

This looks like a nice way of getting some data masking on the cheap.  It also looks like there are a couple of built-in functions for defining data types, as well as the UNMASK permission so that you don’t need to modify application code to call some type of unmasking function.

Comments closed

Configuring SSMS

Published 2016-01-29 by Kevin Feasel

James Anderson walks us through SQL Server Management Studio, including some configuration:

Sometimes when using SSMS you will see a redline under a table or object name in your T-SQL. This means SSMS thinks the object doesn’t exist in the current database. Usually it’s right, but if you have just created the object, the query editor wont know as it’s local cache is not regularly refreshed. To force a refresh you can hit Ctrl + Shift + R but I always forget keyboard shortcuts. For this I like to add a button to the toolbar.

This is a good intro-level article on SSMS basics and some configuration options.

Comments closed

Principals

Published 2016-01-29 by Kevin Feasel

Jason Brimhall has an introductory-level post on server and database principals:

A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. SQL Server principals come in more than flavor. (This is where a lot of confusion gets introduced.) The different types of principals are database and server. A database principal is also called a database user (sometimes just user). A server principal is also called a server login, or login for short.

Server and database principals are quite a bit different in function and come with different permission sets. The two are sometimes used, in reference, interchangeably, but that is done incorrectly. Today I hope to unravel some of what each is and how to see permissions for each type of principal.

Read the whole thing.

Comments closed

More On OPENROWSET

Published 2016-01-29 by Kevin Feasel

After introducing us to OPENROWSET, Dave Mason is now digging deeper into the topic, looking at data and format files:

Let’s take a closer look at the format file 1 There is a single <RECORD>element with multiple <FIELD> elements that correspond to the fields in the data file. There is also a single <ROW> element with multiple <COLUMN> elements that correspond to table columns. Note the xsi:type attributes that specify the SQL data types for the columns of the returned rowset.

I’ve never had great luck with OPENROWSET reading files and tend to reach for SSIS, but I think part of that is I’d never seen as clear an example as Dave’s.

Comments closed

Finding Indexes

Published 2016-01-29 by Kevin Feasel

Kendra Little talks about index discovery:

Whenever you set up a script to create or drop an index, you want a safety check in there. Drop the index if it exists. Create the index if it doesn’t.

Or do something else programmatically. Checking if an index exists is a pretty frequent task. But there’s no simple function to test if an index exists in SQL Server.

Good article, and in the comments, Kendra talks about the next logical step:  consolidating indexes.

Comments closed

Trusting Constraints

Published 2016-01-29 by Kevin Feasel

Dennes Torres talks about whether a constraint is trustworthy:

If the check constraint is trustable, it can be used by the query optimizer. For example, if the check constraint avoid values below 100 in a field and a query for 50 arrives, the query optimizer uses the check constraint to stop the query.

The query optimizer can only use the check constraint if it’s trustable, otherwise it could exist in the table records with values below 100, according to our example, and the query would loose these records.

Dennes then goes on to show how you can have non-trustworthy constraints and how to fix the issue.

Comments closed

What Is Power BI?

Published 2016-01-28 by Kevin Feasel

Angela Henry gives a high-level overview of Power BI:

There are lots of reasons to use Power BI, other than, it’s so cool.  For instance, Power BI makes it easy to see, in one glance, all the information needed to make decisions.  It also allows you to monitor the most important information about your business.  Power BI makes collaboration easy and when I say easy I mean EZ!  You can also create customized Dashboards tailored to those C-Suite folks or make a completely different dashboard based on the same data for those that actually do the work.

I’m personally astounded at how far visualization tools have come in half a decade.

Comments closed

Spatial Data

Published 2016-01-28 by Kevin Feasel

Dave Mattingly has a multi-part series on spatial data.  This is part 5 (with links to the previous).

 

If you’re interested in spatial data, this looks like a fantastic set of blog posts which mesh well with Dave’s presentation on spatial data.

Comments closed