Shortcut: Clear server list in SSMS connection dialog

SQL Server Management Studio (SSMS) keeps a list of the server names that you have connected to, and prompts you with those when you drop-down the list while making a connection:

Eventually, that list can either become messy, it can include servers that don't exist anymore, and so on. You might want to clear up the list.

To do this in early versions of SSMS, you needed to locate the SqlStudio.bin file from the Documents and Settings area in your user profile.

Fortunately, that's no longer required.

All you need to do is to open this dialog, arrow down to the ones that you want to remove, and hit the Delete key.

Question: How should DateDiffNoWeekends work?

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.

Now if I calculate the DATEDIFF (in days) from Monday to Wednesday in a given week, the built-in DATEDIFF function returns 2. So it's not inclusive of the end-points, only of the distance between the dates.

I can calculate the number of days from today to tomorrow.

But if today is Sunday, and I ask how many days it is to Monday, and assume that I'm excluding weekends, how many days should that be? 0? 1?

Perhaps because a date is really starting at midnight early on the day, we should treat the first day as part of the period, but the last day as excluded. That would make Saturday to Monday be zero, and same for Sunday to Monday.

However, Friday to Saturday would be 1, as would Friday to Sunday. (because the day time of Saturday and Sunday would not be part of either calculation).

I'm thinking of updating the function based on this feedback, but would love to hear how you would expect such a function to work. Please leave a comment or email me.

 

T-SQL 101: #4 What are Server Instances in SQL Server?

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.

In early versions of SQL server, we could only have one copy of SQL server installed on the computer. In SQL Server 2000, we got the ability to have more than one copy of SQL server and these instances are quite isolated from each other, but installed on the one machine.

So that introduced the concept of a named instance. At the time, we could install 16 of these. You refer to them with the computer name followed by a backslash, then the name of the named instance.

For example, SDUPROD\Production could be one instance, and SDUPROD\Test could be another instance.

In later versions, the number of these that you could install increased to 50 but that's way more than would usually ever make sense.

You also don't have to have a default instance. You might only have named instances.

Local Server

If you want to connect to the local machine, there are other ways you can refer to the SQL Server instance.

  (i.e. a single period) can refer to the local machine. It makes a connection using what's called a shared memory connection.

(local) is a synonym for the single period, and uses the same type of connection.

You can also use these with named instances. I can refer to the Production named instance on my local machine this way:

.\Production

or

(local)\Production

Finally, you can also use localhost (or even 127.0.0.1) for the server, but this requires a TCP/IP connection, even though it's to the same machine. If you don't have TCP/IP enabled as a server protocol, that won't work.

Ports

Connections to SQL Server are normally made on port 1433. If you need to use another port, you put it after a comma in the server name like this:

PDSERVER, 5185

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 Queries for SQL Server course is online, on-demand, and low cost.

SDU Tools: T-SQL tools for working with Chinese Calendars and Years in SQL Server

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.

So next year (2020), Chinese New Year is January 25th, and it will be the year of the Rat.

These functions all work for years 1900 to 2099.

We also added a useful view called ChineseYears for working with these. It contains the following:

You can see them all in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

Shortcut: Change default text in new query window in SSMS

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.

I've also put a BEGIN TRAN that I might use before doing any ad-hoc modifications.

So I save that file, and when I do my next New Query in SSMS, I see this:

If I was really keen, I might also add templated values to be completed.

SDU Tools: Script Windows Logins for SQL Server

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.

But if you just want to create a script, that works too.

You can see it in use in the main image above, and you can watch it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

T-SQL 101: #3 Types of T-SQL Statements

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.

The second type of statement is a DDL statement. That's data definition language. These are used to define how data is stored. For example, you might need to create a new table to hold data. They include words like CREATE, ALTER, and DROP.

The third type of statement is a DCL statement. That's data control language. These statements are used to say who is able to access the data in the first place. They include words like GRANT, DENY, and REVOKE.

The vast majority of the commands we'll look at in this series are DML, because we want to either read data that's already in tables, or we want to put the data there.

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 Queries for SQL Server course is online, on-demand, and low cost.

SQL: What on earth is Halloween protection?

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.

To avoid this, we need to ensure that any update operation reads the data first, and keeps a list of the values that were read, before starting to do the update. For standard updates, SQL Server does this automatically. But this guy had fallen foul of the problem by using a dynamic cursor. If he had used a static cursor (which would have had horrid performance), he would have been ok. It's gets  a copy of all the data before starting.

It's not all about cursors though. Let's look at how SQL Server handles this in typical updates with an example:

The query plan for that is quite straightforward:

If I clustered the table on the SalePrice though, that would be a problem, and you can see how the query plan changes. Note the additional query plan step:

Lots of queries have this extra table spool step, and for similar reasons. It's not exactly the same problem but look at this query's plan:

In most high-level languages, you can't just say A = B and B = A to swap values over, but it works just fine in SQL. That's because SQL Server has read the values, and has a copy of them from before the changes were made.

The name Halloween protection comes from when the problem occurred during research on the System R project at the IBM Almaden Research Center back in 1976. So the name comes from when it happened, rather than from anything about the problem itself. The researchers ( Don Chamberlin, Pat Selinger, and Morton Astrahan) ran a query to raise everyone's salary by 10 percent, only if they had a salary of under $25000. They were suprised to find that after the query had completed, no-one had a salary of less than that, because the rows had been updated more than once, until they reached the $25000 value.

The name has been used to describe this problem ever since.

 

Shortcut: Undock tabs and windows in SSMS to other screens

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.

It then also appears separately in your taskbar in Windows:

You can later drag it back to redock it.

SDU Tools: Script SQL Server Table

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.

Have a bunch of pesky collation settings that you didn't want? It can remove them too, and it can let you choose things like the indents to be used during scripting.

You can see how to use it in the main image above. It's a procedure, that takes the following parameters:

@DatabaseName sysname -> Database name for the table to be scripted
@ExistingSchemaName sysname -> Schema name for the table to be scripted
@ExistingTableName sysname -> Table name for the table to be scripted
 @OutputSchemaName sysname -> Schema name for the output script (defaults to same as existing schema)
 @OutputTableName sysname -> Table name for the output script (defaults to same as existing table)
@OutputDataCompressionStyle nvarchar(10) -> must be one of SAME, NONE, ROW, PAGE (SAME uses whatever the table's first partition currently has)
@AreCollationsScripted bit -> Should all collations be scripted (default is 0 for no)
@AreUsingBaseTypes bit -> Should the table use the underlying base types instead of alias types (default is 1 for yes)
@AreForcingAnsiNulls bit -> Should the script include code to force ANSI_NULLS on (default is 1 for yes)
@AreForcingAnsiPadding bit -> Should hte script include code to force ANSI_PADDING on (default is 1 for yes)
@ColumnIndentSize -> How far should columns be indented from the table definition (defaults to 4)
@ScriptIndentSize -> How far indented should the script be? (defaults to 0)
@TableScript nvarchar(max) OUTPUT -> The output script

The script in the main image produced the following output:

I've mentioned that 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.

But if you just want to create a script, that works too.

You can watch it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com