Partial stored procedures in SQL Server June 8, 2018 by Gerald Britton Introduction SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility. Read more »
The Difference between CROSS APPLY and OUTER APPLY in SQL Server June 6, 2018 by Ben Richardson SQL Server supports table valued functions, what are functions that return data in the form of tables. JOIN operations in SQL Server are used to join two or more tables. However, JOIN operations cannot be used to join a table with the output of a table valued function. APPLY operators are used for this purpose. Read more »
SQL Server performance counters (Batch Requests/sec or Transactions/sec): what to monitor and why June 5, 2018 by Nikola Dimitrijevic When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While they could look similar, they are using a different type of starting point for measurement; the batches and transactions. So, to correctly understand those important metrics, lets first try to understand what the batches and transactions in SQL Server are and what are the differences between the two Read more »
How to Sync Azure SQL databases and on-premises databases with SQL Data Sync June 4, 2018 by Marko Zivkovic SQL Data Sync allows you to synchronize data across multiple Azure SQL databases and on-premises SQL Server databases. In this article, how to synchronize Azure SQL database with on-premises SQL Server database will be shown. Read more »
Data science, data understanding and preparation – basic work with datasets June 4, 2018 by Dejan Sarka In my previous four articles, I worked on a single variable of a dataset. I have shown example code in T-SQL, R, and Python languages. I always used the same dataset. Therefore, you might have gotten the impression that in R and in Python, you can operate on a dataset the same way like you operate on an SQL Server table. However, there is a big difference between an SQL Server table and Python or R data frame. Read more »
What is SQL Data Sync June 1, 2018 by Marko Zivkovic SQL Data Sync is a service that allows synchronizing data across multiple Azure SQL databases and on-premises SQL Server databases. In this article, a base concept of how the SQL Data Sync service works will be explained as well as what the requirements and limitations are when want to create data synchronization by using SQL Data Sync Read more »
Tail-Log Backup and Restore in SQL Server May 31, 2018 by Prashanth Jayaram A tail-log backup is a special type of transaction log backup. In this type of backup, the log records that have not been backed up are captured. This prevents data loss and maintains the log chain sequence. Read more »
Maintaining SQL Server indexes May 31, 2018 by Ahmad Yaseen In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of SQL Server tables and indexes, the guidelines that you can follow in order to design a proper index, the list of operations that can be performed on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes, the different types of SQL Server indexes (above and beyond Clustered and Non-clustered indexes classification), how to tune the performance of the inefficient queries using different types of SQL Server Indexes and finally, how to gather statistical information about index structure and the index usage. In this article, the last article in this series, we will discuss how to benefit from the previously gathered index information in maintaining SQL Server indexes. Read more »
Query optimization techniques in SQL Server: the basics May 30, 2018 by Ed Pollack Description Fixing and preventing performance problems is critical to the success of any application. We will use a variety of tools and best practices to provide a set of techniques that can be used to analyze and speed up any performance problem! Read more »
Real-time streaming in Power BI May 29, 2018 by Esat Erkec The concept of the IOT (Internet of Things) is that every object that you might think of is somehow accessed and connected to other devices on the internet. Nowadays every electronic device is now becoming smart and these devices are now able to communicate with each other. Many ideas and technology on IOT are being developed and are even being used today. Read more »
SQL Server Database Backup and Restore operations using the Cloud May 29, 2018 by Prashanth Jayaram This article discusses the concept of SQL Server backup and the various components required to use the Microsoft Azure Blob storage service as a target for the backup. As we all know, disk and tapes were the default destinations of choice until the evolution of the cloud platform. In addition to this, now we can expand the native functionality of SQL Server backup to cloud storage; the Windows Azure Blob storage. This functionality was added first in SQL Server 2012. In general, backup and restore functionality to and from the cloud are similar to using disk or tape, with very few differences. Read more »
Gathering SQL Server indexes statistics and usage information May 24, 2018 by Ahmad Yaseen In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of both the SQL Server tables and indexes, the best practices that you can follow in order to design a proper index, list of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes, the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification and finally how to tune the performance of the bad queries using the different types of SQL Server Indexes. In this article, we will discuss how to gather statistical information about the index structure and the index usage information. Read more »
Connecting to Azure SQL Database May 23, 2018 by Istvan Martinka The below screenshots and code samples are valid at the time of writing (May 2018) but as things in Azure can change so quickly, please check the latest documentation if a code sample doesn’t work as expected! Read more »
Step by step installation of SQL Server 2017 May 23, 2018 by Daniel Calbimonte Introduction The new SQL Server 2017 comes with new features in the installation. It now supports Machine Learning Services that support R and Python. It also includes SSIS Scale Out Master and Scale Out Worker. It also includes scale out options in PolyBase. Read more »
Backup Linux SQL Server databases using PowerShell and Windows task scheduler May 22, 2018 by Prashanth Jayaram This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines. Here’s an outline of what this article is all about: Introduction Technical specifications How to load SQL Server modules on Windows machine Security – Credential Management The objectives of Backup and Restore Library Linkage How SQL Server 2017 backup feature is used on Linux And more … Read more »
Understanding benefits of Graph Databases over Relational Databases through Self-Joins in SQL Server May 21, 2018 by Sifiso W. Ndlovu Earlier this year, I published several articles on SQLShack with an aim of demonstrating tools available for visualising SQL Server 2017 graph databases. I was so caught up in the excitement of having SQL Server finally support graph databases that I forgot that some people still do not have a good grasp of how graph databases work let alone consider replacing their relational databases models in favour of graph. Although there are several ways that one can go about explaining the usefulness of graph databases over its relational counterpart, I have opted to focus on the benefits and strengths of graph databases by demonstrating the differences in which graph and relational databases deal with hierarchical datasets. Read more »
Securing access for SQL Server auditing May 21, 2018 by Timothy Smith Our organization must restrict permissions and prove to an independent party that we investigate access and restrict permissions. We restrict permissions to objects using least permissions and give full access to an exceptional few based on an organizational design that follows best practices. What can we do additionally that will help us prevent unauthorized access or catch when someone who shouldn’t have access is able to infiltrate our systems, especially in the context of showing a third party that we track this. Read more »
Tracing and tuning queries using SQL Server indexes May 18, 2018 by Ahmad Yaseen In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of the SQL Server tables and indexes, the best practices to follow when designing a proper index, the group of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes and finally the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification. In this article, we will discuss how to tune the performance of the bad queries using SQL Server Indexes. Read more »
How to perform a page level restore in SQL Server May 18, 2018 by Prashanth Jayaram In this article in our stairway series on SQL Server backup and restore, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key to recovery or restoration, and SQL Server provides us with an option to fix database pages at a granular level. Performing a page level restore in SQL Server is a clever option wherein the restoration of the entire VLDB database backup is not required. Read more »
Restricting and monitoring SQL Server data access with views and procedures May 17, 2018 by Timothy Smith We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users? Read more »
