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!

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...

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.