Widening Identity Columns

Aaron Bertrand looks at converting an identity integer into an identity bigint:

This is a very disruptive change to the structure of the table, obviously. (And an interesting side observation: the physical order of the columns, RowID and filler, have been flipped on the page.) Reserved space jumps from 136 KB to 264 KB, and average fragmentation bumps up modestly from 33.3% to 40%. This space does not get recovered by a rebuild, online or not, or a reorg, and – as we’ll see shortly – this is not because the table is too small to benefit.

Note: this is true even in the most recent builds of SQL Server 2016 – while more and more operations like this have been improved to become metadata-only operations in modern versions, this one hasn’t been fixed yet, though clearly it could be – again, especially in the case where the column is an IDENTITY column, which can’t be updated by definition.

Read the whole thing.  The clustered key scenario (which will be later in the series) is a bit more interesting to me, as that would be a more common use case for identity values.

System Views

Robert Sheldon has an introductory-level article on the various system views available within SQL Server:

System views are divided into categories that each serve a specific purpose. The most extensive category is the one that contains catalog views. Catalog views let you retrieve information about a wide range of system and database components—from table columns and data types to server-wide configurations.

Information schema views are similar to some of the catalog views in that they provide access to metadata that describes database objects such as tables, columns, domains, and check constraints. However, information schema views conform to the ANSI standard, whereas catalog views are specific to SQL Server.

In contrast to either of these types of views, dynamic management views return server state data that can be used to monitor and fine-tune a SQL Server instance and its databases. Like catalog views, dynamic management views are specific to SQL Server.

One of the best things the authors of SQL did was require that metadata management be in the same language:  you write SQL code to query metadata the same as if it were normal data.

Analysis Services Permissions

Jens Vestergaard walks through permission scope in SQL Server Analysis Services:

What this post will not be about: The how to setup basic dimension security in SSAS nor How do you manage Security.

In this post, I will highlight the difference between standard NTFS permission scope and the way SSAS handles Allowed and Denied sets when dealing with multiple roles. So if you define multiple roles on your solution, you should be on the lookout, because SSAS has some surprises.

It’s interesting that allowed permissions take precedent over denied permissions, as that’s not the norm for either NTFS or the SQL Server database engine.

Rebuilding Indexes By Partition

Andrea Allred shows how to rebuild indexes one partition at a time:

We have had an index job that has been failing for a while.  This is one of those things you really don’t want to clean up because no one is complaining, but you know you should.  I had heard that I could rebuild one partition at a time, but where to start?  Today, I worked my way through it, so here it is so that you can do it too.

First you need to find the biggest indexes, there is a good chance those are the ones that live on partitions. I am removing Primary Keys.

This can be a real time-saver if a majority of your partitions either are read-only or at least rarely update.

Dynamic Data Masking

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.

Configuring SSMS

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.

Principals

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.

More On OPENROWSET

Kevin Feasel

2016-01-29

Syntax

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.

Finding Indexes

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.

Categories

December 2018
MTWTFSS
« Nov  
 12
3456789
10111213141516
17181920212223
24252627282930
31