;WITH src AS

(

SELECT

database_id, db_buffer_pages = COUNT_BIG(*)

FROM sys.dm_os_buffer_descriptors

--WHERE database_id BETWEEN 5 AND 32766

GROUP BY database_id

)

SELECT

[db_name] = CASE [database_id] WHEN 32767

THEN 'Resource DB'

ELSE DB_NAME([database_id]) END,

db_buffer_pages,

db_buffer_MB = db_buffer_pages / 128,

db_buffer_percent...

Jeff Moden (8/15/2013)

---

BWAAAA-HAAAA!!!! I'm not sure that's so true...

As a little aside, I had a quick look at the rCTE method vs other methods of generating rows. Here's the code:

-- Q1

DROP TABLE #Temp1;

WITH RowGenerator AS (

SELECT...

CREATE TRIGGER dbo.My_Table_Delete_Instead_Of_Trigger

ON dbo.My_Table

INSTEAD OF DELETE

AS

BEGIN

IF NOT EXISTS (SELECT 1 FROM deleted WHERE tag <> 1)

DELETE FROM MyTable

WHERE <key> IN (SELECT <key> FROM deleted)

END

vignesh.ms (8/15/2013)

---

yes of course I want to restrict delete query based on some condition.

for example ,

assume a table named sample, and one of the column is tag which holds value...

SELECT

l.legacyID,

l.field1,

[Newid] = DENSE_RANK() OVER(ORDER BY x.legacyID)

FROM #LoadTest l

OUTER APPLY (

SELECT TOP 1 legacyID

FROM #LoadTest i

WHERE i.field1 = 'H'

AND i.legacyID <= l.legacyID

ORDER BY i.legacyID DESC)...

SQL2k5 introduced APPLY so you're ok with it in your query. Here's...

GPO (8/15/2013)

---

Hi Dwain

I'm enormously grateful for the code you've put up. I'll test yours and Chris's and see what I can learn from them. I'll post back my observations after...

There's a little date arithmetic left for you in this one:

SELECT

location_id,

unoccupied_start_dt = CASE WHEN seq = 1 THEN NULL ELSE unoccupied_start_dt END,

unoccupied_end_dt = CASE WHEN seq =...

Koen Verbeeck (8/14/2013)

---

And this post marks my 10,000th one on SSC! 😎 😎 :w00t: (and some of them were actually useful!)

Now that I'm one of the big boyz and Grant...

dwain.c (8/13/2013)

---

ChrisM@Work (8/13/2013)

---

Here's a sample data generator. I've tested it using Stefan's code and mine and it seems ok. Anyone else up for a race? Package your code into a...

If the query doesn't work, why try to construct a view out of it? Drop the CREATE VIEW part until you've got it working. It's just noise.

Start again, your query...

Thanks for posting those. It looks to me that the indexing strategy for the two tables is a bit hit or miss, meaning there's plenty of scope for improvement.

With...

Have a play with this date arithmetic code:

SELECT DATEDIFF(DD,'19010101',GETDATE())

SELECT DATEDIFF(DD,0,GETDATE())

SELECT DATENAME(DW,CAST('19010101' AS DATETIME))

SELECT

MyDate,

DATENAME(dw,MyDate),

DaysSince19000101 = DATEDIFF(DD,0,MyDate),

DateNoTime = DATEADD(DD,DATEDIFF(DD,0,MyDate),0),

mon_sun = DATEADD(DD,0+DATEDIFF(DD,0,MyDate)/7*7,0),

tue_mon = DATEADD(DD,1+DATEDIFF(DD,1,MyDate)/7*7,0),

wed_tue = DATEADD(DD,2+DATEDIFF(DD,2,MyDate)/7*7,0),

thu_wed = DATEADD(DD,3+DATEDIFF(DD,3,MyDate)/7*7,0),

fri_thu = DATEADD(DD,4+DATEDIFF(DD,4,MyDate)/7*7,0),

sat_fri = DATEADD(DD,5+DATEDIFF(DD,5,MyDate)/7*7,0),

sun_sat...

Stefan_G (8/13/2013)

---

If you want maximum performance you could use something like this:

create function fixop2(@a varchar(20)) returns table

as

return select

case

when right(@a,1) between 'A' and 'I' then +cast(left(@a, len(@a)-1) as int)*10 + (ascii(right(@a,1))...