SQL Saturday in Redmond, WA

I just signed up to speak at SQL Saturday in Redmond on October 3rd. I will be presenting two sessions:

• Exploring SQL 2008’s Transparent Data Encryption
• Exploring SQL Server Column-Level Encryption
  

I really enjoyed the SQL Saturday here in Portland and look forward to Redmond.

[Not SQL-related] Networking Through Existing House Wires

I seldom write about non-SQL topics, but this product was just too great. Last night I purchased and installed a Linksys Powerline AV Network Kit (model PLK300).

Problem: We moved to a new house about two months ago. Our wireless (N) router is at one end of the house on the main floor. I was trying to connect a computer in the basement on the other end of the house. That room receives zero cell service, and I found that the wireless signal was too weak. I purchased two different receivers, but neither worked.

Solution: After talking with someone at Best Buy about what options we had, we purchased the "Powerline Network Kit". It routes the network signals over your home's existing power wires.

Installation was dead simple. Plug one unit into the router; plug the other unit into the receiving computer. They both plug into the regular wall outlet. Bingo. It worked. The only thing I had to do was plug both into the outlet without going through a surge suppressor. That was explained in the brief installation instructions.

The receiver has four ethernet ports, but I'm only using one. The speed seems quite good.

Nice product. Great solution to my problem.

[Slight] Caveat: The little stands are a waste. The units are so light that the cables make them fall over. Pitch the stands and just lay them down flat. That works great.

Table Variables and Transactions

Did you know that table variables are not affected by ROLLBACKs? After I discovered it in my testing, I searched Google and found many references to it. Apparently everyone knew about this besides me.

Here is a simple example:

-- Create two tables: one a temp table and one a table variable
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t  (Col1 int NOT NULL)
DECLARE @t TABLE (Col1 int NOT NULL)

-- Begin a transaction
BEGIN TRAN

-- INSERT two values into each table
INSERT INTO @t VALUES (1)
INSERT INTO @t VALUES (2)
INSERT INTO #t VALUES (1)
INSERT INTO #t VALUES (2)

-- ROLLBACK and check values
ROLLBACK TRAN

PRINT '#t'
SELECT * FROM #t
PRINT '@t'
SELECT * FROM @t

-- Just in case
IF @@TRANCOUNT > 0 ROLLBACK TRAN

Result:

#t
Col1
----

@t
Col1
----
   1
   2

The table variable (@t) is not affected by the ROLLBACK.

References:

• Comparing Table Variables with Temporary Tables
• Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance
• Table Variables In T-SQL

"dt_*" Stored Procedures

I was recently trying to find all objects in our production databases that had GRANTs to public. I found a number of "dt_*" stored procedures. In researching them, I found some outdated information.

This post states that "They are safe to delete, provided you are not using full-blooded source control and Visual Studio integration, and you do not have any diagrams that you want to keep around."

I won't comment about the VSS issues, but the diagram issues appear to be outdated. (That post is from March of 2005.)

There is similar information in this post. Jens Suessmeyer, a moderator, comments, "Yes, if you need created database diagrams you should leave the dbpoperties.[sic]" My assumption is that he meant the dtpoperties table. In another post, Steve Twitchell states,

You can delete the dt_* procedures if you're sure no one is using the database designer, table designer, view designer, or query designer in Visual Studio, SQL Server Management Studio, or SQL Enterprise Manager. The database tools functionality will recreate them (after prompting for permission) if it thinks it needs them. If you delete the dt_* tables, you'll delete all the database diagrams stored on the server.

Time to Test

In SQL Server 2005 (Developer Edition), I first created a new database and checked for stored procedures. No diagram-related SPs existed. I right-clicked on the "Database Diagrams" folder and got a message about the database not having support objects. I clicked Yes to create them.

I then created a diagram and re-checked for new objects. I found a dbo.sysdiagrams system table and seven dbo.sp_*diagram* system stored procedures.

These objects appear to be the SQL Server 2005 versions of the old dt_ objects.

Conclusion

This does not answer all possible questions regarding dt_ stored procedures or their SQL Server 2005 equivalents, but hopefully it is useful.

As always, test in your own environment. For us, we're going to remove all the dt_* procedures.