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
, IsApproved
, CreateDate
)
VALUES
( 1
, @NewUserId
, @Password
, 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
No comments:
Post a Comment