Shortcut: Running SQL Server Management Studio as someone else

You don’t always want to run SQL Server Management Studio (SSMS) as your current login for Windows.

Now if all you want to do is to use a SQL Server login, then that’s easy. When you connect to a server in Object Explorer, or when you start a new Database Engine query, you can just choose SQL authentication instead.

But three other scenarios commonly occur.

If you need to run SSMS as an administrator on a machine with UAC, you can do this:

You right-click the link to SSMS and choose Run as administrator.

Another option is that you need to run as another user ie: not just an administrator. If you are using Windows 10, the way to do this is to hold the Shift key before you right-click the SSMS link. Then you see this instead:

And you can then choose the Run as a different user option to log on as someone else just for this one application.

The third scenario is when you are needing to make a Windows authenticated connection to a server, but from a system that is not part of the domain. In that case, you need to run SSMS from the command line like this:

You will need to modify that command to point to the current location of Ssms.exe for the version that you are running.

You will then be prompted to log on to the domain.

Shortcut: Adding multi-level undo, redo to the SSMS toolbar

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.

So, when SQL Server 2012 was released, what got the biggest reaction from the audiences? Was it the new availability groups? Was it the new tabular data model in SSAS? Was it the new project model in SSIS?

It might (or might not) surprise you to hear that the biggest reaction world-wide to the release came when I showed the audience that the undo/redo options in the SSIS designer now actually worked.

Even better, they were multi-level undo and redo.

SQL Server Management Studio can also use the same option. By default, it only has a single-level undo and redo. It can go back quite a distance in undo but it does so one step at a time. Adding a multi-level undo and redo can really improve your editing experience. I have no idea why it’s not there on the toolbar by default. So let’s fix that !

First let’s see the toolbar as it started.

Right-click a blank area in the tool bar then choose Customize.

Then choose the Commands tab, the SQL Editor for the Toolbar, and click Add Command:

From the Categories, choose Edit. When the commands appear, scroll down to the MultiLevel Redo and click OK.

Do the same to add MultiLevel Undo, then move them down to where you want them using the Move Down button. I like to have them just after the comment / uncomment buttons.

Once they are in place, note that unlike the normal undo (or Control Z), you can click the button, see a list of your previous actions, and choose how many to apply.

 

Once you undo some, the same applies to Redo.

 

 

SDU Tools: Alphabetic Only and Alphanumeric Only in T-SQL

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.

You can see both in action in the main image above and here:

For more information on joining SDU Insiders to get our free SDU Tools and other resources, please just visit here:

http://sdutools.sqldownunder.com

Shortcut: Using script projects and solutions in SQL Server Management Studio

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.

To get to the point faster, here’s one that I created earlier:

There are three sections of interest:

Connections – keeps details of all connections used by the project. I typically only have one for any project but I can imagine scenarios where I would have more. A big advantage of this is that if I need to change the connection, I do it once, and all scripts here will use it when I open them.

Queries – as the name says. A nice addition in recent versions is that it automagically keeps them shown in alphabetical order. It didn’t used to do that so we had to remove them all and put them back in.

Miscellaneous – you’ll find if you right-click this that there’s nothing of interest. So how does something get there I hear you ask? Well it’s when you right-click the project and ask to add an existing item.

If you pick any file apart from those with a .sql extension (such as a text file), they’ll be placed into the Miscellaneous folder.

These projects are a great free built-in way to manage your scripts.

Shortcut: Dependency tracking in SQL Server Management Studio

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.

Here’s an example. If I right-click the Application.DeliveryMethods table in the WideWorldImporters database, I can choose to View Dependencies:

By default, you are shown the objects that depend upon the selected object (in this case the table that we right-clicked):

Note that this is a multi-layer dependency tree. We can see that the Customers table depends upon this table, as does the AddCustomers stored procedure. The Orders table also depends upon the DeliveryMethods table, and through that, the CustomerTransactions table, and from there, onto the GetTransactionUpdates procedure.

We can also see objects that the DeliveryMethods table depends upon:

In this case, we can see that the table depends upon its own primary key (ie: DeliveryMethodID), and on the People table because there is a foreign key to the PersonID column in that table, for the last person who modified the rows in the table.

We can also see dependencies for other types of objects. Here is the tree for the InsertCustomerOrders stored procedure:

It depends upon the OrderLines table, the OrderList and OrderLineList table types, and the CalculateCustomerPrice function.

I’m really pleased that such a good dependency system is available within SSMS.

Shortcut: Making sense of the colors in the SSMS scroll bar

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.

The colors that are displayed are indicating the following:

Red – this is showing where syntax errors appear in your code

Blue – this shows where the cursor currently is. That’s helpful when you have scrolled but haven’t moved the cursor. However, given this is the most useful one for me, I have to say that when all the other colors are present, it’s the one that I find hard to locate.

Yellow – this is indicating changes that you have made but have not yet saved.

Green – this is showing saved changes.

Maroon – this shows the location of “marks” – for us this means breakpoints

Black – this shows bookmarks “Marks” are shown in maroon (Breakpoints) and black (Bookmarks).

And remember that you can change which ones are displayed by right-clicking the scroll bar and changing its settings.

SDU Tools: SQL Variant Info for T-SQL

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.

For the developers, this is a bit like var in C#. In C# if I use code like this:

I could have instead written:

and the variable customer ends up being of type CustomerLink.

Similarly, in SQL Server, look at the main image above. I’ve defined a variable called @Value as being of sql_variant data type. I’ve then assigned an ASCII string to it.

In our free DBA and developer SDU Tools, we’ve added a function to let you interrogate the contents of a sql_variant. In the image, you can see that it returns a table (with a single row) showing the details of what is currently stored in the sql_variant value.

You can see it in action here:

For more information on joining SDU Insiders to get our free SDU Tools and other resources, please visit here:

http://sdutools.sqldownunder.com

Upcoming SQL Saturdays – Brisbane, Melbourne, Auckland

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.

I hope to see as many people as possible there. Please come and say hi.

Opinion: NEWSEQUENTIALID is a pointless function

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.

The logic was that NEWSEQUENTIALID() would give you an ever-increasing value that was like a GUID. But for me, it missed the main reason for the GUID values in the first place.

If you look at why people use GUID values, regardless of if it’s to support cross database or server merges or replication or whatever, it’s all about being able to generate a value that’s unique without having to have any central thing that’s issuing the numbers. By comparison, if I use an IDENTITY value or if I use a SEQUENCE as a default, I have to go to the database to get the value before I can use it.

With a GUID, I can just create one, assemble a bunch of things in memory that use it, then throw them all into the database without caring whether they’ll collide with anything already in the database, or at least that’s the thinking.

ie: the core logic is that it can be done outside the database and without referring to the database.

The fundamental problem with NEWSEQUENTIALID() (even ignoring its other flaws) is that the only way you can use it is as a default value for a column inside the SQL Server database. You can’t even just SELECT it.

If you’re going to go to the database to get a value, you might as well just get an int or a bigint in the first place. Getting a “pretend” GUID is of no value at all.

 

 

SQL: Implicit vs Explicit Transaction Handling – JDBC Driver I’m looking at you

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.

If you want applications to scale, you need to clearly manage the lifetime of your transactions.

In SQL Server, if I just execute an UPDATE statement, the statement either completes or doesn’t but no other transaction is involved. This is the default behavior.

But SQL Server also has an option to enable implicit transaction handling. This used to often be called “chained mode”. I can enable it like this:

With this enabled, whenever I execute a statement that modifies data, SQL Server automagically starts a transaction for me. Importantly, the data is not committed until I explicitly later say COMMIT.

I really dislike this option because it breaks my desire to manage transaction lifetimes, and breaks the rules about keeping transactions as short as possible.

I’ve noticed that some applications do this instead of managing transactions. JDBC-based apps seem to be key offenders. What they do is this:

  • enable implicit transaction mode
  • do whatever work they need to do, making changes when required
  • later run statements to commit any uncommitted work
  • and yet again execute yet another commit if there is still any uncommitted work

I’m sorry, but this is not transaction management, and it’s certainly not a formula for an application that will scale.