Tag Archives: SQL server management studio

Computed Columns in SQL Server Management Studio (SSMS)

Formula property in computed columns

Computed columns in SQL Server Management Studio (SSMS) allow you to configure a column within a table that automatically calculates a value based on an expression that can include references to other columns. For example, in the Course table you could have a PricePerUnit and a Units column, plus a Total column, as shown in the Figure below, where the Total column has the following Formula property:

If a value in either the PricePerUnit or the Units column changes, the Total column is automatically recomputed. However, by default this value is not actually storedit is calculated and returned when needed, as in a query or view. The calculated value is stored on disk only if you index the column, which is what happens if you set Is Persisted to Yes.

TIP: When you modify a table’s design with Management Studio, such as to add the formula for the Total computed column, and then click the Generate Change Script button on the left side of the toolbar, you’ll see the T-SQL that Management Studio uses to make the change. This T-SQL does not represent the most efficient way of making the change. (You’ll need to uncheck the Prevent saving changes that require the table to be recreated in the Tools|Options dialog box in the Designers|Table and Database Designers page. This option is checked by default.)

For every change or set of changes that you make, Management Studio creates a new table, copies any data in the old table into the new one, deletes the old table, and then renames the new one with the same name as the old. This is not necessary it is just an easy way for Management Studio to avoid creating different scripts for every kind of change.

When you write the T-SQL yourself, you can use an ALTER TABLE statement that makes only the necessary change or changes to the existing table without creating a new table. That can make a big difference in the time required to change a table that contains lots of data.

Save the change to the Course table. You’ll get a Save dialog box that verifies that you want to change the tables in the database, as shown in the Figure below. Select Yes to make the change.

verifying you want to change table

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Configuring Management Studio course written by expert Don Kiely.

SQL Server Management Studio: Displaying Multiple Code Windows

ldn-expertdkiely

You have several options for viewing and manipulating multiple code windows.

When you have two or more queries open, you can right-click the title bar of the Query Editor and choose either New Horizontal Tab Group (see the Figure below) or New Vertical Tab Group. The same options are available from the main Window menu in Management Studio.

If you select a new horizontal tab group, you’ll see something like the Figure below. Note that each window still can maintain an independent connection to an instance of SQL Server; you can see this information in the status bar at the bottom of each query editor window. Each of the tab groups you create can contain multiple query editor windows.

The Query Editor in a horizontal tab group

To move the active window to another tab group, choose Window|Move to Next Tab Group or right-click the title bar and choose Move to Next Tab Group. The option in either case might be Move to Previous Tab Group if a window in the second tab group is active. Or, you can simply drag the window to another group.

There are three ways to un-split the window:

  • If the window you move is the last one in a tab group, the empty tab group will automatically close.
  • Close all of the query editor windows in a tab group.
  • Drag all of the query editor windows from a tab group to another tab group.

TIP: To maximize the Query Editor window, press SHIFT+ALT+ENTER. Press it again to switch back to Normal view. This keyboard shortcut works with any document window.

If you select the Window|Close All Documents option from the main menu, all documents and all tab groups will close. You’ll be prompted to save any files in windows with unsaved changes. Management Studio has your back!

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Configuring Management Studio course written by expert Don Kiely.

SQL Server Management Studio: Displaying and Filtering Objects

sql production schema

SQL Server Management Studio displays server and database information in Object Explorer, which presents information about database objects in a treeview. In addition to displaying SQL Server Database Engine objects, Object Explorer also displays Analysis Services, Reporting Services, Integration Services, and other objects. You can also use it to administer SQL Server Security, SQL Server Agent, Replication, and Database Mail. It provides you with deep insight into just about every corner of an instance of SQL Server and all its services.

The Object Explorer displays information in a hierarchical treeview for all servers to which it connects. It populates the treeview on demand when you expand a node. You can double-click a node to expand it, and click the Stop button to stop the expansion.

Displaying and Filtering Objects

Object Explorer can display up to 65,536 objects. If you want to view additional objects, you need to close some nodes or apply a filter to reduce the number of displayed objects. To filter the view, select the parent node that you want to filter, then right-click and choose Filter|Filter Settings. For example, in the Tables node you can filter by Name, Schema, Owner, or Creation Date and you can choose which operator to use for each, as shown in the Figure below for the AdventureWorks2012 database. Click OK to display only tables in the Production schema.

When you filter a list of items, Object Explorer displays that information in the node name, as shown in the Figure below. This way, you don’t forget that it is filtered when you’re looking for an object that the filter excluded.

filtered view in object explorer

To remove the filter, right-click the node where you applied the filter and choose Filter|Remove Filter. Now all the database tables again appear in the Tables node.

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Configuring Management Studio course written by expert Don Kiely.