SQL Server, SQL Simantics

SQL Server: TRY-CATCHes, RAISERRORs, and THROWs

Randy Sims SQL Server

In the SQL Server criminal justice system, uncaught errors are considered to be especially heinous. In your code, the TRY-CATCH along with RAISERROR or THROW are members of an elite squad that can be used to trap errors, trap invalid values, or trap user mistakes before it even gets to the database. This is their […]

SQL Server Set Operators: UNION, INTERSECT, and EXCEPT

Randy Sims SQL Server

Did you know you can do set-based math with SQL Server? With the UNION, INTERSECT, and EXCEPT set operators, you can compare one result set to another and add or subtract results, called a compound query. This can be a handy tool for your toolbox. This is very similar to Oracle Set Operators: UNION, INTERSECT and MINUS

SQL Server: Fake a Dynamic IN with STRING_SPLIT

Randy Sims SQL Server

use string_split to actually join your multiple choices to a table! #SQLServer2016 #SQL #TSQL

SQL Server: Duplicate Rows with Your Own Values Using STRING_SPLIT and CROSS JOIN

Randy Sims SQL Server

SQL Server 2016 came out with the STRING_SPLIT function that will return what amounts to a table from character-separated string. Using a CROSS JOIN with this function, you can return one row multiple times with whatever values you want!

Review: SQL Operations Studio (Preview) for SQL Server

Randy Sims SQL Server

As someone who develops on SQL Server every day, I wanted to try it out the latest gizmo, SQL Operations Studio, or SQLOps. I hoped it was going to be my new favorite toy with all those extensions. In the end, I’m fine with it. There is one problem and a couple of missing features, […]

SQL Server 2016’s STRING_SPLIT

Randy Sims SQL Server

I found a pretty cool little function as I was perusing the SQL Server functions documentation the other day. (What, doesn’t everyone do that?) I just read the name of this function and thought, well, no need to create that function with a loop any longer! Finally, SPLIT has come to SQL Server! What is […]

SQL Server’s [N]VARCHAR(MAX) and How to Wield It

Randy Sims SQL Server

[N]VARCHAR(MAX) is a data type that was created to replace [N]TEXT. Depending on how many characters you store in this data type, it will react differently to its surroundings. It might be just what you needed or it might be your worst nightmare, so choose wisely. Here’s a run down on this data type.

SQL Server’s CROSS APPLY and how to use it

Randy Sims SQL Server

CROSS APPLY is a beautiful alternative for your FROM clause. It isn’t JOINed to anything but it can be correlated to other fields in your query. It also allows for an aggregate to be run on a row-by-row basis. CROSS APPLY can be very useful, and the following will show you what it is and […]

SSIS: Show Values of Variables in Progress or Execution Results

Randy Sims SQL Server

Here’s a simple way to show the values of variables in an SSIS package. This will allow you an easy way to view whatever variables or parameters you want to see in the progress or execution results of a package. It will also be visible in the Execution Report in the Integration Services Catalogs. This […]

Dynamic SQL – Two Ways to Make a SQL String and Run It

Randy Sims SQL Server

Dynamic SQL is just like it sounds: creating a SQL statement dynamically. A normal SQL Statement is just the string you are going to run. SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1; But should you have a good business reason to do so, maybe you want to dynamically create the queries. (Personally, Dynamic SQL is the last […]