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.

Learning Mandarin: Looks like, sounds like and more

I mentioned in an earlier post that I like to learn sets of words or phrases, rather than trying to learn words individually. In that post, I discussed "Electric Words". 

Another interesting word grouping, is what I call the "like" group.

起来 (qǐlái) can be used for a number of things, often like "up", or "stand up", or "add up" and so on. It's pronounced a bit like "chee lie".

In fact, our then Prime Minister Malcolm Turnbull upset a number of Chinese by altering the sentence 中国人民站起来了 (Zhōngguó rénmín zhàn qǐláile) which basically means "The Chinese people have stood up". This sentence has long been attributed to Chairman Mao at the opening of the PRC (People's Republic of China) on October 1st 1949. Turnbull said that now 澳大利亚人民站起来 (Àodàlìyǎ rénmín zhàn qǐlái) or "Australians stand up". Some Chinese were offended by that reference, even though many claim that Chairman Mao didn't ever say it anyway.

But the ones I wanted to talk about today are the 动词 (Dòngcí) ie: verbs, or more specifically "perception or sensation verbs", followed by 起来 (qǐlái).  

Here are some examples:

看 (Kàn) means to look, so 看起来 (Kàn qǐlái) means "looks like".

听 (Tīng) means to listen, so 听起来 (Tīng qǐlái) means "sounds like".

闻 (Wén) means to smell, so 闻起来 (Wén qǐlái) means "smells like".

尝 (Cháng) means to taste, so 尝起来 (Cháng qǐlái) means "tastes like".

摸 (Mō) means to touch or feel, so 摸起来 (Mō qǐlái) means "feels like".

It's worth noting at this point though, that this last phrase is a great example of how tools like Google Translate are awesome, but often have "interesting" translations when taken out of context. It says the last one means "touch up" or to "grope". Bing Translate says "Feel it".

I'd be guessing that a sentence like 你的床摸起来很舒服。(Nǐ de chuáng mō qǐlái hěn shūfú.) is more likely to be "Your bed feels very comfortable", even though, that would probably be better translated as 你的床感觉很舒服。(Nǐ de chuáng gǎnjué hěn shūfú.)

很舒服 (hěn shūfú) basically means "very" and "comfortable". All of the phrases above would typically be followed by an adverb and an adjective like this.

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.

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.

Opinion: Size is the last great legal discrimination

Background

I was reading a story about a woman in the US who was removed from a flight because she didn't want to sit in the middle seat between two large people either side of her. When questioned by another passenger, she responded "do you want to sit between those two pigs?"

Now if you replaced weight (which is contrary to common perception a nasty medical situation, not just a result of endless self-indulgence), in that story, with race, religion, other physical disability, etc., the public outrage would be huge.

Let's get something clear here. For a long time, the medical profession and the governments claimed that eating fat was the primary culprit, and that overeating and inactivity was the secondary cause for weight gain. Unfortunately for everyone involved, that's never been true.

There's a perception that overweight people have no willpower, yet I know so many overweight people who have way above average willpower, and it's obvious in every other aspect of their lives.

When inflammation, insulin, and other hormones get involved, telling people to just eat less is like telling someone to just stop growing quite so tall.

Cold hard reality is that the medicos had it wrong for a very long time, and ignored all research to the contrary. The real reasons are now becoming quite apparent, and I'll just note that if you still think people get overweight primarily from overeating, and inactivity, you are both wrong, and you are part of the problem.

This has allowed there to still be a legal discrimination against overweight people, and worse, it allows for people to feel justified in victim shaming. The same people wouldn't be game to blame short kids for being short, tall kids for being tall, Asian kids for being Asian, or blind kids for being blind, etc.

And so we have the woman in the story above, feeling justified in calling the overweight people in her row "pigs". And somehow that's meant to be OK.

It's not.

Airlines

And now we come to the real culprits of this story, even though they are rarely directly blamed. Airlines have been constantly reducing passenger space on airliners for a long time, at the same time that average passengers have been getting larger.

How is this OK?

Then when people don't fit in the seats any more, somehow it's their own fault?

And yet if you're tall, the airlines cater for you. But not if you're wider.

Note the advert from QANTAS above. Want window or aisle: OK. Want more leg-room: also OK. But want wider? Ah, that's a problem.

I love flying with Virgin Australia, but it's the same issue. Note their page on Economy X.

Again, extra legroom? No problems. Wider? Oh…

Seat Options

Now, you could argue that there are options available. If you want a 50% larger seat, you could buy a business class seat (or in the USA, 1st class seat). Really? For 50% more room, is it fair to need to pay this difference?

So a "normal" person can fly for $244 but a larger person should pay $1432 to get 50% more space? Sorry, but it's not the person that's disgusting, it's those prices.

Now to be fair, some airlines will let you buy two seats. Even that's nasty. For 50% more room, we need to pay twice the fare, because airliners aren't required to allow for wider people?

And worse, I've done this in the past, and I can tell you it isn't easy. Most airlines have no way for you to do this on their online booking sites. Why? That in itself is poor.

I have clients who want to buy airline seats for me. Is there any way for me to make them larger after they or their travel agent has bought them?

That would be No.

I've also regularly had issues after purchasing double seats. It's then almost impossible to change a flight, compared to a normal ticket. I've even had my seating allocated to two separate places in the plane. And I've had flights where they decided that the two seats could no longer be offered. If you've booked a double seat flight on frequent flyer points, and need to change anything, that's into the realm of true challenge.

Why oh why does it need to be like this? And why do airlines put people, who already feel bad about being overweight (from societal pressure), into smaller and smaller spaces within the planes. They are already almost impossible to be comfortable in, and it's not fair to other surrounding passengers either.

I suspect that one day soon, the true nature of weight issues will become clear to the community, and real action will take place on this blatant discrimination. Some compassion will replace the victim shaming.

I'd just love to see the airlines get ahead of the game on this, as they are one of the main culprits causing the issues in the first place by their shrinking of passenger space.

 

 

Happy new year to all my Chinese buddies

Just a quick post to say Happy New Year to all my Chinese buddies and family members, and welcome to the year of the pig.

新年快乐 (Xīnnián kuàilè)

is pretty much "Happy New Year" directly translated. It's pronounced pretty much like "shin neean kwai ler", so remember to say that to your Chinese friends.

But you'll often also hear:

恭喜发财 (Gōngxǐ fācái)

which is pretty much "wishing you happiness and prosperity". It's pronounced pretty much like "gong she far tsai".

恭喜恭喜 (Gōngxǐ gōngxǐ)

is a phrase you'll often hear just for "congratulations".

Thank you to all those who've helped me with my continued learning of the Chinese language and culture.

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.