Development

Dinesh Asanka
Transaction log usage after TRUNCATE TABLE statment is executed.

Truncate Table Operations in SQL Server

September 25, 2019 by

Truncating a table is removing all the records in an entire table or a table partition. TRUNCATE table is functionally similar to DELETE table with no WHERE clause. However, TRUNCATE table is much faster than DELETE with respect to the time and the resource consumptions which we will look at in this article. TRUNCATE statement removes the data by de-allocating the data pages in the table data. This means that TRUNCATE is similar to drop and re-create the table. Also, it records only the page de-allocations in the transaction log, not the row-wise as in DELETE statement.

Read more »
Ed Pollack
Sample form with validation errors that protect the underlying code from SQL injection attacks.

SQL Injection: Detection and prevention

August 30, 2019 by

Summary

With an understanding of what SQL injection is and why it is important to an organization, we can shift into a discussion of how to prevent it. We ultimately want systems where SQL injection is impossible or very difficult to pull off. We then want systems where exploiting bugs is slow, laborious, and likely to raise monitoring alarms within an organization when attempted. The trio of layered security, prevention, and alerting can provide an immense advantage against not only SQL injection, but other data security threats.

Read more »
Ed Pollack
Attempting to locate a SQL injection vulnerability by entering special characters into form fields.

SQL Injection: What is it? Causes and exploits

August 30, 2019 by

Summary

Many security vulnerabilities are discovered, patched, and go away forever. Some linger and continue to plague software development and will continue to do so for years to come. Setting aside social engineering and non-technical attacks, SQL injection remains one of the top security threats to our data, as well as one of the most misunderstood.

Read more »
Ed Pollack

Methods to Insert Data into SQL Server

April 10, 2019 by

Summary

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 »
Timothy Smith

Securely Working with Invoke-SqlCmd

March 26, 2019 by

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 »
Timothy Smith
Our CSV file returns with the Export-CSV function that catches the output from the select

Working with PowerShell’s Invoke-SqlCmd

March 18, 2019 by

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 »
Esat Erkec

SQL Unit Testing with SSDT (SQL Server Data Tools)

February 12, 2019 by

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 »
Timothy Smith

Working With Line Numbers and Errors Using Bulk Insert

January 10, 2019 by

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.

Ranga Babu

SQL Server Code simplification using synonyms

January 4, 2019 by

Problem

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 »