General Design Question

Hey

I need to store something a little different in a DB and I was hoping one of
you guys might be able to help me.

Basically it represents a 'world'. I have an initial state and then I get
info like this...

27/11/03 17:21 Mary is born
27/11/03 17:21 Dave is born
27/11/03 17:22 Sean is born
27/11/03 17:23 Peter dies
27/11/03 17:23 Fred is born

I need to be able to run querys like this...

How many people are alive at 27/11/03 17:22
Who was born between 27/11/03 17:22 and 27/11/03 17:23
etc.

Problem is, I'm going to have hundres of 'world's each with thousands of
entrys.

All help is appreciated :)

Tnx

Naomi"Naomi Morton" <dopey_delete@.remove.iol.ie> wrote in message
news:1069954180.280897@.emeairlvalid.ie.baltimore.c om...
> Hey
> I need to store something a little different in a DB and I was hoping one of
> you guys might be able to help me.
> Basically it represents a 'world'. I have an initial state and then I get
> info like this...
> 27/11/03 17:21 Mary is born
> 27/11/03 17:21 Dave is born
> 27/11/03 17:22 Sean is born
> 27/11/03 17:23 Peter dies
> 27/11/03 17:23 Fred is born

Perhaps something like this:

CREATE TABLE Worlds
(
world_id INT NOT NULL PRIMARY KEY
)

CREATE TABLE Persons
(
world_id INT NOT NULL REFERENCES Worlds (world_id),
person_name VARCHAR(25) NOT NULL,
birth_datetime DATETIME NOT NULL,
death_datetime DATETIME NULL, -- NULL if still alive
CHECK (death_datetime >= birth_datetime),
PRIMARY KEY (world_id, birth_datetime, person_name) -- simplification
)

> I need to be able to run querys like this...
> How many people are alive at 27/11/03 17:22

DECLARE @.alive_at_datetime DATETIME
SET @.alive_at_datetime = '20031127 17:22'
SELECT world_id, COUNT(*) AS alive_at_datetime
FROM Persons
WHERE birth_datetime <= @.alive_at_datetime AND
(death_datetime IS NULL OR death_datetime > @.alive_at_datetime)
GROUP BY world_id

> Who was born between 27/11/03 17:22 and 27/11/03 17:23

DECLARE @.start_datetime DATETIME, @.end_datetime DATETIME
SET @.start_datetime = '20031127 17:22'
SET @.end_datetime = '20031127 17:23'
SELECT world_id, person_name, birth_datetime
FROM Persons
WHERE birth_datetime BETWEEN @.start_datetime AND @.end_datetime

> etc.
> Problem is, I'm going to have hundres of 'world's each with thousands of
> entrys.

Millions of rows should not present a problem at all.

Regards,
jag

> All help is appreciated :)
> Tnx
> Naomi|||> 27/11/03 17:21 Mary is born
> 27/11/03 17:21 Dave is born
> 27/11/03 17:22 Sean is born
> 27/11/03 17:23 Peter dies
> 27/11/03 17:23 Fred is born
> I need to be able to run querys like this...
> How many people are alive at 27/11/03 17:22
> Who was born between 27/11/03 17:22 and 27/11/03 17:23
> etc.

Hi Naomi,

What you have is similar to banking transaction data. For example,
27/11/03 17:21 customer #1 debited $100 from his checking account. In
this case, the entity in question are individual accounts.

I assume you're creating a fantasy gaming world. The entity in
question are the character "avatars". To make a long story short, you
should have a WORLD table and an AVATAR table. The avatar is
populated by your journal transaction entries and should have worldID,
avatarID, birth, and death columns.

To query how many are alive:
select count(*) from avatar where death < @.death or death is null and
worldID=@.worldID

To query who was born between @.start and @.end:
select * from avatar where worldID=@.worldID and birth between @.start
and @.end

-- Louis

Gather Format and Store - Right or Wrong

The IT group that I work with has the habit of gathering data,
formatting (i.e. in reports) and then storing the same formated data in
the same database.
I think the practice is wrong. I think the activity is fundamentally
wrong because we are storing the exact same data in a database in two
different locations. Somehow I have the impression that database design
is about "oneness".
I believe that collecting the data and then storing summerized data for
reporting into a data warehouse would be the right solution.
I am getting flack for my viewpoint.
Am I all washed up?That sounds weird. IF the formatted data is stored for performance reasons,
I'd at least have it in
another database. But I prefer to do the report off of the production databa
se (if low activity and
doesn't have perf impact), or have a different database better suited for re
porting off of.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rlm" <groups@.rlmoore.net> wrote in message
news:1146747407.524001.97330@.j33g2000cwa.googlegroups.com...
> The IT group that I work with has the habit of gathering data,
> formatting (i.e. in reports) and then storing the same formated data in
> the same database.
> I think the practice is wrong. I think the activity is fundamentally
> wrong because we are storing the exact same data in a database in two
> different locations. Somehow I have the impression that database design
> is about "oneness".
> I believe that collecting the data and then storing summerized data for
> reporting into a data warehouse would be the right solution.
> I am getting flack for my viewpoint.
> Am I all washed up?
>

Gather Format and Store - Right or Wrong

The IT group that I work with has the habit of gathering data,
formatting (i.e. in reports) and then storing the same formated data in
the same database.

I think the practice is wrong. I think the activity is fundamentally
wrong because we are storing the exact same data in a database in two
different locations. Somehow I have the impression that database design
is about "oneness".

I believe that collecting the data and then storing summerized data for
reporting into a data warehouse would be the right solution.

I am getting flack for my viewpoint.

Am I all washed up?That sounds weird. IF the formatted data is stored for performance reasons, I'd at least have it in
another database. But I prefer to do the report off of the production database (if low activity and
doesn't have perf impact), or have a different database better suited for reporting off of.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"rlm" <groups@.rlmoore.net> wrote in message
news:1146747407.524001.97330@.j33g2000cwa.googlegro ups.com...
> The IT group that I work with has the habit of gathering data,
> formatting (i.e. in reports) and then storing the same formated data in
> the same database.
> I think the practice is wrong. I think the activity is fundamentally
> wrong because we are storing the exact same data in a database in two
> different locations. Somehow I have the impression that database design
> is about "oneness".
> I believe that collecting the data and then storing summerized data for
> reporting into a data warehouse would be the right solution.
> I am getting flack for my viewpoint.
> Am I all washed up?

Gather Format and Store - Right or Wrong

The IT group that I work with has the habit of gathering data,
formatting (i.e. in reports) and then storing the same formated data in
the same database.
I think the practice is wrong. I think the activity is fundamentally
wrong because we are storing the exact same data in a database in two
different locations. Somehow I have the impression that database design
is about "oneness".
I believe that collecting the data and then storing summerized data for
reporting into a data warehouse would be the right solution.
I am getting flack for my viewpoint.
Am I all washed up?That sounds weird. IF the formatted data is stored for performance reasons, I'd at least have it in
another database. But I prefer to do the report off of the production database (if low activity and
doesn't have perf impact), or have a different database better suited for reporting off of.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rlm" <groups@.rlmoore.net> wrote in message
news:1146747407.524001.97330@.j33g2000cwa.googlegroups.com...
> The IT group that I work with has the habit of gathering data,
> formatting (i.e. in reports) and then storing the same formated data in
> the same database.
> I think the practice is wrong. I think the activity is fundamentally
> wrong because we are storing the exact same data in a database in two
> different locations. Somehow I have the impression that database design
> is about "oneness".
> I believe that collecting the data and then storing summerized data for
> reporting into a data warehouse would be the right solution.
> I am getting flack for my viewpoint.
> Am I all washed up?
>

Function Performance question

I do have one store procedure which does insert into one table
CREATE PROCEDURE StoreProc1
AS
DECLARE testcursor CURSOR FOR
SELECT col1
FROM table
WHERE Id = @.ID
OPEN testcursor
FETCH NEXT FROM cursor INTO @.col1
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Here i have to use cursor because i am doing some calculation
--here based value of co11
--And then insert into one table
INSERT INTO TESTTABLE
(id,transactiondate...) values (@.value1,@.value2......)
FETCH NEXT FROM testcursor INTO @.col1
END
CLOSE testcursor
DEALLOCATE testcursor
This StoreProc1 i am running every night and which insert approx 500,000
records into TESTTABLE..Now I have a very simple function on TESTTABLE
which is as following..which i use in other store procedures...
CREATE FUNCTION TestFunction
(@.ID as INT,@.dt1 datetime,@.dt2 datetime)
returns money
AS
BEGIN
DECLARE @.retmoney money
SELECT @.retmoney = sum(amount)
FROM TESTTABLE
WHERE transactiondate between @.dt1 and @.dt2 and id = @.Id
and categoryid not in ('1','2')
RETURN @.retmoney
END
so what happen after running StoreProc1 every night...(which insert into
500 K records into TESTTABLE.. My function TestFunction becomes so slow.. it
takes 10 second to run and if i run query of that function
SELECT @.retmoney = sum(amount)
FROM TESTTABLE
WHERE createddate between @.dt1 and @.dt2 and id = @.Id
and categoryid not in ('1','2')
it get execute in only o seconds...
so why if i run that function it takes long and if i run that same query it
is fast...
Pls let me know.Hi
You may have perform maintainance on this table to update the indexes or
statistics as they could be fragmented or out of date. See DBCC SHOWCONTIG,
DBCC DBREINDEX and UPDATE STATISTICS in books online
John
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:6360AC44-D1D9-4D05-91B3-201C58E1B543@.microsoft.com...
>I do have one store procedure which does insert into one table
> CREATE PROCEDURE StoreProc1
> AS
> DECLARE testcursor CURSOR FOR
> SELECT col1
> FROM table
> WHERE Id = @.ID
> OPEN testcursor
> FETCH NEXT FROM cursor INTO @.col1
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --Here i have to use cursor because i am doing some calculation
> --here based value of co11
> --And then insert into one table
> INSERT INTO TESTTABLE
> (id,transactiondate...) values (@.value1,@.value2......)
> FETCH NEXT FROM testcursor INTO @.col1
> END
> CLOSE testcursor
> DEALLOCATE testcursor
> This StoreProc1 i am running every night and which insert approx 500,000
> records into TESTTABLE..Now I have a very simple function on TESTTABLE
> which is as following..which i use in other store procedures...
> CREATE FUNCTION TestFunction
> (@.ID as INT,@.dt1 datetime,@.dt2 datetime)
> returns money
> AS
> BEGIN
> DECLARE @.retmoney money
> SELECT @.retmoney = sum(amount)
> FROM TESTTABLE
> WHERE transactiondate between @.dt1 and @.dt2 and id = @.Id
> and categoryid not in ('1','2')
> RETURN @.retmoney
> END
>
> so what happen after running StoreProc1 every night...(which insert into
> 500 K records into TESTTABLE.. My function TestFunction becomes so slow..
> it
> takes 10 second to run and if i run query of that function
> SELECT @.retmoney = sum(amount)
> FROM TESTTABLE
> WHERE createddate between @.dt1 and @.dt2 and id = @.Id
> and categoryid not in ('1','2')
> it get execute in only o seconds...
> so why if i run that function it takes long and if i run that same query
> it
> is fast...
> Pls let me know.
>

Function inside a store procedure

Is it possible to create a function inside a store procedure use it and at
the end of the procedure drop the function'
Just curious if somebody has made it !!Marco A. Pi?a wrote:
> Is it possible to create a function inside a store procedure use it
> and at the end of the procedure drop the function'
> Just curious if somebody has made it !!
You could...
Create Proc CreateExecDropFunc
as
Begin
Declare @.SQL nvarchar(4000)
Declare @.FuncName nvarchar(36)
Declare @.TestInt int
Set @.FuncName = CAST(NEWID() as nvarchar(36))
-- Watch for line breaks on next line
Set @.SQL = N'Create Function [dbo].[' + @.FuncName + N'] (@.Param INT)
Returns INT as Begin Set @.Param = @.Param + 1 Return @.Param End'
Exec sp_executesql @.SQL
Print @.SQL
Set @.TestInt = 1
Set @.SQL = 'Select [dbo].[' + @.FuncName + N'](@.Param)'
Print @.SQL
Exec sp_executesql @.SQL, N'@.Param INT', @.TestInt
Set @.SQL = N'Drop Function [dbo].[' + @.FuncName + N']'
Exec sp_executesql @.SQL
End
Go
Exec CreateExecDropFunc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Excellent, very usefull !!!
"David Gugick" wrote:

> Marco A. Pi?a wrote:
> You could...
> Create Proc CreateExecDropFunc
> as
> Begin
> Declare @.SQL nvarchar(4000)
> Declare @.FuncName nvarchar(36)
> Declare @.TestInt int
> Set @.FuncName = CAST(NEWID() as nvarchar(36))
> -- Watch for line breaks on next line
> Set @.SQL = N'Create Function [dbo].[' + @.FuncName + N'] (@.Param INT)
> Returns INT as Begin Set @.Param = @.Param + 1 Return @.Param End'
> Exec sp_executesql @.SQL
> Print @.SQL
> Set @.TestInt = 1
> Set @.SQL = 'Select [dbo].[' + @.FuncName + N'](@.Param)'
> Print @.SQL
> Exec sp_executesql @.SQL, N'@.Param INT', @.TestInt
> Set @.SQL = N'Drop Function [dbo].[' + @.FuncName + N']'
> Exec sp_executesql @.SQL
> End
> Go
> Exec CreateExecDropFunc
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

fulltext search HTML problem

Hi,

currently we use text field in table to store HTML, and we use fulltext index to search this field, but we can not always get right results, for example, given text - "<P><STRONG>Access High Resorts<BR>", I can not get result if I search for "Access" or "cce", but if fine if I search for "High" or 'Resor", since I use CONTAINS, and CONTAINS can not search for postfix.

I know I can create another image data type field, and seach will filter HTML tag, but it will take long time to do.

Is there a way I can search postfix or mid-field of word in fulltext search? any idea or suggestion?

Thanks in advancethe sample string should like this - "<P><STRONG>Access High Resorst<BR>"