December 20, 2013 at 1:00 pm
I have a question that I haven't seen put out there and I don't know if it is because it would never be done or is just a unspoken law.
In the last decade, I have had a few different positions at different companies. Some I left for better opportunities, others due to layoffs, and others due to contracts ending.
As a report designer, query writer, programmer (C# mainly), and all around techie guy; I find myself sometimes coming to a new company who wants a report done in much the same way as a previous employer.
Usually, I start from scratch building the report to the new specs; but, on several occasions I use the same techniques as before but possibly forgot what I did to get around a problem. At that point, I do what I normally do, go research the best way to tackle a problem, apply it, and move on.
However, it strikes me that I could have been saving my work all along and have it for reference later. The first obstacle that comes to mind is that I don't want to use the employer data or expose any part of their business to security risks and I don't want to breach any non-compete or other such industry agreement about data etc.
How do you keep track of the nifty problems you've solved and how do you store them for later application in other projects without infringing on the concerns above? Do you make a dummy database that mirrors the one you work on daily? Do you setup mock reports/SPs/code etc. that mimic what you've done?
What I'd like to have eventually is a tidy stable of reports that I've done along with other types of projects in a place that I can use them in current work and show them off as examples of work.
December 21, 2013 at 6:07 pm
Here's one idea... definitely don't take any data that could be construed as belonging to your employer, but if you develop a solution, there's no reason you can't keep a database of things like that. Especially stored procedures, functions etc, or a general design.
Create a database on your personal computer and if you're allowed (check first), script out the structure of the tables you need and the stored procedures, etc. Then fill them with fake data. Jeff Moden has a routine somewhere around here that generates something like a million rows of data (look for "JBMTest"). Modify that to insert the proper data types into your tables.
Then you can create stored procedures and reports based on that. I did one for fake patient data where I found the top 200 most frequent first names and last names, and then did a top values/cross join to populate a table with fake people. Birthdates are easy - generate a random number and use DATEADD() to make it look reasonably realistic. (If you have The Guru's Guide to T-SQL, the author gives a bunch of examples of creating stored procedures to crank out fake data.) Then write all your stored procedures etc for your reports and away you go. Sure, the data is fake, but who cares?
December 21, 2013 at 10:54 pm
Here are a couple of articles for generating gobs of data in a random but highly constrained (you know what the ranges of values will be) fashion.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/
Dwain Camps has a good article on how to generate non-uniform random but constrained data at the following URL.
http://www.sqlservercentral.com/articles/SQL+Uniform+Random+Numbers/91103/
And, no need to search for "JBMTest". I've included one of the many different renditions here.
/**********************************************************************************************************************
Purpose:
Create a voluminous test table with various types of highly randomized data.
--Jeff Moden
**********************************************************************************************************************/
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL
DROP TABLE dbo.JBMTest
;
--===== Create and populate a 1,000,000 row test table.
-- "SomeID" has a range of 1 to 1,000,000 unique numbers
-- "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- "SomeMoney has a range of 0.00 to 100.00 non-unique numbers
-- "SomeDateTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times
-- "SomeDate" has a range of >=01/01/2000 and <01/01/2020 non-unique "whole dates"
-- "SomeName" contains random characters at random lengths from 2 to 20 characters
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A'))
+ CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A')),
SomeMoney = CAST(RAND(CHECKSUM(NEWID())) * 100 AS DECIMAL(9,2)), --Note rounding
SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeDate = ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeName = RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 19 + 2)
INTO dbo.JBMTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
ALTER TABLE dbo.JBMTest
ADD CONSTRAINT PK_JBMTest PRIMARY KEY CLUSTERED (SomeID) WITH FILLFACTOR = 90
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 24, 2013 at 8:41 am
Thank you very much for the insight.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply