Security has been one of the prime concerns of database developers since the inception of database management systems. Various data protection schemes have been introduced to provide secure access to sensitive data.
Read more »
Ben Richardson
Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog
View all posts by Ben Richardson
View all posts by Ben Richardson
Latest posts by Ben Richardson (see all)
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021
Working with XML Data in SQL Server
October 11, 2019XML (eXtensible Markup Language) is one of the most common formats used to share information between different platforms. Owing to its simplicity and readability, it has become the de-facto standard for data sharing. In addition, XML is easily extendable.
Read more »
Performance Monitoring via SQL Server Query Store
August 19, 2019SQL Server Query Store is a performance monitoring tool that helps us evaluate the performance of a SQL query in terms of several different performance metrics such as CPU and Memory Consumption, execution time and the I/O cycles consumed by the query. Query store is similar to the windows “Task Manager”. A task manager provides information about the CPU, Memory, Network and Disc consumption of a process. Similarly, the Query Store provides insight to similar information.
Read more »
Using Logistic Regression in Azure ML for Predicting Customer’s Loan Eligibility
August 19, 2019This article aims to provide the basics of creating Logistic Regression in Azure ML by designing a simple model step-by-step.
Read more »
Understanding Graph Databases in SQL Server
August 16, 2019A graph database is a type of NoSQL database that is based on graph theory. Graph databases are ideal for storing data that has complex many to many relationships. In this article, we will study the very basics of graph databases with the help of a simple example.
Read more »
Understanding the SQL Server CASE statement
June 28, 2019SQL Server CASE statement is equivalent to the IF-THEN statement in Excel.
Read more »
Nested Triggers in SQL Server
March 27, 2019Nested Triggers in SQL Server are actions that automatically execute when a certain database operation is performed, for example, INSERT, DROP, UPDATE etc.
Read more »
Python in SQL Server: The Basics
February 13, 2019With the introduction of SQL Machine Learning Services, it is now possible to run Python Scripts from any SQL Server client such as SQL Server Management Studio. In addition to directly running the Python Scripts on SQL Server Clients, you can write Python Code on native Python editors and run it remotely on SQL Server using Python clients for SQL Server.
In this article, we will see how to execute some of the basic Python functionalities within SQL Server Management Studio. The article provides an introduction to running basic Python scripts in SQL Server Management Studio.
Read more »
Importing and Working with CSV Files in SQL Server
January 28, 2019Introduction
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 »
Machine Learning Services – Configuring R Services in SQL Server
October 25, 2018The R language is one of the most popular languages for data science, machine learning services and computational statistics. There are several IDEs that allow seamless R development. Owing to the growing popularity of the R language, R services have been included by Microsoft in SQL Server 2016 onwards. In this article, we will briefly review how we can integrate R with SQL Server 2017. We will see the installation process and will also execute the basic R commands in SQL Server 2017.
Read more »
Difference between Identity & Sequence in SQL Server
August 15, 2018In SQL Server, both the SEQUENCE object and IDENTITY property are used to generate a sequence of numeric values in an ascending order. However, there are several differences between the IDENTITY property and SEQUENCE object. In this article, we will look at these differences.
Read more »
Identifying Object Dependencies in SQL Server Management Studio
July 16, 2018In relational database systems, objects have different types of relationships with each other. Apart from table relationships (such as one to one, one to many and many to many), objects such as stored procedures, views, custom functions also have dependencies on other objects. It is important to understand object dependencies, particularly if you want to update an object that depends upon other objects.
Read more »
The Difference between CROSS APPLY and OUTER APPLY in SQL Server
June 6, 2018SQL 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 »
Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions
May 16, 2018Data conversion is one of the most fundamental tasks of any programming language. Data received from different sources is often not in the right format. For example, if you receive an XML file where age is in the string format and you want to calculate an average age for the people in the file you will need to convert age into an integer.
To make the conversion process simple, the TRY_PARSE and TRY_CONVERT functions were introduced in SQL Server 2012. Before TRY_PARSE and TRY_CONVERT, SQL Server only had the PARSE and CONVERT functions.
Read more »
Understanding the GUID data type in SQL Server
May 3, 2018What is a GUID?
GUID is a 16 byte binary SQL Server data type that is globally unique across tables, databases, and servers. The term GUID stands for Globally Unique Identifier and it is used interchangeably with UNIQUEIDENTIFIER.
Read more »
Sequence Objects in SQL Server
March 22, 2018Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012.
Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE.
This article will take a detailed look at sequence objects.
Read more »
Debugging stored procedures in SQL Server Management Studio (SSMS)
February 20, 2018Debugging 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 »
Understanding SQL Server query plan cache
January 18, 2018Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance.
The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa.
Read more »
Understanding cursors and replacing them with JOINs in SQL Server
November 24, 2017Relational database management systems including SQL Server are very good at processing data in sets.
However, if you want to process data on row-by-row basis rather than in sets, cursors are your only choice. Unfortunately, cursors are extremely slow and so where possible should be replaced with JOINS.
Read more »
How SQL Server selects a deadlock victim
October 20, 2017In the article on Deadlock Analysis and Prevention, we saw how deadlock occurs. We saw that SQL Server selects one of the processes involved in the deadlock as deadlock victim. In this article, we will look at the criteria on which SQL server selects a deadlock victim. Why one process is selected as deadlock victim and not the other.
Read more »
What is the difference between Clustered and Non-Clustered Indexes in SQL Server?
August 28, 2017Indexes are used to speed-up query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow.
Read more »
How to use SQL Server built-in functions and create user-defined scalar functions
July 7, 2017A function is a set of SQL statements that perform a specific task. Functions foster code reusability. If you have to repeatedly write large SQL scripts to perform the same task, you can create a function that performs that task. Next time instead of rewriting the SQL, you can simply call that function. A function accepts inputs in the form of parameters and returns a value. SQL Server comes with a set of built-in functions that perform a variety of tasks.
Read more »
How to use Window functions in SQL Server
June 9, 2017All database users know about regular aggregate functions which operate on an entire table and are used with a GROUP BY clause. But very few people use Window functions in SQL. These operate on a set of rows and return a single aggregated value for each row.
Read more »