SQL: What is the difference between connecting to .\SQLEXPRESS and (localdb)\v11.0 ?

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

Local DB is a deployment option for SQL Express that runs as an attached process to another application, instead of as a service. It makes it easier to deploy applications like Visual Studio that need a copy of SQL Server locally but don't want to be heavy-handed by installing a service as well as the IDE. You can read about LocalDB here.

Visual Studio also just needs to use a SQL Server for a while during development, but doesn't need it running all the time. When you connect to "(localdb)\v11.0" you are connecting to one of these attached instances, and in this case using version 11. When you start it by accessing it from within Visual Studio, it shuts down again when you close Visual Studio.

It doesn't have to be attached to Visual Studio though. You could attach it to your own application. You can also have multiple versions of localdb on the same machine.

You can even start and stop Local DB separately from applications like Visual Studio. The SqlLocalDB Utility can be used to manage the lifetime (starting and stopping) of Local DB. You can read about it and the functions that it offers, here.

SQL: Adding time to Change Data Capture (CDC) Events

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:

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

There are two ways we can see the changes:

That returns the following:

The first SELECT returns all changes as they occur, and the second SELECT returns the net affect of those changes. Because NewEmployeeID #1 was inserted as Fred Jones, and later changed to Greg Low, the net affect is just of an insert as Greg Low.

But notice, there's no time value. What we can use to get the time is the LSN (log sequence number) by calling the sys.fn_cdc_map_lsn_to_time() function like this:

Now that returns:

You can see that the function returns us the time. The time is in local time for the server.

If you want to tear the example back down, just do this:

 

SDU Tools: Date Dimension Period Columns in SQL Server T-SQL

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

But the DateDimensionPeriodColumns function adds many more useful details:

Is Today, Is Yesterday, Is Tomorrow, Is Future, Is Working Day, Is Last Working Day, Is Next Working Day, Is Weekend, Is Same Month, Is Month to Date, Is Same Month Last Year, Is Month to Date Last Year, Is Same Calendar Year, Is Calendar Year to Date, Is Last Calendar Year, Is Last Calendar Year to Date, Is Same Fiscal Year, Is Same Fiscal Year to Date, Is Last Fiscal Year, Is Last Fiscal Year to Date

That should let you say bye-bye to a whole lot of tricky DAX; it's easy to just use those columns for relative or period filtering

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:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

 

T-SQL 101: #69 Extracting date components in SQL Server T-SQL with DAY, MONTH, and YEAR

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.

You can also do this with the DATEPART() function but these are simpler and clearer when you want these main components of a date.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SQL: How do I stop my database files going into the Users folder with localdb?

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.

The answer is to not let localdb pick where the files will go.

Instead of writing:

use a more complete command like:

While you're at it, check out the CREATE DATABASE syntax and examples for other options that might be useful to you, particularly the ones around filegrowth. (Hint: Use a fixed increment, not a percentage) Not everything will work on localdb but most will.

 

SQL: Why didn't my SQL Server transaction roll back on a primary key violation?

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.
 
It might not be a primary key violation. It might be any number of other errors in the question, but the answer is still the same.
 

What's going on?

The primary key violation is an example of what's called a statement-terminating error. T-SQL data modification statements are atomic. If the INSERT was inserting three rows, none get inserted.
 
But only the statement ends, not the transaction.
 
If, instead, the error was a batch-terminating error, there's a difference. SQL Server aborts the transaction and rolls it back.
 

How can I change that?

If you want to change that behaviour, put
 

at the start of your code.

It tells SQL Server to promote statement-terminating errors to batch-terminating errors. Once you do that, the transaction code above works as expected. The primary key violation aborts and rolls back the transaction.

But I've seen it work without that!

I also occasionally hear people say that they've seen a different outcome. They didn't use XACT_ABORT and it still rolled back. What they're usually missing is that their data access library has set it on for them.
You can tell if it's on by executing this code:

Note: there are a few edge cases on this. Errors that relate to invalid object names, etc. are different. But for standard code, this is how it all works.

SDU Tools: Extracting initials from a name in SQL Server T-SQL

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)

In the main image above, you can see the difference between the first two examples. The only difference is the separator.

You can use our tools as a complete set or as a library that provides great examples of how to write functions like these.

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:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

 

T-SQL 101: #68 Current date and time in SQL Server T-SQL with SYSDATETIME and SYSDATETIMEOFFSET

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.

You can see the output below:

You might also want the current date and time, based on Universal Time (UTC aka GMT). SYSUTCDATETIME() does that. It calculates UTC time by deducting the server's current timezone offset from the current local date and time.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

FIX: SQL Server Replication – Cannot alter column because it is 'REPLICATED'

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.

The issue

That was a good story for me up to a few weeks ago. I was working with a client, and they needed to modify the same column in six different databases. Each separate database was replicating (via transactional replication) to Azure SQL Databases.

The client made the changes and they all worked, apart from one server. It failed with the error:

Cannot alter column 'columnname' because it is 'REPLICATED'

I can't say I love that error message. Why is REPLICATED in quotes? (Pretend replication??)

So I was wondering what on earth was different about the servers, or how they were configured. I did a lot of searching and I found many unhelpful posts and comments. I was surprised how many said that you can't modify a column in transactional replication without dropping it and setting it up again. That's not true, and was obvious from all the other servers where it worked. I checked all their subscribers, and sure enough, the change was applied just like we expected.

The fix

I eventually found a useful comment from Steve Fenton.

He mentioned that he'd seen that error coming up if you happened to try to change a column while the Snapshot Agent is running.

I knew the Snapshot Agent shouldn't have been running but I checked with the client. Turns out the Agent had failed when it ran on the previous Sunday. It had a deadlock error, and stopped. So some part of SQL Server thought the Snapshot Agent was still running.

Sure enough, after the client did another execution of the Snapshot Agent, the column could be altered as expected.

I hope that error message gets fixed, as it's really, really misleading.

SDU Tools: Start of Year, End of Year in SQL Server T-SQL

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.

You can use our tools as a set or as a great example of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com