SQL Saturday #40 - DB Dev T-SQL 0-60 - Code Crackin #29

SQL Saturday #40 - DB Dev T-SQL 0-60 - Code Crackin #29

Hey Now Everybody,

SQL Saturday #40 on July 31st 2010, #sqlsat40
I'll be presenting a Database Developement session named T-SQL 0-60 (from standing to sprinting) a beginner's level 70 minutes session to cover some stellar content. This session will include a good mixture of high level concepts along with detailed examples of T-SQL code. We are going to use two simplifed tables from the ASP.NET Membership database (a real world db not northwind) that will work with to demonstrate many skills. There is a script in this post that you may use & I'll use as our examples. The session will include a little swag give away of a recorded episode of DotNetRocks! & RunAs Radio. Here is a link to my slide deck.
This Database Development T-SQL 0-60 session will be broken up into four major session:
1. IDE SSMS & Queries - Fundamentals including management studio, objects & queries
2. Design View vs. Query Editor Window - Comparing GUI vs. Script to accomplish same tasks
3. Stored Procedures - In depth examples of stored procedures
4. Sky is the Limit - Additional query techniques

Here is an image of the session:

Along with all the stellar content the people whe are going to be there should be fun relax with & talk technology:

Kevin Boles @TheSQLGuru
Dmitri Korotkevitch
Mike Davis @MikeDavisSQL
Ronald Dameron @RonDBA
Brent Ozar @BrentO
Devin Knight
Jack Corbett @uncleBiguns
Jorge Segarra @sqlChicken
Rodney Landrum @rodney_landrum free SQL ebook
Timothy Ford @sqlagentman
Aaron Nelson @sqlvariant
Troy Gallant @gratefuldba
Adam Jorgensen @adam_jorgensen
Argenis Fernandez @afernandez
David Corrales @SAPIEN
David Levy @Dave_Levy
Don Gabor .com
Jose Chinchilla @sqljoe
Patrick LeBlanc @patrickdba
Rafael Salas @rafsalas
Timothy McAliley @SysFrameWorks
Pam Shaw @pamshaw
Hector Hernandez
Maximo Trinidad @maxtrinidad
Jared Nielsen @jarednielsen
Sam Abraham @wildturtle21
Scott Klein
Herve Roggero @hroggero
Shervin Shakibi @shervsherv
Joe Devfish Healy @devfish
Telmo Sampaio @telmosampaio
Michael Mollenhour
Elijah Baker

Here are some details on my session:
1.1. IDE SSMS SQL Server Management Studio
1.1.a. Installation of SQL Server 2008
1.1.b. Connoting to Server
1.1.c. Object Explorer
1.1.d. Query Editor Window
1.1.e Design View
1.1.f Best SSMS Hotkeys
1.2. Queries (Common Commands)
1.2.a. Syntax
1.2.b. Comments
1.2.c. Data Types
1.2.d. Create
1.2.e. Select
1.2.f. Insert
1.2.g. Where
1.2.h. Update
1.2.i. Delete
1.2.j. AND
1.2.k. OR
1.2.l. Order By
1.2.m. Group By
1.2.n. Count
1.2.o. Joins
1.2.p Views
2.1. Design View
2.1.a Creating Tables
2.1.b Modifying Tables
2.1.c Building Queries
2.2. Query Editor Window
2.2,a General
2.2.b Execute & Parse
2.2.c Highlighting Text
2.2.d Scripting
3. Stored Procedures
a. Create
b. Database Definition (Use)
c. Comments
d. Common Settings
e. Begin End
f. Alter / Modify
g. Drop
h. Rollback
i. Grant Permission
j. Execute SP
k. Parameters
4. T-SQL’s Sky is the Limit
a. Alias
b. Scripting
c. Commands
d. Temp Tables
e. Select Into
f. Nested Select
g. Cursors
h. Case vs. If
i. Dependencies
j. Grants
k. Schemas
l. Template Explorer
m. Datetime
n. Try Catch Blocks
o -Execution Plan
p.1 XML
p.2 XML PATH
p.3 XML PATH SHOWPLAN
Q Third Party tools
q.1 Red Gate SQL Search
q.2 SQL Format Snippet Inserter (Google Code)
q.3 SQL Beauty
R. Reference MSDN
S MCTS 70-433
s.1 Skills Measured
s.2 Programming Objects
s.3 Query Fundamentals
s.4 XML Data

A couple sessions I’m looking forward to are:

Troy Gallant - Introduction to Transactional Replication
Brent Ozar - BLITZ! One Hour SQL Server Takeovers
David Corrales - Sneak Preview: SAPIEN's Visual PowerShell 2011
Dmitri Korotkevitch - Database design with performance in mind
Kevin Boles - Advanced TSQL Solutions

  -- Catto Database Development DB Script
-- This is a script from www.ChrisCatto.com for SQL Saturday's Database Development T-SQL session
--1.1. IDE SSMS SQL Server Management Studio
--     1.1.a. Installation
--     1.1.b. Connoting to Server
--     1.1.c. Object Explorer
--     1.1.d. Query Editor Window
--     1.1.e Design View
--1.2. Queries (Common Commands)
--     1.2.a. Syntax
--     1.2.b. Comments
--     1.2.c. Data Types
--     1.2.d. Create
--        1.2.d.1 db
--        1.2.d.2 table Create [catto_aspnet_Users]
--        1.2.d.3 table Add Clustered Index
--     1.2.e. Select
--     1.2.f. Insert
--     1.2.g. Where
--     1.2.h. Update
--     1.2.i. Delete
--     1.2.j. AND
--     1.2.k. OR
--     1.2.l. Order By
--     1.2.m. Group By
--     1.2.n. Count
--     1.2.o. Joins
-------------------------------------------------------
--     1.2.d.1 Create
--CREATE DATABASE [catto_dbtest6]
--GO
--DROP Database [catto_dbtest4]
--GO
--use [catto_sql_sat]
--GO
------------------------------------------------------
---- 1 Create Table [catto_aspnet_Users]
CREATE TABLE [catto_aspnet_Users]
(
[UserId] [int] NOT NULL IDENTITY
, [active] [bit] Not NULL DEFAULT 1
, [UserName] [nvarchar](256) NULL
)
GO
-- 1.2.d.3 [PK_catto_aspnet_Users]
ALTER TABLE [catto_aspnet_Users]
ADD  CONSTRAINT [PK_catto_aspnet_Users] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)
ON [PRIMARY]
GO
----------------------------------------
-- 1.2.d.4 table [catto_aspnet_Membership]
--drop TABLE [dbo].[catto_aspnet_Membership]
CREATE TABLE [dbo].[catto_aspnet_Membership]
(
[MembershipID] [int] IDENTITY(1,1) NOT NULL
, [active] [bit] Not NULL DEFAULT 1
, [UserId] [int] NOT NULL
, [Password] [nvarchar](128) NOT NULL
, [Email] [nvarchar](256) NULL
, [IsApproved] [bit] NOT NULL
, [CreateDate] [datetime] NOT NULL
,
CONSTRAINT [PK_catto_aspnet_Membership] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [nci_userid] ON [dbo].[catto_aspnet_Membership]
(
[UserId] ASC
)
ON [PRIMARY]
GO
----1.2.e. Select
select * from [catto_aspnet_Users]
--drop table [catto_aspnet_Users_4]
---- Create Table & PK in one script
--CREATE TABLE [dbo].[catto_aspnet_Users](
--    [UserId] [int] IDENTITY(1,1) NOT NULL,
--    [active] [bit] NOT NULL default 1,
--    [UserName] [nvarchar](256) NULL,
-- CONSTRAINT [PK_catto_aspnet_Users] PRIMARY KEY CLUSTERED
--(
--    [UserId] ASC
--)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
--) ON [PRIMARY]
--1.2.f. Insert
-- Insert Record
INSERT INTO catto_aspnet_Users
       (UserName)
VALUES ('Catto')
INSERT INTO catto_aspnet_Users
       (UserName)
VALUES ('CattoOrange')
INSERT INTO catto_aspnet_Users
       (UserName)
VALUES ('CattoPotato')
select * from catto_aspnet_Users
-----------------------------------------
--1.2.g.  where
Select [UserId]
,[active]
,[UserName]
From [catto_aspnet_Users]
where [UserName] = 'Catto'
-------------------------------------------------
--1.2.h. Update
UPDATE catto_aspnet_Users
SET       UserName = 'CattoTomato'
WHERE (UserId = 1)
Select *
from catto_aspnet_Users
where (UserId = 1)
--------------------------------------------------
--1.2.i. Delete
DELETE FROM catto_aspnet_Users
WHERE (UserId = 1)
Select *
from catto_aspnet_Users
where (UserId = 1)

INSERT INTO catto_aspnet_Users
       (UserName)
VALUES ('Catto')
--------------------------------------------------
--1.2.j. And
Select *
from catto_aspnet_Users
where (UserId = 2)
AND  (UserName = 'CattoOrange')
-- Highlighting
--     Text
--NEED INSTALL LINK
------------------------------------
--1.2.k. OR
Select *
from catto_aspnet_Users
where (UserId = 2)
OR  UserName = 'CattoPotato'
------------------------------------
--1.2.l. order by
Select *
from catto_aspnet_Users
order by UserId
Select *
from catto_aspnet_Users
order by UserId desc
------------------------------------
--1.2.m. Group by
SELECT active, UserId, UserName
FROM  catto_aspnet_Users
GROUP BY active, UserId, UserName
------------------------------------
--1.2.n. Count
SELECT COUNT(UserId) AS totalCount
FROM  catto_aspnet_Users
-- Count & Group by
SELECT active AS activeBit, COUNT(UserId) AS totalCount
FROM  catto_aspnet_Users
where active = 1
GROUP BY active
------------------------------------
--1.2.o. -- Joins
INSERT INTO catto_aspnet_Membership
       (active, UserId, Password, Email, IsApproved, CreateDate)
VALUES (1, 2, 'blahEncrypted', 'catto@domain.com', 1, CONVERT(DATETIME, '2010-08-08 00:00:00', 102))
select * from catto_aspnet_Membership
select *
from catto_aspnet_Users
INNER JOIN catto_aspnet_Membership
ON catto_aspnet_Users.UserId = catto_aspnet_Membership.UserId
GO
------------------------------------
--1.2.p Views
CREATE VIEW [dbo].[vw_catto_aspnet_Users]
AS SELECT [UserId]
, [UserName]
FROM catto_aspnet_Users
GO
-- 1.2.q selecting from View
-- Selecting from View
SELECT [UserId]
,[UserName]
FROM [vw_catto_aspnet_Users]
--------------------------------------------------------------------------------
-- 2 Design View vs. Query Editor
--------------------------------------------------------------------------------
--2.1 Desgin View Vs. Query Editor Window
--2.1. Design View
--   2.1.a Creating Tables
--   2.1.b Modifying Tables
--   2.1.c Building Queries
--2.2. Query Editor Window
--   2.2,a General
--   2.2.b Execute & Parse
--   2.2.c Highlighting Text
--   2.2.d Scripting
--------------------------
--USE [ccatto_aspnetdb]
--GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Roles_catto]
(
[aspnet_roles_id] [numeric] NOT NULL identity,
[RoleId] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED
(
[RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--USE [catto_dbtest4]
--GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Roles_catto](
[aspnet_roles_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[roles] [nvarchar](50) NULL,
[RoleId] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
CONSTRAINT [PK_roles_catto_id] PRIMARY KEY CLUSTERED
(
[aspnet_roles_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [nci_roleid_catto] ON [dbo].[aspnet_Roles_catto]
(
[roles] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE [aspnet_Roles_catto]
ADD application_id int null
GO
select * from [aspnet_Roles_catto]
--drop table [aspnet_Roles_catto]
-- now we will create a table in design view
INSERT
INTO            aspnet_Roles_catto(role_id, role, description, app_id)
VALUES     (1, 'admin_role', 'admin role most power', 1)
INSERT
INTO            aspnet_Roles_catto(role_id, role, description, app_id)
VALUES     (2, 'user_pawn_role', 'pawn role least power', 1)
select * from [aspnet_Roles_catto]
-- in design view lets insert values
SELECT     role_id, role, description, app_id
FROM         aspnet_Roles_catto
WHERE     (app_id = 1) and  (role_id = 1)

SELECT     COUNT(aspnet_roles_catto_id) AS TotalRecords
FROM         aspnet_Roles_catto
GO
---------------------------------
--3. Stored Procedures
-- a. Create
-- b. Database Definition (Use)
-- c. Comments
-- d. Common Settings
-- e. Begin End
-- f. Alter / Modify
-- g. Drop
-- h. Rollback
-- i. Grant Permission
-- j. Execute SP
-- k. Parameters
-- 3.a. Create
CREATE Procedure p_aspnet_Users_Get
--ALTER Procedure p_aspnet_Users_Get
AS
BEGIN
Select UserName from catto_aspnet_Users
END
GO
---------------------------------------------------
-- 3.b Use
--USE catto_dbtest4
--GO
-- 3.c Comments
-- 3.d Common Settings
-- 3.e Begin End
-- 3.f Alter
--CREATE Procedure p_aspnet_Users_Get
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure p_aspnet_Users_Get
AS
BEGIN
Select UserName
from catto_aspnet_Users
where active = 1
END
-- 3.g DROP
DROP PROCEDURE [dbo].[p_aspnet_Users_Get]
GO
Create  Procedure p_aspnet_Users_Get
AS
BEGIN
Select UserName
from catto_aspnet_Users
where active = 1
END
GO
-- 3.h ROLLBACKS
Alter Procedure p_aspnet_Users_Get
AS
BEGIN
Select UserName
from catto_aspnet_Users
--where active = 1
END
--------------------------------------------------------------------------------
-- Rollback
--------------------------------------------------------------------------------
--Alter Procedure p_aspnet_Users_Get
--AS
--BEGIN
--    Select UserName
--    from catto_aspnet_Users
--    where active = 1
--END
GO
--3.i GRANT
--3. i. Grant Permission
--Grant Execute on p_aspnet_Users_Get
--    to newUser
--GO
-- 3.j execute
Execute p_aspnet_Users_Get
GO
-- 3.k @Parameters
CREATE PROCEDURE [dbo].[p_aspnet_Membership_CreateUser]
--Alter Procedure [p_aspnet_Membership_CreateUser]
(
@UserName    nvarchar(256)
, @Password   nvarchar(128)
, @Email      nvarchar(256)
, @IsApproved bit
)
AS
BEGIN
declare @CreateDate datetime
declare @NewUserId int
SET @CreateDate = GETDATE()
INSERT INTO catto_aspnet_Users
       (UserName)
VALUES (@UserName)
set  @NewUserId =
( select top 1 @@IDENTITY from catto_aspnet_Users)
INSERT INTO catto_aspnet_Membership
(    active
, UserId
, Password
, Email
, IsApproved
, CreateDate
)
VALUES
(    1
, @NewUserId
, @Password
, @Email
, 1
, @CreateDate
)
END
GO
--------------------------------------------------
--- Check latest records in tables
Select top 1 * from catto_aspnet_Membership order by MembershipID desc
select top 1 * from catto_aspnet_Users order by UserId desc
-----------------------------------------------------
-- j. Execute SP
DECLARE    @return_value int
EXEC    @return_value = [dbo].[p_aspnet_Membership_CreateUser]
@UserName = 'ccatto',
@Password = 'blah',
@Email = 'cattoemail@chriscatto.com',
@IsApproved = 1
SELECT    'Return Value' = @return_value
GO
--  Check latest record in tables
Select top 1 * from catto_aspnet_Membership order by MembershipID desc
select top 1 * from catto_aspnet_Users order by UserId desc
-----------------------------------------
--4. T-SQL’s Sky is the Limit
-- a. Alias
-- b. Scripting
-- c. Commands
-- d. Temp Tables
-- e. Select Into
-- f. Nested Select
-- g. Cursors
-- h. Case vs. If
-- i. Dependencies
-- j. Grants
-- k. Schemas
-- l. Template Explorer
-- m. Datetime
-- n. Try Catch Blocks
--------------------------------------
-- 4.a. Alias
--- 4.a.1 example simple
select cau.UserName uname
from catto_aspnet_Users cau
-- -4.a.2 example 2
select cau.UserName
from catto_aspnet_Users cau
INNER JOIN catto_aspnet_Membership cam
ON cau.UserId = cam.UserId
-- 4.b. Scripting
------------------------------------
-- 4.c. Commands
--c.1 Declare
DECLARE @loopCounter int
DECLARE @name varchar (50)
DECLARE @theDate datetime
--c.2 SET
SET @loopCounter = 1
SET @name = 'Catto'
--c.3 If
--SET @loopCounter = 0
IF @loopCounter = 1
BEGIN
Select * From catto_aspnet_Users
END
--c.4 Case
select UserName,
case active
when 0 then 'notActive'
when 1 then 'Active'
--when 2 then 'unknown'
else 'Invalid ActiveBit' end as "Active"
from catto_aspnet_Users
--c.5 Union
--INSERT INTO catto_aspnet_Membership
--              (active, UserId, Password, Email, IsApproved, CreateDate)
--VALUES (1,998, 'blahEncrypted2', 'catto@lives2.com', 1, CONVERT(DATETIME, '2010-08-08 00:00:00', 102))

Select UserID
From catto_aspnet_Users
UNION
Select UserID
From catto_aspnet_Membership
--c.6 Like
Select *
From catto_aspnet_Users
WHERE (UserName LIKE '%Catt%')
--c.7 Top
Select top 1 *
From catto_aspnet_Users
WHERE (UserName LIKE '%Catt%')
--declare @k8variable int
--set @k8variable  = 2
--select top @k8variable * from catto_aspnet_Users
--c.8 With No Lock
Select top 1 *
From catto_aspnet_Users with (nolock)
--c.9 Sum
SELECT SUM(catto_aspnet_Users.UserId) AS SumExample
FROM  catto_aspnet_Users
--c.10 Avg
SELECT AVG(UserId) AS AverageOfUID
FROM  catto_aspnet_Users
-- 4.d. Temp Tables
-- Example of using Temp Tables:
-- Delcare Variables
DECLARE @tableID int
DECLARE @nameOfPerson varchar (50)
-- CREATE temp table tempTablePeeps
CREATE TABLE #tempTablePeeps
(
ttpPeepID int NULL
, ttpPeepName varchar(50) NULL
)
-- Select & set variables
SET @tableID =
(
Select UserId
From catto_aspnet_Users
where userid = 2
)
SET @nameOfPerson =
(
Select userName
From catto_aspnet_Users
where userid = 2
)
-- Insert Values into temp Table
INSERT INTO #tempTablePeeps
(
ttpPeepID
, ttpPeepName
)
VALUES
(
@tableID
, @nameOfPerson
)
-- Select Values from Temp Table
Select ttpPeepID as ID
, ttpPeepName as PeepsName
From #tempTablePeeps
Drop Table #tempTablePeeps
-- 4.e. Select Into w/ temp table
SELECT
userid
, username
INTO #tempTable
FROM catto_aspnet_Users
select * from #tempTable
drop table #tempTable
-- Example: In the next nifty Select into statement an identity column is created on the temp table which could be quite useful especially if the newTablePeopleName table didn't have an identity.
-- catto_aspnet_users have identity already
--SELECT
--    userid
--    , username
--    , IDENTITY( int ) AS workingID
--INTO #tempTable
--FROM catto_aspnet_Users
--select * from #tempTable
--drop table #tempTable
-- 4.f. Nested Select
SELECT username
FROM catto_aspnet_Users
WHERE userid =
(    SELECT userid
FROM catto_aspnet_Membership
WHERE email = 'catto@domain.com'
)
--------------------------------------------------
-- 4.g. Cursors
DECLARE @UID INT
DECLARE curor_Data CURSOR STATIC FOR
select userid from catto_aspnet_Membership
OPEN curor_Data
FETCH curor_Data INTO
@UID
WHILE @@FETCH_STATUS = 0
BEGIN
select *
from catto_aspnet_Users
where @UID = userid
FETCH curor_Data INTO
@UID
END
CLOSE curor_Data
DEALLOCATE curor_Data
-- 4.h. Case vs. If
declare @activeflag int
set @activeflag =1
if @activeflag = 1
Begin
select username, active from catto_aspnet_Users
End
select UserName,
case active
when 0 then 'notActive'
when 1 then 'Active'
--when 2 then 'unknown'
else 'Invalid ActiveBit' end as "Active"
from catto_aspnet_Users
GO
-- 4.i. Dependencies right click on object 'view dependencies'
-- 4.j. Grants
-- GRANT EXECUTE ON p_getNames
--TO [Catto_Person_User]
--GO
-- 4.k. Schemas
create schema s_catto
GO
-- create table [s_catto].[schemaTable] (
--[schemaTable_id] [int] IDENTITY(1,1) NOT NULL)
-- 4.l. Template Explorer 2008
-- 4.m. Datetime
declare @uglyDate datetime
set @uglyDate = '2010-07-31 12:42:36.320'
declare @prettyDate varchar(25)
SET @prettyDate =
(
SELECT CAST(DATEPART(month, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(day, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(year, @uglyDate ) as varchar(4))
)
select @prettyDate as prettydateOutput
GO
-- note output is 7.31.2010
-- 4.n. Try Catch Blocks
BEGIN TRY
-- let us Generate a divide-by-zero error.
SELECT 1/0;
--select 1;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
--4.o --Execution Plan
select * from catto_aspnet_users where userid = 2 and username = 'CattoOrange'
-------------------------------------
-- 4.p.1   XML
SELECT  [UserId]
,[UserName]
FROM [catto_aspnet_Users]
FOR XML AUTO, ELEMENTS
-- 4.p.1   XML PATH
SELECT  [UserId] as  uid
,[UserName] as "info/@userName"
FROM [catto_aspnet_Users]
FOR XML PATH('K8Users');
GO
-- 4.p.3   SHOWPLan XML
SET  SHOWPLAN_XML ON;
GO
SELECT [UserId] as uid
,[UserName] as "info/@userName"
FROM [catto_aspnet_Users]
FOR XML PATH('K8Users');
Go
SET SHOWPLAN_XML OFF;
----END of SCRIPT
-------------------------------------

As Always all comments welcome that is all there will be more
Catto

SSMS Toughest Program to Install? It is!? Code Crackin #28

Hey Now,

SQL Server Management Studio SSMS, is it the toughest program to install? Personally I believe it is, what do you think? Let us take a closer look @ installing SSMS. As I just put a fresh install of Win7 54-bit on this notebook I obviously had to install all of my programs again. The first program I usually install is SSMS & I took some screen shots to include in this post. The version I installed in SQL Server 2008.

Many programs that we install it’s a simple 3 clicks, next, next, finish. I think SSMS takes about 20 clicks before we get to finish. There are many options that we can choose from. Below is the sequence of steps I used to install the program:

Here is a summary of the typical steps taken:

1st Compatibility Message (SP1)
2nd SQL Server Installation Center
3rd Setup Support Rules
4th Product Key
5th License Terms:
6th Setup Support Files
7th Setup Support Rules
8th Feature Selection
9th Instance Configuration
10th Disk Space Req
11th Server Configuration
12th Database Engine Config
13th Analysis Services Configuration SSAS
14th Reporting Services Config SSRS
15th Error & Usage Reporting
16th Installation Rules
17th Installation Rules
18th Ready to Install
19th Installation Progress
20th Complete
21st Tools Options – Customize

SSMS has a whole SQL Server Installation Center that opens when we start installing the program. The option we’ll focus on is adding a new SQL Server stand alone installation. Before I started the install we’ll get prompted about installing SP1.

3 Setup Support Rules is the next step, this will indentify any issue we may have when installing, below we see we passed all six.

4th Product Key- we enter in our Product Key

5th License Terms: We read the entire legal document, click accept then click next.

6th Setup Support Files We click next to setup support files.

7th Setup Support Rules, After it runs it will display passed, failed, or warnings:

8th Feature Selection: we select all the features we want to install such as SSAS, SSRS, full text search ect:

9th Instance Configuration We create an instance & select the location:

 

10th Disk Space Requirements verify there is space on the drive:

11th Server Configuration, this is where I’ve spent some time trying to get this setup correctly

12th Database Engine Configuration This is where we add the users

13th Analysis Services Configuration We setup SSAS Users:

14th Reporting Services Configuration setup SSRS

15th Error & Usage Reporting – we can choose to send information to Microsoft

17th Installation Rules – Rules are run to ensure the installation process will not be blocked

18th Ready to Install – We verify all of our features we want to install

19th Installation Progress – Displays the current process of installation.

20th Complete – The SQL Server installation was successfully installed.

21. Options – In addition to adding line numbers, changing the backround to black this is one option I like to adjust after I install which

As we’ve seen SQL Server is fun to install & has many different options we can choose from. We all need a database to connect to provide database driven applications, installing it is a requirement.

What do you think, is SSMS the toughest program to install? As always all comments welcome.

That is all, there will be more,

Catto