Random Numbers

While working on a project, I needed to create test data, and came across another of Pindal Dave's excellent posts: "SQL SERVER – Random Number Generator Script – SQL Query".

SQL Permissions

Good article on SQL permissions: "Understanding GRANT, DENY, and REVOKE in SQL Server".

Clever workaround for passing an array to a stored procedure or function: "Using a Variable for an IN Predicate".

Data Profiling Article

There's a good article on Data Profiling in SQL Server, complete with a script to profile tables, in SSC. It reminded me of a talk I gave at BSSUG on a data profiling script I wrote.

Interview Questions for SQL Developers

Read a good article on SSC today: "15 Quick Short Interview Questions Useful When Hiring SQL Developers". Some of the questions one might cover only in specific situations, but overall they were broad enough to cover what a Db Developer must know.

BSSUG July 2010 Presentation

On July 19th I gave a presentation to the Baltimore SQL Server Users Group titled "TSQL code to determine possible unique keys of raw data." The content can be found here. Here is the abstract:

I will be describing an algorithm I wrote that employs a hybrid recursive/iterative approach to determining the minimal unique key (if one exists), for a given table of raw data. The presentation will cover the problems involved in developing a solution and various approaches I considered, and of course a review of the solution at which I ultimately arrived.

Tip to open SSMS

If you have a bunch of related .sql script files that you want to open simultaneously (for example, a monthly process), create a MS-DOS batch file and add this command-line:

start ssms “test1.sql” “test2.sql”

That line will open SQL script files test1.sql and test2.sql in the same SSMS window.

This makes grouping scripts together easy, too (especially ones from different folders or projects).

Temp Table Structure

Related to the post on getting the name of a temp table, here's the code to get the structure:

SELECT * FROM TempDb.INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = OBJECT_NAME(OBJECT_ID('TempDb..#x'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))

I'm using this to create a stored proc to examine the contents of a temp table column-by-column, and record values that violate assumptions (e.g., is null), in an audit table. I'm adding this audit to several reporting procs, so creating this audit proc will save me a lot of work.

Create SQL Column with Variable Default

I wanted to create an integer column in a metadata table that would prioritize the rows of the table (this is for column [execution_order]), and for ease-of-use I wanted inserts into the table to default to a unique value. Although this sounds a lot like an identity column, I wanted it to be nullable, and not necessarily unique.

This is what I came up with:

CREATE TABLE [dbo].[metadata] (
[metadata_id] int NOT NULL identity(1,1)
,[project_id] int NOT NULL
,[descr] varchar(100) NOT NULL
,[delete_stmt] varchar(max) NOT NULL
,[execution_order] int NULL
,[insert_date] datetime NOT NULL
,[insert_userid] varchar(50) NOT NULL
,[is_active] bit NOT NULL
,CONSTRAINT pk_dbo_metadata PRIMARY KEY ([metadata_id])
)
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__insert_date
DEFAULT(GETDATE()) FOR [insert_date]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__insert_userid
DEFAULT(SUSER_NAME()) FOR [insert_userid]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__is_active
DEFAULT(1) FOR [is_active]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__execution_order
DEFAULT((IDENT_CURRENT('[db].[dbo].[metadata]')) * 100) FOR [execution_order]
GO