SQL Server Management Studio

What's new in this version:

- SMS 18.0 is the first release that is fully aware of SQL Server 2019 (compatLevel 150)
- Support for "BATCH_STARTED_GROUP" and "BATCH_COMPLETED_GROUP" in SQL Server 2019 and SQL Managed Instance
- SMO: Added support for UDF Inlining
- GraphDB: Add flag in showplan for Graph TC Sequence
- Always Encrypted: added support for AEv2 / Enclave
- Always Encrypted: connection dialog has a new tab "Always Encrypted" when the user clicks on the "Options" button to enable/configure Enclave support
- The current size is ~500 MB, approximately half of the SSMS 17.x bundle
- The new shell (SSMS is based on Visual Studio 2017 15.9.11) unlocks all the accessibility fixes that went into both SSMS and Visual Studio, and includes the latest security fixes
- Much work went in to address accessibility issues in all the tools (SSMS, DTA, and Profiler)

"This option is available from both the command line (useful for unattended installation) and the setup UI. From the command line, pass this extra argument to the SSMS-Setup-ENU.exe:
- SSMSInstallRoot=C:MySSMS18
- By default, the new install location for SSMS is: %ProgramFiles(x86)%Microsoft SQL Server Management Studio 18Common7IDEssms.exe This does not mean that SSMS is multi-instance."
- The block on mixed languages setup has been lifted. You can, for example, install SSMS German on a French Windows. If the OS language does not match the SSMS language, the user needs to change the language under Tools > Options > International Settings, otherwise SSMS will show the English UI
- Much effort went in to avoid sharing components with SQL Engine, which often resulted in serviceability issues (one clobbering the files installed by the other)
- We upgraded our minimum requirement from NetFx4.6.1 to NetFx4.7.2: this allows us to take advantage of the new functionality exposed by the new framework
- When SSMS 18 is started for the first time, the user will be prompted to migrate the 17.x settings. The user setting files are now stored as a plain XML file, thus improving portability and possibly allowing editing
- High DPI is now enabled by default
- For details, see Download Microsoft OLE DB Driver for SQL Server
- Due to the new dependency on NetFx 4.7.2, SSMS 18.0 does not install on Windows 8 and older versions of Windows 10 and Windows Server 2016. SSMS setup will block on those systems. Windows 8.1 is still supported
- Path to SSMS.EXE (and tools in general) is not added to the path anymore. Users can either manually add it, or if on a modern Windows computer, use on the Start menu
- In the past, SSMS was selectively loading only well-known packages, thus requiring developers to register their own package. This is no longer the case
- Exposing AUTOGROW_ALL_FILES config option for Filegroups in SSMS
- Removed risky 'lightweight pooling' and 'priority boost' options from SSMS GUI. For details, see Priority boost details – and why it’s not recommended
- New menu and key bindings to creates files: CTRL+ALT+N. CTRL+N will continue to create a new query
- New Firewall Rule dialog now allows the user to specify a rule name, instead of automatically generating one on behalf of the user
- Improved intellisense in Editor especially for v140+ T-SQL
- Added support in SSMS UI for UTF-8 on collation dialog
- Switched to "Windows Credential Manager" for connection dialog MRU passwords. This addresses a long outstanding issue where persistence of passwords was not always reliable
- Improved support for multi-monitor systems by making sure that more and more dialogs and windows pop up on the expected monitor
- Exposed the 'backup checksum default' server configuration in the new Database Settings page of the Server Properties Dialog
- Exposed "maximum size for error log files" under "Configure SQL Server Error Logs"
- Added "Migrate to Azure" under Tools menu – We have integrated Database Migration Assistant and Azure Database Migration Service to provide quick and easy access to help accelerate your migrations to Azure
- Added logic to prompt the user to commit open transactions when "Change connection" is used
- Added menu item to start/download Azure Data Studio
- Added "Start Azure Data Studio" menu item to Object Explorer
- When right-clicking on a database node in OE, the user is presented with context menus to either run a query or create a new notebook in Azure Data Studio
- SLO/Edition/MaxSize database properties now accept custom names, making it easier to support future editions of Azure SQL databases
- Added support for recently added vCore SKUs (General Purpose and Business Critical): Gen4_24 and all the Gen5
- Added new "AAD logins" as a new login type in SMO and SSMS when connected to an Azure SQL Managed Instance
- Rehash RTO (estimated recovery time) and RPO (estimated data loss) in SSMS Always on Dashboard
- The Enable Always Encrypted checkbox in the new Always Encrypted tab in the Connect to Server dialog now provides an easy way to enable/disable Always Encrypted for a database connection

"Several enhancements have been made to support Always Encrypted with secure enclaves in SQL Server 2019 preview:
- A text field for specifying enclave attestation URL in the Connect to Server dialog (the new Always Encrypted tab)
- The new checkbox in the New Column Master Key dialog to control whether a new column master key allows enclave computations
- Other Always Encrypted key management dialogs now expose the information on which column master keys allow enclave computations."
- Changed authentication method from Storage Account Key based to Azure AD-based authentication
- Updated list of known audit actions to include FEATURE RESTRICTION ADD/CHANGE GROUP/DROP
- Reorganized data classification task menu: added sub menu to the database tasks menu and added an option to open the report from the menu without opening the classify data window first
- Added new feature 'Data classification' to SMO. Column object exposes new properties: SensitivityLabelName, SensitivityLabelId, SensitivityInformationTypeName, SensitivityInformationTypeId, and IsClassified (read-only). For more information, see ADD SENSITIVITY CLASSIFICATION (Transact-SQL)
- Added new "Classification Report" menu item to the "Data Classification" flyout
- Updated recommendations

"Added a new option under > Tasks > Database Upgrade. This starts the new Query Tuning Assistant (QTA) to guide the user through the process of:
- Collecting a performance baseline before upgrading the database compatibility level
- Upgrading to the desired database compatibility level
- Collecting a second pass of performance data over the same workload
- Detect workload regressions, and provide tested recommendations to improve workload performance
- This is close to the database upgrade process documented in query store usage scenarios, except for the last step where QTA does not rely on a previously known good state to generate recommendations"
- Added support to import/export data tier application with graph tables
- Added logic to notify the user that an import may have resulted in a renaming of the columns
- Added support to allow customers to schedule SSIS packages on Azure-SSIS IRs that are in Azure Government cloud
- When you use SQL Agent of Azure SQL Managed Instance via SSMS, you can configure parameter and connection manager in SSIS agent job step
- When connecting to Azure SQL DB/Managed Instance, you can connect to it with default as initial db
- Added a new entry item Try SSIS in Azure Data Factory under "Integration Services Catalogs" node, which can be used to launch the "Integration Runtime Creation Wizard" and create "Azure-SSIS Integration Runtime" quickly
- Added Create SSIS IR button in "Catalog Creation Wizard", which can be used to launch the "Integration Runtime Creation Wizard" and create "Azure-SSIS Integration Runtime" quickly
- ISDeploymentWizard now supports SQL Auth, Azure Active Directory Integrated Auth, and Azure Active Directory Password Auth in command-line mode
- Deployment Wizard now supports creating and deploying to Azure Data Factory SSIS Integration Runtime
- Add new menu items for "CREATE OR ALTER" when scripting objects
- Improved usability of some reports (Overall Resource Consumptions) by adding thousands separator to numbers displayed on the Y-axis of the charts
- Added a new Query Wait Statistics report
- Added "Execution Count" metric to "Tracked Query" View
- Added support for non-default port specification feature in Replication Monitor and SSMS
- Added actual time elapsed, actual vs estimated rows under ShowPlan operator node if they are available. This will make actual plan look consistent with Live Query Stats plan
- Modified tooltip and added comment when clicking on Edit Query Button for a ShowPlan, to indicate to user that the ShowPlan might be truncated by the SQL engine if the query is over 4000 characters
- Added logic to display the "Materializer Operator (External Select)"
- Add new showplan attribute BatchModeOnRowStoreUsed to easily identify queries that are using the " batch-mode scan on rowstores" feature. Anytime a query performs batch-mode scan on rowstores, a new attribute (BatchModeOnRowStoreUsed="true") gets added to StmtSimple element
- Added Showplan Support to LocalCube RelOp for DW ROLLUP and CUBE
 -New LocalCube operator for the new ROLLUP and CUBE aggregation feature in Azure SQL Data Warehouse
- Extend SMO Support for Resumable Index Creation
- Added new event on SMO objects ("PropertyMissing") to help application authors to detect SMO performance issues sooner
- Exposed new DefaultBackupChecksum property on the Configuration object which maps to the "backup checksum default" server configuration
- Exposed new ProductUpdateLevel property on the Server object, which maps to the servicing level for the version of SQL in use (for example, CU12, RTM)
- Exposed new LastGoodCheckDbTime property on Database object, which maps to "lastgoodcheckdbtime" database property. If such property is not available, a default value of 1/1/1900 12:00:00 AM will be returned
- Moved location for RegSrvr.xml file (Registered Server configuration file) to "%AppData%MicrosoftSQL Server Management Studio" (unversioned, so it can be shared across versions of SSMS)
- Added "Cloud Witness" as a new quorum type and as a new resource type
- Added support for "Edge Constraints" in both SMO and SSMS
- Added cascade delete support to "Edge Constraints" in both SMO and SSMS
- Added support for data classification "read-write" permissions
- Enabled Vulnerability Assessment tasks menu on Azure SQL DW
- Change the set of vulnerability assessment rules that are run on Azure SQL Managed Instance servers, so that "Vulnerability Assessment" scan results will be consistent with the ones in Azure SQL DB
- "Vulnerability Assessment" now supports Azure SQL DW
- Added a new exporting feature to export the vulnerability assessment scan results to Excel
- XEvent Viewer: enabled showplan window for more XEvents

Details:
- Fixed a source of common SSMS crashes related to GDI objects
- Fixed a common source of hangs and poor performance when selecting "Script as Create/Update/Drop" (removed unnecessary fetches of SMO objects)
- Fixed a hang when connecting to an Azure SQL DB using MFA while ADAL traces are enabled
- Fixed a hang (or perceived hang) in Live Query Statistics when invoked from Activity Monitor (the issue manifested when using SQL Server authentication with no "Persist Security Info" set)
- Fixed a hang when selecting "Reports" in Object Explorer which could manifest on high latency connections or temporary non-accessibility of the resources
- Fixed a crash in SSSM when trying to use Central Management Server and Azure SQL servers. For details, see SMSS 17.5 application error and crash when using Central Management Server
- Fixed a hang in Object Explorer by optimizing the way IsFullTextEnabled property is retrieved
- Fixed a hang in "Copy Database Wizard" by avoiding to build unnecessary queries to retrieve Database properties
- Fixed an issue that was causing SSMS to hang/crash while editing T-SQL
- Mitigated an issue where SSMS was becoming unresponsive when editing large T-SQL scripts
- Fixed an issue that was causing SSMS to run out of memory when handling the big datasets returned by queries
- Fixed an issue there the "ApplicationIntent" was not passed along in connections in "Registered Servers"
- Fixed in issue where the "New XEvent Session Wizard UI" form was not rendered properly on High DPI monitors
- Fixed an issue where trying to import a bacpac file
- Fixed an issue where trying to display the properties of a database (with FILEGROWTH > 2048GB) was throwing an arithmetic overflow error
- Fixed an issue where the Perf Dashboard Report was reporting PAGELATCH and PAGEIOLATCH waits that could not found in subreports
- Another round of fixes to make SSMS more multi-monitor aware by having it open dialog in the correct monitor
- Fixed an issue where the "Advanced Settings" to the AS XEvent UI was clipped
- Fixed an issue where DAX parsing throws file not found exception
- Fixed an issue where the database list was not populated correctly for Azure SQL Database query window when connected to a user database in Azure SQL DB instead of to master
- Fixed an issue where it was not possible to add a "Temporal Table" to an Azure SQL database
- Enabled the Statistics properties sub menu option under menu Statistics in Azure, since it has been fully supported for quite some time now
- Fixed issues in common Azure UI control that was preventing the user from displaying Azure subscriptions (if there were more than 50). Also, the sorting has been changed to be by name rather by Subscription ID. The user could run into this one when trying to restore a backup from URL, for example
- Fixed an issue in common Azure UI control when enumerating subscriptions which could yield a "Index was out of range. Must be non-negative and less than the size of the collection." error when the user had no subscriptions in some tenants. The user could run into this one when trying to restore a backup from URL, for example
- Fixed issue where Service Level Objectives were hardcoded, thus making it harder for SSMS to support newer Azure SQL SLOs. Now, the user can sign in to Azure and allow SSMS to retrieve all the applicable SLO data (Edition and Max Size)
- Improved/polished the support for Managed Instances: disabled unsupported options in UI and and a fix to the View Audit Logs option to handle URL audit target
- "Generate and Publish scripts" wizard scripts unsupported CREATE DATABASE clauses
- Enable Live Query Statistics for Managed Instances
- Database properties->Files was incorrectly scripting ALTER DB ADD FILE
- Fixed regression with SQL Agent scheduler where ONIDLE scheduling was chosen even when some other scheduling type was chosen
- Adjusting MAXTRANSFERRATE, MAXBLOCKSIZE for doing backups on Azure Storage
- The issue where tail log backup is scripted before RESTORE operation (this is not supported on CL)
- Create database wizard not scripting correctly CREATE DATABASE statement
- Special handling of SSIS packages within SSMS when connected to Managed Instances
- Fixed an issue where an error was displayed while trying to use "Activity Monitor" when connected to Managed Instances
- Improved support for AAD Logins (in SSMS Explorer)
- Improved scripting of SMO Filegroups objects
- Improved UI for credentials
- Added support for Logical Replication
- Fixed an issue which was causing right-clicking on a database and choosing 'Import data-tier application' to fail
- Fixed an issue which was causing right-clicking on a "TempDB" to show errors
- Fixed an issue where trying to scripting ALTER DB ADD FILE statement in SMO was causing the generated T-SQL script to be empty
- Improved display of Managed Instances server specific properties (hardware generation, service tier, storage used and reserved)
- Fixed an issue where scripting of a database ("Script as Create...") was not scripting extra filegroups and files
- Fixed an issue where the user was unable to attach a database when physical filename of .mdf file does not match the original filename
- Fixed an issue where SSMS might not find a valid restore plan or might find one which is sub-optimal
- Fixed issue where the "Attach Database" wizard was not displaying secondary files that were renamed. Now, the file is displayed and a comment about it is is added (e.g. "Not Found")
- Generate scripts/Transfer/Copy Database Wizard try to create a table with an in memory table doesn't force ansi_padding on
- Transfer Database task/Copy Database Wizard broken on SQL Server 2017 and SQL Server 2019
- Generate scripts/Transfer/Copy Database Wizard script table creation before creation of associated external data source
- Enabled the removal of usernames from previous username list by pressing the DEL key. For details, see Allow deletion of users from SSMS login window
- Fixed an issue DAC Import Wizard was not working when connected using AAD
- Fixed an issue when saving classifications in the data classification pane while there are another data classification panes open on other databases
- Fixed an issue where the user was not able to import a Data-tier Application (.dacpac) due to limited access to the server (e.g. no access to all the databases on the same server)
- Fixed an issue which was causing the import to be extremely slow when many databases happened to be hosted on the same Azure SQL server
- Added support for Rejected_Row_Location in template, SMO, intellisense, and property grid
- Fixed an issue where the "Import Flat File Wizard" was not handling double quotes correctly (escaping)
- Fixed an issue where related to incorrect handling of floating-point types (on locales that use a different delimiter for floating points)
- Fixed an issue related to importing of bits when values are 0 or 1
- Fixed an issue where floats were entered as nulls
- Fixed an issue where the import wizard was not able to process negative decimal values
- Fixed an issue where the wizard was not able to import from single column CSV files
- Will be in SSMS 17.9] Fixed issue where Flat File Import does not allow changing destination table when table is already existing
- Improved logic around honoring the online/offline modes (there may still be a few issues that need to be addressed)
- Fixed the "View Help" to honor the online/offline settings
- Fixed an issue where roles in "Fail Over Availability Groups" wizard was always displayed as "Resolving"
- Fixed an issue where SSMS was showing truncated warnings in "AG Dashboard"
- Fixed a SxS issue that deployment wizard will fail to connect to sql server when SQL Server 2019 and SSMS 18.0 are installed on the same machine
- Fixed an issue that maintenance plan task can’t be edited when designing the maintenance plan
- Fixed an issue that deployment wizard will stuck if the project under deployment is renamed
- Enabled environment setting in Azure-SSIS IR schedule feature
- Fixed an issue that SSIS Integration Runtime Creation Wizard stops responding when the customer account belongs to more than 1 tenants
- Fixed crash while using Job Activity Monitor (with filters)
- Fixed an issue where SSMS was throwing an "Object cannot be cast from DBNull to other types" exception when trying to expand "Management" node in OE (misconfigured DataCollector)
- Fixed an issue where OE was not escaping quotes before invoking the "Edit Top N..." causing the designer to get confused
- Fixed an issue where the "Import Data-Tier application" wizard was failing to launch from the Azure Storage tree
- Fixed an issue in "Database Mail Configuration" where the status of the SSL checkbox was not persisted
- Fixed an issue where SSMS grayed out option to close existing connections when trying to restore database with is_auto_update_stats_async_on.
- Fixed an issue where right-clicking on nodes in OE the (e.g. "Tables" and wanting to perform an action such as filtering tables by going to Filter > Filter Settings, the filter settings form can appear on the other screen than where SSMS is currently active)
- Fixed a long outstanding issue where the DELETE key was not working in OE while trying to rename an object
- When displaying the properties of existing database files, the size appears under a column "Size (MB)" instead of "Initial Size (MB)" which is what is displayed when creating a new database
- Disabled the "Design" context-menu item on "Graph Tables" since there is no support for those kind of tables in the current version of SSMS
- Fixed an issue where the "New Job Schedule" dialog was not rendering properly on High DPI monitors
- Fixed/improved the way an issue where a database size ("Size (MB)") is displayed in Object Explorer details: only 2 decimal digits and formatted using the thousands separator
- Fixed an issue that was causing the creation of a "Spatial Index" to fail with an error like "To accomplish this action, set property PartitionScheme"
- Minor performance improvements in Object Explorer to avoid issuing extra queries, when possible
- Extended logic to request confirmation when renaming a database to all the schema objects (the setting can be configured)
- Added proper escaping in Object Explorer filtering
- Fixed/improved the view in Object Explorer Details to show numbers with proper separators
- Fixed context menu on "Tables" node when connected to SQL Express, where the "New" fly-out was missing, Graph tables were incorrectly listed, and System-Versioned table was missing
- Overall perf improvements - Generate Scripts of WideWorldImporters takes half the time compared to SSMS 17.7
- When scripting objects, DB Scoped configuration which have default values are omitted
- Don't generate dynamic T-SQL when scripting
- Omit the graph syntax "as edge" and "as node" when scripting a table on SQL Server 2016 and earlier
- Fixed an issue where scripting of database objects was failing when connecting to an Azure SQL DB using AAD with MFA
- Fixed an issue where trying to script a spatial index with GEOMETRY_AUTO_GRID/GEOGRAPHY_AUTO_GRID on a Azure SQL DB was throwing an error
- Fixed an issue which was causing the database scripting (of a Azure SQL database) to always target an on-prem SQL, even if the "Object Explorer" scripting settings were set to match the source
- Fixed an issue where trying to script a table in a SQL DW database involving clustered and non-clustered indexes was generating incorrect T-SQL statements
- Fixed an issue where trying to script a table in a SQL DW database with both "Clustered Columnstore Indexes" and "Clustered Indexes" was generating incorrect T-SQL (duplicate statements)
- Fixed Partitioned table scripting with no range values (SQL DW databases)
- Fixed an issue where the user would be unable to script an audit/audit specification SERVER_PERMISSION_CHANGE_GROUP
- Fix and issue where the user is unable to script statistics from SQL DW
- Fixed an issue where the "Generate script wizard" shows incorrect table having scripting error when "Continue scripting on Error" is set to false
- Improved script generation on SQL Server 2019
- Added "Aggregate Table Rewrite Query" event to Profiler events
- Fixed an issue where a "DocumentFrame (SQLEditors)" exception could be thrown
- Fixed an issue when trying to set a custom time interval in the build-in Query Store reports the user was not able to select AM or PM on the start/end interval
- Fixed an issue that was causing the in High Contrast mode (selected line numbers not visible)
- Fixed an issue which resulted in an "Index out of range" exception when clicking on the grid
- Fixed an issue where the grid result background color was being ignored
- New mem grant operator properties display incorrectly when there is more than one thread
- Add the following 4 attributes in RunTimeCountersPerThread of actual execution xml plan: HpcRowCount (Number of rows processed by hpc device), HpcKernelElapsedUs (elapsed time wait for kernel execution in use), HpcHostToDeviceBytes (bytes transferred from host to device), and HpcDeviceToHostBytes (bytes transferred from device to host)
- Fixed an issue where the similar plan nodes are highlighted in the wrong position
- Fixed an issue where SMO/ServerConnection did not SqlCredential-based connections correctly
- Fixed an issue where an application written using SMO would encounter an error if it tried to enumerate databases from the same server on multiple threads even though it uses separate SqlConnection instances on each thread
- Fixed performance regression in Transfer from External Tables
- Fixed issue in ServerConnection thread-safety which was causing SMO to leak SqlConnection instances when targeting Azure
- Fixed an issue which was causing a StringBuilder.FormatError when trying to restore a database which had curly braces in its name
- Fixed an issue where Azure databases in SMO were defaulting to Case-Insensitive collation for all string comparisons instead of using the specified collation for the database
- Fixed an issue where "SQL System Table" where restoring the default colors was chancing the color to lime green, rather than the default green, making it very hard to read on a white background. For details, see Restoring wrong default color for SQL System Table. The issue still persists on non-English versions of SSMS
- Fixed issue where intellisense was not working when connected to Azure SQLDW using AAD authentication
- Fixed intellisense in Azure when user lacks access to master database
- Fixed code snippets to create "temporal tables" which were broken when the collation of the target database was case sensitive
- New TRANSLATE function now recognized by intellisense
- Improved intellisense on FORMAT built-in function
- LAG and LEAD are now recognized as built-in functions
- Fixed an issue where intellisense was giving a warning when using "ALTER TABLE...ADD CONSTRAINT...WITH(ONLINE=ON)"
- Fixed an issue where several system views and table values functions were not properly colorized
- Fixed an issue where clicking on editor tabs could cause the tab to be closed instead of getting the focus
- Fixed an issue where Tools > Options > SQL Server Object Explorer > Commands page was not resizing properly
- SSMS will now by default disable automatic download of DTD in XMLA editor -- XMLA script editor (which uses the xml language service) will by default now prevent automatically downloading the DTD for potentially malicious xmla files. This is controlled by turning off the “Automatically download DTDs and Schemas” setting in Tools > Options > Environment > Text Editor > XML > Miscellaneous
- Restored CTRL+D to be the shortcut as it used to be in older version of SSMS
- Fixed a crash in "Edit 200 rows"
- Fixed an issue where the designer was allowing to add a table when connected to an Azure SQL database
- Fixed an issue where the scan results are not being loaded properly
- Added two columns "action_name" and "class_type_desc" that show action id and class type fields as readable strings
- Removed the event XEvent Viewer cap of 1,000,000 events
- Fixed an issue where XEvent Profiler failed to launch when connected to a 96-core SQL Server
- Fixed an issue where XEvent Viewer was crashing when trying to group the events using the "Extended Event Toolbar Options"