SSMS 2017 Table Properties

June 4, 2017

A few new things I’ve noticued snder table properties in SSMS 2016 and 2017.
Table Properties – Microsoft Docs

1) I noticed a new page for ‘Security Predicates’ and wasn’t quite sure what this displays. I finally figured out this refers to (Row Level Security. A security policy is created, which links a table to the function that will determine if a row will be displayed or not. As part of the policy a predicate is created, either a Filter (filter rows read) or a Block (block a write) predicate.
This information is also available by querying the sys.security_predicates view.

2) Under ‘General’, there is a ‘GraphDB’ section with two properties ‘Table is a node table’ and ‘Table is an edge table’. In the upcoming release of the SQL Server 2017 engine there will be graph database functionality added.
Microsoft Docs – SQL Graph
It looks like SQL Server will implement the graph database with separate tables for nodes and edges.


SSMS – New Version Setup

May 31, 2016

June 1 is the release date for SQL Server 2016, so plenty of developers will be downloading and setting up new instances. One pain point is installing a new version of SSMS(SQL Server Management Studio) and getting it configured. I recently upgraded SSMS from 2012 to 2016 RC3 at work so that I could connect to 2014 instances. Here are the steps I went through to customize SSMS after it was installed.

1) Tools=>Options=>Designers=>Table and Database Designers – Uncheck ‘Prevent saving changes that require table re-creation’.
By default, the table designer prevents you from making schema changes to a table that will force the table to be rebuilt.

2) Search and Replace – By default sends results to the ‘Find Results 1’ window – On that window, click the down arrow and select ‘Auto Hide’ to keep the window from displaying next time.
Some people may appreciate this functionality, but this isn’t something I ever use. When searching a script, I want to see the

3) Templates: I use templates as a starting point for several different things, such as creating a new stored procedure or a table. I wrote about templates in an earlier post.
All that’s involved is copying the templates from the old installation to the 2016 directory:
“C:\Users\{User}\AppData\Roaming\Microsoft\SQL Server Management Studio\13.0\Templates\Sql\CS\*.sql”
Where {User} is the Windows user name, and CS is the directory I created for the custom templates. SSMS needs to be closed and re-opened to pick up these changes.

4) Registered servers: The registered servers can be exported to the new edition.
From SSMS 2012 – Right click on your group to export=>Tasks=>Export
Save export file – Select if you want to save user names and passwords with the file.
Saves a regsrvr file.
In new SSMS, right click on Local Server groups=>Tasks=>Import.

5) Toolbar: Select down arrow at end of toolbar then ‘Add or remove buttons’ to select Icons to remove or add to toolbar, to remove anything you won’t use to de-clutter the toolbar.

6) Function Names: I like my function names in lower case. To set up for Intellisense to fill in function names as lower case:
Tools=>Options=>Text Editor=>Transact-SQL=>IntelliSense : ‘Casing for built-in function names’ – Lower Case.

There was one feature I used that wasn’t included in this edition. I frequently export data to a CSV file, so SSMS had a setting to surround any text that contained the delimiter(a comma) with quotes. Hopefully this will be restored in a future update.
1) Tools=>Options=>Query Results=>SQL Server=>Results to grid – ‘Quote strings containing list separators when saving .csv results”

SSMS Keyboard Shortcuts

January 25, 2014

Some keyboard shortcuts in SQL Server Management Studio:

New Query Window: ctl + N
Toggle between open query windows: ctrl + F6
Toggle between tabs on results window: F6
Close query window: ctl + F4
Results Pane on/off: ctl + R
Execution Plan: ctl + M
Comment out selected lines: ctrl + K,C
Un-comment selected lines: ctrl + K,U
Object Explorer: F8
Run Query: F5
Stop running query: alt + break
Parse Query: ctl + F5
Save: ctrl + S
Select multiple lines to edit all lines at once – add commas to all lines, etc.: alt + shift + arrow

A more comprehensive list is at:
Simple Talk

And a good post with shortcuts at:
Tech Recipes

Central Management Server

June 2, 2013

A Central Management Server can be set up in SSMS in order to manage several servers, like execute the same query against each server, or manage policies for all servers.

I was confused at first, because the same functionality is available by creating a Local Server Group in SSMS. You can right click on the group and run queries, evaluate policies, etc.

I found a post that explained why using CMS is preferable:

Basically, the Local Server Group information is stored in a file locally, so each machine has to maintain its own configuration. The CMS information is stored in the MSDB database, so the same configuration can be used by multiple users, plus you can back up the data in case of server failure.

Set up is easy, just open the Registered Servers tab, and right click on ‘Central Management Servers’ to set up the Central server. One that’s added, add the servers that you want to monitor. You can’t include the Management server in this group.

Enable IntelliSense

January 9, 2011

To enable/disable Intellisense:

Tools => Options
Text Editor => Transact-SQL = > Intellisense

Enable IntelliSense
On the same tab is an option for ‘Casing for built-in function names’, so that SQL function can be filled in either in all upper-case or all lower-case

Query Analyzer Word Wrap

November 6, 2010

To enable word wrap in the Query Analyzer:

Options => Text Editor => Transact SQL => General = > Word Wrap

Error saving changes to table

August 15, 2010

I ran across an error in Management Studio:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

To resolve the error, change a setting in Management Studio.

In SSMS, go to Tools => Options => Designers => Table and Database designers.
Make sure the option ‘Prevent Saving Changes That Require Table Re-creation’ is unchecked.