Entry 1: (T-SQL) Where it all Begins
Paul Rizza’s T-SQL, SSIS and SSRS Blog
Many of my clients and the students in my classes for SQL Pass SQLSaturday sessions often ask if I have a blog where I post more information, so I decided I should go ahead and start one.
Since many of my future blog posts will be on T-SQL I decided to make this first blog a very simple one, where I can store the setup for the database I will run future code, as well as store some of the common items I will need for those future entries.
My background:
I have been working with SQL Server since version 6.5. For the last 13 years I have been working as a Consultant, most recently for Microsoft Corporation. I also started with DTS in SQL 2000, and moved to SSIS in SQL 2005. Many of my SSIS based postings will also include C# code that makes use of a set of DLLs called EzAPI. EzAPI is a code wrapper for the SSIS DLLs that helps to produce SSIS packages in C# code without as much effort (saving 100s of hours in many cases).
Topic requests:
While I will do my best to post Blog entries on the topics I get feedback requesting, I am limited to the topics I have time to fully research. In many cases that limitation of time is driven by my current assignments for my current clients at Microsoft. If there is something you would like to see in more detail please leave a comment and I will do my best to cover it in a later post, but I cannot promise to cover everything.
Getting Started:
This first script will create the database where I will keep and run all of my future blog posts:
Create BlogDB Database
USE [master]
GO
/****** Object: Database [BlogDB] Script Date: 5/27/2013 2:10:12 PM ******/
CREATE DATABASE [BlogDB] ON PRIMARY
( NAME = N'BlogDB', FILENAME = N'C:\MSSQL\Data\BlogDB.mdf' , SIZE = 524288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 131072KB )
LOG ON
( NAME = N'BlogDB_log', FILENAME = N'C:\MSSQL\Logs\BlogDB_log.ldf' , SIZE = 131072KB , MAXSIZE = 2048GB , FILEGROWTH = 131072KB )
GO
ALTER DATABASE [BlogDB] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [BlogDB] SET RECOVERY SIMPLE
GO
This next script will create and populate a table called Tally which has a single column (N type INT) that holds the numbers from 1 to 1 Million. You would not think that having a table with 1 million numbers in it would be that important but once you have read through a few of my blogs you are going to find that you may not be able to live without it.
Table Tally
USE [BlogDB]
go
-- A Tally table comes in very handy for lots of different functions (but you should make sure you keep track of how many rows you load
CREATE TABLE Tally(N INT NOT NULL PRIMARY KEY)
-- I am going to keep 1 million rows because that has worked very well for me in the past
INSERT INTO Tally
(N)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY A.Object_ID) AS N
FROM
--Just some random tables so I know my result set will have at least 1 million rows
sys.all_objects a, sys.objects b, sys.objects c
Comments, Questions and Requests:
I know this is a very simple Blog for the first entry. I understand that there is not much value add, but the main reason for this one is so I can reference it in the future. As new permanent items are added to the BlogDB database I will update this entry and add them to the code for this blog. Please feel free to post comments, leave questions or add requests, and I will do my best to address them as time permits.
Comments
- Anonymous
June 04, 2013
It's a quite simple and helpful script anyway!What about adding it to the TechNet Gallery?