Navicat Blog

Important SQL Server Functions - String Utilities May 7, 2021 by Robert Gravelle

Part 1: String Utilities

There are certain functions that seem to come up in every programming language. Although SQL differs from your typical procedural programming language like C# or Java in many ways, it too comes equipped with an impressive assortment of built-in functions. These may be applied to Char, Varchar, and Text data types. Each database vendor does not implement functions in exactly the same way, so it pays to familiarize yourself with functions that are specific to the database you work with. In this series, we'll be taking a look at a few important SQL functions, as implemented by SQL Server. Today's blog will tackle string functions, while subsequent installments will explore numerical, date functions, and more!

Iterate over Query Result Sets Using a Cursor May 4, 2021 by Robert Gravelle

Being a transactional programming language, SQL is designed to execute its work in an all-or-nothing capacity. Meanwhile, procedural programming languages such as C# and Java are often iterative in nature. As such, they tend to loop over the same code until the stack is diminished and fully processed. Cursors are a notable exception to SQL's transactional approach. Like WHILE loops, cursors allow programmers to process each row of a SELECT result set individually by iterating over them. While many SQL purists shun cursors out of disdain or fear, they have their place in database development and are well worth learning. To that end, today's blog will describe when and how to use cursors within your stored procedures.

Copying a Table to a New Table using Pure SQL Apr 28, 2021 by Robert Gravelle

There are many times where one needs to copy data from an existing table to a new one, for example, to back up data or to replicate data in one environment in another, as one might do for testing purposes. In SQL, one would typically use CREATE TABLE and SELECT statements as follows:

CREATE TABLE new_table; 
SELECT SELECT col, col2, col3 
INTO new_table 
FROM
    existing_table;

In the first statement, the database creates a new table with the name indicated in the CREATE TABLE statement. The structure of the new table is defined by the result set of the SELECT statement. Then, the database populates data with the results of the SELECT statement to the new table.

While the above procedure works perfectly well, there's an easier way to copy a table into a new one using a variation of the CREATE TABLE statement! We'll learn how to use it here today.

Using Transactions in Stored Procedures to Guard against Data Inconsistencies Apr 20, 2021 by Robert Gravelle

In the Understanding Database Transactions blog, we leaned how transactions are a fantastic way to guard against data loss and inconsistencies by guaranteeing that all operations performed with a transaction succeed or fail together. In today's follow-up, we'll learn how to employ a transaction within a stored procedure in order to ensure that all tables involved remain in a consistent state.

Understanding Database Transactions Apr 16, 2021 by Robert Gravelle

Atomicity Consistency Isolation Durability, or "ACID", was coined by Andreas Reuter in 1983. It's a concept in database management systems (DBMS) that identifies a set of standard properties used to guarantee the reliability of a database. ACID properties ensure that all database transactions remain accurate and consistent, and support the recovery from failures that might occur during processing operations. As such, it is implemented by nearly all Relational Databases.

Navicat Blogs
Feed Entries
Blog Archives
Share