The SQL Server Management Studio is truly an Integrated Development Environment. There are utilities, tools and graphical designers available within SSMS which are useful to audiences of multiple levels. The graphical designers are a set of utilities, which allow one to perform basic SQL Server development without writing a single line of T-SQL code.
In this tutorial, I will provide a basic introduction to these designers and also focus on how one can use these tutorials to enter the next level – that envious ability to write T-SQL code for almost anything that needs to be done in SQL Server.
Table Designer
Modifying an existing table
Let’s begin with something simple – modifying an existing table, using nothing but the graphical features of the SSMS table designer. Here are the steps:
- Using the Object Explorer, navigate to the database whose table needs to be modified
- Expand the database node, and navigate to the table requiring modifications from the Table node
- Right-click on the required table, and choose “Design”
This opens the table designer. The view has two parts:
- Basic editing of columns, which allows one to define:
- The column name
- Data-type
- Null-ability
- Configure Keys
- Configure Indexes & relationships
- Advanced editing of column properties
- Column Collation
- Computed Column specification
- Description
- Read only property fields, like:
- Deterministic
- Is Indexable
- Is Sparse
To extend the length of the “Job Title” column from 50 to 60, simply click in the “Data Type” column and make the necessary modifications. Simply click “Save” on the toolbar to commit the changes to the database.
| Changing an existing column |
Saving the change |
 |
 |
Creating a new table
Creating a new table is just as simple as modifying an existing one. The steps involved are also similar:
- Using the Object Explorer, navigate to the database whose table needs to be modified
- Expand the database node, and navigate to the Table node
- Right click and choose “New Table”
An empty table designer window opens. Now, define create the new table as required, and save the changes.
| Define the columns |
 |
| Define the primary keys |
 |
| Defined primary key |
 |
| Define the identify column properties |
 |
NOTE that the options to change the table name and the associated schema are available in the Properties window, which is typically available to the right of the Table Designer. This window allows us to change the following (includes, but not limited to):
- Table Name
- Schema Name
- Lock Escalation
- File-group
- Description
Generating T-SQL Code from the designers
Here’s probably the one thing I would recommend each and every one working with the table designer to do – use it to learn how to write production-quality T-SQL code.
We made a change above wherein we changed the data-type of one of the columns. As soon as we make the modification, notice that a small icon becomes enabled on the toolbar. This icon enables a user to generate the T-SQL code for the change. The script can then save the script to a text file or copy-paste the change to a query editor window.
| “Generate Change Script” icon |
T-SQL script for the associated changes |
 |
 |
Controlling Table Designer options
From an administration perspective, one might want to control the level of changes that can be made to the database. For example, an administrator may want to restrict changes that would require a user to recreate the table, pump data in from the old table and then discard the old table. The table designer allows the administrator to configure this option as follows:
- Go to Tools –> Options
- Navigate to “Designers”
- Drill down to “Table and Database designers”
- Set the options required
If we attempt to perform the same operation (changing the size of the “Job title” field as above), the user would encounter the following message indicating that the change is not allowed.
Query Designer
If you have an existing query in SSMS, just select the entire query, and use the Ctrl+Alt+Q shortcut key or use the Query –> Design Query in Editor menu option. You will see that your query is now available in the Query Designer, ready for some key-stroke-less, graphical editing.
Let’s begin with a basic query. Notice that the query is not formatted to our liking.
SELECT Employee.BusinessEntityID, Person.LastName, Person.FirstName,EmployeeDepartmentHistory.ShiftID,Department.GroupName,Department.Name
from HumanResources.Employee
INNER JOIN HumanResources.EmployeeDepartmentHistory ON Employee.BusinessEntityID = EmployeeDepartmentHistory.BusinessEntityID INNER JOIN HumanResources.Department ON EmployeeDepartmentHistory.DepartmentID = Department.DepartmentID
INNER JOIN Person.Person ON Employee.BusinessEntityID = Person.BusinessEntityID
order by Person.LastName, Person.FirstName
After selecting the query and pressing Ctrl+Alt+Q, the Query designer launches, which allows us to add/remove columns, define column aliases and format the query.
Click on “OK” and the user is returned to the SSMS Query Editor, with the query changes made. The updated query looks like:
SELECT Employee.BusinessEntityID, Person.Person.LastName, Person.Person.FirstName, HumanResources.EmployeeDepartmentHistory.ShiftID,
HumanResources.Department.GroupName AS [Department Group], HumanResources.Department.Name AS [Department Name], Employee.Gender
FROM HumanResources.Employee AS Employee INNER JOIN
HumanResources.EmployeeDepartmentHistory ON Employee.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID INNER JOIN
HumanResources.Department ON HumanResources.EmployeeDepartmentHistory.DepartmentID = HumanResources.Department.DepartmentID INNER JOIN
Person.Person ON Employee.BusinessEntityID = Person.Person.BusinessEntityID
ORDER BY Person.Person.LastName, Person.Person.FirstName
The query designer is not only suitable for existing queries, but can also be used in a similar fashion for new queries, which you can explore as an exercise to this tutorial.
Template Explorer
Per Microsoft SQL Server Books On Line, Template Explorer is a component in SQL Server Management Studio that presents templates to quickly construct code in the Code Explorer. The templates are grouped by the type of code being created, which in turn makes finding the right templates blazingly fast.
Navigating through the Template Explorer
To launch the Template Explorer launch the SQL Server Management Studio and simply use the Ctrl+Alt+T keyboard combination or go to View->Template Explorer.
Launching the Template Explorer
SQL Server provides the user the option to choose from any of the 3 families of templates:
- SQL Server
- Analysis Services
- SQL Compact
NOTE: Templates are stored in the user’s Documents and Settings folder when the Template Explorer is opened for the first time:
- On SQL Server 2008, the templates are found under %APPDATA%MicrosoftMicrosoft SQL Server100ToolsShellTemplates
- On the upcoming SQL Server 2012 (SQL 11/”Denali”), the templates are found under %APPDATA%MicrosoftSQL Server Management Studio11.0Templates
SQL Server Templates
For the purposes of this example, we will attempt to create a new stored procedure using SQL Server templates. All one needs to do is to navigate out to “Stored Procedures” folder in the Template Explorer and double-click on “Create Procedure Basic Template”.
The first thing that strikes us is that they contain parameters to help us customize the code. Per BOL, template parameter definitions use this format <parameter_name, data_type, value>, where:
- parameter_name is the name of the parameter in the script
- data_type is the data type of the parameter
- value is the value that is to replace every occurrence of the parameter in the script
Replacing Parameters in SQL Server Templates
Replacing the parameters in a SQL Server Template to create an almost fully-functional script is a simple three step process, as under:
- On the Query menu, click Specify Values for Template Parameters
- In the Specify Values for Template Parameters dialog box, the Values column contains suggested value for the parameter. Accept the value or replace it with a new value as required, and then click OK to close the Replace Template Parameters dialog box and modify the script in the query editor
- Modify the query to add whatever business logic necessary
| Launching the “Specify Values for Template Parameters” dialog |
Replacing Template Parameters |
 |
 |
Here’s the script with the parameters replaced. The SELECT statement in the body of the stored procedure has been replaced with business logic:
-- =============================================
-- Create basic stored procedure template
-- =============================================
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'[Sales]'
AND SPECIFIC_NAME = N'[proc_AddNumbers]'
)
DROP PROCEDURE [Sales].[proc_AddNumbers]
GO
CREATE PROCEDURE [Sales].[proc_AddNumbers]
@p1 int = 0,
@p2 int = 0
AS
--Template Default
--SELECT @p1, @p2
SELECT @p1+@p2 AS 'Result'
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE [Sales].[proc_AddNumbers] 1, 2
GO
As you can see, our basic script is now ready for use in just 3 simple steps.
Interesting Reading…
Aaron Bertrand (blogs | twitter) believes that using the designer is a bad habit (http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-the-visual-designers.aspx). I agree with Aaron, however, share the view that these designers are great for the developers who would want to study how to write production quality T-SQL code. Once a certain comfort level is achieved in writing T-SQL code, the reader would be motivated to explore options to improve the code and subsequently reduce the dependency on the designers.
Practice Exercises…
It’s time to practice! Today’s exercises may take some more time as it involves playing around with the various designers to your heart’s content:
- Create new database objects as mentioned here: http://msdn.microsoft.com/en-us/library/hh272695(v=VS.103).aspx
- Create a custom template to confirm to the coding standards of your organization
- Deploy the newly created custom template
- Create a test database as below:
CREATE DATABASE foo
GO
USE foo
GO
CREATE TABLE MyTbl (MyName CHAR(4030))
GO
INSERT INTO MyTbl
SELECT REPLICATE('a',4030)
GO
- Increase the length of the column MyTbl.MyName to 4031 characters. Study the T-SQL script generated by table designer
- Can you figure out why the T-SQL script was generated in this way?
Further reading
- SQL Server – SSMS – Table Designer v/s ALTER TABLE – Best Practices – Msg 1701 – Creating or altering table failed….This exceeds the maximum allowable table row size of 8060 bytes
- SSMS – Query Designer – Ad-hoc query writing/editing made simple
- Template Explorer – Underappreciated features of Microsoft SQL Server
- Template Explorer – Changes & Template updates in SQL 11 (Code Named: “Denali”) CTP01 – comparison with SQL Server 2008
- Template Explorer – Deploying custom templates in SSMS – VTD Webcast feedback question – Underappreciated Features of Microsoft SQL Server
Until we meet next time,
Be courteous. Drive responsibly.
Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql
SQLTwins by Nakul Vachhrajani 