how to

How to Replace INSTR() with CHARINDEX() and PATINDEX() in SQL Server

by

If you’ve worked with Oracle or MySQL before, you may have used the INSTR() function to find the position of a substring inside a string. But when you’re porting code to SQL Server, you’ll quickly notice that INSTR() isn’t available. Instead, you’ll need to use either CHARINDEX() or PATINDEX(), depending on what you’re trying to do.

The good news is that both functions are pretty straightforward once you know the difference. CHARINDEX() handles simple substring searches, while PATINDEX() adds the ability to use patterns. In this article, we’ll walk through how each one works and how you can swap them in when you’d otherwise use INSTR().

Read more

Connect to Azure SQL Database from Your Mac

by

If you’ve set up an Azure SQL Database, you’ll almost certainly want to connect to it at some point. You’ll probably want to connect to it, and then run a bunch of SQL queries, just like you would do if you had SQL Server installed locally or across the network.

Fortunately, connecting to Azure SQL Database is relatively straight forward. Let’s look at two options for connecting via a Mac: SQL command line tools and VS Code.

Read more

How to Drop and Recreate a Primary Key Constraint in SQL Server

by

In relational database management systems (RDBMSs) like SQL Server, the primary key constraint is a fundamental element that uniquely identifies each record in a table and enforces data integrity. However, there are situations where you might need to drop and then recreate this constraint. Such situations could include modifying the primary key columns, changing data types, resolving design issues, etc.

Understanding how to safely remove and reapply primary key constraints is extremely important for maintaining database consistency and minimizing downtime during schema changes.

In this article, we’ll walk through how to safely drop and then re-add a primary key constraint without breaking your database.

Read more

Using DROP TABLE IF EXISTS When Working with Temporary Tables in SQL Server

by

In SQL Server, when working with temporary tables, it’s common to ensure a temp table with the same name doesn’t already exist before creating it. This prevents any unwanted errors. SQL Server doesn’t support CREATE TABLE IF NOT EXISTS like some other DBMSs do, so you must explicitly check and drop the table beforehand.

Read more

Converting Between Time Zones in SQL Server with AT TIME ZONE

by

Converting between time zones in SQL Server has evolved throughout the years. Prior to SQL Server 2016, there was no simple, built-in function to handle this task. We had to use a complex, multi-step approach involving functions like SWITCHOFFSET() and TODATETIMEOFFSET(), and we had to manually account for Daylight Saving Time (DST) rules for each time zone. This method was often prone to error and required constant maintenance to keep up with changing time zone and DST regulations.

Read more

Convert DDMMYYYY to DATE in SQL Server

by

Sometimes you might get dates in a non-standard format, such as DDMMYYYY. By “non-standard” I mean a format that SQL Server doesn’t recognize as a date. Such a format is actually a little ambiguous. Some dates presented in this format could be mistaken for another date. For example, 02082026 could be August 2, 2026, or it could be February 8, 2026 depending on which locale you’re using.

Therefore, we need to do a bit of work in order to get SQL Server to recognize it as a date. Once that’s done, it’s a simple matter of converting it to an actual DATE type.

Below are a few options for converting a DDMMYYYY string to a DATE in SQL Server.

Read more

Create an Azure SQL Database for Free

by

Azure SQL Database is a fully managed cloud database service used by organizations of all sizes, from startups to large enterprises. It provides high availability, scalability, and security out of the box while supporting the familiar SQL Server engine. Because it runs entirely in the cloud, you can build data-driven applications without managing hardware or dealing with routine maintenance.

Even though it’s built mainly for enterprise-grade workloads, Azure SQL can also be a great choice if you’re a developer or student who wants to learn SQL, build prototypes, experiment with cloud-based apps, etc.

And Microsoft offers us a free option, so you can do it all at no cost.

Read more

Getting Started with Azure Sign Up

by

Azure is Microsoft’s cloud computing platform that offers a wide range of services, including databases, virtual machines, and analytics tools. For SQL developers, Azure provides tools like Azure SQL Database and Azure Data Studio, which make it easy to build and scale applications in the cloud.

Even though Azure is built with enterprise scenarios in mind, Azure SQL provides a flexible platform for SQL developers and students to learn, prototype, and experiment in the cloud.

Below are the steps I used to sign up with Azure. I did this in order to take advantage of the free SQL database offer (which provides free access to a SQL database for life). But for the purpose of this article, we’ll look solely at the sign up process, as you’ll need to do this regardless of whether you’re doing the free SQL database thing.

Read more