Sql-Server

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

When you’re writing T-SQL code, it’s really common to need to extract components of a date, and the most common need is for year, month, and day. So T-SQL has separate functions just for that.

You can see the output from this query here:

The YEAR(), MONTH(), and DAY() functions take a date (or datetime, datetime2, smalldatetime) as input and return an integer for the year, month, or day.

2020-05-11

I’ve seen questions on the forums asking about where localdb stores its database files. Often, the question is about how to stop them going into the C:\Users folder. Clearly, that doesn’t seem to be a good spot for them.

localdb stores database files in the C:\Users area by default as it’s trying to find a location that the current user will have permission to write to. While I understand the rationale, that’s not where most users will want those files.

2020-05-08

There’s a question that I hear all the time in SQL Server forums:

Why didn’t the transaction roll back when a primary key violation occurred?

Take a look at the code in the main image above. Would you expect it to execute the second and third INSERTs if the first INSERT failed with a primary key violation? So many people would. If you’re one of them, read on.

By default, that’s not how SQL Server works.

2020-05-07

I recently came across a requirement to extract someone’s initials from within their name. That was a new one for me, so we added a new function into our free SDU Tools for developers and DBAs. It’s called InitialsFromName.

It’s a straightforward scalar function that takes two parameters:

@Name nvarchar(max) is the name to extract the initials from

@Separator nvarchar(max) is a separator placed between the returned initials.  (Make it an empty string for none)

2020-05-06

It’s really common to need to know the current date and time in T-SQL code. Ever since the beginning of the SQL Server product, we’ve had a function called GETDATE(). It returns the current date and time of the server as a datetime value.

In SQL Server 2008, we got new higher precision data types, and so new current date and time functions were added as well.

SYSDATETIME() is a function that returns the current date and time as a datetime2 value. And SYSDATETIMEOFFSET() returns the timezone offset for the server as well.

2020-05-04

In SQL Server 2005, I really appreciated the addition of DDL replication.  Before that, I found that making schema changes to replicated tables was a pain in the neck. Almost every time, I was there dropping replication and later setting it up again. That’s easy but takes a long time, so sometimes, I’d resort to lots of manual hacking.

Using DDL replication, I could make a whole lot of different types of changes and the subscribers would be updated automatically, at least in most standard subscriptions.

2020-04-30

I mentioned in my last two SDU_Tools posts that I get really good feedback all the time from users of our free SDU Tools for developers and DBAs, but none more than when we have more and more date and time functions. Yet another pair of functions that we added in v19 that was recently shipped was StartOfYear and EndOfYear.

We had feedback that even though we had StartOfFinancialYear and EndOfFinancialYear, we didn’t have just StartOfYear and EndOfYear. So we’ve fixed that. These are simple scalar functions that take a single date parameter, and return the date for the start of the calendar year and the end of the calendar year that contain the date provided.

2020-04-29