@knechod Length _IS_ what makes them different. In the vast majority of cases, you'll NEVER see problems with this. But because the lengths are different, SQL Server will sometimes treat the values and the data differently if you're trying to compare different 'sized' values that should, logically, be identical.

Rather than try and explain it, here's an example. (And it gets even more interesting if you're using numeric 'string's like '1245689'...)

SELECT NULLIF('test string','test string')

SELECT ISNULL(NULL,'A value that I want to replace nulls with.')

SELECT ISNULL(
NULLIF('test string','test string'), -- should be null
'A value that I want to replace nulls with.' -- should be the replacement
)

The results should be a bit surprising. And... if you imagine an index experiencing similar problems, you can see why (once in a blue moon) data type coercion (i.e. letting SQL Server figure out what data types to use because you weren't EXPLICIT enough) can look like from a performance standpoint...

--Mike

Michael6/18/2009 8:12:45 PM

@kbreneman You are correct sir. Long story short: I lost my mind with that one and I'm sorry everyone had to see it. (I started using a template a few years ago for this and made a few tweaks here and there to serve as an example... but forgot to make sure my optimizations for the template even made SENSE before dropping them off as an example in this article.)

My advice, though, still stands as this is a key consideration. Just, um, ignore my crappy syntax ;)

Michael6/18/2009 8:10:43 PM

I've always declared cursors in a way similar to the example in point #7, but I've not been including READ_ONLY because I've been under the impression that FAST_FORWARD already creates a read only cursor. Is this not true?

KEN5/8/2009 11:34:34 AM

Very comprehensive and useful article

Rogerio5/8/2009 8:03:37 AM

I must be one of thse developers who don't understand data type differences between varchar(20) and varchar(30). How are they different (other than length)?

Kevin5/7/2009 4:30:14 PM

One topic that would be very useful for DBA/developers is the usage of tempdb.Can you please comment:

Temp table usage:

Method 1: First dump data into a Temp Table and use the Temp Table in query:
----------------------------------------------------------------------------

select * into #TempTable1 from Employee where Dept = 'XYZ'



Use the #TempTable1 table in the query below:

Select....

from

join #TempTable1 Temp1
..
..
join #TempTable1 Temp2
where
....



Method 2: Use derived tables, instead of temp table
---------------------------------------------------


Select....

from

inner join (select * from Employee where Dept = 'XYZ') Temp1
..
..
inner join (select * from Employee where Dept = 'XYZ') Temp2
where
....



I was trying to avoid temp table usage, but looking at the reads with "set statistics io on", it seems using temp table does less reads. So, What is the best practice ? Do we always go by the READS ? OR Try to Avoid Temp Table as much as possible ? Please share your ideas as how do you decide ? I hope this discussion will be a very good resource for temp table usage.

Ranga5/7/2009 3:44:42 PM

Great article

Rob5/7/2009 3:27:27 PM

Gary,

You are correct - that .25/processor ratio is specifically for the tempDB, otherwise the 1/processor applies to user DBs. Sadly, given the tiny bit of space I had to work with, I just had to say .25-1/processor to be 'all inclusive'. Interestingly enough, I THOUGHT I had a link to that EXACT PSS post in my article.

That said... I've also written another article focusing solely on improving IO performance that should be getting published... .sometime soon - where I deal with this just a bit more.

--Mike
www.sqlservervideos.com

Michael8/28/2008 6:15:58 PM

Kurt,

Check out this article:
http://technet.microsoft.com/en-us/library/ms190673.aspx

Where there are a couple of cases where MS points out that AWE can, indeed, address physical memory below the 4GB limit (and above the 2GB non-AWE window).

Here's a quote:
Since AWE-mapped memory is supported below 3 GB, you can define the min server memory and max server memory values within the physical memory range, or use the default values for both options.

This article also points out that you can configure AWE on systems where the OS LIMITS you to only 4GB - which infers that you could (and would want to) set AWE on systems with as 'little' as 4GB of physical RAM.


That said, I agree - this is one of the most confusing aspects of SQL Server management and configuration. It's also worth pointing out that in the article I was really only talking about cases where I come in to an environment where there's some beefy hardware and can spot that AWE isn't configured correctly because task manager is only showing just under 2GB of RAM being used. In other words, I wasn't talking about setting AWE in those 2-4GB ranges either.

And um, cough, since I have to rate the article to submit comments... I'll um... rate it as a 5 /5 *grin*

Michael8/28/2008 6:07:31 PM

Hi Michael,
I'm not sure if your article specifically recommend to allocate to create 0.25 to 1 data file per filegroup per processor (point 9). If yes, I dont think i would agree with it as I feel this recommendation is only contained to tempdb.

PSS has specifically mention in their article stating this recommendation is not for user database as it does not usually have DDL contention, unless for whatever reason you're creating/dropping too many temp tables on your user database.

http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

Most people have this misconception of a worker allocated per data file in sql. Actually, this one worker per data file is i.e. when you create database different drive, hence, workers do the creation so the database may be created faster due to parallel work streams.

Please comment and reply to me on your thoughts.

Regards,
Garry

garry.mortimer@btfinancialgroup.com 7/21/2008 8:04:43 PM