I have a couple of the top of my head

1) replacing a cursor with a while loop, thinking you are not using a cursor thus making it faster. Meanwhile you are still processing per row

2)Thinking you can trap everything with try catch

run this to see what I mean

BEGIN TRANSACTION TranA

BEGIN TRY

DECLARE @cond INT;

SET @cond = 'A';

END TRY

BEGIN CATCH

PRINT 'a'

END CATCH;

COMMIT TRAN TranA

This transaction will blow up with the following message

Server: Msg 3930, Level 16, State 1, Line 15

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Server: Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

We can use XACT_STATE() to check if we need to rollback or not without blowing up. I also used the ERROR_MESSAGE() function to print the error

BEGIN TRANSACTION TranA

BEGIN TRY

DECLARE @cond INT;

SET @cond = 'A';

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE();

END CATCH;

IF XACT_STATE() =0

BEGIN

COMMIT TRAN TranA

END

ELSE

BEGIN

ROLLBACK TRAN TranA

END

After running that we can see that the following message was printed

Conversion failed when converting the varchar value 'A' to data type int.

We trapped the error, rolled back the transaction and the transaction did not blow up

3) a clustered index guarantees order (see Hugo's post (first link in my best posts of the year))

4)Thinking that by changing exec(@sql) to sp_execute(@sql) you are more efficient

You need parameters for this to work obviously

5) I posted that here on sqlblog before: a truncate cannot be rolled back

So where is table variable stored actually. In tempdb?

Table variables *can* be stored in memory, but it is not guaranteed.  Otherwise, yes, they will utilize tempdb.

Avinash,

Both table variables and temp tables are stored in tempdb.

If they are small enough to fit in cache, they'll also be in cache for as llong as they live. And if they live short enoough to be removed before the next checkpoint, they're never even actually written to tempdb's storage - but all this goes for permanent tables as well, this is just how SQL Server manages its cache.

maybe the contents of table variables can be cached, but rows written to table variables are always written to the tempdb's tlog, which means there is always disk access in the tempdb with table variables.

Here's another myth:

"You can't put any indexes on a table variable"

here is a big myth: you can separate clustered indexes from tables

I have seen several times a setup like this

1 drive for the log files

1 drive for the data (tables)

1 drive for the clustered indexes

1 drive for the non clustered indexes

One  drive will always be very empty of course

Here another myth, perpetuated by Paul in his previous comment

Myth: Lazywriter is responsible for writing dirty data to disk.

Fact: The process most responsible for writing dirty data to disk is the Checkpoint Process. Individual user processes will sometimes also write to disk. Lazywriter is responsible for keeping a sufficient number of pages in the freelist, and only if memory pressure is experienced, and a number of other conditions are met, LW might do some writing to disk.

One of my favorites: Nullable columns don't take up space/memory if they have no values. We have a third-party production system that is built around a HUGE number of bad choices re: data types, normalization, and allowing nulls. As a result, about 30-40 gigs of real information takes 120 gigs to store, and where it ought to run comfortably with 4 GB of RAM, it totally exhausts a server with 8. Instead of a modest server with a few drives, it demands a SAN with 20 fiber channel disks. Interesting case study about what not to do.

Myth: clustering on monotonically increasing identity keys reduces logical fragmentation

Fact: monotonically increasing identity keys can produce near perfect logical fragmentation, meaning that the data is in essentially random order, save for the chronology in which it was inserted. This is a particularly stubborn myth because this massive fragmentation is completely "masked" by the available tools. That is to say, if you run some analysis like DBCC showcontig, it looks like the the data is not fragmented at all, while you still face most of the performance problem caused by storing data in random order.

To be a little more generous, some of these 'myths' are actually based on behaviors that used to be facts. The product changes with each version, and it's hard enough to learn about all the brand new features, without also going back over all the things you thought you already knew to find out which are still true.

For example, prior to SQL 7, NULL columns did not take any space.

Kalen - ah, interesting. I didn't realize that. My green showing :-)

No "truncate table can't be rolled back myth" ?  Thats probably the biggest one I have seen perpetuated through internet articles and blogs :)

@Merrill,

The NULL column take up space is aleviated to some degree with SQL 2005 SP2 (Ent. only) with the vardecimal data type.  I have used it to shrink data warehouses 30-40% in size.  The I/O overhead saved sometimes eclipses the overhead to maintain the vardecimal leading to big performance gains, not just space saved.  (just like an article here a few weeks ago that showed SQL 2008 compression I/O saved > de-compression overhead) SQL Server 2008 builds on that.

@Denis,

I like ur one about the splitting up the objects to four drives.  I just read a couple articles recommending that very same thing.   The try/catch one I didn't know about either.

@All,

I think this would be an interesting project to maintain in a TDD test that we could run to see how these myths stack up in 2005/2008 and going forward :)  Like Kalen mentioned stuff changes over the versions and it is way to much information to keep in the back of ur head with all the different versions of SQL (especially now that they will be coming every 2 years)...and I don't even consider myself a SQL guy as a primary language.

@Denis:

As you know, truncate is also a logged operation, but in a different way. It logs the deallocation of the data pages in which the data exists. So rolling back is possible as long as it happens within a transaction.

http://vadivel.blogspot.com/2006/12/rolling-back-truncate-operation.html

@Paul and Kalen,

Part of the propagation of the CHECKPOINT myth comes right out of Microsoft's own curriculum, where for at least 10 years, every entry-level SQL trainee comes out of a MOC class having been told that only committed transactions are written to disk. It's up to the poor trainer to 1) know the actual information and 2) to convince the delegate that the book is wrong. Argh!

Hi Chris!

My comment was not about CHECKPOINT writing uncommitted changes. Mine was about whether it's the LAZYWRITER or CHECKPOINT writing pages to disk at all (committed or not). The myth about  only writing committed changes is a completely separate issue. I am well aware of the MOC material. For many years, every time a new course came out with this same horrendously misleading information, I went to the management of the curriculum group at Microsoft and complained. For a while I was told they would fix it. Then finally, one of the managers told me point blank, "I know it's wrong, but we write it this way because it's easier to understand".  (!!!) That's about the time I decided to stop teaching MOC and only teach my own courseware where I would have total quality control.

This doesn't mean my material is 100% error free, but as soon as I realize any errors, I fix it and the next time I teach that error is gone.

How did I forget this one? The famous memory leak 'issue'

this question was asked here today

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2632022&SiteID=1

"when i'm consistantly updating database on the server, I've noticed that the memory usage goes higher and higher. How can I prevent this? Or is there a way to prevent this other than turning off the computer time to time?

thank you,"