Sql-Server

Years ago, I had the privilege of presenting “what’s new in SQL Server 2012” sessions at many locations around the world. When you present sessions, you can sometimes learn as much as you teach. What I learned while delivering those sessions is that the product group sees what’s important in a release very differently to what the attendees do.

Each time there’s a new version of SQL Server, there will normally be three or four marketing pillars (groups of functionality), and each will have about eight to ten bullet points.

2018-05-10

Sometimes you need to remove all characters from a string, except for a certain type of character. This often happens when processing incoming data from an external source, before you use it within your systems.

In our free DBA and developer SDU Tools, we added some functions to help with this.

AlphabeticOnly limits the output string to just alphabetic characters, based on the English language.

AlphanumericOnly limits the output string to just alphabetic characters (again based on the English language) or the digits from 0 to 9.

2018-05-09

I’m puzzled that so few people use script projects and solutions when working with SQL Server Management Studio (SSMS).

They are easy to use. Let’s see an example:

Instead of just starting to create scripts, from the File menu, click New, then Project. You are greeted with the new Project dialog which also allows you to create a solution.

I’ve selected SQL Server Scripts as the project template. Note there is also one for Analysis Services scripts. I’ve named the project, picked a location, and chosen to create a new solution. I might choose to create a solution with a different name if it will contain multiple projects. In this case, I’m not doing that.

2018-05-07

In early versions of SQL Server, the only way to try to track dependencies between tables, procedures, functions, etc. was to use the sp_depends stored procedure. And everyone thought it lied. The real problem was that it didn’t understand partial dependencies and deferred resolution of objects. For example, it got confused if you created a procedure that mentioned a table, then later created the table.

SQL Server 2012 introduced far superior dependency views, and SQL Server Management Studio (SSMS) now shows dependencies using those views under the covers.

2018-05-04

In an earlier post, I described how I didn’t particularly like all the colors that are shown in the scroll bar now in SQL Server Management Studio (SSMS):

In that post, I described how to turn them all off, or at least how to kill off some of them. But, of course they are there for a reason. Instead of turning them all off, you might decide to make sense of what they are there for.

2018-05-03

If you aren’t aware of the SQL Server data type called sql_variant, don’t feel bad; you’re not alone. Many people who’ve worked with SQL Server for a very long time haven’t used it.

sql_variant is a very special data type. It’s the data type that you use when you need to store other data but you’re not sure what type you will need to store. Once it is stored though, it has the appropriate data type.

2018-05-02

SQL Server 2005 introduced the NEWSEQUENTIALID() function, with some fanfare. I could never see it being interesting in any way, and I still don’t.

The argument was that there were so many performance problems being caused by developers using GUIDs as primary keys in tables, and those primary keys also ended up being the clustering keys for the tables (doesn’t have to be that way but that’s the default behavior). The random order was then causing big fragmentation issues when INSERT operations were performed.

2018-05-01

We’re coming into the season for another round of SQL Saturday events.

I can’t be at all of the local ones unfortunately. (Sad to miss Sydney but won’t be around then). I’ll be presenting sessions in Brisbane, and Auckland, hopefully also in Melbourne, then delivering a number of user group sessions around Switzerland.

In Brisbane, I’ll be speaking on SQL Server Management Studio tips and tricks: http://www.sqlsaturday.com/713/eventhome.aspx

That will cover off a number of the items from our new eBook: http://ssmsbook.sqldownunder.com .

2018-05-01

In a relational database like SQL Server, transactions are the mechanism used to ensure that entire operations either complete or are rolled back. The obvious example used to be that if you transfer funds from one place to another, that both the debit and the credit need to occur, or neither occurs.

Fair enough and straightforward enough.

Computers also try to give you the illusion that you are the only one using them. Concurrent transactions are a place where that illusion breaks. While you are working in a transaction, you are potentially affecting other users of the system. So we try to manage how long transactions are held open for. The aim is to always have transactions protect what’s needed but be as short as possible.

2018-04-30

SQL Server Management Studio (SSMS) is a highly configurable tool. One of the areas that’s often ignored but which can be quite important is color configuration.

SSMS color codes SQL scripts (and other types of files that it understands) as you type.

This is really useful but I’ve found on some systems that some of the color selections aren’t great. Here’s an example:

On many systems that I work with, the color for sys.tables in the query above is quite a fluoro green and almost unreadable. But if you don’t like this, you can change it.

2018-04-26