SQL Archives - Page 2 of 8 - Improve & Repeat

Little SQL Server Tricks: CREATE OR ALTER Views

2022-02-22 by Johnny Graber

With SQL Server 2016 SP1 came a small but helpful new statement to create views: OR ALTER. If you add this to the create script for a view, SQL Server will figure out if it should run the CREATE or the ALTER statement:

CREATE OR ALTER VIEW EmployeeReport
AS
SELECT 
	p.FirstName, 
	p.MiddleName, 
	p.LastName, 
	e.HireDate, 
	e.JobTitle
FROM Person.Person p
JOIN HumanResources.Employee e 
	ON p.BusinessEntityID = e.BusinessEntityID

CREATE OR ALTER VIEW EmployeeReport

SELECT

p.FirstName,

p.MiddleName,

p.LastName,

e.HireDate,

e.JobTitle

FROM Person.Person p

JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

This feature is especially helpful when you incrementally develop your views. …

Octopus Deploy & SSIS: Grant Permissions to the Correct User

2022-02-15 by Johnny Graber

I am a big fan of Octopus Deploy. This tool helps us to deploy our applications with ease and in a reliable way. In fact, it is so easy that I always forget the few important settings you need to have in place so that Octopus can do its magic. We use SSIS to synchronise …

SQL Server and Microsoft Defender: A Slow Combination

2022-02-08 by Johnny Graber

We are currently migrating our larger databases to SQL Server 2019. While the migration went smoothly for the small databases, there was an unexpected problem with the last server: the backup was incredibly slow. The first larger database we moved to the new server took 10 times longer to back up than on the old …

How to Create a Dev Container for SQL Server 2019

2023-11-192021-11-30 by Johnny Graber

Dev containers are not only for old software. They are also a great help when you plan a migration and need a new technology stack. Let’s look how SQL Server 2019 works nicely with our dev containers. SQL Server 2019 on Linux If you want to use SQL Server 2019 in a container you …

How to Create a Dev Container for PostgreSQL

2023-11-192021-11-16 by Johnny Graber

PostgreSQL is a free and open-source relational database that I use for most of my private database projects. Today we explore the steps to put PostgreSQL into a dev container. Get the PostgreSQL Docker image You find the official Docker images for PostgreSQL on hub.docker.com. If you need a specific version, you can get …

Python Friday #92: Where to Start With SQLAlchemy

2021-10-08 by Johnny Graber

SQLAlchemy is a massive database toolkit that you can use in various ways. Let us do a recap on the options you have and look for a good starting point for your situation. This post is part of my journey to learn Python. You can find the other parts of this series here. You find …

Little SQL Server Tricks: Disable All Triggers and Constrains of a Table

2021-10-05 by Johnny Graber

For our light-weight data synchronisation tool we need to disable all triggers and constrains on our tables until the data is consistent again. In SQL Server we can use these commands to disable everything, do our work and then reactivate the checks and triggers:

-- turn all checks off
DISABLE TRIGGER ALL ON your_table_name;
ALTER TABLE your_table_name NOCHECK CONSTRAINT ALL;

-- do the work

-- turn the checks back on
ALTER TABLE your_table_name CHECK CONSTRAINT ALL;
ENABLE TRIGGER ALL ON your_table_name;

-- turn all checks off

DISABLE TRIGGER ALL ON your_table_name;

ALTER TABLE your_table_name NOCHECK CONSTRAINT ALL;

-- do the work

-- turn the checks back on

ALTER TABLE your_table_name CHECK CONSTRAINT ALL;

ENABLE TRIGGER ALL ON your_table_name;

Be aware that you disable the data protection of your …

Python Friday #91: Automap for SQLAlchemy ORM

2024-04-072021-10-01 by Johnny Graber

The mapping for SQLAlchemy ORM takes a lot of initial work – often too much work for just a few queries against an existing database. However, there is an extension that offers a quicker way to work with a rudimentary object model and the nice parts of ORM. This post is part of my journey …

Python Friday #90: SQL Server Connection Strings for SQLAlchemy

2024-04-072021-09-24 by Johnny Graber

My first steps with SQLAlchemy and SQL Server ended in a lot of problems, mainly around the driver and the correct form of the connection string. Let us look what you need to successfully connect to SQL Server. This post is part of my journey to learn Python. You can find the other parts of …

Python Friday #89: Constraints, Indexes and Default Values in SQLAlchemy ORM

2024-04-072021-09-17 by Johnny Graber

The right index can boost the performance of your SQL query enormously while constrains help you to enforce the integrity of your data in the database. Let us look how SQLAlchemy can help us with both. I made my examples for SQLAlchemy ORM, but they work in Core as well. This post is part of …