Monthly Archives: August 2014

by | August 28, 2014 · 10:30 am

Helper Stored Procedures in SQL Server - Part 1

Reading Time: 2 minutes

Often I have days where a large amount of my time is spent writing SQL code in SQL Server Management Studio (SSMS). Building tables, creating indices and verifying data are all common tasks. However, I often myself needing to checking which columns are in a table or to take a quick look at the data. I got tired of typing Select Top 10 * From [MyTable] more than five times per day.   In order to get around typing that repeatedly I made a function called 'sp_top10'.

CREATE Procedure [dbo].[sp_top10]
@Table as varchar(100)

as
Begin
set nocount on

Declare  @dbname as varchar(100)
Set @dbname = db_name()

Declare @MyQuery Varchar(max)
Set @MyQuery='
Select Top 10 *
From ' + @dbname + ' ..' + @Table + ' with(nolock)'

Exec (@MyQuery)
End

This stored procedure can be invoked by typing

exec sp_top10'YourTableHere'  where you substitute in your own table name. The real power comes when you assign your new stored procedure to a shortcut. In SSMS you can do this by going to Tools > Options > Keyboard > Query Shortcuts .  Simply add  'sp_top10' to an open key combination and then restart SSMS. You should now be able to highlight ( or doubleclick) a tablename and then use your new shortcut to view the top 10 records in that table. I set my shortcut to 'Ctrl+F1'.

An example:

-- Create Table To Hold Our Dataset
If Object_ID('tempdb..#TestData','U') is not null
Drop Table #TestData

Create Table #TestData
(ID bigint not null identity(1,1) ,
Letter varchar(1)
)

-- Quick Loop to get all lower case letters into our table
Declare @Counter Int;Set @Counter=97
While @Counter <=122
Begin
Declare @Sql Varchar(Max)
Set @SQL ='
Insert into #TestData (letter)
Select char(' + cast(@Counter as varchar)  + ')
'
--print @SQL
Exec (@Sql)
Set @Counter = @Counter+1
End

-- View all 26 records
Select *
From #TestData

-- Just view the top 10
exec sp_top10'#testdata'

You output should look similar to figure 1.

sp_top10_example

Figure 1.

Feel free to edit the SP to suit your particular situation. I use this in conjunction with 'sp_help' which has a default shortcut of 'Alt+F1'  in SSMS almost everyday. So I can select a tablename and 'Alt+F1' if I need to see what type of columns I have and then 'Ctrl+1' to view the first ten records of that table. Hopefully this will help save you time in your daily work.

Note: SSMS 2012 was used for this example.

1 Comment

Filed under Sql Server

by | August 15, 2014 · 3:34 am

Making a Code Book in Sql Server

Reading Time: 4 minutes

While working through a coursera course recently (https://www.coursera.org/course/getdata) I started to think about how a code book could be implemented in sql server. There are 3 points that Jeff Leek makes regarding a code book and they are as follows:

  1. Information about the variables ...
  2. Information about the Summary Choices  you made
  3. Information about the study design you used

My focus for this article will be able point number one, information about the variables. Often times it would be nice to know more about your data than simply what data structure it is defined by. For a simple working example, load the Iris datasets into Sql Server and then add some metadata about the columns to provide further information for the users.

 

Use YourDataBaseName
Go

-- Create Table 
If Object_Id('YourDataBaseName.dbo.Iris','U') is not null 
Drop Table dbo.Iris

Create Table dbo.Iris
(
IrisID bigint not null identity(1,1) ,
SepalLength numeric,
SepalWidth numeric,
PetalLength numeric,
PetalWidth numeric,
Species nvarchar(100)
)
-- Insert Values
Insert Into dbo.Iris Values('5.1','3.5','1.4','0.2','setosa')
Insert Into dbo.Iris Values('4.9','3','1.4','0.2','setosa')
Insert Into dbo.Iris Values('4.7','3.2','1.3','0.2','setosa')
Insert Into dbo.Iris Values('4.6','3.1','1.5','0.2','setosa')
Insert Into dbo.Iris Values('5','3.6','1.4','0.2','setosa')
Insert Into dbo.Iris Values('5.4','3.9','1.7','0.4','setosa')
Insert Into dbo.Iris Values('4.6','3.4','1.4','0.3','setosa')
Insert Into dbo.Iris Values('5','3.4','1.5','0.2','setosa')
Insert Into dbo.Iris Values('4.4','2.9','1.4','0.2','setosa')
Insert Into dbo.Iris Values('4.9','3.1','1.5','0.1','setosa')
Insert Into dbo.Iris Values('5.4','3.7','1.5','0.2','setosa')
Insert Into dbo.Iris Values('4.8','3.4','1.6','0.2','setosa')
Insert Into dbo.Iris Values('4.8','3','1.4','0.1','setosa')
Insert Into dbo.Iris Values('4.3','3','1.1','0.1','setosa')
Insert Into dbo.Iris Values('5.8','4','1.2','0.2','setosa')
Insert Into dbo.Iris Values('5.7','4.4','1.5','0.4','setosa')
Insert Into dbo.Iris Values('5.4','3.9','1.3','0.4','setosa')
Insert Into dbo.Iris Values('5.1','3.5','1.4','0.3','setosa')
Insert Into dbo.Iris Values('5.7','3.8','1.7','0.3','setosa')
Insert Into dbo.Iris Values('5.1','3.8','1.5','0.3','setosa')
Insert Into dbo.Iris Values('5.4','3.4','1.7','0.2','setosa')
Insert Into dbo.Iris Values('5.1','3.7','1.5','0.4','setosa')
Insert Into dbo.Iris Values('4.6','3.6','1','0.2','setosa')
Insert Into dbo.Iris Values('5.1','3.3','1.7','0.5','setosa')
Insert Into dbo.Iris Values('4.8','3.4','1.9','0.2','setosa')
Insert Into dbo.Iris Values('5','3','1.6','0.2','setosa')
Insert Into dbo.Iris Values('5','3.4','1.6','0.4','setosa')
Insert Into dbo.Iris Values('5.2','3.5','1.5','0.2','setosa')
Insert Into dbo.Iris Values('5.2','3.4','1.4','0.2','setosa')
Insert Into dbo.Iris Values('4.7','3.2','1.6','0.2','setosa')
Insert Into dbo.Iris Values('4.8','3.1','1.6','0.2','setosa')
Insert Into dbo.Iris Values('5.4','3.4','1.5','0.4','setosa')
Insert Into dbo.Iris Values('5.2','4.1','1.5','0.1','setosa')
Insert Into dbo.Iris Values('5.5','4.2','1.4','0.2','setosa')
Insert Into dbo.Iris Values('4.9','3.1','1.5','0.2','setosa')
Insert Into dbo.Iris Values('5','3.2','1.2','0.2','setosa')
Insert Into dbo.Iris Values('5.5','3.5','1.3','0.2','setosa')
Insert Into dbo.Iris Values('4.9','3.6','1.4','0.1','setosa')
Insert Into dbo.Iris Values('4.4','3','1.3','0.2','setosa')
Insert Into dbo.Iris Values('5.1','3.4','1.5','0.2','setosa')
Insert Into dbo.Iris Values('5','3.5','1.3','0.3','setosa')
Insert Into dbo.Iris Values('4.5','2.3','1.3','0.3','setosa')
Insert Into dbo.Iris Values('4.4','3.2','1.3','0.2','setosa')
Insert Into dbo.Iris Values('5','3.5','1.6','0.6','setosa')
Insert Into dbo.Iris Values('5.1','3.8','1.9','0.4','setosa')
Insert Into dbo.Iris Values('4.8','3','1.4','0.3','setosa')
Insert Into dbo.Iris Values('5.1','3.8','1.6','0.2','setosa')
Insert Into dbo.Iris Values('4.6','3.2','1.4','0.2','setosa')
Insert Into dbo.Iris Values('5.3','3.7','1.5','0.2','setosa')
Insert Into dbo.Iris Values('5','3.3','1.4','0.2','setosa')
Insert Into dbo.Iris Values('7','3.2','4.7','1.4','versicolor')
Insert Into dbo.Iris Values('6.4','3.2','4.5','1.5','versicolor')
Insert Into dbo.Iris Values('6.9','3.1','4.9','1.5','versicolor')
Insert Into dbo.Iris Values('5.5','2.3','4','1.3','versicolor')
Insert Into dbo.Iris Values('6.5','2.8','4.6','1.5','versicolor')
Insert Into dbo.Iris Values('5.7','2.8','4.5','1.3','versicolor')
Insert Into dbo.Iris Values('6.3','3.3','4.7','1.6','versicolor')
Insert Into dbo.Iris Values('4.9','2.4','3.3','1','versicolor')
Insert Into dbo.Iris Values('6.6','2.9','4.6','1.3','versicolor')
Insert Into dbo.Iris Values('5.2','2.7','3.9','1.4','versicolor')
Insert Into dbo.Iris Values('5','2','3.5','1','versicolor')
Insert Into dbo.Iris Values('5.9','3','4.2','1.5','versicolor')
Insert Into dbo.Iris Values('6','2.2','4','1','versicolor')
Insert Into dbo.Iris Values('6.1','2.9','4.7','1.4','versicolor')
Insert Into dbo.Iris Values('5.6','2.9','3.6','1.3','versicolor')
Insert Into dbo.Iris Values('6.7','3.1','4.4','1.4','versicolor')
Insert Into dbo.Iris Values('5.6','3','4.5','1.5','versicolor')
Insert Into dbo.Iris Values('5.8','2.7','4.1','1','versicolor')
Insert Into dbo.Iris Values('6.2','2.2','4.5','1.5','versicolor')
Insert Into dbo.Iris Values('5.6','2.5','3.9','1.1','versicolor')
Insert Into dbo.Iris Values('5.9','3.2','4.8','1.8','versicolor')
Insert Into dbo.Iris Values('6.1','2.8','4','1.3','versicolor')
Insert Into dbo.Iris Values('6.3','2.5','4.9','1.5','versicolor')
Insert Into dbo.Iris Values('6.1','2.8','4.7','1.2','versicolor')
Insert Into dbo.Iris Values('6.4','2.9','4.3','1.3','versicolor')
Insert Into dbo.Iris Values('6.6','3','4.4','1.4','versicolor')
Insert Into dbo.Iris Values('6.8','2.8','4.8','1.4','versicolor')
Insert Into dbo.Iris Values('6.7','3','5','1.7','versicolor')
Insert Into dbo.Iris Values('6','2.9','4.5','1.5','versicolor')
Insert Into dbo.Iris Values('5.7','2.6','3.5','1','versicolor')
Insert Into dbo.Iris Values('5.5','2.4','3.8','1.1','versicolor')
Insert Into dbo.Iris Values('5.5','2.4','3.7','1','versicolor')
Insert Into dbo.Iris Values('5.8','2.7','3.9','1.2','versicolor')
Insert Into dbo.Iris Values('6','2.7','5.1','1.6','versicolor')
Insert Into dbo.Iris Values('5.4','3','4.5','1.5','versicolor')
Insert Into dbo.Iris Values('6','3.4','4.5','1.6','versicolor')
Insert Into dbo.Iris Values('6.7','3.1','4.7','1.5','versicolor')
Insert Into dbo.Iris Values('6.3','2.3','4.4','1.3','versicolor')
Insert Into dbo.Iris Values('5.6','3','4.1','1.3','versicolor')
Insert Into dbo.Iris Values('5.5','2.5','4','1.3','versicolor')
Insert Into dbo.Iris Values('5.5','2.6','4.4','1.2','versicolor')
Insert Into dbo.Iris Values('6.1','3','4.6','1.4','versicolor')
Insert Into dbo.Iris Values('5.8','2.6','4','1.2','versicolor')
Insert Into dbo.Iris Values('5','2.3','3.3','1','versicolor')
Insert Into dbo.Iris Values('5.6','2.7','4.2','1.3','versicolor')
Insert Into dbo.Iris Values('5.7','3','4.2','1.2','versicolor')
Insert Into dbo.Iris Values('5.7','2.9','4.2','1.3','versicolor')
Insert Into dbo.Iris Values('6.2','2.9','4.3','1.3','versicolor')
Insert Into dbo.Iris Values('5.1','2.5','3','1.1','versicolor')
Insert Into dbo.Iris Values('5.7','2.8','4.1','1.3','versicolor')
Insert Into dbo.Iris Values('6.3','3.3','6','2.5','virginica')
Insert Into dbo.Iris Values('5.8','2.7','5.1','1.9','virginica')
Insert Into dbo.Iris Values('7.1','3','5.9','2.1','virginica')
Insert Into dbo.Iris Values('6.3','2.9','5.6','1.8','virginica')
Insert Into dbo.Iris Values('6.5','3','5.8','2.2','virginica')
Insert Into dbo.Iris Values('7.6','3','6.6','2.1','virginica')
Insert Into dbo.Iris Values('4.9','2.5','4.5','1.7','virginica')
Insert Into dbo.Iris Values('7.3','2.9','6.3','1.8','virginica')
Insert Into dbo.Iris Values('6.7','2.5','5.8','1.8','virginica')
Insert Into dbo.Iris Values('7.2','3.6','6.1','2.5','virginica')
Insert Into dbo.Iris Values('6.5','3.2','5.1','2','virginica')
Insert Into dbo.Iris Values('6.4','2.7','5.3','1.9','virginica')
Insert Into dbo.Iris Values('6.8','3','5.5','2.1','virginica')
Insert Into dbo.Iris Values('5.7','2.5','5','2','virginica')
Insert Into dbo.Iris Values('5.8','2.8','5.1','2.4','virginica')
Insert Into dbo.Iris Values('6.4','3.2','5.3','2.3','virginica')
Insert Into dbo.Iris Values('6.5','3','5.5','1.8','virginica')
Insert Into dbo.Iris Values('7.7','3.8','6.7','2.2','virginica')
Insert Into dbo.Iris Values('7.7','2.6','6.9','2.3','virginica')
Insert Into dbo.Iris Values('6','2.2','5','1.5','virginica')
Insert Into dbo.Iris Values('6.9','3.2','5.7','2.3','virginica')
Insert Into dbo.Iris Values('5.6','2.8','4.9','2','virginica')
Insert Into dbo.Iris Values('7.7','2.8','6.7','2','virginica')
Insert Into dbo.Iris Values('6.3','2.7','4.9','1.8','virginica')
Insert Into dbo.Iris Values('6.7','3.3','5.7','2.1','virginica')
Insert Into dbo.Iris Values('7.2','3.2','6','1.8','virginica')
Insert Into dbo.Iris Values('6.2','2.8','4.8','1.8','virginica')
Insert Into dbo.Iris Values('6.1','3','4.9','1.8','virginica')
Insert Into dbo.Iris Values('6.4','2.8','5.6','2.1','virginica')
Insert Into dbo.Iris Values('7.2','3','5.8','1.6','virginica')
Insert Into dbo.Iris Values('7.4','2.8','6.1','1.9','virginica')
Insert Into dbo.Iris Values('7.9','3.8','6.4','2','virginica')
Insert Into dbo.Iris Values('6.4','2.8','5.6','2.2','virginica')
Insert Into dbo.Iris Values('6.3','2.8','5.1','1.5','virginica')
Insert Into dbo.Iris Values('6.1','2.6','5.6','1.4','virginica')
Insert Into dbo.Iris Values('7.7','3','6.1','2.3','virginica')
Insert Into dbo.Iris Values('6.3','3.4','5.6','2.4','virginica')
Insert Into dbo.Iris Values('6.4','3.1','5.5','1.8','virginica')
Insert Into dbo.Iris Values('6','3','4.8','1.8','virginica')
Insert Into dbo.Iris Values('6.9','3.1','5.4','2.1','virginica')
Insert Into dbo.Iris Values('6.7','3.1','5.6','2.4','virginica')
Insert Into dbo.Iris Values('6.9','3.1','5.1','2.3','virginica')
Insert Into dbo.Iris Values('5.8','2.7','5.1','1.9','virginica')
Insert Into dbo.Iris Values('6.8','3.2','5.9','2.3','virginica')
Insert Into dbo.Iris Values('6.7','3.3','5.7','2.5','virginica')
Insert Into dbo.Iris Values('6.7','3','5.2','2.3','virginica')
Insert Into dbo.Iris Values('6.3','2.5','5','1.9','virginica')
Insert Into dbo.Iris Values('6.5','3','5.2','2','virginica')
Insert Into dbo.Iris Values('6.2','3.4','5.4','2.3','virginica')
Insert Into dbo.Iris Values('5.9','3','5.1','1.8','virginica')

-- Add Extended Properties
EXEC sys.sp_addextendedproperty @name=N'Notes', @value=N'
Sepal Length of Iris plant in centimetres.
' , @level0type=N'SCHEMA',
	@level0name=N'dbo', 
	@level1type=N'TABLE',
	@level1name=N'Iris', 
	@level2type=N'COLUMN',
	@level2name=N'SepalLength'
GO


EXEC sys.sp_addextendedproperty @name=N'Notes', @value=N'
Sepal width of Iris plant in centimetres.
' , @level0type=N'SCHEMA',
	@level0name=N'dbo', 
	@level1type=N'TABLE',
	@level1name=N'Iris', 
	@level2type=N'COLUMN',
	@level2name=N'SepalWidth'
GO



EXEC sys.sp_addextendedproperty @name=N'Notes', @value=N'
Petal Length of Iris plant in centimetres.
' , @level0type=N'SCHEMA',
	@level0name=N'dbo', 
	@level1type=N'TABLE',
	@level1name=N'Iris', 
	@level2type=N'COLUMN',
	@level2name=N'PetalLength'
GO

EXEC sys.sp_addextendedproperty @name=N'Notes', @value=N'
Petal Width  of Iris plant in centimetres.
' , @level0type=N'SCHEMA',
	@level0name=N'dbo', 
	@level1type=N'TABLE',
	@level1name=N'Iris', 
	@level2type=N'COLUMN',
	@level2name=N'PetalWidth'
GO

 
EXEC sys.sp_addextendedproperty @name=N'Notes', @value=N'
Species of Iris plant.
' , @level0type=N'SCHEMA',
	@level0name=N'dbo', 
	@level1type=N'TABLE',
	@level1name=N'Iris', 
	@level2type=N'COLUMN',
	@level2name=N'Species'
GO

Now, you should have a table called Iris with some basic metadata about the columns. While you can navigate to the extended properties through the object explorer, it would be nice to access this information through a query.

-- View the Code book for the Iris Table and some information about the data structure
SELECT 
	[major_id], 
	[minor_id], 
	[t.name] AS [Table Name], 
	[c.name] AS [Column Name], 
	[value] AS [Extended Property],
	infos.[Data_Type],
	infos.[is_nullable],
	infos.[Numeric_Precision],
	infos.[Numeric_Scale]
FROM 
	sys.extended_properties AS ep
	inner join
	sys.tables AS t 
	ON ep.major_id = t.object_id 
	inner join
	sys.columns AS c 
	ON ep.major_id = c.object_id 
	  and ep.minor_id = c.column_id
	inner join 
	INFORMATION_SCHEMA.COLUMNS infos
	on infos.table_name = t.name 
	  and infos.column_name = c.name
Where 
	class = 1
	and t.name ='Iris' /* This is our Table name */



-- Drop Iris Table to avoid cluttering up your database
Drop Table dbo.Iris

Now you have the framework for creating a data code book that can be self contained within the table itself. This will prove most useful when you can can share a sql server table with someone else.

 

Leave a Comment

Filed under Sql Server

by | August 8, 2014 · 11:49 pm

Unexpected behavior with summary function in R

Reading Time: 2 minutes

I often find myself working with data that includes dates and times. Sometimes I am interested in looking at what happened on on a particular calendar day. I usually avoid dealing with actual datetime formats when working at the day level and prefer to use an integer for representing a day. For instance the first day of each quarter can be represented as integers (numeric also works for this example). For instance if I wanted to know the oldest date in my dataset I can just take the minimum since I am using a numerical data structure.

mydates  <- c(20140101L,20140401L,20140701L,20141001L)
min(mydates)

[1] 20140101

For some reason which I don't recall, I tried using the summary() function on my dates. The only values that would be valid are the minimum and the maximum, or so I thought.

summary(mydates)

The output:
# Min.            1st Qu.       Median      Mean          3rd Qu.       Max.
# 20140000 20140000 20140000 20140000 20140000 20140000

This behavior which seemed odd to me, is caused by the way summary() deals with numerical data. So I decided to look at what summary is actually doing. To view the code behind the summary function type:

summary.default

The portion of code that we are interested in is this:

else if (is.numeric(object)) {
nas <- is.na(object)
object <- object[!nas]
qq <- stats::quantile(object)
qq <- signif(c(qq[1L:3L], mean(object), qq[4L:5L]), digits)
names(qq) <- c("Min.", "1st Qu.", "Median", "Mean", "3rd Qu.",
"Max.")
if (any(nas))
c(qq, `NA's` = sum(nas))
else qq
}

This code chunk gets executed when the object that we pass to summary is numeric.  If we substitute in our object 'mydates' we get the following code.

digits = max(3L, getOption("digits") -     3L)
nas <- is.na(mydates)
mydates<- mydates[!nas]
qq <- stats::quantile(mydates)
qq <- signif(c(qq[1L:3L], mean(mydates), qq[4L:5L]), digits)

If you step through the code line by line, you will notice that after line 4, summary produces what we would expect to see for a min and max value. However, after you execute line 5, the numbers are changed because they are not the actual numbers but they are changed to be significant figures. For example try:

signif(20140101,digits)

[1] 20140000

So be careful when using generic functions if you don't know what they are doing. I would encourage to take a look at the code behind some of the R functions you use the most. For instance using the fivenum() function does not change my min and max values the same way summary did.

Leave a Comment

Filed under R