Tag: SQLNewBlogger

Opening .SQL Files in SSMS 2016 by Default #SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is an easy one, but you’ll want to try this yourself.

As I’ve been trying to move off the various SSMS versions I have installed to the new, free, updated SSMS 2016, I’ve found that on every system (4 so far) that the default action for .SQL files is to open SSMS 2012 or SSMS 2014. That used to be OK, but it’s annoying. I have SSMS 2016 open (maybe just SSMS?) and waiting on the long load times for 2012/2014 SSMS, especially in demos, is annoying.

If I right click a .SQL file, I get the chance to choose, but what do I choose?

A little experimentation shows that I want SSMS, not SQL Server Management Studio. So let’s change that.

First, open the Control Panel. On Windows 10, this is the Win+I key combination. I find it hard to know where some things are located, but I like the Search in the upper right.

When I type “default,” I get a list of things. The fourth one down is the one I want to click.

This brings me a list of each file type and the app. Boy, there are a lot of types to go through. You’ll want to scroll down about two thirds of the way to find .SQL.

Once I find the file, I click the icon on the right, where it says SQL Server Management Studio. This brings up a “Choose an app” dialog as shown below. I click the SSMS choice.

Now when I double click a file, it just opens in SSMS (2016). Quickly.

SQLNewBlogger

A handy tip. This shows I saw a problem and fixed it. I’m sure some of you could write this in 10 minutes. Some might even built a .reg file or some other automation to show off skills that would help you fix this.

August 11, 2016

Dropping Masking from a Column–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is a quick one. As I experimented with Dynamic Data Masking for the Stairway to Dynamic Data Masking, and writing my Using SQL Compare with Dynamic Data Masking, I needed to remove masking from a column. I didn’t want to rebuild tables, and hoped there was an easy way to ALTER a column.

There is.

Here’s my table. I added masking to this table, but this is the scripted DDL.

CREATE TABLE [dbo].[OrderHeader](
    [OrderID] [INT] IDENTITY(1,1) NOT NULL,
    [Orderdate] [DATETIME2](3) NULL,
    [CustomerID] [INT] NULL,
    [OrderTotal] [NUMERIC](12, 4) MASKED WITH (FUNCTION = 'default()') NULL,
    [OrderComplete] [TINYINT] NULL,
    [SalesPersonID] [INT] NULL,
PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Note, I have masking enabled on the OrderTotal column. If I want to turn this off, I merely run this:

ALTER TABLE dbo.OrderHeader
ALTER COLUMN OrderTotal ADD MASKED WITH (FUNCTION='default()');

This removes masking, and if I script the table again, I get this:

CREATE TABLE [dbo].[OrderHeader](
    [OrderID] [INT] IDENTITY(1,1) NOT NULL,
    [Orderdate] [DATETIME2](3) NULL,
    [CustomerID] [INT] NULL,
    [OrderTotal] [NUMERIC](12, 4) MASKED WITH (FUNCTION = 'default()') NULL,
    [OrderComplete] [TINYINT] NULL,
    [SalesPersonID] [INT] NULL,
PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Easy to turn off if you need to. No application changes, and no security changes.

SQLNewBlogger

A quick, handy piece. Give a why you need this, and show how to do it. Any of you could write this in 5 minutes.

August 4, 2016

Quick Scalar Tables–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This actually comes from Itzik Ben-Gan, by way of Kevin Feasel. Kevin’s post is about CROSS APPLY, which is something I need to play with more because I hadn’t thought about this.

However, the one thing I did see as interesting is the use of VALUES to get a quick table. Here’s an example. Suppose I want to get a list of the last four years in a table for some reason, I can do this:

WITH    cteYears ( Yr )

          AS ( SELECT   2015

               UNION
               SELECT   2014
               UNION
               SELECT   2013
               UNION
               SELECT   2012
             )
    SELECT  Yr
    FROM    cteYears;

Or I could be more compact and do this:

WITH    cteYears ( Yr )
          AS ( SELECT  Yr = y
               FROM ( VALUES (2016), (2015), (2014), (2013) ) a(y)
             )
    SELECT  Yr
    FROM    cteYears;

Is one arguably easier? Certainly some might like the UNION, but it’s a lot more typing, and I think it can be easier to make some mistake in editing. The VALUES clause can easily simulate a table, and you can quickly see groups of rows as well.

WITH    cteYears ( Yr, champ )
          AS ( SELECT   y
                      , c
               FROM     ( VALUES ( 2016, 'Broncos')
                           , ( 2015, 'Patriots')
                           , ( 2014, 'Seahawks')
                           , ( 2013, 'Ravens') ) a ( y, c )
             )
    SELECT  Yr, Champ
    FROM    cteYears;

SQLNewBlogger

This is a quick item I noted, and one I’ve started to use more and more to build quick tables. It seems much easier for me to visualize and create the virtual table. I have started to use this to mock data, or run quick tests.

July 28, 2016

Rename a Primary Key–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Having system named objects is one of those things that people may debate, but as you move to more automated and scripted processes can cause you issues. In the case of a Primary Key (PK), if you do something like this:
```
CREATE TABLE OrderDetail
    (
      OrderID INT IDENTITY(1, 1)
                  PRIMARY KEY ,
      OrderDate DATETIME
    );
```
What you get is something like this:

When you compare that with the same table in another database, what’s the likelihood that you’ll have the PK named PK__OrderDet__D3B9D30C7D677BB4? Probably pretty low.

This means that if you are looking to deploy changes, and perhaps compare the deployment from one database to the next, you’ll think you have different indexes. Most comparison tools will then want to change the index on your target server, which might be using this technique. Or the choice might be something that performs much worse.

What we want to do is get this named the same on all databases. In this case, the easiest thing to do with rename the constraint on all systems. This is easy to do with sp_rename, which is better than dropping and rebuilding the index.

I can issue an easy query to do this:
```
exec sp_rename ‘PK__OrderDet__D3B9D30C7D677BB4’, ‘OrderDetail_PK’
```
When do this, I see the object is renamed.

This table has over a million rows, and while that’s not large, it does take time time to rebuild the index. With a rename, the change is to the metadata and takes a split second.

SQLNewBlogger

These quick, easy, administrator items are great to blog about. As an exercise, if this table has millions of rows, how much longer does the index rebuild take?

This is a great topic for you to write about (or learn about) and show how you can better administer your SQL Server databases.
July 21, 2016

Tag: SQLNewBlogger

Opening .SQL Files in SSMS 2016 by Default #SQLNewBlogger

SQLNewBlogger

Dropping Masking from a Column–#SQLNewBlogger

SQLNewBlogger

Quick Scalar Tables–#SQLNewBlogger

SQLNewBlogger

Rename a Primary Key–#SQLNewBlogger

SQLNewBlogger