Transact-SQL - Joel Lipman .Com

DataScramble - Randomizing data rows

: Joel Lipman; Transact-SQL; 07 December 2012; Hits: 7446

What?
Along with my DataJumble function and DataTumble procedure which also help scramble database tables sent to suppliers/developers, this is a function which simply finds random characters and inserts these.

I would recommend using the DataTumble script over this one as this leaves data very difficult to work with:

copy raw

Before:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           John Smith           1990-03-21
           2           Fred Bloggs          1988-11-02
           3           Another User         1985-07-11
           4           Yet Another User     1977-06-25

       After:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           PDUHjRWJcb           1926-01-02
           2           WRmNqQKxvuV          1969-03-14
           3           nBCkAVDrvdhe         1968-05-05
           4           RJDsFMaeNcLrcMWw     1964-08-08

Before:
StudentID StudentName DateOfBirth
----------- -------------------- -------------
1 John Smith 1990-03-21
2 Fred Bloggs 1988-11-02
3 Another User 1985-07-11
4 Yet Another User 1977-06-25
After:
StudentID StudentName DateOfBirth
----------- -------------------- -------------
1 PDUHjRWJcb 1926-01-02
2 WRmNqQKxvuV 1969-03-14
3 nBCkAVDrvdhe 1968-05-05
4 RJDsFMaeNcLrcMWw 1964-08-08

How?

Category: Transact-SQL :: Article: 460

: Joel Lipman; Transact-SQL; 07 December 2012; Hits: 5434

What?
This is a stored procedure I've nabbed from some consultants from my day job. It shuffles the records and matching data values:

Before:

copy raw

ID        Name         DateOfBirth
--------- ------------ -----------
1         John Smith   1988-06-24
2         Fred Bloggs  1972-11-17
3         Another User 1964-02-18

ID Name DateOfBirth
--------- ------------ -----------
1 John Smith 1988-06-24
2 Fred Bloggs 1972-11-17
3 Another User 1964-02-18

After:

copy raw

ID        Name         DateOfBirth
--------- ------------ -----------
1         Fred Bloggs  1964-02-18
2         Another User 1988-06-24
3         John Smith   1972-11-17

ID Name DateOfBirth
--------- ------------ -----------
1 Fred Bloggs 1964-02-18
2 Another User 1988-06-24
3 John Smith 1972-11-17

Looks pretty good, doesn't it? The advantages of this is that you can send this data to your developers and the data types will be correct and maybe they'll resolve issues faster than if they were given scrambled data (see my articles on DataJumble and DataScramble).

How?

Category: Transact-SQL :: Article: 459

: Joel Lipman; Transact-SQL; 26 October 2012; Hits: 7772

What?
Yes you could just run a SELECT DISTINCT query along with collation to make it case-sensitive and/or accent-sensitive but using this stored procedure means I only specify the table and then the columns.

How?

: Joel Lipman; Transact-SQL; 10 October 2012; Hits: 8510

What?
This article is for demonstrating how to use a SOUNDEX in a select and then listing all the variations based on case-sensitivity.

Why?
We have a database with data in it. For a particular column we have setup default values, let's use the example "Data Not Yet Available". Unfortunately the end-user reported these default values sometimes list twice, especially when the case is different, eg. "Data not yet available". The final system (qlikview) was case-sensitive despite our server collation being case-insensitive.

Furthermore, we now have the task of finding all the variations of the default values which we found we could do with the built-in SOUNDEX function.

How?

: Joel Lipman; Transact-SQL; 03 August 2012; Hits: 9679

Why?
Hmm... I was writing a stored procedure that will scramble data given a table as a parameter. Because I only want to update a temporary table and not the original (source) table, I needed the following stored procedure (or part of).

What?
This will copy a given table into a temporary table all the while maintaining the structure and data.

Thinking inside of the box
I think everyone suggests the following (or at least the idea of):

copy raw

SELECT *
INTO #MyTempTable
FROM @GivenTable

SELECT *
INTO #MyTempTable
FROM @GivenTable

If @GivenTable is a parameter then the above will simply return an error. Also, if it was this easy, I wouldn't need to post this note on my website.

Category: Transact-SQL :: Article: 424

: Joel Lipman; Transact-SQL; 03 August 2012; Hits: 14599

What?
Some people have been suggesting you can use "CREATE OR REPLACE ... VIEW ... FUNCTION" but my SQL Server 2008 Management Studio doesn't like this and refuses to understand what I'm trying to do.

Why?
As this data seemed to be across various websites, I wanted a page which has all of them in one place. So here you go, I hope it's of some use. If it's wrong then just post a comment at the bottom of this page.

: Joel Lipman; Transact-SQL; 01 August 2012; Hits: 11879

This was called a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which merely uses the same characters but switches their order randomly, so I've renamed it DataJumble as opposed to my article on Data-Scrambling.

Why?
We want to scramble sensitive data that we send to suppliers for support or analysis. With inspiration from: "Obfuscating your SQL Server Data" by John Magnabosco but tweaked for our purposes. NOTE that the following has only ever been run on development environments and I would not recommend running this on a production system as I have not tested the performance and database load.

copy raw

Before:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           John Smith           1990-03-21
           2           Fred Bloggs          1988-11-02
           3           Another User         1985-07-11
           4           Yet Another User     1977-06-25

       After:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           mStnoh iJh           1926-01-02
           2           lgreg BFdos          1969-03-14
           3           onrUest ehAr         1968-05-05
           4           otentre AhYe Usr     1964-08-08

Before:
StudentID StudentName DateOfBirth
----------- -------------------- -------------
1 John Smith 1990-03-21
2 Fred Bloggs 1988-11-02
3 Another User 1985-07-11
4 Yet Another User 1977-06-25
After:
StudentID StudentName DateOfBirth
----------- -------------------- -------------
1 mStnoh iJh 1926-01-02
2 lgreg BFdos 1969-03-14
3 onrUest ehAr 1968-05-05
4 otentre AhYe Usr 1964-08-08

Category: Transact-SQL :: Article: 422

: Joel Lipman; Transact-SQL; 11 October 2011; Hits: 10765

I have googled, binged and asked but to no avail. Some self-proclaimed IT experts in forums said "why would you want to do that?". Unhelpful Bellends. It's a bit like asking me "Why can't I turn on my computer" and me replying "why would you want to do that?".

Anyway, I was looking for something like the record separator in Oracle SQL*Plus where a row of data (blank or made of symbols) separates two sets of data from within the same select query based on a column that's different. So for example, I have data like the following:

copy raw

SELECT
	DATENAME(dw, StartDate) AS 'Day'
FROM 
	Timetable
ORDER BY
	StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC

-- Yields
/*
Day              
---------------- 
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/

SELECT
DATENAME(dw, StartDate) AS 'Day'
FROM
Timetable
ORDER BY
StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC
-- Yields
/*
Day
----------------
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/

Category: Transact-SQL :: Article: 394

: Joel Lipman; Transact-SQL; 23 March 2011; Hits: 7737

My setup

Windows XP SP3
MS SQL Server Management Studio 2008
SQL Server 2008 R2
Regional Setting: London BST (British Summer Time)

: Joel Lipman; Transact-SQL; 14 January 2011; Hits: 14086

So I find myself searching the web for a way of producing an outlook-style calendar within Business Intelligence Development Studio (BIDS). Weekdays along the top and then dates inside. Additionally we would like this linked to events in a database.

I'm calling this project "Reinventing the Wheel" because the request was a report that was similar to an MS Outlook calendar which will display any room bookings for that month. This is "Reinventing" because we used to use MS Outlook to store these kind of things until the company implemented a software to deal with room bookings and said we shouldn't use Outlook for this...

: Joel Lipman; Transact-SQL; 11 January 2011; Hits: 22742

Thought I'd add a note as I was getting confused with the built-in function "FormatDateTime()". The example is shown as:

copy raw

=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

The other formats are:

copy raw

=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)

=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)

Unfortunately if you are using US dates and want the report to use a specific European date format and you spend as long as I did searching the web for a solution, then ignore all the above.

Another built-in function is the text-formatter

Category: Transact-SQL :: Article: 317

: Joel Lipman; Transact-SQL; 06 January 2011; Hits: 15090

Thought I'd put a note on this error. Bearing in mind that this is a general data type error and not just because I tried to convert a date in SQL format to a Month name.

copy raw

An error occurred during local report processing.
An error has occurred during report processing.
The Group expression used in grouping 'table1_month' returned a data type that is not valid.

An error occurred during local report processing.
An error has occurred during report processing.
The Group expression used in grouping 'table1_month' returned a data type that is not valid.

Category: Transact-SQL :: Article: 313