If you have Reporting Services (SSRS) installed, you can right-click on a database in SSMS and select Reports -> Standard Reports -> Disk Usage by Table or Disk Usage by Top Tables.

Read the section on 'Concatenating values when the number of items is not known'.

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/[^]

Identity (auto-increment) is not part of the transaction, so isn't rolled back. In your example, the ID for the next call would be 102.

If you are using SQL Server 2008 or later, I suggest you try T-SQL Fundamentals for Microsoft SQL Server by Itzik Ben-Gan.

Wow, I didn't think that anyone would still be using Sql Server 2000. It's five versions old and all support ends for it in April 2013 (see http://support.microsoft.com/lifecycle/?LN=en-us&x=14&y=13&c2=1044[^]).

Try this [not tested but it should work!]

SELECT t1.PI,
t1.JobID,
t1.FormID,
t1.ShiftID,
t2.StartEvent,
t2.SW,
t2.SG,
t3.EndEvent,
t3.EG,
t3.EW
FROM
(
SELECT DISTINCT PI, JobID, FormID, ShiftID
FROM [table]
) t1
CROSS APPLY
(
SELECT TOP 1 StartEvent, SW, SG
FROM [table]
WHERE PI = t1.PI
AND JobID = t1.JobID
AND FormID = t1.FormID
AND ShiftID = t1.ShiftID
ORDER BY StartEvent ASC
) t2
CROSS APPLY
(
SELECT TOP 1 EndEvent, EG, EW
FROM [table]
WHERE PI = t1.PI
AND JobID = t1.JobID
AND FormID = t1.FormID
AND ShiftID = t1.ShiftID
ORDER BY EndEvent DESC
) t3

DO IT PROPERLY! Best practice is called that for a reason. If your Senior dev suggests otherwise, he is an idiot and doesn't deserve to be a professional developer.

The WHERE clause was causing the LEFT OUTER JOIN to act as an INNER JOIN, hence you lost all of the records where there wasn't a match. Well done on finding the right way to do it and for showing others what you did!

You are misunderstanding what Query Cost is. If you run any single query, the cost will always be 100%. If you run multiple queries, the total cost of all of the queries in the batch added together will equal 100%. The value is used to compare the relative cost of each query in the batch as the message says.

CREATE TABLE #data
(
ID INT NOT NULL,
PID INT NULL,
VALUE VARCHAR(20) NOT NULL
);

INSERT INTO #data
(ID, PID, VALUE)
VALUES
(1, NULL, 'node 1'),
(4, 1, 'node 1/1'),
(6, 4, 'node 1/1/1'),
(5, 1, 'node 1/2'),
(7, 1, 'node 1/3'),
(2, NULL, 'node 2'),
(3, NULL, 'node 3'),
(8, 3, 'node 3/1');

WITH MyCTE AS
(
SELECT ID, PID, VALUE, 0 AS [LEVEL]
FROM #data
WHERE PID IS NULL
UNION ALL
SELECT D.ID, D.PID, D.VALUE, [LEVEL] + 1
FROM MyCTE M
JOIN #data D
ON D.PID = M.ID
)
SELECT ID, PID, VALUE, [LEVEL]
FROM MyCTE;

DROP TABLE #data;

CREATE TABLE #Person
(
ID INT NOT NULL,
DOB DATE NOT NULL
);

CREATE TABLE #PersonName
(
ID INT NOT NULL,
PID INT NOT NULL,
FNAME VARCHAR(50) NOT NULL,
MNAME VARCHAR(10) NULL,
LNAME VARCHAR(50) NOT NULL,
NAMETYPE CHAR(1) NOT NULL
);

INSERT INTO #Person
(ID, DOB)
VALUES
(1, '2010-01-01'),
(2, '2011-02-01'),
(3, '2009-01-10');

INSERT INTO #PersonName
(ID, PID, FNAME, MNAME, LNAME, NAMETYPE)
VALUES
(1, 1, 'James', 'D', 'Doe', 'L'),
(2, 1, 'Jim', NULL, 'Doe', 'C'),
(3, 2, 'Martha', NULL, 'Stu', 'L'),
(4, 3, 'William', 'H', 'Jefferson', 'L'),
(5, 3, 'Bill', NULL, 'Jefferson', 'C');

WITH L AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'L'
),
C AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'C'
)
SELECT P.ID,
L.FNAME AS FNAME_L,
L.MNAME AS MNAME_L,
L.LNAME AS LNAME_L,
C.FNAME AS FNAME_C,
C.MNAME AS MNAME_C,
C.LNAME AS LNAME_C
FROM #Person P
LEFT JOIN L
ON L.PID = P.ID
LEFT JOIN C
ON C.PID = P.ID;

DROP TABLE #PersonName;
DROP TABLE #Person;

Have a look at Massive[^], a data access class written by Rob Conery.

http://www.lostechies.com/content/pablo_ebook.aspx[^]

Use FULL OUTER JOIN.

CREATE TABLE #first
(
Id INT NOT NULL,
Name VARCHAR(10) NOT NULL
);

CREATE TABLE #second
(
Id INT NOT NULL,
Name VARCHAR(10) NOT NULL
);

INSERT INTO #first (Id, Name) VALUES (1, 'One');
INSERT INTO #first (Id, Name) VALUES (2, 'Two');
INSERT INTO #first (Id, Name) VALUES (3, 'Three');
INSERT INTO #first (Id, Name) VALUES (9, 'Nine');

INSERT INTO #second (Id, Name) VALUES (2, 'Two');
INSERT INTO #second (Id, Name) VALUES (3, 'Three');
INSERT INTO #second (Id, Name) VALUES (5, 'Five');
INSERT INTO #second (Id, Name) VALUES (7, 'Seven');

SELECT f.Id AS fid,
f.Name AS fname,
s.Id AS [sid],
s.Name AS sname
FROM #first f
FULL OUTER JOIN #second s
ON s.Id = f.Id
WHERE s.Id IS NULL OR f.Id IS NULL

DROP TABLE #first;
DROP TABLE #second;

Yes, I go most months to Coventry and Birmingham and occasionally to Cambridge, Oxford and Hereford. Ian

Enjoyed the nugget last night. If you were nervous, it didn't really show. It was almost as if the 'mistakes' were deliberate to show how if you follow the instructions, the thing works!

Blog post by Kimberly Tripp

If you are using Sql Server 2005 or above, you should look at DDL Triggers for capturing changes to the database structure. http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-ddl-trigger-workbench/[^]

Buy a copy of Sql Server 2008 T-SQL Fundamentals by Itzik Ben-Gan published by Microsoft; It will give you a thorough grounding in T-SQL. I can't recommend this book highly enough.