This article will provide a full overview, with examples of the SQL Outer join, including the full, right and left outer join as well as cover the union between SQL left and right outer joins.
Read more »
Database development
SQL Code Snippets in Azure Data Studio
April 16, 2019This article will fully cover the code snippet SQL developer productivity feature in Azure Data Studio including a list of available snippets and examples of how to create custom code snippets
Read more »
INSERT INTO SELECT statement overview and examples
April 12, 2019This article covers the SQL INSERT INTO SELECT statement along with its syntax, examples, and use cases.
Read more »
Methods to Insert Data into SQL Server
April 10, 2019Summary
There are a variety of ways of managing data to insert into SQL Server. How we generate and insert data into tables can have a profound impact on performance and maintainability! This topic is often overlooked as a beginner’s consideration, but mistakes in how we grow objects can create massive headaches for future developers and administrators.
Read more »
SQL PARTITION BY Clause overview
April 9, 2019This article will cover the SQL PARTITION BY clause and, in particular, the difference with GROUP BY in a select statement. We will also explore various use cases of SQL PARTITION BY.
Read more »
SQL Order by Clause overview and examples
April 9, 2019This article will cover the SQL ORDER BY clause including syntax, usage scenarios to sort out results in a Select statement.
Read more »
SQL unit testing best practices
April 8, 2019SQL unit testing is a testing method which allows us to test the smallest, atomic programmable part of a database object. SQL unit testing plays a key role in the modern database development cycle because it allows us to test individual parts of the database objects work as expected. SQL unit testing adds a great worth to the database project because unit tests are more reliable then manual test methods.
Read more »
SQL SELECT INTO statement
April 5, 2019This article will cover the SQL SELECT INTO statement including syntax, parameters and use with multiple tables, filegroups and a WHERE condition
Read more »
SQL Foreign key
April 5, 2019In this article let us review different ways to create a SQL foreign key, rules on updates and deletes, enabling foreign key constraints, disabling foreign key constraints and not for replication in foreign keys.
Read more »
SQL Convert Date functions and formats
April 3, 2019In this article, we will explore various SQL Convert Date formats to use in writing SQL queries.
Read more »
CASE statement in SQL
April 1, 2019The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well. In this article, we would explore the CASE statement and its various use cases.
Read more »
Conventional SQL Unit Testing with tSQLt in Simple Words
March 28, 2019This article is about basics of conventional SQL unit testing concepts and its implementation through tSQLt, a highly acclaimed SQL unit testing framework because of being written in T-SQL and its built-in design support for SQL SQL unit testing needs ranging from simple to complex scenarios.
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 »
Securely Working with Invoke-SqlCmd
March 26, 2019We have a convenient tool for working with PowerShell and SQL Server when using Invoke-SqlCmd. As we saw when running statements, we can run DDL and DML changes with the command without writing our own custom scripts. This carries advantages when we need to quickly develop with PowerShell, but it can come with drawbacks on security if we’re not careful how we use this function. We’ll look at security when using this function by starting with a few examples of what we can do when we have unlimited access along with how we can design to limit our environment to be strict with our use of this tool.
Read more »
SQL Unit testing with the tSQLt framework and SQL Server Database Project integration
March 25, 2019In the previous article SQL unit testing with the tSQLt framework for beginners in this series, we covered tSQLt framework essentials and installation. we also demonstrated basic SQL unit testing through the tSQLt framework. In this article, we will continue to explore SQL unit testing and we will also discuss the benefits of SQL unit testing. Additionally, we will learn how we can use the tSQLt framework in a SQL Server Database Project.
Read more »
Windocks; Database cloning for SQL Server dev/test on “live” production data
March 19, 2019Introduction
Today, with DevOps, the need for database cloning is urgent. The development and testing process is shorter and it is required to work and test the data faster.
Working with PowerShell’s Invoke-SqlCmd
March 18, 2019PowerShell features many one-line commands for working with SQL Server, one of which is Invoke-SqlCmd. This tool can be useful in many development contexts where we need to quickly execute scripts or test code and it helps to know some of the parameters we’ll often use. In addition, because we may want a custom script using some of the underlying .NET objects, we’ll look at an alternative where we will be able to create a custom PowerShell script that connects to SQL Server in order to run commands. The latter can be useful because one-line scripts have a tendency to change in future versions of PowerShell and working with the library directly can sometimes avoid this challenge.
Read more »
SQL unit testing with the tSQLt framework for beginners
March 18, 2019tSQLt is a powerful, open source framework for SQL Server unit testing. In this article, we will mainly focus on how to create and run SQL unit testing with help of the tSQLt framework. Before we begin to learn tSQLt framework details, let’s discuss essentials and importance of the database unit testing approach, in general. SQL unit testing is a vital and inseparable part of the modern database development approach and it makes possible to prevent errors before producing the release deployment process. Some database developers are still discussing the needs of the SQL unit testing concept; however, database unit testing is very significant to control and check the behavior of the individual parts of the database. For this reason, we cannot ignore the need to write unit test cases.
Read more »
tSQLt – A Forgotten Treasure in Database Unit Testing
March 12, 2019This article is an overview of tSQLt, which is one of the best SQL unit testing frameworks, due to number of reasons, including the ease of writing unit tests in the same language (T-SQL) in which the database objects themselves are written.
Read more »
DevSecOps: Developing with Automated Security Testing
March 6, 2019A key component of DevSecOps and identical to running unit tests to validate code after a build, running automated security testing after an application has been deployed (such as automated penetration tests) can provide us with a tool that identifies security risks. As we’ve seen recently, there’s been a growth of many companies experiencing information being compromised and with the development culture of “move fast and break things”, I expect this trend of successful attacks will continue. Before we look at our options for automating this testing, we want to be aware of its limits, evaluate the requirements, and consider common designs that are useful.
Read more »
SQL FILESTREAM and SQL Server Full Text search
February 19, 2019In this article, the latest in our series on the SQL FILESTREAM feature, we are going to look at the synergy and interoperability with SQL Server Full Text search, another powerful SQL Server feature
Read more »
SQL Unit Testing with SSDT (SQL Server Data Tools)
February 12, 2019In this article, we will discuss the essentials of the SQL unit testing and then we will explore how to apply SQL unit testing methodology in Azure SQL Database with help of SQL Server Data Tools (SSDT). In particular, this article will focus about unit test theory in terms of SQL Server database development aspect and illustrate SQL unit testing with a detailed example.
Read more »
SQL Convert Function
January 29, 2019In this article, we will discuss and learn basics and all details about SQL Server data type converting operations and also we will review the SQL CONVERT and TRY_CONVERT built-in functions with various samples. At first, we will explain and clarify syntax of the SQL CONVERT function and then we will learn how can we make data converting process numerical and date/time data types to character data.
Read more »
FILESTREAM in SQL Server
January 11, 2019This article will discuss SQL Server FILESTREAM including installation, configuration, enabling and general considerations.
Working With Line Numbers and Errors Using Bulk Insert
January 10, 2019In the first part of reviewing the basics of bulk insert, we looked at importing entire files, specifying delimiters for rows and columns, and bypassing error messages. Sometimes we’ll want to skip first and ending lines, log errors and bad records for review after inserting data, and work with data types directly without first importing using a varchar and converting to the data type later. In this part, we look at these techniques using T-SQL’s native bulk insert.
