Database DEnormalization and Violating Simplicity Principles

Timothy Claason, at SQL Server Central, writes:

"Denormalization is not a design strategy. It is a design work-around. Well
normalized databases represent a good design strategy, but can often lead to a
great deal of complexity when it comes to support, maintenance, and new
development. A well designed database can mean that, in order to get specific
data you need, you need to go through 5, 10, or even more tables which represent
the data you're looking for. Though there are many solutions to this dilemma,
such as virtual tables (views), programmatic solutions, temporary tables, and
more, I think it's important to not discount the value of well-placed
denormalization in the database. The intent of this article is to consider some
use cases for denormalization, and from those use cases, assert some
generalizations about when and why to use denormalization."

Continued

SQL for Running Totals: The Power of Cross Join


SELECT
t.transaction_date, t.amount, t.[description], sum (t2.amount) as RunningTotal
FROM
cash_transaction_table t
CROSS JOIN cash_transaction_table t2
WHERE
(t2.transaction_date <= t.transaction_date)
GROUP BY
t.transaction_date,
t.amount,
t.[description]
ORDER BY
t.transaction_date


Too Many Stored Procedures

Is there such a thing as Too Many Stored Procedures?
Jeff Atwood at Coding Horror makes some great points that reflect my own thoughts over at:
http://www.codinghorror.com/blog/archives/000117.html

I think SQL Server's Object Explorer needs to have a built-in mechanism for managing the thousands of stored procedures that occupy a database, instead of trusting that a sensible naming convention will be used.  In my experience, eventually any naming convention breaks down after time, as new developers and technologies arrive.

Linked to in Atwood's post is this by Frans Bouma, who seems to be the main promoter of cautionary use of Stored Procedures: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

T-SQL Searching through the schema

For SQL Server 2005:

---

Some examples:
select * from sys.objects
select * from sys.sql_modules <-- Stored Procedures and Views
select * from sys.columns
select * from sys.triggers


Searching the text of all stored procedures:
select o.name from sys.sql_modules m
inner join sys.objects o
on m.object_id = o.object_id
where m.definition like '%string you are looking for%'

Searching for tables that contain the column (field) you want:
select a.name
from sys.objects a
inner join sys.columns b
on a.object_id = b.object_id
where b.name='column name you are looking for'

For SQL Server 2000:

---

Searching the text of all stored procedures:
select object_name(id)
from syscomments
where objectproperty(id,'IsProcedure') = 1
and [text] like '%string you are looking for%'

Reqular Expressions and SQL

FIND: ^\(.+\)$
REPLACE: \0 = formToSql("\0")

Getting index value of new record created

In your T-SQL stored procedure, after you insert the new record into a table that has an auto-incrementing index, use the following:

SELECT NEWID = SCOPE_IDENTITY()
OR (if using pre 2000 sql server)
select returnval = @@identity

You can retrieve the value in the following way:
set rs = con.Execute(strSql)
yournewID = rs(0)