SQL Server

SQL BETWEEN

Today’s post is short, but it outlines the SQL BETWEEN Operator.  I’ve been using SQL for many years and I have not used this operator before, but came across it a couple weeks back.  It essentially selects values within a given range, and the values specified are included in the results (i.e. greater than or … Read more

0 comments

SQL MERGE

When working with tables in SQL, you will inevitably need to combine the contents of two or more tables at some point.  The SQL MERGE statement provides a quick and straight-forward way to accomplish this task.  The SQL MERGE statement performs insert, update, or delete operations on a target table based on the results of … Read more

0 comments

SQL NULL

When working with tables in SQL, you will sometimes encounter field values that are empty or missing, which is referred to as NULL.  This occurs during the creation of a record in a given table, when the field is not required to have a value.  This differs from zero values or spaces, and cannot be … Read more

0 comments

SQL Find Field in Database

The following post shows a simple query to find a specified field in a given database, using system views sys.all_columns and sys.all_objects in a specified database on your SQL Server.  This query provides a quick way to search your database for a field to see any objects that may be impacted if you intent to … Read more

0 comments

SQL CHOOSE Logical Function

The SQL CHOOSE Logical Function returns the item at the specified index from a list of values in SQL Server.  I’ve used it sporadically, but it equates to a work around for the CASE statement, making the query a bit more readable in some instances.  Let’s get to a couple examples. SQL CHOOSE Logical Function … Read more

0 comments

SQL List all Columns and Data Types in Database and Server

Last week, I had several questions regarding fields and how many tables and views a given field name appeared in our server.  I’ve typically used the INFORMATION_SCHEMA built-in views to query this type of information, but this was always at the database level, not the server level. After a bit of research, I came across … Read more

0 comments

SQL Query Database Mail History

Like most SQL groups, we use Database Mail to sent email notifications regarding job status and a host of other tasks. Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, … Read more

0 comments

SQL IN Logical Operator

The following post provides a few simple examples on how to utilize the SQL IN Logical Operator when executing T-SQL statements.  There are several techniques that make this a very useful statement in the WHERE clause. From the Microsoft Documentation site:  Logical operators test for the truth of some condition. Logical operators, like comparison operators, … Read more

0 comments

SQL Query Upcoming Agent Job Schedule

Last week, my team needed to install a couple patches to our server environment, which required a full reboot of the server.  This led to the obvious question of when to run the updates and reboot.  I wanted to make sure no SQL jobs were impacted, so I needed a quick listing of the SQL … Read more

0 comments

SQL Add Leading Zeros

The following post provides a few examples of how to add leading zeros to CHAR, VARCHAR and INT data type field values.  I have to use these techniques quite often in my data world, so I figured it was a good post to share with others struggling with this task.  Unfortunately, these techniques often result … Read more

0 comments

SQL Take Database Offline Stuck

Last week, I ran into two separate instances where I tried to take a SQL database offline, and the process got stuck.  Turns out the resolution is quite simple, so I thought I would share the steps in hopes it would help someone else stuck in a similar predicament.  It took me a quite a while … Read more

0 comments

SQL JOIN Multiple Tables with Concatenation

I often get questions about joining tables in SQL queries.  One particular question that comes up frequently is joining tables on multiple fields, when a user is trying to get unique matches based on the content of two fields.  This post, SQL JOIN Multiple Tables with Concatenation, covers a simple example to illustrate this concept. … Read more

0 comments

SQL CONCAT

Transact-SQL (TSQL) Quick Tips – SQL CONCAT In many cases I need to bring together two separate fields to create a new field.  Whether it be for display purposes, informational or joining to another table, the ability to combine two fields into one comes up frequently in my day-to-day work. Starting with SQL Server 2012, … Read more

0 comments

SQL Query Database Size

Transact-SQL (TSQL) Quick Tips – SQL Query Database Size I recently experienced a situation where disk space was close to capacity.  We manage a lot of databases and wanted to get a quick understanding of which particular databases were taking up the most space.  Using the following query, you can easily view the full list of databases on a specific server, … Read more

0 comments

SQL Display Duplicate Rows

Transact-SQL (TSQL) Quick Tips – SQL Display Duplicate Rows I was recently compiling a distribution list (email) into a flat table.  The structure was simple with name and email address.  In some cases, a user was included on multiple email distribution lists, so they would show up multiple times in the table.  In this case, I … Read more

0 comments

SQL UNION

The following post illustrates how to use the SQL UNION and SQL UNION ALL Operators. From time to time, I need to bring content from two separate tables together in a query.  Using the SQL UNION or SQL UNION ALL operators, I can combine this information using two SELECT statements, bringing the results together. SQL … Read more

0 comments

SQL Delete Duplicate Rows

Transact-SQL (TSQL) Quick Tips – SQL DELETE Duplicate Rows I was recently compiling a distribution list (email) into a flat table.  The structure was simple with name and email address.  In some cases, a user was included on multiple email distribution lists, so they would show up multiple times in the table.  I know it would … Read more

0 comments

SQL SELECT Nth Record

Transact-SQL (TSQL) Quick Tips – SQL SELECT Nth Record I recently needed to compare two month end data snapshots.  The data was organized in a historical table with monthly datasets, dating back several years.  The specific need was to query changes between the previous dataset and the most recent.  The task itself isn’t difficult.  What … Read more

0 comments

SQL SELECT INTO – Make copy of SQL Table

Transact-SQL (TSQL) Quick Tips – SQL SELECT INTO Statement The SQL SELECT INTO statement allows you to SELECT the data contents of one table and move it into a separate table.  Essentially, you are making a copy of the original table (either full copy or partial, depending on your SELECT statement).  SELECT INTO is based on a simple … Read more

0 comments

SQL HAVING

Transact-SQL (TSQL) Quick Tips – SQL HAVING clause Here is a simple example of how the SQL HAVING clause works.  You can copy this code into your Query Window and EXECUTE as-is.  The code includes a #temp table with data to illustrate the functionality.  You can modify to suit your needs. The SQL HAVING clause specifies … Read more

0 comments

SQL INFORMATION_SCHEMA – Table and View Metadata

There are many instances where I like to get details about tables, views and the fields contained within a given database.  A simple way to get at this information is querying a database’s INFORMATION_SCHEMA views.  The following post will give a couple examples of how to work with these valuable objects. From MSDN:  An information … Read more

0 comments

SQL PIVOT

Transact-SQL (TSQL) Quick Tips – SQL PIVOT Here is a simple example of how SQL PIVOT works in TSQL.  You can copy this code into your Query Window and EXECUTE as-is.  The code includes a #temp table with data to illustrate the functionality.  You can modify to suit your needs. The PIVOT functionality is used to … Read more

1 comment

SQL DATE Functions

Transact-SQL (TSQL) Quick Tips – SQL DATE Functions In the following post, I demonstrate a collection of common SQL DATE Functions I use on a regular basis.  I encourage you to copy this code into your Query Window, then EXECUTE as-is.  The code includes a #temp table with data to illustrate the functionality.  You can modify to suit your needs. … Read more

2 comments

SQL CHARINDEX

Transact-SQL (TSQL) Quick Tips – SQL CHARINDEX Function Here is a simple example of how the SQL CHARINDEX function works.  You can copy this code into your Query Window and EXECUTE as-is.  The code includes a #temp table with data to illustrate the functionality.  You can modify to suit your needs. The SQL CHARINDEX function searches … Read more

0 comments

SQL SUBSTRING

Transact-SQL (TSQL) Quick Tips – SQL SUBSTRING Function Here is a simple example of how the SQL SUBSTRING function works.  You can copy this code into your Query Window and EXECUTE as-is.  The code includes a #temp table with data to illustrate the functionality.  You can modify to suit your needs. The SQL SUBSTRING function returns … Read more

0 comments

SQL REPLACE

Transact-SQL (TSQL) Quick Tips – SQL REPLACE Function The following post contains a simple example of the SQL REPLACE function.  I encourage you to copy the code provided below, then paste the code into your Query Window and EXECUTE as-is.  If you execute the code yourself, you will better understand how the SQL REPLACE function works.  The code includes a temp table with data to … Read more

0 comments

SQL LIKE with Variable

The following code snippet will show you how to setup a LIKE search using a variable.  If you ever find the need to allow a user to perform a SQL LIKE with Variable search, this works well and can easily be adapted to fit your needs. By Definition:  LIKE Determines whether a specific character string … Read more

0 comments

SQL COALESCE – Comma Separated List

The following code snippet will create a comma separated list of values, based on selection parameters for a given field.  Using SQL COALESCE, you can easily adapt the following code to fit your needs. By Definition:  COALESCE Evaluates the arguments in order and returns the current value of the first expression that initially does not … Read more

0 comments

SQL ROW_NUMBER

The following code snippet will identify the MAX value on a given dimension(s), based on the value of a specified field.  This works well if you are trying to determine which value of a given dimension that you would like to display, if there are multiple options. By Definition:  Returns the sequential number of a … Read more

2 comments