Help me win this bet

You might know that some friends and myself have launched a site named LessThanDot, this is a site where users can collaborate via the wiki, ask questions in the fora and can read out blogs. It is a technical site but we do have a forum where you can post pretty much anything you want(not vulgar or offensive). Here are some examples
The Rage of the Previously Rich
We're In Ur Bank, Bailing It Out
Girls Habanero Eating Contest

Of course we do have our technical forums, some examples
Microsoft SQL Server
ASP.NET
Tech Rants

So I made a bet with the other owners of this site that I will have the most people sign up until November 1st. So please sign up and let your coworkers/friends know and let them sign up too, after all you don't want me to lose or do you?


Don't be scared to participate in the forums :-)

My username is SQLDenis

I tried to keep it a secret but it is all over the internet: I am a SQL Server MVP

So all over the internet is exaggerated, I became a SQL Server MVP today and was not going to do a blog post about it. However some other people had other ideas

Congratulations, Denis!
SQL Server MVP - Denis Gobo

and even an announcement on lessthandot

My profile is here: https://mvp.support.microsoft.com/profile=BCCF7416-DA4E-4D73-83E2-65FD61BAB16D

Stay tuned, I will have an interview with Jamie Thomson tomorrow

Make Your Case Sensitive Searches 1000 Times Faster

I had an case sensitive update query that ran in about 9 minutes. Since it was a case sensitive query it did an index scan not an index seek.
Once I modified my WHERE clause the update took a little less than 3 seconds

Let's get started and see what I did

First create this table

CREATE TABLE #CaseSensitiveSearchTemp (Val CHAR(1))

INSERT #CaseSensitiveSearchTemp VALUES('A')
INSERT #CaseSensitiveSearchTemp VALUES('B')
INSERT #CaseSensitiveSearchTemp VALUES('C')
INSERT #CaseSensitiveSearchTemp VALUES('D')
INSERT #CaseSensitiveSearchTemp VALUES('E')
INSERT #CaseSensitiveSearchTemp VALUES('F')
INSERT #CaseSensitiveSearchTemp VALUES('G')
INSERT #CaseSensitiveSearchTemp VALUES('H')

Now we will insert some lowercase characters

INSERT #CaseSensitiveSearchTemp
SELECT LOWER(Val) FROM #CaseSensitiveSearchTemp

Now we will create our real table which will have 65536 rows

CREATE TABLE CaseSensitiveSearch (Val VARCHAR(50))

We will do a couple of cross joins to generate the data for our queries

INSERT CaseSensitiveSearch
SELECT t1.val + t2.val + t3.val + t4.val
FROM #CaseSensitiveSearchTemp t1
CROSS JOIN #CaseSensitiveSearchTemp t2
CROSS JOIN #CaseSensitiveSearchTemp t3
CROSS JOIN #CaseSensitiveSearchTemp t4


This should give you 65536 rows

SELECT * FROM CaseSensitiveSearch


Create an index on the table

CREATE INDEX IX_SearchVal ON CaseSensitiveSearch(Val)


This is how you do a case sensitive search

SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS



Now hit CRLK + K (SQL Server 2000) or CRLK + M(SQL Server 2005)

run these 2 queries in one batch by highlighting them both and hitting F5

SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS

SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'ABCD'

Look at the execution plan, I get 98.71% for the first query and 1.29% for the second query. Just by adding the AND condition SQL server is able to do an index seek and run the query many times faster




Now try it with a lowercase a

SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS

SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'aBCD'


You see it all works without a problem, the correct result is returned

SQL Server Podcast: James Luetkehoelter Talks About Disaster Recovery Planning

SQL Down Under has made available their latest podcast. From the site:

Announcing show 23 with SQL Server MVP James Luetkehoelter. In this show, James discusses disaster recovery planning and technology for SQL Server, clustering, log shipping, mirroring and snapshots.

Dowmload it here: http://www.sqldownunder.com/

Microsoft Announces Small Business Developer Center

To help address the needs of the small business developer, Microsoft designed a web site based around the top tasks that these developers are faced with regularly: integrating data between systems, building reports, building small web and windows LOB applications. The Small Business Developer Center is focused on providing practical, actionable platform guidance for developers working with small businesses.

Here are some links to get you started:

Integrate Data
Efficiently extract customer data that exists in various formats, clean it up and transform it into a consistent format that can be imported and utilized for reporting.

Create Reports
Take full advantage of Microsoft Office Excel and SQL Server Reporting Services to give your customers powerful reports – including pivot tables, charts and interactivity – that they can update and share internally or online.

Build Web Applications
Quickly develop static Web sites for customers in need of a simple online presence or leverage the ASP.NET platform and Visual Studio to create dynamic, interactive Web sites with deep customization.

Build Windows Applications
Create applications that small businesses can run on their existing Windows-based infrastructure, enabling them to manipulate and then synchronize data both internally and externally.

Deploy and Secure Your Applications
Ensure the Windows and Web applications you develop can be fully utilized by your customers once deployed and that data housed in an online environment is well-secured.

Video: Secrets to Fast Detection and Recovery from Database Corruptions

How can you tell whether your data is corrupt? If you have corruption, how do you work out what's wrong with the database? How do you ensure you have a valid backup? If you don't have a valid backup, how and what do you repair? If you do have a backup, how do you work out whether you should restore or repair? And at what granularity? How do you go about determining what went wrong in the first place? It's all about limiting downtime and data-loss when a corruption occurs—from knowing the tools, to understanding the choices, to planning a successful strategy. Some of the features discussed in this session are: torn-page detection and page checksums, IO read-retry, backup checksums, consistency checks (DBCC CHECKDB and related commands), and database repairs. Facing database corruption is almost inevitable in every DBAs career—make sure you're prepared when it happens to you.

Watch the video here: http://www.microsoft.com/emea/itsshowtime/sessionh.aspx?videoid=549

How To Check If A Temporary Table Exists

How do you check if a temp table exists?
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL
Let's see how it works


USE Norhtwind
GO

--Create table
CREATE TABLE #temp(id INT)

--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

--Another way to check with an undocumented optional second parameter
IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END



--Don't do this because this checks the local DB and will return does not exist
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END


--unless you do something like this
USE tempdb
GO

--Now it exists again
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

--let's go back to Norhtwind again
USE Norhtwind
GO


--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

now open a new window from Query Analyzer (CTRL + N) and run this code again
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END


It doesn't exist and that is correct since it's a local temp table not a global temp table


Well let's test that statement
--create a global temp table
CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END

And yes this time it does exist since it's a global table


Cross-posted from http://sqlservercode.blogspot.com/

NULL and SQL Problematic?

There seems to be a lot of confusion on newsgroups about NULLs and how they behave.
Before I start I would like to point out that all the code will behave this way if ANSI_NULLS is set to on not to off

First create these 2 tables

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)

Now run these queries and you will understand why NOT IN should never be used

--We get back value 1 here
SELECT * FROM testjoin WHERE ID IN(SELECT ID FROM testnulls)

--Nothing is returned because testnulls contains NULL values
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)

--Value 3 is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)


--value 3 is returned
SELECT * FROM testjoin j
WHERE NOT EXISTS (SELECT n.ID
FROM testnulls n
WHERE n.ID = j.ID)


--value 3 is returned
SELECT j.* FROM testjoin j
LEFT OUTER JOIN testnulls n ON n.ID = j.ID
WHERE n.ID IS NULL


Counts are also tricky with NULLS

--a count of 3 is returned
SELECT COUNT(*) FROM testnulls

-- a count of 2 is returned, the count ignores NULL values
SELECT COUNT(id) FROM testnulls

--By using coalesce the count is also 3
SELECT COUNT(COALESCE(id,0)) FROM testnulls

--all 3 rows are returned
SELECT * FROM testnulls

The WHERE clause is also something that people have problems with

-- 1 row is returned
SELECT * FROM testnulls
WHERE ID = 1

-- only 1 row is returned the row with the NULL value is ignored
SELECT * FROM testnulls
WHERE ID <> 1


-- Now both rows that are not 1 are returned
SELECT * FROM testnulls
WHERE ID <>1
OR ID IS NULL

-- Now both rows that are not 1 are returned also
SELECT * FROM testnulls
WHERE COALESCE(ID,0) <> 1

Some more NULL Fun
You can''t compare NULL with anything
Since both values are unknown even a comparison with another NULL is unknown

DECLARE @v INT
DECLARE @v2 INT

SELECT @v =NULL,@v2 = NULL
IF @v = @v2
SELECT 'yes'
ELSE
SELECT 'No'


Be carefull with forgetting to initialize parameters while building string

DECLARE @SQL VARCHAR(500)
DECLARE @Id INT

SELECT @SQL = 'SELECT * FROM testnulls
WHERE ID =' + CONVERT(VARCHAR,@Id)

--Oops nothing is returned because NULL + anything else is always NULL
SELECT @SQL

SQL Server Interview Questions

Can you answer these SQL Server Interview Questions?

What is normalization
What is the fastest way to empty a table
what is a deadlock
Can you give an example of creating a deadlock
How do you detect deadlocks
What is an audit trail
what is an identity column
How do you return an identity value from a table
How do you return an identity value from a table with a trigger
How many bytes can you fit in a row, do you know why
What is a clustered index
How many clustered indexes per table
How many nonclustered indexes per table
what is an execution plan
What does trace flag 1204 do

That is just a partial list, you can find the rest here here: How Well Do You Interview And Do You Use Wizard Driven Programming?

Ten SQL Server Functions That You Hardly Use But Should Use

Below are 10 SQL Server functions that are hardly used but should be used a lot more


BINARY_CHECKSUM
SIGN
COLUMNPROPERTY
DATALENGTH
ASCII, UNICODE
NULLIF
PARSENAME
STUFF
REVERSE
GETUTCDATE

You can read the article here: Ten SQL Server Functions That You Hardly Use But Should