The only worth of the question is the good news:

"filtered index" (SQL Server 2008).

This feature is present in MS-ACCESS starting from version 1.0 (year 1990).

INSERT ... WHERE ...

... and UPDATE ????

I could insert NULL and then change (UPDATE) it to an existing value!

The solution is insert/update by stored proc, but the risk to miss...

tommyh (12/2/2010)

---

There is no **** way that "By adding a where clause in ALL inserts that checks for the existence of new value in the table" is a fix to...

I disagree: the query returns all rows that contains the word 'order' in the column [text] that may appear in comments or definition.

Carlo Romagnano (11/22/2010)

---

WayneS (11/22/2010)

---

BTW, the best way to check for the existance of a temporary table in your current session is:

IF OBJECT_ID('tempdb..#YourTempTable') IS NOT NULL

Note that in SQL Server thru...

WayneS (11/22/2010)

---

BTW, the best way to check for the existance of a temporary table in your current session is:

IF OBJECT_ID('tempdb..#YourTempTable') IS NOT NULL

Note that in SQL Server thru version 2008R2,...

That's true!

I ran it on sql2000 sp4.

vk-kirov (11/22/2010)

---

Carlo Romagnano (11/22/2010)

---

Also this is wrong:

You will see ALL tables that begins with '#testvm', also, tables belonging...

Also this is wrong:

You will see ALL tables that begins with '#testvm', also, tables belonging to other sessions.

Hugo Kornelis (11/17/2010)Without the reversal, the question would have been a solid test of whether people know about the dangers of using CASE in ORDER BY, and thus an excellent...

mister.magoo (11/16/2010)

---

Thanks to those of you who appreciate this question.

To those who think it had unnecessary "tricks" in it, I did try several variants of this question and they all...

tommyh (11/16/2010)

---

Carlo Romagnano (11/16/2010)

---

I should remember implicit cast using CASE clause.

Good question. I refreshed implicit cast.

In sql2000, it gives error also with SET @sortby='Date1'. (Syntax error converting datetime from character...

I should remember implicit cast using CASE clause.

Good question. I refreshed implicit cast.

In sql2000, it gives error also with SET @sortby='Date1'. (Syntax error converting datetime from character string.), not in...

Oleg Netchaev (11/15/2010)

---

Carlo,

Please check compat level of your SQL Server 2005 AdventureWorks database. If the level...

Execute the batch without the 'GO'

EXEC uspLogError

SELECT st.text QueryText,objtype

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'%uspLogError%'

Result:

CREATE PROCEDURE [dbo].[uspLogError] ....,'Proc'

EXEC uspLogError SELECT st.text QueryText FROM...