Cursors, loops and dSQL fall under the last choice column but this is one of those cases...

For tables you would do this:

EXEC sp_MSforeachtable'SELECT TOP 1 * FROM ?'

For...

I know I am a little late here but, Great article Gus.

FYI - the to Celko's article (http://www.intelligententerprise.com/001020/celko.jhtml.) is broke 😉

Lynn, Jeff, Kevin, ChrisM: thank you very much. This has been a particularly informative and excellent thread. It's given me a lot of new things to chew on.

Inline Table...

This is something I need to play around with more. I took what Jeff said to imply that, in the code below, the iTVF function (nsq_iTVF) would be faster than...

In case there are other schemas:

SELECT TABLE_SCHEMA, TABLE_NAME...

Google sp_send_dbmail.

No problem Amy.

That's actually a newer technique for me too and has been very helpful. You can do deletes in the same way...

;WITH X AS (SELECT * FROM dbo.emp...

Jeff Moden (2/25/2013)

---

Inline Table...

Scalar functions return one value, table valued functions return a table variable.

If you can produce the result set then all you can can produce an update...

SQLWannabe (2/25/2013)

---

Alan,

Thanks for the resposne. I should have been more clear. What I should've said was:

"I need to change the collation in my query".

The COLLATE function/keyword is definitely...

SQLWannabe (2/25/2013)

---

Lynn,

Holy cr@p, that's exactly what I want.

I didn't even think about the collation.

So since my collation is: SQL_Latin1_General_CP1_CI_AS, I need to change the collation?

Can you give me...

I started my technical career working on mainframes and switched to a database support role. Since then I have worked in the data world as a DBA, SQL Developer and...

Using the sample code I created before you can also get days in/days not in using this:

DECLARE @startdate bigint=20121101,

@endDate bigint=20121130;

;WITH

notthere AS

(SELECTStudentID, COUNT(*) AS DaysAbs

FROM #x

WHERE DateID>=@startdate AND DateID<=@endDate

AND...

This should do the trick:

-- Setup

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

DROP TABLE #x;

CREATE TABLE #x

( Studentid int, DateID bigint unique, attendaceind bit)

INSERT INTO #x

SELECT 1234, 20121031, 1 UNION ALL

SELECT...