Sql-Server

This one is a very simple and quick tip.

When working in SQL Server Management Studio (SSMS), I often need to open File Explorer in the folder where the script file is stored. Turns out there is an easy way to do that.

There are two interesting options when you right-click the tab at the top of a query window:

Note that you can open the containing folder for the script. You can also copy the path to the script into the clipboard.

2019-03-07

In our free SDU Tools for developers and DBAs, a number of the string formatting functions have been quite popular, and we keep getting requests for even more.

Today’s post highlights two of these. Screaming Snake Case is words in capitals separated by underscores. Sponge Bob Snake Case has underscore separated words (hence the “snake case” part) with alternating capitals. (It’s just for fun).

You can see them (and some others) in action in the main image above, and in this video:

2019-03-06

When you create or rebuild an index in SQL Server, you can specify a FILLFACTOR value. It determines how full (as a percentage) that SQL Server should try to make the pages.

There is also one special value. Zero says “I haven’t set a value” and it applies the default, which by default, is 100%. Any value from 1 to 100 is the target percentage.

So why do people set lower values for FILLFACTOR? The argument is they know that their inserts are going to happen all over the table (i.e. not just neatly at the end of the table), and they’re trying to avoid page splits. And yes, lots of page splits can be a nasty thing. The most common problem case that people describe is where they’ve used a GUID as a clustering key (usually also a primary key) for a table. Then the inserts happen randomly throughout the table.

2019-03-05

One concept that often confuses newcomers to SQL Server is the difference between a login and a user.

In an earlier post, I mentioned that being authenticated to a server is what’s called a login. In this example shown, I have a log in here called Malathi. But as I said, this means nothing about what databases Malathi then has access to.

The connection between a login and a database is what the concept of a user is about.

2019-03-04

In SQL Server Management Studio (SSMS) for SQL Server 2016, a new search tool called Quick Launch was added. It’s this bar up the top:

Note that there’s another bar underneath it to the left. That’s not the Quick Launch bar. That’s the Find bar.

While the Find bar is useful for searching for text within queries, etc., the Quick Launch bar is useful for searching within SSMS itself.

2019-02-28

On occasions, I need to work with XML data in SQL Server T-SQL and one problem is that I need to be able to encode and decode strings, using the XML encoding standards. For example, my strings might include % characters or < or > characters, etc.

So we added a pair of tools (XMLEncodeString and XMLDecodeString) our free SDU Tools for developers and DBAs.

You can see them in action in the main image above, and in this video:

2019-02-27

There was an interesting discussion on a local mailing list about the performance of a table that was just used for logging. One of the things that intrigued me was that the developer had used the text data type to store his log messages.

Now text, along with ntext and image, was deprecated back in 2005. Instead of text, ntext, and image, you should be using varchar(max), nvarchar(max), and varbinary(max).

2019-02-26

You might be wondering what databases are.

A database is just a collection of information. Let’s take a look at an example:

On this system, I have a number of user databases (AdventureWorks, PopkornKraze, and others). There are also some system databases:

These are ones that are provided by SQL server itself, and mostly are used to hold SQL Server’s own configuration.

Now if we look inside any of the databases, we’ll see a list of tables, views, and programmable objects like stored procedures, and functions. Tables are where most of the information lives. In PopkornKraze, you can see the tables that are present:

2019-02-25

Every now and again, I come back to my laptop and find that it has rebooted for some reason, while I wasn’t expecting it. A prime cause of that is Windows Updates. I really, really wish that wasn’t so, but someone at Microsoft has decided that I must apply these updates. I have very little control over the time when that occurs. For example, if I’m on the road delivering presentations, there’s no “wait till I get home” option for Windows Updates.

2019-02-21

In a previous SDU Tools post, I described how to calculate the columns required for a date dimension. Another related tool that we have in our free SDU Tools for developers and DBAs, is a function that creates the columns needed for a time period dimension. It’s called TimePeriodDimensionColumns.

You supply the starting time, and the number of minutes per time period,  and it supplies the output columns.

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

2019-02-20