mahbub422 (5/10/2010)

---

Dear Peter,

Now I need little bit more support. You helped me to count the left and right side child nodes. Now I need the list of those left...

rjv_rnjn (5/6/2010)

---

Change data: I read through the MSDN articles a bit, but do not see any mention of where are they stored. It seems the default prune date is 3...

Jeff Moden (5/5/2010)

---

Unless you have some indication of time in the table or you run the job once an hour automatically whilst capturing the value of an IDENTITY column as...

I'm not sure what you are hoping for, but maybe this one will do:

DECLARE @test-2 TABLE

(

...

Paul White NZ (4/30/2010)

---

Peter Brinkhaus (4/30/2010)

---

Some other thing, why are you explicitly testing if the inserted row set is empty. If it is, the insert statement is a no-op (even...

Paul White NZ (4/30/2010)

---

Peter Brinkhaus (4/30/2010)

---

If the LunchPeriod is not explicitly inserted it's default value will be filled in the 'inserted' row set within the trigger. Given that it easy...

If the LunchPeriod is not explicitly inserted it's default value will be filled in the 'inserted' row set within the trigger. Given that it easy to modify and even simplify...

Atif Sheikh (4/29/2010)

---

Hi...

DECLARE @MissingNumbers TABLE (N INT)

Declare @vMax int

INSERT INTO @MissingNumbers

VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20)

Set @vMax = (Select MAX(N) from @MissingNumbers)

;with wcte as (

Select Top(@vMax) ROW_NUMBER() over(order by a.N) NAll

from @MissingNumbers a, @MissingNumbers...

I suppose a reseed is done when IDENTITY_INSERT is turned off. Maybe this behaviour can be derived from the description of DBCC CHECKIDENT in BOL:

DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value...

As this is a SQL2K8 forum, I used the new SQL2K8 syntax for inserting multiple values. If you are using SQL2K5 then you should use UNION ALL indeed.

Peter

I'm not sure if this fulfills your requirements, but you can use a tally or numbers table to find the missing numbers:

DECLARE @MissingNumbers TABLE (N INT)

INSERT INTO @MissingNumbers

VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20)

;WITH Tally(N)...

Here's another parameterized version. Thanks to Quatrei.X for the test script.

Declare @tbl Table

(

customer nvarchar(10),

product...

Paul White NZ (4/6/2010)

---

If you look back to...

To me it seems MS has reinvented its own definition of (non-)determinism. Given the MS definition, even GETDATE() would be a deterministic function, if only for 3ms. Anyway, it was...

Unless I am missing something completely, it looks like the 'deterministic' function is just an identity function. Looks like 'SELECT @i = a FROM dbo.t ...' is a no-op because...