Nested Triggers in SQL Server March 27, 2019 by Ben Richardson Triggers in SQL Server are actions that automatically execute when a certain database operation is performed, for example, INSERT, DROP, UPDATE etc. Read more »
Importing and Working with CSV Files in SQL Server January 28, 2019 by Ben Richardson Introduction CSV (comma separated values) is one of the most popular formats for datasets used in machine learning and data science. MS Excel can be used for basic manipulation of data in CSV format. We often need to execute complex SQL queries on CSV files, which is not possible with MS Excel. See this article for what is possible with Power BI. However, before we can execute complex SQL queries on CSV files, we need to convert CSV files to data tables. Read more »
Database Upgrade using the Query Tuning Assistant wizard in SSMS 18 December 6, 2018 by Rajendra Gupta This article will cover managing a SQL Server database upgrade using new features in SQL Server Management Studio 18 including the query tuning assistant wizard, database upgrade feature, query store and more
Static Data Masking in SSMS 18 December 6, 2018 by Rajendra Gupta In this article we are going to talk about Static Data Masking, a new feature for the SQL Databases in SSMS. Static Data Masking feature previously was available only for the Azure SQL DB.
SQL Execution Plan enhancements in SSMS 18.0 November 13, 2018 by Ahmad Yaseen This article will provide an overview of the SSMS 18.0 with particular focus on improvements to the SQL execution plan feature. To work with Microsoft SQL Server Database Engine, you need to have an environment to edit, debug and deploy scripts written in different languages such as T-SQL, DAX, MDX, XML and JSON. In addition, you need a GUI tool that helps you to configure, query, monitor and administrate your SQL Server instances wherever they are hosted; locally at your machine, on a remote Windows or Linux server or in the cloud. All this can be achieved using SQL Server Management Studio aka SSMS. Read more »
SQL Server Execution Plan Operators – Part 4 October 29, 2018 by Ahmad Yaseen In the previous articles of this series, we went through three sets of SQL Server Execution Plan operators that you will meet with while working with the different Execution Plan queries. We described the Table Scan, Clustered Index Scan, Clustered Index Seek, the Non-Clustered Index Seek, RID Lookup, Key Lookup, Sort, Aggregate – Stream Aggregate, Compute Scalar, Concatenation, Assert, Hash Match Join, Hash Match Aggregate , Merge Join and Nested Loops Join Execution Plan operators. In this article, we will dive in the fourth set of these SQL Server Execution Plan operators. Read more »
SQL Server Execution Plan Operators – Part 3 October 29, 2018 by Ahmad Yaseen In the previous articles of this series, we discussed a group of SQL Server Execution Plan operators that you will face when studying the SQL Execution Plan of different queries. We showed the Table Scan, Clustered Index Scan, Clustered Index Seek, the Non-Clustered Index Seek, RID Lookup, Key Lookup and Sort Execution Plan operators. In this article, we will discuss the third set of these SQL Execution Plan operators. Read more »
SQL Server Execution Plan Operators – Part 2 October 26, 2018 by Ahmad Yaseen In the previous article, we talked about the first set of operators you may encounter when working with SQL Server Execution Plans. We described the Non Clustered Index, Seek Execution Plan operators, Table Scan, Clustered Index Scan, and the Clustered Index Seek. In this article, we will discuss the second set of these SQL Server execution plan operators. Read more »
Manage SQL code formatting using SQL formatter options October 24, 2018 by Marko Zivkovic This article will provide a review of SQL code formatting using the SQL formatter options in SSMS Nobody likes to read a large amount of text, even when it’s just a plain one. When reading SQL script where there is a large amount of non-formatted SQL code, the problem becomes even bigger. Read more »
Top free add-ins for SQL Server Management Studio (SSMS) September 13, 2018 by SQLShack This collection of content presents an overview of free SQL Server Management Studio (SSMS) add-ins, with product page links for each of the tools and screenshots if available. SSMS add-ins are presented in alphabetical order. If you noticed a free tool we missed, please let us know in the comments below. Read more »
SQL Server Execution Plan Operators – Part 1 September 12, 2018 by Ahmad Yaseen In the previous articles of this series, SQL Server Execution Plans overview , SQL Server Execution Plans types and How to Analyze SQL Execution Plan Graphical Components, we discussed the steps that are performed by the SQL Server Relational Engine to generate the Execution Plan of a submitted query and the steps performed by the SQL Server Storage Engine to retrieve the requested data or perform the requested modification operation. Read more »
How to Analyze SQL Execution Plan Graphical Components September 7, 2018 by Ahmad Yaseen In the previous articles of this series, SQL Server Execution Plans overview and SQL Server Execution Plans types we went through the different stages that the submitted SQL Server query followed and how it processed by the SQL Server Relational Engine that generates the Execution Plan and the SQL Server Storage Engine that performs the requested data retrieval or modification operation. In addition, we described deeply the different types and formats of the SQL Server Execution Plans that can be used for queries performance troubleshooting purposes. In this article, we will discuss the graphical query plan components and how to analyze it. Read more »
Performance troubleshooting when the query plan from the application is different than SSMS August 24, 2018 by James Rhoat Troubleshooting performance issues in a database is one of the main jobs of DBAs and by now most can trace the problem back to a query which is either running to slow or is causing a blocking issue on a key table. However, what is often not known is why this doesn’t cause problems in SSMS or why you don’t get the same query plan as what is inside the app. For example, in your extended event trace you see the query running longer from the application when compared to SSMS. Read more »
4K resolution and high DPI: What you need to know about it to have great visuals April 26, 2018 by Bojan Petrovic 4K resolution, AKA Ultra HD, refers to a horizontal screen display resolution in the order of approximately 3,840 pixels. On two-dimensional display devices such as computer monitors, it represents display resolution of 3840×2160 pixels which is four times as many pixels as regular Full High Definition (1920×1080). What this means for users is the more pixels per inch (PPI) the higher resolution on the monitor. In other words, you can be closer to the monitor and still enjoy a crisp, clear picture. Read more »
What’s new in SSMS 17.5; Data Discovery and Classification and more April 16, 2018 by Ahmad Yaseen SQL Server Management Studio aka SSMS is the default integrated graphical user interface tool that is used to access, administrate, configure, manage, query and monitor the different SQL Server instances that are hosted locally at the user machine, remotely on a server or anywhere in the cloud. It is a comprehensive environment that allows us to edit, debug and deploy scripts written in different languages such as T-SQL, DAX, MDX, XML and more. In my previous articles, we followed the enhancements to the existing SSMS features, the fixes to the bugs found and reported in the previous SSMS versions and the new features added to each new release. These new features that are introduced in the previous versions include the Connections dialog box new options, the embedded Performance Dashboard, Showplan node search, Import Flat File Wizard, the XEvent Profiler and Always On Availability Group dashboard new additions, which are described deeply in these articles. Read more »
How to enable High-DPI in SSMS (SQL Server Management Studio) – Tips, tricks and issues April 11, 2018 by Bojan Petrovic SQL Server Management Studio (SSMS) is getting better and better with each new release but Microsoft seems to ignore one big issue affecting a group of users that grows rapidly as the technology moves forward and we move on to modern workstations and laptops. Read more »
SQL Server data compression using the SSMS Data Compression Wizard April 3, 2018 by Marko Zivkovic Every DBA knows that database grows over time and that the growth never stops. The more data is in the database, the more time (work) SQL Server is needed in order to deal with it. From SQL Server 2008 on, new tools are available in order to help DBAs to reduce the size of database. This article will describe SQL Server data compression and the SSMS Data Compression Wizard will be explained. Read more »
Debugging stored procedures in SQL Server Management Studio (SSMS) February 20, 2018 by Ben Richardson Debugging is one of the most important but painful parts of any software process. To find some errors you have to run the code step by step to see which section of the code is responsible for the error. This is called runtime debugging. Read more »
What’s new in SQL Server Management Studio 17.4; Always On AG dashboard enhancements and more December 27, 2017 by Ahmad Yaseen As all other database engines that require a GUI too to interact with, Microsoft built SQL Server Management Studio as a tool that provides an environment to manage, configure, monitor and administrate the SQL Server infrastructure for different kind of instances, that are installed locally at your machine, on a remote server with Windows or Linux operating systems or hosted in the cloud such as SQL Azure or Azure Data warehouse. Recall that the SQL Server Management Studio consists of the Object Explorer, which is used to view and manage all objects within the SQL Server instance, the Template Explorer, that is used to deal with the scripting text files and the Solution Explorer, that is responsible for building the projects that contain list of T-SQL scripts in SQL Server. Read more »
What’s new in SQL Server Management Studio 17.4; SQL Vulnerability assessment and more December 26, 2017 by Samir Behara Microsoft released the latest version of the SQL Server Management Studio 17.4 a few weeks back. This release brings in exciting new features and bug fixes to the SSMS IDE for SQL Developers and DBAs. In this article, we will look at a new inbuilt tool for identifying security vulnerabilities in your database. We will also understand the improvements made to the XEvent Profiler and ShowPlan features. Read more »
What’s new in SQL Server Management Studio 17.3; Import Flat File wizard and XEvent Profiler November 1, 2017 by Ahmad Yaseen SQL Server Management Studio is an integrated graphical user interface tool that is used to perform wide range of administration and development tasks on the SQL Server instances, hosted locally on the user machine, remotely on a Windows or Linux server or on SQL Azure or Azure Data warehouse instances hosted in the cloud. You can use SQL Server Management Studio to easily administrate, develop, deploy, configure and monitor the SQL Server instances. Read more »
How to import a flat file into a SQL Server database using the Import Flat File wizard October 19, 2017 by Marko Zivkovic Import Flat File wizard is a new feature implemented in SQL Server Management Studio (SSMS) v17.3 for importing a .csv or a .txt file into a SQL Server database. The latest SSMS version can be found on this link Read more »
How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL October 2, 2017 by Prashanth Jayaram In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more. Read more »
Setting up the dark theme in SQL Server management studio September 28, 2017 by Bojan Petrovic In SSMS 2016 Microsoft introduced visual setting option to choose between Blue and Light color theme. In both SSMS 2016 and the latest SSMS 17, the user can switch between the Blue or Light theme by going to Tools > Options > Environment > General > Color theme: Read more »
How to create a linked server to an Azure SQL database September 27, 2017 by Marko Zivkovic Linked servers allow to access data from another SQL Server or another data source (e.g. Excel) by using SQL Server Management Studio (SSMS) or Transact-SQL. This article will explain how to create and configure a linked server to retrieve data from an Azure SQL database. Also, we will explain how to solve some common problems/issues during the process of creating a linked server to an Azure SQL database. Read more »
