Monthly Archives: September 2014

Job Market For Statisticians

Reading Time: 2 minutes

I have been forced to think about the job market lately. It started with a class assignment which was meant to simply open my eyes to current job market. I felt that I was already familiar enough but completed the assignment to be a good student. I completed the assignment and outlined the skills I need improve upon and so forth. With in day of completing my assignment I came across "A Guide and Advice for Economists on the U.S. Junior Academic Job Market: 2014-2015 Edition" after clicking through some links on facebook. I found it a great read and it caused me to starting thinking about a few things that will likely prove helpful down the road. I had originally intended to pursue a Ph.D. in Economics after finishing my M.S. in Statistics. However, life took a turn and I ended up working full time and then started working on my Ph.D. in Statistics part time while continuing to work. Make sure that you take a look at the salary tables that are included. The table below is for full time working White Males by which Ph.D. they obtained. There is more variation associated with the Economics degree, but not enough to not make it look better than Math or Statistics based solely upon salary.

For White Males Median Salary SE       95 % Range
Mathematics/Statistics  $100,000   1,500  (97,000 - 103,000)
Economics   $126,000   5,500 (115,000 -137,000)

(Data taken from: Table 50 , the 95% range is mine based on the assumption of a normal distribution.)

There is also an article in the American Statistician recently about career paths, "Which Career Path Will You Follow?". Between these three events that occurred within a week, I thought that it merited a post. Have some other useful job advice or interesting statistics that current graduate students should know? Post a comment.

Leave a Comment

Filed under Uncategorized

Helper Stored Procedures in SQL Server - Part 2

Reading Time: 3 minutes

Continuing with last week's post about Helper Stored Procedures, I wanted to introduce two more that I use frequently. The first one is a built in stored procedure (  using SQL Server) called 'sp_helptext'. This stored procedure allows you to view the content of other stored procedures (SP) and more, here is a link to the Microsoft documentation. Here is an example of how you use it:

sp_helptext @objname='sp_helptext'

This will show you the contents of sp_helptext  by using it. This comes in very handy as a keyboard shortcut. I assigned mine to 'Ctrl+3'.  Often I have a SP that does data manipulation and puts the final data in a table that occurs as the last step of my SP. I may not remember which table the data goes into, but I can simply highlight the SP ( I typically double click) and then use 'Ctrl+3' and the contents of the SP appears in my results tab. This also works nicely for function and triggers that you might use.

Another piece of information that I am often curious about is how large is a particular table. If you like going through menus, then you can left click on a table name in the object explorer and choose properties. Then select 'storage' from the left navigation menu. You will see under the 'general' section: "Data Space","Row Count" and "Index Space". While this is okay if you only perform this task once a month, it takes way to long if you are running this 5 + times per day. I wrote a stored procedure called 'sp_tablesize' that is a wrapper for another Microsoft SP, that is sp_spaceused.

USE [YourDatabase]



Alter Procedure [dbo].[TableSize_Sp]
@TableName varchar(500)

If object_id(@Tablename,'U') is not null
If Object_ID('tempdb..#TableSummary','U') Is Not Null  
Drop Table #TableSummary

CREATE TABLE #TableSummary
[database] varchar(500) default db_name(),
table_name sysname ,
row_count BIGINT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50),
[timestamp] datetime default getdate() not null,
Calendardate as convert(datetime,convert(varchar,[timestamp],101))

Declare @Sql varchar(max)
Set @Sql='
INSERT #TableSummary ([table_name],[row_count],[reserved_size],[data_size],[index_size],[unused_size])
EXEC sp_spaceused''' + @TableName + '''

Exec (@Sql)

If Object_ID('tempdb..#TableSummaryCleaned','U') is not null
Drop Table #TableSummaryCleaned
	a.table_name [TableName],
	round(sum(cast(a.row_count as bigint)),2)[Rows],
	round(sum(cast(reserved_size_kb as float)/1024),2)ReservedSizeMB,
	round(sum(cast(data_size_kb as float)/1024),2)DataSizeMB,
	round(sum(cast(index_size_kb as float)/1024),2)IndexSizeMB,
	round(sum(cast(unused_size_kb as float)/1024),2)UnusedSizeMB,
Into #TableSummaryCleaned	
		table_name ,
		cast(replace(reserved_size, ' KB','') as bigint) reserved_size_kb,
		cast(replace(data_size, ' KB','') as bigint) data_size_kb,
		cast(replace(index_size, ' KB','') as bigint) index_size_kb,
		cast(replace(unused_size, ' KB','') as bigint) unused_size_kb,
	From #TableSummary
Group By 

Alter Table #TableSummaryCleaned
Add TotalMB float

Update #TableSummaryCleaned 
Set TotalMB = ReservedSizeMB 

Alter Table #TableSummaryCleaned
Add TotalGB float

Update #TableSummaryCleaned
Set TotalGB =TotalMB/1024 

Select *
From #TableSummaryCleaned
	Declare @ErrorText varchar(500)
	Set @ErrorText = @TableName+' is not a valid table.'
 RAISERROR(@ErrorText, 16, 1)


After you create the SP 'tablesize_SP' you can assign it to a shortcut in SSMS. I choose to use 'Ctrl+4' for myself. We can create a table and add data and an index to make sure that our new SP works.


Create Table dbo.TestTable
( numbers float )

-- Create Table with numbers 1 -100
Declare @Counter int
Set @Counter=1
While @Counter <=1000
    Insert Into dbo.TestTable Select @counter*1000
    Set @Counter=@Counter+1

-- Check Table Size
exec tablesize_sp'TestTable'

-- Create Index
Create NonClustered Index [NC_Numbers] on dbo.TestTable

-- Check size after creating index
exec tablesize_sp'TestTable'

You will notice that our index size increased after adding one, which is to be expected. Hopefully this will allow you to quickly check the size of a table. Combine that with 'sp_helptext' and 'sp_top10' you can find out a lot of information about a table quite quickly. Let me leave you with a quick reference guide of which SP to use depending on what you want to know.


SP Name Use Shortcut
sp_helptext View the contents of a stored procedure or function Ctrl+3
tablesize_SP Check the size of a table including indicies Ctrl+4
sp_top10 View the top 10 records of a table Ctrl+1
sp_help View Information about an object for instance table schema Alt+F1

Note: SSMS 2012 was used for this example.

Leave a Comment

Filed under Sql Server