Automated DB Source Control (2 of 5): The SSIS Package

Josh,

To continue from the last post, you'll need an SSIS package to drive the process.  This package joins all the steps, running the custom code, the SQL Scripts, and Checking In the changes.  Here's what you came up with...

Automated DB Source Control (1 of 5): Overview and Setup

Josh,

So you've run into a situation where a SQL Database is considered the container of production and/or development code.  You would like to source control all the tables, views, stored procedures, and other database objects.  However, saving the TSQL to the appropriate files/project and checking it in can be very cumbersome.  It would be nice if the developer could just focus on getting the appropriate changes in the database they are working on and let the source control be automatically updated each night.  Here is the solution you came up for this...

Case Sensitive SQL Statements

Josh,

So, you have a column with some upper case and lower case values.  By default, SQL Server treats upper case and lower case the same.  The case sensitivity can be set at the Server, Database, or Column level as well as on a query by query basis...

SQL Derived Table from Values

Josh,

So you have a small set of data you want to use in a query.  The data is static and temporary in nature so you don't really want to store it in a table.  A derived table from values might do the trick!

SSRS Multi-Value Parameter Performance Issue

Josh,

So you've built a report off a query that uses a multi-value parameter and you've defaulted the parameter to use every option from a dataset.  This works fine for a few values, but when the multi-value parameter contains hundreds, the query is dreadfully slow!

The solution is to add a default value to ignore your parameter in the query...

SQL Get Column Data Counts Procedure

Josh,

So you are reviewing a data set and want to know the unique values of a column and how many times they occur in the table.  Use this nifty procedure instead of re-writing it each time:

Create Hash of Multiple Columns

Josh,

So, you need to compare several columns of one table to several columns of another.  Perhaps you are checking to see if data has changed before updating a row, or you need to track if multiple columns have changed.  Besides doing a bunch of AND statements, a nifty feature of SQL is the HASHBYTES function.

Search for Table Columns Using the System Schema

Josh,

So you want to search if a column exists in another table, but can't remember which table.  Here's a handy script to be able to search a specific schema for a certain column name:

Select Into Creates Identity Column on New Table

Josh,

Say you have a table like this:

CREATE TABLE MyOldTable (MyId INT IDENTITY(1,1),Name VARCHAR(100)
)

Then, you need to create another table from this for simplicity sake:

SELECT
     MyId,
     Name,
     row_number() OVER(ORDER BY Name DESC) AS MyNewIdINTO
     MyNewTableFROM
     MyOldTable

Looking at the "MyNewTable", you'll notice the MyId column is defined as an INT IDENTITY(1,1)!  Most likely you didn't intend for this to happen.  The fix is pretty simple:

Using ROW_NUMBER() to Get the Last Event for Each Category

Josh,

So the scenario is you have a table with a list of events like this:

CREATE TABLE [dbo].[Events](
   [EventDate] [datetime] NULL,
   [Category] [varchar](10) NULL,
   [EventDescription] [varchar](60) NULL
)  

You only want the latest record for each Category.  One way to accomplish this is the ROW_NUMBER() function...

Sql Check if Object Exists

Josh,

You know those situations where you want to drop and recreate something, or create something if it doesn't exist?  You can use the object_id function to check if an object exists in the database.

Maximum Column Content Lengths

Josh,

So you are working on loading data into a table from another table and you're getting truncation errors.  To keep the destination table as small as possible for speed, you need to find out which column is causing the truncation error and how much you need to increase the destination columns size.

Let's use the handy SYS schema to help us out...

SQL Format Function

Josh,

If you need a date/time in a format other than the standard ones covered by the CONVERT function, try out the FORMAT function:

SELECT
   FORMAT(@SomeDateTime, 'yyyy-MM-dd') AS FormattedDateTime  


This will turn a date/time like "2014-04-24 14:38:13.0000000" into "2014-04-24".

Auditing MS SQL Dependencies

Josh,

So one of the concepts discussed at the Kimball training was around audit metadata and tracking the objects and actions that get a Dimension or Fact to its final result. This definitely seems like it could be valuable but also could require a lot of effort.  Unless it is a firm business requirement, it isn't likely to be implemented due to the large amount of time and effort to track and document the lineage.

However, some of the tracking might be already done for you!

Generate C# Properties from SQL Table

Josh,

So, you like to design your models in the database but need them in C# as well. To make the recreation in C# quicker, you created a SQL script to convert the table columns into C# Properties.

Reviewing SQL Schema Dates

Josh,

So you're wanting to know some basic dates around a schema in SQL Server.  Unfortunately the "sys.schemas" view doesn't have anything to indicate create or modify dates.  Furthermore if the schema creation isn't logged, then the only way to review this is to relate the schema to the "sys.objects" view.

Row Level Security in MS SQL

Dear Josh,

You just got back from the excellent Kimball DW/BI Lifecycle in Depth in NYC, and one of the concepts briefly mentioned was row level security.  You made a note to look into this and came up with this demo script:

MSSQL Character Value Rank

I recently ran into a scenario where a calculation was needed between an INTEGER and a VARCHAR field, with the issue being the VARCHAR field might contain values that could not be converted into an INTEGER type.  To exclude the values that contained text, I thought about adding to the WHERE clause only records with the field in question less than a character value.  But I ended up settling on a solution found on Stack Overflow here, as it works in more scenarios.

I was still curious as to the Character Value rank and after a search for a listing of Characters and their SQL ranking came up empty, I decided to create one with the code below.