collation ansi padding and trailing blanks

Hi,

This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?

I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...

The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...

I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...

Cany somebody please explain why they all return two rows?

PRINT 'Testing with ANSI_PADDING ON'

SET ANSI_PADDING ON;

GO

CREATE TABLE t1 (

charcol CHAR(16) NULL,

varcharcol VARCHAR(16) NULL,

varbinarycol VARBINARY(8)

);

GO

INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t1

where varcharcol='Trailing blank';

GO

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t1

where varcharcol='Trailing blank ';

GO

PRINT 'Testing with ANSI_PADDING OFF';

SET ANSI_PADDING OFF;

GO

CREATE TABLE t2 (

charcol CHAR(16) NULL,

varcharcol VARCHAR(16) NULL,

varbinarycol VARBINARY(8)

);

GO

INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t2

where varcharcol='Trailing blank';

GO

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t2

where varcharcol='Trailing blank ';

GO

DROP TABLE t1

DROP TABLE t2

ANSI padding setting only affects the storage and how the trimming of blanks is performed for non-unicode data. It doesn't change the search semantics. SQL Server will always ignore trailing blanks / spaces for equality searches. If you perform the same using LIKE then trailing blanks will be considered. If you do the query below after inserting the data, you will see how the storage differs when ANSI_PADDING is ON and OFF.

select datalength(charcol), datalength(varcharcol)

from t1

select datalength(charcol), datalength(varcharcol)

from t2

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20

ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA

CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]

Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:

Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATEFUNCTION dbo.ConRow(@.JID int)

RETURNSVARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output =COALESCE(@.Output+', ','')+CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROPFUNCTION dbo.ConRow

|||

Untested, but should give a single row

SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20

ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA

CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]

Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:

Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

|||

Untested, but should give a single row

SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20

ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA

CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]

Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:

Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

|||

Untested, but should give a single row

SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.

Collapsible reports

Hi,

I have a Dataset with repetitive data and i want to group all data based on a particular field and have it disaplayed as collapsible rows on a report.

BTW i am generating reports dynamically in my APP.Any ideas on how to proceed?

If I understand correctly, you want to group your data and have the details toggled by the group. To do that, you just need to add a table group on the field, set the visibility properties on the detail row of the table - 1. it's toggled by a textbox in the group header 2. the initial visibility to be hidden if you want to start as collapsed.|||

Hi,

Thanks 4 ur post wang...

You understood my question exactly...

Do u want me to add one more table to the Report?

could you please be more elaborate as to what needs to be done?

|||

No, you don't need another table. Here is a sample report to show how to do this:

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>30d8ee62-a72d-48fa-ad77-66fdebc3f620</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<DataSetName>DataSet1</DataSetName>
<Top>0.625in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<rd:DefaultName>ProductName</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<rd:DefaultName>UnitPrice</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>textbox6</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Category ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Unit Price</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>3.25in</Height>
</Body>
<rd:ReportID>87afdd90-7b33-49bc-8880-0df212c6637a</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT TOP 10 CategoryID, ProductName, UnitPrice
FROM Products</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.50001in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<PageFooter>
<Height>0.375in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageFooter>
<TopMargin>1in</TopMargin>
</Report>

Coalesce with Sum

I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.

Below is my sample:

SELECT *

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY q.StudentID

Having

(SUM(Coalesce(q.AbsenceValue),0) = 0.00)

COALESCE(SUM(q.AbsenceValue) = 0.00,0)

I have tried using the coalesce statement a couple of ways with no resolution, pls help!!

Change to this:

COALESCE( q.AbsenceValue, 0)

|||Ok, but how does that incorporate summing the column?|||

Try something like this: (in case you need the student name from your student table)

SELECT w.StudentID, w.StudentName, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

But you don't need to do the coalesce: SUM and AVG will skip the NULL value in the caculation.

The follwing should return the same result:

SELECT w.StudentID, w.StudentName, SUM(q.AbsenceValue) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

|||

Thanks for putting me on the right track. I actually got the result I needed by modifying your first example a little.

SELECT w.StudentID, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID

Having SUM(Coalesce(q.AbsenceValue,0) ) = 0.00

This gets me the desired result. I still needed to compare the result of the sum so that it equalled 0.00.

Thanks for setting me straight, I was about an inch from pulling hairs .

Clustered Indexes

I have a DB which has tables for each month with about 9 million rows each.
Now after that we create a clustered index on the table with specific
required columns. The issue is that the primary datafile doesn't grow much
every month but the secondary file, which has only indexes is huge abt 70
GB. Every month as the previous month's data is not needed on a regular
basis, I tried dropping the clustered index in a hope that this might save
some space but of no use.
Any advice?
When you create a clustered index, the leaf pages are the data pages. Thus,
if you create a clustered index and specify the filegroup, the data pages
will go into that filegroup.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Renu Doda" <renu.doda@.newsgroup.nospam> wrote in message
news:%23kQjWN3oFHA.2976@.TK2MSFTNGP12.phx.gbl...
I have a DB which has tables for each month with about 9 million rows each.
Now after that we create a clustered index on the table with specific
required columns. The issue is that the primary datafile doesn't grow much
every month but the secondary file, which has only indexes is huge abt 70
GB. Every month as the previous month's data is not needed on a regular
basis, I tried dropping the clustered index in a hope that this might save
some space but of no use.
Any advice?
|||Yeah, the fact of the matter is you have about 70G of data (ie. not
indexes - as Tom says a clustered index IS the data itself) and it has
to go somewhere.
You may be able to squish it a little by rebuilding the clustered
index(es) in question and specifying a fill factor of 100% to ensure
you're not leaving any empty space on each data page (ie. leaf node of
the clustered index). That will cram as much data as possible onto each
data page, which may mean your 70G may come down a little (if there was
empty space already, for example if the clustered indexes were built
with a fill factor less than 100%) but it may not.
A downside to 100% fill factors though is if there are changes to the
clustered keys or inserts, then you'll get quite a lot of page splits
because there's no free space on each data page to put the new/changed
key (so SQL Server has to split an existing page (known as a page split)
onto 2 new pages in the index, each being 50% full, so it can fit the
new/changed data in the correct location in the index), which will mean
increased I/O against those clustered indexes. But I'm assuming data
from past months won't change much so presumedly there won't be many
changes to make to the clustered keys.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tom Moreau wrote:

>When you create a clustered index, the leaf pages are the data pages. Thus,
>if you create a clustered index and specify the filegroup, the data pages
>will go into that filegroup.
>
>
|||In addition to the other replies: if you drop the clustered index, the
data will remain in the filegroup of the clustered index. To move the
data to another filegroup you would have to recreate the index on the
desired filegroup (easiest with CREATE ... CLUSTERED INDEX ... WITH
DROP_EXISTING).
Gert-Jan
Renu Doda wrote:
> I have a DB which has tables for each month with about 9 million rows each.
> Now after that we create a clustered index on the table with specific
> required columns. The issue is that the primary datafile doesn't grow much
> every month but the secondary file, which has only indexes is huge abt 70
> GB. Every month as the previous month's data is not needed on a regular
> basis, I tried dropping the clustered index in a hope that this might save
> some space but of no use.
> Any advice?

Clustered Indexes

I have a DB which has tables for each month with about 9 million rows each.
Now after that we create a clustered index on the table with specific
required columns. The issue is that the primary datafile doesn't grow much
every month but the secondary file, which has only indexes is huge abt 70
GB. Every month as the previous month's data is not needed on a regular
basis, I tried dropping the clustered index in a hope that this might save
some space but of no use.
Any advice?When you create a clustered index, the leaf pages are the data pages. Thus,
if you create a clustered index and specify the filegroup, the data pages
will go into that filegroup.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Renu Doda" <renu.doda@.newsgroup.nospam> wrote in message
news:%23kQjWN3oFHA.2976@.TK2MSFTNGP12.phx.gbl...
I have a DB which has tables for each month with about 9 million rows each.
Now after that we create a clustered index on the table with specific
required columns. The issue is that the primary datafile doesn't grow much
every month but the secondary file, which has only indexes is huge abt 70
GB. Every month as the previous month's data is not needed on a regular
basis, I tried dropping the clustered index in a hope that this might save
some space but of no use.
Any advice?|||Yeah, the fact of the matter is you have about 70G of data (ie. not
indexes - as Tom says a clustered index IS the data itself) and it has
to go somewhere.
You may be able to squish it a little by rebuilding the clustered
index(es) in question and specifying a fill factor of 100% to ensure
you're not leaving any empty space on each data page (ie. leaf node of
the clustered index). That will cram as much data as possible onto each
data page, which may mean your 70G may come down a little (if there was
empty space already, for example if the clustered indexes were built
with a fill factor less than 100%) but it may not.
A downside to 100% fill factors though is if there are changes to the
clustered keys or inserts, then you'll get quite a lot of page splits
because there's no free space on each data page to put the new/changed
key (so SQL Server has to split an existing page (known as a page split)
onto 2 new pages in the index, each being 50% full, so it can fit the
new/changed data in the correct location in the index), which will mean
increased I/O against those clustered indexes. But I'm assuming data
from past months won't change much so presumedly there won't be many
changes to make to the clustered keys.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tom Moreau wrote:

>When you create a clustered index, the leaf pages are the data pages. Thus
,
>if you create a clustered index and specify the filegroup, the data pages
>will go into that filegroup.
>
>|||In addition to the other replies: if you drop the clustered index, the
data will remain in the filegroup of the clustered index. To move the
data to another filegroup you would have to recreate the index on the
desired filegroup (easiest with CREATE ... CLUSTERED INDEX ... WITH
DROP_EXISTING).
Gert-Jan
Renu Doda wrote:
> I have a DB which has tables for each month with about 9 million rows each
.
> Now after that we create a clustered index on the table with specific
> required columns. The issue is that the primary datafile doesn't grow much
> every month but the secondary file, which has only indexes is huge abt 70
> GB. Every month as the previous month's data is not needed on a regular
> basis, I tried dropping the clustered index in a hope that this might save
> some space but of no use.
> Any advice?

Clustered Indexes

I have a DB which has tables for each month with about 9 million rows each.
Now after that we create a clustered index on the table with specific
required columns. The issue is that the primary datafile doesn't grow much
every month but the secondary file, which has only indexes is huge abt 70
GB. Every month as the previous month's data is not needed on a regular
basis, I tried dropping the clustered index in a hope that this might save
some space but of no use.
Any advice?When you create a clustered index, the leaf pages are the data pages. Thus,
if you create a clustered index and specify the filegroup, the data pages
will go into that filegroup.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Renu Doda" <renu.doda@.newsgroup.nospam> wrote in message
news:%23kQjWN3oFHA.2976@.TK2MSFTNGP12.phx.gbl...
I have a DB which has tables for each month with about 9 million rows each.
Now after that we create a clustered index on the table with specific
required columns. The issue is that the primary datafile doesn't grow much
every month but the secondary file, which has only indexes is huge abt 70
GB. Every month as the previous month's data is not needed on a regular
basis, I tried dropping the clustered index in a hope that this might save
some space but of no use.
Any advice?|||This is a multi-part message in MIME format.
--060606090504010006000906
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Yeah, the fact of the matter is you have about 70G of data (ie. not
indexes - as Tom says a clustered index IS the data itself) and it has
to go somewhere.
You may be able to squish it a little by rebuilding the clustered
index(es) in question and specifying a fill factor of 100% to ensure
you're not leaving any empty space on each data page (ie. leaf node of
the clustered index). That will cram as much data as possible onto each
data page, which may mean your 70G may come down a little (if there was
empty space already, for example if the clustered indexes were built
with a fill factor less than 100%) but it may not.
A downside to 100% fill factors though is if there are changes to the
clustered keys or inserts, then you'll get quite a lot of page splits
because there's no free space on each data page to put the new/changed
key (so SQL Server has to split an existing page (known as a page split)
onto 2 new pages in the index, each being 50% full, so it can fit the
new/changed data in the correct location in the index), which will mean
increased I/O against those clustered indexes. But I'm assuming data
from past months won't change much so presumedly there won't be many
changes to make to the clustered keys.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tom Moreau wrote:
>When you create a clustered index, the leaf pages are the data pages. Thus,
>if you create a clustered index and specify the filegroup, the data pages
>will go into that filegroup.
>
>
--060606090504010006000906
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Yeah, the fact of the matter is you have about 70G of data (ie. not
indexes - as Tom says a clustered index IS the data itself) and it has
to go somewhere.<br>
<br>
You may be able to squish it a little by rebuilding the clustered
index(es) in question and specifying a fill factor of 100% to ensure
you're not leaving any empty space on each data page (ie. leaf node of
the clustered index). That will cram as much data as possible onto
each data page, which may mean your 70G may come down a little (if
there was empty space already, for example if the clustered indexes
were built with a fill factor less than 100%) but it may not.<br>
<br>
A downside to 100% fill factors though is if there are changes to the
clustered keys or inserts, then you'll get quite a lot of page splits
because there's no free space on each data page to put the new/changed
key (so SQL Server has to split an existing page (known as a page
split) onto 2 new pages in the index, each being 50% full, so it can
fit the new/changed data in the correct location in the index), which
will mean increased I/O against those clustered indexes. But I'm
assuming data from past months won't change much so presumedly there
won't be many changes to make to the clustered keys.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tom Moreau wrote:
<blockquote cite="miduJA4xn3oFHA.3988@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">When you create a clustered index, the leaf pages are the data pages. Thus,
if you create a clustered index and specify the filegroup, the data pages
will go into that filegroup.
</pre>
</blockquote>
</body>
</html>
--060606090504010006000906--|||In addition to the other replies: if you drop the clustered index, the
data will remain in the filegroup of the clustered index. To move the
data to another filegroup you would have to recreate the index on the
desired filegroup (easiest with CREATE ... CLUSTERED INDEX ... WITH
DROP_EXISTING).
Gert-Jan
Renu Doda wrote:
> I have a DB which has tables for each month with about 9 million rows each.
> Now after that we create a clustered index on the table with specific
> required columns. The issue is that the primary datafile doesn't grow much
> every month but the secondary file, which has only indexes is huge abt 70
> GB. Every month as the previous month's data is not needed on a regular
> basis, I tried dropping the clustered index in a hope that this might save
> some space but of no use.
> Any advice?

clustered index rebuilds and performance hit

Hi
We're about to implement some clustered index changes on tables with 10s of
millions of rows.
I would like to know what the implications will be on:
SELECT
INSERT
UPDATE
DELETE
during the duration of the index changes/rebuilds. We have to plan for this
and want our users to know exactly what to expect i.e. what they will and
will not be able to do in the database.
thanks..u
-- cranfield, DBA
You mention both "change" and rebuild. Which is it? Removing one clustered index and adding it back
on some other column? Or defragmenting the clustered index?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
> Hi
> We're about to implement some clustered index changes on tables with 10s of
> millions of rows.
> I would like to know what the implications will be on:
> SELECT
> INSERT
> UPDATE
> DELETE
> during the duration of the index changes/rebuilds. We have to plan for this
> and want our users to know exactly what to expect i.e. what they will and
> will not be able to do in the database.
> thanks..u
> --
> -- cranfield, DBA
|||if you drop and recreate OR if you use DBCC DBREINDEX, then the table will
be exclusively locked for the duration.
IF you just defrag it via DBCC IndexDefrag, then there will only be minor
impact.
Greg Jackson
PDX, Oregon
|||Hi Tibor
We are removing the clustered index and creating a new one on a different key.
We will be creating the old clustered index as non-clustered. These changes
are required due to a logic change in our application.
thanks for the reply.
alan cranfield
"Tibor Karaszi" wrote:

> You mention both "change" and rebuild. Which is it? Removing one clustered index and adding it back
> on some other column? Or defragmenting the clustered index?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
>
>
|||Any time you drop, create or re-create a clustered index the table will be
unavailable for the duration of the event. Your best bet is to follow these
steps:
1. stop all access to this table
2. drop all the nonclustered indexes
3. drop the clustered index
4. Create the new clustered index
5. Recreate the nonclustered indexes
If you just drop the clustered index first it will recreate all the
nonclustered indexes. Then when you create a new clustered index it will
rebuild all the nonclustered indexes again.
Andrew J. Kelly SQL MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:18AB918A-44F4-4B91-8F40-1CFA8B5DEC40@.microsoft.com...[vbcol=seagreen]
> Hi Tibor
> We are removing the clustered index and creating a new one on a different
> key.
> We will be creating the old clustered index as non-clustered. These
> changes
> are required due to a logic change in our application.
> thanks for the reply.
> alan cranfield
> "Tibor Karaszi" wrote:

clustered index rebuilds and performance hit

Hi
We're about to implement some clustered index changes on tables with 10s of
millions of rows.
I would like to know what the implications will be on:
SELECT
INSERT
UPDATE
DELETE
during the duration of the index changes/rebuilds. We have to plan for this
and want our users to know exactly what to expect i.e. what they will and
will not be able to do in the database.
thanks..u
--
-- cranfield, DBAYou mention both "change" and rebuild. Which is it? Removing one clustered i
ndex and adding it back
on some other column? Or defragmenting the clustered index?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
> Hi
> We're about to implement some clustered index changes on tables with 10s o
f
> millions of rows.
> I would like to know what the implications will be on:
> SELECT
> INSERT
> UPDATE
> DELETE
> during the duration of the index changes/rebuilds. We have to plan for th
is
> and want our users to know exactly what to expect i.e. what they will and
> will not be able to do in the database.
> thanks..u
> --
> -- cranfield, DBA|||if you drop and recreate OR if you use DBCC DBREINDEX, then the table will
be exclusively locked for the duration.
IF you just defrag it via DBCC IndexDefrag, then there will only be minor
impact.
Greg Jackson
PDX, Oregon|||Hi Tibor
We are removing the clustered index and creating a new one on a different ke
y.
We will be creating the old clustered index as non-clustered. These changes
are required due to a logic change in our application.
thanks for the reply.
alan cranfield
"Tibor Karaszi" wrote:

> You mention both "change" and rebuild. Which is it? Removing one clustered
index and adding it back
> on some other column? Or defragmenting the clustered index?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
>
>|||Any time you drop, create or re-create a clustered index the table will be
unavailable for the duration of the event. Your best bet is to follow these
steps:
1. stop all access to this table
2. drop all the nonclustered indexes
3. drop the clustered index
4. Create the new clustered index
5. Recreate the nonclustered indexes
If you just drop the clustered index first it will recreate all the
nonclustered indexes. Then when you create a new clustered index it will
rebuild all the nonclustered indexes again.
Andrew J. Kelly SQL MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:18AB918A-44F4-4B91-8F40-1CFA8B5DEC40@.microsoft.com...[vbcol=seagreen]
> Hi Tibor
> We are removing the clustered index and creating a new one on a different
> key.
> We will be creating the old clustered index as non-clustered. These
> changes
> are required due to a logic change in our application.
> thanks for the reply.
> alan cranfield
> "Tibor Karaszi" wrote:
>

clustered index rebuilds and performance hit

Hi
We're about to implement some clustered index changes on tables with 10s of
millions of rows.
I would like to know what the implications will be on:
SELECT
INSERT
UPDATE
DELETE
during the duration of the index changes/rebuilds. We have to plan for this
and want our users to know exactly what to expect i.e. what they will and
will not be able to do in the database.
thanks..u
--
-- cranfield, DBAYou mention both "change" and rebuild. Which is it? Removing one clustered index and adding it back
on some other column? Or defragmenting the clustered index?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
> Hi
> We're about to implement some clustered index changes on tables with 10s of
> millions of rows.
> I would like to know what the implications will be on:
> SELECT
> INSERT
> UPDATE
> DELETE
> during the duration of the index changes/rebuilds. We have to plan for this
> and want our users to know exactly what to expect i.e. what they will and
> will not be able to do in the database.
> thanks..u
> --
> -- cranfield, DBA|||if you drop and recreate OR if you use DBCC DBREINDEX, then the table will
be exclusively locked for the duration.
IF you just defrag it via DBCC IndexDefrag, then there will only be minor
impact.
Greg Jackson
PDX, Oregon|||Hi Tibor
We are removing the clustered index and creating a new one on a different key.
We will be creating the old clustered index as non-clustered. These changes
are required due to a logic change in our application.
thanks for the reply.
alan cranfield
"Tibor Karaszi" wrote:
> You mention both "change" and rebuild. Which is it? Removing one clustered index and adding it back
> on some other column? Or defragmenting the clustered index?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
> > Hi
> >
> > We're about to implement some clustered index changes on tables with 10s of
> > millions of rows.
> >
> > I would like to know what the implications will be on:
> >
> > SELECT
> > INSERT
> > UPDATE
> > DELETE
> >
> > during the duration of the index changes/rebuilds. We have to plan for this
> > and want our users to know exactly what to expect i.e. what they will and
> > will not be able to do in the database.
> >
> > thanks..u
> > --
> > -- cranfield, DBA
>
>|||Any time you drop, create or re-create a clustered index the table will be
unavailable for the duration of the event. Your best bet is to follow these
steps:
1. stop all access to this table
2. drop all the nonclustered indexes
3. drop the clustered index
4. Create the new clustered index
5. Recreate the nonclustered indexes
If you just drop the clustered index first it will recreate all the
nonclustered indexes. Then when you create a new clustered index it will
rebuild all the nonclustered indexes again.
--
Andrew J. Kelly SQL MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:18AB918A-44F4-4B91-8F40-1CFA8B5DEC40@.microsoft.com...
> Hi Tibor
> We are removing the clustered index and creating a new one on a different
> key.
> We will be creating the old clustered index as non-clustered. These
> changes
> are required due to a logic change in our application.
> thanks for the reply.
> alan cranfield
> "Tibor Karaszi" wrote:
>> You mention both "change" and rebuild. Which is it? Removing one
>> clustered index and adding it back
>> on some other column? Or defragmenting the clustered index?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
>> > Hi
>> >
>> > We're about to implement some clustered index changes on tables with
>> > 10s of
>> > millions of rows.
>> >
>> > I would like to know what the implications will be on:
>> >
>> > SELECT
>> > INSERT
>> > UPDATE
>> > DELETE
>> >
>> > during the duration of the index changes/rebuilds. We have to plan for
>> > this
>> > and want our users to know exactly what to expect i.e. what they will
>> > and
>> > will not be able to do in the database.
>> >
>> > thanks..u
>> > --
>> > -- cranfield, DBA
>>

CLUSTERED INDEX or NONCLUSTERED

I have 3 table A, B, C

Table A (15 field, 4 fields indexed and Primary Key) approximate rows: 50.000 60.000

Table B (18 field, 6 fields indexed and Primary Key) approximate rows: 350.000 500.000

Table C (16 filed, 9 fields indexed and Primary Key) approximate rows: 500.000 1.000.000

Structure is something like this:
A (master) --> B (detail) --> C (sub detail)

On each 3 table is added new record, in table C the record is added after a search in table B.
My question is: Which is the best method? CLUSTERED INDEX or NONCLUSTERED INDEX

Thanks
Sorry for my englishIt is not clear about relations between tables (number of fields, etc.) by anyway clustered index for PK and nonclustered for others will be OK.|||Not enough info, these links may help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TuningofaDifferentSort.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp|||Thank you for your answer.
The diagram is attached, form left to right table A; B; C|||The diagram|||Still not enough info. Some questions:

What is your ratio of inserts to queries? Are you heavy insert or heavy queries or both?

What is typically used for your select criterias?

I would reccomend you start with reading those articles and you may play around with "set statistics IO on" to evaluate your logical IO when you have added a clustered index, taken it off, added a nonclustered index, etc. This to me is the best advice to become self sufficient on indexing questions.

HTH

clustered index choice

Hi.
We have a table called Master that has like ~400000 rows, and details
table with ~5M rows that is masterid column and gets about 5-6k
inserts during the business day, updates and deletes never happen.
The details table get a lot of reads (dozens per second), and 90% of
all queries to it are "where masterid = @.blah".
Currently, it is clustered by detailid (surrogate primary key) and has
a non clustered index on detailid column. The performance is
relatively good, but then our server is really top notch so it might
be worse on weaker servers of our clients.
The question is, given the query nature, would it be resonable to
cluster the table by masterid? or is the non-clustered index
sufficient to make reads by masterid fast?Sergey
What is the version of SQL SERVER?

> Currently, it is clustered by detailid (surrogate primary key) and has
> a non clustered index on detailid column.
Sorry, I don't understand, did you mean that uoi have clustered indec on
detailid column and non-clustered on masterid?

> all queries to it are "where masterid = @.blah".
How many rows do yuo get by using this condition?
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1177913754.037363.81800@.p77g2000hsh.googlegroups.com...
> Hi.
> We have a table called Master that has like ~400000 rows, and details
> table with ~5M rows that is masterid column and gets about 5-6k
> inserts during the business day, updates and deletes never happen.
> The details table get a lot of reads (dozens per second), and 90% of
> all queries to it are "where masterid = @.blah".
> Currently, it is clustered by detailid (surrogate primary key) and has
> a non clustered index on detailid column. The performance is
> relatively good, but then our server is really top notch so it might
> be worse on weaker servers of our clients.
> The question is, given the query nature, would it be resonable to
> cluster the table by masterid? or is the non-clustered index
> sufficient to make reads by masterid fast?
>|||Sergei
Thanks for putting a nice post together - you've made a good attempt at
covering what's important in youur post to enable us to give you a solid
answer. Hoever there are other pieces of information which are crucial for
anyone to give you a really comprehensive answer.
Firstly, you mentioned that 90% of all queries to it are "where masterid =
@.blah". This may be so, but queally important is which columns are accessed
by these queries? If * then the answer is very likely that yes, a CIX on
masterid is your best best. On the other hand, if 90% of your queries only
access a small subset of the columns, then it might not matter which column
the CIX is on as a NCIX might be much better.
Is it possible for you to post the DDL for these tables & examples of the
queries being run? It would also be helpful to know what types of queries
comprise the other 10% as although these might not be run as frequently,
it's possible they might still represent an abnormally large workload if
they run inefficiently.
Regards,
Regards,
Greg Linwood
SQL Server MVP
http://www.SQLBenchmarkPro.com
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1177913754.037363.81800@.p77g2000hsh.googlegroups.com...
> Hi.
> We have a table called Master that has like ~400000 rows, and details
> table with ~5M rows that is masterid column and gets about 5-6k
> inserts during the business day, updates and deletes never happen.
> The details table get a lot of reads (dozens per second), and 90% of
> all queries to it are "where masterid = @.blah".
> Currently, it is clustered by detailid (surrogate primary key) and has
> a non clustered index on detailid column. The performance is
> relatively good, but then our server is really top notch so it might
> be worse on weaker servers of our clients.
> The question is, given the query nature, would it be resonable to
> cluster the table by masterid? or is the non-clustered index
> sufficient to make reads by masterid fast?
>|||Uri: SQL Server version is 2005; yeah masterid index is non-clustered,
my bad.
Select gets, on average, 10-30 rows, and the majority of data in the
table (almost select *, the only column omitted is masterid itself).
Sorry I don't have access to ddl here, it's at work.
Other 10% of queries are select top N * from details where userid =
@.userid order by detailid desc, N is currently 30. Userid is another
foreign key column that has non clustered index on it.
The thing about this query however is that it is unimportant and we
could cache it or abandon it altogether if it yields a significant
performance gain to the first one.|||Sergey
As Greag has already said
> if 90% of your queries only
>access a small subset of the columns, then it might not matter which column
>the CIX is on as a NCIX might be much better.
Well also take a look at INCLUDE clause a new feature that you may use to
cover other columns ( I don't know how many do you have?)
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1177919501.967547.319830@.p77g2000hsh.googlegroups.com...
> Uri: SQL Server version is 2005; yeah masterid index is non-clustered,
> my bad.
> Select gets, on average, 10-30 rows, and the majority of data in the
> table (almost select *, the only column omitted is masterid itself).
> Sorry I don't have access to ddl here, it's at work.
> Other 10% of queries are select top N * from details where userid =
> @.userid order by detailid desc, N is currently 30. Userid is another
> foreign key column that has non clustered index on it.
> The thing about this query however is that it is unimportant and we
> could cache it or abandon it altogether if it yields a significant
> performance gain to the first one.
>|||I think the problem with clustering the detail table on masterid is that you
will wind up page-splitting at the tail of the table as you add new rows in
because the masterid is (I assume) an identity column. So the detail table
will get all these 50/50 page splits as you add in new data. Since (per a
later post in this thread) you return 10-30 rows of data per detail read it
is likely not THAT much slower to use a heap table and a non-clustered index
or keep your clustered pk index on detailid. The bookmark lookup won't be
that costly and you keep very tight data pages and have reduced maintenance
as well.
TheSQLGuru
President
Indicium Resources, Inc.
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1177913754.037363.81800@.p77g2000hsh.googlegroups.com...
> Hi.
> We have a table called Master that has like ~400000 rows, and details
> table with ~5M rows that is masterid column and gets about 5-6k
> inserts during the business day, updates and deletes never happen.
> The details table get a lot of reads (dozens per second), and 90% of
> all queries to it are "where masterid = @.blah".
> Currently, it is clustered by detailid (surrogate primary key) and has
> a non clustered index on detailid column. The performance is
> relatively good, but then our server is really top notch so it might
> be worse on weaker servers of our clients.
> The question is, given the query nature, would it be resonable to
> cluster the table by masterid? or is the non-clustered index
> sufficient to make reads by masterid fast?
>|||>I think the problem with clustering the detail table on masterid is that you will wind up[v
bcol=seagreen]
>page-splitting at the tail of the table as you add new rows in because the
masterid is (I assume)
>an identity column.[/vbcol]
SQL Server won't page split 50/50 when you add new values for a monotonicall
y increasing index key.
It will just allocate new pages at the end of the linked list:
USE tempdb
CREATE TABLE a(c1 int identity, filler char(300) default 'hello')
INSERT INTO a SELECT TOP 20000 'hello' FROM syscolumns AS a, syscolumns AS b
SELECT * FROM sys. dm_db_index_physical_stats(DB_ID('tempdb
'), OBJECT_ID('a')
, NULL, NULL, NULL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:utRvh65iHHA.1220@.TK2MSFTNGP03.phx
.gbl...
>I think the problem with clustering the detail table on masterid is that yo
u will wind up
>page-splitting at the tail of the table as you add new rows in because the
masterid is (I assume)
>an identity column. So the detail table will get all these 50/50 page spli
ts as you add in new
>data. Since (per a later post in this thread) you return 10-30 rows of dat
a per detail read it is
>likely not THAT much slower to use a heap table and a non-clustered index o
r keep your clustered pk
>index on detailid. The bookmark lookup won't be that costly and you keep v
ery tight data pages and
>have reduced maintenance as well.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
> news:1177913754.037363.81800@.p77g2000hsh.googlegroups.com...
>|||Here are the usage stats. I wonder what user_lookups is? Is it good
or bad? I know what scans and seeks are but have no idea of how the
lookup is different from seek.
updates seeks scans lookups
PK 22648 1551 1566 57490
TaskID_index 22648 57509 0 0
Also, Tibor: I guess the splitting can occur when details are added to
the old masters, but my guess is that a generous fillfactor setting
can cure this...|||> Also, Tibor: I guess the splitting can occur when details are added to
> the old masters, but my guess is that a generous fillfactor setting
> can cure this...
I'm not use I see the big picture here. I just wanted to point out that page
splits do not occur of
you add rows for a b-tree with increasing values. My apologies if I confused
the subject...

> Here are the usage stats. I wonder what user_lookups is?
This is when SQL Server finds a row in an NC index and then uses the row loc
ator to find the
corresponding row in the clustered index. AKA bookmark lookup. Can be expens
ive if low selectivity,
so covering indexes will eliminate this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1178093405.536234.312520@.c35g2000hsg.googlegroups.com...
> Here are the usage stats. I wonder what user_lookups is? Is it good
> or bad? I know what scans and seeks are but have no idea of how the
> lookup is different from seek.
>
> updates seeks scans lookups
> PK 22648 1551 1566 57490
> TaskID_index 22648 57509 0 0
> Also, Tibor: I guess the splitting can occur when details are added to
> the old masters, but my guess is that a generous fillfactor setting
> can cure this...
>