Table Variables!

Praise be to the SQL Development team!

In previous versions of SQL Server, you could not insert into a table variable by executing a stored procedure, this would fail

insert into @table (blah, meh)
execute Proc2

So, you would have to create a temp table to accomplish it.

SQL 2008!

You can now execute a stored procedure to populate a table variable!

Go forth SQL kodars, create data and execute procedures!

one more normalization post

This is really starting to bug me. I have been arguing with an external group about some database design. Someone said to me, and I summarize here "Normalization does not depend on the data or the relationships between rows".

I was stunned, I sat back and reevaluated the last 10 years of my life. IF the data does not matter, why do I spend all of this time on it? Making tables, creating keys and constraints. With this new view on normalization, why would I ever use more than a few tables?

I think DBA1 (anonymous friend 1) put it best when I brought it up to him, "FAIL".

The statement that data is not important was so utterly bizarre that it borders on madness. Of course the data matters, it is data normalization!

Here's a new rule. People who have no background in IT, Development or Design should not be allowed to know anything about the database layer of an application.

it's been a while

We've gone on vacation and I've had at least one business trip. I have a couple things rattling in my brain for posts, but I have been writing a database standards document and have not formed anything concrete yet. I think the next post might be on why people who don't understand technology should not even be allowed in the building when tech decisions are being made.

Normalization

ok, there are books on it... rules about it... and no one really does it well

well except for me and a few exceptional dbas I know :)

I was remembering fun times at a previous company, here's some things they did to violate normalization.

1) The EMAIL column could be used for any type of data someone wanted, but then again so could address2 and a host of others. Here's an idea people, if you name a column something specific how about only that data goes in there?

2) SSN stored as an INT. Yes, your SSN is a number but did you know that some of the SSNs start with a 0? Well they do and if you put 098005550 into an INT column guess what happens. That's right, you lose the leading 0. So, do we change the column type? oh hell no, make the software count the values in the string and append zeros to the front of the string if it is less than 8 characters.

3) Don't use foreign keys, they cause errors if you try to delete data from a table that still has data in a child table. And it's hard to delete from tables in a specific order... GAH

ok enough ranting

Normalization is good, it is your friend

If anyone from the big C is reading, this is totally not a post about your company.

--matt

dynamic table names?

I saw a question on lazydba today that I wanted to post about. A person asked if it was possible to pass in a table name as a "bind" variable to a procedure.

If you're doing that, I assume you're trying to use it in a FROM or JOIN statement.

To accomplish it, you would have to use dynamic sql. Passing a table name in a variable won't work otherwise.

Why dynamic sql is evil!

1) You must grant privileges to underlying objects, execute on the procedure will not be sufficient

2) The query plan SQL stores will not be appropriate for every execution of the procedure. So you either have a crap plan or SQL has to recompile.

3) They are a giant PITA to troubleshoot. Inevitably, you end up pulling the dynamic statements out of the proc and running them manually to find the problem.

Avoid dynamic SQL like you would a rabid animal

--matt

SQL vs Oracle - temp tables

SQL vs Oracle

I ran into this a week ago and have not been completely happy with the Oracle solution. I am not saying that it doesn’t work, but it just seems like there is a better way. (oh look, there’s sql server doing it in a better way)

Here’s the skinny

Temporary tables.

I have been known to throw data sets around inside of a stored proc and SQL Server has this sweet little bit of trickery that lets me have a table variable. Oh yes friends, a variable that acts like a table. I can insert, update, delete and select data from it. It resides in memory and will usually never be written to disk.

Ok, quick history lesson for those of you not following. In SQL server, you can have temp tables and table variables. Temp tables are ALWAYS written to disk. Table variables are only written to disk if there is not enough memory to hold the data. So think about this, if I read from a few tables and put the data into a temp table and then read it back out, what am I doing? Read, write, then read. Your disks are just a spinnin! With a table variable (praise Bob!) it sits in memory, is only viable for the scope of the procedure and I am not spinning disks. Super groovy.

(just a personal opinion, but if you are returning so much data that your table variable needs to write to disk… maybe you should rethink what you are trying to do)

Back on topic.

So what do we have in Oracle land that can do the same thing you ask? Well in a word, nothing. The only alternative I can find is the Global Temporary Table. This is a physical table space created once and it sits there for you to use. Read from the disk, write back to the disk and read again. Ugh. It does have a nice feature though, it keeps session data separate. Session 1 can insert rows and retrieve them without the data being mangled and merged with data from sessions 2-900. Kind of nice, but it still writes to disk.

Coding procedures, especially ones where I have to nab a dataset and then join against it, is super easy when using table variables and the impact is pretty minimal. While global temp tables are not hard to use, they do have some tricksy properties.

If you are using a global temp table you need to be aware of the two different settings.

The basic syntax is:

CREATE GLOBAL TEMPORARY TABLE SOMETABLE ( SOMEID NUMBER, SOMEDATA VARCHAR2 (256) )

The next part if the syntax can be one of two settings

ON COMMIT DELETE ROW – This indicates the data should be deleted at the end of the transaction

Or

ON COMMIT PRESERVE ROWS – This indicates the data should be deleted at the end of the session

I personally use the PRESERVE since I use the temp table multiple times in a session / procedure and not just in one transaction.

In contrast, the table variable holds my data for the session.

I really do prefer the table variable to temp tables, writing data back to disk after I just got it out of there seems wrong to me.

Oh, here is the syntax for creating a table variable

Declare @SomeTable Table (SomeID INT, SomeData Varchar(256) )

--matt

Fun stuff

In the past few weeks I have seen the new GI Joe movie and read a new book, Zoe's Tale.

The movie was ok, as long as you don't expect much of a plot or acting.

The book however was enjoyable and I recommend it with a 3 out of 5 stars.

Oracle points

Alright, I have given SQL Server some nice kudos. Now it's Oracles turn.

There are two things in procedure creation that I wish SQL Server would do.

1) CREATE OR REPLACE - this handy little statement basically does a create or alter! No more looking to see if the procedure exists and then Drop / Create statements.

2 Probably my favorite Oracle coding trick so far! For variables in your procedures, you can assign a table.column data type instead of hard coding it.

Oracle -
P_SOMEVARIABLE IN SCHEMA.TABLE.COLUMN%TYPE

In SQL Server, we have to make sure the types in the procedures match up with what we code in the table. I know I'm not the only one who has made a VARCHAR (200) column and set the variable in the INSERT procedure to VARCHAR (100).

Nice work Oracle, that's two super sweet little items that actually help!

Scale out SQL Server

An interesting question was posed to me today. What's a good strategy for scaling out SQL Server?

My answer: It kind of depends on your situation, one of the best ways is to have hardware (active / passive ) clusters at multiple sites and replicate between them. Here are a few scale out options (in no particular order).

1. Peer to Peer Replication - Multiple servers across the planet can be used for read/write access and all of the data can be replicated to each server in the group. This is great if every server needs to have all of your data, you can scale that back a little and replicate only that data relevant to the site.

2. One great way is to separate out processes. OLTP goes to this server, ADHOC / Reporting goes to another server, batch processes go to a third and a fourth server is set apart for data warehouse staging. You can accomplish this with log shipping (still viable in my opinion) or one way replication. The key is to get the extra processing stuff that doesn't need to be on your OLTP server off onto a secondary system. Reduce the traffic your real time app needs to contend with.

3. Horizontal partitioning - There are plenty of articles out there about it, but simply put you break apart your data into manageable chunks and place it on different database servers.

I am not aware of any practical way to have Active Active SQL databases. Unless you are partitioning your data to different databases and then putting them in a 2 way cluster. That scares the willies out of me though, since when you fail one of the boxes over you will be putting the load of both databases onto one machine.

Fight the stupid

KODARS!

Developer, "So, I want to store a bit string for a user. This bit string will define what web forms the user has access to. How do we store that in the database?"

DBA, "We won't be storing it that way. If you want to define relationships between a table of users and a table of web forms with permissions and dates and levels and whatever, that would be great. We will not be storing a bit string."

Developer, "But, it's easier for me to do it that way!"

DBA, "Ok, how about this. If you store a bit string I get to create a job that runs every other tuesday changing the 3rd bit of your string to the letter Q and deleting everything after the 9th bit. How does that work for you?"

Developer, "Well no, that would be bad"

DBA, "Yes, so is your idea. "

Yeah, this really happened.

I think I'll take a nap now.


--matt
Fight the stupid

Stored procedure fight with Oracle

So here is a simple issue, I want a stored procedure to return a data set. I want to pass in parameters and have those parameters be used as criteria to filter the data returned. Not a rocket science request, hell not even a bottle rocket science request. Here's how you do it in SQL Server:

Create Procedure dbo.SomeTableSelect
( @SomeID INT ,
@SomeValue Varchar(50)
)
as

Select SomeID,SomeValue,SomeDate
From dbo.SomeTable
Where SomeID = @SomeID
and SomeValue = @SomeValue

Pretty simple and easy to do right? Well transfer that to Oracle. Guess what, it doesn't work that way! In Oracle, you can pass in parameters but you have to use a cursor to get back the data. What's really fun, is if you want to run the procedure, you have to declare a cursor so that the cursor inside the procedure can write data out. GAHH!

Oracle code:

CREATE OR REPLACE PROCEDURE SOMETABLESELECT
(
P_SOMEID IN NUMBER,
P_SOMEVALUE IN VARCHAR2,
P_OUT_REF OUT SYS_REFCURSOR
)
AS
BEGIN

OPEN P_OUT_REF FOR
SELECT SOMEID, SOMEVALUE, SOMEDATE
FROM SOMETABLE
WHERE SOMEID = P_SOMEID
AND SOMEVALUE = P_SOMEVALUE;
END;

Some things to remember in Oracle

1) Oracle is case sensitive! Always name your objects with ALL CAPS, it will not find the procedure SomeTableSelect, no matter how much you yell at it that the stupid thing is right there.
2) Always use a BEGIN and END, you have to actually or the syntax will be invalid.
3) Remember your ";" this ends commands in Oracle and you need it.
4) Oracle will let you create a procedure that is completely invalid, yipee.

Oracle doesn't actually hate to return data, but it seems to assume that you will only ever ask for a single row. Many examples of how to return data have you putting the result set into a group of out output variables, which means you could only return a single row.

In conclusion, I think SQL Server wins this round. The ability to extract data from your database seems pretty simple and direct. SQL Server makes it easypie and Oracle seems to think it's funny to make me jump through hoops.

--matt
stop the stupid!

Catching up...

It's probably best to bring the world up to speed on what this blog is for. Sharing funny stories about life in IT and Development will be a top priority, but I also want to provide a place for people to ask questions and get answers.

I'll have a few types of posts here for your reading pleasure.

Stupids - how other members of development or IT hurt my brains

Kodars - how code can hurt the database server

Code snippets - fun and helpful SQL (or pl sql) based code to help you

SQL vs Oracle - ok ok there is probably a place for them both in the world, but there are some things that just drive me insane. Sharing the pain and finding the appropriate solutions to every day problems.

Fun stuff - movie, book, tv and other reviews that will keep us from spending too much time on technology.

-- matt
Fight the stupid!

SQLvsOracle

Welcome to the SQL vs Oracle blog! I have been working with MS SQL Server for over 8 years now and have much love for it. Recently I have been thrown into the deep dark abyss of Oracle 10i and have found some wondrous things that just don't make sense. Join me and a few friends as we traverse the Oracle landscape and try to keep ourselves from going insane.... well more insane.