Author: Eric Cobb

How to Encrypt Sensitive Text in SQL Server with ENCRYPTBYPASSPHRASE

How to Encrypt Sensitive Text in SQL Server with ENCRYPTBYPASSPHRASE

Storing sensitive information in a database, like passwords or social security numbers, is common practice. However, storing them securely is less common. Unfortunately, one of the most typical approaches is to store sensitive information in a table as clear text. That means that anyone with access to that table can see all of that sensitive…
Read more

Scripts SQL Server Logins

Useful Scripts For SQL Server Logins and Permissions

Since security and permissions are a big part of a DBA’s job, it’s important to be able to find out things like who has elevated login permissions or when a login was last used. Here are a few queries to help you check your server and database access. Most of these scripts are based off…
Read more

Deprecated Feature SQL

Tracking Deprecated Features in SQL Server

With each new release of SQL Server, we gain exciting new features—but also face the reality of deprecated features that should be phased out. Although these deprecated features aren’t immediately disabled, they won’t be supported in future SQL Server versions. Ideally, you should start removing them from your code now to avoid potential issues during…
Read more

Manually Adjusting Compatibility Level Settings in SQL Server

A Manual Adjustment: Compatibility Level Settings in SQL Server

A database on a specific SQL Server version doesn’t automatically have all features enabled. For example, a database on SQL Server 2019 can still use SQL Server 2014 compatibility, missing some 2019 features. Each database has a Compatibility Level setting that activates certain features of its version. When SQL Server 2014 introduced the Cardinality Estimator,…
Read more

When (Not) to Use NOLOCK

Quick Tips for Faster SQL Servers: When (Not) to Use NOLOCK

Many developers mistakenly use WITH (NOLOCK) in their SELECT statements without fully understanding its effects. While they might aim to speed up queries or prevent deadlocking, NOLOCK can actually lead to invalid results. What does NOLOCK do? A SELECT statement in SQL Server locks data to prevent modifications during querying. The lock is released after…
Read more

Image of a lost person reading a road map. This is your SQL Server when you use the "sp_" prefix to name a stored procedure and send it the long way around.

Quick Tips for Faster SQL Servers: Don’t Name Your Stored Procedures Using The “sp_” Prefix

A common mistake database developers make in SQL Server is naming their stored procedures with the “sp_” prefix. Organizations sometimes even adopt this as a standard convention (along with the “tbl_” prefix for tables!). So, why is this considered a bad practice? It’s inefficient. The “sp_” prefix is used by SQL Server to designate internal…
Read more

SQL Server memory configuration fine-tunes your servers' performance.

SQL Server Settings: Memory Configuration

SQL Server is designed to efficiently manage memory on its own. It requests memory when it’s needed and releases memory back to the system when it’s not. However, the default settings may not be ideal for your situation. An experienced database administrator will usually fine-tune these settings to optimize performance and server stability. Minimum server…
Read more

sql server optimize for ad hoc workloads

SQL Server Settings: Optimize For Ad-hoc Workloads

I’m my last post on SQL Server Setting, I discussed MAXDOP and how it can affect SQL Server performance. In this post, I’ll review another SQL Server configuration setting that can impact performance – Optimize for Ad-hoc Workloads. What is the plan cache? Every time you run a query in SQL Server, an execution plan…
Read more

sql maxdop

SQL Server Settings: MAXDOP

Did you know that not configuring the MAXDOP setting in SQL Server properly could cause performance problems for your queries?

sql server deadlocks

Deadlocks Could Mean Data Loss!

Deadlocks in SQL Server are more than just a nuisance, they can be problematic, and even lead to data loss and inconsistencies.