THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

www.SQLServerBible.com

SQL Myths

It’s amazing what people believe. Here are some SQL beliefs or strange ideas that I’ve encountered in the past year.

·        The column order isn’t important, so long as the column is in the index anywhere, SQL Server can use that column to optimize a where clause. (This one came from a system with only one table (about 25 columns), and it had only one index – a 15 column clustered PK)

·        Table variables are only in memory (a common misconception)

·        We have to run Enterprise Edition because we need 4 CPUs (thinking they need Ent Ed when Std will do is another common misconception)

·        You can pass an array into a proc using dynamic pointers (this one from a MCS SQL consultant!)

I’m sure you've heard a few as well, so please add to the list…

Published Friday, December 28, 2007 6:17 PM by Paul Nielsen
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

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))

December 28, 2007 7:39 PM
 

Denis Gobo said:

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

December 28, 2007 7:43 PM
 

Avinash Goyal said:

So where is table variable stored actually. In tempdb?

December 28, 2007 11:31 PM
 

Aaron Bertrand said:

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

December 29, 2007 1:31 AM
 

Hugo Kornelis said:

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.

December 29, 2007 5:52 AM
 

Greg Linwood said:

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.

December 29, 2007 6:46 AM
 

David Markle said:

Here's another myth:

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

December 30, 2007 3:23 PM
 

Denis Gobo said:

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

December 30, 2007 4:30 PM
 

Paul Nielsen said:

Denis,

> replacing a cursor with a while loop

I've started calling this anti-technique a Surrogate Cursor ;-)

-Paul

December 30, 2007 5:17 PM
 

Paul Nielsen said:

Myth: only committed data is written to disk.

Fact: dirty pages, comitted or not, are written to disk by the LazyWriter and other processes.

That's why the Recovery Process may need to roll-back uncommitted transactions from the data file.

December 30, 2007 5:18 PM
 

Kalen Delaney said:

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.

December 31, 2007 1:56 AM
 

Merrill Aldrich said:

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.

December 31, 2007 1:35 PM
 

Merrill Aldrich said:

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.

December 31, 2007 1:51 PM
 

Paul Nielsen said:

Kalen, Thanks for the clarification. I owe you one.

---

Hi Merrill,

so true!

Maybe it's not a pure SQL Server myth, but...

IT Managers tend to believe that Off-The-Shelf databases are better designed than custom apps.

fact: most OTS, or ISV apps have horrid databases.

AND

myth: denormalizing a database gives improved performance

fact: denormalizing means a larger database and extra code to keep the values consistent or to normalize data for set-based queries. most common result: a slower, bloated, more complex database.

There’s wisdom in the fact that Word wants to autocorrect "denormalize" with "demoralize" !  

December 31, 2007 1:57 PM
 

Kalen Delaney said:

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.

December 31, 2007 3:27 PM
 

Merrill Aldrich said:

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

December 31, 2007 3:55 PM
 

Bart Czernicki said:

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.

January 1, 2008 1:31 PM
 

Vadivel said:

@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

January 1, 2008 1:55 PM
 

Chris Randall said:

@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!

January 1, 2008 6:08 PM
 

Kalen Delaney said:

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.

January 1, 2008 9:38 PM
 

Denis Gobo said:

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,"

January 4, 2008 12:13 PM
 

Paul Nielsen said:

here's another one, mentioned to me by a DBA admin (not db developer type)

myth: Not every table needs a Primary Key. He belives there may be business requirements that prohibit placing a primary key on a table.

fact: Even though SQL Server allows "create table" without a primary key, that doesn't mean it's really a table by definition. No PK, It's just a mess of data, it's not really a table.

January 9, 2008 9:28 PM
 

Kay said:

I like to ask about myth #2, XACT_STATE().

Is it ok to use the same stored procedure skeleton for cascading stored procedure calls?

i.e. S.P. A calls S.P. B. If we use the same skeleton to write both s.p., and failure occurs in s.p. B, we can still obtain the error code and message by calling s.p. A?

THANKS MUCH!

July 29, 2008 6:54 AM
 

Paul Els said:

I wrote this in the beginning of last year on another site: http://www.simple-talk.com/sql/database-administration/dml-trigger-status-alerts/

Maybe you can tell me how you handle the fact that SQL 2005 Try Catch doesn't handle schema changes... The SQL 2005 Try Catch is a very welcome addition (at least in concept - since it simplifies coding errorhandling tremendously) to tsql, but we've noticed that the Catch does not catch everything. In short I was wondering how other dbas/tsql developers are handling of the unhandled errors. This is especially true where code resides in a SP and the underlying schema changes. The code that is already compiled (SP) does not know that about the pblm until it is executed. Furthermore, the Catch in the SP does not catch the error.

-- It is really easy to reproduce

-- using these 7 steps

-- This is a quick test to reproduce that

-- Catch of Try Catch on tsql

-- does not catch (handle) everything.

-- This is serious, coz in our mission critical

-- systems we simply cannot rely on it

-- Step 1 - Create a table on a some_dummy_db and add 2 records:

use some_dummy_db

go

create table testtable (Col1 int, Col2 varchar(20))

insert into testtable values (1, 'val1')

insert into testtable values (2, 'val2')

-- Step 2 - compile a sp that will result in

-- a handled error when executed

create proc usp_illistrate

as

BEGIN

begin try

begin tran -- starts a transaction

-- this shows something that will work

update testtable set col2 = 'val3' where col1 = 1

-- now force an error that will be caught

select 1/0

-- this shows something that will not be caught

update testtable set col2 = 'this error is not caught'

commit -- this will not be reached

end try

begin catch

print 'in (catch) error handler'

IF @@TRANCOUNT > 0

ROLLBACK

DECLARE @ERRMSG NVARCHAR(4000), @ERRSEVERITY INT

SELECT @ERRMSG = ERROR_MESSAGE(),

@ERRSEVERITY = ERROR_SEVERITY()

RAISERROR(@ERRMSG, @ERRSEVERITY, 1)

end catch

END

-- STEP 3 - Execute the SP that will result in a handled error

-- this returns this error because of the forced error:

-- Divide by zero error encountered.

-- notice you know for a fact you're in the error handler

-- because the first msg is: "in (catch) error handler"

-- and then "Divide by zero error encountered."

EXEC usp_illistrate

-- STEP 4 - Now recompile the SP,

-- to show that compiled code may not catch

-- errors as a result of underlying schema changes.

-- Notice that I have just removed the forced error

alter proc usp_illistrate

as

BEGIN

begin try

begin tran -- starts a transaction

-- this shows something that will work

update testtable set col2 = 'val3' where col1 = 1

-- this shows something that will not be caught

update testtable set col2 = 'this error is not caught'

commit -- this will not be reached

end try

begin catch

print 'in (catch) error handler'

IF @@TRANCOUNT > 0

ROLLBACK

DECLARE @ERRMSG NVARCHAR(4000), @ERRSEVERITY INT

SELECT @ERRMSG = ERROR_MESSAGE(),

@ERRSEVERITY = ERROR_SEVERITY()

RAISERROR(@ERRMSG, @ERRSEVERITY, 1)

end catch

END

-- Step 5 - this simulates someone changing

-- the underlying table schema

-- by renaming a field

EXECUTE sp_rename N'dbo.testtable.Col2', N'Col3', 'COLUMN'

-- Step 6

-- this just retrieves the records so that you

-- can see that col2 is renamed to col3

select * from testtable

-- Step 7

-- now see how the catch does not catch the error

-- as you don't receive the 1st msg from

-- the error handler, namely "in (catch) error handler"

EXEC usp_illistrate

February 10, 2009 3:57 AM
 

GR said:

How do you put any index on a table variable? I get a syntax error on the following?!

declare @table table (i int not null, s varchar(20) not null)

CREATE INDEX IDX_1 ON @table (i)

<quote>

David Markle said:

Here's another myth:

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

</quote>

December 16, 2009 2:10 PM
 

mbourgon said:

<quote> How do you put any index on a table variable?

</quote>

declare @table table (i int primary key, s varchar(20) not null)

Because of the PK, it creates a clustered key.

July 1, 2010 2:28 PM
 

mbourgon said:

Myth I've heard (and propagated over the years):

if you have multiple indexes on 1 table, the optimizer can use them together.  This was in Inside SQL Server 7, IIRC.  Practically, it never does that.  

July 1, 2010 2:34 PM
 

Ray said:

You said "The column order is important". Where can I read more about it? I can't find any information about this on google or any other sql related website. I would like to know why the order matters and how I could optimize the order of my tables.

August 2, 2010 2:59 PM
 

Paul Nielsen said:

Hi Ray,

Column order in the table does not matter at all, but hte order of columns in a index is important. For example, if the index was (LastName, FirstName) and you searched for FirstName = 'Ray' you'ld have to scan the entire table, but if the was index (fristname, lastname) then searching for FirstName = 'Ray' woudl be a near instant index seek.

-Paul

August 2, 2010 3:24 PM
 

Paul Nielsen said:

mbourgon,

Assuming index1(col1) and index2(col2), and Where col1 = 'A' and col2 = 123, SQL server will do an index seek on each of the two indexes and then merge join the results of the two index seeks. So it does use the two indexes, but not as efficiently as a single composite index.

-Paul

August 2, 2010 3:32 PM
 

MrTintin said:

@Ray

Column order - if you have a multi column index, where your query uses all columns (or at least more than the first), then the best order is the order of decreasing selectivity. So, if you have a column of cities with 500 entries and a column of counties (states) with 50, put city first in the index as that filter will produce the shortest list (approx 1/500 of the total); then county. Putting county first would only reduce the list to 1/50 - ie 10 times the size of the other option - and that's less efficient.

December 13, 2010 9:38 AM
 

Sunny Singh said:

Hello I have a question regarding the database error "Transaction count after EXECUTE

indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.)"

The scenario is we are having a JAVA-J2EE based web based application for which sqlserver 2005 is the database.

I have a stored procedure which executes some dml statements and having  explicit begin transaction with try and catch block.I am getting the above error on Java side whenever sql exception is raised in stored procedure.When debugged the sp from db I am getting "conversion error issue",This eror is now getting modified to the "COMMIT or ROLLBACK TRANSACTION error" so My question is who is at fault the stored proceure the way we have written the excoption handling logic or is it the java?Let me know if you reqire any more inputs to shed some light on it.

March 8, 2011 6:37 AM
 

George said:

//Shouldn't this be:

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

//This:

BEGIN TRANSACTION TranA

BEGIN TRY

DECLARE @cond INT;

SET @cond = 'A';

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE();

END CATCH;

IF XACT_STATE() = 1

BEGIN

COMMIT TRAN TranA

END

ELSE IF XACT_STATE() = -1

BEGIN

ROLLBACK TRAN TranA

END

ELSE

BEGIN

PRINT 'no transaction and a commit or rollback operation would generate an error'

END

October 26, 2011 12:37 AM

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement