This 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 case of SQL PARTITION BY.Read more »
This 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 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 »
This article will cover the SQL SELECT INTO statement including syntax, parameters and use with multiple tables, filegroups and a WHERE conditionRead more »
In 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 »
In this article, we will explore various SQL Convert Date formats to use in writing SQL queries.Read more »
The 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 Insert statement as well. In this article, we would explore CASE statement and its various use cases.Read more »
This 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 are actions that automatically execute when a certain database operation is performed, for example, INSERT, DROP, UPDATE etc.Read more »
We 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 »
In 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 »
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.
PowerShell 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 »
tSQLt 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 »
This 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 »
A 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 »
In 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 featureRead more »
In 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 »
In 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 »
This article will discuss SQL Server FILESTREAM including installation, configuration, enabling and general considerations.
In 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.
In our environment, we use different DB servers and these servers are linked via Linked servers for cross server transactions. We use these linked servers in our stored procedures. The server names are different in different environments and we had to change the server names manually when we promote the stored procedures to other environments. There is a chance of errors while changing the server names manually. In this article let us see how can we eliminate changing the code manually.Read more »
In this article, we are going to learn how to use Power BI Desktop Q&A functionality to enable users to ask the question from the data set and easily get the results without any configuration or write complex codes.
In this article, we will explore ‘ Clustered columnstore online index build and rebuild’ feature of SQL Server 2019 including comparing execution plans, offline builds and moreRead more »
This article will cover the STRING_SPLIT function in SQL Server including an overview and detailed usage examples.