1	First Look: SQL Server Express and SQL Express Manager Presented by Rick Dobson to the CAUG on 4/28/2005
2	Background Resource for Presentation Full book addresses SQL Server Express Visual Basic Express Visual Web Developer Express Due as soon as August 2005 This presentation focuses exclusively on SQL Server Express SQL Express Manager SQL Server 2005 Innovations
3	Overview What is SQL Server Express? Demo and discuss installation and configuration of SQL Server Express What is SQL Express Manager? Demo use of SQL Express Manager for running T-SQL statements Demo of some easy-to-use T-SQL innovations with SQL Server 2005 that are available to SQL Server Express developers
4	What is SQL Server Express? An edition of SQL Server 2005 with the same core database engine features as other SQL Server 2005 editions Database engine specs Up to 4 GB size for database Up to 1 GB of memory 1 processor is supported No workload governor XCOPY deployment of databases SSE integrates tightly with Visual Basic Express Visual Web Developer Express Other Express suite components, such as Visual C# It’s FREE – think of it as MS’s MySQL warrior
5	Similarities of SSE to commercial SQL Server 2005 Editions Engine and T-SQL features are shared Queries, tables, views, stored procs, user-defined functions, triggers, and even linked servers Innovations: Data types: xml data type, max data types Improved ranking (better TOP keyword, new ROW_NUMBER, RANK, DENSE_RANK, and NTILE functions) Common Table Expressions for query re-use without objects PIVOT/UNPIVOT for cross-tabs and restoring relational format Full SQL Server Security Encryption by synchronous and asynchronous keys and certificates Schema ownership rather than direct user ownership of database objects Hosted CLR
6	Dissimilarities of SSE to commercial SQL Server 2005 Editions Enterprise features are not shared No hosting of Reporting Services, Integration Services (DTS), or Analysis Services No fail-over cluster or database snapshots No SQLMail No partitioned views SSE can participate in Replication, but only as a client Service Broker network, but only if a full version of SQL Server 2005 is present
7	SSE vs. MSDE SSE is a replacement for MSDE SSE enhancements relative to MSDE Installation wizard Simple Query Designer Tool Other management tools: SQL Computer Manager and SQL Surface Area Configuration Manager Compatibility with SQL Server 2005 instead of SQL SERVER 2000/7 XCOPY deployment of databases
8	Who is SSE For? Hobbyists and other non-professional developers Building solutions for personal satisfaction Building solutions based on business not programming experience (company-based Access developers) Business analysts who need to explore data for decision support (Access used to do this, now SSE can too) DBAs Free way to learn about a subset of SQL Server 2005 features Equips DBAs to help clients using SSE Professional developers who need a modern database Building solutions for small businesses and departments in larger organizations (independent Access developers) Developers needing embedded databases for a distributed solution
9	SSE Requirements and Installation Instructions Hardware Pentium 3 processor 256 MB memory Operating system Windows 2K, SP 4 Windows XP, SP 1 Windows 2003 .NET 2.0 Framework Download SSE Get download from http://lab.msdn.microsoft.com/express/sql/default.aspx Make sure that you have a .NET Framework version compatible with SSE version Invoke SSE Installation Wizard by running sqlexpr.exe
10	Demo: Start Installation Double-click sqlexpr.exe from desktop Accept license Sqlexp.exe checks for required components Sqlexp.exe checks for computer configuration requirements Clicking Next on Wizard Welcome screen View, save, print System Configuration Check Proceed through rest of wizard screens
11	Reminder: Mixed Mode and sa Account Clear the Hide advanced configurations check box on a previous screen to present Authentication Mode screen Select Mixed Mode on Authentication Mode screen Enter and confirm a password for the sa account
12	Demo: Confirm Install and Configure From Command Prompt type Sqlcmd –S instance name (e.g. .\sqlexpress) If login works Use sqlcmd session SELECT name FROM sys.databases GO Exit sqlcmd.exe Exit cmd.exe From Services applet For instance name, choose Startup type: Automatic Click Start in Service status (and wait for completion) Click OK Repeat above steps for SQL Browser Service
13	Demo: Confirm Install and Configure – Cont’d Open SQL Server Surface Area Configuration Click Surface Area Configuration for Services and Protocols Select Network Protocols Select Enable TCP/IP (and optionally Named Pipes protocol) Can stop and then start service from this tool to commit changes Instead of above, you can open SQL Computer Manager Expand Protocols for SQLEXPRESS (or other instance name) Right-click and enable TCP/IP (and optionally Named Pipes) Expand SQL Server 2005 Services Select SQL Server Right-click instance name Select Restart Note: While MSDE gave you no installation help, SSE gives you a wizard and two graphical tools
14	Optional Installation Steps Download Abridged BOL for SSE Download full BOL Download sample databases Particularly AdventureWorks; it has design features that are important for learning SQL Server 2005, including SSE Northwind and pubs for working with familiar databases
15	SQL Express Manager Overview Connects to SQL Server Express instances Other SQL Server 2005 instances SQL Server 2000/7 instances Connects to one instance at a time, but allows multiple concurrent sessions Runs SELECT Any other T-SQL statements Explore objects On a server instance Drills down within a database Timing for SSE and SQL Express SQL Express may be replaced by a SSMS Lite – TBD Timeframe for release of SSE is 2^nd half of 2005 TBD will be resolved before then
16	Express Manager UI Menu Windows Object Explorer Query Editor – like Query Analyzer but simpler Tabs for separate scripts Results pane for showing result sets Messages pane for feedback about queries Toolbars Main toolbar Window-based toolbars Wizard for new database – maybe more wizards to come
17	Express Manager UI – Cont’d Menu commands File: Change connection; new query; save query; open previously saved query Edit: typical cut, copy, paste, undo, redo, find, and replace View: select Object Explorer or Workspace (Query Editor); control font size Query: start new query, results to: grid/text/file, execute, parse
18	Express Manager UI – Cont’d Toolbars Subset of menu features for quick access Main toolbar New query open a previously saved file execute script on current tab or highlighted code parse script on current tab or highlighted code save script as file change font size to one of five sizes in a round-the-clock rotation
19	Express Manager UI – Cont’d Object Explorer toolbar Change connection Refresh Object Explorer with the server for the current connection Query Editor toolbar Drop-down box to select a current database context Present query results In text format In grid format Saved to a file Fourth button control toggles visibility of query results
20	Express Manager UI – Cont’d Object Explorer lets you Browse objects within a server instance Databases Logins Examine database objects, including Tables Views Stored procedures User-defined functions User accounts within a database Invoke a wizard to create a new database Rename and delete databases
21	Express Manager UI – Cont’d Query Editor window lets you Write and run T-SQL queries (SELECT statements) Data definition statements, such as CREATE TABLE or CREATE TRIGGER Switch between query tabs for different query scripts Save Initially Re-save an open query Open a previously saved query
22	Demo: Connect with Windows or SQL Server Authentication Name the instance to which you want to connect Designate Windows or SQL Server authentication Instruct Express Manager to pass connection info to server instance
23	Demo: Browse All Databases and Drill Down on AdventureWorks Database Below server instance name in Object Explorer Expand Databases to view database names on server Expand Security folder to open Logins folders with SQL Server logins Expand any database name, such as AdventureWorks, to view folders for Tables, Views, Programmability, Security
24	Demo: Drill Down on Tables Notice tables in AdventureWorks database have two-part name: schema_name.table_name You must reference table by both parts for SSE to know which table you reference The use of schema names, instead of owner names, facilitates individuals leaving an organization without reassignment of the owner of a table Expand Columns folder within a table, such as HumanResources.Employee, to show column names Indexes folder shows names of indexes for a table
25	Demo: Drill Down on Views, Programmability, and Security Column folder for a specific views shows the names of columns returned by a view Programmability folder shows names for Stored procs Parameters folder for a specific stored proc show its parameters’ names and data types User-defined functions Note: Views and stored procs together are approximately the same as queries in Access Security folder contains Users folder with the user account names for that folder
26	Demo: Simple SELECT queries from a Single Database Object Open the SalesTrackingScripts.sql Run the query to return sales tracking data by SalesPersonID from the SalesPerson table Notice the use of the Sales schema name to qualify the table name in the FROM clause Removing the Sales schema qualifier for SalesPerson causes an error What if you want to add sales person name, you can use the vSalesPerson view in the Sales schema Use Object Explorer to browse the column names in the view Update the SELECT statement from the SalesPerson table to use the vSalesPerson view Run scripts with Results to Grid, Text, and File; verify file output to file with Notepad
27	Triggers are Cool They act like event procedures on tables and views for inserts, updates, and deletes Trigger types Earliest type of trigger is an “after” trigger that lets you roll back in case the insert, update, or delete to a table is not what you want Newer type of trigger is an “instead of” trigger that runs before, not after, an insert, update, delete to a table or a view Triggers Can work directly on tables or views Therefore, triggers can protect data better than Access event procedures which generally run from forms and their controls
28	Demo: Do Triggers Show in Object Explorer Open DoesSQLEMSupportTriggersBrowsing.sql List triggers in the ProSSEAppsCh04 (or any other convenient database There shouldn’t be any (or at least the one we use) at first Create two database objects A table named TrigrTest A trigger for the table named trgUpdateTrigrTest The trigger Rolls back all updates and deletes But does not impact inserts Also confirm trgUpdateTrigrTest is in the database It does not show in Object Explorer But you can verify its existence through the sys.triggers view
29	Demo: Do Triggers Show in Object Explorer – Cont’d Verify table and trigger TrigrTest works for inserts trgUpdateTrigrTest also works to block deletes Drop trgUpdateTrigrTest After dropping trigger notice that you can Delete rows from TrigrTest Finally, drop TrigrTest
30	Demo: New Database Wizard Right-click Databases in Object Explorer and choose New Database New Database tab includes Database name text box Accept default name of Database_1 (or some other number) Override with a meaningful name Database location text box By default database files go in the Data folder of the SQL Server instance of the Program Files folder for Microsoft SQL Server Can manually type a new path Browse button Can use browse button to graphically specify a new path on local computer OK, Cancel, and Apply buttons Commit new database (OK, Apply) Cancel new database
31	Demo: New Database Wizard – Cont’d Open Database_1TableTrigger.sql Similar to DoesSQLEMSupportTriggerBrowsing.sql with replacement of USE ProSSEAppsCh04 USE Database_1 Browse Database_1 in Object Explorer to verify it is empty Run code to create a table and a trigger Verify creation of objects Browse Database_1 in Object Explorer to confirm creation of TrigrTest table Run script for sys.triggers to confirm creation of trgUpdateTrigrTest trigger Graphically rename database to Database_1b New name in Object Explorer New output from SELECT name FROM sys.databases Graphically delete Database_1b database Notice it disappears from Object explorer Not reported by SELECT name FROM sys.databases
32	TOP Keyword TOP keyword for a SELECT statement causes statement to save the result with all rows in a temporary working table Then SSE uses TOP keyword argument to select a set of rows from the top of the temporary working table at Compile time Run time Ability to set TOP keyword at run time is a SQL Server 2005 innovation
33	Demo: TOP Keyword Open ReturnFixedOrVariableTopRows.sql First SELECT statement returns a fixed number of rows Second SELECT statement Returns a variable number of rows based on the value of a local variable Change value to update returned rows Can use parameter in a stored procedure, instead of local variable in a script, to dynamically set number of rows to return at run time
34	Ranking, Ordering Functions ROW_NUMBER, RANK, DENSE_RANK, and NTILE permit you to Rank and order rows over whole result set based on an ORDER BY clause within the function Rank and order rows within partitions of a result set Summary of functions ROW_NUMBER assigns sequential numbers to successive rows – ignores ties (1,2,3,4,5,6,7) RANK assigns rank numbers, but they are spread out after ties (1,1,1,4,5,5,7) DENSE_RANK assigns rank numbers, but they are not spread out after ties (1,1,1,2,3,3,4) NTILE Assigns number to indicate N^th tile position of row in result set Must designate N as argument for function
35	Demo: Rank vs. Dense_Rank Open RankVsDenseRank.sql First sample contrasts RANK and DENSE_RANK functions Both functions rely on the same descending SalesQuota order RANK function numbers are spread out over 17 rows (with nulls at bottom) DENSE_RANK function numbers are not spread out Aside from function name notice syntax is nearly identical between the two functions Second sample Illustrates syntax for Setting a partition Using two ranking variables Shows rank numbers nested within partitions based on TerritoryGroup
36	Summary SSE is a light-weight, modern database that is Compatible with SQL Server 2005 Has traditional T-SQL capabilities Offers T-SQL innovations Has a graphical query design tool Compatible with other Express suite components, and it can also be used by Visual Studio 2005 Is FREE!!!! If you want to use it with SQL Express Manager, you will get your best value by getting a good grasp of T-SQL