doubts at no of records in a table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

doubts at no of records in a table.

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2007-09-13 : 08:34:11

hi,
here i am inserting the total dates between 1983/08/26 to 2006/08/26.
i.e the table will have 36525 recoeds approximately. after executing the command i verified the table n i noticed that the last record in this table was 2025/xx/xx approximately. why? defualtly what is the capacity of a sql table. can i change the table properties to hold more num. of records. please help me on this.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-13 : 08:57:40

Show us the query you executed

Madhivanan

Failing to plan is Planning to fail

Bharat
Starting Member

3 Posts

Posted - 2007-09-13 : 09:06:23

is there IDENTITY constraints (column on this) !!

rammohan
Posting Yak Master

212 Posts

Posted - 2007-09-13 : 09:27:44

Actually i had written the following stored procedure to insert data into the table:

USE [master]
GO
/****** Object: StoredProcedure [dbo].[proc_datefunction] Script Date: 09/13/2007 18:55:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[proc_datefunction]
@FIRST_DATE datetime,
@LAST_DATE datetime
as
declare @cr varchar(2)
set @cr = char(13)+Char(10)
declare @ErrorMessage varchar(400)
declare @START_DATE datetime
declare @END_DATE datetime
declare @LOW_DATE datetime

declare @start_no int
declare @end_no int
begin
if @FIRST_DATE is null
begin
set @ErrorMessage = 'First Date Cannot Be Null'
return
end
if @LAST_DATE is null
begin
set @ErrorMessage = 'Last Date Cannot Be Null'
return
end
IF @FIRST_DATE < '17540101'
begin
select @ErrorMessage =
'@FIRST_DATE cannot before 1754-01-01'+
', @FIRST_DATE = '+
isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')
return
end
IF @LAST_DATE > '99971231'
begin
select @ErrorMessage =
'@LAST_DATE cannot be after 9997-12-31'+
', @LAST_DATE = '+
isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
return
end
if @FIRST_DATE > @LAST_DATE
begin
select @ErrorMessage =
'@FIRST_DATE cannot be after @LAST_DATE'+
', @FIRST_DATE = '+
isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')+
', @LAST_DATE = '+
isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
return
end
select @START_DATE = dateadd(dd,datediff(dd,0,@FIRST_DATE),0)
select @END_DATE = dateadd(dd,datediff(dd,0,@LAST_DATE),0)
select @LOW_DATE = convert(datetime,'17530101')
set @start_no = datediff(dd,@LOW_DATE,@START_DATE)
set @end_no = datediff(dd,@LOW_DATE,@END_DATE)
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)
declare @ISO_WEEK table
(
[ISO_WEEK_YEAR] int not null
primary key clustered,
[ISO_WEEK_YEAR_START_DATE] datetime not null,
[ISO_WEEK_YEAR_END_DATE] Datetime not null
)
declare @rows_needed int
declare @rows_needed_root int
set @rows_needed = @end_no - @start_no + 1
if @rows_needed < 10
set @rows_needed = 10
set @rows_needed_root = convert(int,ceiling(sqrt(@rows_needed)))
insert into @num1 (NUMBER)
select NUMBER = 0 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15
order by 1
insert into @num2 (NUMBER)
select
NUMBER = a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER)
from
@num1 a cross join @num1 b cross join @num1 c
where
a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER) <
@rows_needed_root
order by
1
insert into @num3 (NUMBER)
select
NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER)
from
@num2 a
cross join
@num2 b
where
a.NUMBER+(@rows_needed_root*b.NUMBER) < @rows_needed
order by
1
declare @iso_start_year int
declare @iso_end_year int

set @iso_start_year = datepart(year,dateadd(year,-1,@start_date))
set @iso_end_year = datepart(year,dateadd(year,1,@end_date))
insert into @ISO_WEEK
(
[ISO_WEEK_YEAR],
[ISO_WEEK_YEAR_START_DATE],
[ISO_WEEK_YEAR_END_DATE]
)
select
[ISO_WEEK_YEAR] = a.NUMBER,
[0ISO_WEEK_YEAR_START_DATE] =
dateadd(dd,(datediff(dd,@LOW_DATE,
dateadd(day,3,dateadd(year,a.[NUMBER]-1900,0))
)/7)*7,@LOW_DATE),
[ISO_WEEK_YEAR_END_DATE] =
dateadd(dd,-1,dateadd(dd,(datediff(dd,@LOW_DATE,
dateadd(day,3,dateadd(year,a.[NUMBER]+1-1900,0))
)/7)*7,@LOW_DATE))
from
(
select
NUMBER = NUMBER+@iso_start_year
from
@num3
where
NUMBER+@iso_start_year <= @iso_end_year
) a
order by
a.NUMBER
insert into DATE
select
[DATE_ID] = a.[DATE_ID] ,
[DATE] = a.[DATE] ,

[NEXT_DAY_DATE] =
dateadd(day,1,a.[DATE]) ,

[YEAR] =
datepart(year,a.[DATE]) ,
[YEAR_QUARTER] =
(10*datepart(year,a.[DATE]))+datepart(quarter,a.[DATE]) ,

[YEAR_MONTH] =
(100*datepart(year,a.[DATE]))+datepart(month,a.[DATE]) ,
[YEAR_DAY_OF_YEAR] =
(1000*datepart(year,a.[DATE]))+
datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 ,
[QUARTER] =
datepart(quarter,a.[DATE]) ,
[MONTH] =
datepart(month,a.[DATE]) ,
[DAY_OF_YEAR] =
datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 ,
[DAY_OF_MONTH] =
datepart(day,a.[DATE]) ,
[DAY_OF_WEEK] =
-- Sunday = 1, Monday = 2, ,,,Saturday = 7
(datediff(dd,'17530107',a.[DATE])%7)+1 ,
[YEAR_NAME] =
datename(year,a.[DATE]) ,
[YEAR_QUARTER_NAME] =
datename(year,a.[DATE])+' Q'+datename(quarter,a.[DATE]) ,
[YEAR_MONTH_NAME] =
datename(year,a.[DATE])+' '+left(datename(month,a.[DATE]),3) ,
[YEAR_MONTH_NAME_LONG] =
datename(year,a.[DATE])+' '+datename(month,a.[DATE]) ,
[QUARTER_NAME] =
'Q'+datename(quarter,a.[DATE]) ,
[MONTH_NAME] =
left(datename(month,a.[DATE]),3) ,
[MONTH_NAME_LONG] =
datename(month,a.[DATE]) ,
[WEEKDAY_NAME] =
left(datename(weekday,a.[DATE]),3) ,
[WEEKDAY_NAME_LONG] =
datename(weekday,a.[DATE]),

[START_OF_YEAR_DATE] =
dateadd(year,datediff(year,0,a.[DATE]),0) ,
[END_OF_YEAR_DATE] =
dateadd(day,-1,dateadd(year,datediff(year,0,a.[DATE])+1,0)) ,

[START_OF_QUARTER_DATE] =
dateadd(quarter,datediff(quarter,0,a.[DATE]),0) ,
[END_OF_QUARTER_DATE] =
dateadd(day,-1,dateadd(quarter,datediff(quarter,0,a.[DATE])+1,0)) ,

[START_OF_MONTH_DATE] =
dateadd(month,datediff(month,0,a.[DATE]),0) ,
[END_OF_MONTH_DATE] =
dateadd(day,-1,dateadd(month,datediff(month,0,a.[DATE])+1,0)),

[START_OF_WEEK_STARTING_SUN_DATE] =
dateadd(dd,(datediff(dd,'17530107',a.[DATE])/7)*7,'17530107'),
[END_OF_WEEK_STARTING_SUN_DATE] =
dateadd(dd,((datediff(dd,'17530107',a.[DATE])/7)*7)+6,'17530107'),

[START_OF_WEEK_STARTING_MON_DATE] =
dateadd(dd,(datediff(dd,'17530101',a.[DATE])/7)*7,'17530101'),
[END_OF_WEEK_STARTING_MON_DATE] =
dateadd(dd,((datediff(dd,'17530101',a.[DATE])/7)*7)+6,'17530101'),

[START_OF_WEEK_STARTING_TUE_DATE] =
dateadd(dd,(datediff(dd,'17530102',a.[DATE])/7)*7,'17530102'),
[END_OF_WEEK_STARTING_TUE_DATE] =
dateadd(dd,((datediff(dd,'17530102',a.[DATE])/7)*7)+6,'17530102'),

[START_OF_WEEK_STARTING_WED_DATE] =
dateadd(dd,(datediff(dd,'17530103',a.[DATE])/7)*7,'17530103'),
[END_OF_WEEK_STARTING_WED_DATE] =
dateadd(dd,((datediff(dd,'17530103',a.[DATE])/7)*7)+6,'17530103'),

[START_OF_WEEK_STARTING_THU_DATE] =
dateadd(dd,(datediff(dd,'17530104',a.[DATE])/7)*7,'17530104'),
[END_OF_WEEK_STARTING_THU_DATE] =
dateadd(dd,((datediff(dd,'17530104',a.[DATE])/7)*7)+6,'17530104'),

[START_OF_WEEK_STARTING_FRI_DATE] =
dateadd(dd,(datediff(dd,'17530105',a.[DATE])/7)*7,'17530105'),
[END_OF_WEEK_STARTING_FRI_DATE] =
dateadd(dd,((datediff(dd,'17530105',a.[DATE])/7)*7)+6,'17530105'),

[START_OF_WEEK_STARTING_SAT_DATE] =
dateadd(dd,(datediff(dd,'17530106',a.[DATE])/7)*7,'17530106'),
[END_OF_WEEK_STARTING_SAT_DATE] =
dateadd(dd,((datediff(dd,'17530106',a.[DATE])/7)*7)+6,'17530106'),

[QUARTER_SEQ_NO] =
datediff(quarter,@LOW_DATE,a.[DATE]),
[MONTH_SEQ_NO] =
datediff(month,@LOW_DATE,a.[DATE]),

[WEEK_STARTING_SUN_SEQ_NO] =
datediff(day,'17530107',a.[DATE])/7,
[WEEK_STARTING_MON_SEQ_NO] =
datediff(day,'17530101',a.[DATE])/7,
[WEEK_STARTING_TUE_SEQ_NO] =
datediff(day,'17530102',a.[DATE])/7,
[WEEK_STARTING_WED_SEQ_NO] =
datediff(day,'17530103',a.[DATE])/7,
[WEEK_STARTING_THU_SEQ_NO] =
datediff(day,'17530104',a.[DATE])/7,
[WEEK_STARTING_FRI_SEQ_NO] =
datediff(day,'17530105',a.[DATE])/7,
[WEEK_STARTING_SAT_SEQ_NO] =
datediff(day,'17530106',a.[DATE])/7,

[JULIAN_DATE] =
datediff(day,@LOW_DATE,a.[DATE])+2361331,
[MODIFIED_JULIAN_DATE] =
datediff(day,'18581117',a.[DATE]),
--/*

[ISO_DATE] =
replace(convert(char(10),a.[DATE],111),'/','-') ,

[ISO_YEAR_WEEK_NO] =
(100*b.[ISO_WEEK_YEAR])+
(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 ,

[ISO_WEEK_NO] =
(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 ,

[ISO_DAY_OF_WEEK] =
-- Sunday = 1, Monday = 2, ,,,Saturday = 7
(datediff(dd,@LOW_DATE,a.[DATE])%7)+1 ,

[ISO_YEAR_WEEK_NAME] =
convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+
right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) ,

[ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] =
convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+
right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) +
'-'+convert(varchar(1),(datediff(dd,@LOW_DATE,a.[DATE])%7)+1) ,
--*/
[DATE_FORMAT_YYYY_MM_DD] =
convert(char(10),a.[DATE],111) ,
[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))),
[DATE_FORMAT_MM_DD_YYYY] =
convert(char(10),a.[DATE],101) ,
[DATE_FORMAT_M_D_YYYY] =
convert(varchar(10),
convert(varchar(2),month(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
convert(varchar(4),year(a.[DATE]))),
[DATE_FORMAT_MMM_D_YYYY] =
convert(varchar(12),
left(datename(month,a.[DATE]),3)+' '+
convert(varchar(2),day(a.[DATE]))+', '+
convert(varchar(4),year(a.[DATE]))),
[DATE_FORMAT_MMMMMMMMM_D_YYYY] =
convert(varchar(18),
datename(month,a.[DATE])+' '+
convert(varchar(2),day(a.[DATE]))+', '+
convert(varchar(4),year(a.[DATE]))),
[DATE_FORMAT_MM_DD_YY] =
convert(char(8),a.[DATE],1) ,
[DATE_FORMAT_M_D_YY] =
convert(varchar(8),
convert(varchar(2),month(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
right(convert(varchar(4),year(a.[DATE])),2))
from
(
-- Derived table is all dates needed for date range
select top 100 percent
[DATE_ID] = aa.[NUMBER],
[DATE] =
dateadd(dd,aa.[NUMBER],@LOW_DATE)
from
(
select
NUMBER = NUMBER+@start_no
from
@num3
where
NUMBER+@start_no <= @end_no
) aa
order by
aa.[NUMBER]
) a
join
-- Match each date to the proper ISO week year
@ISO_WEEK b
on a.[DATE] between
b.[ISO_WEEK_YEAR_START_DATE] and
b.[ISO_WEEK_YEAR_END_DATE]
order by
a.[DATE_ID]

return

end

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

rammohan
Posting Yak Master

212 Posts

Posted - 2007-09-13 : 09:29:46

@bharath

no identity constraint on any column in this table.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources