The Bit Bucket

When you’re writing T-SQL code, it’s also common to need to work out how far apart two dates are, or it could even be dates and times.

The DATEDIFF() function does that. In this case I’m asking how many days is it from 28th of February 2019 to 31st of July 2019?  The answer is:

So it says the difference is 153 days. Now we could have used any of those other intervals for that. I mentioned them before in this post.

2020-05-25

I used to be able to tell when someone moved from working with Access databases and arrived at SQL Server. What was the tell-tale sign?

All their tables had names like tblCustomers.

We’ve pretty much stopped people doing that, and we also have pretty much stamped out Hungarian Notation, at least in SQL Server.  We don’t use variable names like @intCreditRating, although there are still a number of people that I see clinging to Hungarian Notation when they work with SSIS, particularly in their .NET code. In their code, you’ll see values like strCustomerName and so on. The irony is that .NET developers long since moved past doing that, yet people writing .NET code in SSIS still do it.

2020-05-22

I answered a forum question the other day about how to list all the columns in a SQL Server database. That’s straightforward enough, but they also asked for all the extended properties for the column.

In case you need to do this, here’s some code:

SELECT s.[name] AS SchemaName,
       t.[name] AS TableName,
       c.[name] AS ColumnName,
       c.is_nullable AS IsNullable,
       typ.[name] AS DataTypeName,
       c.max_length AS MaximumLength,
       c.[precision] AS [Precision],
       c.scale AS Scale, 
       ep.ExtendedPropertyName,
       ep.ExtendedPropertyValue
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON t.object_id = c.object_id 
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id 
INNER JOIN sys.types AS typ
ON typ.system_type_id = c.system_type_id 
AND typ.user_type_id = c.user_type_id 
OUTER APPLY 
(
    SELECT ep.[name] AS ExtendedPropertyName,
           ep.[value] AS ExtendedPropertyValue
    FROM sys.extended_properties AS ep
    WHERE ep.major_id = c.object_id 
    AND ep.minor_id = c.column_id
) AS ep
WHERE t.is_ms_shipped = 0
AND t.[name] <> N'sysdiagrams'
ORDER BY SchemaName, TableName, ColumnName, ExtendedPropertyName;

How it works

I start with the sys.columns view and join it to sys.tables and sys.schemas, to get the schema and table name. The other reason is to make sure it’s not a Microsoft-supplied table. I also wanted to exclude the sysdiagrams table that is created when you first create a database diagram using SQL Server Management Studio. (Curiously, that one’s not flagged as a Microsoft-supplied table).

2020-05-21

The DatesBetweenNoWeekends function in our free SDU Tools for developers and DBAs, is really popular. It provides a range of dates between starting and ending dates, ignoring weekends.

DatesBetweenNoWeekends is a simple table-valued function that takes two parameters:

@StartDate - the first date to return @EndDate - the last date to return

The columns returned are:

DateNumber - a sequential number for the dates returned DateValue - the date

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

2020-05-20

In some countries, post office boxes are quite anonymous. And for that reason, some vendors aren’t keen to send goods to PO Boxes. But that’s not all countries. In Australia, for example, you have to provide all sorts of ID to the post office to be able to get one.

Why PO Boxes?

The fundamental reason that many people use PO Boxes is to have a relatively safe location for their mail to be collected. At so many houses, letter boxes are quite unsafe. And for people living in apartments, the situation is often far, far worse.

2020-05-19

When you’re writing T-SQL, you might also need to add intervals to dates, perhaps add days or subtract days and so on. The DATEADD() function is the one that we can add or subtract intervals to the date and time.

Now in the example shown here, what I’ve said is in 20190228 or  28th of February 2019. I want to add on 12 days. To subtract 12 days, I would have just put -12 instead of 12. You can see the output here:

2020-05-18

I keep hearing questions from developers about the difference between SQL Server Express Edition and LocalDB. One asked me the other day:

What’s the difference between connecting to .\SQLEXPRESS and (localdb)\v11.0 ?

SQL Express (it’s really SQL Server Express Edition) is a service-based version of SQL Server i.e. it runs as a service all the time, independently of other applications. When you say .\SQLEXPRESS you are looking for a named instance of SQL Server called “SQLEXPRESS” that is on your local machine and connected to via a shared memory interface (that’s what the dot is).

2020-05-15

Several times now on forums, I’ve seen questions about Change Data Capture (CDC). People like the way they can retrieve details about changes that have occurred (often to trickle-feed into a data warehouse), but they are puzzled why CDC doesn’t tell them when the event occurred. There’s an easy fix for that.

Let’s start by doing a quick CDC setup:

DROP DATABASE IF EXISTS CDC;
GO

CREATE DATABASE CDC;
GO

USE CDC;
GO

CREATE TABLE dbo.NewEmployees
( 
    NewEmployeeID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    FullName nvarchar(100)
);
GO

-- note new last few columns in:

SELECT * FROM sys.databases WHERE name = N'CDC';

-- enable cdc at the database level

EXEC sys.sp_cdc_enable_db;

-- enable cdc for the table - requires SQL Agent running

EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'NewEmployees',
  @supports_net_changes = 1,
  @role_name = NULL;

-- review the state of CDC

SELECT name, is_tracked_by_cdc FROM sys.tables;
GO

And let’s then add three rows of data, then change one of them:

2020-05-14

The DateDimensionColumns function in our free SDU Tools for developers and DBAs, has been really popular. It provides the standard columns that are part of a typical date dimension in a data warehouse. But we’re especially proud of a new function that can be used to expand a date dimension with really useful additional information. We call it DateDimensionPeriodColumns.

The DateDimensionColumns function had the usual columns:

Date, Day Number, Day Name, Short Day Name, Month Name, Short Month Name, Month Number, Month Label, Year, Year Label, Day of Year, Fiscal Month Number, Fiscal Month Label, Fiscal Year, Fiscal Year Label, Day of Fiscal Year, ISO Week Number

2020-05-13

Most websites that I visit have a link at the bottom of the page that suggests that you can use it to contact either the website team or the company that owns the site. (Might not be the same people) Based on years of trying, my expectation of ever getting a response from using one of these links is close to zero.

If you have a website that has a contact link, does it lead anywhere sensible?

2020-05-12