Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
SSMSE, Using Views, and SQL Server Express Blog
In this issue Michael Otey tells you how to get and use SQL Server Management Studio Express (SSMSE), how to create and use views, and where to find Microsoft's SQL Server Express blog.
Michael Otey
June 4, 2006
5 Min Read
Commentary
Using SQL Server Management Studio for Express
by Michael Otey
In this column, I tell you how to get SQL Server Management Studio Express (SSMSE) and how to use it to manage SQL Server 2005 Express. I also point out some of the differences between the standard SSMSE and the full-blown SQL Server Management Studio (SSMS) that comes with SQL Server 2005 Enterprise Edition, Standard Edition, and Workgroup Edition.
SSMSE is the graphical management tool that Microsoft provides for SQL Server 2005 Express. Having SSMSE as a companion product with SQL Server Express is one of the main advantages that SQL Server Express has over its predecessor, Microsoft Data Engine (MSDE). MSDE has no built-in management tools; instead you are limited to managing MSDE by using the not-so-user-friendly osql command-line tool or (if you're an existing SQL Server customer) you can use Enterprise Manager.
SSMSE isn't delivered as part of SQL Server 2005 Express Edition. There are three ways to get it: download SSMSE separately from the Microsoft site, receive SSMSE with the full- featured SQL Server 2005 Express Edition with Advanced Services, or download SSMSE with the SQL Server 2005 Express Edition Toolkit. You'll find links for these options at
http://msdn.microsoft.com/vstudio/express/sql/download/default.aspx
After you've downloaded and installed SSMSE, you can begin to manage your SQL Server Express instances. You start SSMSE by selecting Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio Express. When SSMSE starts, you'll see a Connect to Server dialog box. By default, the "Server name" drop- down box displays the name of the local SQL Server Express instance. If the display is blank and you used the default instance name during installation, you can connect to the local SQL Server Express instance by typing .SQLEXPRESS in the "Server name" drop-down box. You can also use SSMSE to connect to SQL Server 2005 by entering the SQL Server 2005 name in the "Server name" drop-down box, but SSMSE lacks some of the capabilities in SQL Server Management Studio--more on that later.
Next, select the authentication method you want to use to connect to the SQL Server Express instance. If you selected the default options during installation, you'll use Windows Authentication. Otherwise, you'll need to select SQL Server Authentication and enter the SQL Server username and password that you specified during the installation process or while you were using the SQLCMD tool. Click Connect to populate SSMSE with the server instance and database objects in the left pane. To explore the database objects, click the plus sign next to each object to expand it. Here are some brief descriptions of the objects:
Database object: lists user and system databases
Security object: lists SQL Server logins and roles
Server Objects: lists backup devices, linked servers (remote database systems) and server triggers
Replication node: lists local subscriptions
Management node: displays SQL Server logs and the Activity
To enter and run T-SQL statements, open the Query Editor by clicking New Query.
You'll find the look and feel of SSMSE to be similar to the version of SSMS that's included with SQL Server 2005, but there are differences. The full-featured version of SSMS lets you manage SQL Server 2005 features that aren't included in SQL Server Express. For example, SSMS lets you manage SQL Server Agent jobs and SQL Server replication. These features aren't included with SQL Server Express and SSMSE. However, SSMSE and SSMS share important capabilities, such as the ability to browse database objects and use Query Editor to execute T-SQL statements.
SQL Server Express Jump Start
Using Views
by Michael OteyIn this column, I drill down a little deeper into the world of SQL Server 2005 Express database objects and show you how to create and use views. First, what is a view? As its name suggests, a view is a customizable "window" that displays the contents of one or more selected tables or other views. The technical definition of a view is a virtual table created by the results of a query. This virtual table represents the data from one or more tables or views in an alternative way. Because a view is a virtual table (i.e., a query result), creating a view doesn't take up disk space.
You use views in a couple of common ways: First, views let you easily create a subset of the data contained in a single table, and second, you can use views to join multiple tables into a single table to simplify data access. In the April 19, 2006 Jump Start column (http://www.sqlmag.com/Article/ArticleID/50035/ sql_server_50035.html) I described how to create a Media table. The following code determines whether the vwMedia view exists, then creates a view called vwMedia that lists a subset of the columns in the Media Table:
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'vwMedia' AND type = 'U')
DROP TABLE Media
CREATE VIEW vwMedia
AS
SELECT MediaID, MediaType, MediaTitle
FROM MediaNow, run the following SELECT * statement to return all the columns (e.g., MediaID, MediaType, MediaTitle) from the view.
SELECT * FROM vwMedia
Check It Out
Microsoft's SQL Server 2005 Express Blog
by Michael OteyLooking for a place to learn more about SQL Server 2005 Express and keep up with some of the latest SQL Server Express news and events? If so, check out Microsoft's SQL Server Express blog at
http://blogs.msdn.com/sqlexpress/
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
