SOT: I found a new 'worst query'

Before you ask, SOT means Slightly Off Topic :-())

Take a look at Hi, I need help on simplyfying this Update query!

I don't even want to count the nested SELECTS, The query is 688 lines long, somehow schadenfreude enters my mind.

Now where does the update come into the picture?

The last two days have been the worst of my life

The last two days have been the worst of my life; it feels like he combination of a really bad hangover, the flu and a massive toothache. It all started on Wednesday afternoon; my tooth and the gum fell a little sore. Instead of going to the dentist like a normal person I decided to wait because it will go away. Thursday it fell worse and I had to ask for Motrin at work. I did not sleep at all Thursday night, I called my dentist at 6AM but he couldn’t see me until 12:20 PM. I figured he’ll give me a shot, clean out the tooth and I’ll be back at work at 2PM.
Well it turns out a root canal I had done 10 years ago acted up and they have to do a root end resection. My dentist doesn’t do root canals; he referred me to a specialist. The specialist is fully booked and doesn’t have an opening till Tuesday morning.
My dentist prescribed Oxycodone W/APAP (also known as Oxycontin or Roxicodone) and Amoxicillin. That stuff is pretty intense, your pain goes away but you are pretty much useless. The pain is gone but now my right cheek is swollen and I have to throw up every hour or so. I just hope all this ends by Tuesday, the fix the root canal and I can be at my desk by 12PM.
The reason I wrote this is that next time I say “I have no time to floss for five5 minutes I’ll do one minute instead” I can look back at this post and hopefully change my mind

The World Is Small, The Risk Of Your Data Being Stolen Is Not!

Remember the How Is Your Sensitive Data Encrypted In The Database? post I wrote a while back? A colleague just informed me that he got a letter from that same datacenter. The letter states that his personal data was on one of those servers which got stolen. I told him that this is the reason we encrypt our data and also why we encrypt outside of the DB. The world is small indeed.

Here is a pic of the letter

Wow, talking about a rant against Ruby On Rails

Take a look at this post: Rails Is A Ghetto
This is probably one of the longest rants I have ever seen, interesting but be warned NSFW

help hisham to set up an online business, aka begging on the web

Posted here at the microsoft.public.sqlserver.programming forum

Hello world.
I had some problems with my business.
My name is Noor Hisham Bin Ahmad.
I,m from Malaysia.
I need some funds to support my blog because I want set up an online
business.

this is my account number.
Bank Simpanan Nasional
0210029816898886


via western union

How pathetic is that, maybe it should be called Begging 2.0

The Sad State Of Programmers Part 2 : The In Person Interview

This is part two of a three part series. Part one was about the phone interview, this part is about the in person interview. Part three is about general tips

The point of the in person interview is to see if the person will fit in with the rest of the people in the organization. This is where you get asked such wonderful questions like:
How many gas stations are there in the state of New Jersey?
How would you move Mount Fuji?

These questions are usually asked by managers to see how you handle pressure. There is usually no real answer to these questions, you have to logically come up with an answer and solve this puzzle. For example there are 8 million people in New Jersey, these people have 6 million cars. A gas station gets between 3 and 4 cars per minute; this adds up to 4320 cars per day. You would need about 2000 gas stations to serve all these people. All these numbers could be completely wrong; the point is can you logically figure out the steps to ‘solve’ this problem. It doesn’t matter how brilliant you are, if the people that interview you feel that you are a mismatch personality wise you won’t get hired.

Some observations about the process
Dress in business attire
One person dressed very inappropriate, in fact when picking him up at the security desk we thought he was a delivery person. This is not a way to start the face to face interview. I will get into more detail in part 3 of what to wear and not to wear.

You are your own worst enemy
A person was given source code after the phone interview to study. When asked about the code during the face to face interview she mentioned that she just glanced over the code and did not really look at it. Why would you say something like that if you want to get hired? Now, would you consider hiring this person?

Be proper
Do not grimace like you have a squirrel chewing on your big toe while thinking about an answer. Do not chew on your glasses either while thinking about the answer. When you answer the question wrong, the interviewer gives the correct answer don’t say that is what I meant. If that is what you meant then you should have said so, there are of course exceptions but I am talking about real black and white scenarios.

Bring good code
When you bring source code make sure it is the best code you could possible bring. One person was asked about best practices and error handling; he seemed to know this pretty good. Then he offered to show his code, we noticed that he didn’t have any of these best practices in his code. His proc was called sp_ProcName, error handling was wrong and a bunch of other things. In this case it would have been better if the person did not volunteer to show the code.

And now the questions.

I decided to ask every person these exact questions in the face to face interview; depending on how these were answered I would ask a bunch more questions. There are a couple of questions which you couldn’t answer wrong and I did not count them as toward the total score. Here is a list of some of the questions (Alex don’t get mad), I will comment on these questions and give the answers.

How many bytes can you fit in a row, do you know why
How do you enforce that only values between 1 and 10 are allowed in a column
How to check for a valid date if it is passed in as a string
Can you name the 4 isolation levels in SQL Server 2000
How would you select all rows where the date is 20061127
Name some differences between isnull and coalesce
If you create a local temp table and then call a proc is the temp table available inside the proc
What is a SQL injection
Update trigger to capture changed information
Which naming conventions did you use?
Name some best practices which you implemented
Select 3/2
Decimal(6,5)
Create table with PK symbol 10 characters, price 8 digit precision
Add clustered index to that table
What does set xact_abort on
Select * from table where ID <> 1
Favorite SQL Server book
How do you keep your SQL skills up to date?



How many bytes can you fit in a row, do you know why?
I am looking for one of these answers
8K
8060 bytes
Same as a page
8K + overflow column/text columns
If a person does not know the answer to this question then that is ok.

How do you enforce that only values between 1 and 10 are allowed in a column?
I am looking for check constraint as the answer. A bunch of people didn’t answer anything, some answered trigger and one said you should never check in the DB but in the application itself.

How to check for a valid date if it is passed in as a string?
The answer I am looking for is ISDATE() Some people knew this answer, some people answered “convert to datetime and then check for the error” one person said “parse the string”. A bunch of people didn’t answer anything at all

Can you name the 4 isolation levels in SQL Server 2000?
Another nice to have question, if you don’t know it then is not the end of the world. I will also ask the default transaction level

How would you select all rows where the date is 20061127?
I will draw a table on the board with values like these
2007-12-06 15:36:10.293

2007-12-07 00:00:00.000
2007-12-07 15:36:10.293
2007-12-07 15:36:10.293

2007-12-08 00:00:00.000

I am looking for the 3 rows which start with 2007-12-07
The answer I want is this

WHERE date >= ‘20061127’
AND date < ‘20061128’ I don’t want between because it will grab the 2007-12-08 value also, I don’t want convert because that causes an index scan. Less than half the people get the correct answer. Some stuff to read: Do You Know How Between Works With Dates? http://sqlservercode.blogspot.com/2006/10/do-you-know-how-between-works-with.html

How Are Dates Stored In SQL Server?
http://sqlservercode.blogspot.com/2006/11/how-are-dates-stored-in-sql-server.html

The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp

Name some differences between ISNULL and COALESCE
The main answer I am looking for is that ISNULL can only use 2 values while COALESCE can use a lot more. If the person knows other differences then that is also good. I was surprised at the number of people who never heard of COALESCE. Below is some code which shows the differences

There are three major differences besides being ANSI or not between COALESCE and ISNULL
1) COALESCE correctly promotes its arguments to the highest data type in the expression list, ISNULL does not
2) ISNULL can only work with 2 values while COALESCE can take a lot more
3) The alternate value takes the length of the first vale with ISNULL, with COALESCE this doesn't happen


Let's get started, run the following blocks of code

The result is 7, integer math

SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)

The result is 7.5, which is correct

SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)

You will see that the result is not the same ISNULL does integer math while COALESCE does not

COALESCE correctly promotes its arguments to the highest data type in the expression list.
ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int
COALESCE looks at 2.00 and 15 and then promotes the integer to decimal

Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more
Here we have 4 variables and all except for one are null


DECLARE @Var1 VARCHAR(20)
DECLARE @Var2 VARCHAR(20)
DECLARE @Var3 VARCHAR(20)
DECLARE @Var4 VARCHAR(20)
SELECT @Var4 = 'ABC'

--This will return ABC
SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)

Last example.
ISNULL returns NOT while COALESCE returns Not There.

DECLARE @v VARCHAR(3)
SELECT COALESCE(@v,'Not There')
SELECT ISNULL(@v,'Not There')

The alternate value takes the length of the first value with ISNULL, with COALESCE this doesn't happen

If you create a local temp table and then call a proc is the temp table available inside the proc?
The answer is yes and the code is below

--create proc
CREATE PROC TestProc
AS

SET NOCOUNT ON

SELECT * FROM #temp
GO

--create our temp table
CREATE TABLE #temp (id int)

INSERT #temp VALUES(1)
INSERT #temp VALUES(2)
INSERT #temp VALUES(3)

--exec proc
EXEC TestProc

What is SQL injection?
People either knew or did not know about SQL injection, the ones who knew also knew what to do to prevent it. A little less than half the people interviewed knew what it was.

Some stuff to read:
SQL injection cheat sheet.
http://ferruh.mavituna.com/makale/sql-injection-cheatsheet/


The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html


Update trigger to capture changed information
In drew two tables on the whiteboard and explained to the people that I wanted to move data into a history table only when the value in a certain column changed. I was looking for several things
If the person knew about inserted and deleted tables
If the person would code the trigger for multi row statements and not assign the value to a variable
If the person knew about IF UPDATE(Column) and that this was true even if the table was updated with the same value
If the person would join inserted and deleted to make sure no row would be inserted if the value didn’t change

Some stuff to read:
Fun With SQL server Update Triggers
http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html

What naming conventions did you use?
Here I wanted to hear if the person had any standards, I would also ask for an example of a proc name hoping sp_ProcName wouldn’t be answered

Some stuff to read:
The ISO organization has a document on their site. The one that deals with naming conventions is 11179-5 The link will point to a zip file which has a pdf file in it. The TOC of this pdf file is below Contents Foreword 1 Scope 2 Normative references 3 Terms and definitions 4 Data Identifiers within a registry 5 Identification 6 Names 6.1 Names in a registry 6.2 Naming conventions 7 Development of naming conventions 7.1 Introduction 7.2 Scope principle 7.3 Authority principle 7.4 Semantic principle 7.5 Syntactic principle 7.6 Lexical principle 7.7 Uniqueness principle Annex A (informative) Example naming conventions for names within an MDR registry Annex B (informative) Example naming conventions for Asian languages

What best practices did you implement?
There is a whole range of possible answers here and I won’t get into detail.

Select 3/2
When I wrote Select 3/2 on the board and ask what this would return several people looked at me if I was crazy. The answer is of course 1 because of integer math. I ask this question because we run reports which deal with calculation with integers. Take a look at this code to see what you can do to ‘fix’ the issue

Run this
--Integer math
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2
SELECT @Val1/@Val2
GO

It returns 1


Now run this
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2

--Implicit
SELECT @Val1/(@Val2*1.0)

--Explicit
SELECT CONVERT(DECIMAL(18,4),@Val1)/@Val2
GO

Both of those return 1.50000000000000

So what does @val2*1.0 do? well run this

DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2
SELECT CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'BaseType') AS VARCHAR(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'Precision') AS VARCHAR(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'Scale') AS VARCHAR(10)) + ')'

As you can see it is numeric(13,1)

Decimal(6,5)
What is the greatest value that Decimal(6,5) can hold? A lot of people answered 999999.99999 which is wrong of course. The total number of numbers is 6 and 5 of those are after the comma. So the answer is 9.99999. Run this to try it out for yourself.


Create table with PK symbol 10 characters, price 8 digit precision
I would tell people to create a table with 2 columns
1 with a column named symbol variable character 10 in length, it would also be a primary key
Price which was decimal and can hold a number as big as 999999.99999999
They had to do this on the whiteboard in T-SQL (create table…..)
Most people got the PK syntax wrong; some people got the decimal wrong. The reason I am asking this question is because we can’t go on a production machine with Enterprise Manager and start clicking. You might not have permissions to make changes, you need to supply a script which other people might run.

Add clustered index to that table
My next question would be to add a clustered index to that table. The correct answer would be you can’t since the table has a primary key which is a clustered index by default

What does set xact_abort on do
Only one person answered this question correctly, this was another nice to know question. I did explain to people why you would use this, sometimes you cannot trap the error and this statement will enable you to rollback everything up until that point.

Some stuff to read:
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html

Error Handling in SQL Server – a Background.
http://www.sommarskog.se/error-handling-I.html

Select * from table where ID <> 1
I would draw a table with three rows, the values being 1, 2 and null. Then I would ask what would be returned after running this query

SELECT * FROM #temp WHERE id <> 1

A lot of people chocked on this one, the answer is 2 of course, null will not be returned. Test it out for yourself


CREATE TABLE #temp (id int)
INSERT #temp VALUES(1)
INSERT #temp VALUES(2)
INSERT #temp VALUES(null)

SELECT * FROM #temp WHERE id <> 1

Some stuff to read:
NULL trouble In SQL Server Land
http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html

NULL - The database's black hole
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx

The logic of three-valued logic
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/17/the-logic-of-three-valued-logic.aspx

Dr. Unknown, or how I learned to stop worrying and love the NULL
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null.aspx

What if null if null is null null null is null?
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-is-null-null-null-is-null.aspx

Favorite SQL Server book
Here something bizarre happened; there were several people who did not have a SQL Server book at all. I did not understand this; how do you not have a SQL server books as a developer? A bunch of people listed Ken Henderson’s Guru book, some of them listed Inside SQL Server and one person answered SQL Server 2005 unleashed (I have the 2000 version, I should check this one out). In part three I will give you a list of my favorite books.

How do you keep your SQL skills up to date?
I am looking for websites, books, blogs, conferences, webinars, podcasts etc. Some people answered Books On Line. One person answered certification (I will address certification and my dislike of them in part 3). I was surprised by the difficulty that people had in answering this question. SQL Server Central was a popular website as well as DevX and MSDN. Nobody answered podcast at all. I would ask people if they played around with the latest CTP of SQL Server 2008, most of them were not aware that this was available to the general public. I did interview a bunch of SQL Server authors and I asked them what they did or would do to master SQL. You can read those interviews by clicking on the links below

Louis Davidson
Itzik Ben-Gan
Ken Henderson
Adam Machanic
Kalen Delaney

That was it fort part two, I originally wanted to put more content here but I was afraid it would become too long, I moved some of that stuff to part three.

The Most Natural Set Option

SET ANSI_NULL_DFLT_OFF ON

this is what this means, in English....

Set the setting ANSI NULL Default Off to ON

Why not just SET ANSI_NULL_DFLT [ON|OFF]?

This is even worse than SET NOCOUNT ON

How many Java programmers does it take to round up to a power of two?

How many Java programmers does it take to round up to a power of two?
Well take a look:
http://forum.java.sun.com/thread.jspa?threadID=248212&start=0&tstart=0

Do you want to laugh or cry?

I don't know if I should laugh or cry after looking at this code. This could not have been written by a human right?

Anyway here it is, consider it a teaser. If you want to torture yourself and look at the whole query then go here: http://www.tek-tips.com/viewthread.cfm?qid=1421304&page=1



HAVING (((1 = 1)
AND (PP.Created >= ISNULL(NULL,'1/1/1900')
AND PP.Created < 1 =" 2)">= ISNULL(NULL,'1/1/1900')
AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))



WHERE
(
(NULL IS NOT NULL
AND pv.DoctorId IN (NULL))
OR(NULL IS NULL)
)



CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END


Someone will red flag the post, here is the whole query.


/* New Patient Analysis */

SET NOCOUNT ON

CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL ,
[Prefix] [varchar] (10) NULL ,
[First] [varchar] (30) NULL ,
[Middle] [varchar] (30) NULL ,
[Last] [varchar] (30) NULL ,
[Suffix] [varchar] (20) NULL ,
[Created] [datetime] NOT NULL
)

Insert #PatientTemp

SELECT PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix,
CASE
WHEN 1 = 1 THEN PP.Created
WHEN 1 = 2 THEN Min(PV.Visit)
ELSE NULL
END As Created

FROM PatientVisit PV
INNER JOIN PatientProfile PP ON PP.PatientProfileId = PV.PatientProfileId

GROUP BY PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix, PP.Created

HAVING (((1 = 1)AND
(PP.Created >= ISNULL(NULL,'1/1/1900') AND PP.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(PV.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))

Order By PP.First, PP.Last

SELECT dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix) AS Name,
CASE
WHEN 1 = 1 THEN #PatientTemp.Created
WHEN 1 = 2 THEN Min(pv.Visit)
ELSE NULL
END As Created,
COUNT(*) AS [Number Of Visits],
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULLA
END As Grouping

FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN DoctorFacility df1 ON pv.ReferringDoctorId = df1.DoctorFacilityId
LEFT JOIN DoctorFacility df2 ON pv.FacilityId = df2.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId

WHERE
(
(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.ReferringDoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)

GROUP BY #PatientTemp.Created,dbo.FormatName(#PatientTemp.Prefix, #PatientTemp .First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix),
pva.PatientProfileId,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END

HAVING (((1 = 1)AND
(#PatientTemp.Created >= ISNULL(NULL,'1/1/1900') AND #PatientTemp.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(pv.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(pv.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))

ORDER BY dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp .Suffix), #PatientTemp.Created
Drop table #PatientTemp

How To Protect Yourself From Fat-Finger Sally, Crazy Bosses and Other SQL Villains

You all have been through this at least once in your life. In your shop there is this one person who likes to use Enterprise Manager as their Rapid Data Entry Application. We all know how these people operate; they delete rows, drop tables and all kinds of other funky stuff. SQL Server 2005 has DDL triggers to help you protect against these scoundrels. What about if you are still running that piece of software from the late Triassic period known as SQL Server 2000, what can help you in that case? Don’t worry I will show you a way but first I will tell you a story. About 6 years ago I worked in New York City as a consultant on a project for a nonprofit organization. I looked in the database and found this table which was named YesNoTable. I was curious I opened the table and noticed it had only 2 rows. Here is what was stored in the table.

0 no
1 yes

I dropped it immediately. 5 minutes went by and suddenly the CRM application was broken. They ran the debugger and found out a table was missing. Luckily for me it was very easy to recreate this table. And yes, we did get rid of it soon after. Now had the table be used by a view which had been created with schemabinding I would not be able to drop the table without dropping the view first. You see even I became a SQL villain one time.

What the code below does is it will loop through all the user created tables then union them all, I created a where 1 =0 WHERE clause just in case someone decides to open the view. Since a union can only have 250 selects or so, I have created the code so that you can specify how many tables per view you would like, you do that with the @UnionCount variable.

The code does print statements it does not create the views

If you run the code in the msdb database and you specify 5 as the @UnionCount your output will be this

-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_1 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[log_shipping_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_monitor]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_history]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plans]
WHERE 1=0
GO


-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_2 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[RTblClassDefs]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDatabaseVersion]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBMProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBXProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDTMProps]
WHERE 1=0
GO


The code is not very complex if there are more tables in the DB than you specify in the @UnionCount variable then it will do them in chunks of whatever you specified, if there are less then it will do all of them in 1 view.
Below is the code, if you have any questions then feel free to leave a comment.

USE msdb
SET NOCOUNT ON

DECLARE @UnionCount int
SELECT @UnionCount = 20

IF @UnionCount > 250 OR @UnionCount <1
BEGIN
RAISERROR ('@UnionCount has to be between 1 and 250', 16, 1)
RETURN
END

SELECT identity(int,1,1) AS id,QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) AS tablename
INTO #Tables
FROM information_schema.tables
WHERE table_type ='base table'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped') = 0
ORDER BY table_name

DECLARE @maxloop int
DECLARE @loop int
DECLARE @tablename varchar(200)




SELECT @maxloop = MAX(id)
FROM #Tables

BEGIN
DECLARE @OuterLoopCount int, @OuterLoop int

SELECT @OuterLoopCount = COUNT(*) FROM #Tables
WHERE id %@UnionCount =0

SELECT @OuterLoopCount = COALESCE(NULLIF(@OuterLoopCount,0),1)



IF (SELECT COUNT(*) FROM #Tables) % 10 <> 0
SELECT @OuterLoopCount = @OuterLoopCount +1

SELECT @OuterLoop =1

SELECT @Loop = MIN(id),@maxloop=MAX(id)
FROM #Tables WHERE ID <= @UnionCount * @OuterLoop

WHILE @OuterLoop <=@OuterLoopCount
BEGIN
SELECT @Loop = MIN(id),@maxloop=MAX(id)
FROM #Tables WHERE ID <= @UnionCount * @OuterLoop
AND id > (@UnionCount * @OuterLoop) - @UnionCount


PRINT'-- **************************** '
PRINT'-- **** View Starts Here ***** '
PRINT'-- **************************** '
PRINT 'CREATE VIEW DoNotDropMe_' + CONVERT(VARCHAR(10),@OuterLoop) + ' WITH SCHEMABINDING'+ char(10) + 'AS'
WHILE @Loop <= @maxloop
BEGIN
SELECT @tablename = tablename
FROM #Tables
WHERE id = @Loop
PRINT 'SELECT 1 As Col1 FROM ' + @tablename + char(10) + 'WHERE 1=0'
IF @Loop < @maxloop
PRINT UNION ALL'
SET @Loop = @Loop + 1

END
SET @OuterLoop = @OuterLoop + 1
PRINT 'GO'
PRINT ''
PRINT ''

END
END


DROP table #Tables


Cross-posted from SQLBlog! - http://www.sqlblog.com/

Man sues IBM over firing, says he's an Internet addict

I won't comment on the Britney madness but here is another gem:

A man who was fired by IBM for visiting an adult chat room at work is suing the company for $5 million, claiming he is an Internet addict who deserves treatment and sympathy rather than dismissal.

James Pacenza, 58, of Montgomery, says he visits chat rooms to treat traumatic stress incurred in 1969 when he saw his best friend killed during an Army patrol in Vietnam.

In papers filed in federal court in White Plains, Pacenza said the stress caused him to become "a sex addict, and with the development of the Internet, an Internet addict." He claimed protection under the American with Disabilities Act.

Right, what's next? I know, I will sue my neighbour because his house is bigger and now I am suffering from Napoleon syndrome because of that.

Here is the link to the CNN story: http://www.cnn.com/2007/LAW/02/18/chat.room.lawsuit.ap/index.html

What Happens When You Let a Three Year Old Use Your Computer For 2 Minutes?

What Happens When You Let a Three Year Old Use Your Computer For 2 Minutes?

This (I Spy a bunch of trouble)

Shipping Method: Standard Shipping
Shipping Preference: Group my items into as few shipments as possible
Subtotal of Items: $234.62
Shipping & Handling: $32.70
Promotion Applied: -$19.95

------
Total for this Order: $247.37


Shipping estimate for these items: February 7, 2007
Delivery estimate: February 12, 2007 - February 14, 2007 1 "I Spy A Penguin (Level 1) (I Spy)"
JEAN MARZOLLO; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy Santa Claus (Scholastic Reader, Level 1)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Balloon (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy Lightning In The Sky (level 1): I Spy Lightning In The Sky (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Dinosaur's Eye (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Dinosaur's Eye (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Pumpkin (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Pumpkin (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy: Year-round Challenger: Year-round Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy A School Bus (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy Little Animals (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "I Spy Fun House: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy School Days (I Spy)"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy Fun House: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy Extreme Challenger! A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy Funny Teeth (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy Gold Challenger (I Spy)"
Jean Marzollo; Hardcover; $11.58

Sold by: Amazon.com
1 "I Spy Spooky Night (I Spy)"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy: A Book Of Picture Riddles: A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy Christmas: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy Little Letters (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "I Spy Little Letters (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "I Spy: Mystery A Book Of Picture Riddles: Mystery A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy: Mystery A Book Of Picture Riddles: Mystery A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy Little Wheels (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "I Spy Little Book (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "Can You See What I See? Dream Machine"
Walter Wick; Hardcover; $11.58

Sold by: Amazon.com


And of course it already shipped, how come when I place an order it doesn't ship for 2 days.....