Sql-Server

Calculating the columns required for a date dimension using T-SQL is a pretty common request from SQL Server developers. So we decided to make it really easy to do. In our free SDU Tools for developers and DBAs, there is a table-valued function called DateDimensionColumns, for just this purpose.

You supply a date, and the month that your financial year starts in, and it supplies the output columns.

You can also easily combine it with our DatesBetween function to get dimension columns for a range of dates:

2019-02-13

One of the tools we have in our free SDU Tools for Developers and DBAs is a version of DATEDIFF that excludes weekends. It’s DateDiffNoWeekends.

This has been a very popular function.

One of the customers who is using these tools found that it was returning different results to what he expected. And it got me re-thinking how it should work.

Let’s start by assuming that weekends are Saturday and Sunday. That’s what most people seem to assume.

2019-02-12

Before you can start to execute queries against SQL Server, you need to connect to the server. The first concept that you need to understand is what a server instance is.

You need to know is what name you should use to refer to the server. As an example, if I have a computer called SDUPROD and it has a copy of SQL server installed on it, we could often just connect to the name SDUPROD. If we do that, then what we’re doing is connecting that what’s called the default instance of SQL Server.

2019-02-11

To celebrate Chinese New Year this week, I thought I should write about some options that we recently added to our free SDU Tools for developers and DBAs, for working with Chinese calendar concepts.

Let’s start with the basic one: when is Chinese New Year? We added a function called DateOfChineseNewYear. You just supply our year number (Gregorian calendar), and it will tell you when Chinese New Year is.

You can see it in use in the image above, along with the much more cute function that tells you what the Chinese Zodiac animal is for the year. It’s called ChineseNewYearAnimalName.

2019-02-08

In SQL Server Management Studio (SSMS), when you click New Query, a new query window is opened, but because it’s blank, what might not be immediately obvious is that it’s based on a template.

The location of the template depends upon the version, but for SSMS 17.6, you’ll find it in this folder:

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql

The file is called SQLFile.sql.

One of the things that I often forget to do is to change my connection to the correct database. Let’s add a USE statement to make that obvious.

2019-02-07

In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. To script out Windows authenticated logins (as opposed to SQL logins), we have ScriptWindowsLogins.

You can see how to use it in the main image above. It’s a function, that takes a list of the logins to script. You can pass the value ALL or a comma-delimited list.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

2019-02-06

In earlier posts of this introductory series for learning T-SQL, I talked about what SQL is, then about what T-SQL is. There are three basic types of T-SQL statements: DML, DDL, and DCL.

The first of these are called DML. That’s data manipulation language. These are the main language statements that you’ll use and are about getting data into and out of tables. They include reading data by using SELECT, and modifying it by INSERT, UPDATE, DELETE, and MERGE.

2019-02-04

If you’ve worked with SQL Server (or with databases in general) for any length of time, you will have heard someone refer to “Halloween protection”.  It refers to a problem where update operations could inadvertently end up modifying a row more than once.

I saw someone who was pretty knowledgeable complaining about just this problem recently. He was using a dynamic cursor in SQL Server. The problem was that he was progressing along an index, but when a row was modified, it was relocated to a position in front of where he was processing, so it got processed again.

2019-02-01

Like Visual Studio that it’s based upon, SQL Server Management Studio (SSMS) is very flexible when working with query windows and tabs.

Most people realize that you can undock and move tabs and windows around. Usually they do that by accident and then realize that the Reset Window Layout option in the Window menu is helpful.

But one option I’ve found that many people don’t seem to realize is that you can undock just a single query window and move it outside the bounds of SSMS. You can even place it across on another screen if you have multiple screens.

2019-01-31

In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. The tool that I’m describing today is one of the most sophisticated tools in our scripting options. It’s ScriptTable.

It’s very flexible. For example, it can change the name of the table, or the schema that it’s in. It can force ANSI_NULLS and ANSI_PADDING on or off. It can change user-defined data types to their base types, change compression strategies, and more.

2019-01-30